SUMA (SUM) prakticky v Excel

Úvodem do funkce SUMA (SUM)

Klasické použití funkce SUMA je jasné, už jsem o této funkci jeden článek napsal. Pokročilejší použití tipy a triky jsou sepsány v tomto článku:

• Klávesová zkratka Alt + = ale pro oblast najednou
• Suma z jiných listů - odkaz na jiné listy s trikem pro více přes *
• SUMA - klasicky, definovaný název, tabulka jako tabulka
• Dílčí součty nad oblastí - například posupně za jednotlivé měsíce
• Suma x největších a nejmenších hodnot
• Suma s funkcí POSUN (OFFSET) - dynamicky konec
• SUMA s INDEX
• SUMA s podmínkami
• ...

Klávesová zkratka

Použití klávesové zkratky Alt + = pro vložení sumy určitě znáte, ale bez problému lze v tabulce s data označit větší oblast a následně se do řádku s sloupců pomocí klávesové zkratky Alt + = vloží výpočty.

Alt + =

Ukázka

Suma z jiných listů

Opět odkaz na jiný list je jasný:

=List1!A1 případně v sumě rozsah: =SUMA(List1!A1:C1)

Stejně jako označujete oblast buněk, lze označit i oblast listů. Pozor! Záleží na pořadí listů.

Pro snadnější pochopení mám k dispozici 4 listy, které obsahují tato čísla:

Suma nad jednou buňkou z více listů. Pozor záměrně je list 2 a 3 přehozen (listy nejsou číselně za sebou).

=SUMA(List1:List3!A1) =SUMA(List1:List3!A1:C1) =SUMA(List1:List2!A1) =SUMA(List1:List2!A1:C1)

Trik

Co když se překliknete a místo =SUMA('List1'!A1:C1) zadáte =SUMA('List*'!A1:C1). Zaměníte číslo za hvězdičku! Funkce neskončí chybou, jak by člověk čekal, ale udělá rozsah nad všemi listmi, které obsahují List a cokoliv.

=SUMA('List*'!A1:C1)

Navíc Excel sám přepíše funkci třeba na:

=SUMA(List1:List4!A1:C1)

Co ale když zadáte třeba Z* a listů je více a nejsou u sebe?

=SUMA('Z*'!A1:C1)

Funkce využije další argumenty, ať může postihnout všechny listy obsahující Z

=SUMA(Základy!A1:C1;'Základy (2)'!A1:C1)

Ukázka

Poznámka pod čarou

Ta moje dyslexije je i k něčemu dobrá ;) Jen člověk zbytečně hledá chybu, proč nepočítá jak má ;) a přitom přijde na zajímavou "vychytávku".

SUMA

Sumu lze mít v klasickém výpočtu (oblast zadat od buňky do buňky), oblast přes definovaný název, nebo nejlépe počítat sumu nad tabulkou jako tabulka

=SUMA(C7:C13) =SUMA(PocetProdeju) =SUMA(Prodeje[Počet prodeju])

Ukázka

Dílčí součty nad oblastí

Mám prodeje v jednotlivých měsících a potřebuji mís součet prodejů do ledna, do února (tj. leden, únor) do března (tj. leden, únor, březen). Jak to provést.

Využít absolutní a relativní odkaz v rozsahu. Neboli když v buňce C7 je prodej za leden.

=SUMA($C$7:C7) =SUMA($C$7:C8)

Ukázka

Suma x největších a nejmenších hodnot

Potřebuji sumu tří největších (nejmenších) hodnot v oblasti.

=SUMA(LARGE(C7:C13;{1;2;3})) =SUMA(SMALL(C7:C13;{1;2;3}))

Jenže co když to budu chtít měnit dynamicky? Krásně mohu využít funkci NEPŘÍMÝ.ODKAZ (INDIRECT) a ŘÁDEK (ROW).

Maticově

Následující ukázky už musím zadávat maticově (Ctrl + Shift + Enter).

největší {=SUMA(LARGE(C7:C13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3"))))} nejmenší {=SUMA(SMALL(C7:C13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3"))))}

Což už je jen krok od odkazu na buňku kde bude poček kolik hodnot chci. Neboli v buňce C28 je číslo 3.

největší {=SUMA(LARGE(C7:C13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:"&C28))))} nejmenší {=SUMA(SMALL(C7:C13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:"&C28))))}

Ukázka

Poznámka

Jde využít kontingenční tabulku, PowerPivot, DAX, SOUČIN.SKALÁRNÍ, zde jde o ukázku použití maticového výpočtu.

Suma s funkcí POSUN (OFFSET)

Ve funkci SUMA lze využít i funkci posun, který mi řekne dolní omezení o řádek vyšší než je suma.

=SUMA(C7:POSUN(C12;-1;0))

Ukázka

SUMA s INDEX

INDEX ve spojení se SUMA lze využít pro procházení tabulek, kdy potřebuji sumu nad určitým vybraným sloupcem. Číslo sloupce mohu získat přes funkci SVYLHEDAT, ZVOLIT, nebo ho zadávat ručně ;).

=SUMA(INDEX(Data_pocty;0;C14))

Ukázka

Tip

Za mě lepší řešení Tabulka jako tabulka a funkce NEPŘÍMÝ.ODKAZ (INDIRECT).

SUMA s podmínkami

Ve funkci SUMA lze použít i podmínky a procvičit si maticové vzorce. V praxi doporučuji funkce SUMIFS, SOUČIN.SKALÁRNÍ. Zde z důvodu studijních a ukázek co Excel umí.

Předpokládám znalost maticových funkcí (vkládat přes Ctrl + Shift + Enter).

{=SUMA(KDYŽ((B6:B11)="GR";D6:D11;0))} {=SUMA(KDYŽ((B6:B11="GR")+(B6:B11="HR");D6:D11;0))} {=SUMA(KDYŽ((B6:B11)={"GR"};D6:D11;0))} {=SUMA(KDYŽ((B6:B11)={"GR"\"HR"};D6:D11;0))}

Ukázka

Závěrem

Používáte nějaký trik s funkci SUMA? Můžete zmínit v komentářích

Článek byl aktualizován: 19.09.2020 10:59

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.