Funkce FILTER je za mě nejlepší funkce, která byla do Excelu přidána od dob funkce SUMA. Filtrování v Excelu vždy zahrnovalo spoustu manuální práce, a kdykoliv se změnila filtrační kritéria, tak bylo potřeba filtry aktualizovat atd. Funkce FILTER všechny tyto nedostatky odstranila a díky ní teď můžeme filtrovat dynamicky. Nebo aspoň ti, co mají Excely alespoň od verze 2021 nebo jsou předplatiteli Microsoft 365. Pokud nevíte, jak funkce FILTER funguje, tak se podívejte na předchozí video o funkci FILTER, které se vám právě teď ukázalo na obrazovce.
Excelový soubor ke stažení:
A teď se už pojďme podívat na několik triků s funkcí FILTER podívat.
Filtr dle seznamu
To, že můžeme ve funkci FILTER filtrovat pomocí více podmínek na více sloupcích to jsme si už ukazovali v několika předešlých videích. Ale věděli jste, že můžete ve funkci filtrovat i podle více podmínek na stejném sloupci? Řekněme, že potřebujeme z tabulky vyfiltrovat záznamy pro vybrané produkty. To znamená, že potřebujeme filtrovat ve sloupci produkt pro více produktů.
Existuje následující trik s funkcí COUNTIFS. Potřebujeme označit řádky, kde se vyskytují v tabulce tyto produkty. K tomu použijeme funkci COUNTIFS. Ve funkci COUNTIFS v parametru oblast kritérií označíme vybrané produkty, pro která chceme filtrovat data. A v parametru kritéria označíme sloupec produkt z excelové tabulky. Když tuto funkci potvrdíme, tak se jedničkami označí řádky, kde máme vybrané produkty.
A tuto funkci můžeme použít v parametru zahrnuje ve funkci FILTER. Před funkci napíšeme funkci FILTER, kde jako pole označíme celou excelovou tabulku a jako parametr zahrnuje následuje funkce FILTER. Funkce FILTER filtruje pravdu a pravda je v Excelu rovněž jednička, takže tato funkce FILTER teď vyfiltruje z excelové tabulky jen řádky, kde funkce COUNTIFS vrátí jedničku.
Funkci potvrdíme a funkce vyfiltruje vybrané řádky. Zkusíme změnit produkty v seznamu a funkce FILTER vrátí správné výsledky.
Částečná shoda textu
Pomocí funkce FILTER sice nemůžete filtrovat pomocí zástupných znaků neboli wildcards, ale i tak můžete filtrovat pomocí částečné shody textu. Ze zdrojové tabulky potřebujeme vyfiltrovat záznamy pro pobočku Praha. Nicméně ve sloupci pobočka nikdy nemáme uvedené jen slovo Praha, ale v tomto sloupci máme adresu i s ulicí. Klasické filtrování pouze pro Prahu by samozřejmě vrátilo prázdnou tabulku, jelikož přesná shoda se v tabulce nevyskytuje.
Použijeme následující zápis. Nejprve pomocí funkce HLEDAT neboli SEARCH ověříme, na kterých řádcích se vyskytuje hledané slovo. Tato funkce vrátí pořadové číslo, když funkce najde hledané slovo. To znamená, že na řádcích, kde funkce našla hledané slovo je číslo a na ostatních řádcích se vrátí chybová hláška. Tuto funkci ale kvůli chybovým hláškám nemůžeme použít přímo ve funkci FILTER.
Musím ji zabalit do funkce JE.ČÍSLO neboli IS.NUMBER. Tato funkce vrátí pravdu tam, kde najde číslo a pokud najde cokoliv jiného, tak vrátí nepravdu. Tuto funkci ale už ve funkci FILTER pro ověření podmínky použít můžeme.
Zabalíme funkci do funkce FILTER, kde v parametru pole označíme excelovou tabulku a jako parametr zahrnuje poslouží funkce JE.ČÍSLO.
Funkci potvrdíme a díky tomuto zápisu můžeme filtrovat i dle částečné shody textu.
FILTER vše
V dalším příkladu chceme použít funkci FILTER pro vyfiltrování produktů dle kategorie. Nicméně chceme rovněž zahrnout možnost, že si uživatelé budou moci v rozbalovacím seznamu vybrat možnost Vše. V takovém případě se vrátí v podstatě kopie zdrojové tabulky. Nejprve vytvoříme zdrojový seznam pro rozbalovací seznam. Použijeme funkci UNIQUE, ve které označíme sloupec Kategorie a tato funkce vrátí jedinečný seznam kategorií.
A ideálně nad tento seznam napíšeme ještě slovo Vše. Důvod, proč Vše píši nad seznam a nikoliv pod něj je ten, že pokud bychom přidali novou kategorii do seznamu, tak by se funkce UNIQUE neměla kam rozlít a došlo by k chybové hlášce #Přesah.
Teď použijeme funkci FILTER. Ve funkci FILTER označíme jako pole excelovou tabulku a jako parametr zahrnuje ověříme podmínku. Samozřejmě, že když toto potvrdíme, tak funkce bude fungovat dokud budeme vybírat kategorie, jakmile ale vybereme Vše, tak se nic nevrátí.
Trik spočívá v použití posledního nepovinného parametru, pokud prázdné. V tomto parametru můžeme určit, co se má vrátit, pokud funkce FILTER nenajde filtrační položku. A v takovém případě chceme vrátit celou excelovou tabulku.
Funkci potvrdíme a pokud vyfiltrujeme Vše v rozbalovacím seznamu, tak se vrátí celá tabulka.
Vybrané sloupce
Věděli jste, že pomocí funkce FILTER nemusíte filtrovat celou tabulku, ale že si můžete vybrat jakékoliv sloupce chcete? Stačí k tomu trik s funkcí ZVOLITSLOUPCE neboli funkcí CHOOSECOL. Do souhrnné tabulky chceme vyfiltrovat pouze záznamy ze zdrojové tabulky pro tyto sloupce. Ve funkci FILTER v poli ale můžeme označovat pouze spolu sousedící sloupce. Abychom toto omezení obešly, tak použijeme ve funkci FILTER funkci ZVOLITSLOUPCE. V této funkci nejprve označíme zdrojovou tabulku a následně si můžeme vybrat sloupce a co je nejlepší? Sloupce můžeme seskládat podle pořadí jak potřebujeme, nemusí jít tedy za sebou. Sloupce označujeme pořadovým číslem ze zdrojové tabulky.
Následně tuto funkci zabalíme do funkce FILTER, kde jako pole poslouží funkce ZVOLITSLOUPCE a v parametru zahrnuje stanovíme logickou podmínku, že se sloupec kategorie ze zdrojové tabulky musí rovnat vybrané kategorii.
Dynamická volba sloupce
Tento trik navazuje na předchozí trik, kde jsme si ukázali, jak vyfiltrovat pole jen pro vybrané a zpřeházené sloupce. Ale klidně můžete funkci FILTER využít i k tomu, že necháte uživatele si vybrat sloupec, pro který budou filtrovat. V souhrnné tabulce chceme vyfiltrovat záznamy jen pro tři sloupce, produkt, typ produktu a v posledním sloupci máme v záhlaví rozbalovací seznam, kde si můžeme vybrat, zda v něm zobrazíme Tržby, Náklady nebo Marže. K vytvoření této tabulky použijeme podobný trik jako v předchozím příkladu s malou úpravou.
Abychom byli schopní správně určit sloupec pro filtrování, tak musíme poznat, podle kterého sloupce se má filtrovat. A to poznáme podle záhlaví. A abychom vyfiltrovali pouze vybrané sloupce, tak víme, že musíme použít funkci ZVOLITSLOUPCE, která jako parametry potřebuje pořadová čísla sloupců. Jaká funkce vrací pořadová čísla sloupců podle názvů? Funkce POZVYHLEDAT neboli funkce MATCH.
Takže funkce POZVYHLEDAT, kde v parametru co označíme záhlaví souhrnné tabulky a kde tyto názvy hledáme? V záhlaví zdrojové tabulky a hledáme přesnou shodu. Tato funkce vrátí pořadová čísla sloupců. K vyhledání byste mohli použít i novou funkci XMATCH, kde jediným rozdílem je, že je přesná shoda v základním nastavení, takže nemusíte určovat, že hledáte přesnou shodu.
A tuto funkci zabalíme do funkce ZVOLITSLOUPCE. A následně to zabalíme do funkce FILTER.
Hotovo. Teď máte dynamickou tabulku, kde kromě filtru podle kategorie ještě filtrujete, zda se vám v posledním sloupci zobrazí tržby, náklady nebo marže.
