Splošna formula
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Povzetek
Če želite besedilo kategorizirati s ključnimi besedami z ujemanjem "vsebuje", lahko s pomočjo INDEX in MATCH uporabite funkcijo SEARCH. V prikazanem primeru je formula v C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kjer je ključna beseda imenovani obseg E5: E14, kategorije pa imenovani obseg F5: F14.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
V bistvu je to funkcija INDEX in MATCH.
Znotraj funkcijo MATCH, ki jih uporabljamo za iskanje celice v stolpcu B za vsak ponujeni ključno besedo v imenovanih dosega funkcijo iskanja ključnih besed (E5: E14):
SEARCH(keywords,B5)
Ker iščemo več elementov (v imenovanih ključnih besedah obsega ), bomo dobili več rezultatov, kot je ta:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Vrednost! pride do napake, ko SEARCH ne najde besedila. Ko SEARCH najde ujemanje, vrne številko, ki ustreza položaju besedila v celici.
Če želimo te rezultate spremeniti v uporabnejšo obliko, uporabimo funkcijo ISNUMBER, ki pretvori vse vrednosti v TRUE / FALSE tako:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Ta matrika gre v funkcijo MATCH kot lookup_array, pri čemer je lookup_value nastavljena na TRUE. MATCH vrne položaj prve TRUE, ki jo najde v matriki (v tem primeru 7), ki je na voljo funkciji INDEX kot row_num:
=INDEX(categories,7)
INDEX kot končni rezultat vrne 7. element v kategorijah , »Samodejno«.
Z XLOOKUP
S funkcijo XLOOKUP lahko to formulo nekoliko poenostavimo. XLOOKUP lahko uporablja isto logiko, ki se uporablja znotraj zgornje funkcije MATCH, zato je enakovredna formula:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP poišče prvo TRUE v polju in vrne ustrezno vrednost iz kategorij .
Preprečevanje lažnih ujemanj
Težava tega pristopa je, da lahko dobite napačna ujemanja iz podnizov, ki se pojavijo v daljših besedah. Če na primer poskušate ujemati "dr", boste morda našli tudi "Andrea", "pijačo", "suho" itd., Ker se v teh besedah pojavlja "dr". To se zgodi, ker SEARCH samodejno izvede ujemanje "vsebuje".
Za hiter kramp lahko dodate prostor okoli iskalnih besed (npr. "Dr" ali "dr"), da ne boste ujeli besede "dr" z drugo besedo. Vendar to ne bo uspelo, če se bo "dr" pojavil prvi ali zadnji v celici ali se bo pojavil s ločili itd
Če potrebujete natančnejšo rešitev, je ena možnost besedilo najprej normalizirati v pomožnem stolpcu, pri čemer pazite, da dodate tudi vodilni in zadnji prostor. Nato lahko iščete cele besede, obdane s presledki.
