Excel képlet: Az n-edik találat megszerzése az INDEX / MATCH - paranccsal

Általános képlet

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Összegzés

Ha egy képlettel több adategyeztetési értéket szeretne lekérni egy adatsorból, akkor az IF és a SMALL funkciók segítségével kitalálhatja az egyes egyezések sorszámát, és visszajuttathatja ezeket az értékeket az INDEX-be. A bemutatott példában az I7 képlete a következő:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Ahol a megnevezett tartományok az amts (D4: D11), id (I3) és ids (C4: C11).

Vegye figyelembe, hogy ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.

Magyarázat

Lényegében ez a képlet egyszerűen egy INDEX képlet, amely egy tömbben egy adott pozícióban lekéri az értéket. Az n értéke a H oszlopban van megadva, és a képlet minden olyan "nehéz" feladata, hogy kitalálja azt a sort, amelyből lekérhető egy érték, ahol a sor megfelel az "n-edik" egyezésnek.

Az IF függvény feladata annak kiderítése, hogy mely sorok tartalmaznak egyezést, és a SMALL függvény visszaadja az adott lista n-edik értékét. Az IF belsejében a logikai teszt a következő:

ids=id

amely ezt a tömböt adja:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Ne feledje, hogy az ügyfél azonosítója az 1. és a 4. helyen áll, amelyek IGAZként jelennek meg. Az IF "value if true" argumentuma a relatív sorszámok listáját generálja ezzel a kifejezéssel:

ROW(ids)-ROW(INDEX(ids,1,1))+1

amely előállítja ezt a tömböt:

(1;2;3;4;5;6;7)

Ezt a tömböt ezután a logikai teszt eredményei "kiszűrik", és az IF függvény a következő tömb eredményt adja vissza:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Megjegyezzük, hogy érvényes sorszámunk van az 1. és a 2. sorhoz.

Ezt a tömböt ezután a SMALL feldolgozza, amely úgy van konfigurálva, hogy a H oszlopban szereplő értékeket használja az "n-edik" értékek visszaadására. A SMALL funkció automatikusan figyelmen kívül hagyja az IGAZ és HAMIS logikai értékeket a tömbben. Végül a képletek a következőkre redukálódnak:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Kezelési hibák

Ha már nincs egyezés az adott azonosítóhoz, a SMALL függvény #NUM hibát ad vissza. Ezt a hibát kezelheti az IFERROR függvénnyel, vagy logika hozzáadásával a mérkőzések számlálásához és a feldolgozás megszakításához, ha a H oszlopban szereplő szám nagyobb, mint az egyezés száma. Az itt látható példa egy megközelítést mutat be.