Povzetek
Za prikaz seznama imen, razvrščenih po številski vrednosti, lahko s pomočjo funkcije TEXT uporabite nabor formul, ki temelji na LARGE, INDEX, MATCH. V prikazanem primeru je formula v G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
In formula v G10 je:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
kjer sta datum (C5: C17) in znesek (C5: C17) odjemalca (B5: B17) imenovana obsega.
Opomba: to so formule matrike in jih je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Pojasnilo
Ta primer je zaradi jasnosti sestavljen iz dveh delov: (1) formula za določitev top 3 zneskov za vsak mesec in (2) formula za pridobivanje imena stranke za vsakega od 3 najboljših mesečnih zneskov.
Upoštevajte, da v izvornih podatkih ni dejanskega ranga. Namesto tega uporabljamo funkcijo LARGE za neposredno delo z zneski. Drug pristop bi bil dodati rang izvornim podatkom s funkcijo RANK in uporabiti vrednost ranga za pridobivanje imen odjemalcev.
1. del: vsak mesec pridobite top 3 zneske
Za pridobitev top 3 zneskov za vsak teden je formula v G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Če delamo od znotraj navzven, najprej uporabimo funkcijo TEXT, da dobimo imena mesecev za vsak datum v imenovanem datumu obsega :
TEXT(date,"mmmm") // get month names
Oblika števil po meri "mmmm" bo vrnila niz, kot so "April", "May", "June" za vsako ime v imenovanem datumu obsega . Rezultat je vrsta mesečnih imen, kot je ta:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcija TEXT dostavi to matriko funkciji IF, ki je konfigurirana za filtriranje datumov v določenem mesecu s preizkušanjem imena meseca glede na vrednost v G4 (mešani sklic, tako da je formulo mogoče kopirati navzdol in navzgor):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Preživijo se samo zneski v aprilu in se prebijejo v primeru IF; vse druge vrednosti so FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Na koncu funkcija LARGE uporabi vrednost v F5 (tudi mešani sklic), da vrne "n-to" največjo vrednost, ki ostane. V celici G5 vrne LARGE 18.500, kar je "1." največja vrednost. Ko se formula kopira navzdol in čez tabelo, funkcija LARGE vrne zgornje 3 zneske v vsakem od treh mesecev.
Zdaj, ko poznamo 3 najvišje vrednosti v vsakem mesecu, lahko te podatke uporabimo kot "ključ" za pridobivanje imena odjemalca za vsakega.
2. del: pridobite imena odjemalcev
Opomba: To je primer uporabe INDEX in MATCH z več merili. Če je ta koncept za vas nov, je tukaj osnovni primer.
Če želite pridobiti ime, povezano z zgornjimi tremi vrednostmi v G5: I7, uporabimo INDEX in MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Funkcija MATCH, ki deluje od znotraj navzven, je konfigurirana tako, da uporablja logično logiko, kot je ta:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Iskalna vrednost je 1 in iskalna matrika je sestavljena s tem izrazom:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Izraz, ki ustvari iskalno polje, uporablja logično logiko za "filtriranje" zneskov, ki (1) niso v aprilu in (2) niso vrednosti v G5 (18.500). Rezultat je matrika 1s in 0s, kot je ta:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Z vrednostjo iskanja 1 in nič za vrsto ujemanja (za vsiljevanje natančnega ujemanja) MATCH vrne 3 neposredno v funkcijo INDEX:
=INDEX(client,3) // returns "Janus"
INDEX vrne tretjo vrednost v imenovanem odjemalcu obsega, "Janus".
Ko se formula kopira navzdol in čez tabelo, vrne 3 najboljše stranke v vsakem od treh mesecev.
