V dnešním videu si ukážeme základy jednoduchého filtrování v Excelu. Jednoduchý filtr v Excelu, neboli automatický filtr, slouží k tomu, abychom z velkého množství dat vyselektovali pouze data, která nás zajímají. V zásadě máme několik způsobů, jak v Excelu můžeme data filtrovat. Můžeme využít jednoduchého filtru, můžeme využít excelové funkce a nebo použít rozšířený filtr, tzv. advanced filtr. Dnes si ukážeme jak pracovat s jednoduchým filtrem.
Excelový soubor ze stažení
Základy práce s jednoduchým filtrem
Nejrychleji vložíme jednoduchý filtr do tabulky tak, že klikneme do tabulky, kterou chceme filtrovat a použijeme klávesovou zkratku CTRL+SHIFT+L. Tím se do záhlaví tabulky vloží jednoduchý filtr. Druhou možností, jak filtr do tabulky vložit, je že na kartě Domů, v sekci Úpravy vybereme Seřadit a filtrovat a Filtr. Pro odebrání filtru z tabulky postupujeme stejně jako u vložení. Buď klikneme do tabulky a znovu zmáčkneme klávesovou kombinaci CTRL+SHIFT+L nebo klikneme do tabulky a na kartě Domů vybereme Filtr a buď klikneme znovu na tlačítko Filtr, čímž se tlačítko deaktivuje nebo vybereme možnost Vymazat.
Další možností jak rychle vyfiltrovat hodnoty v tabulce je pomocí pravého tlačítka myši. Klikneme do tabulky na hodnotu, podle které si přejeme tabulku filtrovat. Řekněme, že chceme vyfiltrovat pouze obuv ve sloupci produkty, kliknu si tedy na první obuv v tabulce, kterou vidím a pravým tlačítkem myši vyberu možnost Filtr > a zde vyberu Filtrovat podle hodnoty vybrané buňky. Tím se do tabulky vloží jednoduchý filtr a rovnou se tabulka vyfiltruje pouze na obuv.
Stejně tak lze v tabulce filtrovat i dle barev. Řekněme, že máme některé buňky v tabulce označené barevně. Stačí v tabulce kliknout pravým tlačítkem myši na tuto barevnou buňku a vybrat možnost Filtr a Filtrovat dle barvy buňky. Tabulka se vyfiltruje podle buněk s příslušnou barvou. Stejný princip funguje i na filtr dle barvy písma. Filtrovat podle barev můžete i bez toho, že byste na hodnoty přímo klikali. Můžete rovněž otevřít jednoduchý filtr ze záhlaví tabulky a vybrat možnost Filtr dle barvy a vybrat požadovanou barvu, buď buňky a nebo písma.
To, že máte v tabulce aktivní filtr poznáte jednoduše podle toho, že se v záhlaví tabulky objeví ikony šipek. Pokud nějaký filtr uplatníte, řekněme, že vybereme například obuv ve sloupci produkty, tak se ikona šipky změní na ikonu filtru. To, že máte na tabulce uplatněný aktivní filtr poznáte i podle toho, že se čísla filtrovaných řádků změnila na modrou barvu. Rovněž vidíte, že čísla řádků nenavazují, což značí že jsou některé řádky skryté filtrováním.
Tabulka, kterou si přejete filtrovat by měla mít vždy záhlaví. Pokud Vaše tabulka mít záhlaví nebude, tak se filtr vloží na první řádek vaší tabulky.
Základní filtrování dat
V naší zdrojové tabulce prodejních dat ke stažení máme zhruba pět set řádků dat. Z těchto dat bychom potřebovali zjistit, kolik se prodalo obuvi v regionu Prahy. Začneme tedy tím, že do tabulky vložíme jednoduchý filtr. Klikneme do tabulky a buď použijeme klávesovou zkratku CTRL+SHIFT+L nebo vybereme možnost Filtr na hlavní liště. Do záhlaví tabulky se vložil jednoduchý filtr, což poznáme podle toho, že se v záhlaví objevily tlačítka se šipkami značící jednoduchý filtr.
Teď můžeme přistoupit k samotnému filtrování dat. Zajímalo nás, kolik se prodalo obuvi, takže ve sloupci druh produktu klikneme na filtr a v poli možností vybereme pouze obuv. Ostatní položky, které ve filtru nechceme stačí jednoduše odkliknout. Pokud máte v seznamu více položek, tak je rychlejší vybrat možnost Vybrat vše, tím se odškrtnou všechny položky a následně vybrat jen ty, které chcete v tabulce zachovat. Vybereme tedy obuv a potvrdíme. Tabulka se vyfiltrovala pouze na druh produktu obuv. Druhým kritériem bylo, kolik se prodalo obuvi v Praze. Musíme tedy uplatnit ještě druhý filtr na Region. Postup je stejný, klikneme v záhlaví sloupce na filtr a vybereme pouze region Praha. A máme vyfiltrovanou tabulku na data, která odpovídají našemu zadání.
Vlevo dole na spodním řádku vidíte, kolik hodnot se vám v tabulce po aktivaci filtru ukazuje.
Filtr datumových hodnot
Nejprve se podíváme na možnosti filtru u datumových hodnot. U datumových hodnot většinou filtr seskupí hodnoty dle roku. U každého roku vidíme toto malé plus. Pokud na něj klikneme, tak se rok rozbalí do měsíců. Měsíce mají zase znaménko plus, na které když kliknete, tak se měsíce rozbalí dále do jednotlivých dnů. Máte tak možnost vyfiltrovat data například jen pro rok, vybraný měsíc a nebo specifické dny. Co kdybychom ale chtěli vyfiltrovat všechny lednové hodnoty bez ohledu na rok? Abyste nemuseli rozklikávat každý rok, odškrtávat hodnoty a vybírat správný měsíc, tak můžete do tohoto vyhledávacího řádku napsat leden a označí se vám všechny hodnoty s lednem. Pokud bychom chtěli všechny ledny až na leden 2021, tak ho stačí odškrtnout a potvrdit výběr.
Kromě tohoto výběru můžete u kalendářních dat vybrat i možnost Filtry kalendářních dat. Můžeme například filtrovat hodnoty, které jsou po nebo před určitým datumem. Můžeme tak vyfiltrovat tabulku po červnu 2021. Vybereme filtr kalendářních dat, zde vybereme možnost po, a zde vyplníme datum. Nemusíme si lámat hlavu s formátem a můžeme použít tento kalendář a datum jednoduše vybrat. Vybereme tedy hodnoty po 1.6.2021. Potvrdíme a tabulka se vyfiltrovala.
Rovněž můžeme filtrovat tabulku mezi dvěma datumy. Zrušíme tento filtr tím, že klikneme do záhlaví a vybereme Vymazat filtr z datumu. Vybereme znovu Filtr kalendářních dat a zde vybereme možnost Mezi. Teď již stačí vyplnit datumy, mezi kterými si přejeme zobrazit hodnoty. Vybereme tedy třeba 1.1.2021 až 1.4.2021. Potvrdíme a tabulka se vyfiltrovala podle zadání.
Stejně tak můžete datumové hodnoty filtrovat dle minulého roku, minulého měsíce atd. Možností je opravdu spousta.
Filtr číselných hodnot
U číselných hodnot v tabulce můžete rovněž filtrovat manuálně, takže si dle ve výběru vyberete hodnoty, které vás zajímají a nebo můžete využít Filtr čísel. Řekněme, že nás zajímají tržby nad 4 000 kč, ve sloupci Tržba vybereme filtr a zde ve filtru čísel vybereme Větší než. Otevře se detailnější filtr hodnot, kde už stačí je napsat, že nás zajímají tržby větší než 4 000 Kč. Potvrdíme a tabulka se vyfiltruje jen na tyto hodnoty. Kromě hodnot větší a menší můžete filtrovat i dle pořadí hodnot. Řekněme, že chceme vyfiltrovat prvních 5 hodnot. Vybereme tedy možnost filtr a filtrovat čísla a zde vybereme Prvních deset. Jak vidíte zde, tak i přesto, že je tato položka nazvaná prvních deset, tak si zde můžete počet položek vybrat. A nejen prvních, ale i posledních položek. My jsme chtěli vidět prvních 5, tak že změníme hodnotu 10 na pět a potvrdíme.
Filtr textových hodnot
Spoustu možností nabízí i filtr textových hodnot. Zde kromě manuálního výběru můžeme provést i filtr textu. Filtrovat textové hodnoty můžeme tak, že do tohoto řádku napíšeme kus textu a filtr nám vrátí všechny textové hodnoty, které zadaný kus textu obsahují. Druhou možností je vybrat Filtr textu a zde specifikovat podrobnější filtrová kritéria. Můžeme například chtít filtrovat text, který neobsahuje slovo pánské. A z tabulky zmizely všechny hodnoty, které obsahovaly slovo pánské. Stejně tak zde můžete filtrovat i jen podle části slova. Je možné, že nevíme, jak jsou polobotky přímo v tabulce napsané, takže vyfiltrujeme pouze slova, která obsahují část textu polo. A tabulka vyfiltruje vše, co část tohoto slova obsahuje. Kromě polobotek se vyfiltrovaly i hodnoty pro Polo trička, jelikož stejně jako polobotky obsahují část textu polo.
Filtr logické podmínky NEBO
Jednoduchý filtr lze kromě logické podmínky A použít i k filtrování ve smyslu NEBO. Všechny filtry, které jsme si ukazovali až doteď používali logiku A, tedy že všechny aktivní filtry musely platit zároveň. Pokud byste chtěli uplatnit logickou podmínku NEBO, tak toho docílíte následovně. Ukážeme si to na sloupci s produkty. Řekněme, že chceme vyfiltrovat tabulku na produkty, které obsahují slovo pánská nebo šaty. Otevřeme tedy možnost filtru v záhlaví sloupce Produkt a vybereme Filtr textu a zde klikneme na Vlastní filtr. Jako první nás zajímaly produkty, které obsahují slovo pánská. Zde tedy najdeme možnost obsahuje a vedle napíšeme slovo pánská. A jelikož nás zajímá logická podmínka nebo, tak zde vybereme NEBO a znovu vybereme, že produkt obsahuje slovo šaty. Potvrdíme a máme vyfiltrovanou tabulku dle logické podmínky nebo. Všimněte si, že se vyfiltrovaly pouze šaty a pánské košile, jelikož jsme jako slovo uvedli pánská. Pokud bychom chtěli filtr na tabulku čehokoliv, co je pánské, tak bychom se k filtru vrátili a místo slovo pánská bychom napsali třeba jen pánsk. Tím bychom předešli problémům se skloňováním. Potvrdíme a tabulka se přefiltrovala na všechny pánské produkty a šaty.
Výpočty v tabulce s filtrem
Řekněme, že jsme s takto vyfiltrovanými daty spokojení a že byste teď chtěli sečíst tržby, které tedy obuv v Praze vydělala. Nad tabulkou jsme si nechali prázdný řádek, přesně pro tyto účely. Pokud by vás teď napadlo použít funkci SUMA, tak byste udělali chybu. A hned vám ukáži proč. Pokud nad sloupcem tržby napíšeme funkci SUMA a označíme tento sloupec, tak se vrátí hodnota přes milion korun. To ale na první pohled neodpovídá hodnotám, které máme uvedené ve vyfiltrované tabulce. To, že jsme něco udělali nesprávně se nám snaží napovědět i Excel, a to tímto zeleným trojúhelníkem v levém horním rohu. Tímto se nám Excel snaží říct, že jsme nezahrnuli do výpočtu všechny buňky. Rychlou kontrolu toho, zda máme součet správně můžeme provést i tím, že si data označíme a součet zkontrolujeme na spodní liště. Zde součet říká 48 520 Kč, což už by na první pohled našim hodnotám odpovídalo.
Kde jsme tedy udělali chybu?
Funkce SUMA totiž ignoruje vyfiltrovaná data a sčítá i vyfiltrované buňky. Proto zde máme součet mnohonásobně vyšší než kolik by měl být. A proto se zde objevil zelený trojúhelník. Jelikož nesčítáme celý sloupec, ale pouze některá data, což se Excelu nezdá být správně. A taky že není.
Pokud chcete provádět výpočty nad filtrovanou tabulkou, tak k tomuto účelu slouží funkce SUBTOTAL. Ta si totiž s filtrovanými daty dokáže poradit.
Pokud byste ale teď smazali funkci SUMA a nahradili jí funkcí SUBTOTAL, tak by se nejednalo o správný postup a ukáži vám proč. Pokud smažeme funkci SUMA a napíšeme SUBTOTAL, vybereme možnost SUMA, a označíme vyfiltrovaný sloupec, tak se pro obuv v Praze objeví správný výsledek. Pokud se ale rozhodneme filtrovat dále a změnit obuv například na oblečení v Praze, tak dle spodního kontrolního řádku by měl být součet těchto položek 66 205 Kč, ale funkce SUBTOTAL ukazuje 50 650 Kč. Důvodem je to, že jste použili funkci SUBTOTAL na již vyfiltrovaná data. Neoznačili jste tak celý sloupec dat, ale pouze část sloupce, což se vám opět snaží Excel ukázat zeleným trojúhelníkem.
Můžeme si to ověřit i tím, že zrušíme všechny aktivní filtry. Tím se vrátíme do původního rozložení tabulky. Klikněte si do funkce SUBTOTAL a podívejte se, které buňky se sčítají. Vidíme, že tím, že jsme funkci použili na již vyfiltrovaná data u Obuvi, tak jsme opravdu neoznačili všechna data. Správný postup je tedy tento. Ještě než začneme tabulku filtrovat, tak vložíme funkci SUBTOTAL, vybereme výpočet, třeba SUMU a označíme celý sloupec hodnot. Můžeme si pomoci klávesovou zkratkou CTRL SHIFT a šipka dolů. Celková suma sloupce by měla být 1 471 191 Kč. Pro kontrolu ověříme na spodním řádku a součty sedí. Teď můžeme přistoupit k filtrování dat. Znovu vybereme Obuv v Praze a tentokrát se vrátil správný součet. Znovu ještě změníme filtr z obuvi na oblečení a máme správný výsledek.
Filtrovat můžeme i několik položek najednou. Můžeme se tak vrátit k filtru region a kromě Prahy vybrat například i Brno a České Budějovice. Pokud bychom chtěli vybrat všechna města, tak se vrátíme k filtru a vybereme možnost Vybrat vše. Zůstal nám tedy filtr pouze na obuvi. Pokud bychom teď filtr z tabulky zrušili, například klávesovou zkratkou STRL SHIFT L, tak se všechny hodnoty vrátí nazpět a filtr se z tabulky odebere. Buď odškrtneme filtr a nebo je tam tlačítko vymazat.
A na co byste si při práci s filtry měli dát pozor?
U funkce SUBTOTAL si musíte pamatovat, že reaguje na aktivní výběr v tabulce. Pokud tedy vedle tabulky budu chtít provést součet obuvi v Praze, a napíšu zde funkci SUBTOTAL, tak se vrátí suma pro tržby obuvi v Praze. Pokud ale v druhém kroku filtr změním, tak bude aktivně reagovat i funkce subtotal. Což znamená, že máme rázem v tomto řádku špatný součet. Stejně tak, pokud provedu součet funkcí SUBTOTAL a následně filtr zruším, tak funkce SUBTOTAL bude reagovat a přepočítá se.
Nelze tedy vedle v tabulce provést několik součtů pomocí funkce SUBTOTAL a následně filtry měnit. Tato funkce se vám vždy aktivně přepočítá na výběr, který je zrovna aktivní. Pro tyto účely byste měli spíše využít funkce SUMIF, COUNTIF, SOUČIN.SKALÁRNÍ neb funkci dostupnou pro předplatitele služby Office 365 funkce FILTR.
Stejně tak je potřeba dát si pozor na to, co tvoříte nebo počítáte vedle tabulky, kterou plánujete filtrovat. Tím, že samozřejmě vyfiltrujete řádky, tak vám ty řádky zmizí, takže pokud plánujete vedle tabulky mít nějakou souhrnnou tabulku, tak vám budou ty řády mizet. Souhrnnou tabulku je tak lepší mít na jiném listě nebo například nad filtrovanou tabulkou. Prostě někde, kde vám aktivní filtr nebude ovlivňovat řádky. Pozor si dejte i na to, že vedle filtrované tabulky něco napíšete a následně tabulku odfiltrujete. Všimněte si, že jsme zde nerespektovali toto poučení a pouhým okem vidíme, že jsme jednu hodnotu napsali do řádku… a druhou na řádek…. Když teď odfiltrujeme tabulku, tak tyto vepsané hodnoty samozřejmě zůstanou na svých původních řádcích a celé se nám to rozjede.
