Yhteenveto
Numeroluettelon mukaan luokiteltujen nimien luettelon näyttämiseksi voit käyttää TEXT-funktion avulla kaavasarjaa, joka perustuu LARGE, INDEX, MATCH. Esitetyssä esimerkissä kaava G5: ssä on:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Ja G10: n kaava on:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
missä asiakas (B5: B17) päivämäärä (C5: C17) ja summa (C5: C17) on nimetty alueiksi.
Huomaa: nämä ovat matriisikaavoja ja ne on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Selitys
Tämä esimerkki on jaettu selkeyden vuoksi kahteen osaan: (1) kaava, jolla määritetään kolmen kuukauden parhaat kolme määrää, ja (2) kaava, jolla haetaan asiakkaan nimi kullekin kolmelle parhaimmalle summalle.
Huomaa, että lähdetiedoissa ei ole todellista sijoitusta. Sen sijaan käytämme LARGE-toimintoa työskennellessäsi suoraan summien kanssa. Toinen lähestymistapa olisi lisätä sijoitus lähdetietoihin RANK-toiminnolla ja käyttää sijoitusarvoa asiakkaiden nimien hakemiseen.
Osa 1: Hae 3 parasta summaa kuukaudessa
Voit hakea kolmen viikon 3 parasta summaa kaavalla G5 seuraavasti:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Työskentely sisältä ulos, me ensin käyttää tekstitoiminto saada kuukausien nimiä jokaiselle päivämäärä nimetty alue päivämäärä :
TEXT(date,"mmmm") // get month names
Mukautettu lukumuoto "mmmm" palauttaa merkkijonon, kuten "huhtikuu", "toukokuu", "kesäkuu" jokaiselle nimetyn alueen päivämäärän nimelle . Tuloksena on joukko tällaisia kuukausien nimiä:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
TEXT-funktio toimittaa tämän taulukon IF-funktiolle, joka on määritetty suodattamaan päivämäärät tietyllä kuukaudella testaamalla kuukauden nimeä G4: n arvoon nähden (sekaviite, joten kaava voidaan kopioida alaspäin).
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Vain huhtikuussa elossa olevat määrät selviävät ja menevät läpi IF: n kautta; kaikki muut arvot ovat EPÄTOSI:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Lopuksi LARGE-funktio käyttää F5: n arvoa (myös sekaviittausta) palauttamaan jäljellä olevan "suurimman" arvon. Solussa G5 LARGE palauttaa 18500, "1." suurimman arvon. Kun kaava kopioidaan alaspäin ja taulukon yli, LARGE-funktio palauttaa kolmen ensimmäisen kuukauden kolme parasta summaa.
Nyt kun tiedämme kolmen kuukauden parhaat kolme arvoa, voimme käyttää näitä tietoja "avaimen" tavoin hakemaan kunkin asiakkaan nimen.
Osa 2: Hae asiakkaiden nimet
Huomaa: Tämä on esimerkki INDEXin ja MATCHin käytöstä useilla ehdoilla. Jos tämä käsite on sinulle uusi, tässä on perusesimerkki.
G5: I7: n kolmeen ensimmäiseen arvoon liittyvän nimen hakemiseksi käytämme INDEX ja MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
Työskentely sisältä ulospäin MATCH-toiminto on määritetty käyttämään loogista logiikkaa seuraavasti:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Hakuarvo on 1, ja hakutaulukko muodostetaan tällä lausekkeella:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Hakutaulukon luova lauseke käyttää Boolen logiikkaa "suodattamaan" määrät, jotka ovat (1) ei huhtikuussa ja (2) eivät G5: n arvoa (18 500). Tuloksena on taulukko 1s ja 0s kuten tämä:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Kun hakuarvo on 1 ja nolla hakutyypille (tarkan haun pakottamiseksi), MATCH palauttaa 3 suoraan INDEX-funktiolle:
=INDEX(client,3) // returns "Janus"
INDEX palauttaa nimetyn alueen asiakkaan kolmannen arvon "Janus".
Kun kaava kopioidaan alaspäin ja taulukon poikki, se palauttaa kolme parasta asiakasta kullakin kolmella kuukaudella.
