Běh celkem (také nazýván kumulativní součet) se v mnoha situacích používá zcela běžně. Je to metrika, která vám řekne, jaký je součet hodnot dosud.
Pokud máte například údaje o měsíčních tržbách, pak by vám celkový součet řekl, kolik prodejů bylo provedeno do konkrétního dne od prvního dne měsíce.
Existují také některé další situace, kde se často používá celkový součet, například výpočet zůstatku v hotovosti na bankovních výpisech/účetní knize, počítání kalorií v jídelním plánu atd.
V aplikaci Microsoft Excel existuje několik různých způsobů, jak vypočítat průběžné součty.
Metoda, kterou zvolíte, by také závisela na struktuře vašich dat.
Pokud například máte jednoduchá tabulková data, můžete použít jednoduchý vzorec SUM, ale pokud máte tabulku aplikace Excel, je nejlepší použít strukturované odkazy. K tomu můžete také použít Power Query.
V tomto tutoriálu se budu zabývat všemi těmito různými metodami vypočítat průběžné součty v Excelu.
Začněme tedy!
Výpočet celkového součtu s tabulkovými daty
Pokud máte tabulková data (tj. Tabulku v aplikaci Excel, která není převedena do tabulky aplikace Excel), můžete k výpočtu průběžných součtů použít několik jednoduchých vzorců.
Použití doplňkového operátoru
Předpokládejme, že máte data o prodejích podle data a chcete vypočítat průběžný součet ve sloupci C.
Níže jsou uvedeny kroky, jak to provést.
Krok 1 - Do buňky C2, což je první buňka, ve které chcete celkový počet, zadejte
= B2
Jednoduše tak získáte stejné prodejní hodnoty v buňce B2.
Krok 2 - Do buňky C3 zadejte následující vzorec:
= C2+B3
Krok 3 - Aplikujte vzorec na celý sloupec. K výběru a přetažení můžete použít úchyt Vyplnit nebo jednoduše zkopírovat a vložit buňku C3 do všech zbývajících buněk (což by automaticky upravilo odkaz a poskytlo správný výsledek).
Tím získáte výsledek, jak je uvedeno níže.
Je to opravdu jednoduchá metoda a ve většině případů funguje dobře.
Logika je jednoduchá - každá buňka nabere hodnotu nad sebou (což je kumulativní součet do předchozího data) a přičte hodnotu v buňce, která k ní sousedí (což je prodejní hodnota pro daný den).
Existuje pouze jedna nevýhoda - v případě, že odstraníte některý ze stávajících řádků v této sadě dat, všechny níže uvedené buňky, které by vrátily referenční chybu (#REF!)
Pokud je to u vaší sady dat možné, použijte další metodu, která používá vzorec SUM
Použití SUM s částečně zamčeným odkazem na buňku
Předpokládejme, že máte data o prodejích podle data a chcete vypočítat průběžný součet ve sloupci C.
Níže je uveden součet SUM, který vám poskytne celkový součet.
= SUM ($ B $ 2: B2)
Dovolte mi vysvětlit, jak tento vzorec funguje.
Ve výše uvedeném vzorci SUM jsem použil odkaz pro přidání jako $ B $ 2: B2
- $ B $ 2 - toto je absolutní reference, což znamená, že když zkopíruji stejný vzorec do níže uvedených buněk, tato reference se nezmění. Takže při kopírování vzorce do buňky níže se vzorec změní na SUM ($ B $ 2: B3)
- B2 - toto je druhá část odkazu, která je relativní referencí, což znamená, že se to upraví, když zkopíruji vzorec dolů nebo doprava. Takže při kopírování vzorce v buňce níže se z této hodnoty stane B3
Přečtěte si také: Absolutní, relativní a smíšené odkazy na buňky v Excelu
Skvělé na této metodě je, že v případě, že odstraníte některý z řádků v datové sadě, tento vzorec by se upravil a přesto by vám poskytl správné běžící součty.
Výpočet celkového součtu v tabulce aplikace Excel
Při práci s tabulkovými daty v Excelu je dobré je převést do excelové tabulky. To velmi usnadňuje správu dat a také umožňuje snadné použití nástrojů, jako jsou Power Query a Power Pivot.
Práce s tabulkami aplikace Excel přináší výhody, jako jsou strukturované odkazy (což opravdu usnadňuje odkazování na data v tabulce a jejich použití ve vzorcích) a automatické úpravy odkazů v případě, že data z tabulky přidáváte nebo odstraňujete.
I když můžete stále používat výše uvedený vzorec, který jsem vám ukázal v tabulce aplikace Excel, ukážu vám nějaké lepší metody, jak toho dosáhnout.
Předpokládejme, že máte tabulku aplikace Excel, jak je uvedeno níže, a chcete vypočítat průběžný součet ve sloupci C.
Níže je uveden vzorec, který to provede:
= SUM (Data prodeje [[#záhlaví], [prodej]]: [@prodej])
Výše uvedený vzorec může vypadat trochu zdlouhavě, ale nemusíte jej psát sami. to, co vidíte v součtovém vzorci, se nazývá strukturované odkazy, což je účinný způsob aplikace Excel, jak odkazovat na konkrétní datové body v tabulce aplikace Excel.
Například SalesData [[#Headers], [Sale]] odkazuje na záhlaví Sale v tabulce SalesData (SalesData je název tabulky aplikace Excel, kterou jsem zadal při vytváření tabulky)
A [@Sale] odkazuje na hodnotu v buňce ve stejném řádku ve sloupci Sale.
Právě jsem to vysvětlil, abyste to pochopili, ale i když nevíte nic o strukturovaných referencích, můžete tento vzorec snadno vytvořit.
Níže jsou uvedeny kroky, jak to provést:
- Do buňky C2 zadejte = SUM (
- Vyberte buňku B1, což je záhlaví sloupce, který má prodejní hodnotu. Můžete použít myš nebo klávesy se šipkami. Všimnete si, že Excel automaticky zadá strukturovaný odkaz pro tuto buňku
- Přidejte: (symbol dvojtečky)
- Vyberte buňku B2. Excel by znovu automaticky vložil strukturovaný odkaz do buňky
- Zavřete závorku a stiskněte Enter
Také si všimnete, že vzorec nemusíte kopírovat do celého sloupce, excelová tabulka to udělá automaticky za vás.
Další skvělá věc na této metodě je, že v případě, že přidáte nový záznam do této sady dat, tabulka aplikace Excel automaticky vypočítá průběžný součet pro všechny nové záznamy.
I když jsme do vzorce zahrnuli záhlaví sloupce, nezapomeňte, že vzorec bude ignorovat text záhlaví a bude brát v úvahu pouze data ve sloupci
Výpočet celkového běhu pomocí Power Query
Power Query je úžasný nástroj, pokud jde o propojení s databázemi, extrahování dat z více zdrojů a jejich transformaci před vložením do Excelu.
Pokud již pracujete s Power Query, bylo by efektivnější přidat běžící součty při transformaci dat v samotném editoru Power Query (namísto 1. získání dat v Excelu a následného přidání běžících součtů pomocí kteréhokoli z výše uvedených výše popsané metody).
I když v Power Query není žádná vestavěná funkce pro přidání běžících součtů (což bych si přál, aby byla), stále to můžete udělat pomocí jednoduchého vzorce.
Předpokládejme, že máte tabulku aplikace Excel, jak je znázorněno níže, a chcete k těmto datům přidat průběžné součty:
Níže jsou uvedeny kroky, jak to provést:
- Vyberte libovolnou buňku v tabulce aplikace Excel
- Klikněte na Data
- Na kartě Získat a transformovat klikněte na ikonu z tabulky/rozsahu. Otevře se tabulka v editoru Power Query
- [Volitelné] Pokud váš sloupec Datum již není seřazen, klikněte na ikonu filtru ve sloupci Datum a poté klikněte na Seřadit vzestupně
- V editoru Power Query klikněte na kartu Přidat sloupec
- Ve skupině Obecné klikněte na rozevírací seznam Sloupec rejstříku (neklikejte na ikonu Sloupec rejstříku, ale na malou černou nakloněnou šipku hned vedle zobrazte další možnosti)
- Klikněte na možnost „Od 1“. Tím se přidá nový sloupec indexu, který bude začínat od jednoho a do celého sloupce bude zadávat čísla zvyšující se o 1
- Klikněte na ikonu „Vlastní sloupec“ (která je také na kartě Přidat sloupec)
- V dialogovém okně vlastního sloupce, které se otevře, zadejte název nového sloupce. v tomto příkladu použiji název „Running Total“
- Do pole Vzorec vlastního sloupce zadejte následující vzorec: List.Sum (List.Range (#”Přidaný index“ [Prodej], 0, [Index]))
- Zkontrolujte, zda je v dolní části dialogového okna zaškrtávací políčko s nápisem „Nebyly zjištěny žádné chyby syntaxe“
- Klikněte na OK. Tím by se přidal nový běžící celkový sloupec
- Odeberte indexový sloupec
- Klikněte na kartu Soubor a poté klikněte na „Zavřít a načíst“
Výše uvedené kroky by do sešitu vložily nový list s tabulkou, která obsahuje průběžné součty.
Pokud si nyní myslíte, že je to příliš mnoho kroků ve srovnání s předchozími metodami používání jednoduchých vzorců, máte pravdu.
Pokud již máte datovou sadu a vše, co musíte udělat, je přidat celkové součty, je lepší Power Query nepoužívat.
Používání Power Query má smysl tam, kde musíte extrahovat data z databáze nebo kombinovat data z více různých sešitů a v tomto procesu do ní také přidávat běžící součty.
Jakmile provedete tuto automatizaci pomocí Power Query, příště, když se vaše sada dat změní, nemusíte to dělat znovu, můžete jednoduše aktualizovat dotaz a poskytne vám výsledek na základě nové sady dat.
Jak to funguje?
Nyní mi dovolte rychle vysvětlit, co se v této metodě děje.
První věc, kterou v editoru Power Query děláme, je vložit indexový sloupec začínající od jednoho a zvyšující se o jeden, jak jde dolů po buňkách.
Děláme to proto, že tento sloupec musíme použít, zatímco počítáme průběžný součet v jiném sloupci, který vložíme v dalším kroku.
Poté vložíme vlastní sloupec a použijeme níže uvedený vzorec
List.Sum (List.Range (#"Přidaný index" [Prodej], 0, [Index]))
Toto je vzorec List.Sum, který by vám poskytl součet rozsahu, který je v něm uveden.
A tento rozsah je určen pomocí funkce List.Range.
Funkce List.Range udává zadaný rozsah ve sloupci prodej jako výstup a tento rozsah se mění na základě hodnoty indexu. Například pro první záznam bude rozsah jednoduše hodnotou prvního prodeje. A jak půjdete dolů po buňkách, tento rozsah se rozšíří.
Takže pro první buňku. List.Sum vám poskytne pouze součet první prodejní hodnoty a pro druhou buňku vám poskytne součet prvních dvou prodejních hodnot atd.
I když tato metoda funguje dobře, s velkými datovými sadami - tisíci řádky - je opravdu pomalá. Pokud máte co do činění s velkou sadou dat a chcete do ní přidat běžící součty, podívejte se na tento návod, který ukazuje další metody, které jsou rychlejší.Výpočet celkového běhu na základě kritérií
Doposud jsme viděli příklady, kdy jsme vypočítali průběžný součet pro všechny hodnoty ve sloupci.
Mohou však nastat případy, kdy chcete vypočítat průběžný součet pro konkrétní záznamy.
Níže například mám soubor dat a chci vypočítat průběžný součet pro tiskárny a skenery samostatně ve dvou různých sloupcích.
To lze provést pomocí vzorce SUMIF, který vypočítá průběžný součet a přitom se ujistí, že je splněna zadaná podmínka.
Níže je uveden vzorec, který to provede pro sloupce Tiskárna:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Podobně pro výpočet součtu běhů pro skenery použijte následující vzorec:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Ve výše uvedených vzorcích jsem použil SUMIF, který by mi dal součet v rozsahu, pokud jsou splněna zadaná kritéria.
Vzorec má tři argumenty:
- rozsah: toto je rozsah kritérií, který bude zkontrolován podle zadaných kritérií
- kritéria: toto jsou kritéria, která by byla zaškrtnuta, pouze pokud je toto kritérium splněno, pak budou přidány hodnoty ve třetím argumentu, což je rozsah součtů
- [rozsah_součtu]: toto je rozsah součtů, ze kterého by byly hodnoty přidány, pokud jsou splněna kritéria
Také v rozsah a rozsah_sumu Argument, zamkl jsem druhou část odkazu, takže když půjdeme dolů po buňkách, rozsah se bude stále rozšiřovat. To nám umožňuje pouze zvažovat a přidávat hodnoty do tohoto rozsahu (tedy běžící součty).
V tomto vzorci jsem jako kritéria použil sloupec záhlaví (tiskárna a skener). kritéria můžete také pevně zakódovat, pokud záhlaví sloupců nejsou přesně stejná jako text kritérií.
V případě, že máte více podmínek, které musíte zkontrolovat, můžete použít vzorec SUMIFS.
Celkem spuštěno v kontingenčních tabulkách
Pokud chcete přidat průběžné součty do výsledku kontingenční tabulky, můžete to snadno provést pomocí vestavěné funkce v kontingenčních tabulkách.
Předpokládejme, že máte kontingenční tabulku, jak je uvedeno níže, kde mám datum v jednom sloupci a hodnotu prodeje ve druhém sloupci.
Níže jsou uvedeny kroky k přidání dalšího sloupce, který bude zobrazovat průběžný součet prodejů podle data:
- Přetáhněte pole Prodej a vložte jej do oblasti Hodnota.
- Tím se přidá další sloupec s hodnotami prodeje
- Klikněte na možnost Součet prodeje2 v oblasti Hodnota
- Klikněte na možnost „Nastavení pole hodnoty“
- V dialogovém okně Nastavení pole hodnoty změňte vlastní název na „Spuštění součtů“
- Klikněte na kartu „Zobrazit hodnotu jako“
- V rozevíracím seznamu Zobrazit hodnotu jako vyberte možnost „Running Total in“
- V možnostech pole Základní zkontrolujte, zda je vybráno Datum
- Klikněte na Ok
Výše uvedené kroky by změnily druhý sloupec prodeje na sloupec Running Total.
Toto jsou tedy některé ze způsobů, jak můžete vypočítat průběžný součet v Excelu. Pokud máte data v tabulkovém formátu, můžete použít jednoduché vzorce a pokud máte tabulku v Excelu, pak můžete použít vzorce, které využívají strukturované odkazy.
Také jsem se zabýval tím, jak vypočítat celkový běh pomocí Power Query a v kontingenčních tabulkách.
Doufám, že jste našli tento návod užitečný.
