Üldine valem
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Kokkuvõte
N-nda suurima väärtuse nime saamiseks võite kasutada funktsiooni LARGE INDEX ja MATCH. Näidatud näites on lahtris H5 valem:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
kus nimi (B5: B16) ja skoor (D5: D16) nimetatakse vahemikeks.
Selgitus
Lühidalt, see valem kasutab funktsiooni LARGE, et leida andmekogumis n-ö suuruselt väärtus. Kui see väärtus on käes, ühendame selle standardnimega INDEX ja MATCH seotud nime hankimiseks. Teisisõnu, me kasutame seotud teabe hankimiseks n-ö suurimat väärtust nagu "võti".
Funktsioon SUUR on sirgjooneline viis saada vahemikus suuruselt n-ö väärtus. Esitage lihtsalt esimese argumendi (massiivi) vahemik ja teise argumendina (k) väärtus n:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Seestpoolt edasi töötades on esimene samm saada LARGE funktsiooniga andmete suurim väärtus "1.":
LARGE(score,F5) // returns 93
Sel juhul on väärtuse F5 väärtus 1, seega palume saada suuruselt 1. skoori (st parima punktisumma), mis on 93. Nüüd saame valemit lihtsustada järgmiselt:
=INDEX(name,MATCH(93,score,0))
Funktsiooni INDEX sees on seatud funktsioon MATCH, et leida 93 asukohta nimetatud vahemiku skooris (D5: D16):
MATCH(93,score,0) // returns 3
Kuna kolmandas reas ilmub 93, tagastab MATCH 3 otse reale INDEX, kus nimi on massiiv:
=INDEX(name,3) // Hannah
Lõpuks tagastab funktsioon INDEX kolmanda rea nime "Hannah".
Pange tähele, et valime n väärtused vahemikust F5: F7, et saada valemi kopeerimisel 1., 2. ja 3. kõige suurem tulemus.
Rühma toomine
Sama põhivalem töötab mis tahes seotud teabe hankimiseks. Suurimate väärtuste rühma saamiseks saate lihtsalt muuta INDEXile esitatud massiivi nimega vahemiku rühmaga :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Kui väärtus F5 on 1, saab LARGE kõrgeima punktisumma ja valem tagastab "A".
Märkus. Exceli 365 abil saate funktsioonide FILTER abil dünaamiliselt loetleda ülemise või alumise tulemuse.
XLOOKUPiga
Funktsiooni XLOOKUP saab kasutada ka suuruselt n-nda väärtuse nime tagastamiseks:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE tagastab suurima väärtuse 93 otse otsingu väärtusena XLOOKUP:
=XLOOKUP(93,score,name) // Hannah
Kui otsitud massiiviks on nimetatud vahemiku skoor (D5: D16) ja tagasimassiiviks nimi (B5: B16), tagastab XLOOKUP "Hannah" nagu varem.
Lipsude käsitlemine
Numbriliste andmete duplikaatväärtused loovad võrdse tulemuse. Kui järjestatavate väärtuste korral toimub võrdsus, näiteks kui esimene ja teine suurim väärtus on sama, tagastab LARGE igaühe jaoks sama väärtuse. Kui see väärtus edastatakse funktsioonile MATCH, tagastab MATCH esimese vaste positsiooni, nii et näete sama (eesnime) nime.
Kui on olemas sidemete võimalus, võiksite rakendada mingisugust sidemete katkestamise strateegiat. Üks lähenemisviis on luua uus abiväärtus, mis on kohandatud sidemete purustamiseks. Seejärel kasutage teabe järjestamiseks ja hankimiseks abistaja veeru väärtusi. See muudab sidemete katkestamiseks kasutatava loogika selgeks ja selgesõnaliseks.
Teine lähenemisviis on sidemete katkestamine ainult positsiooni põhjal (st esimene võit "võidab"). Siin on selline lähenemisviis:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Siin kasutame numbri 1 leidmiseks vastet MATCH ja konstrueerime otsemassiivi loogika abil, mis (1) võrdleb kõiki hindeid LARGE tagastatud väärtusega:
score=LARGE(score,F5)
ja (2) kasutab laienevat vahemiku kontrolli, kui nimi on juba järjestatud loendis:
COUNTIF(H$4:H4,name)=0
Kui nimi on juba loendis, siis loogika järgi "tühistatakse" see ja järgmine (duplikaat) väärtus sobitatakse. Pange tähele, et laienev vahemik algab eelmisest reast, et vältida ringikujulist viidet.
See lähenemine töötab selles näites, kuna nimeveerus pole korduvaid nimesid. Kui aga järjestatud nimedes esinevad järjestatud väärtused, tuleb lähenemist kohandada. Lihtsaim lahendus on veenduda, et nimed on unikaalsed.
Märkused
- Kriteeriumitega n-nda väärtuse nime saamiseks (st piirake tulemusi A- või B-rühmaga) peate valemit täiendava loogika kasutamiseks laiendama.
- Excel 365-s on funktsioon FILTER parem viis ülemise või alumise tulemuse dünaamiliseks loetlemiseks. See lähenemisviis haldab sidemeid automaatselt.
