Generisk formel
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Sammanfattning
För att kategorisera text med hjälp av nyckelord med en "innehåller" -matchning kan du använda SÖK-funktionen med hjälp av INDEX och MATCH. I exemplet som visas är formeln i C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
där nyckelord är namngivna intervall E5: E14, och kategorier är namngivna intervall F5: F14.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
Kärnan är detta en INDEX- och MATCH-funktion.
Inuti MATCH-funktionen använder vi SÖK-funktionen för att söka celler i kolumn B för varje listat nyckelord i de angivna intervallens nyckelord (E5: E14):
SEARCH(keywords,B5)
Eftersom vi letar efter flera objekt (i de angivna intervallens nyckelord ) får vi tillbaka flera resultat så här:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Värdet! fel inträffar när SEARCH inte hittar texten. När SEARCH hittar en matchning returnerar det ett tal som motsvarar positionen för texten inuti cellen.
För att ändra dessa resultat till ett mer användbart format använder vi ISNUMBER-funktionen, som omvandlar alla värden till SANT / FALSK så:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Denna matris går in i MATCH-funktionen som lookup_array, med lookup_value inställd som TRUE. MATCH returnerar positionen för den första SANT den hittar i matrisen (7 i detta fall) som tillhandahålls till INDEX-funktionen som radnummer:
=INDEX(categories,7)
INDEX returnerar det sjunde objektet i kategorier , "Auto", som slutresultat.
Med XLOOKUP
Med XLOOKUP-funktionen kan denna formel förenklas något. XLOOKUP kan använda samma logik som används i MATCH-funktionen ovan, så motsvarande formel är:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP lokaliserar den första SANT i matrisen och returnerar motsvarande värde från kategorier .
Förhindra falska matchningar
Ett problem med detta tillvägagångssätt är att du kan få falska matchningar från understrängar som visas i längre ord. Om du till exempel försöker matcha "dr" kan du också hitta "Andrea", "drink", "dry", etc. eftersom "dr" visas i dessa ord. Detta händer eftersom SEARCH automatiskt gör en "innehåller" matchning.
För en snabb hackning kan du lägga till utrymme runt sökorden (dvs. "dr" eller "dr") för att undvika att fånga "dr" i ett annat ord. Men detta misslyckas om "dr" visas först eller sist i en cell, eller visas med skiljetecken etc.
Om du behöver en mer exakt lösning är ett alternativ att normalisera texten först i en hjälpkolumn, var noga med att lägga till ett ledande och efterföljande utrymme. Sedan kan du söka efter hela ord omgiven av mellanslag.
