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é.