Generisk formel
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Sammendrag
For å kategorisere tekst ved hjelp av nøkkelord med "inneholder" -treff, kan du bruke SØK-funksjonen, med hjelp fra INDEX og MATCH. I eksemplet vist er formelen i C5:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
der nøkkelord er det navngitte området E5: E14, og kategorier er det navngitte området F5: F14.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
Forklaring
I kjernen er dette en INDEKS- og MATCH-funksjon.
Inne i MATCH-funksjonen bruker vi SEARCH-funksjonen til å søke i celler i kolonne B for hvert oppførte nøkkelord i de nevnte områdesøkeordene (E5: E14):
SEARCH(keywords,B5)
Fordi vi leter etter flere elementer (i de nevnte nøkkelordene for området ), får vi tilbake flere resultater som dette:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Verdien! feil oppstår når SØK ikke finner teksten. Når SØK finner et samsvar, returnerer det et tall som tilsvarer plasseringen av teksten inne i cellen.
For å endre disse resultatene til et mer brukbart format, bruker vi ISNUMBER-funksjonen, som konverterer alle verdier til SANN / FALSK slik:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Denne matrisen går inn i MATCH-funksjonen som lookup_array, med lookup_value satt som TRUE. MATCH returnerer posisjonen til den første SANNE den finner i matrisen (7 i dette tilfellet) som blir gitt til INDEX-funksjonen som radnummer:
=INDEX(categories,7)
INDEX returnerer det syvende elementet i kategoriene "Auto", som et endelig resultat.
Med XLOOKUP
Med XLOOKUP-funksjonen kan denne formelen forenkles noe. XLOOKUP kan bruke den samme logikken som brukes i MATCH-funksjonen ovenfor, så den tilsvarende formelen er:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP lokaliserer den første SANNE i matrisen, og returnerer den tilsvarende verdien fra kategorier .
Forebygge falske kamper
Et problem med denne tilnærmingen er at du kan få falske treff fra understreng som vises i lengre ord. Hvis du for eksempel prøver å matche "dr", kan du også finne "Andrea", "drikke", "tørr" osv. Siden "dr" vises inne i disse ordene. Dette skjer fordi SEARCH automatisk samsvarer med "inneholder".
For en rask hacking kan du legge til mellomrom rundt søkeordene (dvs. "dr" eller "dr") for å unngå å fange "dr" i et annet ord. Men dette vil mislykkes hvis "dr" vises først eller sist i en celle, eller vises med tegnsetting, etc.
Hvis du trenger en mer nøyaktig løsning, er et alternativ å normalisere teksten først i en hjelpekolonne, og passe på å også legge til et ledende og etterfølgende rom. Deretter kan du søke etter hele ord omgitt av mellomrom.
