Všeobecný vzorec
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Zhrnutie
Ak chcete kategorizovať text pomocou kľúčových slov so zhodou „obsahuje“, môžete pomocou funkcie INDEX a MATCH použiť funkciu VYHĽADÁVANIE. V zobrazenom príklade je vzorec v C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kde kľúčové slová sú pomenovaný rozsah E5: E14 a kategórie je pomenovaný rozsah F5: F14.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
V jadre ide o funkciu INDEX a MATCH.
Vo vnútri funkcie MATCH používame funkciu SEARCH na vyhľadávanie buniek v stĺpci B pre každé uvedené kľúčové slovo v pomenovaných kľúčových slovách rozsahu (E5: E14):
SEARCH(keywords,B5)
Pretože hľadáme viac položiek (v pomenovaných kľúčových slovách rozsahu ), vrátime späť niekoľko výsledkov, ako je tento:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Hodnota! chyba, keď SEARCH nenájde text. Keď SEARCH nájde zhodu, vráti číslo, ktoré zodpovedá pozícií textu vo vnútri bunky.
Na zmenu týchto výsledkov do použiteľnejšieho formátu používame funkciu ISNUMBER, ktorá prevádza všetky hodnoty na TRUE / FALSE takto:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Toto pole vstupuje do funkcie MATCH ako lookup_array s hodnotou lookup_value nastavenou na TRUE. MATCH vráti pozíciu prvej TRUE, ktorú nájde v poli (v tomto prípade 7), ktoré je poskytované funkcii INDEX ako row_num:
=INDEX(categories,7)
INDEX vráti 7. položku v kategóriách „Auto“ ako konečný výsledok.
S XLOOKUPOM
Pomocou funkcie XLOOKUP sa dá tento vzorec trochu zjednodušiť. XLOOKUP môže využívať rovnakú logiku použitú vo vnútri vyššie uvedenej funkcie MATCH, takže ekvivalentný vzorec je:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP vyhľadá prvý TRUE v poli a vráti zodpovedajúcu hodnotu z kategórií .
Predchádzanie nesprávnym zhodám
Jedným z problémov tohto prístupu je, že môžete získať falošné zhody z podreťazcov, ktoré sa objavujú vo vnútri dlhších slov. Ak sa napríklad pokúsite nájsť zhodu s výrazom „dr“, môžete tiež nájsť „Andrea“, „piť“, „suché“ atď., Pretože v týchto slovách sa vyskytuje výraz „dr“. Stáva sa to preto, lebo SEARCH automaticky vykoná zhodu „obsahuje“.
Ak chcete rýchlo zaseknúť, môžete okolo hľadaných slov (napr. „Dr“ alebo „dr“) pridať medzeru, aby ste zabránili chyteniu slova „dr“ v inom slove. To sa však nepodarí, ak sa výraz „dr“ objaví ako prvý alebo posledný v bunke alebo sa objaví s interpunkciou atď.
Ak potrebujete presnejšie riešenie, jednou z možností je normalizácia textu najskôr v stĺpci pomocníka, pričom treba pridať aj úvodnú a koncovú medzeru. Potom môžete vyhľadávať celé slová obklopené medzerami.
