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 2013

práce s databázemi a kontingenčními tabulkami

TIRÁŽ TIŠTĚNÉ PUBLIKACE:

Vydala Grada Publishing, a.s.

U Průhonu 22, 170 00  Praha 7

tel.: +420 234 264 401, fax: +420 234 264 400

www.grada.cz

jako svou 5434. publikaci

Spoluautor Michal Bureš

Odpovědný redaktor Petr Somogyi

Sazba Petr Somogyi

Počet stran 176

První vydání, Praha 2014

© Grada Publishing, a.s., 2014

Cover Photo © fotobanka allphoto

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

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

ISBN 978-80-247-5003-3

ELEKTRONICKÉ PUBLIKACE:

E-knihu ve formátu EPUB vytvořil Petr Somogyi

ISBN 978-80-247-9074-9 (ve formátu PDF)

ISBN 978-80-247-9075-6 (ve formátu EPUB)

Úvod

Kniha, kterou právě otvíráte, je věnována jedné z nejčastěji využívaných možností tabulkového kalkulátoru MS Excel: práci s rozsáhlými tabulkami (databázemi).

Kromě stručné úvodní kapitoly spočívá těžiště výkladu ve čtyřech tématech.

Prvním je zpracování dlouhých datových tabulek. V 2. kapitole je popsáno řazení a filtrace různými způsoby, tvorba přehledů a souhrnů. Je zde také vysvětleno použití vyhledávacích a databázových funkcí.

Druhé téma je věnováno práci s externími daty. Naleznete zde informace o tvorbě tabulky, propojené z databáze programu MS Access a MS SQL Server. V 3. kapitole je rovněž popsán import dat z textového souboru. V závěru najdete popis spolupráce sešitu Excelu s internetem a práce se soubory typu XML.

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

Poslední, 5. kapitola knihy se věnuje novince v Excelu 2013: datovému modelu a jeho využití, především pro tvorbu kontingenčních tabulek a grafů.

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.

Kniha je orientována na poslední verzi Excel 2013. Nicméně převážnou většinu postupů můžete bez velkých problémů využívat i v předchozích verzích Excelu. U postupů, které jsou použitelné pouze v Excelu 2013, je tato skutečnost uvedena.

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

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

1.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ů či volby v nabídkách jsou zvýrazněny tučným písmem.

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

3.Parametry funkcí poznáte podle tučné kurzivy.

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

Ať se vám práce s databázemi a kontingenčními tabulkami daří!

Autoři

1.Práce s databázemi

V úvodní kapitole se seznámíte s pojmem „databáze“, a to jak obecně, tak s jejím chápáním v tabulkovém kalkulátoru Excel. Kapitola také obsahuje některé postupy, které využijete při práci s velkými tabulkami.

1.1Pojem „databáze“ v Excelu

Při práci s tabulkovým kalkulátorem MS Excel se často setkáváte s pojmem „databáze“. Stává se to například při tvorbě kontingenční tabulky. Označení „databáze“ nese také celá skupina standardních funkcí Excelu.

Základní definice databáze by mohla znít tak, že se jedná o uspořádanou sérii dat, uloženou v elektronické podobě na paměťovém médiu. Excel umožňuje používat některé základní databázové operace – řazení, filtrování, seskupování a tvorbu souhrnů – u dat zapsaných v tabulce (datové oblasti) na některém z listů. Tato data je pak možné považovat za jednoduchou databázi, pokud splňují některé podmínky:

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

■Každý sloupec v tabulce by měl mít samostatný nadpis, nadpisy by měly být umístěny ve společném řádku.

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

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

V praxi se vyskytují také případy, kdy zdrojová data netvoří jednu souvislou tabulku, ale jsou rozdělena do řady dílčích tabulek, z nichž každá je souvislá. Tyto tabulky jsou umístěny na různých listech nebo i v různých sešitech. Některé operace – zejména tvorbu souhrnů pomocí kontingenční tabulky – je možné v Excelu provádět i s daty tohoto typu.

I když novější verze Excelu (počínaje verzí 2007) umožňuje zapsat na list podstatně větší množství dat než u dřívějších verzí, nehodí se pro uchovávání velikého množství informací. Firmy a jiné instituce zpravidla využívají pro úschovu svých dat specializované programy, označované jako databázové systémy. Excel však nabízí řadu výkonných nástrojů pro analýzu dat, jež u velkých databázových systémů většinou chybí. Proto jsou součástí Excelu také nástroje pro komunikaci s běžnými databázovými systémy. To umožňuje získávat přístup k datům uloženým ve vnějším datovém zdroji a využít při jejich zpracování všechny možností a pohodlí, které Excel poskytuje.

Za dobu existence tabulkového kalkulátoru Excel prošly databázové systémy velkým vývojem – a tomu se přizpůsobuje i Excel. V novějších verzích Excelu (počínaje verzí 2007) je přístup k vnějším datům do značné míry sjednocen a příliš nezáleží na tom, z jakého zdroje se data získala. Poslední verze 2013 pokročila v tomto směru ještě dále a umožňuje zacházet s daty způsobem, který je obdobný jako při práci s velkými databázemi.

1.2Práce s rozsáhlou tabulkou

Pro práci s rozsáhlou tabulkou zahrnující mnoho řádků nabízí Excel několik nástrojů, které usnadňují manipulaci s daty v tabulce.

1.2.1Očíslování řádků

Velmi častou akcí při práci s delší tabulkou je její setřídění podle jednoho nebo více sloupců (třídění je popsáno v následující kapitole). Při opakovaném třídění podle různých kritérií je často obtížné identifikovat původní pořadí řádků. Jednou z možností je vytvořit na novém listu kopii původní tabulky, což ale vede ke značnému zvětšení sešitu. Proto je mnohdy lepší řádky v tabulce očíslovat: na začátek tabulky přidejte nový sloupec, v tomto sloupci zapište do prvního řádku tabulky číslo 1 a do druhého řádku číslo 2. Označte obě buňky a naplňte tuto oblast dolů tahem myší za pravý dolní růžek. Ve sloupci tím vytvoříte číselnou řadu 1, 2, 3…

1.2.2Ukotvení řádků a sloupců

U rozsáhlé tabulky je nepříjemné, že při práci s řádky umístěnými níže nejsou vidět nadpisy jednotlivých sloupců. Proto je výhodné řádek s nadpisy ukotvit tak, aby byl stále viditelný. Provedete to těmito kroky:

1.Vložte kurzor do sloupce A na první řádek tabulky, kde jsou umístěna data (pod řádek s nadpisem).

2.Přejděte na kartu Zobrazení.

3.V sekci Lupa na této kartě klepněte na tlačítko Ukotvit příčky a následně použijte příkaz Ukotvit příčky.

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

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

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

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

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

Po ukotvení prvního řádku nebo sloupce A nelze příkaz Ukotvit příčky použít. Proto je nutné nejprve uvolnit příčky a teprve pak použít příkaz Ukotvit příčky. Je ale možné použít ukotvení prvního řádku při ukotveném sloupci A a naopak – ukotvení na listu se změní.

1.2.3Opakování nadpisů při tisku

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

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

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

1.Přejděte na kartu Rozložení stránky.

2.V sekci Vzhled stránky klepněte na tlačítko Tisk názvů.

3.V zobrazeném okně klepněte do položky Nahoře opakovat řádky.

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

5.Stiskněte tlačítko OK.

Při tisku tabulky se označené řádky vytisknou na začátku každé stránky.

Pro tisk široké tabulky s mnoha sloupci, která se vytiskne na několik stránek vedle sebe, můžete využít také položku Vlevo opakovat sloupce.

1.3Velké databázové systémy

U velkých databázových systémů se pod pojmem „databáze“ nemíní jediná souvislá datová tabulka, ale celá soustava tabulek, zpravidla vzájemně propojených pomocí sloupců obsahujících společné údaje (například tabulky zákazníků a prodejů mají společný sloupec, který obsahuje identifikační číslo zákazníka). Snahou je ukládat každou informaci pouze jednou a tím minimalizovat objem ukládaných dat.

Kromě vlastních datových tabulek jsou do databáze zpravidla zahrnuty i jiné objekty, zejména uložené příkazy k výběru určených dat z jedné nebo více tabulek (tzv. dotazy nebo pohledy). Při získávání externích dat pro Excel se tyto výběry dají použít stejně jako původní datové tabulky.

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

■Microsoft Access – databázový systém určený pro menší evidence. Je součástí programového balíku MS Office.

■Microsoft SQL Server – výkonný databázový systém, využívaný zejména ve středních a větších institucích.

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

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

Připojení sešitu Excelu k některému z databázových systémů se děje většinou prostřednictvím přístupu ODBC (Open Database Connectivity). Před vlastním propojením sešitu s daty je proto zpravidla zapotřebí vytvořit v počítači příslušný ovladač (driver), který zajišťuje komunikaci s připojenou databází. Postup při tvorbě tohoto spojení je popsán v podkapitole 3.2, jež se věnuje importu dat z SQL Serveru. Výjimkou je databáze MS Access, která je s Excelem nejvíce kompatibilní a k níž se snadno lze připojit přímo.

Vytvořené připojení se stává součástí uloženého sešitu a umožňuje snadnou aktualizaci připojených dat. Neumožňuje však zpětný zápis údajů do připojené databáze.

2.Použití tabulky Excelu jako databáze

Se souvislou tabulkou, která je tabulkovým kalkulátorem Excel chápána jako databáze, je možné provádět základní databázové operace: řazení afiltrování záznamů, rychlé vyhledávání údajů ztabulky avytváření částečných součtů adalších přehledů. Excel má také zvláštní skupinu funkcí, určených pro práci sdatabázovou tabulkou. Vtéto kapitole se stěmito možnostmi seznámíte.

2.1Řazení vtabulce

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

■Použít tlačítka pro seřazení tabulky podle hodnot vjednom sloupci.

■Použít dialogové okno, které umožňuje využít všechny možnosti řazení.

Obrázek 2.1: Karta Data

Při rychlém řazení tabulky umístěte kurzor do potřebného sloupce apoužijte tlačítka se šipkou apísmeny AZ (vzestupné řazení) nebo šipkou apísmeny ZA (sestupné řazení), která jsou umístěna vsekci Seřadit afiltrovat na kartě Data. Vzestupné řazení znamená utextů seřazení od Ado Z, učíselných hodnot od nejmenší knejvětší, uhodnot kalendářního data od nejstaršího knejnovějšímu aulogických hodnot vpořadí NEPRAVDA, PRAVDA. Usestupného řazení je tomu naopak.

Pro rychlé seřazení podle jednoho sloupce můžete využít také příkazy vtlačítku Seřadit afiltrovat, které je umístěno na kartě Domů vsekci Úpravy.

Jestliže tabulka obsahuje pouze texty, použití tlačítek pro rychlé řazení může 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.

2.1.1Řazení podle hodnot

Dialogová okna pro řazení můžete využít pro seřazení tabulky podle jednoho nebo více sloupců. Řazení podle jednoho sloupce nastavíte tímto postupem:

1.Označte libovolnou buňku vtabulce apoužijte tlačítko Seřadit, umístěné vsekci Seřadit afiltrovat na kartě Data. Druhou možností je použít tlačítko Seřadit afiltrovat, které je umístěno na kartě Domů vsekci Úpravy, azadat příkaz Vlastní řazení.

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

2.Volbou Data obsahují záhlaví určíte, jestli je první řádek vtabulce považován za nadpisy sloupců. Pokud je volba označena, sloupce pro řazení určíte podle jejich nadpisů. Při neoznačené volbě je první řádek vtabulce považován za řádek sdaty asloupec určíte podle jeho adresy (sloupec „B“, „C“ atd.).

Obrázek 2.3: Dialogové okno pro řazení tabulky

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

4.Vseznamu Pořadí určíte, zda se bude řadit vzestupně nebo sestupně. Utextových hodnot jsou vseznamu možnosti Aaž Znebo Zaž A, učíselných alogických hodnot Od nejmenšího knejvětšímu nebo Od největšího knejmenšímu auhodnot kalendářních dat Od nejstaršího knejnovějšímu nebo Od nejnovějšího knejstaršímu.

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

5.Řazení potvrďte klepnutím na tlačítko OK.

Výchozí volba ulogických hodnot je Od nejmenšího knejvětšímu, která odpovídá pořadí NEPRAVDA, PRAVDA. Při řazení tabulky podle textových hodnot se nerozlišují malá avelká písmena. Jestliže potřebujete velká amalá písmena rozlišit, použijte tlačítko Možnosti, vdalším okně označte volbu Rozlišovat malá avelká apotvrďte tlačítkem OK.

Při použití této volby jsou texty svelkými písmeny umístěny za malými, platí tedy „kopírka < Kopírka < KOpírka < KOPÍRKA“.

Jestliže je na listu umístěna pouze jedna tabulka, Excel si zapamatuje označenou volbu  Rozlišovat malá avelká apoužije ji při příštím řazení. To platí ipro rychlé řazení tabulky pomocí tlačítek.

Při řazení do více úrovní použijte tlačítko Přidat úroveň, kterým se do dialogového okna přidá další úroveň řazení. Tímto způsobem můžete seřadit tabulku do několika úrovní. Seřazení tabulky probíhá shora dolů, tedy úroveň na prvním řádku je považována za nejvyšší, úroveň na druhém řádku opět seřadí záznamy, pro něž je hodnota ve sloupci zadaném vprvní úrovni stejná atd. Nová úroveň řazení se přidá pod označenou úroveň. Tlačítky se šipkami nahoru adolů je možné změnit pořadí úrovní pro řazení, tlačítkem Odstranit úroveň se označená úroveň zřazení vymaže. Při přepisu údajů, podle nichž byla tabulka seřazena, se může stát, že pořadí řádků již neodpovídá nastavenému řazení. Vtakovém případě umístěte kurzor do tabulky, použijte tlačítko Seřadit nebo Seřadit afiltrovat anastavené řazení znovu potvrďte tlačítkem OK. Toto platí pro seřazení tabulky podle hodnot ivšemi dalšími způsoby, které jsou popsány dále.

2.1.2Řazení podle textového seznamu

Součástí Excelu je seznam měsíců vroce adní vtýdnu. Jestliže do buňky zapíšete např. slovo „Leden“ anaplníte oblast tažením za pravý dolní růžek buňky, vytvoří se texty „Únor“, „Březen“ atd. (uvedený efekt lze potlačit, jestliže při plnění podržíte současně klávesu CTRL). Tyto seznamy můžete využívat ikseřazení tabulky. Seřazení podle seznamu provedete takto:

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

2.Vseznamu Seřadit podle vyberte název sloupce.

3.Vseznamu Pořadí vyberte volbu Vlastní seznam.

4.Vdalším dialogovém okně vlevo označte potřebný seznam aklepněte na tlačítko OK.

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

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

Pro toto řazení tabulky můžete využít pouze sloupce se zapsanými texty. Není tedy možné například naformátovat sloupec shodnotami kalendářního data tak, aby se zobrazoval pouze název dne vtýdnu (formátovací kód „dddd“), apomocí tohoto sloupce seřadit tabulku od pondělí do neděle.

Řazení podle vlastního seznamu můžete kombinovat sřazením podle jiných sloupců. Nezáleží přitom na pořadí úrovní.

Obrázek 2.6: Řazení tabulky svyužitím seznamu

Jestliže nastavíte řazení podle seznamu aznovu použijete seznam  Pořadí, vnabízených volbách se zobrazí také text seznamu vobráceném pořadí. Tímto způsobem můžete řadit texty od prosince do ledna nebo od neděle do pondělí.

Měsíce jsou vseznamu zapsány malými písmeny, dny vtýdnu svelkým počátečním písmenem. Pokud jsou vtabulce texty zapsané jinak, při tvorbě řazení zkontrolujte, zda není aktivní volba  Rozlišovat malá avelká.

Tvorba vlastních seznamů

Kromě seznamu měsíců adnů, které jsou součástí instalace Excelu, si můžete vytvářet iseznamy vlastní. Uživatelsky vytvořený seznam je možné použít kseřazení tabulky ataké kautomatickému plnění oblasti předem zvolenými texty.

Vytvoření vlastního seznamu můžete provést tímto způsobem:

1.Do buněk pod sebou zapište texty budoucího seznamu atuto oblast buněk označte.

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

3.Vzobrazeném dialogovém okně klepněte vlevo na sekci Upřesnit.

4.Obsah pravé části okna srolujte úplně dolů aklepněte na tlačítko Upravit vlastní seznamy.

5.Odkaz na označenou oblast buněk se zobrazí vpoložce Importovat seznam zbuněk.

Obrázek 2.7: Nastavení Excelu

6.Pro načtení textů ztéto oblasti stiskněte tlačítko Importovat. Texty nového seznamu se přenesou do pravé části okna.

7.Vytvoření nového seznamu potvrďte tlačítkem OK.

Obrázek 2.8: Tvorba vlastního seznamu

Takto vytvořený vlastní seznam je součástí instalace Excelu aje možné ho využít vlibovolném sešitu.

Obrázek 2.9: Odstranění vlastního seznamu

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

Úpravy nebo odstranění je možné provádět pouze unově vytvořených seznamů. Standardní seznamy měsíců adnů vtýdnu nelze ani upravit, ani vymazat.

2.1.3Řazení podle barev aikon

Další možností, jak seřadit řádky vtabulce, je využít různé barvy písma nebo barevné pozadí buněk. Tyto odlišné barvy mohou být vytvořeny běžným formátováním nebo vzniknout vdůsledku použití podmíněného formátování buněk. Kseřazení tabulky můžete využít také barevné ikony, vzniklé rovněž podmíněným formátováním.

Pro seřazení tabulky podle barevného pozadí buněk použijte následující postup:

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

1.Označte buňku vtabulce.

2.Zobrazte dialogové okno pro řazení.

3.Vseznamu Seřadit podle vyberte název sloupce, ve kterém se vyskytují buňky snastaveným barevným pozadím.

4.Vseznamu Řazení vyberte volbu Barva buňky.

5.Klepněte na šipku useznamu Pořadí. Objeví se nabídka spoužitými barvami avolbou Žádná barva buňky. Označte potřebnou barvu.

6.Vposledním seznamu se automaticky nastaví volba Nahoře. To znamená, že buňky se zvolenou barvou budou vtabulce řazeny jako první. Jestliže vyberete volbu Dole, budou řazeny jako poslední.

7.Jestliže je ve sloupci použito více barev, označte řazení podle vytvořené barvy aklepněte na tlačítko Kopírovat úroveň. Tím se do dialogu vloží další řádka pro řazení podle barvy, kde stačí pouze označit jinou barvu buňky.

Pokud je ve zvoleném sloupci použito více barev, volba Nahoře znamená seřazení shora dolů – buňky sbarvou vybranou při řazení jako první se umístí na začátku tabulky. Při použití volby Dole se řadí zdola nahoru abarva, vybraná vprvní úrovni řazení, se při seřazení tabulky naopak použije jako poslední.

Řazení podle barvy písma je obdobné, pouze vseznamu Řazení vyberete volbu Barva písma. Vseznamu Pořadí se zobrazí použité barvy písma avolba Automatická, což znamená výchozí černé písmo.

Obrázek 2.11: Řazení podle ikon

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

Seřazení tabulky podle ikon provedete stejným postupem jako ubarvy písma nebo pozadí buňky. Vseznamu Seřadit podle vyberte nadpis sloupce, vseznamu Řazení volbu Ikona buňky, vseznamu Pořadí zvolte první zpoužitých ikon avposledním seznamu pomocí voleb Nahoře aDole nastavte, zda mají být buňky se zvolenou ikonou umístěny vtabulce jako první nebo jako poslední. Dále použijte tlačítko Kopírovat úroveň avyberte další ikonu. Tímto způsobem vytvořte úrovně řazení pro všechny použité ikony až na jednu, která se zařadí jako poslední (při volbě Nahoře) nebo jako první (při volbě Dole).

Řazení podle ikon je opět možné kombinovat sjinými způsoby řazení.

2.1.4Řazení ve vodorovném směru avčásti tabulky

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

1.Umístěte kurzor do tabulky.

2.Zobrazte dialogové okno pro řazení.

3.Klepněte na tlačítko Možnosti.

4.Vdalším zobrazeném okně označte volbu Seřadit zleva doprava apotvrďte klepnutím na tlačítko OK.

5.Vseznamu Seřadit podle vyberte číslo řádku, podle kterého se mají sloupce vtabulce seřadit.

6.Vseznamu Pořadí zvolte vzestupné nebo sestupné řazení.

7.Potvrďte tlačítkem OK.

Při řazení ve vodorovném směru je možné vybrat pouze řádek určitého čísla avolba Data obsahují záhlaví je nepřístupná. První sloupec tabulky se tedy do řazení vždy zahrne.

Ve vodorovném směru je možné řadit do více úrovní. Křazení lze využívat itextové seznamy, barvy pozadí buňky, barvy písma aikony, vytvořené podmíněným formátováním.

Obrázek 2.12: Řazení tabulky podle řádků

Všechny dosavadní způsoby řazení tabulky fungují tak, že Excel vsouvislé tabulce určí její rozsah ařazení se vztahuje kcelé tabulce. Okno pro seřazení tabulky však můžete zobrazit itehdy, jestliže je na listu označena oblast buněk, zahrnující pouze část řádků (popřípadě část sloupců) zcelé tabulky. Při seřazení vyznačené oblasti můžete využít všechny možnosti, které dialogové okno pro řazení poskytuje.

Obrázek 2.13: Oblast označená pro řazení musí být souvislá

Oblast buněk, jež se má seřadit, musí být souvislá. Při označení nesouvislé oblasti buněk se dialogové okno pro řazení nespustí amísto něj se zobrazí chybová zpráva.

Tímto způsobem můžete například rozdělit delší tabulku na několik částí akaždou část seřadit odděleně. Je však třeba vždy pečlivě zkontrolovat volbu  Data obsahují záhlaví, aby se nastavené seřazení provedlo správně.

2.2Automatický filtr

Jednou znejdůležitějších databázových operací je filtrace – výběr záznamů podle určeného kritéria. Excel nabízí dvě možnosti filtrace řádků vtabulce: automatický filtr arozšířený filtr. Oba způsoby se liší postupem asvými možnostmi. Vtéto kapitole popíšeme automatický filtr, který se zpravidla používá častěji. Orozšířeném filtru pak pojednává následující podkapitola.

Pro vytvoření automatického filtru vtabulce označte některou buňku vtabulce apoužijte tlačítko Filtr, umístěné vsekci Seřadit afiltrovat na kartě Data. Druhou možností je klepnout na tlačítko Seřadit afiltrovat, které naleznete na kartě Domů zcela vpravo vsekci Úpravy, avtlačítku použít příkaz Filtr.

Po vytvoření automatického filtru se tlačítko Data zvýrazní azároveň se unadpisů jednotlivých sloupců vytvoří tlačítka se šipkou. Tato tlačítka použijete pro nastavení filtru uzvoleného sloupce. Opětovným klepnutím na tlačítko Data se šipky odstraní; pokud je vtabulce již nějaký filtr použit, vymaže se avtabulce se zobrazí všechny řádky.

2.2.1Filtrace podle konkrétních hodnot

Nejjednodušším způsobem filtrace je výběr jedné nebo více konkrétních hodnot. Usloupce, obsahujícího texty, čísla nebo logické hodnoty PRAVDA aNEPRAVDA, postupujte takto:

1.Klepněte na šipku usloupce, podle něhož chcete filtrovat. Vzobrazené nabídce je seznam všech hodnot, které se vsloupci vyskytují. Hodnoty jsou seřazeny akaždá hodnota se vseznamu vyskytuje pouze jednou.

2.Zrušte označení (Vybrat vše) aoznačte hodnoty, které se mají ztabulky vybrat, nebo zrušte označení uhodnot, jež se mají ze zobrazení vyřadit.

3.Klepněte na tlačítko OK.

Obrázek 2.14: Nabídka pro filtraci utextových hodnot

Obrázek 2.15: Nabídka pro filtraci uhodnot kalendářního data

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

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

Usloupce obsahujícího hodnoty kalendářního data se nabídka rozčlení po rocích, měsících adnech. Klepnutím na čtvereček se symbolem plus zobrazíte nabídku oúroveň nižší, klepnutím na čtvereček se symbolem minus naopak nižší úroveň skryjete. Toto členění nabídky umožňuje vybrat jedno nebo více určitých kalendářních dat, popřípadě jedno nebo více zvolených období, tedy měsíců nebo roků.

Nastavený filtr se ukládá společně se sešitem.

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

■Klepněte na šipku unadpisu sloupce sfiltrem apoužijte příkaz Vymazat filtr z+ nadpis sloupce.

■Na kartě Data, vsekci Seřadit afiltrovat, klepněte na tlačítko Vymazat.

■Na kartě Domů klepněte na tlačítko Seřadit afiltrovat umístěné vsekci Úpravy, použijte příkaz Vymazat.

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

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

Vyřazením volby (Vybrat vše) aoznačením volby Prázdné zobrazíte pouze řádky snevyplněnými buňkami ve zvoleném sloupci, vyřazením volby Prázdné naopak zobrazíte řádky, ve kterých jsou buňky ve zvoleném sloupci neprázdné.

Obnovení nastaveného filtru

2.2.2Práce sfiltrovanou tabulkou

Kopírování

Řazení

Obrázek 2.18: Řazení podle barev

Tvorba součtu adalších souhrnů

Typ souhrnu

1

2

3

4

2.2.3Filtrace podle číselných hodnot

Nadprůměrné apodprůměrné hodnoty

Největší anejmenší hodnoty

2.2.4Filtrace podle hodnot kalendářního data

2.2.5Filtrace podle textů

Výběr podle abecedy

Filtrace textů pomocí masek

2.2.6Filtrace podle barev aikon

2.2.7Filtrace podle více sloupců

2.3Rozšířený filtr

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

2.3.2Filtrace pomocí dvou avíce podmínek

2.3.3Filtrace podle vzorce

2.3.4Další možnosti rozšířeného filtru

2.4Seskupování atvorba souhrnů

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

Rozdělování aslučování seskupovaných částí

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

2.4.2Automatický přehled

2.4.3Tvorba souhrnů

Úprava vytvořeného souhrnu

Víceúrovňové souhrny

2.5Rychlá analýza dat aautomatické doplňování

2.5.1Rychlá analýza dat

Podmíněné formátování

Celkové součty

2.5.2Dynamické doplňování

2.6Vyhledávací funkce

2.6.1Funkce SVYHLEDAT aVVYHLEDAT

2.6.2Funkce VYHLEDAT

Vektorová varianta funkce VYHLEDAT

Maticová varianta funkce VYHLEDAT

2.6.3Funkce INDEX

Maticová forma funkce INDEX

Forma odkazu ufunkce INDEX

2.6.4Funkce POZVYHLEDAT

2.7Databázové funkce

2.7.1Funkce DSUMA

2.7.2Tvorba podmínky pro výpočet

2.7.3Ostatní databázové funkce

Funkce DZÍSKAT

2.8Slučování nesouvislých oblastí

Slučování oblastí spropojením

2.9Formátované tabulky

2.9.1Vytvoření formátované tabulky

2.9.2Základní operace sformátovanou tabulkou

Formátování tabulky

Úprava vzhledu formátované tabulky

2.9.3Výpočty ve formátované tabulce

Tvorba souhrnů pod daty

2.9.4Filtrace ve formátované tabulce

Filtrace pomocí průřezů (Excel 2013)

2.9.5Exportování dat zformátované tabulky

Table of Contents

Úvod

1. Práce s databázemi

1.1 Pojem „databáze“ v Excelu

1.2 Práce s rozsáhlou tabulkou

1.2.1 Očíslování řádků

1.2.2 Ukotvení řádků a sloupců

1.2.3 Opakování nadpisů při tisku

1.3 Velké databázové systémy

2. Použití tabulky Excelu jako databáze

2.1 Řazení v tabulce

2.1.1 Řazení podle hodnot

2.1.2 Řazení podle textového seznamu

Tvorba vlastních seznamů

2.1.3 Řazení podle barev a ikon

2.1.4 Řazení ve vodorovném směru a v části tabulky

2.2 Automatický filtr

2.2.1 Filtrace podle konkrétních hodnot

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

Obnovení nastaveného filtru

2.2.2 Práce s filtrovanou tabulkou

Kopírování

Řazení

Tvorba součtu a dalších souhrnů

2.2.3 Filtrace podle číselných hodnot

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

Největší a nejmenší hodnoty

2.2.4 Filtrace podle hodnot kalendářního data

2.2.5 Filtrace podle textů

Výběr podle abecedy

Filtrace textů pomocí masek

2.2.6 Filtrace podle barev a ikon

2.2.7 Filtrace podle více sloupců

2.3 Rozšířený filtr

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

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

2.3.3 Filtrace podle vzorce

2.3.4 Další možnosti rozšířeného filtru

2.4 Seskupování a tvorba souhrnů

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

Rozdělování a slučování seskupovaných částí

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

2.4.2 Automatický přehled

2.4.3 Tvorba souhrnů

Úprava vytvořeného souhrnu

Víceúrovňové souhrny

2.5 Rychlá analýza dat a automatické doplňování

2.5.1 Rychlá analýza dat

Podmíněné formátování

Celkové součty

2.5.2 Dynamické doplňování

2.6 Vyhledávací funkce

2.6.1 Funkce SVYHLEDAT a VVYHLEDAT

2.6.2 Funkce VYHLEDAT

Vektorová varianta funkce VYHLEDAT

Maticová varianta funkce VYHLEDAT

2.6.3 Funkce INDEX

Maticová forma funkce INDEX

Forma odkazu u funkce INDEX

2.6.4 Funkce POZVYHLEDAT

2.7 Databázové funkce

2.7.1 Funkce DSUMA

2.7.2 Tvorba podmínky pro výpočet

2.7.3 Ostatní databázové funkce

Funkce DZÍSKAT

2.8 Slučování nesouvislých oblastí

Slučování oblastí s propojením

2.9 Formátované tabulky

2.9.1 Vytvoření formátované tabulky

2.9.2 Základní operace s formátovanou tabulkou

Formátování tabulky

Úprava vzhledu formátované tabulky

2.9.3 Výpočty ve formátované tabulce

Tvorba souhrnů pod daty

2.9.4 Filtrace ve formátované tabulce

Filtrace pomocí průřezů (Excel 2013)

2.9.5 Exportování dat z formátované tabulky