Formula generică
=SUMPRODUCT(--(A1=things))>0
rezumat
Dacă doriți să testați o celulă pentru a vedea dacă este egal cu unul din mai multe lucruri, puteți face acest lucru cu o formulă care utilizează funcția SUMPRODUCT.
Context
Să presupunem că aveți o listă de șiruri de text în intervalul B5: B11 și doriți să testați fiecare celulă cu o altă listă de lucruri din intervalul E5: E9. Cu alte cuvinte, pentru fiecare celulă din B5: B11, doriți să știți: această celulă este egală cu oricare dintre lucrurile din E5: E9?
Ați putea începe să construiți o formulă mare bazată pe instrucțiuni IF imbricate, dar o formulă matrice bazată pe SUMPRODUCT este o abordare mai simplă și mai curată.
Soluţie
Soluția este de a crea o formulă care va testa mai multe valori și va returna o listă de valori ADEVĂRATE / FALSE. Odată ce avem acest lucru, putem procesa lista respectivă (o matrice, de fapt) cu SUMPRODUCT.
Formula pe care o folosim arată astfel:
=SUMPRODUCT(--(B5=things))>0
Explicaţie
Cheia este acest fragment:
--(B5=things)
care compară pur și simplu valoarea din B5 cu fiecare valoare din intervalul numit „lucruri”. Deoarece comparăm B5 cu o matrice (adică gama denumită „lucruri”, E5: E11), rezultatul va fi o matrice de valori ADEVĂRATE / FALSE ca aceasta:
(ADEVĂRAT; FALS; FALS; FALS; FALS)
Dacă avem chiar un adevărat în matrice, știm că B5 este egal cu cel puțin un lucru din listă, deci, pentru a forța valorile TRUE / FALSE la 1s și 0s, folosim un negativ dublu (-, numit și dublu unar). După această constrângere, avem acest lucru:
(1; 0; 0; 0; 0)
Acum procesăm rezultatul cu SUMPRODUCT, care va adăuga elementele din matrice. Dacă obținem orice rezultat diferit de zero, avem cel puțin o potrivire, deci folosim> 1 pentru a forța un rezultat final fie ADEVĂRAT, fie FALS.
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:
--(B5=("red","blue","green"))
Se ocupă de spații suplimentare
Dacă celulele pe care le testați conțin spațiu suplimentar, acestea nu se vor potrivi corect. Pentru a elimina tot spațiul suplimentar, puteți modifica formula pentru a utiliza funcția TRIM astfel:
=SUMPRODUCT(--(TRIM(A1)=things))>0
