Üldine valem
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Kokkuvõte
Kriteeriumitega n-nda suurima väärtuse nime saamiseks võite kasutada funktsiooni INDEX ja MATCH, funktsiooni LARGE ja funktsiooni IF abil loodud filtrit. Näidatud näites on lahtris G5 valem, mis on kopeeritud allpool:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kus nimi (B5: B16), rühm (C5: C16) ja skoor (D5: D16) nimetatakse vahemikeks. Valem tagastab A-rühma 1., 2. ja 3. kõrgeima väärtusega seotud nime.
Märkus. See massiivivalem, mis tuleb sisestada juhtklahviga + tõstuklahv + sisestusklahv, välja arvatud Excelis 365.
Selgitus
Funktsioon SUUR on lihtne viis saada vahemikus suuruselt n-ö väärtus:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Selles näites saame kõrgeima punktisumma saamiseks kasutada funktsiooni SUUR, seejärel kasutada skoori nagu "võti", et leida seotud nimi INDEXi ja MATCH-iga. Pange tähele, et valime n väärtused vahemikust F5: F7, et saada 1., 2. ja 3. kõige kõrgem skoor.
Kuid antud juhul on see viga, et peame tegema vahet A- ja B-grupi skooridel. Teisisõnu peame rakendama kriteeriume. Me teeme seda funktsiooniga IF, mida kasutatakse väärtuste "filtreerimiseks", enne kui neid hinnatakse LARGE-ga. Üldise näitena saate vahemiku2 suurima väärtuse (st 1. väärtuse) saamiseks, kus vahemik 1 = "A", kasutada järgmist valemit:
LARGE(IF(range="A",range2),1)
Märkus: IF-i kasutamine sellisel viisil muudab selle massiivi valemiks.
Seestpoolt edasi töötades on esimene samm saada A-rühmaga seotud funktsiooni LARGE suurima väärtuse väärtus "1.":
LARGE(IF(group="A",score),F5)
Sellisel juhul on väärtuse F5 väärtus 1, seega küsime grupi A tipphinnet. Kui IF-funktsiooni hinnatakse, testib see kõiki nimetatud vahemiku rühma väärtusi . Nime vahemiku skoor esitatakse väärtusele_tões. See genereerib uue massiivi, mis tagastatakse otse funktsioonile SUUR:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Pange tähele, et ainsad skoorid, mis filtri üle elavad, kuuluvad rühmast A. LARGE tagastab kõrgeima järelejäänud skoori 93 otsingu väärtusena otse funktsioonile MATCH. Nüüd saame valemit lihtsustada:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nüüd näeme, et funktsioon MATCH on konfigureeritud sama filtreeritud massiivi abil, mida nägime ülal. Funktsioon IF filtreerib uuesti soovimatud väärtused ja valemi MATCH osa lahendab:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Kuna 93 on 3. positsioonil, tagastab MATCH 3 otse funktsioonile INDEX:
=INDEX(name,3) // Hannah
Lõpuks tagastab funktsioon INDEX kolmanda rea nime "Hannah".
XLOOKUPiga
Funktsiooni XLOOKUP saab kasutada ka selle probleemi lahendamiseks, kasutades ülalkirjeldatud lähenemist:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Nagu ülalpool, on LARGE konfigureeritud töötama IF-i poolt filtreeritud massiiviga ja tagastab otsingu väärtusena XLOOKUP-i tulemuse 93:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Otsimismassiiv luuakse ka kasutades IF-d filtrina rühmade A skoorides. Tagasimassiiv on esitatud nimena (B5: B16). XLOOKUP tagastab lõpptulemuseks "Hannah".
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.
