Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako použiť funkciu SUMIFS na súčet údajov zodpovedajúcich konkrétnym mesiacom v Exceli a Tabuľkách Google.
Suma, ak do mesiaca
Najprv si ukážeme, ako sčítať údaje zodpovedajúce dátumom, ktoré spadajú do konkrétneho mesiaca a roku.
Na súčet môžeme použiť funkciu SUMIFS spolu s funkciami DATE, YEAR, MONTH a EOMONTH Počet predajov v rámci každého Mesiac.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Aby sme vytvorili vzorec vyššie, začneme definovaním rozsahu dátumov pre každý mesiac. Na definovanie prvého dňa v mesiaci (napr. 1. 5. 2021) používame funkciu DÁTUM. Môžeme to urobiť „pevným kódovaním“ dátumu do vzorca:
| 1 | "> =" A DÁTUM (2021,5,1) |
Alebo ešte lepšie, kritériá môžeme urobiť flexibilnými odkazom na dátum v bunke E3, extrahovaním dátumu a roku a mesiaca (a nastavením dňa na 1), ako je uvedené v príklade vyššie:
| 1 | "> =" A DÁTUM (ROK (E3), MESIAC (E3), 1) |
Na definovanie posledného dňa v mesiaci môžeme použiť funkciu EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Keď spojíme všetky tieto kritériá, môžeme napísať nasledujúci vzorec SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Zamknutie odkazov na bunky
Aby boli naše vzorce ľahšie čitateľné, ukázali sme vzorce bez uzamknutých odkazov na bunky:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" A DÁTUM (ROK (E3), MESIAC (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Formátovanie hodnôt mesiaca
V tomto prípade sme uviedli mesiace v stĺpci E. Tieto hodnoty mesiacov sú v skutočnosti dátumy formátované tak, aby vynechali deň, pomocou vlastného formátovania čísel.
Vlastný formát údajov je „mmm rrrr“, ktorý sa má uviesť máj 2022.
Suma za mesiac za viac rokov
Vyššie uvedený príklad zhrnul údaje s dátumami, ktoré spadali do konkrétneho mesiaca a roku. Namiesto toho môžete pomocou funkcie SUMPRODUCT zhrnúť údaje s dátumami, ktoré spadajú do jedného mesiaca v ktoromkoľvek roku.
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
V tomto prípade používame funkciu SUMPRODUCT na vykonávanie komplikovaných výpočtov „sum if“. Poďme sa pozrieť na vzorec.
Toto je náš konečný vzorec:
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
Najprv funkcia SUMPRODUCT uvádza Počet predajov pre každý Dátum predaja a potom porovná mesiac každého z nich Dátum predaja proti uvedenému Mesiac, vrátenie hodnoty TRUE, ak sa zhodujú mesiace, alebo FALSE, ak nie:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Ďalej dvojité pomlčky (-) konvertujú hodnoty TRUE a FALSE na 1 s a 0 s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkcia SUMPRODUCT potom znásobí každú dvojicu záznamov v poliach:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Nakoniec sa čísla v poli sčítajú:
| 1 | =98 |
Viac podrobností o použití booleovských príkazov a príkazu „-“ vo funkcii SUMPRODUCT nájdete tu
Suma za mesiac v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google úplne rovnako ako v programe Excel.
