Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak pomocí funkce SUMIFS sčítat data odpovídající konkrétním měsícům v Excelu a Tabulkách Google.
Součet, pokud do měsíce
Nejprve si předvedeme, jak sečíst data odpovídající datům, která spadají do konkrétního měsíce a roku.
K součtu můžeme použít funkci SUMIFS spolu s funkcemi DATE, YEAR, MONTH a EOMONTH Počet prodejů v každém Měsíc.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Abychom vytvořili vzorec výše, začneme definováním časového období pro každý měsíc. Funkci DATE používáme k definování prvního dne v měsíci (např. 1. 5. 2021). Můžeme to udělat tak, že „naprogramujeme“ datum do vzorce:
| 1 | "> =" A DATUM (2021,5,1) |
Nebo ještě lépe, můžeme kritéria učinit flexibilními odkazem na datum v buňce E3, extrahováním roku a měsíce data (a nastavením dne na 1), jak ukazuje výše uvedený příklad:
| 1 | "> =" A DATUM (ROK (E3), MĚSÍC (E3), 1) |
K definování posledního dne v měsíci můžeme použít funkci EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Když dáme dohromady všechna tato kritéria, můžeme napsat následující vzorec SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme vzorce bez zamčených odkazů na buňky:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" A DATUM (ROK (E3), MĚSÍC (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Formátování měsíčních hodnot
V tomto příkladu jsme uvedli měsíce ve sloupci E. Tyto měsíční hodnoty jsou ve skutečnosti data naformátovaná tak, aby den vynechala, pomocí vlastního formátování čísel.
Vlastní formát dat je „mmm rrrr“ pro zobrazení května 2022.
Součet za měsíc za více let
Výše uvedený příklad shrnul data s daty, která spadala do konkrétního měsíce a roku. Místo toho můžete pomocí funkce SUMPRODUCT shrnout data s daty, která spadají do měsíce v kterémkoli roce.
| 1 | = SUMPRODUKT (C3: C8,-(MĚSÍC (B3: B8) = MĚSÍC (G3))) |
V tomto příkladu používáme funkci SUMPRODUCT k provádění komplikovaných výpočtů „sum if“. Pojďme si projít vzorec.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUKT (C3: C8,-(MĚSÍC (B3: B8) = MĚSÍC (G3))) |
Za prvé, funkce SUMPRODUCT uvádí Počet prodejů pro každého Datum prodeje a poté porovná měsíc každého z nich Datum prodeje proti uvedenému Měsíc, vrací TRUE, pokud se měsíce shodují, nebo FALSE, pokud ne:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Dále dvojité pomlčky (-) převádějí hodnoty PRAVDA a NEPRAVDA na 1 s a 0 s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkce SUMPRODUCT pak znásobí každou dvojici záznamů v polích:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Nakonec jsou sečtena čísla v poli:
| 1 | =98 |
Další podrobnosti o použití booleovských příkazů a příkazu „-“ ve funkci SUMPRODUCT najdete zde
Součet za měsíc v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
