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 - 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 |
Počet položek pomocí SUBTOTAL - bez filtru
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ý 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é 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é 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
