Vispārēja formula
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Kopsavilkums
Lai iegūtu n-tās lielākās vērtības nosaukumu ar kritērijiem, varat izmantot INDEX un MATCH, funkciju LARGE un filtru, kas izveidots ar IF funkciju. Parādītajā piemērā šūnas G5 formula, kas nokopēta uz leju, ir:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kur nosaukums (B5: B16), grupa (C5: C16) un rezultāts (D5: D16) ir nosaukti diapazoni. Formula atgriež nosaukumu, kas saistīts ar A grupas 1., 2. un 3. augstāko vērtību.
Piezīme: Šī masīva formula, kas jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Paskaidrojums
LARGE funkcija ir vienkāršs veids, kā iegūt diapazonā n-to lielāko vērtību:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Šajā piemērā mēs varam izmantot funkciju LARGE, lai iegūtu visaugstāko punktu skaitu, pēc tam izmantojiet rezultātu kā "taustiņu", lai izgūtu saistīto vārdu ar INDEX un MATCH. Ievērojiet, ka mēs izvēlamies n vērtības no diapazona F5: F7, lai iegūtu 1., 2. un 3. augstāko punktu skaitu.
Tomēr šajā gadījumā mums ir jānošķir rādītāji A grupā un B grupā. Citiem vārdiem sakot, mums jāpiemēro kritēriji. Mēs to darām ar IF funkciju, kas tiek izmantota vērtību "filtrēšanai", pirms tās tiek novērtētas ar LARGE. Kā vispārīgu piemēru, lai iegūtu lielāko vērtību (ti, 1. vērtību) 2. diapazonā, kur diapazons 1 = "A", varat izmantot šādu formulu:
LARGE(IF(range="A",range2),1)
Piezīme: IF izmantošana šādā veidā padara šo masīva formulu.
Strādājot no iekšpuses uz āru, vispirms ir jāiegūst "1." lielākā vērtība datiem, kas saistīti ar A grupu ar funkciju LARGE:
LARGE(IF(group="A",score),F5)
Šajā gadījumā vērtība F5 ir 1, tāpēc mēs lūdzam augstāko punktu skaitu A grupā. Kad tiek novērtēta IF funkcija, tā pārbauda katru vērtību nosauktajā diapazona grupā . Nosauktais diapazona rezultāts tiek sniegts vērtībai_patiesi. Tādējādi tiek ģenerēts jauns masīvs, kas tiek tieši atgriezts funkcijā LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Ievērojiet, ka vienīgie rādītāji, kas izdzīvo filtrā, ir no A grupas. LARGE pēc tam atgriež augstāko atlikušo rezultātu 93 - tieši funkcijai MATCH kā uzmeklēšanas vērtība. Tagad mēs varam vienkāršot formulu:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Tagad mēs varam redzēt, ka funkcija MATCH ir konfigurēta, izmantojot to pašu filtrēto masīvu, kuru redzējām iepriekš. IF funkcija atkal filtrē nevēlamās vērtības, un formulas MATCH daļa tiek atrisināta šādi:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Tā kā 93 parādās 3. pozīcijā, MATCH atgriež 3 tieši funkcijai INDEX:
=INDEX(name,3) // Hannah
Visbeidzot, funkcija INDEX atgriež 3. rindas nosaukumu "Hannah".
Ar XLOOKUP
Funkciju XLOOKUP var izmantot arī šīs problēmas risināšanai, izmantojot to pašu pieeju, kas paskaidrota iepriekš:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Tāpat kā iepriekš, LARGE ir konfigurēts darbam ar masīvu, kuru filtrē IF, un kā uzmeklēšanas vērtību XLOOKUP atgriež rezultātu 93:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Uzmeklēšanas masīvs tiek izveidots arī, izmantojot IF kā filtru A grupas rādītājiem. Atgriešanas masīvs ir norādīts kā nosaukums (B5: B16). XLOOKUP atgriež "Hannah" kā gala rezultātu.
Piezīmes
- Lai iegūtu n-tās vērtības nosaukumu ar kritērijiem (ti, ierobežot rezultātus līdz A vai B grupai), jums būs jāpaplašina formula, lai izmantotu papildu loģiku.
- Programmā Excel 365 funkcija FILTER ir labāks veids, kā dinamiski uzskaitīt augšējos vai apakšējos rezultātus. Šī pieeja automātiski apstrādās saites.
