Generisk formel
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Resumé
For at kategorisere tekst ved hjælp af nøgleord med en "indeholder" match, kan du bruge funktionen SØG med hjælp fra INDEX og MATCH. I det viste eksempel er formlen i C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
hvor nøgleord er det navngivne område E5: E14, og kategorier er det navngivne område F5: F14.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
Kernen er dette en INDEX- og MATCH-funktion.
Inde i MATCH-funktionen bruger vi SEARCH-funktionen til at søge celler i kolonne B for hvert anført søgeord i de navngivne nøgleord (E5: E14):
SEARCH(keywords,B5)
Fordi vi leder efter flere emner (i de navngivne nøgleord for området ), får vi flere resultater som denne:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Værdien! fejl opstår, når SEARCH ikke kan finde teksten. Når SEARCH finder et match, returnerer det et tal, der svarer til placeringen af teksten inde i cellen.
For at ændre disse resultater til et mere anvendeligt format bruger vi ISNUMBER-funktionen, som konverterer alle værdier til SAND / FALSK som sådan:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Denne matrix går ind i MATCH-funktionen som lookup_array, med lookup_value sat som TRUE. MATCH returnerer positionen for den første SANDE, den finder i arrayet (7 i dette tilfælde), som leveres til INDEX-funktionen som række_nummer:
=INDEX(categories,7)
INDEX returnerer det syvende element i kategorier , "Auto", som et endeligt resultat.
Med XLOOKUP
Med XLOOKUP-funktionen kan denne formel forenkles noget. XLOOKUP kan bruge den samme logik, der bruges i MATCH-funktionen ovenfor, så den tilsvarende formel er:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP lokaliserer den første TRUE i arrayet og returnerer den tilsvarende værdi fra kategorier .
Forebyggelse af falske kampe
Et problem med denne tilgang er, at du kan få falske matches fra understrenge, der vises i længere ord. For eksempel, hvis du prøver at matche "dr", kan du også finde "Andrea", "drikke", "tør" osv., Da "dr" vises inde i disse ord. Dette sker, fordi SEARCH automatisk matcher en "indeholder".
For et hurtigt hack kan du tilføje plads omkring søgeordene (dvs. "dr" eller "dr") for at undgå at fange "dr" i et andet ord. Men dette mislykkes, hvis "dr" vises først eller sidst i en celle eller vises med tegnsætning osv.
Hvis du har brug for en mere nøjagtig løsning, er en mulighed at normalisere teksten først i en hjælpekolonne og passe på at tilføje et ledende og efterfølgende rum. Derefter kan du søge efter hele ord omgivet af mellemrum.
