Obecný vzorec
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
souhrn
V průměru za měsíc můžete použít vzorec založený na funkci AVERAGEIFS s pomocí funkce EOMONTH. V zobrazeném příkladu je vzorec v F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Tento vzorec používá pojmenované rozsahy „částky“ (D5: D104) a „data“ (C5: C104).
Vysvětlení
Funkce AVERAGEIFS může průměrovat rozsahy na základě více kritérií. V tomto případě nakonfigurujeme AVERAGEIFS na průměrné částky podle měsíce pomocí dvou kritérií: (1) data shody větší nebo rovna prvnímu dni měsíce, (2) data shody menší nebo rovna poslednímu dni měsíce. Pokud bychom pevně zakódovali data pro leden 2016 do vzorce pomocí funkce DATE, vypadalo by to takto.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ale nechceme pevně zakódovat data, chceme, aby Excel tyto data vygeneroval za nás. Za normálních okolností je to nepříjemné, protože pokud do sloupce F přidáte názvy měsíců jako text (tj. „Leden“, „únor“, „březen“ atd.), Musíte vytvořit další potíže, abyste vytvořili data, která můžete použít pro kritéria .
V tomto případě však používáme jednoduchý trik, který nám usnadní práci: Ve sloupci F namísto zadávání názvů měsíců přidáváme skutečná data pro první každý měsíc (1. 1. 2016, 2. 2. 2016, 3 / 1/2016 atd.) A k zobrazení názvů měsíců použijte vlastní formát data („mmm“).
Díky tomu je snadné vytvořit kritéria, která pro AVERAGEIFS potřebujeme. K přiřazení dat větších nebo rovných prvnímu v měsíci používáme:
">="&E4
A abychom přiřadili data menší nebo rovna poslednímu dni v měsíci, použijeme:
"<="&EOMONTH(E4,0)
EOMONTH automaticky vrátí poslední den téhož měsíce, protože pro argument měsíce zadáme nulu.
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 data podle roku, měsíce, čtvrtletí atd., Protože kontingenční tabulky poskytují ovládací prvky pro automatické seskupování podle data. Podrobné porovnání vzorců vs. kontingenčních tabulek najdete v tomto videu: Proč kontingenční tabulky.
