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:

Konec ukázky

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.3 Výpočet statistických charakteristik

1.4 Automatický filtr

1.5 Funkce reagující na filtr

1.6 Databázové funkce

1.7 Vyhledávání v tabulce

1.8 Zpracování časových řad

2 Formátované tabulky

2.1 Tvorba a vlastnosti formátovaných tabulek

2.2 Výpočty a souhrny

3 Využití externích dat

3.1 Připojení dat z Accessu

3.2 Připojení dat z MS SQL Serveru

3.3 Práce s editorem dotazů

3.4 Připojení textových souborů

4 Kontingenční tabulky

4.1 Vytvoření kontingenční tabulky z jedné formátované oblasti

4.2 Řazení a filtrace v kontingenční tabulce

4.3 Úprava struktury, formátu a vlastností kontingenční tabulky

4.4 Výpočty v kontingenční tabulce

4.5 Vytvoření kontingenční tabulky z externích dat

4.6 Využití údajů z kontingenční tabulky

4.7 Kontingenční grafy

5 Datový model Excelu

5.2 Tvorba kontingenční tabulky s využitím datového modelu

5.3 Tvorba kontingenční tabulky s využitím relací

5.4 Použití externích dat v datovém modelu

5.5 Práce s kontingenční tabulkou vytvořenou z datového modelu

6 Výpočty v jazyku DAX

6.1 Tvorba vypočtených sloupců

6.2 Datumové a textové funkce

6.3 Práce s mírami

6.4 Funkce časového měřítka

6.5 Kalendář