Bendroji formulė
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Santrauka
Norėdami gauti n-tosios pagal dydį reikšmės pavadinimą pagal kriterijus, galite naudoti INDEX ir MATCH, LARGE funkciją ir filtrą, sukurtą naudojant IF. Parodytame pavyzdyje G5 langelio formulė, nukopijuota žemiau, yra:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
kur pavadinimas (B5: B16), grupė (C5: C16) ir rezultatas (D5: D16) yra įvardijami diapazonai. Formulė grąžina pavadinimą, susietą su 1, 2 ir 3 didžiausiomis A grupės reikšmėmis.
Pastaba: Ši masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Paaiškinimas
LARGE funkcija yra paprastas būdas gauti n-ąją pagal dydį vertę:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Šiame pavyzdyje mes galime naudoti funkciją DIDELIS, kad gautume aukščiausią balą, tada naudokite balą kaip „raktą“, kad gautumėte susietą vardą su INDEX ir MATCH. Atkreipkite dėmesį, kad mes renkame n reikšmes iš diapazono F5: F7, kad gautume 1, 2 ir 3 aukščiausius balus.
Tačiau šiuo atveju yra tas posūkis, kad turime atskirti balus A grupėje ir B grupėje. Kitaip tariant, turime taikyti kriterijus. Tai darome su IF funkcija, kuri naudojama reikšmėms „filtruoti“, kol jos dar nėra įvertintos naudojant LARGE. Kaip bendrą pavyzdį, norėdami gauti didžiausią vertę (ty 1-ąją vertę) 2 diapazone, kur diapazonas 1 = „A“, galite naudoti tokią formulę:
LARGE(IF(range="A",range2),1)
Pastaba: naudojant IF tokiu būdu gaunama masyvo formulė.
Dirbant iš vidaus, pirmiausia reikia gauti „1“ didžiausią duomenų, susietų su A grupe, funkciją LARGE:
LARGE(IF(group="A",score),F5)
Šiuo atveju F5 reikšmė yra 1, todėl mes prašome gauti aukščiausią balą A grupėje. Įvertinus IF funkciją, ji išbando kiekvieną vertę nurodytoje diapazono grupėje . Pavadintas diapazono balas pateikiamas verte_if_true. Tai sukuria naują masyvą, kuris grąžinamas tiesiai į LARGE funkciją:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Atkreipkite dėmesį, kad vieninteliai balai, kurie išgyvena filtrą, yra iš A grupės. DIDELIS grąžina didžiausią likusią balą 93 tiesiai į funkciją MATCH kaip paieškos vertę. Dabar formulę galime supaprastinti taip:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Dabar matome, kad funkcija MATCH sukonfigūruota naudojant tą patį filtruotą masyvą, kurį matėme aukščiau. IF funkcija vėl filtruoja nepageidaujamas reikšmes, o formulės MATCH dalis pasirenka:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Kadangi 93 yra 3-oje pozicijoje, MATCH grąžina 3 tiesiai į INDEX funkciją:
=INDEX(name,3) // Hannah
Galiausiai funkcija INDEX grąžina pavadinimą trečioje eilutėje „Hannah“.
Su XLOOKUP
XLOOKUP funkcija taip pat gali būti naudojama šiai problemai išspręsti naudojant tą patį metodą, kuris buvo paaiškintas aukščiau:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Kaip aukščiau, LARGE yra sukonfigūruotas dirbti su masyvu, filtruotu pagal IF, ir grąžina rezultatą 93 į XLOOKUP kaip paieškos vertę:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Paieškos masyvas taip pat sukurtas naudojant IF kaip A grupės balų filtrą. Pateikiant grįžtamąjį masyvą kaip pavadinimą (B5: B16). XLOOKUP grąžina „Hannah“ kaip galutinį rezultatą.
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.
