Generička formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sažetak
Da biste istaknuli stanice koje sadrže jedan od mnogih tekstualnih nizova, možete upotrijebiti formulu koja se temelji na funkcijama BROJ i TRAŽENJE, zajedno s funkcijom SUMPRODUCT. U prikazanom primjeru, uvjetno oblikovanje primijenjeno na B4: B11 temelji se na ovoj formuli:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Obrazloženje
Radeći iznutra prema van, ovaj dio formule pretražuje svaku ćeliju u B4: B11 za sve vrijednosti u imenovanom rasponu "stvari":
--ISNUMBER(SEARCH(things,B4)
Funkcija SEARCH vraća položaj vrijednosti ako je pronađena i pogrešku #VRIJEDINA ako nije pronađena. Za B4, rezultati se vraćaju u nizu poput ovog:
(8;#VALUE!;#VALUE!)
Funkcija ISNUMBER mijenja sve rezultate u TRUE ili FALSE:
(TRUE;FALSE;FALSE)
Dvostruki negativ ispred ISUMBER prisiljava TRUE / FALSE na 1/0:
(1;0;0)
Funkcija SUMPRODUCT zatim zbraja rezultate, koji se testiraju na nuli:
=SUMPRODUCT((1;0;0))>0
Bilo koji rezultat koji nije nula znači da je pronađena barem jedna vrijednost, pa formula vraća TRUE, pokrećući pravilo.
Zanemarite prazne stvari
Da biste zanemarili prazne ćelije u imenovanom rasponu "stvari", možete isprobati modificiranu formulu poput ove:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
To funkcionira sve dok tekstualne vrijednosti koje testirate ne sadrže niz "FALSE". Ako se to dogodi, možete proširiti funkciju IF tako da uključi vrijednost ako se zna da se lažno ne pojavljuje u tekstu (tj. "Zzzz", "####" itd.)
Opcija osjetljiva na velika i mala slova
PRETRAŽIVANJE ne razlikuje velika i mala slova. Da biste provjerili i slučaj, zamijenite SEARCH s FIND na sljedeći način:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Sprječavanje lažnih podudaranja
Jedan od problema s ovim pristupom je što ćete možda vidjeti lažna podudaranja uzrokovana podnizovima 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 izvršava podudaranje "sadrži".
Za djelomični popravak 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 pored interpunkcije. To se može djelomično riješiti dodavanjem prostora i oko originalnog teksta. Da biste istovremeno dodali prostor na početak i na kraj, možete isprobati formulu poput ove:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Međutim, ovo neće riješiti probleme uzrokovane interpunkcijom.
Ako trebate cjelovitije rješenje, jedna je mogućnost normalizirati tekst prvo u pomoćnom stupcu, vodeći računa da dodate i vodeći i prateći prostor. Tada možete pretraživati cijele riječi okružene razmacima.
