Mnohdy chceme vypočítat některé hodnoty podle měsíce. Jako, kolik prodejů bylo provedeno za perikulární měsíc. To lze snadno provést pomocí kontingenčních tabulek, ale pokud se pokoušíte o dynamickou sestavu, můžeme pro součet podle měsíce použít vzorec SUMPRODUCT nebo SUMIFS.
Začněme řešením SUMPRODUCT.
Zde je obecný vzorec pro získání součtu za měsíc v aplikaci Excel
= SUMPRODUCT (rozsah_součtu,-(TEXT (rozsah_ data, "MMM") = měsíc_text))
Rozsah součtu : Je to rozsah, který chcete sčítat po měsících.
Časové období : Jedná se o časové období, ve kterém se budete dívat měsíce.
Měsíc_text: Je to měsíc v textovém formátu, ve kterém chcete sčítat hodnoty.
Nyní se podívejme na příklad:
Příklad: Součet hodnot podle měsíců v Excelu
Tady máme nějakou hodnotu spojenou s daty. Tato data jsou z ledna, února a března měsíce roku 2019.
Jak vidíte na obrázku výše, všechna data jsou z roku 2019. Nyní stačí sečíst hodnoty v E2: G2 podle měsíců v E1: G1.
Nyní sečtěte hodnoty podle měsíců a napište tento vzorec do E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Pokud jej chcete zkopírovat do sousedních buněk, použijte jako obrázek absolutní odkazy nebo pojmenované rozsahy.
To nám dává přesnou částku za každý měsíc.
Jak to funguje?
Začněme zevnitř, podívejme se na TEXT (A2: A9, "MMM") část. Zde funkce TEXT extrahuje měsíc z každého data v rozsahu A2: A9 v textovém formátu do pole. Překlad do vzorce do = SUMPRODUCT (B2: B9,-({„Jan“; „Jan“; „Únor“; „Jan“; „Únor“; „Mar“; „Jan“; „Únor“} = E1) )
Další, TEXT (A2: A9, "MMM")= E1: Zde je každý měsíc v poli porovnáván s textem v E1. Protože E1 obsahuje „Jan“, každé „Jan“ v poli je převedeno na TRUE a ostatní na FALSE. Tím se vzorec převede na = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Další -(TEXT (A2: A9, "MMM") = E1), převede TRUE FALSE na binární hodnoty 1 a 0. Vzorec se převede na = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Konečně SUMPRODUKT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkce SUMPRODUCT znásobí odpovídající hodnoty v $ B $ 2: $ B $ 9 na pole {1; 1; 0; 1; 0; 0; 1; 0} a sečte je. V E1 tedy dostaneme součet podle hodnoty jako 20052.
SUMA POKUD měsíce od jiného roku
Ve výše uvedeném příkladu byla všechna data ze stejného roku. Co kdyby byli z různých let? Výše uvedený vzorec bude součet hodnot podle měsíců bez ohledu na rok. Pokud použijeme výše uvedený vzorec, bude například přidán leden 2018 a led 2019. Což je ve většině případů špatně.
K tomu dojde, protože ve výše uvedeném příkladu nemáme pro rok žádná kritéria. Pokud přidáme také kritéria roku, bude to fungovat.
Obecný vzorec pro získání součtu podle měsíce a roku v aplikaci Excel
= SUMPRODUCT (rozsah_součtu,-(TEXT (rozsah_ data, "MMM") = měsíc_text),-(TEXT (rozsah_ data, "rrrr") = TEXT (rok, 0)))
Zde jsme přidali ještě jedno kritérium, které kontroluje rok. Všechno ostatní je stejné.
Pojďme vyřešit výše uvedený příklad, napište tento vzorec do buňky E1, abyste získali součet Jan v roce 2017.
= SUMPRODUKT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "rrrr") = TEXT (D2,0)))
Před kopírováním do níže uvedených buněk použijte pojmenované rozsahy nebo absolutní odkazy. Na obrázku jsem použil pojmenované rozsahy pro kopírování do sousedních buněk.
Nyní vidíme součet hodnoty také podle měsíců a let.
Jak to funguje?
První část vzorce je stejná jako předchozí příklad. Pojďme pochopit další část, která přidává kritéria roku.
-(TEXT (A2: A9, "rrrr") = TEXT (D2,0)): TEXT (A2: A9, "rrrr") převede datum v formátu A2: A9 jako roky v textovém formátu na pole. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Rok je většinou psán v číselném formátu. Abychom porovnali číslo s textem, převedli jsme rok int text pomocí TEXT (D2,0). Dále jsme porovnali tento textový rok s řadou let jako TEXT (A2: A9, "rrrr") = TEXT (D2,0). Tím se vrátí pole true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Dále jsme převedli true false na číslo pomocí - operátoru. To nám dává {0; 0; 1; 1; 0; 1; 0; 1}.
Nakonec bude vzorec přeložen na = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Kde první pole jsou hodnoty. Další je uzavřený měsíc a třetí rok. Nakonec dostaneme náš součet hodnot jako 2160.
Součet podle měsíce pomocí funkce SUMIFS
Obecný vzorec
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Tady,Rozsah součtu : Je to rozsah, který chcete sčítat po měsících.
Časové období : Jedná se o časové období, ve kterém se budete dívat měsíce.
Datum začátku : Je to počáteční datum, od kterého chcete součet. V tomto případě to bude 1. v daném měsíci.
Příklad: Součet hodnot podle měsíce v Excelu
Tady máme nějakou hodnotu spojenou s daty. Tato data jsou leden, únor a březen měsíce roku 2019.
Potřebujeme tyto hodnoty sečíst do měsíce. Teď to bylo snadné, kdybychom měli měsíce a roky odděleně. Ale nejsou. Zde nemůžeme použít žádný sloupec nápovědy.
Abych připravil zprávu, připravil jsem formát zprávy, který obsahuje měsíc a součet hodnot. Ve sloupci měsíce mám ve skutečnosti počáteční datum měsíce. Chcete -li zobrazit pouze měsíc, vyberte počáteční datum a stiskněte CTRL+1.
Ve vlastním formátu napište „mmm“.
Nyní máme data připravena. Shrňme hodnoty podle měsíců.
Napište tento vzorec do E3 pro součet podle měsíců.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Před zkopírováním vzorce použijte absolutní odkazy nebo pojmenované rozsahy.
Konečně jsme tedy dostali výsledek.
Jak to tedy funguje?
Jak víme, že funkce SUMIFS může sčítat hodnoty pro více kritérií.
Ve výše uvedeném příkladu je prvním kritériem součet všech hodnot v B3: B10, kde datum v A3: A10 je větší nebo rovno datu v D3. D3 obsahuje 1. ledna. To se také překládá.
= SUMIFS (B3: B10, A3: A10, "> =" & „1-jan-2019“, A3: A10, "<=" EOMONTH (D3,0))
Další kritéria jsou součtem pouze v případě Datum v A3: A10 je menší nebo roven EOMONTHU (D3,0). Funkce EOMONTH pouze vrací pořadové číslo posledního zadaného měsíce. Nakonec vzorec překládá také.
= SUMIFS (B3: B10, A3: A10, "> = 1-led-2019", A3: A10, "<= 31-led-2019“)
V Excelu tedy dostáváme součet po měsících.
Výhodou této metody je, že můžete upravit počáteční datum pro součet hodnot.
Pokud mají vaše data jiné roky, než je nejlepší, použijte kontingenční tabulky. Kontingenční tabulky vám pomohou snadno oddělit data v ročním, čtvrtletním a měsíčním formátu.
Takže ano, lidi, takto můžete sčítat hodnoty podle měsíců. Oba způsoby mají své vlastní speciality. Vyberte si, jak se vám líbí.
Pokud máte nějaké dotazy týkající se tohoto článku nebo jiných dotazů souvisejících s Excelem a VBA, je pro vás otevřena sekce komentářů.
Související články:
Jak používat funkci SUMIF v aplikaci Excel
SUMIFS s daty v Excelu
SUMIF s neprázdnými buňkami
Jak používat funkci SUMIFS v aplikaci Excel
SUMIFY pomocí logiky AND-OR
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity: Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu: Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel: Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel: Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.