Formula generică
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
rezumat
Dacă doriți să testați o celulă pentru a vedea dacă conține toate elementele dintr-o listă, puteți face acest lucru cu o formulă care utilizează funcția CĂUTARE, cu ajutorul funcțiilor ISNUMBER, SUMPRODUCT și COUNTA.
Context
Să presupunem că aveți o listă de șiruri de text în intervalul B5: B8 și doriți să aflați dacă aceste celule conțin toate cuvintele dintr-un alt interval, E5: E7.
Ați putea construi o formulă care utilizează instrucțiuni IF imbricate pentru a verifica fiecare element, dar care nu se va scala bine dacă aveți o mulțime de lucruri de căutat. De fiecare dată când adăugați un cuvânt de căutat, va trebui să adăugați un alt IF imbricat și să ajustați parantezele.
Soluţie
Soluția constă în crearea unei formule care contează toate meciurile simultan. Odată ce avem acest lucru, comparăm pur și simplu acest număr cu numărul de articole pe care le căutăm. Dacă se potrivesc, știm că o celulă conține toate articolele.
În exemplul prezentat, formula pe care o folosim este:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Explicaţie
Cheia este acest fragment:
ISNUMBER(SEARCH(things,B5)
Aceasta se bazează pe o altă formulă (explicată în detaliu aici) care verifică pur și simplu o celulă pentru un singur șir. Dacă celula conține sub șirul, formula returnează TRUE. Dacă nu, formula returnează FALS.
Cu toate acestea, dacă oferim aceleiași formule o listă de lucruri (în acest caz, folosim un interval numit numit „lucruri”, E5: E7) ne va reda o listă cu valori ADEVĂRATE / FALSE, câte una pentru fiecare element din . Rezultatul este o matrice care arată astfel:
(ADEVĂRAT; ADEVĂRAT; ADEVĂRAT)
Unde fiecare ADEVĂRAT reprezintă un articol găsit și fiecare FALS reprezintă un element care nu a fost găsit.
Putem forța valorile TRUE / FALSE la 1s și 0s cu un dublu negativ (-, numit și unar dublu):
--ISNUMBER(SEARCH(things,B5))
care produce o matrice ca aceasta:
(1; 1; 1)
Apoi, procesăm această matrice cu SUMPRODUCT, care ne va da o sumă totală. Dacă această sumă este egală cu numărul de articole din intervalul numit „lucruri”, știm că am găsit toate lucrurile și putem întoarce ADEVĂRAT. Modul în care facem acest lucru este să comparăm direct cele două numere. Obținem un număr de celule care nu sunt goale în „lucruri” folosind COUNTA:
=COUNTA(things)
Cu o listă codificată
Nu este necesară utilizarea unei game pentru lista de lucruri. Dacă căutați doar un număr mic de lucruri, puteți utiliza o listă în format matrice, care se numește constantă matrice. De exemplu, dacă căutați doar culorile roșu, albastru și verde, puteți utiliza („roșu”, „albastru”, „verde”) astfel:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
