Generička formula
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Sažetak
Da biste dobili naziv n-te najveće vrijednosti s kriterijima, možete upotrijebiti INDEX i MATCH, funkciju LARGE i filtar stvoren pomoću funkcije IF. U prikazanom primjeru, formula u kopiji ćelije G5 je:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
gdje su imena (B5: B16), skupina (C5: C16) i rezultat (D5: D16) imenovani rasponi. Formula vraća ime povezano s 1., 2. i 3. najvišom vrijednošću u skupini A.
Napomena: Ovo je formula niza koja se mora unijeti sa control + shift + enter, osim u programu Excel 365.
Obrazloženje
Funkcija LARGE jednostavan je način za dobivanje n-te najveće vrijednosti u rasponu:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
U ovom primjeru možemo koristiti funkciju LARGE da bismo dobili najvišu ocjenu, a zatim upotrijebiti ocjenu poput "ključa" za dohvaćanje povezanog imena s INDEX i MATCH. Primijetimo da prikupljamo vrijednosti za n iz raspona F5: F7, kako bismo dobili 1., 2. i 3. najvišu ocjenu.
Zaokret u ovom slučaju je da moramo razlikovati rezultate u skupini A i skupini B. Drugim riječima, moramo primijeniti kriterije. To radimo s funkcijom IF koja se koristi za "filtriranje" vrijednosti prije nego što se procijene s LARGE. Kao generički primjer, da biste dobili najveću vrijednost (tj. 1. vrijednost) u rasponu2 gdje je raspon 1 = "A", možete koristiti formulu poput ove:
LARGE(IF(range="A",range2),1)
Napomena: Korištenje IF na ovaj način čini ovu formulu niza.
Radeći iznutra prema van, prvi je korak dobiti "1." najveću vrijednost u podacima povezanim s Grupom A s funkcijom LARGE:
LARGE(IF(group="A",score),F5)
U ovom slučaju, vrijednost u F5 je 1, pa tražimo najbolji rezultat u skupini A. Kada se procijeni funkcija IF, ona testira svaku vrijednost u imenovanoj skupini raspona . Imenovani rezultat raspona predviđen je za value_if_true. Ovo generira novi niz koji se vraća izravno u funkciju LARGE:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Primijetite da su jedine ocjene koje su preživjele filtar iz skupine A. LARGE tada vraća najvišu preostalu ocjenu, 93, izravno u funkciju MATCH kao vrijednost pretraživanja. Sada formulu možemo pojednostaviti na:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Sada možemo vidjeti da je funkcija MATCH konfigurirana koristi isti filtrirani niz koji smo vidjeli gore. Funkcija IF ponovno filtrira neželjene vrijednosti, a dio MATCH formule rješava na:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Budući da se 93 pojavljuje na trećem mjestu, MATCH vraća 3 izravno u funkciju INDEX:
=INDEX(name,3) // Hannah
Konačno, funkcija INDEX vraća ime u 3. redu "Hannah".
Uz XLOOKUP
Funkcija XLOOKUP također se može koristiti za rješavanje ovog problema, koristeći isti gore objašnjeni pristup:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Kao i gore, LARGE je konfiguriran za rad s nizom filtriranim od strane IF i vraća rezultat 93 u XLOOKUP kao vrijednost pretraživanja:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Niz za pretraživanje također se stvara korištenjem IF kao filtra za ocjene iz skupine A. S povratnim nizom navedenim kao ime (B5: B16). XLOOKUP kao konačni rezultat vraća "Hannah".
Bilješke
- Da biste dobili naziv n-te vrijednosti s kriterijima (tj. Ograničite rezultate na skupinu A ili B), morat ćete proširiti formulu da biste koristili dodatnu logiku.
- U programu Excel 365 funkcija FILTER bolji je način za dinamičko popisivanje gornjih ili donjih rezultata. Ovaj će se pristup automatski nositi s vezama.
