Yleinen kaava
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Yhteenveto
Saadaksesi n. Suurimman arvon nimen, voit käyttää INDEX- ja MATCH-funktioita LARGE-toiminnolla. Esitetyssä esimerkissä solun H5 kaava on:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
missä nimi (B5: B16) ja pisteet (D5: D16) on nimetty alueiksi.
Selitys
Lyhyesti sanottuna tämä kaava käyttää LARGE-funktiota etsimään tietosarjan n: nneksi suurin arvo. Kun meillä on tämä arvo, liitämme sen tavalliseen INDEX- ja MATCH-kaavaan saadaksesi liittyvän nimen. Toisin sanoen käytämme n. Suurinta arvoa, kuten "avain", liittyvien tietojen noutamiseen.
LARGE-toiminto on yksinkertainen tapa saada alueen n. Suurin arvo. Anna yksinkertaisesti alue ensimmäiselle argumentille (taulukko) ja arvoksi n toiseksi argumentiksi (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Työskentelemällä sisältä ulospäin, ensimmäinen vaihe on saada datan "suurin" arvo LARGE-toiminnolla:
LARGE(score,F5) // returns 93
Tässä tapauksessa arvo F5: ssä on 1, joten pyydämme 1. suurinta pistemäärää (eli huippupistettä), joka on 93. Voimme nyt yksinkertaistaa kaavaa seuraavasti:
=INDEX(name,MATCH(93,score,0))
Sisällä INDEX toiminto, MATCH toiminto on asetettu paikantaa aseman 93 on nimetty alue pisteet (D5: D16):
MATCH(93,score,0) // returns 3
Koska 93 ilmestyy 3. riville, MATCH palauttaa 3 suoraan INDEX: iin rivinumerona, nimenä taulukko:
=INDEX(name,3) // Hannah
Lopuksi INDEX-funktio palauttaa kolmannen rivin nimen "Hannah".
Huomaa, että haemme n : n arvot alueelta F5: F7 saadaksemme 1., 2. ja 3. korkeimman pistemäärän kaavan kopioinnin aikana.
Hae ryhmä
Sama peruskaava toimii kaikkien liittyvien tietojen noutamiseksi. Saada ryhmä suurimman arvot, voit muuttaa array toimitettu indeksi nimetty alue ryhmä :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Kun arvo 1 on F5, LARGE saa korkeimman pistemäärän ja kaava palauttaa "A".
Huomautus: Excel 365: ssä voit käyttää SUODATIN-toimintoa luetteloiden ylä- tai alaosaa dynaamisesti.
XLOOKUP: n kanssa
XLOOKUP-toimintoa voidaan käyttää myös palauttamaan n: nneksi suurimman arvon nimi näin:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE palauttaa suurimman arvon, 93, suoraan XLOOKUP: lle hakuhintana:
=XLOOKUP(93,score,name) // Hannah
Kun nimetyn alueen pisteet (D5: D16) on hakutaulukko ja nimi (B5: B16) paluuryhmänä, XLOOKUP palauttaa "Hannah" kuten aiemmin.
Solmioiden käsittely
Numeeristen tietojen päällekkäiset arvot luovat "tasan". Jos luokitelluissa arvoissa esiintyy tasa-arvoa, esimerkiksi jos ensimmäinen ja toinen suurin arvo ovat samat, LARGE palauttaa saman arvon kullekin. Kun tämä arvo välitetään MATCH-funktiolle, MATCH palauttaa ensimmäisen ottelun sijainnin, joten näet saman (etunimen) nimen.
Jos on mahdollista solmia, kannattaa ehkä toteuttaa jonkinlainen solmimista koskeva strategia. Yksi lähestymistapa on luoda uusi apusarake arvoja, jotka on mukautettu rikkomaan siteitä. Käytä sitten auttajasarakkeen arvoja tietojen järjestämiseen ja noutamiseen. Tämä tekee siteiden katkaisemiseen käytetyn logiikan selkeäksi ja selkeäksi.
Toinen lähestymistapa on katkaista siteet vain sijainnin perusteella (eli ensimmäinen tie "voittaa"). Tässä on kaava, joka käyttää tätä lähestymistapaa:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Tässä käytämme MATCH: ta numeron 1 etsimiseen ja rakennamme hakutaulukon loogisen logiikan avulla, joka (1) vertaa kaikkia pisteitä LARGE: n palauttamaan arvoon:
score=LARGE(score,F5)
ja (2) käyttää laajenevaa aluetarkistusta, jos nimi on jo sijoitusluettelossa:
COUNTIF(H$4:H4,name)=0
Kun nimi on jo luettelossa, logiikka "peruu" sen ja seuraava (kaksoiskappale) arvo sovitetaan yhteen. Huomaa, että laajeneva alue alkaa edellisestä rivistä, jotta vältetään pyöreä viittaus.
Tämä lähestymistapa toimii tässä esimerkissä, koska nimisarakkeessa ei ole päällekkäisiä nimiä. Kuitenkin, jos päällekkäisiä nimiä esiintyy paremmuusjärjestyksessä, lähestymistapaa on mukautettava. Helpoin ratkaisu on varmistaa, että nimet ovat yksilöllisiä.
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.
