FILTER v Excelu – pokročilejší praktické použití
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
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é.
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.