Excel képlet: Rangértékek havonta -

Ö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: