V dnešním videu si ukážeme tři způsoby, jak v Excelu spočítat kumulativní součty. Od velmi základního způsobu s pomocí nekonzistentního vzorce, po kumulativní sumu až po součet pomocí nové funkce SCAN v kombinaci s funkcí LAMBDA, díky čemuž bude celý výpočet dynamický. Pro členy Akademie Excelu máme na webu rovněž navazující video o tom, jak vytvořit kumulativní SUMU v excelové tabulce, jelikož to není tak jednoznačné, jak si můžete myslet.
Excelový soubor ke stažení
Jak spočítat kumulativní součet
A vrhneme se rovnou na první výpočet.
Ve zdrojové tabulce máme datumy a hodnoty. Naším úkolem je spočítat kumulativní součty na každém řádku. To znamená, že na prvním řádku s lednem bude hodnota shodná s lednovou hodnotu, jelikož se jedná o první měsíc v tabulce. Na řádku s únorem už bude kumulativní součet ale ukazovat leden sečtený s únorem. Na březnovém řádku se bude zobrazovat součet lednové, únorové a březnové hodnoty atd.
Nekonzistentní vzorec
Začneme nejjednodušším způsobem, kterým je použití tzv. nekonzistentního vzorce.
První a nejjednodušším způsobem, jak kumulativní SUMU spočítat je použít tzv. nekonzistentní vzorec. To znamená, že bychom na prvním řádku propojili počáteční hodnotu s hodnotou v tabulce, jelikož v prvním měsíci je kumulativní součet roven první hodnotě.
Ve druhém měsíci potřebujeme mít součet předchozí a aktuální hodnoty v tabulce. Můžeme tedy napsat rovná se a k hodnotě z minulého měsíce přičíst hodnotu aktuální. Žádné buňky přitom nefixujeme, jelikož potřebujeme, aby se buňky posouvaly spolu se vzorcem. Právě proto se tomuto postupu říká nekonzistentní vzorec, jelikož v první buňce sloupce máme jiný vzorec než v buňkách následujících.
Vzorec potvrdíme a máme kumulativní součet předchozí a aktuální hodnoty. Tento vzorec teď můžeme označit a stáhnout ho pro ostatní řádky dolů.
Kumulativní SUMA
Druhou možností je použít tzv. kumulativní SUMU. Základem řešení je funkce SUMA. Ukážeme si to v dalším sloupci. Kumulativní SUMU vytvoříme tak, že ve funkci SUMA označíme první buňku, následuje dvojtečka a zase první buňka. Aby kumulativní SUMA fungovala, tak ale musíme první buňku v tomto rozpětí zafixovat pevně, jak pro řádky, tak sloupce, a to pomocí klávesy F4. Tím jsme ve funkci SUMA vytvořili dynamické rozpětí. Jak vzorec potáhneme dolů, tak první buňka zůstane zafixována na první hodnotě a funkce SUMA k ní na každém řádku bude přičítat aktuální hodnotu ze řádku. Funkci SUMA potvrdíme a stáhneme ji dolů pro všechny řádky.
U vzorce se vám objeví tyto zelené trojúhelníky, jelikož nás Excel upozorňuje na nekonzistentní vzorec. Toto upozornění je u kumulativní SUMY běžné, o žádnou chybu se nejedná, takže můžeme výstrahu ignorovat. Je zajímavé, že i přesto, že v prvním sloupci máme rovněž nekonzistentní vzorec, tak u prvního výpočtu nás Excel na tento nesoulad neupozornil.
Funkce SCAN
Předchozí dva způsoby byly klasickými způsoby, jak kumulativní součty v Excelu spočítat. Ti z vás, kteří používají Microsoft 365 a dynamické funkce mají možnost použít pro kumulativní součet pomocnou funkci LAMBDA funkci SCAN. Výhodou této funkce, i přesto, že není tak jednoduchá jako dva předchozí příklady, je v tom, že výsledek je dynamický a bude reagovat na přibývající a ubývající data v tabulce.
V dalším sloupci napíšeme funkci SCAN. Funkce SCAN uchovává mezi výsledky funkce LAMBDA. Ve funkci SCAN se nejprve označuje počáteční hodnota. Chceme hodnoty načítat, takže počáteční hodnota je nula. Následuje oblast hodnot, což jsou hodnoty, ze kterých chceme provést kumulativní součet. A posledním parametrem je funkce, což musí být funkce LAMBDA. Napíšeme tedy funkci LAMBDA, kde se nejprve určují parametry. Máme dvě hodnoty, počáteční hodnotu nulu a oblast hodnot, takže parametry definujeme třeba jako a, pro nulovou hodnotu a b pro oblast hodnot. A výpočet ve funkci LAMBDA je a+b, jelikož chceme kumulativní součet. Ukončíme závorky u funkcí a potvrdíme. Funkce SCAN doručila na každý řádek kumulativní součet. Jak vidíme, tak se jedná o dynamickou funkci, která se sama rozlila z jedné buňky do ostatních. Když do funkce klikneme, tak se celá oblast funkce modře orámuje, čímž poznáme, že se jedná o dynamickou funkci.
Pokud se chcete o dynamických funkcích a polích dozvědět více, tak na Akademii Excelu máme online excelový kurz Excel 365 Masterclass, kde se dozvíte vše o práci s novými dynamickými poli a funkcemi jako jsou funkce FILTER, UNIQUE, SROVNAT.SVISLE ROZDĚLIT.TEXT nebo funkce LAMBDA a její pomocné funkce. Odkaz na kurz najdete v popisku tohoto videa. Členové Akademie Excelu mají rovněž na nákup kurzu automatickou slevu.
Jedna odpověď
😊