Generička formula
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Sažetak
Da biste kategorizirali tekst pomoću ključnih riječi s podudaranjem "sadrži", možete koristiti funkciju SEARCH, uz pomoć INDEX-a i MATCH-a. U prikazanom primjeru formula u C5 je:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
gdje je ključna riječ imenovani raspon E5: E14, a kategorija imenovani raspon F5: F14.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
U srži je ovo funkcija INDEX i MATCH.
Unutar funkcije MATCH koristimo funkciju SEARCH za pretraživanje stanica u stupcu B za svaku navedenu ključnu riječ u navedenim ključnim riječima raspona (E5: E14):
SEARCH(keywords,B5)
Budući da tražimo više stavki (u navedenim ključnim riječima raspona ), vratit ćemo više rezultata poput ovog:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Vrijednost! pogreška se javlja kada SEARCH ne može pronaći tekst. Kada SEARCH pronađe podudaranje, vraća broj koji odgovara položaju teksta unutar ćelije.
Da bismo ove rezultate promijenili u upotrebljiviji format, koristimo funkciju ISNUMBER koja pretvara sve vrijednosti u TRUE / FALSE ovako:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Ovaj niz ulazi u funkciju MATCH kao lookup_array, s lookup_value postavljenom kao TRUE. MATCH vraća položaj prve TRUE koju pronađe u nizu (7 u ovom slučaju) koji je funkciji INDEX dostavljen kao number_num:
=INDEX(categories,7)
INDEX kao konačni rezultat vraća 7. stavku u kategorijama "Automatski".
Uz XLOOKUP
S funkcijom XLOOKUP ova se formula može ponešto pojednostaviti. XLOOKUP može koristiti istu logiku koja se koristi unutar gornje funkcije MATCH, pa je ekvivalentna formula:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP pronalazi prvu TRUE u polju i vraća odgovarajuću vrijednost iz kategorija .
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 interpunkcijom itd.
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. Tada možete pretraživati cijele riječi okružene razmacima.
