Suma v Exceli trikrát inak

Pre výpočet súčtu sa najčastejšie používa funkcia SUM. Avšak tá niekedy nestačí. Napríklad neberie do úvahy filter ani skryté riadky a bunky s chybami jej robia problémy. V takýchto prípadoch je vhodnejšie použiť iné funkcie, napr. SUBTOTAL alebo AGGREGATE.

Ich použitie si ukážeme na jednoduchom príklade.

Funkcia SUM počíta súčet zo zadaného rozsahu.

=SUM(C2:C10)

Keď potrebujete sumu z vyfiltrovaných riadkov, tak funkcia SUM vôbec nereaguje. Stále poskytuje rovnaký výsledok. Rovnako je to aj v prípade, keď nejaké riadky skryjete.

Funkcia, ktorá bude zohľadňovať filter a skryté riadky, sa nazýva SUBTOTAL. Táto funkcia dokáže vypočítať nielen súčet, ale aj priemer, počet, súčin atď.

Prvým argumentom je práve číslo funkcie, ktorú chcete vypočítať. Súčet predstavuje 9 alebo 109. Číslo 9 použite v prípade, keď chcete zohľadniť iba nastavenie filtra. Číslo 109 v prípade, keď chcete, aby SUBTOTAL ignoroval aj skryté riadky. Ďalšie argumenty funkcie predstavujú bunky, z ktorých chcete vypočítať sumu.

=SUBTOTAL(9;C2:C10)

V prípade, že nie je nič vyfiltrované, tak obe funkcie SUM i SUBTOTAL poskytujú rovnaký výsledok.

Po vyfiltrovaní sa prejaví rozdiel. Funkcia SUM stále počíta so všetkými zadanými bunkami. Funkcia SUBTOTAL počíta iba s vyfiltrovanými.

Problém nastáva v prípade, ak sa medzi bunkami pre súčet nachádza aj chyba. Vtedy ani jedna z týchto funkcií nie je schopná poskytnúť výsledok.

V takomto prípade použite funkciu AGGREGATE, ktorá reaguje na filter, skryté riadky, chyby v bunkách a pod.

Prvým argumentom je číslo funkcie (suma = 9), druhým je práve to, čo potrebujete ignorovať (pri skrytých riadkoch a chybách to je číslo 7) a tretím argumentom sú bunky, z ktorých chcete vypočítať súčet.

=AGGREGATE(9;7;C2:C10)

Ak je všetko v poriadku, nie je nič vyfiltrované ani s chybami, tak všetky tri funkcie poskytujú rovnaký výsledok.

V prípade, že nastavíte filter a v nejakej bunke bude chyba, tak správny výsledok poskytne iba AGGREGATE.

Keď to zhrnieme, tak:

  • Funkcia SUM počíta so všetkými bunkami v zadanom rozsahu za každých podmienok. V prípade, že v niektorej bunke bude chybové hlásenie, tak jej výsledkom bude tiež chyba.
  • Funkcia SUBTOTAL počíta s vyfiltrovanými bunkami, prípadne s neskrytými bunkami. V prípade, že v niektorej bunke bude chybové hlásenie, tak jej výsledkom bude tiež chyba.
  • Funkcia AGGREGATE dokáže podľa nastavenia ignorovať vyfiltrované bunky, chybové hlásenia, iné funkcie SUBTOTAL a pod.

Je len na vás, ktorá z týchto funkcií vám najviac vyhovuje. Viac o funkciách v Exceli sa dozviete na našich kurzoch „Excel pre pokročilých“ alebo „Excel – funkcie podrobne“. Alebo chcete začať úplne od základov? Tak vás pozývame na „Excel pre začiatočníkov“. Prípadne nám napíšte na lapis@lapis.sk a prispôsobíme vám školenie na mieru podľa vašich požiadaviek.


Autor: Ing. Zuzana Pogranová, PhD.

Kontakt: lapis@lapis.sk