Általános képlet
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Összegzés
Az adott hónap maximális értékének meghatározásához használja a MAXIFS függvényt vagy az alábbi alternatívák egyikét. A bemutatott példában a G5 képlete a következő:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Ahol az eladásokat (D5: D15), a dátumokat (B5: B15) és az értékeket (C5: C15) nevezik meg tartományoknak.
Magyarázat
A MAXIFS függvény egy vagy több kritérium alapján megtalálja a maximális értéket egy tartományban. A bemutatott példában a MAXIFS-t használjuk arra, hogy az adott hónapon alapuló maximális értékesítési értéket a hónap első napja és a hónap utolsó napja közötti dátumokkal "zárójelben" találjuk meg. Az első kritérium ellenőrzi, hogy a dátumok nagyobbak vagy egyenlőek-e a hónap első részével:
dates,">="&G4,dates // first of month
Megjegyzés: feltételezzük, hogy a G4-ben szereplő dátum "a hónap első napja".
A második kritérium azt ellenőrzi, hogy a dátumok kisebbek vagy egyenlőek-e a hónap utolsóival, az EOMONTH függvénnyel kiszámítva:
dates,"<="&EOMONTH(G4,0) // last of month
Amikor mindkét feltétel IGAZ értéket ad vissza, a dátum az adott hónapban van, a MAXIFS pedig a kritériumoknak megfelelő dátumok értékének max.
Alternatív képletek
Ha az Excel verziója nem tartalmazza a MAXIFS függvényt, akkor más lehetőségek is vannak. Az egyik lehetőség egy egyszerű tömbképlet, amely a MAX és IF függvényeken alapul:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Megjegyzés: Ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni.
Egy másik lehetőség az AGGREGATE függvény ilyen használata:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Mindkét fenti lehetőség a dátum tesztelésének egyszerűbb módszerét használja a TEXT függvény alapján, amely számformátumot használ a hónap és az év dátumból történő kivonására. A TEXT függvény nem használható így a MAXIFS függvényen belül, mert a MAXIFS tartományt igényel. Ezeket az alternatív megközelítéseket itt részletesebben elmagyarázzuk.
A max kliens
Az adott hónapban a maximális értékhez társított ügyfél lekéréséhez a G6 képlete a következő:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Az ötlet az, hogy már tudjuk a maximális értéket egy hónap alatt (G5), és ezt az értéket "kulcsként" felhasználhatjuk az ügyfél megkeresésére. Megjegyzés: meg kell ismételnünk az adott hónapban az értékek elkülönítésére használt logikát, hogy elkerüljük a hamis egyezéseket egy másik hónapban.
A fent említett alternatívákhoz hasonlóan ez a képlet is egyszerűsített tesztet használ a dátumokra a TEXT függvény alapján. További információ az INDEX-ről és a MATCH-ról több kritériummal itt található.
Az all-in-one képletnél cserélje le a G5-öt a fentebb ismertetett képlet-opciók egyikével az adott hónap maximális értékének meghatározásához.
