FILTER v Excelu – pokročilejší praktické použití

V tomto článku chci ukázat pokročilejší použití excelovské funkce FILTER. Je to jedna z funkcí Excelu, které mají výstup v dynamické oblasti.

Upozorňuji, že se nejedná o klasické filtrování v Excelu, ani o FILTER v DAXu.

Co bude naším úkolem?

Vyjdeme z této tabulky. Jsou v ní zvířata, která mají barvu, cenu a jsou zlevněná nebo ne.

Naším úkolem bude spočítat jedním vzorcem:

• celkovou cenu,
• která bude u relevantních položek snížená o 11
• a bude zahrnovat pouze položky, která jsou Blue nebo Pig (nebo Blue a Pig dohromady)

Výsledkem tedy má být:

• (1000-11) + (3000-11) +6000 + 7000 + 8000 = 24978

Jak na to?

Nejprve funkcí FILTER vyfiltrujeme položky, které jsou Blue.

To se zapíše takto:

• =FILTER(A:D;B:B="Blue")

Teď přidáme i ty, které jsou "Pig".

• =FILTER(A:D;(B:B="Blue")+(A:A="Pig"))

Plus je tam proto, že platí vztah "nebo". Pokud by mělo platit "a", tedy obojí současně, použili bychom hvězdičku (*).

Teď ve výsledku ponecháme jen sloupec s cenou:

• =FILTER(C:C;(B:B="Blue")+(A:A="Pig"))

Teď přidáme podmínku, která zlevněné položky sníží o 11 (a začneme ve vzorečku enterovat, abychom se do něj nezamotali...). Podmínka může pracovat i se sloupcem, který není výstupem funkce FILTER. Výsledkem FILTERu je sloupec C, ale podmínka pracuje se sloupcem A.

• =FILTER(
     IF(
        D:D="Yes";
        C:C-11;
        C:C);
     (B:B="Blue")+(A:A="Pig"))

Už jenom zbývá to celé zabalit do funkce SUMA a máme výsledek:

• =SUM(
     FILTER(
        IF(
           D:D="Yes";
           C:C-11;
           C:C);
     (B:B="Blue")+(A:A="Pig")))

Protože pracujeme s vloženou modrou tabulkou, mohli jsme vzorec zapsat, resp. v praxi spíše naklikat, i takto:

• =SUM(
     FILTER(
        IF(
           Table[Discount 11]="Yes";
           Table[Price]-11;
           Table[Price]);
     (Table[Color]="Blue")+(Table[Animal]="Pig")))

Něco podobného by šlo udělat i pomocí kombinace funkcí SOUČIN.SKALÁRNÍ a SUMIFS nebo maticových funkcí, ale řešení by nejspíš bylo méně přehledné.