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 2019

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

jako svou 7703. publikaci

Spoluautor Michal Bureš

Odpovědná redaktorka Věra Slavíková

Sazba Jan Šístek

Formát ePub Štěpán Böhm

Ikony Freepik (www.flaticon.com)

Počet stran 184

První vydání, Praha 2020

Vytiskly Tiskárny Havlíčkův Brod, a. s.

© Grada Publishing, a.s., 2020

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

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-1669-0 (ePub)

ISBN 978-80-271-1668-3 (pdf)

ISBN 978-80-271-1391-0 (print)

Úvod

Tato kniha 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). Obsahuje šest hlavních témat:

■Zpracování dlouhých datových tabulek: zde je 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í, základní statistické výpočty a agregační funkce, reagující na filtr.

■Práce s formátovanými tabulkami: jedná se o poněkud odlišný přístup k datům, jehož význam se v posledních verzích Excelu silně zvyšuje.

■Práce s externími daty: zde naleznete informace o tvorbě připojených tabulek z databáze MS Access a MS SQL Server, dále připojení textových souborů a souborů typu XML. Další část kapitoly je věnována tvorbě dotazů.

■Práce s kontingenčními tabulkami a kontingenčními grafy: v této 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í vypočtených údajů. Závěr kapitoly je věnován tvorbě kontingenčních grafů.

■Datový model Excelu: jedná se o novinku, která je v Excelu zařazena od verze 2013. Použití datového modelu výrazně rozšiřuje možnosti kontingenčních tabulek. Seznámíte se s využitím relací, tvorbou míry a klíčového ukazatele výkonu.

■Výpočty v jazyku DAX: tato kapitola bezprostředně navazuje na kapitolu o datovém modelu. Seznámíte se s dalšími možnostmi, které je možné využít u kontingenčních tabulek a tvorbě kalendáře.

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. U některých témat odkazujeme čtenáře na naši knihu „Excel 2016 a 2019 – pokročilé nástroje“, kterou vydalo nakladatelství Grada.

Text knihy je doplněn řadou příkladů. Zkušební data si můžete stáhnout v sekci této knihy na webu nakladatelství Grada (www.grada.cz).

Kniha je orientována na poslední verzi Excel 2019. Až na malé výjimky je možné uvedené postupy používat i v předchozí verzi Excelu 2016. Případné odchylky od Excelu 2016 jsou v knize vždy uvedeny.

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.

■Parametry funkcí, volby v nabídkách a názvy oken jsou označeny tučnou kurzívou.

■Názvy sešitů a listů jsou psány kurzívou.

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

■Kódy vzorců jsou zvýrazněny písmem Courier New.

Ať se vám daří!

Autoři

1Databázové operace vExcelu

Pro zpracování rozsáhlého množství dat se často používají programy, označované jako relační databáze. Mezi tyto programy patří např. MS Access, MS SQL Server, MySQL nebo Oracle. Tyto programy uchovávají datovou agendu zpravidla vpodobě série vzájemně propojených datových tabulek aumožňují rychlé řazení, výběry dat filtrací, provádění výpočtů suchovanými daty atvorbu souhrnů.

Některé základní databázové operace– řazení, filtrování, seskupování atvorbu souhrnů můžeme provádět ivExcelu. Tabulka, se kterou chceme tyto operace provádět, však musí splňovat některé podmínky:

■Tabulka je souvislá, tj. nesmí obsahovat prázdné řádky nebo sloupce. Některé buňky vřádku mohou být nevyplněné, avšak každý řádek vtabulce musí obsahovat aspoň jednu vyplněnou buňku.

■První řádek tabulky tvoří nadpisy jednotlivých sloupců. Každý sloupec by měl mít svůj nadpis, který sloupec jedinečným způsobem charakterizuje. Tabulka by neměla obsahovat duplicitní nadpisy nebo nadpisy umístěné na několika řádcích (např. se sloučenými buňkami).

■Údaje vjednotlivých sloupcích mají jednotný charakter: texty, čísla, datumové hodnoty, logické hodnoty PRAVDA aNEPRAVDA nebo vzorce stejného typu.

Tabulka, splňující uvedené požadavky, může být považována za jednoduchou databázi.

Excel se ovšem nehodí pro zpracování zvlášť velkých tabulek. Hlavní důvod je vtom, že vExcelu není předem předepsán charakter údajů vjednotlivých sloupcích tabulky. Proto např. při filtraci nebo řazení musí Excel neustále zjišťovat typ údajů vjednotlivých buňkách. Urelačních databází je naopak každý sloupec tabulky přesně definován jako textový, číselný, datumový atd., aproto je výkonnost relační databáze mnohonásobně vyšší.

Naproti tomu Excel poskytuje řadu účinných apohodlných nástrojů pro analýzu dat, které uvelkých databázových systémů často chybí. Proto jsou součástí Excelu také nástroje pro komunikaci sběžnými databázovými systémy. To umožňuje získat přístup kdatům, uložených vrelační databázi, apři jejich zpracování využít všechny možností, které Excel poskytuje.

1.1Třídění

Setřídění dat vtabulce můžeme provést dvěma způsoby:

■Použít tlačítko pro seřazení tabulky podle jednoho sloupce.

■Využít dialogové okno pro seřazení tabulky.

První způsob je rychlý ajednoduchý, druhý umožňuje využít všechny možnosti řazení.

Obrázek 1.1: Karta Data

1.1.1Řazení podle hodnot

Pro rychlé seřazení tabulky podle hodnot vjednom sloupci postavíme kurzor do zvoleného sloupce apoužijeme jeden ze tří způsobů:

■Na kartě Data ve skupině Seřadit afiltrovat klepneme na tlačítko se šipkou apísmeny AZ (vzestupné řazení) nebo šipkou apísmeny ZA (sestupné řazení).

■Na kartě Domů ve skupině Úpravy klepneme na tlačítko Seřadit afiltrovat apoužijeme příkazy Seřadit od Ado Z(vzestupné řazení) nebo Seřadit od Zdo A(sestupné řazení).

■Klepneme pravým tlačítkem myši na některou buňku ve zvoleném sloupci, zmístní nabídky použijeme příkaz Seřadit, adále volby Seřadit od Ado Znebo Seřadit od Zdo A.

Obrázek 1.2: Tlačítko Seřadit afiltrovat

Vzestupné řazení řadí textové hodnoty abecedně od Ado Z, číselné hodnoty od nejmenší knejvětší, datumové hodnoty od nejstarší knejnovější alogické hodnoty vpořadí NEPRAVDA, PRAVDA. Usestupného řazení je tomu naopak. První řádka tabulky, obsahující nadpisy, je považována za záhlaví.

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

1.1.2Řazení podle více sloupců

Pro seřazení podle hodnot ve více sloupcích musíme použít dialogové okno pro řazení. Pro jeho zobrazení označíme nějakou buňku vtabulce apoužijeme jeden ze tří způsobů:

■Na kartě Data ve skupině Seřadit afiltrovat klepneme na tlačítko Seřadit.

■Na kartě Domů ve skupině Úpravy klepneme na tlačítko Seřadit afiltrovat apoužijeme příkaz Vlastní řazení.

■Klepneme pravým tlačítkem myši na některou buňku vtabulce, zmístní nabídky použijeme příkaz Seřadit, adále volbu Vlastní řazení.

Obrázek 1.3: Okno pro řazení tabulky

Vdialogovém okně pro řazení máme tyto možnosti:

1.Označení volba Data obsahují záhlaví znamená, že je první řádek vtabulce považován za nadpisy sloupců asloupce pro řazení určíme podle jejich nadpisů. Když volba není označená, první řádek vtabulce se považuje za řádek sdaty asloupce pro řazení určíme podle jeho adresy (sloupec „B“, „C“ atd.)

2.Název sloupce nebo odpovídající písmeno sloupce vybereme vseznamu Seřadit podle.

3.Seznam Pořadí určuje vzestupné nebo sestupné řazení. Pro textové hodnoty se vseznamu zobrazí možnosti Aaž Znebo Zaž A, pro číselné alogické hodnoty volby Od nejmenšího knejvětšímu nebo Od největšího knejmenšímu apro datumové hodnoty možnosti Od nejstaršího knejnovějšímu nebo Od nejnovějšího knejstaršímu.

4.Klepneme na tlačítko Přidat úroveň. Tím se vdialogovém okně přidá další úroveň řazení, která se zařadí na konec seznamu úrovní. Pořadí úrovní pro řazení změníme tlačítky se šipkami nahoru adolů, odstranění označené úrovně provedeme tlačítkem Odstranit úroveň.

5.Podle potřeby přidáme další úrovně řazení aklepneme na tlačítko OK.

Řazení tabulky se provede shora dolů, tj. úroveň na prvním řádku je považována za nejvyšší, úroveň na druhém řádku opětovně seřadí záznamy, pro které je hodnota vprvní úrovni stejná atd.

1.1.3Rozlišení malých avelkých písmen

Řazení podle textových hodnot standardně nerozlišuje malá avelká písmena. Pokud je třeba velká amalá písmena rozlišit, vdialogovém okně klepneme na tlačítko Možnosti, vdalším okně označíme volbu Rozlišovat malá avelká apotvrdíme tlačítkem OK.

Při řazení srozlišením malých avelkých písmen jsou malá písmena považována za „menší“ než písmena velká, tedy platí pořadí „excel < Excel < EXcel < EXCEL“.

Obrázek 1.4: Možnosti řazení

1.1.4Aktualizace řazení

Jestliže přepíšeme některou buňku ve sloupci, podle kterého je tabulka seřazena, může se stát, že pořadí řádků vtabulce již neodpovídá nastavenému řazení. Proto si zobrazíme dialogové okno pro seřazení (vokně se zobrazí nastavené řazení) aklepneme na tlačítko OK. Tento postup použijeme itehdy, když byla tabulka seřazena jednoduchým způsobem podle jednoho sloupce.

1.1.5Řazení podle seznamu

Usloupce stextovými hodnotami můžeme také využít řazení podle předem vytvořeného seznamu:

1.Vdialogovém okně pro řazení vybereme sloupec pro řazení.

2.Vseznamu Pořadí označíme volbu Vlastní seznam. Tím si zobrazíme další dialogové okno.

3.Vseznamu Vlastní seznamy označíme potřebný seznam advakrát klepneme na tlačítko OK.

Řazení podle vlastního seznamu je možné libovolně kombinovat sřazením podle jiných sloupců. Instalace Excelu obsahuje seznam měsíců, římských čísel měsíců, dnů vtýdnu azkratek dnů vtýdnu. Tyto seznamy můžeme ihned použít kseřazení.

Seřazení tabulky podle názvů měsíců nebo dnů vtýdnu funguje také usloupce obsahujícího vzorce. Pokud tabulka obsahuje sloupec sdatumovými hodnotami, můžeme do ní přidat sloupec sfunkcí HODNOTA.NA.TEXT, ve druhém parametru funkce použít kód „mmmm“ (název měsíce) nebo „dddd“ (název dne vtýdnu) apodle tohoto sloupce tabulku seřadit.

Další seznamy pro řazení si můžeme vytvořit tímto postupem:

1.Texty vytvářeného seznamu zapíšeme do buněk pod sebou. Oblast buněk označíme.

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

3.Vlevé části zobrazeného okna klepneme na příkaz Upřesnit.

4.Vpravé části okna (ve spodní části) použijeme tlačítko Upravit vlastní seznamy. Tím se zobrazí další okno pro tvorbu vlastních seznamů.

5.Adresa označené oblasti buněk je zobrazena vpoložce Importovat seznam zbuněk. Klepnutím na tlačítko Importovat se texty nového seznamu přenesou do pravé části okna.

6.Klepneme na tlačítko OK.

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

Obrázek 1.6: Tvorba vlastního seznamu

Vlastní seznam se přidá do instalace Excelu amůžeme jej využít vlibovolném sešitu[1].

Jestliže vokně pro tvorbu seznamu označíme vytvořený seznam, můžeme jej dodatečně upravovat: jednotlivé položky seznamu přepisovat, mazat apřidávat položky nové nebo tlačítkem Odstranit seznam vymazat. Akci potvrdíme tlačítkem OK. Úpravu nebo odstranění seznamu je možné provést pouze uvlastního seznamu; standardní seznamy Excelu upravovat nebo odstraňovat není možné.

Řazení tabulky pomocí seznamu se řídí textovými hodnotami vbuňkách nebo výsledky vzorců. Na formátování sloupce nezáleží. Není tedy možné naformátovat sloupec sdatumovými hodnotami tak, aby se zobrazoval pouze název dne vtýdnu nebo název měsíce atento sloupec použít křazení pomocí seznamu.

1.1.6Řazení podle barev aikon

Tabulku můžeme seřadit také podle barev písma, barevného pozadí buněk nebo ikon, vzniklých podmíněným formátováním. Použijeme tento postup:

1.Zobrazíme dialogové okno pro řazení tabulky.

2.Vseznamu Seřadit podle vybereme potřebný název nebo adresu sloupce.

3.Vseznamu Řazení vybereme volbu Barva buňky, Barva písma nebo Ikona buňky.

4.Klepnutím na šipku useznamu Pořadí zobrazíme nabídku spoužitými barvami písma (volba Automatická znamená výchozí černé písmo), barvami pozadí (volba Žádná barva buňky znamená buňky bez nastaveného barevného pozadí) nebo nabídku ikon. Označíme potřebnou barvu nebo ikonu.

5.Vposledním seznamu (bez nadpisu) zvolíme, zdali se označená barva nebo ikona umístí na začátek tabulky (volba Nahoře– výchozí nastavení) nebo na konci tabulky (volba Dole).

Obrázek 1.7: Řazení podle barev

Jestliže je ve sloupci použito více barev nebo více ikon, je třeba vytvořit více úrovní řazení. Vtomto případě postačí označit řazení podle vytvořené barvy aklepnout na tlačítko Kopírovat úroveň. Tím se do dialogového okna vloží další řádka pro řazení podle barvy, kde stačí pouze označit jinou barvu buňky. Totéž platí ipro ikony.

Pro rychlé seřazení tabulky svyužitím jedné barvy nebo ikony můžeme také klepnout pravým tlačítkem myši na buňku sbarevným písmem, pozadím nebo ikonou. Zmístní nabídky použijeme příkaz Seřadit adále volbu Umístit barvu vybrané buňky nahoru, Umístit barvu vybraného písma nahoru, Umístit ikonu vybraného formátování nahoru. Při řazení podle barvy písma příkaz reaguje ina černé písmo, buňky sbarevným písmem se umístí na konci tabulky. Pokud klepneme na buňku bez barevného pozadí, příkaz nereaguje.

Seřazení tabulky podle barvy písma, barvy pozadí buňky nebo podle ikon je možné libovolně kombinovat se seřazením podle hodnoty buněk, ato ive stejném sloupci.

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

Tabulku můžeme také seřadit směrem zleva doprava, tedy setřídit jednotlivé její sloupce. Ve vodorovném směru je můžeme řadit ido více úrovní, využívat textové seznamy akseřazení použít barvy písma, barevné pozadí buňky aikony. Řazení je však možné provádět pouze podle řádku určitého čísla; první sloupec tabulky se při řazení „zamíchá“ mezi ostatní sloupce. Postup:

1.Zobrazíme dialogové okno pro řazení tabulky. Vokně klepneme na tlačítko Možnosti.

2.Vdalším zobrazeném okně označíme volbu Seřadit zleva doprava aklepneme na tlačítko OK.

3.Vseznamu Seřadit podle vybereme číslo řádku, podle kterého se mají sloupce vtabulce seřadit. Volba Data obsahují záhlaví je nepřístupná.

4.Vseznamu Pořadí vybereme vzestupné nebo sestupné řazení.

5.Tlačítkem Přidat úroveň vytvoříme řazení do dvou, popř. více úrovní.

6.Seřazení tabulky potvrdíme klepnutím na tlačítko OK.

Obrázek 1.8: Vodorovné řazení

Příklad 1.1: Řazení tabulky

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

Řešení

Řazení tabulky podle barev pozadí musí být provedeno jako první. Proto nejprve vytvoříme dvě úrovně řazení: vybereme řazení podle sloupce „Tržba“, vseznamu Řazení nastavíme volbu Barva buňky, vseznamu Pořadí zvolíme modrou barvu avposledním seznamu nastavíme volbu Nahoře. Klepneme na tlačítko Kopírovat úroveň, vseznamu Pořadí zvolíme žlutou barvu avposledním seznamu nastavíme volbu Dole.

Ve třetí úrovni řazení zvolíme sloupec „Měsíc“, ponecháme řazení podle hodnot avseznamu Pořadí klepneme na volbu Vlastní seznam. Vzobrazeném okně označíme názvy měsíců apotvrdíme tlačítkem OK. Vposlední úrovni řazení nastavíme sloupec „Tržba“, ponecháme řazení podle hodnot anastavíme sestupné řazení.

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

1.2Seskupování atvorba souhrnů

Uspořádání dlouhých tabulek je často provedeno tak, že jsou řádky (popř. sloupce) rozděleny do několika úrovní. Utakovéto tabulky je možné seskupit řádky nebo sloupce podle jednotlivých úrovní avtabulce svytvořeným seskupováním si můžeme zobrazit pouze řádky nebo sloupce, odpovídající jednotlivým úrovním. Seskupování provedeme ručně nebo automaticky.

1.2.1Ruční seskupování řádků asloupců

Pro ruční seskupování řádků nebo sloupců označíme odpovídající řádky nebo sloupce ana kartě Data ve skupině Přehled použijeme tlačítko Seskupit. Při označení celých řádků nebo sloupců se seskupení vytvoří automaticky, při označení obdélníkové oblasti se zobrazí dialogové okno, ve kterém zvolíme způsob seskupování (po řádcích nebo po sloupcích).

Obrázek 1.9: Seskupování řádků asloupců

Vtabulce se seskupenými řádky se vlevo od čísel řádků zobrazí další sloupec, ve kterém je graficky vyznačen rozsah seskupovaných řádků. Při seskupení sloupců se nad označením jednotlivých sloupců zobrazí obdobný dodatečný řádek. Grafické označení rozsahu seskupovaných řádků nebo sloupců je ukončeno tlačítkem se symbolem „minus“; toto tlačítko je umístěno udalšího řádku nebo sloupce, který následuje za seskupovaným rozsahem. Klepnutím na toto tlačítko se seskupené řádky nebo sloupce skryjí asymbol tlačítka se změní na „plus“. Dalším klepnutím na toto tlačítko se seskupené řádky nebo sloupce opět zobrazí.

Skrytí nebo zobrazení seskupovaných řádků nebo sloupců můžeme provést také tlačítky sčísly 1 a2, umístěných vlevé horní části listu. Tlačítko sčíslem 1 provede skrytí, tlačítko sčíslem 2 zobrazení seskupených řádků nebo sloupců. Stejně fungují tlačítka Zobrazit podrobnosti se zeleným znakem „plus“ aSkrýt podrobnosti sčerveným znakem „minus“, která jsou umístěna na kartě Data ve skupině Přehled. Při použití těchto tlačítek je však třeba označit buňku vřádku nebo sloupci za seskupovaným úsekem, tedy buňku vřádku nebo sloupci, ukterého je zobrazeno tlačítko se symbolem „plus“ nebo „minus“. Při použití tlačítek sčísly 1 a2 může být kurzor umístěn kdekoliv, dokonce imimo tabulku.

Skrytí seskupovaných řádků nebo sloupců může pomoci klepší orientaci vdlouhé tabulce. Utabulky svytvořeným seskupováním se tisknou jen zobrazené řádky asloupce.

Napravo od tabulky se seskupovanými řádky nebo pod tabulkou se seskupovanými sloupci není vhodné vkládat další údaje, protože při skrytí seskupovaných řádků nebo sloupců se mohou tyto údaje také skrýt.

Vtabulce můžeme vytvořit více seskupení po řádcích nebo po sloupcích ataké kombinovat seskupení řádků asloupců. Při navazování seskupovaných částí na sebe musí další vyznačená oblast řádků nebo sloupců začínat až za koncem předchozí části. Seskupovaný úsek řádků nebo sloupců můžeme také rozdělit na dva: označíme některý řádek nebo sloupec vseskupované oblasti aklepneme na tlačítko Oddělit.

Seskupování řádků má zpravidla smysl provádět podle hodnot, zapsaných vněkterém sloupci. Vtakovém případě je vhodné nejprve tabulku podle tohoto sloupce setřídit.

Odstranění některého seskupení provedeme tak, že označíme seskupované řádky nebo sloupce (bez řádku nebo sloupce, ukterého je tlačítko) ana kartě Data, vsekci Přehled klepneme na tlačítko Oddělit. Veškeré seskupování vtabulce odstraníme klepnutím na šipku utlačítka Oddělit apoužijeme příkaz Vymazat přehled.

Odstranění seskupování oběma uvedenými způsoby je nevratná akce.

1.2.2Seskupování ve více úrovních

Jestliže uvnitř seskupované části označíme několik řádků nebo sloupců apoužijeme tlačítko Seskupit, vytvoří se sekundární seskupení druhé úrovně. Voblasti řádků nebo sloupců, seskupených ve druhé úrovni, můžeme stejným postupem vytvořit seskupování ve třetí úrovni atd.

Obrázek 1.10: Tabulka se seskupením do více úrovní

Další úrovně seskupení se opět graficky vyznačí vlevo od čísla řádků nebo nad označením sloupců. Klepnutím na tlačítka se znaménky „plus“ nebo „minus“ zobrazíme nebo skryjeme řádky nebo sloupce různých úrovní. Vlevé horní části listu se zobrazí tlačítka sčísly 1, 2, 3 atd. podle počtu vytvořených úrovní při seskupování. Klepnutí na tlačítko 1 skryje všechny seskupené řádky nebo sloupce, klepnutí na tlačítko 2 skryje řádky nebo sloupce seskupené ve druhé úrovni atd.

Při tvorbě seskupování ve druhé adalších úrovních je třeba dbát na správné vyznačení seskupovaných řádků asloupců. Tlačítko se znaménkem „plus“ se umístí uřádku nebo sloupce, ležící až za vyznačenou oblastí.

1.2.3Automatický přehled

Druhým způsobem, jak vtabulce vytvořit seskupování, je využít automatické tvorby seskupování na základě vzorců. Aby bylo možné automatické seskupení vytvořit, vzorec musí zahrnovat sérii buněk nad sebou nebo vedle sebe. Jednoduchým příkladem je tabulka, která obsahuje vposledním řádku součet. Na kartě Data ve skupině Přehled klepneme na šipku utlačítka Seskupit apoužijeme příkaz Automatický přehled. Vtabulce se vytvoří seskupení, zahrnující všechny řádky vtabulce svýjimkou prvního řádku snadpisy aposledního řádku se součtem. Klepnutím na tlačítko se znaménkem „minus“ nebo na tlačítko sčíslem 1 se všechny řádky vtabulce skryjí azůstane zobrazen jen řádek snadpisy ařádek se součtem.

Vzorce, využívající buňky zněkolika řádků za sebou, vytvoří seskupení zahrnující řádky zahrnuté do vzorce. Obdobně fungují ivzorce, využívající buňky vrůzných sloupcích. Jestliže např. poslední sloupec vtabulce obsahuje součet buněk ve třech předchozích sloupcích, příkaz Automatický přehled vytvoří seskupení těchto tří sloupců.

Utabulky, obsahující vzorce vposledním řádku aposledním sloupci se příkazem Automatický přehled vytvoří seskupení po řádcích ipo sloupcích. Vytvořit automatický přehled jen pro řádky nebo sloupce není utohoto typu tabulky možné. Jestliže vtabulce, která má vzorce vposledním řádku aposledním sloupci označíme např. skupinu řádků apoužijeme příkaz Automatický přehled, zobrazí se chybové hlášení aautomatický přehled se nevytvoří.

Odstranění automatického přehledu provedeme příkazem Vymazat přehled, který zobrazíte klepnutím na šipku utlačítka Oddělit. Vytvoření iodstranění automatického přehledu jsou nevratné akce.

Utabulek, které mají obsahovat dílčí součty, průměry adalší typy souhrnů pro skupiny řádků je zpravidla výhodnější nevytvářet potřebné vzorce aautomatický přehled. Lepším postupem je tabulku seřadit avytvořit vní souhrn (viz dále).

1.2.4Tvorba souhrnů

Souhrn je nástroj, který do tabulky vloží zejména dílčí součty nebo průměry vněkterém sloupci, které zahrnují řádky se stejnou společnou hodnotou vjiném sloupci. Souhrn tedy můžeme vytvořit utabulky, obsahující aspoň dva sloupce:

■Sloupec sopakujícími se hodnotami. Tyto hodnoty tvoří skupiny, podle kterých se budou výpočty provádět.

■Sloupec sčíselnými hodnotami, ze kterých se získají dílčí součty acelkový součet nebo jiný typ souhrnu.

Aby bylo možné souhrn utabulky vytvořit, musí být splněny tyto podmínky:

■Tabulka musí být souvislá aprvní řádek obsahuje nadpisy jednotlivých sloupců. Utabulky bez nadpisů nelze souhrn vytvořit.

■Tabulka je seřazena podle sloupce shodnotami definujícími jednotlivé skupiny. Seřazení je nutné provést podle hodnot, nikoliv podle barvy písma, pozadí buňky nebo podle ikon.

■Souhrny lze vytvořit pouze po řádcích, tedy směrem „shora dolů“.

Tvorbu souhrnu provedeme tímto postupem:

1.Označíme některou buňku vtabulce.

2.Na kartě Data ve skupině Přehled klepneme na tlačítko Souhrn. Zobrazí se dialogové okno pro tvorbu souhrnu

3.Vseznamu Ukaždé změny ve sloupci označíme nadpis sloupce, podle kterého je tabulka setříděna.

4.Vseznamu Použít funkci zvolíme typ výpočtu: součet, průměr, minimum, maximum nebo počet (tato volba počítá neprázdné buňky).

5.Vseznamu Přidat souhrn do sloupce označíme nadpisy sloupců, ve kterých se mají vzorce pro souhrn vytvořit. Vzorce lze vytvořit ve více sloupcích, avšak vždy se stejným typem výpočtu. Součet aprůměr lze použít pro číselné hodnoty, minimum amaximum pro hodnoty číselné nebo datumové.

6.Při označené volbě Konec stránky mezi skupinami se ukaždé skupiny vloží za dílčím výpočtem konec stránky.

7.Označená volba Celkový souhrn pod daty znamená, že se vzorce sdílčími souhrny vloží pod jednotlivé řádky avzorce scelkovým souhrnem budou na konci tabulky. Neoznačená volba znamená, že pořadí je obrácené ařádek scelkovými souhrny se vloží na začátek tabulky.

8.Klepneme na tlačítko OK.

Obrázek 1.11: Tvorba souhrnu

Tabulka se souhrnem je seskupena po řádcích do třech úrovní: nejvyšší úrovní je celkový souhrn, druhou úrovní jsou dílčí souhrny anejnižší úrovní jsou jednotlivé řádky původní tabulky. Zobrazení askrytí jednotlivých úrovní provedeme stejným způsobem, jako uručního seskupení nebo utabulky sautomatickým přehledem.

Utabulky svytvořeným souhrnem se tisknou jen řádky vzobrazené úrovni, avšak po kopírování se vloží vždy celá tabulka isdodatečnými řádky obsahujícími vzorce.

Pro odstranění souhrnu označíme některou buňku uvnitř tabulky, klepneme na tlačítko Souhrn, vzobrazeném okně klepneme na tlačítko Odebrat vše apotvrdíme tlačítkem OK. Tato akce odstraní seskupování řádků aodstraní vložené řádky se vzorci. Vytvoření souhrnu je vratná akce, jeho odstranění je nevratné.

Utabulky svytvořenými souhrny je možné dodatečně změnit typ výpočtu:

1.Označíme některou buňku vtabulce.

2.Klepneme na tlačítko Souhrn.

3.Vseznamu Použít funkci zvolíme typ výpočtu.

4.Označíme volbu Nahradit aktuální souhrny.

5.Klepneme na tlačítko OK.

Jestliže ponecháme volbu Nahradit aktuální souhrny neoznačenou, původní souhrny vtabulce zůstanou aknim se přidají souhrny nové.

1.2.5Souhrny ve více úrovních

Souhrny můžeme vytvářet ivněkolika úrovních. Vtabulce můžeme například vytvořit součty po zaměstnancích, dále součty po pobočkách, anakonec celkový součet. Tvorba tohoto souhrnu vyžaduje seřazení tabulky do několika úrovní, přičemž jednotlivé úrovně seřazení musí odpovídat úrovním, ve kterých chceme souhrny vytvářet– řazení vyšší úrovně odpovídá vyšší úrovni souhrnu.

Postupujeme takto:

1.Vtabulce nejprve vytvoříme souhrn nejvyšší úrovně. Vseznamu Ukaždé změny ve sloupci zvolíme nadpis sloupce, ve kterém bylo vytvořeno seřazení na nejvyšší úrovni.

2.Vybereme typ výpočtu aoznačíme nadpisy sloupců, ve kterých se mají souhrny vytvořit.

3.Ponecháme kurzor vtabulce aznovu klepneme na tlačítko Souhrn.

4.Vseznamu Ukaždé změny ve sloupci vybereme nadpis sloupce, odpovídající setřídění na druhé úrovni.

5.Zrušíme označení volby Nahradit aktuální souhrny aklepneme na tlačítko OK.

6.Kroky 3 až 5 opakujeme pro každou další úroveň souhrnu.

Vytvoření víceúrovňovému souhrnu seskupí řádky vtabulce do čtyř nebo ivíce úrovní. Odstranění souhrnu provedeme stejným postupem jako ujednoduchého souhrnu. Vtabulce se odstraní všechny vytvořené souhrny.

Při tvorbě víceúrovňových souhrnů je vhodné upravit tabulku tak, aby sloupec sřazením vyšší úrovně byl vlevo od sloupce sřazením nižší úrovně. Při nevhodném pořadí sloupců sřazením je tabulka svytvořeným souhrnem nepřehledná apři použití číslovaných tlačítek unižší úrovně se označení vyšší úrovně skryje.

Příklad 1.2: Tvorba souhrnů

Vtabulce vsešitu Souhrny.xlsx vytvořte souhrny do dvou úrovní: podle měsíců apodle obchodních zástupců. Souhrny budou počítat součty tržeb.

Řešení

Vtabulce nejprve vytvoříme řazení do dvou úrovní. Vprvní úrovni seřadíme tabulku podle sloupce „Měsíc“, ponecháme řazení podle hodnot, vseznamu Pořadí klepneme na volbu Vlastní seznam avzobrazeném okně označíme názvy měsíců. Ve druhé úrovni nastavíme řazení podle sloupce „Zástupce“.

Dále vytvoříme první úroveň souhrnů. Vseznamu Ukaždé změny ve sloupci zvolíme měsíc, vseznamu Použít funkci vybereme volbu Součet avseznamu Přidat souhrn do sloupce označíme sloupec „Tržba“. Poté klepneme znovu na tlačítko Souhrn. Vseznamu Ukaždé změny ve sloupci nastavíme zástupce, zrušíme označení Nahradit aktuální souhrny aklepneme na tlačítko OK. Tím se vtabulce vytvoří souhrny do dvou úrovní.

Příklad je vyřešen vsešitu Souhrny– řešení.xlsx.

1.3Výpočet statistických charakteristik

Tlačítko se symbolem řeckého písmene Σ, umístěné na kartě Domů ve skupině Úpravy, umožňuje počítat kromě součtu také průměr, maximální aminimální hodnotu. Při zpracování rozsáhlých dat je často zapotřebí určit také další statistické charakteristiky. Funkce pro tyto výpočty jsou zahrnuty vkategorii Statistické.

1.3.1Funkce LARGE aSMALL

Tyto funkce vrátí druhou, třetí atd. nejvyšší nebo nejnižší hodnotu. Parametry funkcí jsou oblast dat ahodnota k, určující pozici hledané nejvyšší nebo nejnižší hodnoty. Oblast dat musí být souvislá, nemusí být nijak seřazena.

Výpočet funkcemi LARGE aSMALL zahrnuje číselné adatumové hodnoty, ignoruje texty, logické hodnoty aprázdné buňky. Pokud je vráceným výsledkem datum, vrátí se jako nenaformátovaná hodnota. Při opakovaných hodnotách, které vyhovují zadanému pořadí, se vrátí některá ze stejných hodnot. Proto vtomto případě mohou tyto funkce vrátit stejný výsledek pro dvě různé hodnoty parametru k.

Vzadané oblasti dat musí aspoň jedna buňka obsahovat číslo nebo datum, jinak funkce vrátí chybovou hodnotu #ČÍSLO!.

1.3.2Funkce typu RANK

Funkce typu RANK hledají pořadí zadané hodnoty vsérii dat. Excel obsahuje dvě funkce tohoto typu: RANK.EQ aRANK.AVG. Parametry obou funkcí jsou stejné:

■Číslo– hledaná hodnota.

■Odkaz– oblast hodnot kde se hledá.

■Pořadí (nepovinný parametr)– způsob hledání. Žádná hodnota nebo nula je hledání podle sestupného pořadí, libovolné číslo kromě nuly znamená hledání podle vzestupného pořadí.

Rozdíl mezi funkcemi RANK.EQ aRANK.AVG je ve zpracování duplicitních hodnot. Když se hledaná hodnota vyskytuje ve zkoumané oblasti vícekrát, funkce RANK.EQ najde první výskyt (počítáno seshora nebo zezdola), funkce RANK.AVG vrátí průměrnou hodnotu pořadí hledané hodnoty.

Funkce RANK.EQ funguje jako inverzní kfunkci LARGE (při sestupném hledání) nebo kfunkci SMALL (při vzestupném hledání). Pokud ve funkci RANK.EQ zadáme jako hledanou hodnotu výsledek funkce LARGE nebo SMALL, výsledek je pořadí, které jsme ve funkci LARGE nebo SMALL zadali jako parametr k.

1.3.3Medián

1.3.4Kvartily

■0– minimum.

■1– spodní kvartil.

■4– maximum.

1.3.5Percentily

1.3.6Relativní pozice vsadě dat

1.3.7Funkce typu EXC

1.3.8Četnosti

1.4Automatický filtr

1.4.1Filtrace podle konkrétních hodnot

1.4.2Zrušení aaktualizace filtru

1.4.3Filtrace podle číselných hodnot

1.4.4Filtrace podle datumových hodnot

1.4.5Filtrace podle textových hodnot

1.4.6Filtrace podle barev aikon

1.4.7Filtrace podle více sloupců

1.4.8Řazení utabulky sfiltrem

1.4.9Využití filtru při práci stabulkou

1.5Funkce reagující na filtr

1.5.1Funkce SUBTOTAL

1.5.2Funkce AGGREGATE

1.6Databázové funkce

1.6.1Funkce DSUMA

1.6.2Další databázové funkce

1.6.3Funkce DZÍSKAT

1.6.4Použití více podmínek

1.7Vyhledávání vtabulce

1.7.1Funkce SVYHLEDAT

1.7.2Funkce VVYHLEDAT

1.7.3Funkce VYHLEDAT

1.7.4Funkce INDEX

1.7.5Funkce POZVYHLEDAT

1.8Zpracování časových řad

1.8.1Funkce FORECAST.ETS

1.8.2Vložení listu prognózy

1.8.3Další funkce pro zpracování časových řad

Table of Contents

Úvod

1 Databázové operace v Excelu

1.1 Třídění

1.1.1 Řazení podle hodnot

1.1.2 Řazení podle více sloupců

1.1.3 Rozlišení malých a velkých písmen

1.1.4 Aktualizace řazení

1.1.5 Řazení podle seznamu

1.1.6 Řazení podle barev a ikon

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

1.2 Seskupování a tvorba souhrnů

1.2.1 Ruční seskupování řádků a sloupců

1.2.2 Seskupování ve více úrovních

1.2.3 Automatický přehled

1.2.4 Tvorba souhrnů

1.2.5 Souhrny ve více úrovních

1.3 Výpočet statistických charakteristik

1.3.1 Funkce LARGE a SMALL

1.3.2 Funkce typu RANK

1.3.3 Medián

1.3.4 Kvartily

1.3.5 Percentily

1.3.6 Relativní pozice v sadě dat

1.3.7 Funkce typu EXC

1.3.8 Četnosti

1.4 Automatický filtr

1.4.1 Filtrace podle konkrétních hodnot

1.4.2 Zrušení a aktualizace filtru

1.4.3 Filtrace podle číselných hodnot

1.4.4 Filtrace podle datumových hodnot

1.4.5 Filtrace podle textových hodnot

1.4.6 Filtrace podle barev a ikon

1.4.7 Filtrace podle více sloupců

1.4.8 Řazení u tabulky s filtrem

1.4.9 Využití filtru při práci s tabulkou

1.5 Funkce reagující na filtr

1.5.1 Funkce SUBTOTAL

1.5.2 Funkce AGGREGATE

1.6 Databázové funkce

1.6.1 Funkce DSUMA

1.6.2 Další databázové funkce

1.6.3 Funkce DZÍSKAT

1.6.4 Použití více podmínek

1.7 Vyhledávání v tabulce

1.7.1 Funkce SVYHLEDAT

1.7.2 Funkce VVYHLEDAT

1.7.3 Funkce VYHLEDAT

1.7.4 Funkce INDEX

1.7.5 Funkce POZVYHLEDAT

1.8 Zpracování časových řad

1.8.1 Funkce FORECAST.ETS

1.8.2 Vložení listu prognózy

1.8.3 Další funkce pro zpracování časových řad