Metoda 1. Vzorce
Pro zahřátí začneme tou nejjednodušší možností – vzorečky. Pokud máme jako vstup malou tabulku seřazenou podle data, pak pro výpočet průběžného součtu v samostatném sloupci potřebujeme základní vzorec:
Hlavním rysem je zde složité stanovení rozsahu uvnitř funkce SUM – odkaz na začátek rozsahu je absolutní (se znaky dolaru) a na konec – relativní (bez dolaru). Podle toho při kopírování vzorce dolů na celý sloupec dostaneme rozšiřující se rozsah, jehož součet vypočítáme.
Nevýhody tohoto přístupu jsou zřejmé:
- Tabulka musí být seřazena podle data.
- Při přidávání nových řádků s daty bude nutné vzorec rozšířit ručně.
Metoda 2. Kontingenční tabulka
Tato metoda je trochu složitější, ale mnohem příjemnější. A pro umocnění uvažujme vážnější problém – tabulku o 2000 řádcích dat, kde není řazení podle sloupce data, ale dochází k opakování (tj. můžeme prodat vícekrát ve stejný den):
Naši původní tabulku převedeme na „chytrou“ (dynamickou) klávesovou zkratku Ctrl+T nebo tým Domů – Formát jako tabulka (Domů — Formátovat jako tabulku)a poté na ní pomocí příkazu sestavíme kontingenční tabulku Vložit – kontingenční tabulka (Vložit — kontingenční tabulka). Datum vložíme do oblasti řádků v souhrnu a počet prodaného zboží do oblasti hodnot:
Upozorňujeme, že pokud máte ne zcela starou verzi Excelu, data jsou automaticky seskupena podle let, čtvrtletí a měsíců. Pokud potřebujete jiné seskupení (nebo je nepotřebujete vůbec), můžete to opravit kliknutím pravým tlačítkem myši na libovolné datum a výběrem příkazů Seskupit / Oddělit (Seskupit / Zrušit seskupení).
Pokud chcete vidět výsledné součty podle období i průběžný součet v samostatném sloupci, pak má smysl hodit pole do oblasti hodnot Prodáno znovu získat duplikát pole – v něm zapneme zobrazení průběžných součtů. Chcete-li to provést, klepněte pravým tlačítkem myši na pole a vyberte příkaz Dodatečné výpočty – Kumulativní součet (Zobrazit hodnoty jako – průběžné součty):
Tam můžete také vybrat možnost narůstání součtů v procentech a v dalším okně je třeba vybrat pole, pro které půjde akumulace – v našem případě je to pole datum:
Výhody tohoto přístupu:
- Velké množství dat je rychle načteno.
- Žádné vzorce není třeba zadávat ručně.
- Při změně ve zdrojových datech stačí souhrn aktualizovat pravým tlačítkem myši nebo příkazem Data – Obnovit vše.
Nevýhody vyplývají z toho, že se jedná o souhrn, to znamená, že si v něm nemůžete dělat, co chcete (vkládat řádky, psát vzorce, sestavovat libovolné diagramy atd.), přestane fungovat.
Metoda 3: Power Query
Načteme naši „chytrou“ tabulku se zdrojovými daty do editoru dotazů Power Query pomocí příkazu Data – z tabulky/rozsahu (Data – z tabulky/rozsahu). V nejnovějších verzích Excelu byl mimochodem přejmenován - nyní se nazývá S listy (Z listu):
Poté provedeme následující kroky:
1. Seřaďte tabulku vzestupně podle sloupce data pomocí příkazu Seřadit vzestupně v rozevíracím seznamu filtrů v záhlaví tabulky.
2. O něco později pro výpočet průběžného součtu potřebujeme pomocný sloupec s pořadovým číslem řádku. Přidáme to příkazem Přidat sloupec – Indexový sloupec – od 1 (Přidat sloupec — Indexový sloupec — Od 1).
3. K výpočtu průběžného součtu také potřebujeme odkaz na sloupec Prodáno, kde leží naše souhrnná data. V Power Query se sloupcům také říká seznamy (seznam) a odkaz na ně získáte kliknutím pravým tlačítkem na záhlaví sloupce a výběrem příkazu Detailování (Zobrazit detail). Výraz, který potřebujeme, se objeví na řádku vzorců, skládající se z názvu předchozího kroku #„Přidán index“, odkud vezmeme tabulku a název sloupce [Odbyt] z této tabulky v hranatých závorkách:
Zkopírujte tento výraz do schránky pro další použití.
4. Odstraňte nepotřebný další poslední krok Prodáno a místo toho přidejte vypočítaný sloupec pro výpočet průběžného součtu pomocí příkazu Přidání sloupce – Vlastní sloupec (Přidat sloupec – Vlastní sloupec). Vzorec, který potřebujeme, bude vypadat takto:
Zde je funkce Seznam.Rozsah vezme původní seznam (sloupec [Odbyt]) a extrahuje z něj prvky počínaje prvním (ve vzorci je to 0, protože číslování v Power Query začíná od nuly). Počet prvků k načtení je číslo řádku, které vezmeme ze sloupce [Index]. Takže tato funkce pro první řádek vrátí pouze jednu první buňku sloupce Prodáno. Pro druhý řádek – již první dvě buňky, pro třetí – první tři atd.
No a pak funkce Seznam.Součet sečte extrahované hodnoty a v každém řádku dostaneme součet všech předchozích prvků, tj. kumulativní součet:
Zbývá smazat sloupec Index, který již nepotřebujeme a nahrát výsledky zpět do Excelu příkazem Domů – Zavřít & Načíst do.
Problém je vyřešen.
Rychle a zběsile
V zásadě by to šlo zastavit, ale je tu malá moucha – námi vytvořený požadavek funguje rychlostí želvy. Například na mém ne nejslabším PC je tabulka o pouhých 2000 řádcích zpracována za 17 sekund. Co když je dat více?
Pro urychlení lze využít ukládání do vyrovnávací paměti pomocí speciální funkce List.Buffer, která jí daný seznam (seznam) jako argument načte do paměti RAM, což značně urychlí přístup k němu v budoucnu. V našem případě má smysl uložit do vyrovnávací paměti seznam #”Added index”[Prodáno], ke kterému má Power Query přístup při výpočtu průběžného součtu v každém řádku naší tabulky o 2000 řádcích.
Chcete-li to provést, v editoru Power Query na kartě Hlavní klikněte na tlačítko Pokročilý editor (Domů – Pokročilý editor), čímž otevřete zdrojový kód našeho dotazu v jazyce M zabudovaném do Power Query:
A pak tam přidejte řádek s proměnnou Můj seznam, jehož hodnotu vrací funkce ukládání do vyrovnávací paměti a v dalším kroku nahradíme volání seznamu touto proměnnou:
Po provedení těchto změn se náš dotaz výrazně zrychlí a vypořádá se s tabulkou o 2000 řádcích za pouhé 0.3 sekundy!
Další věc, že? 🙂
- Paretův graf (80/20) a jak jej vytvořit v Excelu
- Hledání klíčových slov v textu a ukládání dotazů do vyrovnávací paměti v Power Query