Általános képlet
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Összegzés
Ha egy cellát ellenőriz a több dolog közül, és visszaküldi a listában található első egyezést, használhat egy INDEX / MATCH képletet, amely a SEARCH vagy a FIND segítségével keresi meg az egyezést. A bemutatott példában a C5 képlete a következő:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
ahol a "dolgok" az elnevezett tartomány E5: E9.
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.
Magyarázat
Ebben a példában van egy színek listája egy elnevezett tartományban, az úgynevezett "dolgok" (E5: E9). Ellenőrizni akarjuk a B oszlop szövegét, hogy tartalmazza-e ezeket a színeket. Ha igen, vissza akarjuk adni az első megtalált szín nevét.
Belülről kifelé haladva ez a képlet az ISNUMBER és a SEARCH függvény segítségével keresi meg a B5-ös szövegben az egyes "színekben" felsorolt színeket:
ISNUMBER(SEARCH(things,B5)
Ez a kifejezés egy olyan képleten alapul (amelyet itt részletesen elmagyarázunk), amely ellenőrzi, hogy egy cellában egyetlen alstring van-e. Ha a cella tartalmazza az alszöveget, akkor a kifejezés IGAZ-ot ad vissza. Ha nem, akkor a kifejezés FALSE-t ad vissza.
Amikor megadjuk ezt a KERESÉST, egy dolog felsorolása (egy dolog helyett) egy sor eredményt ad vissza. Minden megtalált szín numerikus pozíciót generál, a nem talált színek pedig hibát generálnak:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Az ISNUMBER függvény ezt követően az eredményeket IGAZ / HAMIS értékekké alakítja. Bármely szám IGAZ, és minden hiba (nem található) HAMIS lesz. Az eredmény egy ilyen tömb:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Ez a tömb visszakerül a MATCH függvénybe tömb argumentumként. A keresési érték IGAZ, és az egyezési típus nullára van állítva a pontos egyezés kikényszerítéséhez. Ha van egyező szín, a MATCH visszaadja az első IGAZ találatot. Ez az érték sorszámként kerül az INDEX függvénybe, tömbként megadva a "dolgok" nevű tartományt. Ha van legalább egy egyezés, az INDEX visszaadja a színt ebben a helyzetben. Ha nem található egyezés, ez a képlet a # N / A hibát adja vissza.
Keményen kódolt értékekkel
Ha ebben a példában nem szeretne beállítani egy olyan külső elnevezett tartományt, mint a "dolgok", akkor az értékeket keményen kódolhatja a képletbe "tömb konstansokként":
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Szerezd meg az első mérkőzést a cellában
A nyelv itt meglehetősen zavaró, de a fenti képlet visszaadja a keresendő dolgok listájában található első találatot. Ha ehelyett vissza szeretné adni a tesztelt cellában található első találatot, akkor kipróbálhat egy ilyen képletet:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
A képlet ezen verziójában a MATCH függvény be van állítva, hogy megkeresse a kódrészlet eredményét:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
amely az AGGREGATE függvényt használja a SEARCH által visszaadott eredmények minimális értékének megszerzéséhez. Szükségünk van itt az AGGREGATE-re, mert a bejövő tömb valószínűleg hibákat tartalmaz (amelyeket a SEARCH ad vissza, ha a dolgok nem találhatók), és szükségünk van egy olyan funkcióra, amely figyelmen kívül hagyja ezeket a hibákat, és mégis megadja a minimális numerikus értéket.
Az AGGREGATE eredménye közvetlenül a MATCH-ra kerül vissza, mint keresési érték, a SEARCH által visszaadott tömbdel együtt. A végeredmény az első találat a cellában, nem az első találat a dolgok listájában.
