Excel vzorec: Získajte n-tý zápas s INDEXOM / ZÁPASOM -

Všeobecný vzorec

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

Zhrnutie

Ak chcete z množiny údajov získať vzorec pomocou viacerých odpovedajúcich hodnôt, môžete pomocou funkcií IF a SMALL zistiť počet riadkov každej zhody a načítať túto hodnotu späť do INDEXU. V zobrazenom príklade je vzorec v I7:

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

V prípade pomenovaných rozsahov ide o hodnoty amts (D4: D11), id (I3) a ids (C4: C11).

Toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter.

Vysvetlenie

V jadre je tento vzorec jednoducho vzorcom INDEX, ktorý načíta hodnotu v poli na danej pozícii. Hodnota n je uvedená v stĺpci H a všetka „náročná“ práca, ktorú vzorec robí, je zistiť riadok, z ktorého sa má načítať hodnota, pričom riadok zodpovedá „n-tej“ zhode.

Funkcia IF vykonáva zisťovanie, ktoré riadky obsahujú zhodu, a funkcia SMALL vráti n-tú hodnotu z tohto zoznamu. Vo vnútri IF je logický test:

ids=id

ktorý dáva toto pole:

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

Všimnite si zhody ID zákazníka na 1. a 4. pozícii, ktoré sa javia ako PRAVDA. Argument „value if true“ v IF generuje zoznam relatívnych čísel riadkov s týmto výrazom:

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

ktorý vytvára toto pole:

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

Toto pole je potom „filtrované“ podľa výsledkov logického testu a funkcia IF vráti nasledujúci výsledok poľa:

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

Upozorňujeme, že máme platné čísla riadkov pre riadok 1 a riadok 2.

Toto pole potom spracuje program SMALL, ktorý je nakonfigurovaný na použitie hodnôt v stĺpci H na vrátenie „n-tých“ hodnôt. Funkcia SMALL automaticky ignoruje logické hodnoty TRUE a FALSE v poli. Nakoniec sa vzorce znížia na:

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

Zaobchádzanie s chybami

Ak pre dané ID už neexistuje zhoda, funkcia SMALL vráti chybu # ČÍSLO. Túto chybu môžete vyriešiť pomocou funkcie IFERROR alebo pridaním logiky na počítanie zhôd a prerušenie spracovania, akonáhle je počet v stĺpci H väčší ako počet zhôd. Tento príklad ukazuje jeden prístup.