Üldine valem
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Kokkuvõte
Teksti liigitamiseks märksõnadega, mille vaste on "sisaldab", saate kasutada funktsiooni OTSI, mille abiks on INDEX ja MATCH. Näidatud näites on valem C5-s:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kus märksõnad on nimega vahemik E5: E14 ja kategooriad nimega vahemik F5: F14.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Põhimõtteliselt on see funktsioon INDEX ja MATCH.
Funktsiooni MATCH sees otsime veerust B lahtritest iga loetletud märksõna nimel vahemikus märksõnades (E5: E14) funktsiooni SEARCH :
SEARCH(keywords,B5)
Kuna otsime mitut üksust (nimetatud vahemiku märksõnades ), saame tagasi mitu sellist tulemust:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
#VALUE! tõrge ilmneb siis, kui SEARCH ei leia teksti. Kui OTSI leiab vaste, tagastab see arvu, mis vastab teksti asendile lahtris.
Nende tulemuste kasutatavamaks vorminguks muutmiseks kasutame funktsiooni ISNUMBER, mis teisendab kõik väärtused väärtuseks TÕENE / VALE:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
See massiiv läheb funktsiooni MATCH otsingu_kaardina, kusjuures otsingu_väärtuseks on seatud TRUE. MATCH tagastab massiivi (antud juhul 7) leitud esimese TÕENE positsiooni, mis on funktsioonile INDEX antud rea_numbrina:
=INDEX(categories,7)
INDEX tagastab lõpptulemusena kategooriates 7. elemendi "Automaatne".
XLOOKUPiga
Funktsiooniga XLOOKUP saab seda valemit mõnevõrra lihtsustada. XLOOKUP saab kasutada sama loogikat, mida kasutatakse ülalolevas funktsioonis MATCH, seega on samaväärne valem järgmine:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP otsib massiivi esimese TRUE ja tagastab kategooriate vastava väärtuse .
Vale vastete vältimine
Selle lähenemisviisi üks probleem on see, et pikemate sõnade sees esinevatest alamstringidest võite saada valesid vasteid. Näiteks kui proovite sobitada "dr", võite leida ka "Andrea", "juua", "kuiv" jne, kuna nende sõnade sees on "dr". See juhtub seetõttu, et SEARCH teeb automaatselt vaste "sisaldab".
Kiire häkkimise huvides võite otsingusõnade (nt "dr" või "dr") ümber tühiku lisada, et vältida "dr" teise sõna tabamist. Kuid see ei õnnestu, kui "dr" ilmub lahtris esimesena või viimasena või ilmub kirjavahemärkidega jne.
Kui vajate täpsemat lahendust, on üks võimalus teksti normaliseerida kõigepealt abistaja veerus, lisades kindlasti ka ees- ja tagaosa. Seejärel saate otsida terveid sõnu, mis on ümbritsetud tühikutega.
