Všeobecný vzorec
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Zhrnutie
Na priemerné doby odozvy podľa mesiaca môžete použiť vzorec založený na funkcii AVERAGEIFS spolu s funkciou EOMONTH.
V zobrazenom príklade je vzorec v G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Vysvetlenie
Tento vzorec používa pomenované rozsahy „dátumy“ (B5: B25) a „trvanie“ (D5: D25). Stĺpec D doby trvania je v minútach, vypočítaný odčítaním dátumu otvoreného od dátumu uzavretého.
Funkcia AVERAGEIFS je navrhnutá tak, aby priemerovala rozsahy na základe viacerých kritérií. V takom prípade nakonfigurujeme AVERAGEIFS na priemerné trvanie po mesiaci pomocou dvoch kritérií: (1) zodpovedajúce dátumy väčšie alebo rovné prvému dňu v mesiaci, (2) zodpovedajúce dátumy menšie alebo rovné poslednému dňu mesiaca.
Na zostavenie dátumu podľa mesiaca používame jednoduchý trik, ktorý nám uľahčí prácu: V stĺpci F namiesto zadávania názvov mesiacov („Jan“, „Feb“, Mar “atď.) Pridáme pridáme skutočné dátumy pre prvý z každý mesiac (1. 1. 2016, 2. 2. 2016, 1. 3. 2016 atď.),. Potom použijeme vlastný formát dátumu („mmm“) na zobrazenie názvov mesiacov.
Takto je oveľa jednoduchšie zostaviť kritériá, ktoré potrebujeme pre AVERAGEIFS, pomocou hodnôt v stĺpci F. Na priradenie dátumov väčších alebo rovnakých k prvému v mesiaci používame:
">="&F5
Na priradenie dátumov menších alebo rovných poslednému dňu mesiaca používame:
"<="&EOMONTH(F5,0)
EOMONTHU dostaneme tak, že vrátime posledný deň toho istého mesiaca zadaním nuly pre argument mesiace .
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ť alebo spriemerovať údaje podľa roku, mesiaca, štvrťroka atď., Pretože kontingenčné tabuľky poskytujú ovládacie prvky na automatické zoskupovanie dátumov. Podrobné porovnanie vzorcov a kontingenčných tabuliek nájdete v tomto videu: Prečo kontingenčné tabuľky.
