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.

Automatizovaná analýza textů nebo dat ve smyslu čl. 4 směrnice 2019/790/EU a použití této knihy k trénování AI jsou bez souhlasu nositele práv zakázány.

Marek Laurenčík

Excel 2021

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 9237. publikaci

Spoluautor Michal Bureš

Odpovědný redaktor Petr Somogyi

Sazba Petr Somogyi

Ikony Freepik (www.flaticon.com)

Počet stran 184

První vydání, Praha 2024

Tisk Iva Vodáková – Durabo

© Grada Publishing, a.s., 2024

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

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-7270-2 (ePub)

ISBN 978-80-271-7269-6 (pdf)

ISBN 978-80-271-3937-8 (print)

Úvod

Možnost práce s rozsáhlými tabulkami chovajícími se obdobně jako databáze patří mezi hlavní přednosti tabulkového kalkulátoru MS Excel. Je jim věnována tato kniha, která je orientována na poslední verzi Excel 2021. Seznámíte se v ní s těmito tématy:

■Zpracování dlouhých datových tabulek: Sem patří řazení a filtrace různými způsoby, tvorba přehledů a souhrnů, základní statistické výpočty a popis funkcí Excelu určených pro zpracování tabulek: agregační funkce reagující na filtr použití, funkce vyhledávací a databázové a funkce pro zpracování časových řad.

■Práce s formátovanými tabulkami: V posledních verzích se v Excelu silně zvyšuje jejich význam, protože tento způsob přístupu k datům má četné výhody.

■Práce s dotazy pomocí editoru Power Query: Tento nástroj je určen pro efektivní spojení mezi dvěma sešity Excelu a pro připojení externích dat. Vzhledem k rozsáhlým možnostem, které editor Power Query poskytuje, jsme jej zařadili do samostatné kapitoly.

■Práce s externími daty: Zde je rozebráno připojení datových zdrojů z databáze MS Access a MS SQL Server, dále pak připojení a přímý import textových souborů. Poslední část kapitoly je věnována komunikaci Excelu se soubory typu XML.

■Práce s kontingenčními tabulkami a kontingenčními grafy: Tato kapitola popisuje tvorbu kontingenční tabulky z různých typů dat – z datové tabulky Excelu, z dotazu a pomocí připojení na externí data. 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ů. V kapitole je také popsána tvorba kontingenčních grafů.

■Datový model Excelu: Použití datového modelu představuje výrazné rozšíření možností, jež nabízí tvorba kontingenčních tabulek. Jedná se o využití relací, tvorbu míry a klíčového ukazatele výkonu.

■Výpočty v jazyku DAX: Použití tohoto nástroje poskytuje při tvorbě kontingenčních tabulek pomocí datového modelu mnoho dalších možností. Umožňuje například zobrazovat data s různým časovým posuvem a využívat vytvořený kalendář.

V knize probíraný materiál na některých místech odkazuje na nástroje Excelu, které nejsou do knihy zařazeny. V takových případech odkazujeme čtenáře na naši knihu Excel 2016 a 2019 – pokročilé nástroje, kterou vydalo nakladatelství Grada Publishing.

Protože má kniha omezený rozsah, nebylo možné popsat některá témata do všech podrobností. Týká se to zejména editoru Power Query a funkcí jazyka DAX. Nicméně podle našich vlastních zkušeností (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) by měl rozsah knihy pokrýt velkou většinu problémů, s nimiž se můžete v praxi setkat.

Valnou většinu postupů uvedených v knize je možné používat i v předchozí verzi Excelu 2019. Výjimku tvoří nové funkce, které jsme popsali co nejpodrobněji. Starší verze funkcí jsou rovněž zmíněny (týká se to především vyhledávacích funkcí).

Jednotlivé kapitoly knihy jsou doplněny řadou příkladů. Zkušební data a řešení příkladů si můžete stáhnout v sekci této knihy na webu nakladatelství Grada (www.grada.cz).

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í a volby v nabídkách jsou označeny tučnou kurzívou.

■Názvy sešitů a listů jsou psány kurzivou.

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

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

Ať se vám daří!

Autoři

1Databázové operace vExcelu

Při vyhodnocení rozsáhlých datových tabulek musíme vExcelu často používat řazení, filtrování, seskupování atvorbu souhrnů. Tyto operace se často označují jako databázové ajsou typické pro programy označované jako relační databáze. Ty jsou určeny pro efektivní zpracování rozsáhlého množství dat.

Knejpoužívanějším relačním databázím patří MS Access aMS SQL Server (používané hlavně pro kancelářské účely), MySQL aMariaDB (používané kuchování informací na webových stránkách) nebo Oracle (velmi výkonný databázový program, určený pro zpracování dat velkých rozměrů). Relační databáze uchovávají datovou agendu vpodobě série vzájemně propojených datových tabulek.

Zpracování zvlášť velkých objemů dat je vExcelu obtížné. Hlavní důvod je vtom, že charakter údajů vjednotlivých sloupcích tabulky není předem předepsán. Proto při práci sdaty 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ý, kalendářní 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ů zpravidla 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ým vrelační databázi apři jejich zpracování využít všechny možností, které Excel poskytuje.

Excel však umožňuje přistupovat kdatovým tabulkám rozumných rozměrů také databázovým způsobem. Tyto rysy Excelu se vjeho posledních verzích (zhruba od Excelu 2013) silně rozšířily. Tabulka vExcelu, jež se chová jako jednoduchá databáze,[1] musí splňovat některé podmínky:

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

■První řádek tabulky obsahuje nadpisy jednotlivých sloupců. Jednotlivé sloupce jsou nadpisem jednoznačně charakterizovány, proto nesmí být nadpisy sloupců duplicitní. Nadpisy nesmí být umístěné na několika řádcích (například se sloučenými buňkami).

■Jednotlivé sloupce musí obsahovat údaje stejného typu, tedy texty, čísla, kalendářní hodnoty, logické hodnoty PRAVDA aNEPRAVDA nebo vzorce, poskytující výsledky stejného charakteru.

Vdalším textu budeme pod pojmem „tabulka“ rozumět oblast buněk, splňující uvedené požadavky. Pojmem „oblast“ budeme označovat libovolnou sérii buněk, která tyto požadavky nesplňuje, anemůže proto sloužit jako databáze.

1.1 Jednoduché akce stabulkou

1.1.1 Ukotvení nadpisů

Udlouhé tabulky je nepříjemné, že při odrolování řádků již nejsou vidět nadpisy jednotlivých sloupců. Můžeme to vyřešit takto: označíme buňku ve sloupci Avřádku těsně pod nadpisy. Na kartě Zobrazení klepneme na tlačítko Ukotvit příčky (skupina Okno) adále na volbu Ukotvit příčky. Všechny řádky nad aktivním řádkem jsou odděleny vodorovnou čárou apři rolování řádku dolů jsou stále vidět.

Když při této akci není označena buňka ve sloupci A, ukotvení se provede isvisle usloupců vlevo od aktivního sloupce. Po ukotvení nefunguje akce ZPĚT, je nutno označit buňku, kde se ukotvení provedlo, aznovu použít tlačítko Ukotvit příčky. Volba Ukotvit příčky se změní na Uvolnit příčky. Ukotvení se ukládá spolu se sešitem.

Vtlačítku Ukotvit příčky je ještě další volba Ukotvit horní řádek, která ukotví vždy první řádek na listu bez ohledu na pozici kurzoru. Toto ukotvení se zruší opět tlačítkem Ukotvit příčky avolbou Uvolnit příčky.

1.1.2 Opakování nadpisů při tisku

Když se má list sdlouhou tabulkou vytisknout nebo převést do souboru PDF, je vhodné, aby se nadpisy sloupců opakovaly na každé stránce. Provede se to následujícím postupem:

1.Na kartě Rozložení stránky klepneme na malý čtvereček vpravém dolním rohu skupiny Vzhled stránky.

2.Vzobrazeném okně přejdeme na kartu List aklepneme do položky Nahoře opakovat řádky.

3.Klepneme do řádku snadpisy (kurzor myší má tvar šipky směřující doprava). Vpoložce se zobrazí číslo řádku (například „$3:$3“).

4.Potvrdíme tlačítkem OK.

Obrázek 1.1: Opakování nadpisů

Toto nastavení platí pouze na aktivním listu auloží se společně se sešitem. Ukotvení řádku snadpisy ajejich opakování při tisku jsou dvě na sobě nezávislé akce.

1.1.3 Vzorce vracející více hodnot

Kromě běžných vzorců, jejichž výsledkem je jediná hodnota, je možné vExcelu vytvářet ivzorce produkující více výsledků. Příkladem je násobení jednoho sloupce čísel druhým. Více výsledných hodnot vrací také řada funkcí. Excel 2021 obsahuje řadu nových funkcí tohoto typu, určených pro práci stabulkami. Vzorec vracející více hodnot se označuje jako maticový aoblast vrácených výsledků jako matice.

Vpředchozích verzích Excelu bylo nutné vyznačit oblast pro výslednou matici avzorec uložit klávesovou zkratkou CTRL+SHIFT+ENTER. Excel 2021 nabízí jednodušší postup. Stačí označit jednu buňku apo vytvoření vzorce potvrdit klávesou ENTER. Další vrácené hodnoty se umístí ve směru dolů, doprava nebo vobou směrech avytvoří matici vrácených hodnot. Totéž platí ipro vkládání funkce vracející více hodnot. Přitom platí tato pravidla:

■Oblasti vstupující do vzorce mají stejnou velikost aorientaci – výsledkem je stejně orientovaná matice (sloupec, řádek nebo obdélník).

■Oblasti vstupující do vzorce mají stejnou orientaci, ale různou velikost – výsledkem je matice svýsledky odpovídající menší oblasti, další buňky obsahují chybu #NENÍ_K_DISPOZICI.

■Jedna vstupní oblast tvoří řádek, druhá sloupec – výsledkem je obdélníková matice, kde se hodnoty zobou vstupních oblastí kombinují způsobem „každý skaždým“.

Při tvorbě maticového vzorce je nutné dbát na to, aby bylo směrem dolů adoprava od buňky se vzorcem dostatečné volné místo. Pokud není nějaká buňka budoucí matice prázdná, vzorec se nevytvoří azobrazí se chyba „#PŘESAH DAT!“. Po vymazání této buňky se vzorec vytvoří správně.

Obrázek 1.2: Maticový vzorec

Při označení některé vrácené hodnoty se celá matice orámuje. Ubuňky vlevo nahoře je vřádku vzorců zobrazen vytvořený vzorec, který zůstane zobrazen i po klepnutí do řádku vzorců. Udalších buněk je vzorec zobrazen slabě šedou barvou apo klepnutí do řádku vzorců zobrazený vzorec zmizí.

Manipulace smaticovým vzorcem vypadá takto:

■Mazání: První buňku vmatici (kde se vzorec vytvořil) lze mazat, pak zmizí celá matice. Další buňky mazat nelze.

■Přepis: Vprvní buňce je možné vzorec upravit. Při přepisu první buňky pevnou hodnotou zbytek matice zmizí. Při přepisu další buňky vmatici výsledků se vprvní buňce zobrazí chyba „#PŘESAH DAT!“, obsah ostatních buněk vmatici zmizí.

■Formátování: Úprava formátu ujedné buňky vmatici se nepřenese do dalších buněk.

■Kopírování: Stačí zkopírovat první buňku, po vložení se zkopíruje celá matice. Adresy výchozích buněk se pozmění běžným způsobem.

■Tvorba dalšího vzorce: Jednotlivé buňky vmatici vstupují do dalšího vzorce nezávisle na sobě. Pokud se má vytvořit další matice, při tvorbě následného vzorce je třeba výchozí matici celou označit.

Matici hodnot vrátí i funkce, jestliže místo odkazu na vstupní buňku zadáme odkaz na vstupní oblast.

1.2 Řazení tabulky

Seřazení dat vtabulce můžeme provést třemi způsoby:

■Použít seřazení tabulky podle jednoho sloupce – jednoduchý arychlý způsob somezenými možnostmi.

■Využít dialogové okno pro seřazení tabulky – tento způsob umožňuje využít všechny možnosti řazení.

■Provést řazení pomocí funkce – Excel 2021 má kdispozici dvě funkce pro řazení, které vrací matici se seřazenou tabulkou.

1.2.1 Rychlé seřazení tabulky

Obrázek 1.3: Karta Data

Pro rychlé seřazení tabulky podle hodnot vjednom sloupci označíme některou buňku vezvoleném sloupci 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, dále pak volby Seřadit od Ado Znebo Seřadit od Zdo A.

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

První řádek tabulky, obsahující nadpisy, se považuje za záhlaví.

Textové hodnoty jsou seřazeny abecedně od Ado Zpři vzestupném aod Zdo Apři sestupném řazení. Číselné hodnoty jsou řazeny od nejmenších knejvětším při vzestupném řazení aopačně při sestupném. Hodnoty kalendářních dat jsou seřazeny chronologicky od nejstarších knejnovějším při vzestupném aobráceně při sestupném řazení. Logické hodnoty jsou při vzestupném řazení uspořádány vpořadí NEPRAVDA, PRAVDA, při sestupném řazení naopak.

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.2.2 Řazení podle více sloupců

Popsaným postupem je možné provést řazení tabulky jen podle jednoho sloupce. Pro seřazení podle hodnot ve více sloupcích musíme použít dialogové okno pro řazení. Označíme nějakou buňku vtabulce aokno zobrazíme jedním ze tří způsobů:

■Klepneme na tlačítko Seřadit na kartě Data (skupina Seřadit afiltrovat).

■Klepneme na tlačítko Seřadit afiltrovat na kartě Domů (skupina Úpravy) apoužijeme příkaz Vlastní řazení.

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

Obrázek 1.5: Okno pro řazení

Vdialogovém okně pro řazení použijeme tato nastavení:

1.Při označené volbě Data obsahují záhlaví se první řádek vtabulce považuje za nadpisy sloupců. Sloupce pro řazení jsou určeny jejich nadpisy. Když volba není označená, první řádek vtabulce se považuje za řádek sdaty asloupce pro řazení určíme podle jejich adresy (sloupec „B“, „C“ atd.)

2.Vseznamu Seřadit podle vybereme název sloupce, popřípadě odpovídající písmeno sloupce.

3.Vseznamu Pořadí nastavíme 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 kalendářní 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 vseznamu řazení zařadí do dalšího řádku. Vybereme sloupec azpůsob řazení. Podle potřeby přidáme další úrovně řazení.

5.Pořadí úrovní pro řazení je možné měnit tlačítky se šipkami nahoru adolů, odstranění označené úrovně provedeme tlačítkem Odstranit úroveň.

6.Klepneme na tlačítko OK.

Jednotlivé úrovně řazení, jak jsou nastaveny vdialogovém okně, se provádějí shora dolů: ú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.

Při řazení podle textových hodnot se standardně nerozlišují 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 1.6: Další možnosti řazení

1.2.3 Aktualizace ř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í. Vtomto případě znovu zobrazíme dialogové okno pro seřazení aklepneme na tlačítko OK. Platí to ipro případ, kdy byla tabulka seřazena jednoduchým způsobem podle jednoho sloupce. Nastavené řazení se ukládá spolu se sešitem. Proto je možné po přepisu některé buňky aktualizovat řazení vtabulce itehdy, když byl sešit mezitím uložen azavřen.

1.2.4 Řazení podle seznamu

Instalace Excelu obsahuje seznam měsíců, římských čísel měsíců, dnů vtýdnu azkratek dnů vtýdnu. Tyto seznamy je možné použít křazení, tedy sloupec snázvy měsíců seřadit vpořadí leden–prosinec, nikoliv abecedně. Řazení podle předem vytvořeného seznamu provedeme takto:

1.Vdialogovém okně pro řazení vybereme sloupec, kde se má nastavit řazení.

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

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

Vpřípadě dnů vtýdnu anázvů měsíců vroce není nutné, aby byly ve sloupci zapsány textové hodnoty. Jestliže sloupec skalendářními hodnotami naformátujeme kódem „mmmm“ (název měsíce slovem) nebo „dddd“ (den vtýdnu slovem), je možné itento sloupec použít křazení podle seznamu. Řazení podle vlastního seznamu je možné libovolně kombinovat sřazením podle jiných sloupců.

Seřazení tabulky podle názvů měsíců nebo dnů vtýdnu funguje i usloupce obsahujícího vzorce. Obsahuje-li tabulka sloupec skalendářní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.

Obrázek 1.7: Výběr seznamu

Excel umožňuje vytvořit si ivlastní seznamy pro řazení. Další seznamy vytvoříme následujícím postupem:

1.Texty vytvářeného seznamu zapíšeme do souvislé oblasti buněk (může to být sloupec nebo řádek). Oblast 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 klepneme na tlačítko Upravit vlastní seznamy (je umístěné skoro na konci). 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 označené texty přenesou do levé části okna jako vlastní nový seznam.

6.Klepneme na tlačítko OK.

Obrázek 1.8: Tvorba vlastního seznamu

Vlastní seznam je součástí instalace Excelu amůžeme ho využít vlibovolném sešitu.[2]

Jestliže vokně pro tvorbu seznamu označíme vytvořený seznam, lze jej dodatečně upravovat: můžeme 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é.

1.2.5 Ř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 podmíněného formátování.

4.Klepneme na šipku useznamu Pořadí. Tím se zobrazí nabídka 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ídka použitých ikon. Označíme potřebnou barvu nebo ikonu.

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

Obrázek 1.9: Řazení podle barev

Použití více barev vjednom sloupci vyžaduje vytvořit více úrovní řazení. Totéž platí ipro ikony – minimální počet jsou tři nebo více ikon, je-li to potřeba. Pro tvorbu další úrovně označíme řádek sřazením podle barvy aklepneme na tlačítko Kopírovat úroveň. Tím se do dialogu vloží další řádek 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 je možné také zvolit nějakou buňku sbarevným písmem, pozadím nebo ikonou aklepnout na ni pravým tlačítkem myši. 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 sřazením podle hodnoty buněk, ato ive stejném sloupci.

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

Tabulku můžeme seřadit isměrem zleva doprava, tedy setřídit jednotlivé její sloupce. Řazení ve vodorovném směru je možné provést pouze podle čísla řádku na listu, první sloupec vtabulce se vždy chápe jako sloupec sdaty.

Řazení ve vodorovném směru je možné provádět do více úrovní, pro řazení lze využívat seznamy aprovádět řazení podle barvy písma, barevného pozadí buňky nebo ikon:

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 Řazení zvolíme způsob řazení.

5.Vseznamu Pořadí vybereme řazení vzestupné, sestupné nebo pomocí seznamu.

6.Pro řazení do dvou, případně více úrovní použijeme tlačítko Přidat úroveň.

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

1.2.7 Řazení pomocí funkce

Excel 2021 obsahuje dvě nové funkce, které seřadí vstupní oblast avrátí její seřazenou podobu jako matici. Funkce SORT provádí seřazení tabulky podle jednoho sloupce. Funkce SORTBY umožňuje řazení do více úrovní. Řazení může být vzestupné nebo sestupné, ve svislém ivodorovném směru. Obě funkce fungují následujícím způsobem:

■Po přepisu výchozí tabulky se výsledek ihned aktualizuje.

■Výsledná matice nemůže obsahovat řádek snadpisy sloupců. Pokud mají být jednotlivé sloupce výsledku nadepsány, je třeba oblast snadpisy zkopírovat afunkci vložit do buňky pod první nadpis.

■Výsledná matice obsahuje nenaformátované hodnoty (včetně kalendářních dat).

Parametry funkce SORT:

■Pole – oblast buněk pro seřazení, tedy tabulka bez nadpisů.

■Index_řazení – pořadové číslo sloupce nebo řádku, podle kterého se určuje řazení. Pořadí se chápe vrámci oblasti, zadané vparametru Pole.

■Pořadí_řazení – hodnota 1 (výchozí) je vzestupné řazení, hodnota – 1 je řazení sestupné.

■Podle_sloupce – hodnota NEPRAVDA nebo 0 (výchozí) je řazení po řádcích (shora dolů), hodnota PRAVDA nebo 1 řazení po sloupcích (zleva doprava).

Parametry funkce SORTBY:

■Pole – oblast tabulky bez nadpisů.

■Podle_pole1 – první oblast (sloupec nebo řádek), podle které se řadí.

■Pořadí_řazení1 – hodnota 1 (výchozí) je vzestupné řazení, hodnota –1 je řazení sestupné.

Při řazení do více úrovní se použijí další dvojice parametrů Podle_pole2 aPořadí_řazení2 atd. Uprvní úrovně řazení se vparametru Pořadí_řazení1 uplatní výchozí hodnota 1, udalších dvojic hodnot je nutné pořadí vždy zadat.

Pokud se má řazení provést ve vodorovném směru, oblast zadaná ve druhém parametru tvoří řádek. Velikost oblasti pro řazení (sloupec nebo řádek) musí být vsouladu svelikostí tabulky, jinak funkce vrátí chybu „#HODNOTA!“.

Při kopírování matice vytvořené funkcemi SORT nebo SORTBY je nutné použít příkaz Vložit jinak. Při obyčejném vložení jsou zkopírované hodnoty chybné, nebo se vcílové buňce zobrazí chybová hodnota „#ODKAZ!“.

Příklad 1.1: Řazení tabulky

Tabulku na listu Data1 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.

Do tabulky na listu Data2 vsešitu Řazení.xlsx přidejte vzorec, který tuto tabulku seřadí podle jména zástupce vzestupně adále podle kalendářního data sestupně.

Řešení:

Řazení první 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í vybereme 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í.

Udruhé tabulky použijeme funkci SORTBY. Prvním parametrem je oblast tabulky bez nadpisů, druhým oblast jmen, třetím číslo 1, čtvrtým oblast kalendářních dat apátým číslo −1. První sloupec vmatici výsledků naformátujeme na datum.

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

1.3 Seskupování atvorba souhrnů

Urozsáhlých tabulek je často výhodné uspořádat data tak, aby byly řádky (popř. sloupce) rozděleny do několika úrovní. Utakto upravené tabulky je pak možné seskupit řádky nebo sloupce podle jednotlivých úrovní. Vtabulce svytvořeným seskupováním si můžeme zobrazit pouze řádky nebo sloupce odpovídající jednotlivým úrovním. Seskupování řádků asloupců je možné provést ručně nebo automaticky.

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

Při ručním seskupování řádků nebo sloupců označíme odpovídající řádky nebo sloupce ana kartě Data klepneme na tlačítko Seskupit (skupina Přehled). Pokud jsme označili celé řádky nebo celé sloupce, seskupení se 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.10: Volba seskupování

Vtabulce se seskupenými řádky se zobrazení buněk posune trochu doprava. Vlevo od čísel řádků je zobrazen sloupec, ve kterém je graficky vyznačen rozsah seskupovaných řádků. Při seskupení sloupců se zobrazí obdobný dodatečný řádek nad označením jednotlivých sloupců.

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í.

Pro skrytí nebo zobrazení všech seskupovaných řádků nebo sloupců jsou určena tlačítka sčísly 1 a2, umístěná vhorní části dodatečného sloupce (useskupení řádků) nebo vlevé části dodatečného řádku (useskupení sloupců). Skrytí seskupených řádků nebo sloupců provedeme klepnutím na tlačítko sčíslem 1, jejich zobrazení klepnutím na tlačítko sčíslem 2. 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 (skupina 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ů se většinou provádí 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 klepneme na tlačítko Oddělit (ve skupině Přehled). Veškeré seskupování vtabulce odstraníme klepnutím na šipku utlačítka Oddělit apoužitím příkazu Vymazat přehled.

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

Seskupování řádků asloupců je možné provádět do více úrovní. Uvnitř seskupované části označíme několik řádků nebo sloupců aklepneme na tlačítko Seskupit. Tím se vytvoří 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.11: Tabulka se seskupením

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í. Vdodatečném sloupci nebo řádku 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ích až za vyznačenou oblastí.

1.3.2 Automatický přehled

Seskupení řádků asloupců vtabulce je možné vytvořit také automaticky na základě vzorců. Na kartě Data klepneme na šipku utlačítka Seskupit (skupina Přehled) aznabídky použijeme příkaz Automatický přehled. Vtabulce se vytvoří seskupení zahrnující řádky nebo sloupce (popřípadě obojí) sbuňkami, které jsou použity ve vzorcích. Klepnutím na tlačítko se znaménkem „minus“ nebo na tlačítko sčíslem 1 se všechny tyto řádky nebo sloupce vtabulce skryjí. Vždy zůstane zobrazen řádek snadpisy ařádky nebo sloupce se vzorci.

Pro vytvoření automatického seskupování je zapotřebí, aby tabulka obsahovala (nejlépe na konci) vzorce zahrnující alespoň dvě buňky. Když jsou vzorce umístěny vposledním sloupci, zahrnují buňky ve stejném řádku. Pokud jsou vzorce umístěny vposledním řádku na konci tabulky, zahrnují buňky ve sloupci nad vzorcem. Do seskupení se zahrnou řádky nebo sloupce srozsahem buněk vstupujících do vzorců až ke sloupci nebo řádku se vzorci. Pokud je vtomto rozsahu sloupec nebo řádek sjinými buňkami, které ve vzorcích použity nejsou, zahrne se rovněž do seskupení.

Typickým příkladem je tabulka, která obsahuje vposledním řádku nebo vposledním sloupci součty. Do seskupení se zahrnou všechny řádky kromě řádku snadpisy ařádku se součty. Obdobně utabulky, kde 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říklad 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 klepnutím na šipku utlačítka Oddělit apříkazem Vymazat přehled. 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ší tabulku seřadit avytvořit vní souhrn (viz dále).

1.3.3 Tvorba souhrnů

Vytvoření souhrnů vloží do tabulky další řádky, které vjednom nebo více sloupcích obsahují nejčastěji dílčí součty nebo jiné souhrnné vzorce (průměr, počet, minimum, maximum), jež zahrnují buňky vřádcích majících vjiném sloupci společnou hodnotu. Souhrn tedy můžeme vytvořit utabulky obsahující alespoň 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í tvořit databázi, tedy je souvislá avprvním řádku má nadpisy jednotlivých sloupců. Udatové oblasti 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, je možné provést iřazení svyužitím seznamu. Při seřazení tabulky podle barvy písma, pozadí buňky nebo podle ikon se souhrny nevytvoří správně.

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

Souhrn nejde vytvořit umatice vytvořené funkcemi SORT nebo SORTBY.

Tvorbu souhrnu provedeme tímto postupem:

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

2.Na kartě Data klepneme na tlačítko Souhrn (skupina Přehled). 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, Počet čísel nebo Počet (tato volba počítá neprázdné buňky). Volba Počet čísel odpovídá funkci POČET, volba Počet funkci POČET2.

5.Vseznamu Přidat souhrn do sloupce označíme nadpisy sloupců, ve kterých se mají vzorce pro souhrn vytvořit. Vzorce mohou být 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, maximum apočet čísel pro hodnoty číselné nebo kalendářní apočet pro kterýkoliv typ hodnot.

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 (výchozí nastavení) 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é: na začátku tabulky je řádek scelkovými souhrny, potom řádky sdílčími souhrny anakonec řádky sdetaily.

8.Klepneme na tlačítko OK.

Tabulka se souhrnem obsahuje seskupení po řádcích do tří ú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, tedy nejjednodušeji pomocí tlačítek sčísly 1, 2 a3.

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í vložené řádky se vzorci azruší seskupování řádků.

Vytvoření souhrnu je vratná akce, jeho odstranění je nevratné.

Obrázek 1.12: Tvorba souhrnů

Utabulky svytvořenými souhrny je možné dodatečně změnit typ výpočtu azměnit výběr sloupců, kde se souhrn provádí:

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.Podle potřeby upravíme volby vseznamu Přidat souhrn do sloupce.

1.3.4 Souhrny ve více úrovních

Postupujeme takto:

1.4 Výpočet statistických charakteristik

1.4.1 Funkce LARGE aSMALL

1.4.2 Funkce typu RANK

1.4.3 Medián

1.4.4 Kvartily

1.4.5 Percentily

1.4.6 Výpočet relativní pozice vsadě dat

1.4.7 Funkce ČETNOSTI

1.5 Filtrace

1.5.1 Filtrace podle konkrétních hodnot

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

1.5.3 Zrušení aaktualizace filtru

1.5.4 Automatický filtr

1.5.5 Uložení automatického filtru

1.5.6 Filtrace ve sloupci sčíselnými hodnotami

1.5.7 Filtrace ve sloupci skalendářními hodnotami

1.5.8 Filtrace ve sloupci stextovými hodnotami

1.5.9 Filtrace podle barev aikon

1.5.10 Filtrace podle více sloupců

1.5.11 Řazení utabulky sfiltrem

1.5.12 Použití funkce FILTER

1.6 Funkce reagující na filtr

1.6.1 Funkce SUBTOTAL

1.6.2 Funkce AGGREGATE

1.7 Databázové funkce

1.7.1 Funkce DSUMA

1.7.2 Další databázové funkce

1.7.3 Funkce DZÍSKAT

1.7.4 Použití více podmínek

1.8 Vyhledávání vtabulce

1.8.1 Hledání podle hodnot – funkce XLOOKUP

1.8.2 Hledání pořadí – funkce XMATCH

1.8.3 Funkce INDEX

1.9 Zpracování časových řad

1.9.1 Vložení listu prognózy

1.9.2 Funkce FORECAST.ETS

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

Table of Contents

Úvod

1 Databázové operace v Excelu

1.1 Jednoduché akce s tabulkou

1.1.1 Ukotvení nadpisů

1.1.2 Opakování nadpisů při tisku

1.1.3 Vzorce vracející více hodnot

1.2 Řazení tabulky

1.2.1 Rychlé seřazení tabulky

1.2.2 Řazení podle více sloupců

1.2.3 Aktualizace řazení

1.2.4 Řazení podle seznamu

1.2.5 Řazení podle barev a ikon

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

1.2.7 Řazení pomocí funkce

1.3 Seskupování a tvorba souhrnů

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

1.3.2 Automatický přehled

1.3.3 Tvorba souhrnů

1.3.4 Souhrny ve více úrovních

1.4 Výpočet statistických charakteristik

1.4.1 Funkce LARGE a SMALL

1.4.2 Funkce typu RANK

1.4.3 Medián

1.4.4 Kvartily

1.4.5 Percentily

1.4.6 Výpočet relativní pozice v sadě dat

1.4.7 Funkce ČETNOSTI

1.5 Filtrace

1.5.1 Filtrace podle konkrétních hodnot

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

1.5.3 Zrušení a aktualizace filtru

1.5.4 Automatický filtr

1.5.5 Uložení automatického filtru

1.5.6 Filtrace ve sloupci s číselnými hodnotami

1.5.7 Filtrace ve sloupci s kalendářními hodnotami

1.5.8 Filtrace ve sloupci s textovými hodnotami

1.5.9 Filtrace podle barev a ikon

1.5.10 Filtrace podle více sloupců

1.5.11 Řazení u tabulky s filtrem

1.5.12 Použití funkce FILTER

1.6 Funkce reagující na filtr

1.6.1 Funkce SUBTOTAL

1.6.2 Funkce AGGREGATE

1.7 Databázové funkce

1.7.1 Funkce DSUMA

1.7.2 Další databázové funkce

1.7.3 Funkce DZÍSKAT

1.7.4 Použití více podmínek

1.8 Vyhledávání v tabulce

1.8.1 Hledání podle hodnot – funkce XLOOKUP

1.8.2 Hledání pořadí – funkce XMATCH

1.8.3 Funkce INDEX

1.9 Zpracování časových řad

1.9.1 Vložení listu prognózy

1.9.2 Funkce FORECAST.ETS

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