Mnohokrát chceme vypočítať niektoré hodnoty podľa mesiaca. Ako, koľko tržieb sa vykonalo za perikulárny mesiac. To sa dá ľahko urobiť pomocou kontingenčných tabuliek, ale ak sa pokúšate o dynamický prehľad, na súčet podľa mesiaca môžeme použiť vzorec SUMPRODUCT alebo SUMIFS.
Začnime s riešením SUMPRODUCT.
Tu je generický vzorec na získanie súčtu za mesiac v programe Excel
= SUMPRODUCT (rozsah_ súčtov,-(TEXT (rozsah_ dátumov, "MMM") = mesiac_text))
Rozsah súčtu : Je to rozsah, ktorý chcete sčítať podľa mesiacov.
Date_range : Je to obdobie, ktoré budete hľadať mesiace.
Mesiac_text: Je to mesiac v textovom formáte, pre ktorý chcete sčítať hodnoty.
Teraz sa pozrime na príklad:
Príklad: Sumové hodnoty podľa mesiacov v programe Excel
Tu máme nejakú hodnotu spojenú s dátumami. Tieto dátumy sú január, február a marec mesiaca roku 2019.
Ako vidíte na obrázku vyššie, všetky dátumy sú z roku 2019. Teraz stačí súčet hodnôt v E2: G2 podľa mesiacov v E1: G1.
Teraz k súčtu hodnôt podľa mesiacov napíšte tento vzorec do E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Ak ho chcete skopírovať do susedných buniek, použite ako obrázok na obrázku absolútne odkazy alebo pomenované rozsahy.
To nám dáva presnú sumu za každý mesiac.
Ako to funguje?
Začnime zvnútra a pozrime sa na TEXT (A2: A9, "MMM") časť. Tu funkcia TEXT extrahuje mesiac z každého dátumu v rozsahu A2: A9 v textovom formáte do poľa. Preklad do vzorca do = SUMPRODUKT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Ďalej TEXT (A2: A9, "MMM")= E1: Tu sa každý mesiac v poli porovnáva s textom v E1. Pretože E1 obsahuje „Jan“, každé „Jan“ v poli sa skonvertuje na TRUE a ostatné na FALSE. To preloží vzorec na = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Ďalej -(TEXT (A2: A9, "MMM") = E1), prevádza TRUE FALSE na binárne hodnoty 1 a 0. Vzorec sa prekladá do = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Konečne SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkcia SUMPRODUCT vynásobí zodpovedajúce hodnoty v $ B $ 2: $ B $ 9 do poľa {1; 1; 0; 1; 0; 0; 1; 0} a sčíta ich. V E1 teda dostaneme súčet podľa hodnoty ako 20052.
SÚHRN, AK mesiace od iného roku
Vo vyššie uvedenom príklade boli všetky dátumy z rovnakého roku. Čo keby boli z rôznych rokov? Vyššie uvedený vzorec bude sčítavať hodnoty podľa mesiacov bez ohľadu na rok. Ak použijeme vyššie uvedený vzorec, bude napríklad pridaný Jan 2018 a Jan 2019. Čo je vo väčšine prípadov nesprávne.
K tomu dôjde, pretože v uvedenom príklade nemáme žiadne kritériá pre daný rok. Ak pridáme aj kritériá roku, bude to fungovať.
Generický vzorec na získanie súčtu podľa mesiaca a roku v programe Excel
= SUMPRODUCT (rozsah_ súčtov,-(TEXT (rozsah_ dátumov, "MMM") = mesiac_text),-(TEXT (rozsah_ dátumu, "rrrr") = TEXT (rok, 0)))
Tu sme pridali ešte jedno kritérium, ktoré kontroluje rok. Všetko ostatné je rovnaké.
Vyriešime vyššie uvedený príklad, napíšte tento vzorec do bunky E1, aby ste získali súčet Jan v roku 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "rrrr") = TEXT (D2,0)))
Pred kopírovaním do nižšie uvedených buniek použite pomenované rozsahy alebo absolútne odkazy. Na obrázku som použil pomenované rozsahy na kopírovanie v susedných bunkách.
Teraz môžeme vidieť aj súčet hodnoty podľa mesiacov a rokov.
Ako to funguje?
Prvá časť vzorca je rovnaká ako predchádzajúci príklad. Poďme porozumieť dodatočnej časti, ktorá pridáva kritériá roka.
-(TEXT (A2: A9, "rrrr") = TEXT (D2,0)): TEXT (A2: A9, "rrrr") prevádza dátum v A2: A9 ako roky v textovom formáte na pole. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Rok je väčšinou písaný v číselnom formáte. Na porovnanie čísla s textom sme skonvertovali rok v internom texte pomocou TEXTU (D2,0). Ďalej sme porovnali tento textový rok s poľom rokov ako TEXT (A2: A9, „rrrr“) = TEXT (D2,0). To vráti pole true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Ďalej sme previedli true false na číslo pomocou - operátora. To nám dáva {0; 0; 1; 1; 0; 1; 0; 1}.
Nakoniec bude vzorec preložený do = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 })). Kde prvé pole sú hodnoty. Ďalší je párovaný mesiac a tretí je rok. Nakoniec dostaneme náš súčet hodnôt ako 2160.
Na sčítanie podľa mesiacov použite funkciu SUMIFS
Generický vzorec
= SUMIFS (rozsah_ súčtu, rozsah_dátumu, ”> =” & dátum začiatku, rozsah_ dátumu, ”<=“ & EOMONTH (dátum_ začiatku, 0))
Tu,Rozsah súčtu : Je to rozsah, ktorý chcete sčítať podľa mesiacov.
Date_range : Je to obdobie, ktoré budete hľadať mesiace.
Dátum začiatku : Je to dátum začiatku, od ktorého chcete súčet. V tomto prípade to bude 1. deň v danom mesiaci.
Príklad: Sumové hodnoty podľa mesiacov v programe Excel
Tu máme nejakú hodnotu spojenú s dátumami. Tieto dátumy sú január, február a marec mesiaca roku 2019.
Potrebujeme tieto hodnoty zhrnúť do jedného mesiaca. Teraz to bolo jednoduché, keby sme mali oddelene mesiace a roky. Ale oni nie sú. Tu nemôžeme použiť žiadny stĺpček pomoci.
Aby som pripravil správu, pripravil som formát správy, ktorý obsahuje mesiac a súčet hodnôt. V stĺpci mesiac mám skutočne dátum začiatku mesiaca. Ak chcete zobraziť iba mesiac, vyberte dátum začatia a stlačte kombináciu klávesov CTRL+1.
Vo vlastnom formáte napíšte „mmm“.
Teraz máme naše údaje pripravené. Sčítajme hodnoty podľa mesiacov.
Napíšte tento vzorec do E3 a sumarizujte ho podľa mesiacov.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Pred skopírovaním vzorca použite absolútne odkazy alebo pomenované rozsahy.
Tak sme sa konečne dočkali výsledku.
Ako to teda funguje?
Ako vieme, funkcia SUMIFS môže sčítať hodnoty podľa viacerých kritérií.
Vo vyššie uvedenom príklade je prvým kritériom súčet všetkých hodnôt v B3: B10, kde dátum v A3: A10 je väčší alebo rovný dátumu v D3. D3 obsahuje 1. januára. To sa tiež prekladá.
= SUMIFS (B3: B10, A3: A10, "> =" & „1-jan-2019“, A3: A10, "<=" EOMONTH (D3,0))
Nasledujúce kritériá sú súčtom iba vtedy, ak je dátum v A3: A10 je menší alebo rovný EOMONTHU (D3,0). Funkcia EOMONTH iba vráti sériové číslo posledného zadaného dátumu v mesiaci. Nakoniec sa prekladá aj vzorec.
= SUMIFS (B3: B10, A3: A10, "> = 1. januára 2019“, A3: A10, „<= 31. Januára 2019“)
V Exceli teda dostaneme sumu po mesiaci.
Výhodou tejto metódy je, že môžete prispôsobiť dátum začiatku súčtu hodnôt.
Ak majú vaše dátumy iné roky, ako je najlepšie, použite kontingenčné tabuľky. Kontingenčné tabuľky vám môžu pomôcť ľahko oddeliť údaje v ročnom, štvrťročnom a mesačnom formáte.
Takže áno, chlapci, takto môžete sčítať hodnoty podľa mesiacov. Oba spôsoby majú svoje vlastné špeciality. Vyberte si, akým spôsobom sa vám páči.
Ak máte akékoľvek otázky týkajúce sa tohto článku alebo akékoľvek ďalšie otázky súvisiace s programom Excel a VBA, sekcia s komentármi je pre vás otvorená.
Súvisiace články:
Ako používať funkciu SUMIF v programe Excel
SUMIFS s dátumami v programe Excel
SUMIF s prázdnymi bunkami
Ako používať funkciu SUMIFS v programe Excel
SUMIFY pomocou logiky AND-OR
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu: Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel: Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel: Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel: Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.