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 v Excelu

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 v podobě série vzájemně propojených datových tabulek a umožňují rychlé řazení, výběry dat filtrací, provádění výpočtů s uchovanými daty a tvorbu souhrnů.

Některé základní databázové operace – řazení, filtrování, seskupování a tvorbu souhrnů můžeme provádět i v Excelu. 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 v tabulce 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 v jednotlivých sloupcích mají jednotný charakter: texty, čísla, datumové hodnoty, logické hodnoty PRAVDA a NEPRAVDA 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 v tom, že v Excelu není předem předepsán charakter údajů v jednotlivých sloupcích tabulky. Proto např. při filtraci nebo řazení musí Excel neustále zjišťovat typ údajů v jednotlivých buňkách. U relačních databází je naopak každý sloupec tabulky přesně definován jako textový, číselný, datumový atd., a proto je výkonnost relační databáze mnohonásobně vyšší.

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

1.1Třídění

Setřídění dat v tabulce 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ý a jednoduchý, 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 v jednom sloupci postavíme kurzor do zvoleného sloupce a použijeme jeden ze tří způsobů:

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

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

■Klepneme pravým tlačítkem myši na některou buňku ve zvoleném sloupci, z místní nabídky použijeme příkaz Seřadit, a dále volby Seřadit od A do Z nebo Seřadit od Z do A.

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

Vzestupné řazení řadí textové hodnoty abecedně od A do Z, číselné hodnoty od nejmenší k největší, datumové hodnoty od nejstarší k nejnovější a logické hodnoty v pořadí NEPRAVDA, PRAVDA. U sestupné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 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.

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 v tabulce a použijeme jeden ze tří způsobů:

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

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

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

Obrázek 1.3: Okno pro řazení tabulky

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

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

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

3.Seznam Pořadí určuje vzestupné nebo sestupné řazení. Pro textové hodnoty se v seznamu zobrazí možnosti A až Z nebo Z až A, pro číselné a logické hodnoty volby Od nejmenšího k největšímu nebo Od největšího k nejmenšímu a pro datumové hodnoty možnosti Od nejstaršího k nejnovějšímu nebo Od nejnovějšího k nejstaršímu.

4.Klepneme na tlačítko Přidat úroveň. Tím se v dialogové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 a dolů, odstranění označené úrovně provedeme tlačítkem Odstranit úroveň.

5.Podle potřeby přidáme další úrovně řazení a klepneme 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 v první úrovni stejná atd.

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

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

Při řazení s rozlišením malých a velký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ů v tabulce již neodpovídá nastavenému řazení. Proto si zobrazíme dialogové okno pro seřazení (v okně se zobrazí nastavené řazení) a klepneme na tlačítko OK. Tento postup použijeme i tehdy, když byla tabulka seřazena jednoduchým způsobem podle jednoho sloupce.

1.1.5Řazení podle seznamu

U sloupce s textovými hodnotami můžeme také využít řazení podle předem vytvořeného seznamu:

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

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

3.V seznamu Vlastní seznamy označíme potřebný seznam a dvakrá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ů v týdnu a zkratek dnů v týdnu. Tyto seznamy můžeme ihned použít k seřazení.

Seřazení tabulky podle názvů měsíců nebo dnů v týdnu funguje také u sloupce obsahujícího vzorce. Pokud tabulka obsahuje sloupec s datumovými hodnotami, můžeme do ní přidat sloupec s funkcí HODNOTA.NA.TEXT, ve druhém parametru funkce použít kód „mmmm“ (název měsíce) nebo „dddd“ (název dne v týdnu) a podle 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.V levé části zobrazeného okna klepneme na příkaz Upřesnit.

4.V pravé čá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 v položce Importovat seznam z buně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 a můžeme jej využít v libovolném sešitu[1].

Jestliže v okně pro tvorbu seznamu označíme vytvořený seznam, můžeme jej dodatečně upravovat: jednotlivé položky seznamu přepisovat, mazat a př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 u vlastního seznamu; standardní seznamy Excelu upravovat nebo odstraňovat není možné.

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

1.1.6Řazení podle barev a ikon

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.V seznamu Seřadit podle vybereme potřebný název nebo adresu sloupce.

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

4.Klepnutím na šipku u seznamu Pořadí zobrazíme nabídku s použ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.V poslední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í. V tomto případě postačí označit řazení podle vytvořené barvy a klepnout 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í i pro ikony.

Pro rychlé seřazení tabulky s využitím jedné barvy nebo ikony můžeme také klepnout pravým tlačítkem myši na buňku s barevným písmem, pozadím nebo ikonou. Z místní nabídky použijeme příkaz Seřadit a dá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 i na černé písmo, buňky s barevný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, a to i ve 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 i do více úrovní, využívat textové seznamy a k seřazení použít barvy písma, barevné pozadí buňky a ikony. Ř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. V okně klepneme na tlačítko Možnosti.

2.V dalším zobrazeném okně označíme volbu Seřadit zleva doprava a klepneme na tlačítko OK.

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

4.V seznamu 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 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.

Ř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“, v seznamu Řazení nastavíme volbu Barva buňky, v seznamu Pořadí zvolíme modrou barvu a v posledním seznamu nastavíme volbu Nahoře. Klepneme na tlačítko Kopírovat úroveň, v seznamu Pořadí zvolíme žlutou barvu a v posledním seznamu nastavíme volbu Dole.

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

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

1.2Seskupování a tvorba souhrnů

Uspořádání dlouhých tabulek je často provedeno tak, že jsou řádky (popř. sloupce) rozděleny do několika úrovní. U takovéto tabulky je možné seskupit řádky nebo sloupce podle jednotlivých úrovní a v tabulce s vytvoř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ů a sloupců

Pro ruční seskupování řádků nebo sloupců označíme odpovídající řádky nebo sloupce a na 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ů a sloupců

V tabulce 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 u dalšího řádku nebo sloupce, který následuje za seskupovaným rozsahem. Klepnutím na toto tlačítko se seskupené řádky nebo sloupce skryjí a symbol 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 a 2, umístěných v levé 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“ a Skrý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, u kterého je zobrazeno tlačítko se symbolem „plus“ nebo „minus“. Při použití tlačítek s čísly 1 a 2 může být kurzor umístěn kdekoliv, dokonce i mimo tabulku.

Skrytí seskupovaných řádků nebo sloupců může pomoci k lepší orientaci v dlouhé tabulce. U tabulky s vytvořeným seskupováním se tisknou jen zobrazené řádky a sloupce.

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.

V tabulce můžeme vytvořit více seskupení po řádcích nebo po sloupcích a také kombinovat seskupení řádků a sloupců. 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 v seskupované oblasti a klepneme na tlačítko Oddělit.

Seskupování řádků má zpravidla smysl provádět podle hodnot, zapsaných v některém sloupci. V takové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, u kterého je tlačítko) a na kartě Data, v sekci Přehled klepneme na tlačítko Oddělit. Veškeré seskupování v tabulce odstraníme klepnutím na šipku u tlačítka Oddělit a použ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ů a použijeme tlačítko Seskupit, vytvoří se sekundární seskupení druhé úrovně. V oblasti řá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í. V levé 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é a dalších úrovních je třeba dbát na správné vyznačení seskupovaných řádků a sloupců. 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 v tabulce 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 v posledním řádku součet. Na kartě Data ve skupině Přehled klepneme na šipku u tlačítka Seskupit a použijeme příkaz Automatický přehled. V tabulce se vytvoří seskupení, zahrnující všechny řádky v tabulce s výjimkou prvního řádku s nadpisy a poslední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 v tabulce skryjí a zůstane zobrazen jen řádek s nadpisy a řádek se součtem.

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

U tabulky, obsahující vzorce v posledním řádku a posledním sloupci se příkazem Automatický přehled vytvoří seskupení po řádcích i po sloupcích. Vytvořit automatický přehled jen pro řádky nebo sloupce není u tohoto typu tabulky možné. Jestliže v tabulce, která má vzorce v posledním řádku a posledním sloupci označíme např. skupinu řádků a použijeme příkaz Automatický přehled, zobrazí se chybové hlášení a automatický přehled se nevytvoří.

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

U tabulek, které mají obsahovat dílčí součty, průměry a další typy souhrnů pro skupiny řádků je zpravidla výhodnější nevytvářet potřebné vzorce a automatický přehled. Lepším postupem je tabulku seřadit a vytvořit v ní 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 v některém sloupci, které zahrnují řádky se stejnou společnou hodnotou v jiném sloupci. Souhrn tedy můžeme vytvořit u tabulky, obsahující aspoň dva sloupce:

■Sloupec s opakují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 a celkový součet nebo jiný typ souhrnu.

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

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

■Tabulka je seřazena podle sloupce s hodnotami 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 v tabulce.

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

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

4.V seznamu 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.V seznamu 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 a průměr lze použít pro číselné hodnoty, minimum a maximum pro hodnoty číselné nebo datumové.

6.Při označené volbě Konec stránky mezi skupinami se u kaž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 s dílčími souhrny vloží pod jednotlivé řádky a vzorce s celkovým souhrnem budou na konci tabulky. Neoznačená volba znamená, že pořadí je obrácené a řádek s celkový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 a nejnižší úrovní jsou jednotlivé řádky původní tabulky. Zobrazení a skrytí jednotlivých úrovní provedeme stejným způsobem, jako u ručního seskupení nebo u tabulky s automatickým přehledem.

U tabulky s vytvořeným souhrnem se tisknou jen řádky v zobrazené úrovni, avšak po kopírování se vloží vždy celá tabulka i s dodatečnými řádky obsahujícími vzorce.

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

U tabulky s vytvořenými souhrny je možné dodatečně změnit typ výpočtu:

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

2.Klepneme na tlačítko Souhrn.

3.V seznamu 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 v tabulce zůstanou a k nim se přidají souhrny nové.

1.2.5Souhrny ve více úrovních

Souhrny můžeme vytvářet i v několika úrovních. V tabulce můžeme například vytvořit součty po zaměstnancích, dále součty po pobočkách, a nakonec 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.V tabulce nejprve vytvoříme souhrn nejvyšší úrovně. V seznamu U kaž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 a označíme nadpisy sloupců, ve kterých se mají souhrny vytvořit.

3.Ponecháme kurzor v tabulce a znovu klepneme na tlačítko Souhrn.

4.V seznamu U kaž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 a klepneme 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 v tabulce do čtyř nebo i více úrovní. Odstranění souhrnu provedeme stejným postupem jako u jednoduchého souhrnu. V tabulce 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 s vytvořeným souhrnem nepřehledná a při použití číslovaných tlačítek u nižší úrovně se označení vyšší úrovně skryje.

Příklad 1.2: Tvorba souhrnů

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

Řešení

V tabulce nejprve vytvoříme řazení do dvou úrovní. V první úrovni seřadíme tabulku podle sloupce „Měsíc“, ponecháme řazení podle hodnot, v seznamu Pořadí klepneme na volbu Vlastní seznam a v zobrazené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ů. V seznamu U každé změny ve sloupci zvolíme měsíc, v seznamu Použít funkci vybereme volbu Součet a v seznamu Přidat souhrn do sloupce označíme sloupec „Tržba“. Poté klepneme znovu na tlačítko Souhrn. V seznamu U každé změny ve sloupci nastavíme zástupce, zrušíme označení Nahradit aktuální souhrny a klepneme na tlačítko OK. Tím se v tabulce vytvoří souhrny do dvou úrovní.

Příklad je vyřešen v seš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í a minimá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 v kategorii Statistické.

1.3.1Funkce LARGE a SMALL

Tyto funkce vrátí druhou, třetí atd. nejvyšší nebo nejnižší hodnotu. Parametry funkcí jsou oblast dat a hodnota 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 a SMALL zahrnuje číselné a datumové hodnoty, ignoruje texty, logické hodnoty a prá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 v tomto případě mohou tyto funkce vrátit stejný výsledek pro dvě různé hodnoty parametru k.

V zadané 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 v sérii dat. Excel obsahuje dvě funkce tohoto typu: RANK.EQ a RANK.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 a RANK.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í k funkci LARGE (při sestupném hledání) nebo k funkci 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.

Dřívější verze Excelu obsahovaly funkce RANK, která funguje stejně, jako funkce RANK.EQ. V novějších verzích Excelu je funkce RANK zařazena ve skupině Kompatibilita.

1.3.3Medián

Funkce MEDIAN nalezne střední hodnotu v řadě čísel. Jinými slovy řečeno, jestliže seřadíme čísla podle velikosti, je to hodnota uprostřed (lichý počet čísel) nebo průměr dvou čísel uprostřed (sudý počet). Funkce může zpracovat až 255 nesouvislých oblastí, oblasti nemusí být seřazeny. Do výpočtu se zahrnou číselné a datumové hodnoty, texty, logické hodnoty a prázdné buňky se ignorují. Jestliže je výsledkem datum, vrátí se jako nenaformátovaná hodnota.

V oblasti zpracovávaných dat musí být aspoň jedno číslo nebo datum, jinak funkce vrátí chybovou hodnotu #ČÍSLO!.

Na první pohled by se mohlo zdát, že medián funguje obdobně jako průměr – poskytne z řady čísel střední hodnotu. Medián je však definován zcela odlišným způsobem. Rozdíl mezi mediánem a průměrem se projeví zejména u řady čísel, u které je jedna nebo více hodnot řádově vyšší než ostatní. Příkladem může být řada čísel 0;200;300;400;500 a 10 000. Medián poskytne hodnotu 350, zatímco výpočet průměru 1 900. Je zřejmé, že průměr poskytuje v tomto případě obraz zcela falešný, neboť až na jednu mají všechna čísla v uvedené řadě podprůměrnou hodnotu[2].

1.3.4Kvartily

Medián zřejmě dělí skupinu číselných hodnot na dvě poloviny podle jejich velikosti: hodnoty nad mediánem patří do vyšší poloviny, pod mediánem do nižší poloviny. Obdobně je definován horní kvartil, což je hodnota, která dělí řadu čísel na nejvyšší čtvrtinu a zbytek. Spodní kvartil dělí řadu čísel na vyšších 75 % a zbytek. Pomocí této terminologie bychom mohli medián označit jako prostřední kvartil. U řady čísel 1;2;3;4 a 5 je spodní kvartil roven 2, prostřední kvartil (medián) roven 3 a horní kvartil roven 4.

Jestliže určení spodního nebo horního kvartilu nevyjde na některou konkrétní hodnotu, použije se vážený průměr ze sousedících hodnot. U spodního kvartilu je to 75% nižší a 25% vyšší hodnoty, u horního kvartilu je tomu naopak. Například v řadě čísel 1;2;3;4;5;6;7;8;9 a 10 je spodní kvartil roven 3,25, medián čili prostřední kvartil má hodnotu 5,5 a horní kvartil hodnotu 7,75.

Pro výpočet kvartilů je určena funkce QUARTIL.INC. Funkce má dva parametry: Matice je oblast zpracovávaných hodnot a Kvartil je číselný kód, označující typ kvartilu:

■0 – minimum.

■1 – spodní kvartil.

■2 – prostřední kvartil (medián).

■3 – horní kvartil.

■4 – maximum.

Oblast hodnot, zadaná v parametru Matice, musí být souvislá a nemusí být seřazena. Texty, logické hodnoty a prázdné buňky se ignorují. Je-li výsledek funkce datumová hodnota, vrátí se jako nenaformátovaná. Oblast dat v parametru Matice musí obsahovat aspoň jedno číslo nebo datum, jinak funkce vrátí chybovou hodnotu #ČÍSLO!.

1.3.5Percentily

Řadu čísel, seřazenou vzestupně podle velikosti, dělí spodní kvartil na rozhraní 25 % (počítáno od nejmenší k nejvyšší hodnotě), medián na rozhraní 50 % a horní kvartil na rozhraní 75 %. Obdobně bychom mohli tuto řadu rozdělit např. na nejmenší třetinu a zbytek, zde by bylo dělení provedeno na rozhraní 33,3 %. Obecné dělení řady čísel podle stanoveného rozhraní se označuje jako percentil. Pro výpočet percentilu musíme ovšem také zadat použité rozhraní. Jedná se tedy o zobecnění pojmů medián a kvartil.

Pro výpočet percentilu je určena funkce PERCENTIL.INC. Parametry funkce jsou dva: Matice je oblast hodnot, K je rozmezí pro výpočet percentilu, které se udává jako desetinné číslo od nuly do jedné. Oblast hodnot musí být souvislá, nemusí být seřazena. Texty, logické hodnoty a prázdné buňky se ignorují. Datumová hodnota jako výsledek se vrátí nenaformátovaná. V oblasti dat v parametru Matice musí být aspoň jedno číslo nebo datum, jinak funkce vrátí chybovou hodnotu #ČÍSLO!.

Pokud se určení percentilu netrefí do některé hodnoty v zadané řadě, spočítá se výsledek funkce jako vážený průměr ze sousedících hodnot.

Při zadání parametru K jako 0,25 funkce PERCENTIL.INC vypočítá hodnotu spodního kvartilu, u hodnoty 0,5 spočítá medián a u hodnoty 0,75 hodnotu horního kvartilu.

1.3.6Relativní pozice v sadě dat

Funkce PERCENTRANK.INC určí relativní pozici zadaného čísla v řadě číselných hodnot, uvažovaných od nejmenší k největší hodnotě. Výsledkem funkce je hodnota od nuly do jedné.

Funkce má tři parametry:

■Matice – oblast hodnot.

■X – hodnota, pro kterou se hledá pozice (nemusí být obsažena ve zpracovávané sadě dat).

■Významnost (nepovinný parametr) – počet desetinných míst ve výsledku, výchozí počet míst je 3.

Pro parametr Matice (zpracovávaná oblast dat) platí totéž, co u funkce PERCENTIL.INC. Pokud je hodnota X rovna nejmenší hodnotě v sadě dat, výsledek funkce je nula, když je hodnota X rovna největší hodnotě v sadě dat, výsledek funkce je jednička.

Funkce PERCENTRANK.INC je inverzní funkcí k funkci PERCENTIL.INC. Když ve funkci PERCENTIL.INC zadáme jako parametr K výsledek funkce PERCENTRANK.INC, výsledek je hodnota X, která byla zadaná do funkce PERCENTRANK.INC.

1.3.7Funkce typu EXC

Funkce PERCENTIL.INC, QUARTIL.INC a PERCENTRANK.INC se v dřívějších verzích Excelu jmenovaly PERCENTIL, QUARTIL a PERCENTRANK. V Excelu 2016 a 2019 jsou tyto starší funkce zařazeny ve skupině Kompatibilita.

Kromě funkcí PERCENTIL.INC, QUARTIL.INC a PERCENTRANK.INC jsou v Excelu k dispozici také funkce PERCENTIL.EXC, QUARTIL.EXC a PERCENTRANK.EXC. Fungují obdobně, jako funkce s příponou INC, ale z mezí pro rozdělení jsou vyloučeny hodnoty 0 a 1. Funkce typu EXC proto poskytují stejné výsledky jako funkce typu INC, jestliže se ze zpracovávané oblasti dat vyřadí největší a nejmenší hodnota.

Parametry funkcí typu EXC jsou stejné, jako u funkcí typu INC, ale pro zadané rozdělení platí následující omezení:

■U funkce PERCENTIL.EXC nelze jako parametr K zadat hodnotu 0 nebo 1, výsledkem funkce je v tomto případě chyba #ČÍSLO!

■U funkce QUARTIL.EXC nejde jako hodnotu K zadat nulu nebo číslo 4, výsledkem funkce je v tomto případě chyba #ČÍSLO!

■U funkce PERCENTRANK.EXC je možné jako parametr X zadat nejmenší nebo největší hodnotu ze sady dat. Výsledkem funkce však není nula nebo jednička, ale hodnota, vypočtená „protažením“ sady dat tak, aby se na začátek a konec v mezích pro rozdělení přidaly hodnoty 0 a 1.

1.3.8Četnosti

Funkce ČETNOSTI spočítá počty hodnot podle rozdělení do zadaných intervalů. Funkce má dva parametry:

■Data – oblast buněk s analyzovanými hodnotami.

■Hodnoty – oblast buněk s mezemi jednotlivých intervalů.

Výsledkem funkce je oblast s jednotlivými počty, umístěnými do sloupce pod sebe (oblast pro výsledek je stejně velká, jako oblast buněk s mezemi). Protože funkce vrací více hodnot, vytváří maticový vzorec, který je nutno uložit klávesovou kombinací CRTL + SHIFT + ENTER.

Výpočet četností se provede podle těchto pravidel:

■Funkce bere v úvahu číselné a datumové hodnoty. Prázdné buňky, textové a logické hodnoty se ignorují.

■Datumové hodnoty se chápou jako pořadové číslo dne od 1.1.1900, tj. jako nenaformátované datum.

■Hodnoty, které jsou menší, než nejmenší mez se započítají do prvního intervalu.

■Hodnoty, které jsou přesně rovny některé z mezí, se započítají do intervalu, určeného touto mezí.

■Hodnoty, které jsou vyšší než nejvyšší mez, započítány nejsou.

V parametru Hodnoty mohou být i vzorce. Jako poslední mez proto můžeme použít funkci MAX s oblastí testovaných dat.

Funkci ČETNOSTI můžeme použít i pro rozdělení hodnot podle percentilů, např. pro meze 0–20 %, 20–40 % atd. V tomto případě budou jako hodnoty mezí použity výsledky funkce PERCENTIL.INC.

Příklad 1.3: Statistické výpočty

V sešitu Statistika.xlsx vytvořte vzorce, počítající z datové tabulky druhou nejmenší hodnotu, druhou největší hodnotu, průměr, medián, horní a spodní kvartil. Dále vytvořte tabulku četností pro meze 0–5, 5–20 a nad 20.

Řešení

Druhou nejmenší hodnotu a druhou největší hodnotu spočítáme pomocí funkcí SMALL a LARGE. Prvním parametrem je oblast dat, druhým číslo 2. Dále použijeme funkce PRŮMĚR, MEDIAN a QUARTIL.INC. U této funkce použijeme jako první parametr oblast dat, jako druhý čísla 3 (horní kvartil) a 1 (spodní kvartil).

Pro použití funkce ČETNOSTI vytvoříme oblast mezí: čísla 5 a 20 a poslední mezí bude funkce MAX (parametrem je oblast dat). Prvním parametrem funkce ČETNOSTI je oblast, druhou meze. Vzorec s funkcí ČETNOSTI musíme uložit pomocí klávesové kombinace ALT + CTRL + DELETE.

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

1.4Automatický filtr

Důležitou databázovou operací je filtrace – výběr řádků v tabulce podle určeného kritéria. Excel poskytuje dvě možnosti filtrace:

■Automatický filtr, kde se filtrační kritérium nastaví přímo v tabulce.

■Filtr (v dřívějších verzích Excelu označovaný jako „rozšířený“), kde se filtrační kritérium zapíše do buněk mimo tabulku.

Druhý způsob filtrace je pozůstatkem dřívějších verzí Excelu. Práce s ním je méně pohodlná a poskytuje podstatně méně možností. Proto se budeme zabývat pouze automatickým filtrem, který je u současných verzí Excelu běžně používaný.

1.4.1Filtrace podle konkrétních hodnot

Nejjednodušším způsobem filtrace je výběr jedné nebo více konkrétních hodnot v určitém sloupci. K jednoduché filtraci podle jedné konkrétní hodnoty můžeme klepnout pravým tlačítkem myši na buňku s touto hodnotou, z místní nabídky použít příkaz Filtr a volbu Použít filtr podle hodnoty vybrané buňky. V tabulce se vytvoří filtr podle zvolené hodnoty a v buňkách s nadpisy sloupců se zobrazí tlačítka se šipkou. Na tlačítku u sloupce, použitého pro filtraci, se zobrazí obrázek trychtýře a zároveň se čísla řádků, ve kterých je filtrovaná tabulka umístěna, označí modře.

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