Yleinen kaava
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Yhteenveto
Voit saada n: nneksi suurimman arvon nimen kriteereillä käyttämällä hakemistoa ja vastaavuutta, LARGE-funktiota ja IF-funktiolla luotua suodatinta. Esitetyssä esimerkissä solun G5 kaava on kopioitu alaspäin:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
missä nimi (B5: B16), ryhmä (C5: C16) ja pisteet (D5: D16) on nimetty alueiksi. Kaava palauttaa nimen, joka liittyy ryhmän A 1., 2. ja 3. korkeimpaan arvoon.
Huomaa: Tämä matriisikaava, joka on syötettävä näppäimillä + + Vaihto + Enter, paitsi Excel 365: ssä.
Selitys
LARGE-toiminto on helppo tapa saada alueen n. Suurin arvo:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Tässä esimerkissä voimme käyttää LARGE-toimintoa saadaksesi korkeimman pistemäärän, ja sitten käyttää pisteitä "avaimen" avulla hakeaksesi siihen liittyvän nimen hakemistolla INDEX ja MATCH. Huomaa, että haemme n : n arvot alueelta F5: F7 saadaksesi 1., 2. ja 3. korkeimman pistemäärän.
Tässä tapauksessa käänne on kuitenkin se, että meidän on tehtävä ero ryhmän A ja ryhmän pisteiden välillä. Toisin sanoen meidän on sovellettava kriteerejä. Teemme tämän IF-toiminnolla, jota käytetään arvojen "suodattamiseen" ennen kuin ne arvioidaan LARGE-arvolla. Yleisenä esimerkkinä saat suurimman arvon (eli 1. arvon) alueelta 2, jossa alue 1 = "A", käyttämällä seuraavaa kaavaa:
LARGE(IF(range="A",range2),1)
Huomaa: jos IF: n käyttäminen tällä tavalla tekee tästä taulukon kaavan.
Työskentelemällä sisältä ulospäin, ensimmäinen vaihe on saada "1." suurin arvo ryhmään A liittyvissä tiedoissa LARGE-toiminnolla:
LARGE(IF(group="A",score),F5)
Tässä tapauksessa arvo F5 on 1, niin vaadimme alkuun pisteet ryhmään A. Kun IF-toiminto on arvioitu, se testaa kunkin arvo on nimetty alue ryhmä . Nimetty alue- pisteet annetaan arvolle_tosi_tosi. Tämä luo uuden taulukon, joka palautetaan suoraan LARGE-toimintoon:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
Huomaa, että ainoat suodattimen selviytyneet pisteet ovat ryhmästä A. SUURI palauttaa korkeimman jäljellä olevan pistemäärän 93 suoraan MATCH-funktiolle hakuarvona. Voimme nyt yksinkertaistaa kaavaa seuraavasti:
=INDEX(name,MATCH(93,IF(group="A",score),0))
Nyt voimme nähdä, että MATCH-toiminto on määritetty käyttämällä samaa suodatettua taulukkoa, jonka näimme yllä. IF-toiminto suodattaa uudelleen ei-toivotut arvot, ja kaavan MATCH-osa ratkaisee:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
Koska 93 näkyy kolmannessa paikassa, MATCH palauttaa 3 suoraan INDEX-funktiolle:
=INDEX(name,3) // Hannah
Lopuksi INDEX-funktio palauttaa kolmannen rivin nimen "Hannah".
XLOOKUP: n kanssa
XLOOKUP-toimintoa voidaan käyttää myös ongelman ratkaisemiseen samalla tavalla kuin edellä on selitetty:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
Kuten yllä, LARGE on määritetty toimimaan IF: n suodattaman taulukon kanssa ja palauttaa tuloksen 93 XLOOKUP: lle hakuarvoksi:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
Hakutaulukko luodaan myös käyttämällä IF: tä suodattimena ryhmän A pisteistä. Palautusryhmänä on nimi (B5: B16). XLOOKUP palauttaa "Hannah" lopputulokseksi.
Huomautuksia
- Saadaksesi n: nnen arvon nimen kriteereillä (eli rajoita tulokset ryhmään A tai B), sinun on laajennettava kaavaa lisälogiikan käyttämiseksi.
- Excel 365: ssä SUODATIN-toiminto on parempi tapa luetteloida ylä- ja alaosien tulokset dynaamisesti. Tämä lähestymistapa käsittelee automaattisesti siteet.
