Funkce SUMA toho umí o tolik víc, než kolik se na první pohled zdá. A právě tyto triky, které vám dneska ukáži, jsou tím, co vás odliší od konkurence. Nejedná se jen o to, zda umíte udělat dynamickou kumulativní sumu, ale umíte rovněž ošetřit, že se vám řádek celkem bude automaticky sčítat, i po přidání nových řádků? Že ne? Po shlédnutí dnešního videa budete funkci SUMA ovládat jako profík.
Excelový soubor ke stažení
Funkce SUMA triky
Triky s funkcí SUMA, které musí znát každý excelový profesionál.
Automatická aktualizace funkce SUMA
Velmi častou chybou v Excelu je to, že po přidání nových hodnot zapomeneme aktualizovat vzorec SUMA o nové hodnoty. Tento problém lze vyřešit pomocí triku, který kombinuje funkci SUMA a funkci POSUN. Pod data, která chceme sčítat napíšeme funkci SUMA, kde označíme první buňku hodnot, v našem případě buňku B3. Neoznačíme ve funkci SUMA ovšem celé rozpětí, tak jako vždy, ale za buňku B3 napíšeme dvojtečku a bude následovat funkce POSUN. Funkce POSUN nám dovolí se v rámci hodnot dynamicky posouvat a vždy najít poslední hodnotu v seznamu. Ve funkci POSUN jako odkaz označíme styčný bod, kterým bude řádek celkového součtu. Jako druhý parametr řádky vyplníme -1, jelikož poslední hodnota v seznamu bude vždy o jeden řádek výše, než je řádek celkového součtu. Ostatní parametry funkce POSUN nepotřebujeme.
Druhou možností je, že nebudete chtít sčítat hodnoty přímo pod hodnotami, ale někde vedle tabulky. V takovém případě by funkce SUMA v kombinaci s funkcí POSUN vypadala následovně. Opět bychom začali funkcí SUMA, kde by jako první buňka byla první buňka hodnot, v našem případě buňka G3. Následuje dvojtečka a funkce POSUN. Parametrem odkaz by v tomto případě bylo záhlaví seznamu, v parametru řádek by byla hodnota 1, jelikož hodnoty začínají jeden řádek pod záhlavím. Parametr sloupce nepotřebujeme, ale potřebujeme parametr výška. Zde využijeme funkci POČET, ve které označíme celý sloupec hodnot. Tato funkce spočítá počet hodnot ve sloupci a díky tomu funkce SUMA vždy zahrne správný počet buněk.
SUMA dle nadpisů
Abychom nemuseli manuálně sčítat hodnoty dle nadpisů, tak jak to vidíme na obrázku č.3 v buňce C11, tak použijeme trik, kdy funkce SUMA sečte hodnoty dle nadpisů. Do buňky C16 napíšeme funkci SUMA, kde v samostatných závorkách označíme záhlaví tabulky (B2:M2) a porovnáme je s hodnotou, kterou chceme sečíst, což jsou lednové hodnoty (B16). Tato podmínka je prvním polem ve funkci SUMA, která identifikuje lednové nadpisy. Tam, kde bude ve zdrojové tabulce v záhlaví napsáno Leden, vrátí podmínková funkce jedničku. V ostatních případech vrátí nulu. Celou tuto podmínku vynásobíme celým polem hodnot (B3:M9). Tím zajistíme, že funkce SUMA sečte pouze hodnoty, u kterých je uvedený Leden. Jelikož se jedná o maticový vzorec, tak starší verze Excelů budou vzorec muset potvrdit stisknutím kláves CTRL+SHIFT+ENTER.
Kumulativní SUMA
Kumulativní SUMA znamená, že se k nové hodnotě na řádku přičítá hodnota z minulého řádku. Nejjednodušší způsob, jak kumulativní SUMU vytvořit je uvedený na obrázku č.4 ve sloupci D, kde jednoduše sčítáme předchozí řádek s řádkem novým. Kumulativní SUMU lze ovšem jednoduše vyřešit i pomocí funkce SUMA. Do funkce SUMA stačí v první buňce označit první hodnotu v seznamu hodnot, v našem případě buňku E3, napsat dvojtečku a opět označit buňku E3 (E3:E3). Trik s kumulativní SUMOU je v tom, že první buňku E3 musíte zafixovat klávesou F4, jak pro sloupce, tak řádky. Tím vznikne v poli SUMA dynamické rozpětí, jelikož tím, jak vzorec potáhnete dolů, tak první buňka zůstane v rozpětí zafixována a poslední se bude rozpínat, tak jak je vidět na obrázku č.4 ve sloupci F.
3D SUMA – sčítání z více listů
Trik s 3D SUMOU využijete při sčítání z více listů. Místo toho abyste jednotlivé hodnoty manuálně označovali a překlikávali se z listu na list, tak využijete trik s 3D SUMOU. Tento trik bude ale fungovat pouze ve chvíli, kdy máte jednotlivé listy stejně strukturované, to znamená, že hodnota, kterou chcete sečíst se na všech listech vyskytuje ve stejné buňce. V našem příkladu chceme sečíst hodnoty z buňky C9 z listů Leden, Únor a Březen. Do funkce SUMA tak napíšeme Leden:Březen!C9. Jako pole ve funkci SUMA jsme použili názvy listů. Nesmíme zapomenout na vykřičník, jelikož vykřičníkem Excel označuje, že se jedná o list v Excelu. Následně označíme buňku C9, což je buňka, kterou chceme na každém listu sečíst.
