„Excel“ formulė: gaukite n-ąją atitiktį -

Turinys

Bendroji formulė

=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)

Santrauka

Norėdami gauti n-osios atitikties poziciją (pvz., 2-ą atitikimo vertę, 3-iąją atitikimo vertę ir kt.), Galite naudoti formulę, pagrįstą SMALL funkcija. Parodytame pavyzdyje formulė G5 yra:

=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)

Ši formulė pateikia antrojo sąrašo „raudona“ vietą.

Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.

Paaiškinimas

Ši formulė naudoja pavadintą diapazoną „sąrašas“, kuris yra diapazonas B5: B11.

Šios formulės esmė yra funkcija MAŽA, kuri tiesiog grąžina n-tąją mažiausią reikšmę eilutės numerius atitinkančių reikšmių sąraše. Eilučių numerius „filtravo“ IF sakinys, kuris taiko rungtynių logiką. Dirbdamas iš vidaus, IF lygina visas nurodyto diapazono „sąrašo“ reikšmes su B5 verte, kuri sukuria tokį masyvą:

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

„Value if true“ yra santykinių eilučių skaičių rinkinys, sukurtas pagal šį kodą:

ROW(list)-MIN(ROW(list))+1

Rezultatas yra toks masyvas:

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

Išsamų paaiškinimą rasite šiame puslapyje.

Naudojant loginį testą, kuris pateikia rezultatų masyvą, IF funkcija veikia kaip filtras - išlieka tik eilučių numeriai, atitinkantys atitiktį, likę - FALSE. IF grąžintas rezultatas atrodo taip:

(1;FALSE;FALSE;FALSE;5;FALSE;7)

Skaičiai 1, 5 ir 7 atitinka „raudonos“ vietą sąraše.

Galiausiai, SMALL grąžina n-tą mažiausią elementą šiame sąraše, nepaisydamas FALSE reikšmių. Pavyzdyje F5 yra 2, taigi SMALL pateikia 2-ą mažiausią reikšmę: 5.