„Excel“ formulė: gaukite n-ąją atitiktį naudodami INDEX / MATCH -

Bendroji formulė

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

Santrauka

Norėdami gauti kelias atitikimo reikšmes iš duomenų rinkinio pagal formulę, galite naudoti IF ir SMALL funkcijas, norėdami išsiaiškinti kiekvienos atitikties eilutės numerį ir grąžinti tą vertę atgal į INDEX. Parodytame pavyzdyje I7 formulė yra:

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

Kur pavadinti diapazonai yra amtai (D4: D11), id (I3) ir id (C4: C11).

Atminkite, kad tai yra masyvo formulė ir ją reikia įvesti paspaudus „Control“ + „Shift“ + „Enter“.

Paaiškinimas

Iš esmės ši formulė yra tiesiog INDEX formulė, kuri nuskaito masyvo vertę tam tikroje pozicijoje. N vertė pateikiama H stulpelyje, o visas „sunkus“ darbas, kurį atlieka formulė, yra išsiaiškinti eilutę, iš kurios gaunama reikšmė, kur eilutė atitinka „n-ąją“ atitiktį.

IF funkcija atlieka išsiaiškinimą, kuriose eilutėse yra atitikimas, o funkcija SMALL grąžina n-ąją reikšmę iš to sąrašo. IF viduje loginis testas yra:

ids=id

gaunantis šį masyvą:

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

Atkreipkite dėmesį, kad kliento ID atitinka 1 ir 4 pozicijas, kurios rodomos kaip TRUE. IF argumentas „value if true“ sukuria santykinių eilučių skaičių sąrašą su šia išraiška:

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

kuris sukuria šį masyvą:

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

Tada šį masyvą „filtruoja“ loginiai bandymų rezultatai, o IF funkcija pateikia šį masyvo rezultatą:

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

Atkreipkite dėmesį, kad mes turime galiojančius 1 ir 2 eilių eilių numerius.

Tada šį masyvą apdoroja SMALL, kuris sukonfigūruotas naudoti H stulpelio reikšmes, kad būtų grąžintos „n-osios“ vertės. SMALL funkcija automatiškai nepaiso masyvo loginių verčių TRUE ir FALSE. Galų gale formulės sumažėja iki:

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

Tvarkymo klaidos

Kai nebeliks nurodyto ID atitikmenų, funkcija SMALL grąžins klaidą #NUM. Šią klaidą galite išspręsti naudodami IFERROR funkciją arba pridėdami logiką skaičiuodami atitikmenis ir nutraukdami apdorojimą, kai skaičius H stulpelyje yra didesnis nei atitikčių skaičius. Čia pateiktas pavyzdys rodo vieną požiūrį.