Vispārēja formula
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Kopsavilkums
Lai iegūtu n-tās lielākās vērtības nosaukumu, varat izmantot INDEX un MATCH ar funkciju LARGE. Parādītajā piemērā H5 šūnas formula ir:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kur nosaukums (B5: B16) un rezultāts (D5: D16) ir nosaukti diapazoni.
Paskaidrojums
Īsumā šajā formulā tiek izmantota funkcija LARGE, lai datu kopā atrastu n-to lielāko vērtību. Kad mums ir šī vērtība, mēs to pievienojam standarta formulai INDEX un MATCH, lai izgūtu saistīto vārdu. Citiem vārdiem sakot, mēs izmantojam n-to lielāko vērtību, piemēram, "atslēgu", lai izgūtu saistīto informāciju.
LARGE funkcija ir vienkāršs veids, kā iegūt diapazonā n lielāko vērtību. Vienkārši norādiet diapazonu pirmajam argumentam (masīvam) un n vērtību kā otro argumentu (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Strādājot no iekšpuses uz āru, pirmais solis ir iegūt "1." lielāko datu vērtību ar funkciju LARGE:
LARGE(score,F5) // returns 93
Šajā gadījumā vērtība F5 ir 1, tāpēc mēs pieprasām 1. lielāko punktu skaitu (ti, labāko punktu skaitu), kas ir 93. Tagad formulu var vienkāršot šādi:
=INDEX(name,MATCH(93,score,0))
Funkcijas INDEX iekšpusē funkcija MATCH ir iestatīta, lai atrastu 93 pozīciju nosauktajā diapazona rādītājā (D5: D16):
MATCH(93,score,0) // returns 3
Tā kā 3. rindā parādās 93, MATCH atgriež 3 tieši uz INDEX kā rindas numuru ar nosaukumu kā masīvu:
=INDEX(name,3) // Hannah
Visbeidzot, funkcija INDEX atgriež 3. rindas nosaukumu "Hannah".
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, kad formula tiek nokopēta.
Iegūt grupu
Tā pati pamatformula darbosies, lai izgūtu visu saistīto informāciju. Lai iegūtu lielāko vērtību grupu, varat vienkārši nomainīt INDEX piegādāto masīvu ar nosaukto diapazonu grupu :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Ar F5 vērtību 1 LARGE iegūs visaugstāko punktu skaitu, un formula atgriezīs "A".
Piezīme: izmantojot programmu Excel 365, varat izmantot funkciju FILTER, lai dinamiski uzskaitītu augšējos vai apakšējos rezultātus.
Ar XLOOKUP
XLOOKUP funkciju var izmantot arī, lai atgrieztu n lielākās vērtības nosaukumu šādi:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE atgriež lielāko vērtību 93 tieši uz XLOOKUP kā uzmeklēšanas vērtību:
=XLOOKUP(93,score,name) // Hannah
Izmantojot nosaukto diapazona punktu (D5: D16) kā uzmeklēšanas masīvu un nosaukumu (B5: B16) kā atgriešanas masīvu, XLOOKUP atgriež "Hannah" tāpat kā iepriekš.
Darbs ar saitēm
Vērtību dublikāti skaitliskajos datos radīs “neizšķirtu”. Ja ranžējamajās vērtībās ir vienāda vērtība, piemēram, ja pirmā un otrā lielākā vērtība ir vienāda, LARGE katrai atdos to pašu vērtību. Kad šī vērtība tiek nodota funkcijai MATCH, MATCH atgriezīs pirmās spēles pozīciju, tāpēc jūs redzēsiet to pašu (vārdu) atgriezto.
Ja pastāv saikņu iespēja, iespējams, vēlēsities ieviest kaut kādu sasaistīšanas stratēģiju. Viena pieeja ir izveidot jaunu palīgu kolonnu ar vērtībām, kas ir pielāgotas, lai pārtrauktu saites. Pēc tam izmantojiet palīga kolonnas vērtības, lai sarindotu un izgūtu informāciju. Tas padara saikņu nojaukšanai izmantoto loģiku skaidru un skaidru.
Vēl viena pieeja ir pārtraukt saites, pamatojoties tikai uz pozīciju (ti, pirmā neizšķirta "uzvar"). Šeit ir formula, kas izmanto šo pieeju:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Šeit mēs izmantojam MATCH, lai atrastu skaitli 1, un mēs izveidojam uzmeklēšanas masīvu, izmantojot loģisko loģiku, kas (1) salīdzina visus rādītājus ar vērtību, ko atgriezusi LARGE:
score=LARGE(score,F5)
un (2) izmanto paplašinošu diapazona pārbaudi, ja nosaukums jau ir rangu sarakstā:
COUNTIF(H$4:H4,name)=0
Kad vārds jau ir sarakstā, loģika to "atceļ" un tiek saskaņota nākamā (dublikāta) vērtība. Ievērojiet, ka paplašināšanas diapazons sākas ar iepriekšējo rindu, lai izvairītos no apļveida atsauces.
Šī pieeja darbojas šajā piemērā, jo vārdu kolonnā nav dublētu vārdu. Tomēr, ja sarindotajās vērtībās notiek vārdu dublikāti, pieeja jāpielāgo. Vieglākais risinājums ir pārliecināties, ka nosaukumi ir unikāli.
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.
