Obecný vzorec
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
souhrn
Chcete-li shrnout měsíc, můžete použít vzorec založený na funkci SUMIFS s pomocí funkce EOMONTH. V zobrazeném příkladu je vzorec v F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Tento vzorec používá pojmenované rozsahy „množství“ (C4: C9) a „datum“ (B4: B9).
Vysvětlení
Funkce SUMIFS může součet hodnot v rozmezí na základě více kritérií.
V tomto případě nakonfigurujeme SUMIFS tak, aby sumarizoval částky podle měsíce pomocí dvou kritérií: jedno, aby odpovídalo datům větším nebo rovným prvnímu dni měsíce, jednomu, aby odpovídalo datům menším nebo rovným poslednímu dni měsíce. Jinými slovy, potřebujeme vzorec, jako je tento:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Nebo neprůstřelnější alternativa:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Tato verze je robustnější, protože data jsou konstruována s oddělenými hodnotami roku, měsíce a dne a nejsou zadávána jako text, který je třeba interpretovat Excelem.
Za normálních okolností je to nepříjemné, protože pokud do sloupce E 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žijeme jednoduchý trik, který nám usnadní práci: Ve sloupci E namísto zadávání názvů měsíců přidáváme skutečná data (1. 1. 2016, 2. 1. 2016, 3. 1. 2016), poté použijte vlastní formát data („mmmm“) k zobrazení názvů měsíců.
Díky tomu je snadné vytvořit kritéria, která pro SUMIFS 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 toto:
"<="&EOMONTH(E4,0)
S nulou pro měsíce funkce EOMONTH automaticky vrátí poslední den měsíce poskytnutý datem.
Zřetězení s ampersandem (&) je nutné při vytváření kritérií, která používají logický operátor s číselnou hodnotou.
Ř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 pro vás tento druh seskupení provedou bez jakýchkoli vzorců. Podrobné porovnání vzorců vs. kontingenčních tabulek najdete v tomto videu: Proč kontingenční tabulky.
