Splošna formula
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Povzetek
Če želite dobiti n-to največjo vrednost z merili, lahko uporabite INDEX in MATCH, funkcijo LARGE in filter, ustvarjen s funkcijo IF. V prikazanem primeru je formula v kopirani celici G5:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kjer so imena (B5: B16), skupina (C5: C16) in rezultat (D5: D16) imenovani obsegi. Formula vrne ime, povezano z 1., 2. in 3. najvišjo vrednostjo v skupini A.
Opomba: Ta formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Pojasnilo
Funkcija LARGE je preprost način, da dobite n-to največjo vrednost v območju:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
V tem primeru lahko uporabimo funkcijo LARGE, da dobimo najvišjo oceno, nato pa rezultat kot "ključ" pridobimo s pripadajočim imenom z INDEX in MATCH. Upoštevajte, da pobiramo vrednosti za n iz območja F5: F7, da dobimo 1., 2. in 3. najvišjo oceno.
Vendar je v tem primeru zasuk v tem, da moramo razlikovati med rezultati v skupini A in skupini B. Z drugimi besedami, uporabiti moramo merila. To naredimo s funkcijo IF, ki se uporablja za "filtriranje" vrednosti, preden so ovrednotene z LARGE. Kot splošni primer lahko za pridobitev največje vrednosti (tj. 1. vrednosti) v obsegu2, kjer je obseg 1 = "A", uporabite naslednjo formulo:
LARGE(IF(range="A",range2),1)
Opomba: če IF uporablja ta način, je to matrična formula.
Če delamo od znotraj navzven, je prvi korak dobiti "1." največjo vrednost v podatkih, povezanih s skupino A s funkcijo LARGE:
LARGE(IF(group="A",score),F5)
V tem primeru je vrednost v F5 enaka 1, zato zahtevamo najvišji rezultat v skupini A. Ko se oceni funkcija IF, preskusi vsako vrednost v imenovani skupini obsegov . Imenovana ocena obsega je na voljo za vrednost_ef_true. To ustvari novo matriko, ki se vrne neposredno v funkcijo LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Upoštevajte, da so edini rezultati, ki preživijo filter, iz skupine A. LARGE nato vrne najvišji preostali rezultat, 93, neposredno funkciji MATCH kot iskalna vrednost. Zdaj lahko formulo poenostavimo na:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Zdaj lahko vidimo, da je funkcija MATCH konfigurirana, uporablja isto filtrirano matriko, kot smo jo videli zgoraj. Funkcija IF spet filtrira neželene vrednosti, del MATCH formule pa se razreši na:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Ker se 93 pojavi na tretjem mestu, MATCH vrne 3 neposredno v funkcijo INDEX:
=INDEX(name,3) // Hannah
Končno funkcija INDEX vrne ime v 3. vrstici "Hannah".
Z XLOOKUP
Za rešitev te težave je mogoče uporabiti tudi funkcijo XLOOKUP z enakim zgoraj pojasnjenim pristopom:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Kot zgoraj je LARGE konfiguriran za delo z matriko, ki jo filtrira IF, in vrne rezultat 93 v XLOOKUP kot vrednost iskanja:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Iskalno polje se ustvari tudi z uporabo IF kot filtra za ocene iz skupine A. Z vrnilno matriko, ki je navedena kot ime (B5: B16). XLOOKUP kot končni rezultat vrne "Hannah".
Opombe
- Če želite dobiti n-to vrednost z merili (tj. Omejite rezultate na skupino A ali B), boste morali formulo razširiti, da bo uporabila dodatno logiko.
- V programu Excel 365 je funkcija FILTER boljši način za dinamično uvrstitev zgornjih ali spodnjih rezultatov. Ta pristop bo samodejno obravnaval vezi.
