Kategorizujte text pomocou kľúčových slov

Na kategorizáciu textu pomocou kľúčových slov so zhodou „obsahuje“ môžete použiť funkciu VYHĽADÁVAŤ, s pomocou nástrojov INDEX a MATCH. V uvedenom príklade vzorec v C5 je:

excelová referenčná bunka na inom hárku

{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,text)),0))}

kde Kľúčové slová je pomenovaný rozsah E5: E14 a Kategórie je pomenovaný rozsah F5: F14.

Poznámka: toto je an maticový vzorec a je potrebné ho zadať kombináciou klávesov Ctrl + Shift + Enter.

V jadre je to ja Funkcia NDEX a MATCH .

Vnútri funkcie MATCH používame Funkcia SEARCH na vyhľadávanie buniek v stĺpci B pre každé uvedené kľúčové slovo v pomenovaný rozsah Kľúčové slová (E5: E14):

 
{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,B5)),0))}

Pretože hľadáme viacero položiek (v pomenovanom rozsahu Kľúčové slová ), vrátime sa k viacerým výsledkom takto:

 
 SEARCH (keywords,B5)

Hodnota! nastane chyba, keď funkcia SEARCH nemôže nájsť text. Keď funkcia SEARCH nájde zhodu, vráti číslo, ktoré zodpovedá pozícii textu v bunke.

Na zmenu týchto výsledkov na použiteľnejší formát používame príponu Funkcia ISNUMBER , ktorý prevádza všetky hodnoty na TRUE/FALSE takto:

 
{#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!24#VALUE!#VALUE!#VALUE!}

Toto pole prechádza do funkcie MATCH ako lookup_array , s lookup_value nastaviť ako PRAVDU. MATCH vráti pozíciu prvej PRAVDU, ktorú nájde v poli (v tomto prípade 7), ktoré je poskytnuté súboru Funkcia INDEX ako riadok_číslo :

 
{FALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSE}

INDEX vráti 7. položku v Kategórie „Auto“ ako konečný výsledok.

S XLOOKUP

S Funkcia XLOOKUP , tento vzorec je možné trochu zjednodušiť. XLOOKUP môže využívať rovnakú logiku, akú používal vo vyššie uvedenej funkcii MATCH, takže ekvivalentný vzorec je:

Excel spočítať všetky bunky s textom

 
= INDEX (categories,7)

XLOOKUP vyhľadá prvú PRAVDU v poli a vráti zodpovedajúcu hodnotu z Kategórie .

Predchádzanie falošným zhodám

Jeden problém tohto prístupu je, že môžete získať falošné zhody z podreťazcov, ktoré sa zobrazujú v dlhších slovách. Ak sa napríklad pokúsite priradiť výraz „dr“, môžete tiež nájsť výrazy „Andrea“, „nápoj“, „suché“ atď., Pretože v týchto slovách sa nachádza „dr“. Stáva sa to preto, že SEARCH automaticky robí zhodu „obsahuje“.

Ak chcete rýchlo hacknúť, môžete okolo hľadaných slov (napr. „Dr“ alebo „dr“) pridať medzeru, aby ste zabránili zachytávaniu výrazu „dr“ v inom slove. To však zlyhá, ak sa „dr“ zobrazí ako prvé alebo posledné v bunke alebo sa zobrazí s interpunkciou atď.

Ak potrebujete presnejšie riešenie, jednou z možností je normalizovať text najskôr v a pomocný stĺp , pričom dbajte tiež na to, aby ste pridali vodiaci a koncový priestor. Potom môžete vyhľadávať celé slová obklopené medzerami.