Obecný vzorec
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
souhrn
K průměrné době odezvy podle měsíce můžete použít vzorec založený na funkci AVERAGEIFS společně s funkcí EOMONTH.
V zobrazeném příkladu je vzorec v G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Vysvětlení
Tento vzorec používá pojmenované rozsahy „data“ (B5: B25) a „doby trvání“ (D5: D25). Sloupec D trvání je v minutách, počítáno odečtením data otevření od data uzavření.
Funkce AVERAGEIFS je navržena tak, aby průměrovala rozsahy na základě více kritérií. V tomto případě nakonfigurujeme AVERAGEIFS na průměrnou dobu trvání podle měsíce pomocí dvou kritérií: (1) odpovídající data větší nebo rovna prvnímu dni měsíce, (2) odpovídající data menší nebo rovna poslednímu dni měsíce.
Abychom sestavili data podle měsíce, použijeme jednoduchý trik, který nám usnadní práci: Ve sloupci F namísto zadávání názvů měsíců („Jan“, „Feb“, Mar “atd.) Přidáme přidáme skutečná data pro první z každý měsíc (1. 1. 2016, 2. 1. 2016, 3. 1. 2016 atd.). Poté použijeme vlastní formát data („mmm“) k zobrazení názvů měsíců.
Díky tomu je mnohem jednodušší sestavit kritéria, která pro AVERAGEIFS potřebujeme, pomocí hodnot ve sloupci F. K porovnání dat větších nebo rovných prvnímu v měsíci používáme:
">="&F5
K přiřazení dat menších nebo rovných poslednímu dni měsíce používáme:
"<="&EOMONTH(F5,0)
Dostaneme EOMONTH, abychom vrátili poslední den téhož měsíce zadáním nuly pro argument měsíce .
Poznámka: Zřetězení s ampersandem (&) je nutné při vytváření kritérií na základě odkazu na buňku.
Řešení kontingenční tabulky
Kontingenční tabulka je vynikajícím řešením, když potřebujete sumarizovat nebo průměrovat data podle roku, měsíce, čtvrtletí atd., Protože kontingenční tabulky poskytují ovládací prvky pro automatické seskupování dat. Podrobné porovnání vzorců vs. kontingenčních tabulek najdete v tomto videu: Proč kontingenční tabulky.
