FILTER jako SUMIFS na steroidech

Funkce SUMIFS (a obdobné COUNTIFS, AVERAGEIFS, MINIFS, MAXIFS) jsou notoricky známé a patří k těm nejpoužívanějším.

Mají ale své limity, k jejichž obejití můžete použít FILTER.

Logika FILTER vs logika SUMIFS

Logika FILTER a SUMIFSu a spol. je odlišná.

Logika SUMIFS 

SUMIFS udělá vlastně dvě akce – nejprve odfiltruje položky, které chcete, a pak sečte sloupec který potřebujete.

Třeba tady nejprve v tabulce vyfiltruje jen Fordy, a pak sečte jejich cenu.

• =SUMIFS(C:C;A:A;”Ford”)

Logika FILTER

FILTER pouze vyfiltruje tabulku, a vy pak použijete jinou funkci na sečtení výsledků (průměrování, maximum…).

Abychom jako v předchozím příkladu zjistili cenu Fordů, zapíšeme to takto:

• =SUM(FILTER(C:C;A:A=”Ford”))

Filtr tedy použijeme v kombinaci s jinou funkcí, výsledek je tady stejný jako u SUMIFSu.

V čem je FILTER lepší

FILTER je nepatrně složitější než SUMIFS (a spol.), má nicméně tři báječné výhody:

Univerzálnost pro jakýkoliv výpočet

SUMIFS umožňuje pouze sčítat, AVERAGEIFS průměrovat atd., MINIFS a MAXIFS hledat krajní hodnoty a COUNTIFS počítat řádky. FILTER ale umožňuje použít jakoukoliv agregační funkci. Třeba spojit texty pomocí TEXTJOIN, ale opravdu to může být prakticky cokoliv smysluplného z textových funkcí, statistických funkcí atd. FILTER je tedy mnohem univerzálnější.

Např. tady chceme vypsat všechny modely, které Ford nabízí. Nejprve FILTER omezí tabulku na Ford, pak UNIQUE odebere duplicity a pak to TEXTJOIN spojí do jedné buňky.

• =TEXTJOIN(“, “;1;UNIQUE(FILTER(B:B;A:A=”Ford”)))

Výsledkem je výpis všech modelů, které mají značku Ford.

Výpočet před agregací

FILTER umožňuje provést před agregací ještě další výpočet. Třeba tady – eurové částky přepočítáme na CZK kurzem 25, korunové necháme, a pak sečteme:

• =SUM(
     FILTER(
        IF(
           B:B=”CZK”;
           C:C;
           C:C*25);
        A:A=”a”))

V tomto případě vidíme podobnou logiku jako u DAXové funkce SUMX.

Více podmínek se vztahem “NEBO”

Pokud SUMIFS pracuje s více podmínkami, musí vždy platit všechny dohromady. FILTER lze ale definovat tak, aby platila jedna, druhá nebo obě.

Třeba takto sečteme ceny aut, která jsou Ford, nebo jsou červená, nebo obojí.

 Více o vícekriteriálním FILTERu tady.