Upozornění pro čtenáře a uživatele této knihy
Všechna práva vyhrazena. Žádná část této tištěné či elektronické knihy nesmí být reprodukována ani šířena v papírové, elektronické či jiné podobě bez předchozího písemného souhlasu nakladatele. Neoprávněné užití této knihy bude trestně stíháno.

Marek Laurenčík

Excel 2016
Práce s databázemi a kontingenčními tabulkami

Vydala Grada Publishing, a.s.
U Průhonu 22, Praha 7
obchod@grada.cz, www.grada.cz
tel.: +420 234 264 401, fax: +420 234 264 400
jako svou 6630. publikaci

Odpovědný redaktor Petr Somogyi
Sazba Petr Somogyi
Formát ePub Štěpán Böhm
Počet stran 144
První vydání, Praha 2017
Vytiskly Tiskárny Havlíčkův Brod, a. s.

© Grada Publishing, a.s., 2017
Cover Design © Grada Publishing, a. s., 2017

Názvy produktů, firem apod. použité v knize mohou být ochrannými známkami nebo registrovanými ochrannými známkami příslušných vlastníků.

ISBN 978-80-271-9841-2 (ePub)
ISBN 978-80-271-9840-5 (pdf)
ISBN 978-80-271-0477-2 (print)

Úvod

Kniha, kterou právě otvíráte, je věnována jedné z nejčastěji využívaných možností tabulkového kalkulátoru MS Excel: práci s rozsáhlými tabulkami (databázemi). Kromě stručné úvodní kapitoly spočívá těžiště výkladu ve čtyřech tématech.

Prvním je zpracování dlouhých datových tabulek. Je zde popsáno řazení a filtrace různými způsoby, tvorba přehledů a souhrnů, použití vyhledávacích a databázových funkcí a práce s formátovanými tabulkami.

Druhé téma je věnováno práci s externími daty. Naleznete zde informace o tvorbě připojených tabulek databáze MS Access a MS SQL Server a o tvorbě dotazů. V kapitole je rovněž popsán import dat z textového souboru. V závěru najdete popis exportu dat ze sešitu na internet a práce se soubory typu XML.

Dalším tématem je práce s kontingenčními tabulkami a kontingenčními grafy. Ve 4.  kapitole se dočtete o tvorbě kontingenční tabulky ze souvislé tabulky a z externích dat. Dále je zde popsáno formátování kontingenční tabulky, různé typy souhrnů a pohledů na zpracovávaná data, tvorba výpočtů v kontingenční tabulce a využití dat z kontingenční tabulky. Závěr kapitoly se zabývá tvorbou kontingenčních grafů.

Poslední kapitola knihy se věnuje datovému modelu a jeho využití, především pro tvorbu kontingenčních tabulek. Zde se seznámíte s využitím relací, tvorbou míry, klíčového ukazatele výkonu a kalendářových tabulek. Součástí výkladu jsou také základy jazyka DAX a jeho využití u vypočtených sloupců a kontingenčních tabulek.

Omezený rozsah knihy nám neumožnil podrobně popsat všechny problémy, které by mohly s tématem souviset. Při výběru materiálu jsme se řídili především vlastními zkušenostmi, a to jak z praktické práce s tabulkovým kalkulátorem Excel, tak především z rozsáhlé lektorské praxe při výuce práce s Excelem na různých úrovních. Oproti předchozím vydáním je kniha doplněna řadou příkladů, které si můžete (společně s řešeními) stáhnout v sekci této knihy na webu nakladatelství Grada (www.grada.cz).

V knize se pracuje s poslední verzí Excel 2016. Převážnou většinu postupů nicméně můžete bez velkých problémů využívat i v předchozí verzi Excelu 2013. U postupů, které jsou použitelné pouze v Excelu 2016, je tato skutečnost uvedena.

Knihu jsme připravili společně s Ing. Michalem Burešem jako spoluautorem, který vybral praktické příklady ze své dlouholeté školicí praxe a současně je propojil s teoretickými částmi. Vzniklo tak ucelené dílo, které vám nyní společně předkládáme.

Při výkladu používáme standardní terminologii: klepnutí myší (kliknutí), poklepání myší (dvojklik, dvojité kliknutí), tažení myší (pohyb myši se stisknutým levým tlačítkem), místní nabídka (kontextové menu zobrazené pomocí pravého tlačítka myši) a klávesová zkratka (stisknutí jedné klávesy při podržení jiné, zpravidla klávesy CTRL).

V knize jsou použity následující typografické konvence:

■Názvy ovládacích karet a jejich skupin, názvy sekcí a záložek v dialogových oknech, názvy tlačítek a příkazů jsou zvýrazněny tučným písmem.

■Názvy souborů a webové adresy jsou psány kurzivou.

■Klávesové zkratky jsou zvýrazněny pomocí KAPITÁLEK.

■Kódy vzorců jsou zvýrazněny neproporcionálním písmem.

Ať se vám daří!

Autoři

1Práce s databázemi

1.1Pojem „databáze“ v Excelu

Při práci s tabulkovým kalkulátorem MS Excel se často setkáváme s pojmem „databáze“. Základní definice databáze by mohla znít tak, že se jedná o uspořádanou sérii dat, uloženou v elektronické podobě. Data, zapsaná v tabulce (datové oblasti) je možné považovat za jednoduchou databázi, pokud splňují některé podmínky:

■Tabulka dat musí být souvislá, nesmí obsahovat prázdné řádky nebo sloupce. Některé buňky v řádku mohou být nevyplněné, avšak každý řádek v tabulce musí obsahovat aspoň jednu vyplněnou buňku. Tuto podmínku je nutné dodržet, aby tabulkový kalkulátor rozpoznal začátek a konec tabulky.

■Každý sloupec v tabulce má samostatný nadpis, nadpisy jsou ve společném řádku.

■Údaje v jednotlivých sloupcích mají jednotný charakter: texty, čísla, hodnoty kalendářního data nebo logické hodnoty PRAVDA a NEPRAVDA.

■V tabulce mohou být i sloupce obsahující vzorce. Při operacích jako řazení, filtrování nebo seskupování se využívá aktuální výsledek vzorce.

U těchto tabulek umožňuje Excel používat některé základní databázové operace: řazení, filtrování, seskupování a tvorbu souhrnů.

Excel se pro uchovávání velikého množství informací příliš nehodí. K tomuto účelu se zpravidla využívají specializované programy, označované jako databázové systémy. Excel však nabízí řadu výkonných nástrojů pro analýzu dat, které u velkých databázových systémů často chybí. Proto jsou součástí Excelu i nástroje pro komunikaci s běžnými databázovými systémy. Umožňuje to získávat přístup k datům uloženým ve vnějším datovém zdroji a při jejich zpracování využít všechny možností, které Excel poskytuje.

1.2Práce s rozsáhlou tabulkou

Excel obsahuje několik nástrojů, které usnadňují manipulaci s daty v rozsáhlé tabulce.

1.2.1Ukotvení řádků a sloupců

U velké tabulky je nepříjemné, že při práci s řádky umístěnými níže nejsou vidět nadpisy jednotlivých sloupců. Proto je výhodné řádek s nadpisy ukotvit tak, aby byl stále viditelný. Vložíte kurzor do sloupce A na první řádek tabulky, kde jsou umístěna data (pod řádek s nadpisem), klepnete na tlačítko Ukotvit příčky (karta Zobrazení, skupina Lupa) a použijete příkaz Ukotvit příčky.

Obrázek 1.1: Tlačítko pro ukotvení příček

Nad aktivním řádkem se zobrazí vodorovná čára. Všechny řádky umístěné nad touto čárou jsou na listu stále zobrazeny. Ukotvení řádku se ukládá spolu se sešitem.

Jestliže použijete popsaný postup a označíte buňku v prvním řádku listu, ukotvíte všechny sloupce, které leží nalevo od aktivní buňky. Můžete to s výhodnou využít u tabulky s mnoha sloupci. Pokud je označena buňka, která neleží ani v prvním řádku, ani ve sloupci A, ukotvíte současně řádky ležící nad označenou buňkou a sloupce ležící nalevo od ní.

Po ukotvení řádků, sloupců nebo obojího se příkaz Ukotvit příčky v tlačítku Ukotvit příčky změní na příkaz Uvolnit příčky. Tímto příkazem se pak ukotvení vymaže.

V tlačítku Ukotvit příčky naleznete i příkazy Ukotvit horní řádek nebo Ukotvit první sloupec. Těmito příkazy ukotvíte první řádek nebo sloupec A, bez ohledu na pozici kurzoru. Při použití některého z těchto příkazů na listu, kde je ukotvení již vytvořeno, se dosavadní ukotvení vymaže a nahradí novým.

1.2.2Opakování nadpisů při tisku

Při tisku tabulky, která se nevejde na jednu stránku, je žádoucí, aby se nadpisy sloupců na každé stránce opakovaly. Postup je následující:

Obrázek 1.2: Karta rozložení stránky

1.Klepnete na tlačítko Tisk názvů (karta Rozložení stránky, skupina Vzhled stránky).

2.V zobrazeném okně klepnete do položky Nahoře opakovat řádky.

3.Na listu klepnete do řádku s nadpisy sloupců. Tažením myší se stisknutým levým tlačítkem můžete vyznačit několik řádků za sebou.

4.Stisknete tlačítko OK.

Při tisku tabulky se označené řádky vytisknou na začátku každé stránky. Pro tisk široké tabulky s mnoha sloupci, která se vytiskne na několik stránek vedle sebe, můžete využít také položku Vlevo opakovat sloupce.

Obrázek 1.3: Opakování nadpisů při tisku

1.3Velké databázové systémy

U velkých databázových systémů se pod pojmem „databáze“ zpravidla míní soustava více tabulek, mezi nimiž existují vzájemné logické vazby (například tabulky zákazníků a prodejů mají společný sloupec, obsahující identifikační číslo zákazníka). Snahou je ukládat každou informaci pouze jednou a tím minimalizovat objem ukládaných dat. Poslední verze Excelu (2013 a 2016) obsahují nástroj označovaný jako „datový model“, který umožňuje vytvářet obdobné soustavy tabulek. Kromě vlastních datových tabulek databáze často obsahuje uložené příkazy k výběru určených dat z jedné nebo více tabulek (tzv. dotazy nebo pohledy). Při získávání externích dat do Excelu se tyto výběry dají použít stejně jako původní datové tabulky.

V České republice se v současné době můžete setkat s různými databázovými systémy, k nejčastěji používaným patří:

■Microsoft Access – databázový systém určený pro menší evidence. Je součástí balíku kancelářských programů MS Office.

■Microsoft SQL Server – výkonný databázový systém, využívaný pro střední a větší objemy dat.

■MySQL – databázový systém, který je k dispozici zdarma pro nekomerční využití, pro komerční účely ho prodává firma Sun Microsystems. Využívá se zejména pro tvorbu webových stránek.

■Oracle – velice výkonný databázový systém pro největší objemy dat.

Databázové systémy firmy Microsoft (Access a SQL Server) jsou s Excelem poměrně kompatibilní a lze se k nim připojit přímo. U ostatních databázových systémů se tak děje většinou prostřednictvím přístupu ODBC (Open Database Connectivity). K tomu je zapotřebí vytvořit v počítači příslušný ovladač (driver), který zajišťuje komunikaci s připojenou databází. Vytvořené připojení se stává součástí uloženého sešitu a umožňuje snadnou aktualizaci připojených dat. Neumožňuje však zpětný zápis údajů do připojené databáze.

2Tabulky Excelu jako databáze

Se souvislou tabulkou je možné provádět základní databázové operace: řazení a filtrování záznamů, rychlé vyhledávání údajů z tabulky, vytváření částečných součtů a dalších přehledů. Excel má ale i zvláštní skupinu funkcí pro práci s databázovou tabulkou. S těmito možnostmi Excelu se nyní seznámíme.

2.1Řazení tabulky

Řazení tabulky můžete provést dvěma způsoby:

■Tlačítkem seřadíte tabulku podle hodnot v jednom sloupci.

■Použijete dialogové okno, jež nabízí všechny možnosti řazení.

Obrázek 2.1: Karta Data

Při rychlém řazení tabulky vložíte kurzor do potřebného sloupce a ve skupině Seřadit a filtrovat na kartě Data použijete tlačítka se šipkou a písmeny AZ (vzestupné řazení), nebo se šipkou a písmeny ZA (sestupné řazení). Stejně fungují i příkazy v tlačítku Seřadit a filtrovat, které je umístěno na kartě Domů ve skupině Úpravy.

Vzestupné řazení řadí textové hodnoty od A do Z, číselné hodnoty od nejmenší k největší, hodnoty kalendářního data od nejstarší k nejnovější a logické hodnoty v pořadí NEPRAVDA, PRAVDA. U sestupného řazení je tomu naopak. První řádek tabulky, obsahující nadpisy, je považován za záhlaví.

Obrázek 2.2: Tlačítko Seřadit a filtrovat

Jestliže tabulka obsahuje pouze texty, může řazení pomocí tlačítek někdy vést k tomu, že se seřazení tabulky provede bez záhlaví. Nadpisy sloupců se přitom považují za hodnoty a při seřazení se zamíchají mezi ostatní texty.

2.1.1Řazení podle hodnot

Dialogové okno Seřadit umožňuje seřadit tabulku podle jednoho nebo více sloupců:

1.Označíte libovolnou buňku v tabulce. Použijete tlačítko Seřadit (skupina Seřadit a filtrovat na kartě Data) nebo tlačítko Seřadit a filtrovat (skupina Úpravy na kartě Domů) a poté příkaz Vlastní řazení.

2.Označená volba Data obsahují záhlaví znamená, že je první řádek v tabulce považován za nadpisy sloupců. Sloupce pro řazení určíte podle nadpisů. Při neoznačené volbě je první řádek v tabulce považován za řádek s daty, sloupec určíte podle jeho adresy (sloupec B, C atd.).

3.V seznamu Seřadit podle vyberete název sloupce, nebo odpovídající písmeno.

4.V seznamu Pořadí určíte, zda se bude řadit vzestupně, nebo sestupně. U textových hodnot jsou v seznamu možnosti A až Z nebo Z až A, u číselných a logických hodnot Od nejmenšího k největšímu nebo Od největšího k nejmenšímu a u hodnot kalendářního data možnosti Od nejstaršího k nejnovějšímu nebo Od nejnovějšího k nejstaršímu.

5.Stisknete tlačítko OK.

Obrázek 2.3: Okno pro řazení tabulky

U textových hodnot se nerozlišují malá a velká písmena. Pokud je třeba velká a malá písmena rozlišit, použijete tlačítko Možnosti, v dalším okně označíte volbu Rozlišovat malá a velká a potvrdíte tlačítkem OK. Při rozlišení malých a velkých písmen jsou texty s velkými písmeny umístěny za malými, tedy platí, že „medvěd < Medvěd < MEdvěd < MEDVĚD“. Klepnutím na tlačítko Přidat úroveň přidáte v dialogovém okně další úroveň řazení. Nová úroveň řazení se přidá pod označenou úroveň. Tlačítky se šipkami nahoru a dolů je možné měnit pořadí úrovní pro řazení, odstranění označené úrovně provedete tlačítkem Odstranit úroveň. Řazení tabulky se provede shora dolů, tedy úroveň na prvním řádku je považována za nejvyšší, úroveň na druhém řádku opětovně seřadí záznamy, pro něž je hodnota v první úrovni stejná, atd. Při upravování seřazené tabulky se může stát, že pořadí řádků již neodpovídá nastavenému řazení. Proto vložte kurzor do tabulky, použijte tlačítko Seřadit a nastavené řazení opětovně potvrďte tlačítkem OK.

Obrázek 2.4: Okno Další možnosti řazení

2.1.2Řazení podle textového seznamu

Instalace Excelu obsahuje seznam měsíců v roce a dní v týdnu. Tabulku můžete seřadit i podle některého z těchto seznamů:

1.Vložíte kurzor do tabulky a zobrazíte dialogové okno pro řazení.

2.V seznamu Seřadit podle vyberete název sloupce.

3.V seznamu Pořadí vyberete volbu Vlastní seznam.

4.V dalším dialogovém okně označíte vlevo potřebný seznam a klepnete na tlačítko OK.

5.Seřazení tabulky potvrdíte tlačítkem OK.

Řazení podle seznamu můžete kombinovat s řazením podle jiných sloupců. Na pořadí úrovní nezáleží.

Obrázek 2.5: Výběr seznamu pro řazení

Řazení tabulky pomocí seznamu funguje pouze u sloupce s pevnými texty. Není tedy možné například naformátovat sloupec s hodnotami kalendářního data tak, aby se zobrazoval pouze název dne v týdnu nebo měsíce, a tento sloupec pak použít k řazení pomocí seznamu.

Tvorba vlastních seznamů

Kromě seznamů, které jsou součástí instalace Excelu, si můžete vytvářet i seznamy vlastní. Vlastní seznam použijete k seřazení tabulky a také k automatickému plnění oblasti předem zvolenými texty:

1.Do buněk pod sebou zapíšete texty vytvářeného seznamu a oblast buněk označíte.

2.Na kartě Soubor klepnete na příkaz Možnosti.

3.V zobrazeném dialogovém okně vlevo klepnete na příkaz Upřesnit.

4.V pravé části okna klepnete na tlačítko Upravit vlastní seznamy.

5.Adresa označené oblasti buněk je zobrazena v položce Importovat seznam z buněk. Klepnete na tlačítko Importovat, texty nového seznamu se přenesou do pravé části okna.

6.Vytvoření seznamu potvrdíte tlačítkem OK.

Vlastní seznam se stane součástí Excelu a můžete jej využít v libovolném sešitu.

Vlastní seznamy můžete také dodatečně upravovat. Popsaným postupem zobrazíte okno pro tvorbu seznamu a v levé části označíte upravovaný seznam. V poli Položky seznamu je možné jednotlivé položky seznamu přepisovat, mazat a přidávat položky nové. Úpravu seznamu potvrdíte tlačítkem OK. Pomocí tlačítka Odstranit vytvořený seznam vymažete. Akce je jištěna kontrolním dotazem a je nevratná.

Obrázek 2.6: Tvorba vlastního seznamu

2.1.3Řazení podle barev a ikon

Pro seřazení tabulky můžete také využít různé barvy písma, barevné pozadí buněk nebo ikon, jež vznikly podmíněným formátováním. Použijete následující postup:

Obrázek 2.7: Řazení podle barvy buňky

1.Označíte některou buňku v tabulce a zobrazíte dialogové okno pro řazení.

2.V seznamu Seřadit podle vyberete název sloupce, ve kterém se vyskytují buňky s nastaveným barevným pozadím, barevným písmem nebo ikonami.

3.V seznamu Řazení vyberete volbu Barva buňky, Barva písma nebo Ikona buňky.

4.Klepnete na šipku u seznamu Pořadí. Objeví se nabídka s použitými barvami písma nebo pozadí, případně nabídka ikon. U barevného pozadí je v seznamu i volba Žádná barva buňky, u barevného písma volba Automatická, což znamená výchozí černé písmo. Označíte potřebnou barvu nebo ikonu.

5.V posledním seznamu se automaticky nastaví volba Nahoře. To znamená, že buňky se zvolenou barvou nebo ikonou budou v tabulce řazeny jako první. Jestliže vyberete volbu Dole, budou řazeny jako poslední.

6.Jestliže je ve sloupci použito více barev, označíte řazení podle vytvořené barvy a klepnete na tlačítko Kopírovat úroveň. Tím se do dialogového okna vloží další řádek pro řazení podle barvy, kde stačí pouze označit jinou barvu buňky nebo písma. Totéž platí i pro ikony.

Volba Nahoře znamená seřazení shora dolů – buňky s barvou nebo ikonou, vybrané při řazení jako první, se umístí na začátku tabulky. Při použití volby Dole se řadí zdola nahoru a barva nebo ikona, vybraná v první úrovni řazení, se při seřazení tabulky použije naopak jako poslední.

Seřazení tabulky podle barvy písma, barvy pozadí buňky nebo podle ikon můžete kombinovat se seřazením podle hodnoty buněk, a to i ve stejném sloupci.

2.1.4Řazení tabulky ve vodorovném směru

Dialogové okno pro řazení umožňuje také setřídění tabulky, orientované zleva doprava, tedy seřazení jednotlivých sloupců. Postupujete takto:

1.Postavíte kurzor do tabulky a zobrazíte dialogové okno pro řazení.

2.Klepnete na tlačítko Možnosti.

3.V dalším okně označíte volbu Seřadit zleva doprava. Pak klepnete na tlačítko OK.

4.V seznamu Seřadit podle vyberete číslo řádku, podle něhož se mají sloupce seřadit.

5.V seznamu Pořadí zvolíte vzestupné nebo sestupné řazení.

6.Potvrdíte tlačítkem OK.

Při řazení ve vodorovném směru je volba Data obsahují záhlaví nepřístupná. Řazení je možné pouze podle řádku určitého čísla, první sloupec tabulky se zahrne do řazení. Ve vodorovném směru je možné řadit i do více úrovní, využívat textové seznamy, barvy pozadí buňky, barvy písma a ikony.

Obrázek 2.8: Řazení tabulky ve vodorovném směru

PŘÍKLAD 2.1

Tabulku v sešitu Řazení.xlsx upravte tak, aby byly řádky s tržbami zvýrazněnými modře zobrazeny jako první, řádky s tržbami zvýrazněnými žlutě jako poslední a zbylé řádky uprostřed. Každou z těchto částí tabulky seřaďte podle měsíců v pořadí leden–prosinec a dále sestupně podle hodnoty tržeb.

Využití

Seřazením tabulky zvýšíte její přehlednost, rychle naleznete požadované hodnoty a nejvyšší nebo nejnižší hodnoty, například nejnovější prodeje nebo nejvyšší tržby.

Řešení

Pomocí tlačítka Seřadit na kartě Data si zobrazíte dialogové okno pro řazení. Řazení tabulky podle barev pozadí musí být provedeno jako první. Proto nejprve vytvoříte dvě úrovně řazení: vyberete řazení podle sloupce „Tržba“, v seznamu Řazení nastavíte volbu Barva buňky, v seznamu Pořadí vyberete modrou barvu a v posledním seznamu nastavíte volbu Nahoře. Použijete tlačítko Kopírovat úroveň, v seznamu Pořadí vyberete žlutou barvu a v posledním seznamu nastavíte volbu Dole.

Ve třetí úrovni řazení zvolíte sloupec „Měsíc“, ponecháte řazení podle hodnot a v seznamu Pořadí klepnete na volbu Vlastní seznam. V zobrazeném okně označíte názvy měsíců a potvrdíte tlačítkem OK. V poslední úrovni řazení nastavíte sloupec „Tržba“, ponecháte řazení podle hodnot a v seznamu Pořadí klepnete na volbu od největšího k nejmenšímu.

Příklad je vyřešen v sešitu Řazení – řešení.xlsx.

2.2Automatický filtr

Důležitou databázovou operací je výběr záznamů podle určeného kritéria. Excel poskytuje dvě možnosti filtrace řádků v tabulce: automatický filtr a rozšířený filtr. V této podkapitole je popsán automatický filtr, který se zpravidla používá častěji. O rozšířeném filtru pojednává následující podkapitola. Pro vytvoření automatického filtru v tabulce označíte některou buňku v tabulce a použijete tlačítko Filtr, umístěné ve skupině Seřadit a filtrovat na kartě Data.

Po vytvoření automatického filtru se tlačítko Data zvýrazní a zároveň se u nadpisů jednotlivých sloupců vytvoří tlačítka se šipkou. Tato tlačítka použijete pro nastavení filtru u zvoleného sloupce, který se ukládá společně se sešitem. Opětovným klepnutím na tlačítko Data se šipky odstraní; pokud je v tabulce již nějaký filtr použit, vymaže se a v tabulce se zobrazí všechny řádky.

Obrázek 2.9: Nabídka filtrace pro textové hodnoty

2.2.1Filtrace podle konkrétních hodnot

Nejjednodušším způsobem filtrace je výběr jedné nebo více konkrétních hodnot:

1.Klepnete na šipku u sloupce, podle kterého chcete filtrovat. V zobrazené nabídce je seznam všech hodnot, jež se v sloupci vyskytují. Hodnoty jsou seřazeny a každá hodnota se v seznamu vyskytuje pouze jednou.

2.Zrušíte označení (Vybrat vše) a označíte hodnoty, jež se mají z tabulky vybrat, nebo zrušíte označení u hodnot, které se mají ze zobrazení vyřadit.

3.Klepnete na tlačítko OK.

Obrázek 2.10: Nabídka filtrace pro hodnoty kalendářního data

Obrázek 2.11: Filtrace podle nevyplněných buněk

Na tlačítku u sloupce, v němž jste použili filtr, se zobrazí obrázek trychtýře a zároveň se čísla řádků, ve kterých je filtrovaná tabulka umístěna, označí modře.

U sloupce obsahujícího kalendářní hodnoty se nabídka rozčlení po rocích, měsících a dnech. Klepnutím na čtvereček se symbolem „plus“ zobrazíte nabídku o úroveň nižší, klepnutím na čtvereček se symbolem „minus“ naopak nižší úroveň skryjete. Toto členění nabídky umožňuje vybrat jedno nebo více zvolených období.

Zrušení nastaveného filtru provedete jedním ze tří možných postupů:

■Klepnete na šipku u nadpisu sloupce s filtrem a použijete příkaz Vymazat filtr z...

■Klepnete na tlačítko Vymazat (karta Data, skupina Seřadit a filtrovat).

■Klepnete na tlačítko Seřadit a filtrovat (karta Domů, skupina Úpravy) a použijete příkaz Vymazat.

Filtrace podle vyplněných nebo prázdných buněk

Jestliže jsou v některém sloupci v tabulce nevyplněné buňky, v nabídce hodnot pro filtraci se jako poslední zobrazí také hodnota Prázdné.

Vyřazením volby (Vybrat vše) a označením volby Prázdné se zobrazí pouze řádky, které mají ve zvoleném sloupci nevyplněné buňky. Vyřazením volby Prázdné naopak zobrazíte řádky, v nichž jsou buňky ve zvoleném sloupci neprázdné.

Obnovení nastaveného filtru

Jestliže některou buňku ve filtrovaném sloupci přepíšete tak, aby nastavenému filtru nevyhovovala, řádek s buňkou zůstává v tabulce stále zobrazen. Nastavený filtr musíte použít znovu, aby se zobrazení tabulky aktualizovalo: klepnete na tlačítko Použít znovu (skupina Seřadit a filtrovat na kartě Data). Druhou možností je klepnout na tlačítko Seřadit a filtrovat (skupina Úpravy na kartě Domů) a použít příkaz Použít znovu.

2.2.2Filtrace podle číselných hodnot

U sloupce obsahujícího čísla naleznete ve filtrační nabídce také příkaz Filtry čísel, který zobrazí nabídku pro filtrování číselných hodnot.

Volby Rovná se, Nerovná se, Větší než, Větší nebo rovno, Menší než a Menší nebo rovno zobrazí okno, ve kterém je již nastaven způsob porovnání. Do položky vpravo zapíšete potřebnou hodnotu a potvrdíte tlačítkem OK.

Obdobně funguje nabídka Mezi. V zobrazeném okně vyplníte v prvním řádku spodní mez, ve druhém řádku horní mez a potvrdíte tlačítkem OK.

V příkazu Filtry čísel není nabídka „Není mezi“. Proto postupujete takto:

1.Použijete příkaz Filtry čísel a nabídku Mezi.

2.V zobrazeném okně vyplníte v prvním řádku horní mez a ve spodním řádku dolní mez.

3.V přepínači mezi oběma řádky označíte volbu Nebo.

4.Klepnete na tlačítko OK.

Obrázek 2.12: Nabídka pro filtry číselných hodnot

Přepínač, umístěný mezi oběma řádky, určuje vzájemný vztah obou podmínek. U volby A se obě podmínky uplatní současně, volba Nebo znamená, že se uplatní alespoň jedna z nich.

Všechny uvedené možnosti nahradí příkaz Vlastní filtr. Tato volba zobrazí prázdné okno pro filtraci číselných hodnot, ve kterém nastavíte typ porovnání, hodnoty a vztah mezi oběma podmínkami.

Obrázek 2.13: Vlastní filtr

Nadprůměrné a podprůměrné hodnoty

Pro výběr nadprůměrných nebo podprůměrných hodnot jsou určeny volby Nad průměrem a Pod průměrem. Průměr, použitý pro porovnání, se spočítá z celého sloupce.

Největší a nejmenší hodnoty

Zobrazení zadaného počtu nejvyšších nebo nejnižších hodnot provede příkaz Prvních 10. Volba zobrazí okno, ve kterém vyplníte tyto údaje:

■V levém seznamu vyberete volbu prvních (největší hodnoty) nebo posledních (nejmenší hodnoty).

■V pravém seznamu vyberete volbu položek (zobrazí se uvedený počet řádků) nebo procent (zobrazí se uvedený podíl z celkového počtu řádků).

■Do číselníku uprostřed zapíšete počet řádků nebo procenta.

■Klepnete na tlačítko OK.

Obrázek 2.14: Filtrace největších a nejmenších hodnot

Pokud se údaje ve filtrovaném sloupci opakují, může se zobrazit více řádků, než je stanovený počet.

2.2.3Filtrace podle hodnot kalendářního data

Filtrační tlačítko u sloupce s hodnotami kalendářního data obsahuje příkaz Filtry kalendářních dat. Tento příkaz umožňuje porovnání hodnot ve sloupci s pevně zadaným datem nebo s aktuálním datem, tedy systémovým datem počítače.

Pro filtraci prvním způsobem použijete volby Rovná se, Před, Po a Mezi. Ty zobrazují stejné okno jako u číselných hodnot. V okně vyplníte datum pro porovnání a potvrdíte tlačítkem OK. Volbou Vlastní filtr zobrazíte prázdné okno pro nastavení filtru. Pro filtraci s použitím aktuálního data jsou určeny volby Zítra, Dnes, Včera, Příští týden, Tento týden, Minulý týden, Příští měsíc, Tento měsíc, Minulý měsíc, Příští čtvrtletí, Toto čtvrtletí, Minulé čtvrtletí a Příští rok, Tento rok, Minulý rok. Tato filtrace se však provádí jednorázově a použije aktuální datum, platné v okamžiku tvorby filtru. Jestliže použijete například volbu Dnes a uložíte sešit i s filtrem, následující den jsou zobrazeny tytéž řádky. Proto musíte klepnout na tlačítko Použít znovu a filtr aktualizovat.

Volba Od začátku roku zobrazí kalendářní data od 1. ledna běžného roku až po aktuální datum. Také tento způsob filtrace je zapotřebí aktualizovat tlačítkem Použít znovu.

Volba Všechna data v období zobrazí další nabídku obsahující jednotlivá čtvrtletí a názvy měsíců v roce. Výběrem některého čtvrtletí nebo měsíce zobrazíte všechna data spadající do zvoleného období, a to bez ohledu na rok.

Obrázek 2.15: Nabídka filtrů pro kalendářní data

2.2.4Filtrace podle textových hodnot

Příkaz Filtry textu zobrazí nabídku možností pro filtraci textových hodnot.

Volby Rovná se, Nerovná se, Má na začátku, Má na konci, Obsahuje a Neobsahuje zobrazí okno, ve kterém zapíšete text pro porovnání a potvrdíte tlačítkem OK. Volba Vlastní filtr zobrazí prázdné filtrační okno. Při filtraci textů platí tato pravidla:

■U voleb Má na začátku, Má na konci, Obsahuje a Neobsahuje stačí zapsat jen textový řetězec. Není třeba zapisovat hvězdičky, jako například při hledání souborů.

■Filtrace se provádí podle celých slov i podle části slov.

■Při filtraci se nerozlišují malá a velká písmena.

Obrázek 2.16: Nabídka filtrů textu

Výběr podle abecedy

Excel při filtraci rozlišuje pořadí u čísel (podle velikosti), kalendářních dat (chronologicky) a také u textů (podle abecedy). Pro zobrazení textů například z horní poloviny abecedy, tedy od písmena M výše, použijete volbu Vlastní filtr, v zobrazeném okně nastavíte volbu Je větší než a napravo zapíšete písmeno M. Pro obrácené zobrazení, tedy do počátečního písmena M včetně, použijete volbu Je menší než a písmeno N.

Filtrace textů pomocí masek

Filtraci podle části textu můžete provést také tak, že použijete volbu Rovná se a jako vzorek textu použijete text s maskou, využívající speciální znaky:

■hvězdička znamená libovolný text nebo žádný znak,

■otazník znamená jeden libovolný znak.

Například všechny firmy z Prahy by bylo možné zobrazit s použitím masky *Praha*. Jednodušší je zřejmě použít volbu Obsahuje. Použití masky je však nezbytné ve speciálních případech, kdy potřebujete filtrovat podle jednoho nebo více znaků, umístěných na přesně stanoveném místě v textu.

Jestliže například potřebujete zobrazit řádky, které mají ve zvoleném sloupci umístěné na třetím a čtvrtém místě znaky ab, volba Obsahuje nevybere zobrazené řádky správně. V tomto případě musíte použít volbu Rovná se a jako vzorek textu zapsat masku ??ab*.

2.2.5Filtrace podle barev a ikon

Příkaz Filtrovat podle barvy umožňuje použít k filtraci barvu buňky či písma nebo ikonu. V příkazu je pak zobrazena další nabídka s možnostmi Filtrovat podle barvy buňky, Filtrovat podle barvy písma nebo Filtrovat podle ikony buňky.

Při filtraci podle barev nebo ikon můžete zvolit pouze jedinou barvu nebo ikonu, která bude ve sloupci zobrazena.

Obrázek 2.17: Filtrace podle barev

U sloupce s hodnotami kalendářního data není možné vyfiltrovat zadaný počet nejstarších nebo nejnovějších dat, protože volba Prvních 10 u datových hodnot chybí. Problém vyřešíte podmíněným formátováním. Označíte sloupec s hodnotami kalendářního data, vytvoříte v něm podmíněné formátování s volbou Formátovat pouze hodnoty zařazené jako první nebo poslední a vyberete odlišnou barvu pozadí nebo písma. Poté použijete filtrování podle barvy.

2.2.6Filtrace podle více sloupců

Všechny popsané způsoby filtrování můžete použít u dvou nebo více sloupců v tabulce. Při použití filtru u více sloupců se vyhodnotí všechny filtry současně. Proto se zobrazí záznamy, které vyhovují všem nastaveným podmínkám. Tímto způsobem můžete výběr záznamů z tabulky postupně omezovat.

Při odstranění filtrace u tabulky, kde se filtruje podle více sloupců, máte k dispozici dva způsoby:

■Příkaz Vymazat filtr z …, který je v nabídce filtračního tlačítka, odstraní filtraci u zvoleného sloupce, zatímco filtrace u ostatních sloupců zůstane nastavena.

■Tlačítko Vymazat odstraní filtr u všech sloupců v tabulce.

2.2.7Práce s filtrovanou tabulkou

Tisk

Při tisku listu se tisknou se jen zobrazené řádky. Nastavená filtrace zůstává účinná, i když sloupec s vytvořeným filtrem skryjete.

Kopírování

Jestliže označíte jako oblast tabulku s vytvořeným automatickým filtrem nebo několik jejích sloupců, označenou oblast zkopírujete do schránky a zkopírované buňky vložíte na jiný list, vloží se jen zobrazené řádky. To umožňuje vykopírovat z dlouhé tabulky několik tabulek menších.

Vložení oblasti, zkopírované z tabulky s vytvořeným filtrem, můžete provést i na stejném listu, ale mimo rozsah řádků s filtrovanou tabulkou.

Řazení

Při klepnutí na tlačítko se šipkou u nadpisu sloupce se na začátku nabídky zobrazí také příkazy pro vzestupné nebo sestupné řazení. Po použití příkazu pro řazení se na filtračním tlačítku zobrazí malá šipka orientovaná nahoru (vzestupné řazení) nebo dolů (sestupné řazení). U téhož sloupce je možné použít zároveň řazení a filtr. Na filtračním tlačítku se pak zobrazí oba symboly.

Pokud jsou v některém sloupci tabulky použity různé barvy písma, různé barevné pozadí buňky nebo ikon, ve filtrační nabídce je přístupný i příkaz Seřadit podle barvy. Klepnutím na tento příkaz se zobrazí nabídka pro seřazení podle barvy buňky, seřazení podle barvy písma nebo seřazení podle ikon. Opakovaným použitím příkazu Seřadit podle barvy umístíte jednotlivé barvy nebo ikony za sebe; barva nebo ikona nastavená jako první je zcela nahoře. Odstraněním automatického filtru se v tabulce zobrazí všechny řádky, ale nastavené seřazení zůstane.

Tvorba součtu a dalších souhrnů

Na kartě Domů ve skupině Úpravy naleznete tlačítko se symbolem řeckého písmene Σ, určené pro rychlou tvorbu součtu. Označíte buňku pod sloupcem s číselnými hodnotami u tabulky, kde je použit automatický filtr, a na toto tlačítko klepnete. Do buňky se místo funkce SUMA vloží funkce SUBTOTAL. Tato funkce provádí součet zobrazených buněk (funkce SUMA provede vždy součet přes celou zadanou oblast, bez ohledu na to, zda jsou buňky zobrazeny nebo ne). Výsledný vzorec proto dává různé hodnoty součtu podle toho, jaký filtr v tabulce použijete.

Funkce SUBTOTAL má dva povinné parametry:

■Funkce je číslo, určující typ souhrnu (pro součet je použito číslo 9).

■Odkaz1 je oblast buněk, zahrnující celý sloupec tabulky včetně řádku s nadpisem (další parametry Odkaz2 atd. jsou nepovinné, funkce může zpracovat až 255 oddělených oblastí).

Po vložení funkce SUBTOTAL můžete klepnout do řádku vzorců a přepsat první parametr funkce. Tím získáte jiný typ souhrnu, který se rovněž aktualizuje podle použitého filtru.

Tabulka 2.1: Možnosti funkce SUBTOTAL

Typ souhrnu

Hodnota parametru

Průměr

1

Počet číselných hodnot nebo hodnot kalendářního data

2

Počet neprázdných buněk

3

Největší hodnota

4

Nejmenší hodnota

5

Součet

9

Jestliže však klepnete na šipku u sumačního tlačítka a použijete například příkaz Průměr, do buňky se nevloží funkce SUBTOTAL s parametrem 1, ale funkce PRŮMĚR.

V tabulce musí být nastaven nějaký filtr. Pokud jsou zobrazeny všechny záznamy, sumační tlačítko vloží do buňky funkci SUMA, i když je tlačítko Filtr aktivní.

PŘÍKLAD 2.2

Z tabulky v sešitu Filtr.xlsx vyberte nejmenší čtvrtinu tržeb, realizovaných v českých (nikoliv moravských) městech v posledním čtvrtletí roku 2016. Tyto řádky zkopírujte na nový list.

Využití

Postup, popsaný v tomto příkladu, se hodí například při rozdělení velké tabulky do série dílčích tabulek, umístěných na různých listech. Použití automatického filtru pro dělení velké tabulky je efektivní a spolehlivé.

Řešení

V tabulce označíte některou buňku a na kartě Data klepnete na tlačítko Filtr. Nejprve nastavíte rozmezí kalendářního data od 1. 10. 2016 do 31. 12. 2016. Po klepnutí na šipku u sloupce s hodnotami kalendářního data použijete volbu Filtry kalendářních dat a dále volbu Mezi. V zobrazeném okně zapíšete počáteční a koncové datum (můžete přitom použít tlačítka s obrázkem kalendáře). Dále klepnete na šipku u sloupce s pobočkami a vyřadíte volbu „Brno“. Nakonec klepnete na šipku u sloupce s tržbami, použijete volby Filtry čísel a dále Prvních 10. V zobrazeném okně nastavíte v prvním seznamu volbu posledních, v číselníku zapíšete hodnotu 25 a v druhém seznamu označíte volbu procent.

Do sešitu vložíte nový list, označíte oblast celé tabulky, zkopírujete ji do schránky a vložíte ji do nového listu.

Příklad je vyřešen v sešitu Filtr – řešení.xlsx.

2.3Rozšířený filtr

U rozšířeného filtru zapíšete filtrační podmínku do buněk mimo tabulku. Rozšířený filtr má podstatně méně možností než filtr automatický. Je však možné provádět filtraci u více sloupců tak, aby byly filtrační podmínky spojeny jako „nebo“, aby platila alespoň jedna z nastavených podmínek, což automatický filtr neumožňuje.

2.3.1Vytvoření rozšířeného filtru

Tvorba rozšířeného filtru zahrnuje tyto kroky:

1.Do buněk mimo tabulku zapíšete filtrační podmínku.

2.Umístíte kurzor do tabulky, kterou chcete filtrovat.

3.Klepnete na tlačítko Upřesnit (karta Data, skupina Seřadit a filtrovat).

4.V položce Oblast seznamu je zobrazen odkaz na celou filtrovanou tabulku. Klepnete do položky Oblast kritérií a vyznačíte buňky se zapsaným filtrem.

5.Potvrdíte tlačítkem OK.

Obrázek 2.18: Rozšířený filtr

Zápis filtru pro jeden sloupec provedete do dvou buněk nad sebou:

■Do horní buňky zapíšete nebo zkopírujete nadpis sloupce, podle něhož chcete filtrovat.

■Do spodní buňky zapíšete filtrační výraz. Samotná hodnota znamená „rovno“, pro porovnání čísel nebo hodnot kalendářního data můžete použít znaky „větší“, „menší“ a rovnítko, například „>=100“ znamená „větší nebo rovno než 100“. Pro porovnání typu „nerovná se“ použijete znaky „<>“. Pro filtraci podle textových hodnot použijete masku se znaky hvězdičky nebo otazníku; význam těchto znaků je stejný jako u automatického filtru.

Filtr můžete zapsat v podstatě kamkoliv, dokonce i na jiný list, než na kterém je filtrovaná tabulka. Není však vhodné jej zapisovat napravo od tabulky, protože filtrace skryje některé řádky a filtr může z listu zmizet.

Rozšířenou filtraci odstraníte pomocí tlačítka Vymazat, umístěného ve skupině Seřadit a filtrovat na kartě Data.

Tlačítko Použít znovu je při rozšířené filtraci nepřístupné. Jestliže přepíšete některou buňku ve filtrovaném sloupci nebo samotný filtr, je třeba označit některou buňku v tabulce, znovu použít tlačítko Upřesnit a v zobrazeném okně klepnout na tlačítko OK. Oblast buněk s filtrem již není třeba znovu vybírat.

Obrázek 2.19: Buňky se zapsaným filtrem

2.3.2Filtrace pomocí dvou a více podmínek

U rozšířeného filtru můžete využít dvě nebo více filtračních podmínek, vztažených ke stejnému nebo k různým sloupcům. Podmínky zapíšete do dvou sloupců vedle sebe, nadpisy sloupců do stejného řádku a filtrační podmínky do jednoho řádku vedle sebe nebo ob řádek. První možnost znamená spojení „a“, tedy obě podmínky platí současně, druhá možnost spojení „nebo“, tedy platí jedna nebo druhá podmínka.

Obrázek 2.20: Filtrace podle více podmínek

Rozšířená filtrace tedy umožňuje vytvořit filtr u dvou různých sloupců tak, aby platila jedna nebo druhá podmínka, což u automatického filtru nebylo možné.

Zápis tří podmínek, spojených stejným způsobem, zapíšete obdobně. Při kombinaci obou typů spojení musíte respektovat skutečnost, že mezi spojeními „a“ a „nebo“ je obdobný vztah jako mezi násobením a sčítáním. Spojení typu „a“ tedy svazuje podmínky těsněji než spojení typu „nebo“. Podmínky platící současně (spojení „a“) musíte proto zapsat ve stejném řádku. To může vést k tomu, že při spojení typu „(podmínka 1 nebo podmínka 2) a podmínka 3“ se musí podmínka 3 zapsat dvakrát (viz obrázek 2.21).

Dvě nebo více podmínek spojených jako „nebo“ a platných pro stejný sloupec v tabulce můžete zapsat pod sebou do jednoho sloupce. Tím se filtr zjednoduší.

Obrázek 2.21: Filtrace podle více podmínek se zápisem hodnot pod sebou

2.3.3Filtrace s využitím vzorce

Vytvořený filtr může obsahovat i vzorec. Jako nadpis zvolíte libovolný text (nesmíte použít nadpis některého sloupce v tabulce), jako podmínku vytvoříte vzorec vracející hodnotu PRAVDA nebo NEPRAVDA. Vzorec odkazuje na první buňku s hodnotou ve sloupci, kde se vyhodnocuje podmínka. Tento odkaz ponecháte jako relativní, ostatní odkazy upravíte na absolutní. Například zobrazení nadprůměrných nebo podprůměrných hodnot provedete pomocí vzorce využívajícího funkci PRŮMĚR (viz obrázek 2.22). Filtraci s využitím vzorce můžete také použít k výběru textových hodnot s rozlišením malých a velkých písmen. Jako vzorec použijete funkce STEJNÉ. Tato funkce porovnává dva textové řetězce, zadané v parametrech Text1 a Text2 s rozlišením malých a velkých písmen, a jako výsledek porovnání vrací hodnotu PRAVDA nebo NEPRAVDA. V prvním parametru je odkaz na první buňku s hodnotou ve sloupci, ve druhém je porovnávaný text.

Vážení čtenáři, právě jste dočetli ukázku z knihy Excel 2016.
Pokud se Vám líbila, celou knihu si můžete zakoupit v našem e-shopu.