Generieke formule
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Samenvatting
Om tekst te categoriseren met behulp van trefwoorden met een "bevat" -overeenkomst, kunt u de SEARCH-functie gebruiken, met hulp van INDEX en MATCH. In het getoonde voorbeeld is de formule in C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
waarbij trefwoorden het benoemde bereik E5: E14 zijn, en categorieën het benoemde bereik F5: F14.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
In de kern is dit een INDEX- en MATCH-functie.
Binnen de MATCH-functie gebruiken we de SEARCH-functie om cellen in kolom B te doorzoeken voor elk vermeld trefwoord in de benoemde bereikzoekwoorden (E5: E14):
SEARCH(keywords,B5)
Omdat we op zoek zijn naar meerdere items (in de benoemde reeks trefwoorden ), krijgen we als volgt meerdere resultaten terug:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
De waarde! fout treedt op wanneer SEARCH de tekst niet kan vinden. Als SEARCH een overeenkomst vindt, retourneert het een getal dat overeenkomt met de positie van de tekst in de cel.
Om deze resultaten in een bruikbaarder formaat te veranderen, gebruiken we de ISNUMBER-functie, die alle waarden omzet naar TRUE / FALSE als volgt:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Deze array gaat naar de MATCH-functie als de lookup_array, met de lookup_value ingesteld als TRUE. VERGELIJKEN retourneert de positie van de eerste WAAR die het vindt in de array (in dit geval 7) die aan de functie INDEX wordt geleverd als het rij_getal:
=INDEX(categories,7)
INDEX retourneert het 7e item in de categorieën , "Auto", als een eindresultaat.
Met XLOOKUP
Met de functie XLOOKUP kan deze formule enigszins worden vereenvoudigd. XLOOKUP kan dezelfde logica gebruiken die wordt gebruikt in de MATCH-functie hierboven, dus de equivalente formule is:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP lokaliseert de eerste TRUE in de array en retourneert de corresponderende waarde uit categorieën .
Voorkomen van valse overeenkomsten
Een probleem met deze benadering is dat u mogelijk valse overeenkomsten krijgt van subtekenreeksen die in langere woorden voorkomen. Als u bijvoorbeeld "dr" probeert te matchen, kunt u ook "Andrea", "drinken", "droog", enz. Vinden, aangezien "dr" in deze woorden voorkomt. Dit gebeurt omdat SEARCH automatisch een "bevat" -overeenkomst doet.
Voor een snelle hack kun je spatie toevoegen rond de zoekwoorden (bijv. "Dr" of "dr") om te voorkomen dat je "dr" in een ander woord ophaalt. Maar dit zal mislukken als "dr" als eerste of laatste in een cel verschijnt, of wordt weergegeven met interpunctie, enz.
Als u een nauwkeurigere oplossing nodig heeft, is een optie om de tekst eerst in een hulpkolom te normaliseren, waarbij u ervoor zorgt dat u ook een voorloop- en volgspatie toevoegt. Vervolgens kunt u zoeken op hele woorden omringd door spaties.
