Rozšířený filtr - příklad využití výpočtového kritéria

Rozšířený filtr – filtrování dat pomocí výpočtového kritéria

Rozšířený filtr umožňuje filtrování dat na základě výpočtového kritéria. Tato podmínka je při volání rozšířeného filtru vyhodnocena pro každý záznam oblasti dat a musí být vytvořena tak, aby vracela pravdivostní hodnoty PRAVDA | NEPRAVDA (TRUE | FALSE). Hovoříme pak o vyhodnocení na základě výrazu.

Filtrování dat pomocí výpočtového kritéria

***Zkušební soubor ke stažení 03-08_rozsireny_filtr.xlsx***

Nejjednodušším a zároveň nejčastěji využívaným způsobem konstrukce výrazu je postup, kdy vytvoříte výpočetní vzorec  – například =PRŮMĚR($A$2:$A$100) – a výsledek tohoto vzorce porovnáte s hodnotou kritéria pomocí operátorů =, >, <. Pro větší přehlednost a zároveň zajištění správného postupu výpočtu a porovnání  je doporučováno uzavřít výraz do kulatých závorek.

Výsledný výraz může tedy vypadat například takto: =(A2>PRŮMĚR(($A$2:$A$100)). V tomto případě je naším cílem získat z oblasti dat pouze ty záznamy, ve kterých je hodnota zadaná do sloupce A vyšší než průměr všech hodnot zadaných do oblasti A2:A100.

Rozšířený filtr - příklad využití výpočtového kritéria

Rozšířený filtr – příklad využití výpočtového kritéria

Upozornění: Pokud filtrujete data pomocí vypočtené podmínky, musíte řádek kritérií, který by měl obsahovat nadpis sloupce, ponechat prázdný. Propojení výrazu na oblast dat není v tomto případě zajištěno shodou nadpisů sloupců, ale odkazy použitými ve výrazu. Zcela přesně řečeno můžete zapsat do prvního řádku oblasti text, kterým kritérium popíšete (například NADPRŮMĚR), ale tento text se nesmí shodovat s názvem žádného sloupce oblasti dat.

***Zkušební soubor ke stažení 03-08_rozsireny_filtr.xlsx***

Filtrování dat na základě výkonnostního kritéria

***Zkušební soubor ke stažení 03-09_rozsireny_filtr.xlsx***

V praxi velmi často stojíme před problémem získání záznamů ze sady hodnot, které představují n% nejvyšších nebo naopak nejnižších hodnot. Zajímá nás například skupina 10% nejúspěšnějších prodejců nebo naopak chceme získat přehled produktů, jejichž prodej nedosáhl ani 25% stanovené hodnoty KPI.

Pro podobné úlohy použijte vypočtené kritérium založené na funkci PERCENTIL(), která vrací k-tý percentil hodnot v oblasti. Například zadejte výraz =B2>=PERCENTIL($B$2:$B$20;0,9) pro získání všech záznamů, které patří do TOP 10% hodnot (tvoří 10% nejvyšších hodnot oblasti). Odkaz B2 směřuje na první buňku ve sloupci s hodnotami. Nezapomeňte odkaz na oblast hodnot v argumentu funkce PERCENTIL() zadat v absolutní adresaci – pokud ponecháte adresaci relativní, nebude rozšířený filtr vracet správné výsledky.

Rozšířený filtr - využití funkce PERCENTIL

Rozšířený filtr – využití funkce PERCENTIL

Pro snazší pochopení struktury funkce PERCENTIL můžete použít i alternativní zápis =B2>=PERCENTIL($B$2:$B$20;1-10%), ve kterém je hledané kritérium 10% přímo zapsáno.

Dalším častým příkladem je vyfiltrování záznamů, pro které se měřená hodnota nachází v zadaném pásmu (například ±10%) od zjištěného průměru nebo mediánu. V tomto případě můžete vypočtené kritérium doplnit o logické funkce A případně NEBO a nemusíte zapisovat 2 kritéria specificky pro dolní a pro horní mez: =A(B2>PRŮMĚR($B$2:$B$20)*0,9;B2<PRŮMĚR($B$2:$B$20)*1,1)

***Zkušební soubor ke stažení 03-09_rozsireny_filtr.xlsx***

Vložit komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *