Obecný vzorec
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
souhrn
Chcete-li kategorizovat text pomocí klíčových slov se shodou „obsahuje“, můžete použít funkci HLEDAT s pomocí INDEXU a ZÁPASU. V zobrazeném příkladu je vzorec v C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kde klíčová slova je pojmenovaný rozsah E5: E14 a kategorie je pojmenovaný rozsah F5: F14.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Jádrem je funkce INDEX a MATCH.
Uvnitř funkce MATCH jsme použít funkci Najít pro vyhledání buňky ve sloupci B pro každé klíčové slovo v památkově chráněné pojmenované oblasti klíčových slov (E5: E14):
SEARCH(keywords,B5)
Protože hledáme více položek (v klíčových slovech pojmenovaného rozsahu ), získáme zpět několik výsledků, jako je tento:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Hodnota! dojde k chybě, když SEARCH nemůže najít text. Když SEARCH najde shodu, vrátí číslo, které odpovídá pozici textu uvnitř buňky.
Chcete-li tyto výsledky změnit na použitelnější formát, použijeme funkci ISNUMBER, která převede všechny hodnoty na TRUE / FALSE takto:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Toto pole jde do funkce MATCH jako lookup_array, s lookup_value nastavenou na TRUE. MATCH vrací pozici první TRUE, kterou najde v poli (v tomto případě 7), které je poskytováno funkci INDEX jako row_num:
=INDEX(categories,7)
INDEX vrací 7. položku v kategoriích „Auto“ jako konečný výsledek.
S XLOOKUPEM
Pomocí funkce XLOOKUP lze tento vzorec trochu zjednodušit. XLOOKUP může využívat stejnou logiku použitou uvnitř výše uvedené funkce MATCH, takže ekvivalentní vzorec je:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP vyhledá první TRUE v poli a vrátí odpovídající hodnotu z kategorií .
Předcházení falešným shodám
Jedním z problémů tohoto přístupu je, že můžete získat falešné shody z podřetězců, které se objevují uvnitř delších slov. Pokud se například pokusíte najít výraz „dr“, můžete také najít „Andrea“, „drink“, „suchý“ atd., Protože uvnitř těchto slov se objevuje „dr“. K tomu dochází, protože SEARCH automaticky provede shodu „obsahuje“.
Chcete-li rychle zaseknout, můžete přidat hledaná slova (např. „Dr“ nebo „dr“), abyste zabránili zachycení slova „dr“ v jiném slově. To se ale nezdaří, pokud se „dr“ objeví jako první nebo poslední v buňce, nebo se objeví s interpunkcí atd.
Pokud potřebujete přesnější řešení, jednou z možností je normalizovat text nejprve ve sloupci pomocníka a současně přidat úvodní a koncový prostor. Poté můžete hledat celá slova obklopená mezerami.
