Generička formula
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Sažetak
Da biste dobili ime n-te najveće vrijednosti, možete koristiti INDEX i MATCH s funkcijom LARGE. U prikazanom primjeru formula u stanici H5 je:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
gdje su naziv (B5: B16) i rezultat (D5: D16) imenovani rasponi.
Obrazloženje
Ukratko, ova formula koristi funkciju LARGE za pronalaženje n-te najveće vrijednosti u skupu podataka. Jednom kad imamo tu vrijednost, uključujemo je u standardnu formulu INDEX i MATCH za dohvaćanje pridruženog imena. Drugim riječima, koristimo n-tu najveću vrijednost poput "ključa" za dohvaćanje povezanih podataka.
Funkcija LARGE jednostavan je način za dobivanje n-te najveće vrijednosti u rasponu. Jednostavno navedite raspon za prvi argument (niz) i vrijednost za n kao drugi argument (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Radeći iznutra prema van, prvi korak je dobiti "1." najveću vrijednost u podacima s funkcijom LARGE:
LARGE(score,F5) // returns 93
U ovom slučaju, vrijednost u F5 je 1, pa tražimo 1. najveći rezultat (tj. Najbolji rezultat), koji je 93. Sada formulu možemo pojednostaviti na:
=INDEX(name,MATCH(93,score,0))
Unutar funkcije INDEX, funkcija MATCH postavljena je za lociranje položaja 93 u imenovanom rezultatu raspona (D5: D16):
MATCH(93,score,0) // returns 3
Budući da se 93 pojavljuje u 3. retku, MATCH vraća 3 izravno u INDEX kao broj retka, s imenom kao nizom:
=INDEX(name,3) // Hannah
Konačno, funkcija INDEX vraća ime u 3. redu "Hannah".
Primijetimo da prikupljamo vrijednosti za n iz raspona F5: F7, kako bismo dobili 1., 2. i 3. najvišu ocjenu dok se formula kopira.
Dohvati grupu
Ista osnovna formula funkcionirat će za dohvaćanje svih povezanih podataka. Da biste dobili grupu za najveće vrijednosti, možete jednostavno promijeniti niz isporučen u INDEX s imenovanom grupom raspona :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
S vrijednosti 1 u F5, LARGE će dobiti najveći rezultat, a formula će vratiti "A".
Napomena: s programom Excel 365 možete koristiti funkciju FILTER da biste dinamički popisali gornje ili donje rezultate.
Uz XLOOKUP
Funkcija XLOOKUP također se može koristiti za vraćanje imena n-te najveće vrijednosti poput ove:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE vraća najveću vrijednost, 93, izravno u XLOOKUP kao vrijednost pretraživanja:
=XLOOKUP(93,score,name) // Hannah
S imenovanom ocjenom raspona (D5: D16) kao nizom za traženje i imenom (B5: B16) kao nizom za povrat, XLOOKUP vraća "Hannah" kao i prije.
Rukovanje vezama
Dvostruke vrijednosti u numeričkim podacima stvorit će "vezu". Ako se, na primjer, dogodi jednaka vrijednost u rangiranim vrijednostima, ako su prva i druga najveće vrijednosti iste, LARGE će vratiti istu vrijednost za svaku. Kada se ova vrijednost prenese u funkciju MATCH, MATCH će vratiti položaj prvog podudaranja, pa ćete vidjeti isto (prvo) ime.
Ako postoji mogućnost veza, možda ćete htjeti provesti neku vrstu strategije prekida veze. Jedan od pristupa je stvaranje novog pomoćnog stupca vrijednosti koji su prilagođeni prekidu veza. Zatim upotrijebite vrijednosti pomoćnih stupaca za rangiranje i dohvaćanje podataka. To logiku koja se koristi za prekid veza čini jasnom i eksplicitnom.
Drugi pristup je prekidanje veza na temelju samo pozicije (tj. Prva nerešena pobjeda). Evo formule koja zauzima taj pristup:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim u programu Excel 365.
Ovdje koristimo MATCH za pronalaženje broja 1 i konstruiramo niz pretraživanja koristeći logičku logiku koja (1) uspoređuje sve rezultate s vrijednošću vraćenom LARGE:
score=LARGE(score,F5)
i (2) koristi provjeru raspona koji se širi ako je naziv već na rang listi:
COUNTIF(H$4:H4,name)=0
Kada se ime već nalazi na popisu, logika ga "poništava" i podudara se sljedeća (duplikata) vrijednost. Primijetite da opseg širenja započinje na prethodnom retku kako bi se izbjegla kružna referenca.
Ovaj pristup djeluje u ovom primjeru jer u stupcu imena nema dvostrukih imena. Međutim, ako se duplicirani nazivi pojave u rangiranim vrijednostima, pristup treba prilagoditi. Najlakše je rješenje osigurati da su imena jedinstvena.
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.
