Összegzés
A névlista számérték szerinti rangsorolásához a SZÖVEG függvény segítségével képletkészletet használhat, amelynek alapja a LARGE, INDEX, MATCH. A bemutatott példában a G5 képlete a következő:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
És a G10 képlete a következő:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
ahol az ügyfél (B5: B17) dátumot (C5: C17) és az összeget (C5: C17) nevezik meg.
Megjegyzés: ezek tömbképletek, amelyeket a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Magyarázat
Ez a példa az egyértelműség kedvéért két részből áll: (1) képlet az egyes hónapok három legfontosabb összegének meghatározásához, és (2) képlet az ügyfél nevének lekérdezéséhez a legfontosabb 3 havi összeghez.
Vegye figyelembe, hogy a forrásadatokban nincs tényleges rangsor. Ehelyett a NAGY függvényt használjuk, hogy közvetlenül az összegekkel dolgozzunk. Egy másik megközelítés az lenne, ha rangot adnának a forrásadatokhoz a RANK funkcióval, és a rangértéket használnák az ügyfélnevek lekérésére.
1. rész: Minden hónapban keresse meg a 3 legfontosabb összeget
A legfelső 3 összeg lekéréséhez minden héten a G5 képlete a következő:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Munka belülről kifelé, először használja a szöveg funkció kap havonta neveket minden időpontra a megnevezett tartományban dátuma :
TEXT(date,"mmmm") // get month names
Az "mmmm" egyéni számformátum olyan karakterláncot ad vissza, mint "április", "május", "június" az egyes nevekhez a megnevezett tartomány dátumában . Az eredmény egy ilyen hónapnév tömb:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
A TEXT függvény elküldi ezt a tömböt az IF függvénynek, amely úgy van konfigurálva, hogy az adott hónap dátumait kiszűrje a hónap nevének a G4-ben szereplő értékével történő tesztelésével (vegyes hivatkozás, így a képlet lefelé és végig másolható):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Csak az áprilisi összegek maradnak fenn és jutnak el IF-n keresztül; az összes többi érték HAMIS:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Végül a LARGE függvény az F5-ben szereplő értéket (szintén vegyes referencia) használja a fennmaradó "n-edik" legnagyobb érték visszaadására. A G5 cellában a LARGE 18 500-at ad vissza, ami az "1." legnagyobb érték. Amint a képletet lefelé és végig másolja a táblázat, a NAGY függvény a három hónap mindegyikében a legfelső 3 összeget adja vissza.
Most, hogy ismerjük a havi 3 legfontosabb értéket, ezeket az információkat "kulcsként" felhasználhatjuk az egyes ügyfelek nevének lekérésére.
2. rész: ügyfélnevek beolvasása
Megjegyzés: Ez egy példa az INDEX és a MATCH használatára több kritériummal. Ha ez a koncepció új számodra, itt van egy alapvető példa.
A G5: I7 első három értékéhez társított név beolvasásához az INDEX és a MATCH parancsokat használjuk:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni, az Excel 365 kivételével.
Belülről kifelé haladva a MATCH funkció úgy van konfigurálva, hogy a következő logikai logikát használja:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
A keresési érték 1, és a keresési tömb a következő kifejezéssel épül fel:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
A keresési tömböt létrehozó kifejezés logikai logikát használ az olyan összegek "kiszűrésére", amelyek (1) nem áprilisban vannak, és (2) nem a G5-ben szereplő értékek (18 500). Az eredmény egy 1-es és 0-os tömb, mint ez:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
1 és nulla keresési értékkel az egyezés típusához (a pontos egyezés kikényszerítéséhez) a MATCH 3-at ad vissza az INDEX függvénynek:
=INDEX(client,3) // returns "Janus"
Az INDEX visszatér a megnevezett tartomány kliens harmadik értékéhez, a "Janus" -hoz.
Amint a képletet lefelé és végig másolja a táblázat, a három hónapban a legjobb 3 ügyfelet adja vissza.
