Kokkuvõte
Numbriloendi kuvamiseks, mis on järjestatud numbrilise väärtuse järgi, saate funktsiooni TEXT abil kasutada valemite komplekti, mis põhineb LARGE, INDEX, MATCH. Näidatud näites on valem G5-s järgmine:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Ja valem G10-s on:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
kus kliendi (B5: B17) kuupäev (C5: C17) ja summa (C5: C17) nimetatakse vahemikeks.
Märkus: need on massiivivalemid ja need tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Selgitus
See näide on selguse huvides koostatud kahest osast: (1) valem iga kuu kolme kõige suurema summa määramiseks ja (2) valem kliendinime hankimiseks kõigi kolme kuu kõige suurema summa kohta.
Pange tähele, et lähteandmetes puudub tegelik auaste. Selle asemel kasutame funktsiooni SUUR, et töötada otse summadega. Teine lähenemisviis oleks lisada auaste algandmetele funktsiooniga RANK ja kasutada auaste väärtust klientide nimede hankimiseks.
1. osa: hankige iga kuu top 3 summat
Iga nädala kolme kõige suurema summa hankimiseks on valemis G5 järgmine:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Seestpoolt edasi töötades kasutame kõigepealt funktsiooni TEXT, et saada kuupalade nimed iga vahemiku jaoks määratud kuupäeva jaoks :
TEXT(date,"mmmm") // get month names
Kohandatud numbrivorming "mmmm" tagastab nimede vahemiku kuupäeva iga nime jaoks stringi nagu "aprill", "mai", "juuni" . Tulemuseks on selline kuude nimede massiiv:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funktsioon TEXT edastab selle massiivi funktsioonile IF, mis on konfigureeritud filtreerima antud kuu kuupäevi, testides kuu nime G4 väärtuse järgi (segatud viide, nii et valemit saab kopeerida allapoole ja teisele):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Alles aprillis olevad summad jäävad ellu ja jõuavad IF-i kaudu; kõik muud väärtused on FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Lõpuks, funktsioon LARGE kasutab väärtust F5-s (ka segaviide), et tagastada allesjäänud n-nda väärtus. Lahtris G5 tagastab LARGE suurima väärtuse "1." väärtuse 18 500. Kui valem kopeeritakse allapoole ja kogu tabelisse, tagastab funktsioon SUUR kolm kolme kõige paremat summat.
Nüüd, kui teame iga kuu 3 parimat väärtust, saame kasutada seda teavet nagu "võti", et hankida igaühe jaoks kliendi nimi.
2. osa: hankige klientide nimed
Märkus. See on näide INDEXi ja MATCHi kasutamisest mitme kriteeriumiga. Kui see kontseptsioon on teie jaoks uus, on siin üks näide.
G5: I7 kolme peamise väärtusega seotud nime hankimiseks kasutame INDEX ja MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Märkus: see on massiivivalem ja see tuleb sisestada juhtklahviga + shift + enter, välja arvatud Excelis 365.
Seestpoolt töötades on funktsioon MATCH konfigureeritud kasutama Boole'i loogikat järgmiselt:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Otsingu väärtus on 1 ja otsingu massiiv on koostatud selle avaldisega:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Otsingumassiivi loov avaldis kasutab Boole'i loogikat summade "välja filtreerimiseks", mis (1) pole aprillis ja (2) ei ole G5 väärtus (18 500). Tulemuseks on massiivid 1s ja 0s:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Otsingu väärtusega 1 ja null vaste tüübi jaoks (täpse vaste sundimiseks) tagastab MATCH 3 otse funktsioonile INDEX:
=INDEX(client,3) // returns "Janus"
INDEX tagastab nimetatud vahemiku kliendi kolmanda väärtuse "Janus".
Kui valem kopeeritakse allapoole ja kogu tabelisse, tagastab see iga kolme kuu jooksul 3 parimat klienti.
