pravděpodobně narazíte na potřebu spouštění součtů, pokud máte co do činění s jakýmkoli druhem denních dat.

Představte si, že sledujete prodej každý den. Vaše data obsahují řádek pro každé datum s celkovou částkou prodeje, ale možná budete chtít znát celkový prodej za měsíc v každý den. Jedná se o průběžný součet, je to součet všech prodejů do A včetně aktuálních dnů prodeje.

v tomto příspěvku se budeme zabývat několika způsoby, jak vypočítat celkový počet vašich denních dat. Prozkoumáme, jak používat vzorce listu, kontingenční tabulky, Power pivot s Dax a power query.

také prozkoumáme, co se stane s běžícím celkovým výpočtem při vkládání nebo mazání řádků dat a jak aktualizovat výsledky.

Získejte soubor se všemi příklady.

spuštění součtů pomocí jednoduchého vzorce

pomocí operátoru + je možné vytvořit základní běžící celkový vzorec.

k dokončení práce však budeme muset použít dva různé vzorce.

1. =C3 bude první vzorec a bude pouze v prvním řádku běžícího součtu.
2. =C4+D3 bude ve druhém řádku a lze je zkopírovat do zbývajících řádků pro běžící součet.

vzorec v prvním řádku nelze přidat buňku nad to celkem obsahuje textovou hodnotu pro sloupec nadpis. To by způsobilo hodnotu#! chyba, která se objeví v běžícím součtu, protože + nemůže zpracovat textové hodnoty. Tomu se vyhneme jiným vzorcem v prvním řádku, který neodkazuje na buňku výše.

Co se stane s běžícím celkem, když vložíme nebo odstraníme řádky v našich datech?

vložením nového řádku se vytvoří mezera v běžícím součtu. Chcete-li to opravit, budeme muset zkopírovat vzorec dolů z první buňky nad nově vloženými řádky až do posledního řádku.

odstranění všech řádků bude mít za následek #REF! chyby od odstranění řádku znamená odstranění buňky, na kterou odkazuje vzorec pod ní. Chcete-li to opravit, budeme muset zkopírovat vzorec z poslední bezchybné buňky až do posledního řádku.

Běží Součty s částkou Vzorec

vyhněte se trapnost s použitím dvou různých vzorců v našem běží celkem sloupec využitím funkce SUMA místo operátor+. Když funkce SUM narazí na textovou buňku, bude s ní zacházet stejně jako A, i když obsahovala 0.

tímto způsobem můžeme použít následující vzorec rovnoměrně pro každý řádek včetně prvního řádku.

=SUMA(C3,D2)

Tento vzorec bude odkazovat na záhlaví sloupce obsahující text pro první řádek, ale to tak v pořádku, jak je zacházeno jako 0.

při vkládání nebo mazání řádků se stále setkáme se stejnými problémy s prázdnými buňkami a chybami. Můžeme je opravit stejným způsobem jako při běžících součtech v jednoduché metodě vzorce.

spuštění součtů s částečně pevným rozsahem

Další možností s funkcí SUM je odkazovat pouze na sloupec prodeje a použít částečně pevný rozsah.

pokud použijeme následující vzorec =SUM ($C $ 3: C3), můžeme jej zkopírovat a vložit do rozsahu. Nebude odkazovat na žádné záhlaví sloupců a rozsah, na který se odkazuje, poroste na každý řádek.

bohužel i toto bude mít stejné problémy (a řešení) při vkládání nebo mazání řádků.

spuštění součtů s relativním pojmenovaným rozsahem

můžeme se vyhnout problémům s vkládáním a mazáním řádků z našich dat, pokud použijeme relativní pojmenovaný rozsah. To bude odkazovat na buňku přímo nad bez ohledu na to, kolik řádků vložíme nebo odstraníme.

Jedná se o trik, který zahrnuje dočasné přepnutí referenčního stylu aplikace Excel z A1 na R1C1. Poté definujte pojmenovaný rozsah pomocí notace R1C1. Poté přepnete referenční styl zpět na A1.

v referenčním stylu R1C1 jsou buňky označovány podle toho, jak daleko jsou od buňky pomocí odkazu. Například =RC odkazuje na buňku 2 nahoru a 3 napravo od buňky pomocí tohoto vzorce.

můžeme použít toto relativní odkazování k vytvoření pojmenovaného rozsahu, který je vždy o jednu buňku nad odkazující buňkou se vzorcem = RC .

Chcete-li přepnout styl odkazu, přejděte na kartu Soubor a vyberte Možnosti. Přejděte do části vzorce v nabídce možností aplikace Excel a zaškrtněte políčko referenční styl R1C1 a poté stiskněte tlačítko OK.

Nyní můžeme přidat náš pojmenovaný rozsah. Přejděte na kartu vzorce na pásu karet Excel a vyberte příkaz Definovat název.

Vložte název jako „výše“ jako název rozsahu. Přidejte vzorec = RC do odkazuje na vstup a stiskněte tlačítko OK.

Nyní můžeme přepnout Excel zpět na výchozí referenční styl. Přejděte na kartu Soubor > Volby Vzorec část > zrušte zaškrtnutí políčka styl odkazu R1C1 box > a poté stiskněte tlačítko OK.

Nyní můžeme použít vzorec =SUM (výše) v našem běžícím sloupci celkem.

výše uvedený rozsah bude vždy odkazovat na buňku přímo výše. Když vložíme nebo odstraníme řádky, relativní pojmenovaný rozsah se odpovídajícím způsobem upraví a není nutná žádná akce.

ve skutečnosti, pokud umístíme naše data do tabulky aplikace Excel, vzorec se automaticky vyplní pro všechny nové řádky, protože vzorec je jednotný pro celý sloupec. Ke zkopírování vzorců není nutná žádná akce.

běžící součty s kontingenční tabulkou

kontingenční tabulky jsou velmi užitečné pro shrnutí jakéhokoli typu dat. Je jich víc, než jen sčítání, počítání a hledání průměrů. Existuje mnoho dalších typů výpočtů vestavěných, a tam je vlastně běží celkový výpočet!

nejprve musíme vložit kontingenční tabulku založenou na datech. Vyberte buňku uvnitř dat a přejděte na kartu Vložit a vyberte příkaz kontingenční tabulky. Poté projděte okno Vytvořit kontingenční tabulku a vyberte, kam chcete kontingenční tabulku, buď v novém listu, nebo někde ve stávajícím listu.

Přidat pole Datum do oblasti Řádků kontingenční tabulky přidejte pole Prodej do Hodnoty oblasti kontingenční tabulky. Nyní přidejte další instanci pole prodeje do oblasti řádků.

nyní bychom měli mít dvě identická prodejní pole, přičemž jedno z nich bude označeno součtem Prodejů2. Můžeme přejmenovat tento štítek kdykoliv pouhým zadáním přes něj s něčím jako běží celkem.

klikněte pravým tlačítkem myši na některou z hodnot v poli Sum of Sales2 a vyberte Zobrazit hodnotu jako a poté zvolte Running Total In.

chceme zobrazit běžící součet podle data, takže v dalším okně musíme vybrat datum jako základní pole.

to je vše, nyní máme nový výpočet, který zobrazuje průběžný součet našich prodejů uvnitř kontingenční tabulky.

Co se stane, budeme-li přidat nebo odstranit řádek v náš zdroj dat, jak to ovlivní celkovou? Výpočty kontingenční tabulky jsou dynamické a budou brát v úvahu všechna nová data při běžícím celkovém výpočtu, stačí aktualizovat kontingenční tabulku.

klikněte pravým tlačítkem kdekoli uvnitř kontingenční tabulky a v nabídce zvolte Obnovit.

Běh součty s Power Pivot a Dax opatření

prvních pár kroků pro to jsou přesně stejné pomocí pravidelné kontingenční tabulky.

vyberte buňku uvnitř dat a přejděte na kartu Vložit a vyberte příkaz kontingenční tabulky.

Když přijdete k Vytvoření Kontingenční tabulky menu, podívejte se na Přidat tahle data do Datového Modelu políčko přidat data do datového modelu a povolit pro použití s power pivot.

umístěte pole Datum do oblasti řádků a pole Prodej do oblasti hodnot kontingenční tabulky.

s power pivotem budeme muset vytvořit další výpočty, které chceme pomocí jazyka DAX. Klikněte pravým tlačítkem myši na název tabulky v okně kontingenční tabulky a poté vyberte Přidat takt a vytvořte nový výpočet. Poznámka: Toto je k dispozici pouze s datovým modelem.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

Nyní můžeme vytvořit naši novou celkovou míru běhu.

1. v okně takt musíme přidat název taktu. V tomto případě můžeme nové opatření pojmenovat jako Running Total.
2. musíme také přidat výše uvedený vzorec do pole vzorců.
3. skvělá věc na power Pivotu je schopnost přiřadit formát čísla taktu. Můžeme zvolit formát měny pro naše opatření. Kdykoli použijeme toto opatření v kontingenční tabulce, formát se automaticky použije.

stiskněte tlačítko OK a vytvoří se nové takt.

V okně kontingenční tabulky bude uvedeno nové pole. Má malou ikonu fx na levé straně, která označuje, že se jedná o opatření a ne o pravidelné pole v datech.

můžeme použít toto nové pole stejně jako jakékoli jiné pole a přetáhnout jej do oblasti hodnot a přidat náš výpočet celkového běhu do kontingenční tabulky.

Co se stane s běžícím celkem, když přidáme nebo odebereme data ze zdrojové tabulky? Stejně jako běžná kontingenční tabulka stačí kliknout pravým tlačítkem myši na kontingenční tabulku a vybrat Aktualizovat pro aktualizaci výpočtu.

běžící součty s Power Query

můžeme také přidat běžící součty k našim datům pomocí power query.

nejprve musíme importovat tabulku do power query. Vyberte tabulku dat a přejděte na kartu Data a vyberte možnost z tabulky / rozsahu. Tím se otevře Editor power query.

dále můžeme třídit naše data podle data. Toto je volitelný krok, který můžeme přidat, takže pokud změníme pořadí našich zdrojových dat, bude se běžící součet stále zobrazovat podle data.

klikněte na přepínač filtr v záhlaví sloupce datum a zvolte Seřadit vzestupně z možností.

musíme přidat sloupec indexu. To bude později použito při výpočtu celkového běhu. Přejděte na kartu Přidat sloupec a kliknutím na malou šipku vedle sloupce Index vložte index začínající na 1 v prvním řádku.

musíme do našeho dotazu Přidat nový sloupec pro výpočet běžícího součtu. Přejděte na kartu Přidat sloupec a vyberte příkaz vlastní sloupec.

sloupec můžeme pojmenovat jako Running Total A přidat následující vzorec.

seznam.Funkce Range vytvoří seznam hodnot ze sloupce prodeje počínaje 1. řádkem (0. Položka), který zahrnuje počet řádků na základě hodnoty ve sloupci index.

seznam.Funkce Sum pak sčítá tento seznam hodnot, což je náš běžný součet.

sloupec indexu již nepotřebujeme, sloužil svému účelu a můžeme jej odstranit. Klikněte pravým tlačítkem myši na záhlaví sloupce a vyberte Odebrat z možností.

máme náš běžící součet a jsme hotovi s editorem dotazů. Můžeme zavřít dotaz a načíst výsledky do nového listu. Přejděte na kartu Domů editoru dotazů a stiskněte tlačítko Zavřít & načíst.

Co se stane s celkovou když jsme přidat nebo odebrat řádky z našich zdrojových dat? Budeme muset obnovit výstupní tabulku power query, abychom aktualizovali běžící součet se změnami. Klikněte pravým tlačítkem kdekoli na stole a zvolte Aktualizovat tabulku.

S volitelným třídění krok výše, přidáme-li data z objednávky do zdroje dat power query bude třídit podle data a vrátit správném pořadí podle data pro běh celkem.

závěry

existuje mnoho různých možností pro výpočet běžících součtů v aplikaci Excel.

prozkoumali jsme možnosti včetně vzorců v listu, kontingenčních tabulkách, vzorcích Dax power pivot a power query. Některé nabízejí robustnější řešení při přidávání nebo odebírání řádků z dat, jiné metody nabízejí snadnější implementaci.

jednoduché vzorce v listu se snadno nastavují, ale nebudou snadno zpracovávat vkládání nebo mazání nových řádků dat. Další řešení, jako jsou kontingenční tabulky, Dax a power query, jsou robustnější a zvládají vkládání nebo mazání řádků dat snadno, ale je těžší je nastavit.

je dobré si uvědomit výhody a nevýhody každé metody a vybrat tu nejvhodnější. Pokud nebudete vkládat nebo mazat nová data, pak mohou být vzorce listu cestou.