Excel formula: iegūstiet n-to atbilstību ar INDEX / MATCH -

Vispārēja formula

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

Kopsavilkums

Lai izgūtu vairākas atbilstošas vērtības no datu kopas ar formulu, varat izmantot funkcijas IF un SMALL, lai noskaidrotu katras atbilstības rindas numuru un atgrieztu šo vērtību atpakaļ uz INDEX. Parādītajā piemērā formula I7 ir šāda:

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

Nosauktie diapazoni ir amti (D4: D11), id (I3) un id (C4: C11).

Ņemiet vērā, ka šī ir masīva formula un tā jāievada, nospiežot taustiņu kombināciju Control + Shift + Enter.

Paskaidrojums

Būtībā šī formula ir vienkārši INDEX formula, kas izgūst masīva vērtību noteiktā pozīcijā. N vērtība tiek sniegta H slejā, un viss "smagais" darbs, ko veic formula, ir noskaidrot rindu, no kuras iegūt vērtību, kur rinda atbilst "n".

Funkcija IF veic darbu, lai noskaidrotu, kurās rindās ir sakritība, un funkcija MAZA atgriež n-to vērtību no šī saraksta. IF iekšpusē loģiskais tests ir:

ids=id

kas dod šo masīvu:

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

Ņemiet vērā, ka klienta ID sakrīt ar 1. un 4. pozīciju, kas parādās kā PATIESA. IF arguments "value if true" ģenerē relatīvo rindu numuru sarakstu ar šo izteicienu:

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

kas ražo šo masīvu:

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

Pēc tam šo masīvu "filtrē" loģiskā testa rezultāti, un IF funkcija atgriež šādu masīva rezultātu:

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

Ņemiet vērā, ka mums ir derīgi rindu numuri 1. un 2. rindai.

Pēc tam šo masīvu apstrādā SMALL, kas ir konfigurēts tā, lai H slejas vērtības izmantotu, lai atgrieztu "n" vērtības. SMALL funkcija masīvā automātiski ignorē loģiskās vērtības TRUE un FALSE. Galu galā formulas samazinās līdz:

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

Rīcības kļūdas

Kad attiecīgajam ID vairs nav atbilstību, funkcija MAZA atgriezīs kļūdu #NUM. Šo kļūdu var apstrādāt, izmantojot funkciju IFERROR, vai pievienojot loģiku, lai saskaitītu spēles un pārtrauktu apstrādi, tiklīdz skaitlis H slejā ir lielāks par atbilstības skaitu. Piemērs šeit parāda vienu pieeju.