souhrn
Chcete-li součet po měsících ve sloupcích, můžete použít funkci SUMIFS společně s funkcí EOMONTH. V zobrazeném příkladu je vzorec v G5:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Tento vzorec používá tři pojmenované rozsahy: „množství“ (D5: D15), „klient“ (B5: B15) a „datum“ (C5: C15).
Vysvětlení
Funkce SUMIFS je navržena tak, aby sumarizovala hodnoty v rozsahu založeném na jednom nebo více kritériích. V tomto případě potřebujeme tři kritéria:
- Název klienta = klient ve sloupci F
- Datum> = první měsíc (od data v řádku 4)
- Datum <= konec měsíce (od data v řádku 4)
Klíčový bod: Názvy měsíců v G4: I4 jsou ve skutečnosti platná data, formátovaná pomocí vlastního formátu čísel „mmm“. To nám umožňuje použít hodnoty data v G4: I4 přímo pro kritéria # 2 a # 3 výše.
Zpět v SUMIFS se ke kontrole jména klienta používá první pár rozsahů / kritérií:
client,$F5
Všimněte si, že F5 je smíšený odkaz se zamčeným sloupcem. To umožňuje změnit řádek při kopírování vzorce přes tabulku, ale jméno klienta vždy pochází ze sloupce F.
Druhá dvojice rozsah / kritéria se používá ke kontrole dat oproti prvnímu v měsíci:
date,">="&G$4
Jak již bylo zmíněno výše, hodnoty v G4: I4 jsou ve skutečnosti data: 1. ledna 2019, 1. února 2019 a 1. března 2019. Toto kritérium tedy jednoduše kontroluje data větší než data prvního měsíce v řádku 4. Všimněte si, že toto je další smíšený odkaz, tentokrát s uzamčeným řádkem. To umožňuje změnit sloupec při kopírování vzorce přes tabulku, ale udržuje číslo řádku pevné. Zřetězení s ampersandem (&) je nutné při vytváření kritérií, která používají logický operátor a hodnotu z jiné buňky.
Třetí pár rozsah / kritéria se používá ke kontrole dat oproti poslednímu dni v měsíci:
date,"<="&EOMONTH(G$4,0)
Chcete-li získat poslední den každého měsíce, použijeme funkci EOMONTH k datu ze záhlaví sloupce v řádku 4. Při argumentu měsíce zadaná nula vrátí EOMONTH poslední den téhož měsíce. Opět je odkaz na G4 smíchán, aby se řádek nezměnil.
Řešení kontingenční tabulky
Kontingenční tabulka by byla vynikajícím řešením tohoto problému, protože se může automaticky seskupovat podle měsíců bez jakýchkoli vzorců. Podrobné porovnání vzorců vs. kontingenčních tabulek najdete v tomto videu: Proč kontingenční tabulky.
