MS Excel – Filtr automatický, rozšířený Obsah kapitoly
V této lekci se seznámíme s nástrojem, který se používá pro výběry dat z rozsáhlých tabulek – s filtrem automatickým a rozšířeným.
Studijní cíle
Studenti budou umět zobrazovat v databázích jen potřebné řádky, a z dat rozsáhlých tabulek budou umět vytvářet tabulky nové, obsahující jen vybrané údaje seznamů původních.
Doba potřebná ke studiu
Pro přečtení výkladu, řešeného příkladu a vypracování samostatného dobrovolného úkolu minimálně 60 minut
Pojmy k zapamatování
Filtr Automatický filtr Rozšířený filtr Kriteriální tabulka, oblast kritérií
Úvod
Často se při práci s tabulkami programu MS Excel setkáváme s problémem, jak z dat rozsáhlého seznamu vybrat jen některé řádky, ev. jak vytvořit z dat rozsáhlého seznamu novou tabulku pouze s některými údaji původního seznamu. Pro podobné výběry se v prostředí programu MS Excel používá nástroj zvaný Filtr. Program nabízí filtry Automatický a Rozšířený, oba najdeme na kartě Data.
Výkladová část
Automatický filtr je jednoduchý na nastavení i použití. Pomocí automatického filtru můžeme vybírat pouze záznamy databáze a to podle hodnot nebo nastaveného formátu buněk vybraných polí (toto neumí starší verze programu). Obě možnosti ale nemůžeme navzájem kombinovat, vybíráme jen podle hodnot, nebo jen podle formátu.
Automatický filtr
Pro nastavení a využití automatického filtru umístíme aktivní buňku kamkoliv do databáze a na kartě Data, ve skupině Seřadit a filtrovat, klepneme na tlačítko Filtr, nebo na kartě Domů rozbalíme nabídku ikony Seřadit a filtrovat a vybereme Filtr. Pokud bychom označili nějaká data, bude program pracovat jen s vybranou skupinou buněk.
V prvním řádku polí seznamu se objeví rozbalovací šipka. Klepnutím na šipku u vybraného sloupce si zobrazíme nabídku příkazů, které umožní nastavit řazení a/nebo výběr záznamů tabulky podle hodnot nebo barev buněk tohoto pole. Příkazy přístupné v zobrazené nabídce vycházejí z vlastností buněk tohoto pole. Po zadání podmínky pro výběr záznamů zobrazí program v tabulce jen ty řádky seznamu, které splňují zadanou podmínku, ostatní řádky skryje. Vybraná data pak můžeme tisknout, kopírovat, vytvářet z nich graf,… Pokud po nastavení automatického filtru a zadání podmínky pro výběr řádků klepneme na tlačítko Součet (automatické shrnutí), bude místo funkce Suma použita složitější funkce Subtotal, která na rozdíl od funkce Suma nepočítá se skrytými hodnotami. Nejjednodušší výběry můžeme realizovat pomocí seznamu hodnot vybraného pole, který program zobrazuje v dolní části rozbalené nabídky. Pomocí zaškrtávacích políček určujeme hodnoty, které se ve vybraných (zobrazených) řádcích mají/nemají vyskytovat. Např. na obrázku dole jsme ve sloupci Typ studia klepli na rozbalovací šipku automatického filtru a klepnutím jsme vyprázdnili políčko před KS. Zobrazíme tak pouze ty řádky, ve kterých je ve sloupci Typ studia uvedena hodnota PS.
Výsledek předchozího výběru:
Pokud je v některém poli zadaná podmínka pro výběr, změní tlačítko v záhlaví pole svůj vzhled. Čísla vybraných řádků jsou zbarvena modře a ve stavovém řádku program oznamuje, kolik řádků z celkového počtu vyhovělo naší podmínce.
Obecnější podmínky pak zadáváme pomocí nabídky, kterou nabízejí (podle hodnot vybraného pole) řádky Filtry textu/Filtr kalendářních dat/ Filtr čísel. Nabídky jsou vidět na obrázcích dole. Filtr textu
Filtr čísel
Filtr kalendářních dat
Podmínky pro výběr můžeme zadávat v několika polích současně. Každý další výběr pracuje se záznamy vybranými předchozím výběrem – podmínky zadané pro různá pole budou platit současně (logické A), např. Typ studia KS a Místo bydliště Praha.
Zrušení podmínky pole a všech podmínek výběru
Pokud potřebujeme zrušit jednu z nastavených podmínek filtru, stačí v rozbalené nabídce filtru příslušného pole vybrat (Vymazat filtr z …). Touto akcí zrušíme podmínku výběru pouze v tomto poli, v ostatních zůstanou podmínky dále nastavené.
Pokud potřebujeme odebrat všechny podmínky, je rychlejší na kartě Data, ve skupině Seřadit a filtrovat, klepnout na tlačítko Vymazat. Pokud jsme na kartě Domů, můžeme zrušit nastavené podmínky tak, že rozbalíme nabídku tlačítka Seřadit a filtrovat a tlačítko Vymazat vybereme tam. Zrušení (vypnutí) celého filtru
Automatický filtr se vypíná tam, kde se nastavuje. Na kartě Domů rozbalíme nabídku tlačítka Seřadit a filtrovat a vybereme Filtr, nebo na kartě Data, ve skupině Seřadit a filtrovat, klepneme na Filtr. S vypnutím filtru se zruší všechny nastavené podmínky pro výběr záznamů, vracíme se tedy k zobrazení všech záznamů databáze. Pokud pro úpravu vzhledu tabulek použijeme na kartě Domů tlačítko Formátovat jako tabulku, nastaví se mimo vybraného formátu v tabulce i automatický filtr. Na jednom listě sešitu nemůžeme používat najednou automatický filtr na dvou různých tabulkách.
Rozšířený filtr
Při zadávání složitějších podmínek pro výběr záznamů z databáze nám možnosti automatického filtru nebudou stačit. Nedá se pomocí něj např. zadat podmínka se vztahem nebo mezi poli (např. vybíráme odpracované hodiny o víkendu (den týdne sobota nebo neděle) nebo v noci (čas mezi 22:00 a 6:00 ráno), nebo výběry, kdy hodnotu v buňce srovnáváme s obsahem jiné buňky (např. hodnoty převyšující vypočítaný průměr). Musíme pak použít tzv. Rozšířený filtr. Rozšířený filtr použijeme i v případě, že z vybraných dat potřebujeme vytvořit novou tabulku, nebo když vybíráme jedinečné hodnoty (z několika stejných hodnot jednoho pole nám stačí jen jedna). Možnosti rozšířeného filtru využijeme i tehdy, když potřebujeme vybrat nejen záznamy, ale i pole původního seznamu.
Kriteriální tabulka
Pro rozšířené filtry se podmínky pro výběr záznamů zadávají do tzv. oblasti kritérií (kriteriální tabulka). Tu vytvoříme tak, že do buněk mimo původní seznam (nezapomeneme nechat alespoň jeden prázdný sloupec nebo řádek), napíšeme vedle sebe názvy těch polí filtrované tabulky, podle jejichž hodnot budeme vybírat. Názvy musí být naprosto stejné jako
v původním seznamu, proto je lepší je z původní tabulky zkopírovat. Můžeme zkopírovat i celé záhlaví tabulky. Pod vytvořené názvy polí pak píšeme podmínky pro výběr záznamů. Podmínky, které napíšeme do jednoho řádku, budou platit současně (logické A), mezi podmínkami napsanými v různých řádcích bude platit vztah nebo (stačí splnění jedné z nich). Pozor na prázdné řádky!!! Při zadávání podmínek výběru se používají relační operátory =, <, > a jejich kombinace <=, >=, <>. Při zápisu podmínek se dá také využívat tzv. hvězdičková konvence – náhrada libovolně velké skupiny znaků znakem *, náhrada právě jednoho znaku znakem ?. U textů program nerozlišuje velikost písmen. Při testování na rovnost čísel se znaménko = psát nemusí. Ukázky podmínek: =100
Rovná se 100 (totéž jako kdybychom napsali jen 100)
<>50
Nerovná se 50
=">"&G2
Je větší než obsah buňky G2. Program výraz vyhodnotí, v buňce uvidíme výsledek výrazu, např. >1985, pokud byla v buňce G2 hodnota 1985.
nov
Začíná písmeny nov (vyhovuje Novák, Novotný, Novosad, nová, novější,…)
*nov*
Obsahuje písmena nov (na začátku, na konci nebo uprostřed)
*nov
Stejně jako v předchozím, obsahuje písmena nov kdekoliv
'=nov
Rovná se nov. Bez uvozovky na začátku by program tento zápis považoval za vzorec s pravděpodobně neexistujícím názvem nov. Aby nedošlo k hlášení chyby programem, je potřeba napsat podmínku do buňky s apostrofem před znakem = ('=nov).
'=*ová
Končí na ová (např. většina příjmení českých žen). Kdybychom napsali jen *ová, vybral by program i např. Sovák.
Ukázky kriteriálních tabulek: Pomocí této kriteriální tabulky bychom ze seznamu vybrali záznamy, ve kterých je v poli Den napsáno sobota nebo neděle bez ohledu na hodnotu v poli Čas, a ty řádky, ve kterých je v poli Čas hodnota větší než 22:00 nebo menší
než 6:00 bez ohledu na den uvedený v poli Den. Pomocí této kriteriální tabulky bychom vybrali záznamy všech z třídy 1. A, kteří mají ve sloupci Kg papíru číslo větší než 45, a všechny z 1. B bez ohledu na Kg papíru. Pomocí této kriteriální tabulky bychom vybrali studenty typu studia KS z Mostu a Zlína a studenty typu studia PS z Chrudimi.
Pokud vytvoříme kriteriální tabulku, můžeme přistoupit k výběru. Aktivní buňku umístíme do filtrované tabulky a na kartě Data, ve skupině Seřadit a filtrovat, klepneme na tlačítko Upřesnit. Program otevře dialogové okno Rozšířený filtr, které vyplníme.
Oblast seznamu je naše databáze. Pokud jsme při vyvolání dialogového okna měli aktivní buňku v seznamu, program adresu oblasti vyplní sám, jinak musíme oblast vybrat (včetně názvů polí). Oblast kritérií – ukážeme připravenou kriteriální tabulku včetně názvů sloupců!!!!. Program podle uvedených názvů vyhledává příslušná pole databáze. Pokud s vybranými daty potřebujeme dále nějak pracovat, můžeme je pomocí volby Kopírovat jinam vybrat mimo původní seznam. Pokud tuto možnost zvolíme, zpřístupní se pole Kopírovat do. V listě ukážeme buňku, od které se mají vybrané řádky do listu vložit. Pokud vybíráme jedinečné hodnoty, zaškrtneme volbu Bez duplicitních záznamů. Pokud z původního seznamu vybíráme nejen řádky, ale i pole, musíme podobně jako u kriteriální tabulky zadat (nejlépe zkopírováním) mimo původní seznam názvy vybíraných polí. Jejich adresu pak zadáme v dialogovém okně Rozšířený filtr do pole
Kopírovat do. Nastavení potvrdíme. Rozšiřující text
Prostudujte si také řešený příklad. Pro lepší zvládnutí nástroje si můžete také zkusit vypracovat dobrovolný úkol s náhledem výsledku.
Shrnutí
Nástroje pro filtrování jsou velice účinným nástrojem při práci s rozsáhlými seznamy.
Kontrolní otázky a úkoly
Zadání úkolu
Studijní literatura
1. Nováková A., Nulíček V.: Aplikační software 1, 2. Eupress 2008 (skripta). ISBN: 978-80-7408-010-4 2. Bříza V.: Excel 2007 – podrobný průvodce. Grada 2007. ISBN: 978-80-247-1965-8 3. Brož M: MS Excel 2007 – podrobná uživatelská příručka. Computerpress
Klíč k úkolům
U dobrovolného úkolu je k dispozici kontrolní náhled
