Formula generică
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
rezumat
Pentru a testa o celulă pentru a vedea dacă conține una dintre multele șiruri, puteți utiliza o formulă bazată pe funcțiile SEARCH, ISNUMBER și SUMPRODUCT. Formula din C5, copiată, este:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
unde lucrurile este gama numită E5: E9.
Explicaţie
Vrem să testăm fiecare celulă din B5: B11 pentru a vedea dacă conține oricare dintre șirurile din obiectele din domeniul numit (E5: E9). Formula pe care o folosim în C5, copiată, este:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Această formulă se bazează pe o formulă (explicată aici) care verifică o celulă pentru un singur șir. Dacă celula conține sub șirul, formula returnează TRUE. Dacă nu, formula returnează FALS:
ISNUMBER(SEARCH(things,B5))
Cu toate acestea, în acest caz, oferim SEARCH o listă de șiruri. Deoarece există 5 șiruri în lucruri , SEARCH returnează 5 rezultate într-o matrice ca aceasta:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Când SEARCH găsește un șir, acesta returnează poziția acelui șir. Dacă SEARCH nu găsește un șir, acesta returnează un #VALUE! eroare. Deoarece „galben” apare ca primul cuvânt din B5, vedem un 1. Deoarece celelalte șiruri nu sunt găsite, celelalte 4 elemente sunt erori.
Această matrice este returnată direct la funcția ISNUMBER. ISNUMBER returnează apoi o serie de valori ADEVĂRATE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Dacă avem chiar un adevărat în matrice, știm că o celulă conține cel puțin unul dintre șirurile pe care le căutăm. Cel mai simplu mod de a verifica ADEVĂRAT este să adăugați toate valorile împreună. Putem face asta cu SUMPRODUCT, dar mai întâi trebuie să forțăm valorile TRUE / FALSE la 1s și 0s cu un dublu negativ (-) astfel:
--ISNUMBER(SEARCH(things,B5))
Astfel se obține o nouă matrice care conține doar 1s și 0s:
(1;0;0;0;0)
livrat direct către SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Cu o singură matrice de procesat, SUMPRODUCT adaugă elementele din matrice și returnează un rezultat. Orice rezultat diferit de zero înseamnă că avem un „hit”, așa că adăugăm> 0 pentru a forța un rezultat final al TRUE sau FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Cu o listă codificată
Nu este necesar să utilizați un interval pentru lista de șiruri de căutat. De asemenea, puteți utiliza o constantă de matrice. De exemplu, pentru a verifica „roșu”, „albastru” și „verde”, puteți utiliza o formulă de genul acesta:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Prevenirea potrivirilor false
O problemă cu această abordare este că puteți obține potriviri false de la șiruri care apar în cuvinte mai lungi. De exemplu, dacă încercați să potriviți „dr”, puteți găsi și „Andrea”, „bea”, „uscat” etc., deoarece „dr” apare în aceste cuvinte. Acest lucru se întâmplă deoarece SEARCH face automat o potrivire „conține”.
Pentru un hack rapid, puteți adăuga spațiu în jurul cuvintelor de căutare (adică „dr” sau „dr”) pentru a evita prinderea „dr” într-un alt cuvânt. Dar acest lucru va eșua dacă „dr” apare prima sau ultima într-o celulă sau apare cu punctuație.
Dacă aveți nevoie de o soluție mai precisă, o opțiune este să normalizați mai întâi textul într-o coloană de ajutor, având grijă să adăugați și un spațiu principal și final. Apoi utilizați formula de pe această pagină pe textul rezultat.
