Általános képlet
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Összegzés
Ha a szöveget a "tartalmaz" egyezésű kulcsszavakkal kategorizálja, használhatja a KERESÉS funkciót, az INDEX és a MATCH segítségével. A bemutatott példában a C5 képlete a következő:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
ahol a kulcsszavak az E5: E14 elnevezett tartomány, a kategóriák pedig az F5: F14 megnevezett tartomány.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Lényegében ez egy INDEX és MATCH függvény.
A MATCH függvényen belül a KERESÉS funkcióval keressük meg a cellákat a B oszlopban a felsorolt kulcsszavak minden felsorolt kulcsszavára (E5: E14):
SEARCH(keywords,B5)
Mivel több elemet keresünk (a megnevezett tartomány kulcsszavaiban ), több ilyen eredményt kapunk vissza:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Az érték! hiba lép fel, amikor a KERESÉS nem találja a szöveget. Ha a KERESÉS talál egyezést, akkor egy olyan számot ad vissza, amely megfelel a cellán belüli szöveg helyzetének.
Ezen eredmények felhasználhatóbb formátumra történő megváltoztatásához használjuk az ISNUMBER függvényt, amely minden értéket IGAZ / HAMISZÁ alakít át:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Ez a tömb a MATCH függvénybe kerül lookup_array néven, a lookup_value értéke IGAZ. A MATCH az első IGAZ helyzetét adja vissza, amelyet a tömbben talál (ebben az esetben 7), amelyet az INDEX függvénynek sor_számként adunk meg:
=INDEX(categories,7)
Az INDEX a kategóriák 7. tételét , az "Auto" -t adja vissza végeredményként.
XLOOKUP-tal
Az XLOOKUP funkcióval ez a képlet némileg egyszerűsíthető. Az XLOOKUP ugyanazt a logikát használhatja, amelyet a fenti MATCH függvényben használnak, tehát az egyenértékű képlet:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
Az XLOOKUP megtalálja a tömb első IGAZ értékét, és a megfelelő értéket adja vissza a kategóriákból .
A hamis egyezések megelőzése
Ennek a megközelítésnek az egyik problémája, hogy hamis egyezéseket kaphat a hosszabb szavak belsejében megjelenő alsorokból. Például, ha megpróbálja megfelelni a "dr" szónak, akkor előfordulhat, hogy megtalálja az "Andrea", "ital", "száraz" stb. Elemeket is, mivel ezekben a szavakban a "dr" szerepel. Ez azért történik, mert a SEARCH automatikusan "tartalmaz" egyezést hajt végre.
A gyors feltörés érdekében helyet adhat a keresőszavak körül (pl. "Dr" vagy "dr"), hogy elkerülje a "dr" más szavak elkapását. De ez nem fog sikerülni, ha a "dr" először vagy utoljára jelenik meg egy cellában, vagy írásjelekkel jelenik meg stb.
Ha pontosabb megoldásra van szüksége, akkor az egyik lehetőség az, hogy először normalizálja a szöveget egy segítő oszlopban, ügyelve arra is, hogy egy vezető és záró helyet is hozzáadjon. Ezután egész szavakat kereshet szóközökkel körülvéve.
