Všeobecný vzorec
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Zhrnutie
V priemere za mesiac môžete použiť vzorec založený na funkcii AVERAGEIFS s pomocou funkcie EOMONTH. V zobrazenom príklade je vzorec v F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Tento vzorec používa pomenované rozsahy „množstvá“ (D5: D104) a „dátumy“ (C5: C104).
Vysvetlenie
Funkcia AVERAGEIFS môže spriemerovať rozsahy na základe viacerých kritérií. V takom prípade nakonfigurujeme AVERAGEIFS na priemerné sumy podľa mesiaca pomocou dvoch kritérií: (1) dátumy zhody väčšie alebo rovné prvému dňu mesiaca, (2) dátumy zhody menšie alebo rovné poslednému dňu mesiaca. Keby sme do vzoru pomocou funkcie DATE napevno zakódovali dátumy január 2016, vyzeralo by to takto.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ale my nechceme pevne zakódovať dátumy, chceme, aby nám tieto dátumy vygeneroval Excel. Spravidla je to nepríjemné, pretože ak do stĺpca F pridáte názvy mesiacov ako text (tj. „Január“, „február“, „marec“ atď.), Budete musieť vytvoriť ďalšie problémy, aby ste vytvorili dátumy, ktoré môžete použiť pre kritériá .
V tomto prípade však používame jednoduchý trik, ktorý nám uľahčí prácu: V stĺpci F namiesto zadávania názvov mesiacov pridávame skutočné dátumy pre prvý každý mesiac (1. 1. 2016, 2. 2. 2016, 3 / 1/2016 atď.) A na zobrazenie názvov mesiacov použite vlastný formát dátumu („mmm“).
Takto je možné ľahko vytvoriť kritériá, ktoré potrebujeme pre AVERAGEIFS. Na priradenie dátumov väčších alebo rovných prvému v mesiaci používame:
">="&E4
A aby sme priradili dátumy menšie alebo rovné poslednému dňu v mesiaci, použijeme:
"<="&EOMONTH(E4,0)
EOMONTH automaticky vráti posledný deň toho istého mesiaca, pretože pre argument mesiacov dodávame nulu.
Poznámka: Pri vytváraní kritérií na základe odkazu na bunku je potrebné zreťazenie s ampersandom (&).
Riešenie kontingenčnej tabuľky
Kontingenčná tabuľka je vynikajúcim riešením, keď potrebujete sumarizovať údaje podľa roku, mesiaca, štvrťroka atď., Pretože kontingenčné tabuľky poskytujú ovládacie prvky na automatické zoskupenie podľa dátumu. Podrobné porovnanie vzorcov a kontingenčných tabuliek nájdete v tomto videu: Prečo kontingenčné tabuľky.
