Általános képlet
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Összegzés
Ha egy cellát tesztel a több karaktersorozat valamelyikén, és egyedi eredményt ad vissza az első megtalált egyezéshez, használhatja a KERESÉS függvényen alapuló INDEX / MATCH képletet. A bemutatott példában a C5 képlete a következő:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
ahol a dolgokat (E5: E8) és az eredményeket (F5: F8) tartományoknak nevezzük.
Ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.
Magyarázat
Ez a képlet két megnevezett tartományt használ: dolgokat és eredményeket . Ha ezt a képletet közvetlenül portolja, mindenképpen használjon megnevezett tartományokat ugyanazokkal a nevekkel (az adatok alapján definiálva). Ha nem akar nevezett tartományokat használni, akkor ehelyett abszolút referenciákat használjon.
Ennek a képletnek a lényege ez a részlet:
ISNUMBER(SEARCH(things,B5)
Ez egy másik képleten alapul (itt részletesen elmagyarázzuk), amely ellenőrzi, hogy egy cellában egyetlen alstring van-e. Ha a cella tartalmazza az alszöveget, a képlet IGAZ értéket ad vissza. Ha nem, akkor a képlet HAMIS.
Mert mi így a kereső funkciót több dolgot kell keresnie, a megnevezett tartományban dolgokat , ez ad nekünk több az egy eredmény, egy tömbben, hogy így néz ki:
(#VALUE!;9;#VALUE!;#VALUE!)
A számok a dolgokban való egyezést jelentik , a hibák pedig azokat az elemeket, amelyek nem találhatók.
A tömb egyszerűsítése érdekében az ISNUMBER függvényt használjuk arra, hogy a tömb összes elemét IGAZ vagy HAMIS értékre konvertáljuk. Bármely érvényes szám IGAZsá válik, és minden hiba (azaz egy dolog nem található) HAMISá válik. Az eredmény egy ilyen tömb:
(FALSE;TRUE;FALSE;FALSE)
amely a MATCH függvénybe kerül a lookup_array argumentumként, az IGAZ keresési_érték:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
A MATCH ekkor az első megtalált IGAZ pozíciót adja vissza, ebben az esetben 2-t.
Végül az INDEX függvény segítségével lekérjük az eredményt a megnevezett tartomány eredményeiből ugyanabban a helyzetben:
=INDEX(results,2) // returns "found red"
Testreszabhatja az eredménytartományt bármilyen értékkel, amely értelmes a felhasználási esetben.
A hamis egyezések megelőzése
Az ISNUMBER + SEARCH megközelítés egyik megközelítésével az a probléma, hogy hamis találatokat kaphat a hosszabb szavak részleges egyezéséből. Például, ha megpróbálja megfelelni a "dr" szónak, akkor előfordulhat, hogy megtalálja az "Andrea", "ivott", "csepegtető" stb. Elemeket is, mivel ezekben a szavakban a "dr" szerepel. Ez azért történik, mert a KERESÉS automatikusan elvégzi a "tartalmaz-típusú" egyezést.
A gyors javítás érdekében a keresett szavakat szóközökbe (például "dr" vagy "dr") is beírhatja, hogy elkerülje a "dr" kifejezés megtalálását egy másik szóban. De ez nem sikerül, ha a "dr" jelenik meg először vagy utoljára egy cellában.
Ha erőteljesebb megoldásra van szüksége, az egyik lehetőség az, hogy először normalizálja a szöveget egy segítő oszlopban, és hozzáad egy vezető és záró helyet. Ezután használja az ezen az oldalon található képletet a segítő oszlop szövegén, az eredeti szöveg helyett.
