Generička formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sažetak
Da biste testirali ćeliju kako biste vidjeli sadrži li jedan od mnogih nizova, možete upotrijebiti formulu koja se temelji na funkcijama SEARCH, ISNUMBER i SUMPRODUCT. Kopirana formula u C5 je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
gdje je stvar imenovani raspon E5: E9.
Obrazloženje
Želimo testirati svaku ćeliju u B5: B11 kako bismo provjerili sadrži li neka od žica u navedenim stvarima raspona (E5: E9). Formula koju koristimo u C5, kopirana, je:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ova se formula temelji na formuli (ovdje objašnjenoj) koja provjerava u ćeliji jedan podniz. Ako ćelija sadrži podniz, formula vraća TRUE. Ako nije, formula vraća FALSE:
ISNUMBER(SEARCH(things,B5))
Međutim, u ovom slučaju dajemo SEARCH-u popis nizova. Budući da u stvarima postoji 5 nizova , SEARCH vraća 5 rezultata u nizu poput ovog:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kada SEARCH pronađe niz, vraća položaj tog niza. Ako SEARCH ne pronađe niz, vraća vrijednost #VALUE! pogreška. Budući da se "žuta" pojavljuje kao prva riječ u B5, vidimo 1. Budući da ostali nizovi nisu pronađeni, ostale su 4 stavke pogreške.
Ovaj se niz vraća izravno u funkciju ISNUMBER. ISNUMBER zatim vraća niz vrijednosti TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Ako u nizu imamo čak i jednu ISTINU, znamo da ćelija sadrži barem jednu od žica koje tražimo. Najlakši način da provjerite TRUE je zbrajanje svih vrijednosti. To možemo učiniti sa SUMPRODUCT, ali prvo moramo prisiliti vrijednosti TRUE / FALSE na 1s i 0s s dvostrukim negativom (-) ovako:
--ISNUMBER(SEARCH(things,B5))
To daje novi niz koji sadrži samo 1 i 0:
(1;0;0;0;0)
isporučuje se izravno SUMPRODUCT-u:
=SUMPRODUCT((1;0;0;0;0))
Sa samo jednim nizom za obradu, SUMPRODUCT dodaje stavke u niz i vraća rezultat. Bilo koji rezultat koji nije nula znači da imamo "pogodak", pa dodajemo> 0 kako bismo forsirali konačni rezultat TRUE ili FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
S tvrdim kodiranim popisom
Nije potrebno koristiti raspon za popis nizova koje treba tražiti. Također možete koristiti konstantu niza. Na primjer, za provjeru "crvene", "plave" i "zelene" možete upotrijebiti formulu poput ove:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Sprječavanje lažnih podudaranja
Jedan od problema s ovim pristupom je da možete dobiti lažna podudaranja iz podnizova koji se pojavljuju unutar dužih riječi. Na primjer, ako pokušate podudarati s "dr", možda ćete pronaći i "Andrea", "piće", "suho" itd. Jer se u tim riječima pojavljuje "dr". To se događa jer SEARCH automatski podudara "sadrži".
Za brzo hakiranje možete dodati prostor oko riječi za pretraživanje (npr. "Dr" ili "dr") kako biste izbjegli hvatanje "dr" u drugoj riječi. Ali to neće uspjeti ako se "dr" pojavi prvi ili zadnji u ćeliji ili se pojavi s interpunkcijskim znakovima.
Ako trebate preciznije rješenje, jedna je mogućnost prvo normalizirati tekst u pomoćni stupac, vodeći računa da dodate i vodeći i prateći prostor. Zatim koristite formulu na ovoj stranici u rezultirajućem tekstu.
