Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)

calc.png Dnešní díl kombinuje funkce, filtry a podmínky. Pomocí funkcí SUBTOTAL a SUMIF totiž můžete sčítat buňky spadající pod filtry nebo splňující zadané podmínky.  

Toto je další díl volného seriálu Funkce v Calcu.

Jestliže používáte v listu filtry pro zobrazování jen některých řádků z vložených dat a současně potřebujete pouze nad zobrazenými údaji provádět základní statistické operace, jako jsou součet, počet, minimum, maximum, průměr apod., jistě jste již zaznamenali, že použití funkcí jako SUM, COUNTA, MIN, MAX atp. není v takovém případě vyhovující, protože ty počítají se všemi buňkami zadané oblasti, ať již jsou vlivem použití filtru zobrazeny, nebo ne. Funkce, kterou v takovém případě potřebujete, se jmenuje SUBTOTAL. Její využití nejlépe osvětlí jednoduchý příklad.

Srovnání SUBTOTAL a SUM - všechny položky zobrazeny (bez filtru)Srovnání SUBTOTAL a SUM - všechny položky zobrazeny (bez filtru)

Srovnání SUBTOTAL a SUM - filtrovány položky s hodnotou 200Srovnání SUBTOTAL a SUM - filtrovány položky s hodnotou 200

Na prvním snímku jsou zobrazeny všechny řádky, ve filtru je tedy nastaveno „Vše". Na řádku 10 je součet vrchních buněk - ve sloupci A pomocí SUBTOTAL, ve sloupci B pomocí klasické SUMy. Oba výsledky se rovnají. Nyní použijte filtr - na druhém snímku je nastaven na hodnotu „200". Nyní je ve sloupci A suma pouze zobrazených, vyfiltrovaných položek, kdežto ve sloupci B je stále suma všech buněk ze zadané oblasti.

SUBTOTAL neprovádí pouze součet, ale umí více funkcí. Která z nich se použije, určuje první parametr. V našem příkladu to byl součet (SUM) s kódem 9. Jejich přehled je uveden v tabulce.

Číslo funkce
Funkce
1
AVERAGE
2
COUNT
3
COUNTA
4
MAX
5
MIN
6
PRODUCT
7
STDEV
8
STDEVP
9
SUM
10
VAR
11
VARP
V dalším příkladu je použita funkce COUNTA pro stanovení počtu záznamů, tedy řádků, které zůstanou zobrazeny po zapnutí filtru.

Počet položek pomocí SUBTOTAL - bez filtruPočet položek pomocí SUBTOTAL - bez filtru

Počet položek pomocí SUBTOTAL - filtrovány položky „muž"Počet položek pomocí SUBTOTAL - filtrovány položky „muž"

Funkce SUBTOTAL se hodí i v případech, kdy v tabulce provádíte mezisoučty. Její inteligence spočívá v tom, že z výpočtu vynechá ty buňky, které obsahují jinou funkci SUBTOTAL. Vše bude jasné opět s jednoduchého příkladu.

SUBTOTAL vynechá položky obsahující jiný SUBTOTALSUBTOTAL vynechá položky obsahující jiný SUBTOTAL

Vzorec na řádku 2 sčítá řádky 3-7, přitom ale vynechá řádek 4, ve kterém je další funkce SUBTOTAL, takže výsledek je správných 40. Kdybyste použili klasickou funkci SUM, výsledek by byl 60, protože by se započítala i hodnota 20 mezisoučtu z řádku 4. Na řádku 13 je pak součet všech položek z řádků 2 až 12, kdy SUBTOTAL vynechá mezisoučty z řádků 2, 4 a 8 (označeny okrově).

Jak již bylo uvedeno výše, SUBTOTAL nemusí jen sčítat, ale stejnou logikou lze použít pro průměr, počet, maximum, minimum, součin, aj.

Jestliže potřebujete sčítat jen hodnoty, které splňují určité kritérium, můžete využít funkci SUMIF. První, kratší způsob syntaxe, zachycuje následující příklad.

SUMIF s podmínkou ve sčítané oblastiSUMIF s podmínkou ve sčítané oblasti

Funkce sečte v zadané oblasti jen ty hodnoty, které vyhovují zadané podmínce. Na řádku 11 je podmínkou "<=10000", sečtou se tedy hodnoty 9500, 8700 a 7900.

Excel od verze 2007 obsahuje funkci SUMIFS, která umožňuje zadání více podmínek současně. Ačkoli s ní specifikace OpenDocument-formula počítá, v OpenOffice.org 3.0.1 ještě není implementována.

Při delším zápisu můžete testovat hodnoty v jedné oblasti a sčítat hodnoty z jiné oblasti. Opět krátký příklad a porovnání s funkcí SUBTOTAL.

SUMIF s podmínkou v jiné než sčítané oblastiSUMIF s podmínkou v jiné než sčítané oblasti

Na řádku 12 je vypočten průměrný věk pomocí SUBTOTAL. Při zobrazení všech položek ve filtru je výsledek stejný jako u AVERAGE na řádku 13. Na řádku 14 je spočten věkový průměr u záznamů „muž" - pomocí SUMIF(A2:A11;"muž";B2:B11) je sečten věk ze sloupce B u těch řádků, které ve sloupci A obsahují „muž" a pomocí COUNTIF(A2:A11;"muž") je určen jejich počet. Vydělením těchto hodnot získáte průměr. Na řádcích 15 je spočten průměrný věk pro ženy a na řádku 16 pro děti.

Jestliže pomocí filtru zobrazíte jen položky „muž", pak SUBTOTAL na řádku 12 spočte průměr jen u tří položek „muž" a výsledek bude shodný se vzorcem na řádku 14. Obdobné pak platí při vyfiltrování žen či dětí. Vzorec SUMIF/COUNTIF však počítá nezávisle na nastavení filtru.

V podmínce SUMIF můžete používat také regulární výrazy obdobně jako v COUNTIF. Pro použití regulárních výrazů ve vzorcích musí být aktivováno jejich vyhodnocování přes menu Nástroje | Volby... | OpenOffice.org Calc | Spočítat zaškrtnutím Povolit regulární výrazy ve vzorcích.

Uvedené příklady si může stáhnout: Funkce pro inteligentní součty

(Jako ve škole) Průměr: 1.05 | Hodnotilo: 19
 

Komentáře

user avatar jipi
Odpovědět
Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
5. 12. 2010, 19:47:02
jak vložit do kriterií fce "SUMIFS" odkazy na buňky případně na buňky v jiném listě případně vjiném sešitě.
Díky
jipi
user avatar hanus
Odpovědět
Re:Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
18. 10. 2011, 08:07:53
Vyhledávací/porovnávací kritérium/podmínku ve fci SUMIF můžete uvést fixně např. "muž", nebo odkazem na buňku, která obsahuje porovnávací kritérium. Odkaz na buňky v jiném listu či sešitu vytvoříte stejně, jako ty v aktuálním - když máte vložit odkaz na buňku, myší se přepnete do jiného listu/sešitu, označíte buňku či oblast a pokračujete pak dále v zápisu funkce (obvykle středníkem nebo ukončením vkládání vzorce).
user avatar Jitka
Odpovědět
Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
25. 09. 2011, 13:29:11
Díky za dobrý návod.:)
user avatar Koťas
Odpovědět
Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
11. 06. 2014, 19:00:00
Dobry den, jak bych mohl pro SUBTOTAL vlozit jeste nejakou podminku?

Konkretneji, mam prvni tabulku, kde v A sloupci mam jmena (kazdy clovek se muze opakovat, tedy stejne jmeno byt vicekrat), v B sloupci vydaje v Kc a v C sloupci datum. A pak na novem liste druhou tabulku, kde mam v D sloupci jmeno (kazde prave jednou) a v E sloupci soucet vydaju pro dane jmeno (tzn soucet hodnot B sloupce prvni tabulky, kde se rovnaji hodnoty jmena v A a D sloupcich).

Udelat samotny soucet v E sloupci je mi jasne - napr.
SUMIFS(list1!B:B;list1!A:A;list2!D:D)

Lze to nejak scitat tak, aby to ignorovalo filtrem schovane radky/jmena - tzn kdyz napriklad na prvnim listu pro C sloupec vyfiltruji jen nejaky rok, tak aby to na druhem listu v E sloupci scitalo jen vydaje v danem roce?

Velmi dekuji za pripadnou radu..
user avatar Honza
Odpovědět
Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
26. 05. 2016, 23:40:19
Potřeboval bych vyřešit problém.. mám tabulku která se každým dnem mění a přidávají se tam hodnoty (splátkový kalendář) potřebuji vytvořit sloupec kde se je rozepsaná částka dluhu do měsíců ale nesmí přesáhnout 5000kč pokud se tak stane potřebuji aby se hodnota nad daných 5000 začala započítávat o řádek níže tedy na další měsíc. Jedná se o splátky k telefonnímu číslu který je stále aktivní. Moc děkuji za pomoc
user avatar neutr
Odpovědět
Re:Funkce pro inteligentní součty (zejména SUBTOTAL, SUMIF)
27. 05. 2016, 06:20:57
Honzo sem takovýhle dotaz nepatří. je to diskuse k danému tématu - která tedy je značně zastaralá - nejméně 7 let. Nikdo sem na stará témata nechodí. K tomuto účelu existuje fórum.

Najděte na fóru sekci Calc, a tam založte nové téma. Před tím se musíte samozřejmě registrovat - ale nikdo při tom nezkoumá Vaši kreditku ani DNA takže se toho nebojte.

Odpovědět

 

Nejsou podporovány žádné značky, komentáře jsou jen čistě textové. Více o diskuzích najdete v nápovědě. Diskuzi můžete sledovat pomocí RSS kanálu.

 
 
 
woo jaw demo hz