Rozšířený filtr v Excelu vám dává neskutečné možnosti, jak v Excelu rychle a snadno filtrovat. Rozšířený filtr je mnohem flexibilnější než automatický jednoduchý filtr v Excelu. A víte, co je nejlepší? Rozšířený filtr mnohdy nahradí i používání excelových funkcí. Takže pokud si chcete práci v Excelu trochu usnadnit, dejte funkci Rozšířený filtr šanci a podívejte se na toto video, jak s ním pracovat.
Excelový soubor ke stažení
Rozšířený filtr v Excelu
Jednoduchý filtr má jednu nevýhodu. Pokud na tabulku uplatníte jednoduchý filtr a tabulku vyfiltrujete v několika sloupcích, tak následně pouhým okem nevidíte, jaká byla vaše filtrovací kritéria. Abychom to zjistili, tak byste museli myší najet na záhlaví sloupce. U rozšířeného filtru dáváme kritéria do vlastních buněk mimo tabulku, takže je na první pohled patrné, jaká kritéria jsme pro filtrování použili.
Největší výhody rozšířeného filtru:
- Umí pracovat s částečnou shodou, to znamená, že umí pracovat se zástupnými znaky neboli wildcards
- Zvládá logické podmínky A i NEBO
- Výsledek lze zobrazit mimo filtrovanou tabulku
- Nahradí používání některých excelových funkcí
Rozšířený filtr najdeme na kartě Data, pod záložkou Seřadit a filtrovat a Upřesnit. Ti z vás, kteří používají anglický Excel zde budou mít napsáno Advanced.
V rozšířeném filtru máme možnost filtrovat přímo v seznamu, což znamená, že tabulka, kterou budeme filtrovat se vyfiltruje pouze na řádky, které splňují zadaná kritéria. Podobně jako když použijeme jednoduchý filtr v tabulce. Máme ale taky možnost vyfiltrovaný seznam zkopírovat do jiného místa v sešitu. Zdrojová tabulka tak zůstane nefiltrovaná a hodnoty, které budou splňovat kritéria se vyfiltrují do místa, které určíme.
Následně musíme definovat oblast seznamu, což je tabulka, kterou si přejeme filtrovat. Jako další zde máme oblast kritérií, kde definujeme, podle čeho se má tabulka filtrovat. Pokud chceme výsledné hodnoty zobrazit mimo zdrojovou tabulku, tak zde vybereme Kopírovat jinam a v Kopírovat do vybereme buňky, kde chceme data zobrazit.
Poslední možností je ještě filtrovat bez duplicitních záznamů.
Rozšířený filtr – základní filtrování
Začneme velmi jednoduchým příkladem. Jako zdrojovou tabulku použijeme tabulku s prodejními daty. V tabulce máme uvedené produkty, pobočky, klienty i tržby za jednotlivé produkty. Z této tabulky bychom potřebovali vyfiltrovat produkty Dámské košile. Někam vedle tabulky si tedy napíšeme kritérium Košile dámská (J2).
Prvním a nejdůležitějším pravidlem u použití rozšířeného filtru je to, že u kritéria musíme vždy použít rovněž záhlaví. V tomto záhlaví musí být stejný název jako je u sloupce, podle kterého chceme tabulku filtrovat. Bez názvů sloupců by Excel nebyl schopný poznat, který sloupec chceme filtrovat. Názvy sloupců musejí být totožné, nerozlišují se ovšem malá a velká písmena. V našem příkladu chceme filtrovat sloupec produktů dle dámských košilí, takže nad kritérium dámské košile napíšeme název sloupce (J1).
Název sloupce musíte vždy napsat nad kritérium, pokud byste název sloupce napsali například vedle, tak rozšířený filtr nebude fungovat.
Na kartě Data vybereme Upřesnit a necháme filtr přímo v seznamu. Pokud jste před kliknutím na rozšířený filtr klikli do tabulky, tak se jako oblast seznamu automaticky označí zdrojová tabulka, jelikož Excel je natolik chytrý, že většinou dokáže odhadnout, ze kterých dat budete chtít filtrovat. Pokud jste byli myší mimo tabulku, tak v poli Oblast seznamu provedete výběr tabulky. Jako oblast kritéria vybereme buňky, kam jsme zapsali kritérium (J1:J2). Označíme tedy jak záhlaví, kde máme napsáno produkt, tak kritérium.
Potvrdíme a tabulka se vyfiltrovala pouze na produkty Dámská košile.
Pokud bychom chtěli rozšířený filtr zrušit, tak na kartě Data v záložce Seřadit a filtrovat vybereme možnost Vymazat.
Rozšířený filtr – logická podmínka A
Řekněme, že budeme chtít tabulku dále filtrovat. Potřebujeme vyfiltrovat dámské košile a to pouze ty, které se prodaly v regionu Pardubice. Vedle kritéria Produkt tedy napíšeme Region (K1) a jako kritérium napíšeme Pardubice (K2).
Pokud kritéria píšete vedle sebe, tak se jedná o vyjádření logické podmínky A. Znamená to, že daný řádek ve zdrojové tabulce musí splňovat jak to, že je v něm produkt Dámské košile, tak to, že se v regionu vyskytují Pardubice. Obě dvě podmínky tak musejí platit zároveň.
Nezáleží přitom na pořadí sloupců, ve kterém uvádíte kritéria. Rozšířený filtr filtruje na základě shody v názvech sloupcích a nikoliv podle pořadí sloupců. Můžete tak klidně v tomto případě uvést kritéria v pořadí Produkt Region i Region Produkt. Obě varianty budou fungovat.
Jak jste si všimli, tak rozšířený filtr se automaticky neaktualizuje, pokud do kritérií něco připíšeme nebo pokud změníme původní kritérium. Kdykoliv něco v kritériích měníme, ubíráme nebo přidáváme, tak musíme filtr aktualizovat. Vrátíme se tedy k rozšířenému filtru a rozšíříme kritérium. Oblast seznamu je v pořádku, ale změním rozsah buněk u kritéria (J1:K2). Potvrdíme a tabulka se přefiltrovala.
Rozšířený filtr na neobsahuje hodnoty
Co kdybychom ale naopak chtěli vyfiltrovat tabulku pro Dámské košile, které se neprodaly v Pardubicích. Chceme tedy, aby se v tabulce objevily všechny řádky s dámskými košilemi, které ale neobsahují region Pardubice. K tomu v kritériu použijeme speciální znak, který se v Excelu používá pro vyjádření, že se něco nerovná. Jedná se o kombinaci znaku větší a menší, což v Excelu vždy vyjadřuje logickou podmínku nerovná se. Do buňky k Pardubicím tedy napíšeme <> a znovu uplatníme rozšířený filtr. Oblast seznamu zůstává stejná, ale aktualizujeme oblast kritérií (J1:K2). Potvrdíme a tabulka se přefiltrovala, takže teď ukazuje prodej dámských košilí bez regionu Pardubic.
Rozšířený filtr – logická podmínka NEBO
Rozšířený filtr zvládá pracovat i s logickou podmínkou NEBO. Logická podmínka NEBO se u rozšířeného filtru uplatní tak, že další kritérium napíšete do dalšího řádku. V dalším příkladu bychom kromě dámských košilí, které se prodaly v Pardubicích chtěli vyfiltrovat tabulku i pro Šaty v lokalitě Praha.
Jde nám tedy o logickou podmínku NEBO, takže kritéria musíme napsat pod sebe. Tabulka má vyfiltrovat řádky pro dámské košile nebo šaty. Pokud bychom tato kritéria náhodou napsali vedle, tak by se vrátila prázdná tabulka, jelikož nikde v tabulce nemáme na stejném řádku Dámské košile a Šaty dohromady.
Pod dámské košile tedy napíšeme šaty (J3) a vedle Praha (K3), uplatníme filtr a aktualizujeme oblast kritérií tím, že označíme o nově přidané řádky (J1:K3). Tabulka se vyfiltrovala za pomocí logické podmínky NEBO.
Rozšířený filtr – číselná kritéria
Kritérií můžeme tímto způsobem uplatnit kolik chceme, v podstatě tolik, kolik máte sloupců v tabulce. Zkusíme to ještě rozšířit a do kritérií přidáme ještě kritérium na hodnotu prodeje. Zajímají nás vyfiltrovaná data na dámské košile a šaty, jejichž hodnota byla vyšší než 3 000 Kč. Vedle kritérií tedy doplníme sloupec Tržba a zde uvedeme >3000 Kč. Na kartě Data vybereme Upřesnit a jako oblast seznamu necháme tabulku a jako oblast kritérií opět rozšíříme oblast s kritérii (J1:L3). Potvrdíme a z tabulky se odfiltrovaly všechny hodnoty, které byly menší než 3 000 Kč.
Pokud bychom chtěl vidět vyfiltrovaná data pro tržby větší než 3 000 Kč, ale zároveň nižší než například 4 000 Kč, tak bychom to samé kritérium napsali ještě jednou vedle (M1). Zde napíšeme menší než 4 000 Kč. Vybereme tedy Upřesnit a rozšíříme oblast kritérií (J1:M3), potvrdíme a tabulka se přefiltrovala. Stejnou logiku můžete uplatnit na jakékoliv jiné číselné hodnoty nebo datumy.
Rozšířený filtr – částečná textová shoda (wildcards)
Rozšířený filtr podporuje i vyhledávání na základě částečné shody, tedy použití wildcards. Řekněme, že chceme v dalším příkladu filtrovat košile, ale víme, že v tabulce nemáme pouze název košile, ale většinou zde máme košile dámské a pánské. Pokud bychom jako kritérium použili pouze slovo košile, filtr by vrátil prázdnou tabulku, jelikož samotné slovo košile se v tabulce nenachází. K vyhledání použijeme tedy wildcards. Do kritéria produktu napíšeme *Košile* (J6), jelikož nevíme, zda se slovo košile vyskytuje na začátku nebo na konci textového řetězce.
Na takto definované kritérium uplatníme rozšířený filtr a tabulka se vyfiltrovala na všechny produkty, které obsahují slovo košile. Stejně tak byste mohli chtít vyfiltrovat například pouze pánské produkty. Jako kritérium bychom tedy použili slovo pánské s dvěma hvězdičkami.
To samé můžeme udělat i pokud si nejsme jistí, jak je slovo v tabulce vyskloňované. Řekněme, že chceme opět dámské košile, ale nevíme zda máme v tabulce košile dámská, dámské nebo jinou vyskloňovanou variantu. I zde můžeme použít wildcard. Tentokrát využijeme zástupného znaku otazníku a napíšeme Košile dámsk? čímž zajistíme, že se najdou všechny varianty skloňování daného slova. Aktualizujeme filtr a tabulka se vyfiltrovala se všemi vyskloňovanými variantami slova dámské.
Rozšířený filtr – unikátní hodnoty
Řekněme, že bychom ze sloupce produkt chtěli dostat unikátní seznam produktů. Uplatníme tedy rozšířený filtr, a jako oblast seznamu vybereme pouze sloupec Produkt (D4:D503) a kritérium nevyplníme. Tentokrát nakopírujeme seznam vedle tabulky, takže vybereme Kopírovat jinam a vybereme třeba buňku J10. Potvrdíme filtr a vrátí se nám unikátní seznam produktů.
Jedna odpověď
Dobrý den,
velice obdivuji Váš projekt a rád jednotlivé návody prohlížím. U tohoto komentáře chci předem říci, že nejde o kritiku, ale nalezení řešení.
U odstavce “Rozšířený filtr – částečná textová shoda (wildcards)” není třeba dávat “Košile” do hvězdiček, i bez nich to vybere všechny košile. Toto beru jako nedostatek Rozšířeného filtrování.
Pokud například filtruji příjmení a mám ve sloupci Novák i Nováková, tak při zadání kritéria filtru “Novák” mi vyjedou oba. Můj dotaz je, jak nastavit filtr jen na “Novák”?
Přeji mnoho úspěchů a předem děkuji za čas, byť jen strávený čtením komentáře 🙂
Zdraví
M. Šilhan