Bendroji formulė
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Santrauka
Norėdami gauti n-tosios didžiausios vertės pavadinimą, galite naudoti INDEX ir MATCH su LARGE funkcija. Parodytame pavyzdyje formulė langelyje H5 yra:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kur vardas (B5: B16) ir rezultatas (D5: D16) yra pavadinti diapazonai.
Paaiškinimas
Trumpai tariant, ši formulė naudoja funkciją LARGE, kad surastų n-tą pagal dydį duomenų rinkinio vertę. Kai turėsime šią vertę, mes prijungsime ją prie standartinės INDEX ir MATCH formulės, kad gautume susietą vardą. Kitaip tariant, mes naudojame devintą pagal dydį vertę, pvz., „Raktą“, norėdami gauti susietą informaciją.
LARGE funkcija yra paprastas būdas gauti n-tąją didžiausią diapazono vertę. Tiesiog nurodykite pirmo argumento (masyvo) diapazoną ir n reikšmę kaip antrąjį argumentą (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Dirbant iš vidaus, pirmas žingsnis yra gauti „1“ didžiausią duomenų reikšmę naudojant funkciją LARGE:
LARGE(score,F5) // returns 93
Šiuo atveju F5 reikšmė yra 1, todėl mes prašome 1-ojo pagal dydį balo (ty aukščiausio balo), kuris yra 93. Dabar formulę galime supaprastinti taip:
=INDEX(name,MATCH(93,score,0))
INDEX funkcijos viduje nustatoma funkcija MATCH, kad surastų 93 poziciją įvardytame diapazono bale (D5: D16):
MATCH(93,score,0) // returns 3
Kadangi 93 pasirodo 3-ioje eilutėje, MATCH grąžina 3 tiesiai į INDEX kaip eilutės numerį, o pavadinimas yra kaip masyvas:
=INDEX(name,3) // Hannah
Galiausiai funkcija INDEX grąžina pavadinimą trečioje eilutėje „Hannah“.
Atkreipkite dėmesį, kad mes renkame n reikšmes iš diapazono F5: F7, kad gautume 1, 2 ir 3 aukščiausią balą, kai formulė nukopijuojama.
Gauti grupę
Ta pati pagrindinė formulė veiks norint gauti bet kokią susietą informaciją. Norėdami gauti didžiausią reikšmę turinčią grupę, galite tiesiog pakeisti masyvą, pateiktą į INDEX, su pavadinta diapazono grupe :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Kai vertė F5 yra 1, LARGE gaus aukščiausią balą, o formulė grąžins „A“.
Pastaba: naudodami „Excel 365“, galite naudoti funkciją FILTRAS, kad dinamiškai išvardytumėte viršutinius arba apatinius rezultatus.
Su XLOOKUP
XLOOKUP funkcija taip pat gali būti naudojama norint grąžinti n-tosios didžiausios vertės pavadinimą:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE grąžina didžiausią vertę 93 tiesiai į XLOOKUP kaip paieškos vertę:
=XLOOKUP(93,score,name) // Hannah
Jei pavadinto diapazono balas (D5: D16) yra paieškos masyvas, o pavadinimas (B5: B16) yra grįžtamasis masyvas, XLOOKUP grąžina „Hannah“ kaip ir anksčiau.
Kaklaraiščių tvarkymas
Pasikartojančios skaitmeninių duomenų vertės sukurs „lygybę“. Pavyzdžiui, jei reitinguojamose reikšmėse yra vienodas skaičius, pavyzdžiui, jei pirmoji ir antra pagal dydį vertės yra vienodos, LARGE kiekvienai grąžins tą pačią vertę. Kai ši vertė bus perkelta į funkciją MATCH, MATCH grąžins pirmosios rungtynių poziciją, taigi pamatysite tą patį (pirmąjį) vardą.
Jei yra ryšių galimybė, galbūt norėsite įgyvendinti kažkokią kaklaraiščių nutraukimo strategiją. Vienas iš būdų yra sukurti naują pagalbinių vertybių stulpelį, kuris buvo pritaikytas pertraukti ryšius. Tada naudokitės pagalbinių stulpelių reikšmėmis, jei norite reitinguoti ir gauti informaciją. Tai daro ryšių nutraukimo logiką aiškią ir aiškią.
Kitas būdas yra nutraukti ryšius tik atsižvelgiant į padėtį (ty pirmasis taškas „laimi“). Štai formulė, kuri naudojasi tokiu požiūriu:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Čia mes naudojame MATCH, kad surastume skaičių 1, ir sukursime paieškos masyvą naudodami loginę logiką, kuri (1) lygina visus balus su LARGE grąžinta verte:
score=LARGE(score,F5)
ir (2) naudoja išplėstinį diapazono patikrinimą, jei vardas jau yra reitinguojamame sąraše:
COUNTIF(H$4:H4,name)=0
Kai vardas jau yra sąraše, logika jį „atšaukia“ ir suderina kitą (pasikartojančią) vertę. Atkreipkite dėmesį, kad besiplečiantis diapazonas prasideda ankstesnėje eilutėje, kad būtų išvengta apskritos nuorodos.
Šis požiūris veikia šiame pavyzdyje, nes vardų stulpelyje nėra pasikartojančių pavadinimų. Tačiau jei reitinguojamose reikšmėse pasikartoja vardai, požiūrį reikia koreguoti. Lengviausias sprendimas yra įsitikinti, kad vardai yra unikalūs.
Pastabos
- Norėdami gauti n-tosios vertės pavadinimą su kriterijais (ty apriboti rezultatus iki A arba B grupės), turėsite išplėsti formulę, kad galėtumėte naudoti papildomą logiką.
- „Excel 365“ funkcija FILTRAS yra geresnis būdas dinamiškai pateikti viršutinius arba apatinius rezultatus. Šis metodas automatiškai tvarkys kaklaraiščius.
