Metóda 1. Vzorce
Začnime, na zahriatie, tou najjednoduchšou možnosťou – vzorcami. Ak máme ako vstup malú tabuľku zoradenú podľa dátumu, potom na výpočet priebežného súčtu v samostatnom stĺpci potrebujeme základný vzorec:
Hlavnou črtou je tu zložité stanovenie rozsahu vo funkcii SUM – odkaz na začiatok rozsahu je absolútny (so znakmi dolára) a na koniec – relatívny (bez dolárov). Podľa toho pri kopírovaní vzorca nadol na celý stĺpec dostaneme rozširujúci sa rozsah, ktorého súčet vypočítame.
Nevýhody tohto prístupu sú zrejmé:
- Tabuľka musí byť zoradená podľa dátumu.
- Pri pridávaní nových riadkov s údajmi bude potrebné vzorec rozšíriť manuálne.
Metóda 2. Kontingenčná tabuľka
Táto metóda je trochu zložitejšia, ale oveľa príjemnejšia. A pre umocnenie si predstavme vážnejší problém – tabuľku s 2000 riadkami údajov, kde nie je triedenie podľa stĺpca dátumu, ale dochádza k opakovaniam (t. j. môžeme predávať viackrát v ten istý deň):
Prevedieme našu pôvodnú tabuľku na „inteligentnú“ (dynamickú) klávesovú skratku ctrl+T alebo tím Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku)a potom na nej pomocou príkazu zostavíme kontingenčnú tabuľku Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka). Do oblasti riadkov v súhrne vložíme dátum a do oblasti hodnôt počet predaných tovarov:
Upozorňujeme, že ak máte nie celkom starú verziu Excelu, dátumy sú automaticky zoskupené podľa rokov, štvrťrokov a mesiacov. Ak potrebujete iné zoskupenie (alebo ho nepotrebujete vôbec), môžete to opraviť kliknutím pravým tlačidlom myši na ľubovoľný dátum a výberom príkazov Zoskupiť / Zrušiť zoskupenie (Skupina / Zrušiť skupinu).
Ak chcete vidieť výsledné súčty podľa období aj priebežný súčet v samostatnom stĺpci, potom má zmysel hodiť pole do oblasti hodnôt Predané opäť získať duplikát poľa – v ňom zapneme zobrazovanie priebežných súčtov. Ak to chcete urobiť, kliknite pravým tlačidlom myši na pole a vyberte príkaz Dodatočné výpočty – Kumulatívny súčet (Zobraziť hodnoty ako – priebežné súčty):
Tam môžete tiež vybrať možnosť rastu súčtu v percentách a v ďalšom okne musíte vybrať pole, pre ktoré bude akumulácia ísť - v našom prípade je to dátumové pole:
Výhody tohto prístupu:
- Veľké množstvo údajov sa rýchlo načíta.
- Nie je potrebné zadávať žiadne vzorce ručne.
- Pri zmene v zdrojových údajoch stačí súhrn aktualizovať pravým tlačidlom myši alebo príkazom Údaje – Obnoviť všetko.
Nevýhody vyplývajú z toho, že ide o sumár, čiže už v ňom nemôžete robiť čo chcete (vkladať riadky, písať vzorce, zostavovať akékoľvek diagramy a pod.).
Metóda 3: Power Query
Načítajme našu „inteligentnú“ tabuľku so zdrojovými údajmi do editora dotazov Power Query pomocou príkazu Údaje – z tabuľky/rozsahu (Údaje – z tabuľky/rozsahu). Mimochodom, v najnovších verziách programu Excel bol premenovaný - teraz sa nazýva S listami (Z hárku):
Potom vykonáme nasledujúce kroky:
1. Pomocou príkazu zoraďte tabuľku vzostupne podľa stĺpca dátumu Zoradiť vzostupne v rozbaľovacom zozname filtra v hlavičke tabuľky.
2. O niečo neskôr, na výpočet priebežného súčtu, potrebujeme pomocný stĺpec s poradovým číslom riadku. Pridajme to príkazom Pridať stĺpec – Indexový stĺpec – od 1 (Pridať stĺpec — Indexový stĺpec — Od 1).
3. Na výpočet priebežného súčtu tiež potrebujeme odkaz na stĺpec Predané, kde sa nachádzajú naše súhrnné údaje. V Power Query sa stĺpce nazývajú aj zoznamy (zoznam) a ak chcete na ne získať prepojenie, kliknite pravým tlačidlom myši na hlavičku stĺpca a vyberte príkaz popisovať (Zobraziť detail). Výraz, ktorý potrebujeme, sa objaví na riadku vzorcov, ktorý pozostáva z názvu predchádzajúceho kroku #„Pridaný index“, odkiaľ vezmeme tabuľku a názov stĺpca [predaj] z tejto tabuľky v hranatých zátvorkách:
Skopírujte tento výraz do schránky pre ďalšie použitie.
4. Odstráňte nepotrebný posledný krok Predané a namiesto toho pridajte vypočítaný stĺpec na výpočet priebežného súčtu pomocou príkazu Pridanie stĺpca – vlastný stĺpec (Pridať stĺpec – vlastný stĺpec). Vzorec, ktorý potrebujeme, bude vyzerať takto:
Tu je funkcia Zoznam.Rozsah vezme pôvodný zoznam (stĺpec [predaj]) a extrahuje z neho prvky počnúc prvým (vo vzorci je to 0, pretože číslovanie v Power Query začína od nuly). Počet prvkov, ktoré sa majú načítať, je číslo riadka, ktoré vyberieme zo stĺpca [Index]. Takže táto funkcia pre prvý riadok vráti iba jednu prvú bunku stĺpca Predané. Pre druhý riadok – už prvé dve bunky, pre tretí – prvé tri atď.
No a potom funkcia Zoznam.Súčet spočíta extrahované hodnoty a v každom riadku dostaneme súčet všetkých predchádzajúcich prvkov, teda kumulatívny súčet:
Zostáva vymazať stĺpec Index, ktorý už nepotrebujeme a nahrať výsledky späť do Excelu príkazom Domov – Zavrieť & Načítať do.
Problém je vyriešený.
Rýchlo a zbesilo
V zásade by sa to dalo zastaviť, no je tu malá mucha – požiadavka, ktorú sme vytvorili, funguje rýchlosťou korytnačky. Napríklad na mojom nie najslabšom PC je tabuľka s iba 2000 riadkami spracovaná za 17 sekúnd. Čo ak je údajov viac?
Na zrýchlenie môžete použiť ukladanie do vyrovnávacej pamäte pomocou špeciálnej funkcie List.Buffer, ktorá načíta zoznam (zoznam), ktorý jej bol pridelený ako argument, do RAM, čo výrazne zrýchli prístup k nemu v budúcnosti. V našom prípade má zmysel uložiť zoznam #”Pridaný index”[Predané], ku ktorému má Power Query prístup pri výpočte priebežného súčtu v každom riadku našej 2000-riadkovej tabuľky.
Ak to chcete urobiť, v editore Power Query na karte Hlavné kliknite na tlačidlo Rozšírený editor (Domov – Pokročilý editor), čím otvoríte zdrojový kód nášho dotazu v jazyku M integrovanom do Power Query:
A potom tam pridajte riadok s premennou Môj zoznam, ktorého hodnotu vráti funkcia vyrovnávacej pamäte a v ďalšom kroku nahradíme volanie zoznamu touto premennou:
Po vykonaní týchto zmien sa náš dopyt výrazne zrýchli a vyrovná sa s 2000-riadkovou tabuľkou len za 0.3 sekundy!
Ďalšia vec, však? 🙂
- Paretov graf (80/20) a ako ho zostaviť v Exceli
- Vyhľadávanie kľúčových slov v texte a ukladanie dotazov do vyrovnávacej pamäte v Power Query