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 – pokročilé nástroje

funkce, databáze, kontingenční tabulky, prezentace, příklady

Vydala Grada Publishing, a.s.

U Průhonu 22, Praha 7

obchod@grada.cz, www.grada.cz

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

jako svou 6039. publikaci

Odpovědný redaktor Petr Somogyi

Sazba Jan Šístek

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

Počet stran 224

První vydání, Praha 2016

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

© Grada Publishing, a.s., 2016

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

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

ISBN 978-80-247-5942-5 (pdf)

ISBN 978-80-247-5570-0 (print)

Úvod

Kniha, kterou právě otvíráte, je určena uživatelům Excelu, kteří již nějaké zkušenosti s tímto programem mají a chtěli by své znalosti rozšířit. Kniha je přednostně orientována na Excel 2013. Mohou ji však využívat i uživatelé starších verzí programu. Pokud se u některých postupů v dřívějších verzích Excelu objevují odchylky nebo omezení, bude to v knize vždy uvedeno.

První kapitoly jsou věnovány nastavení programu, práci s buňkami, formátování a tvorbě vzorců. Podrobně je rozebrána práce se standardními funkcemi Excelu. Funkcím, určeným pro finanční výpočty, a funkcím statistickým jsou věnovány samostatné kapitoly, zaměřené na finanční a statistické výpočty. Další kapitoly jsou pak zaměřeny na ochranu dat, sdílení sešitů mezi uživateli a na export a import dat.

Další prostor je věnován databázovému pojetí Excelu, kontingenčním tabulkám a grafům. Vzhledem k omezenému rozsahu knihy není možné popsat toto rozsáhlé téma v celé jeho šíři. Pro podrobnější seznámení s touto problematikou proto odkazujeme na dřívější knihu obou autorů: Excel 2013 – Práce s databázemi a kontingenčními tabulkami, kterou vydalo nakladatelství Grada v roce 2014.

Závěrečné kapitoly se věnují práci s formulářovými prvky na listu, analýze dat a jejich vizualizaci, včetně využití podmíněného formátování a minigrafů. Součástí knihy je i popis práce se třemi užitečnými doplňky: doplňkem Analytické nástroje, doplňkem Řešitel a doplňkem PowerView.

Najdete zde i řadu příkladů k procvičení. Sešity s příklady si můžete stáhnout na webových stránkách nakladatelství Grada (www.grada.cz). Všechny stahované sešity najdete ve dvou verzích: v první pracovní verzi jsou pouze zadání příkladů, ve druhé kontrolní verzi jsou příklady již vyřešeny.

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

›Názvy ovládacích karet, příkazy a názvy tlačítek, sekcí v dialogových oknech a volby v ovládacích seznamech jsou zvýrazněny tučným písmem.

›Názvy souborů a internetových stránek jsou zvýrazněny kurzívou.

›Označení kláves je zvýrazněno Kapitálkami.

Ať se vám při práci s Excelem daří!

Autoři

1Nastavení programu

1.1Možnosti práce programu

Program Excel umožňuje nastavit různé způsoby, jak s ním pracovat. Pro nastavení přejdete na kartu Soubor a vlevo dole klepnete na příkaz Možnosti. Tím se zobrazí okno Možnosti aplikace Excel, v němž můžete provést obecná nastavení pro práci s Excelem. V levé části okna klepnete na zvolenou skupinu, v pravé části provedete potřebná nastavení a potvrdíte tlačítkem OK.

Nastavení programu je trvalé a neváže se jen na otevřený sešit. Okno Možnosti aplikace Excel obsahuje celou řadu nastavení, jež zpravidla ponecháte beze změny. V této kapitole jsou uvedena pouze taková, která bude zapotřebí v některých případech změnit.

Skupina Obecné

V této skupině můžete vyplnit uživatelské jméno (standardně je použito přihlašovací jméno do systému Windows). Dále můžete nastavit výchozí velikost písma a počet listů v nově vytvořeném sešitu – u Excelu 2013 a 2016 je tato hodnota nastavena na jedničku a nový sešit obsahuje pouze jeden list.

Obrázek 1.1: Nastavení Excelu ve skupině Obecné

Skupina Vzorce

Z této skupiny se v praxi používají především dvě nastavení:

1.V přepínači Přepočet sešitu je možné použít volbu Ručně. Při této volbě se vzorce neaktualizují automaticky, ale je nutné vynutit si aktualizaci klávesou F9. Toto nastavení se hodí u rozsáhlých tabulek se složitými vzorci. Při automatické aktualizaci se totiž po každém přepisu některé buňky aktualizuje vždy celý sešit, což může u velkých sešitů vést k nepříjemnému zdržování při práci.

2.Označení volby Styl odkazu R1C1 způsobí, že se v nadpisech sloupců na listu zobrazí místo písmen pořadová čísla sloupců. Toto nastavení je platné pro celý sešit a použijete jej k bezpečnému zjištění pořadového čísla některého sloupce.

Obrázek 1.2: Nastavení Excelu ve skupině Vzorce

Skupina Kontrola pravopisu a mluvnice

V programu Excel funguje kontrola pravopisu obdobně jako ve Wordu (ve skutečnosti je tato kontrola společným nástrojem balíku programů MS Office a funguje ve všech programech tohoto balíku). V této skupině můžete nastavit způsob kontroly pravopisu a tlačítkem Možnosti automatických oprav upravit automatický přepis některých slov. Je však třeba mít na paměti, že změny provedené v této skupině se projeví u všech programů balíku MS Office.

Skupina Uložit

Ve skupině Uložit je nastaveno automatické ukládání otevřeného sešitu po 10 minutách, což můžete v případě potřeby upravit. Dále zde můžete změnit výchozí umístění sešitů pro ukládání a otvírání (standardně je nastavena složka Dokumenty). Nové umístění je nutné zadat z klávesnice.

Skupina Upřesnit

Tato skupina obsahuje řadu možností, rozdělených do sekcí. Existuje několik nastavení, která by mohla mít význam i při praktické práci.

V seznamu Po stisknutí klávesy Enter přesouvat výběr (sekce Možnosti úprav) způsobí výchozí volba Dolů přechod na nový řádek ve stejném sloupci. Volba Doprava zajistí, že se kurzor po ukončení zápisu v buňce přesune ve stejném řádku o buňku doprava.

Označená volba Automaticky vložit desetinnou čárku (sekce Možnosti úprav) způsobí, že se velikost zapsaného čísla řádově zmenší o hodnotu nastavenou v číselníku Počet. Výchozí hodnota v tomto číselníku je 2, což znamená, že se zapsaná čísla zmenší stokrát. Po vypnutí této volby se čísla budou nadále zapisovat normálně, ale zapsaná čísla se již nezmění.

Zrušením volby Používat oddělovače ze systému (sekce Možnosti úprav) je možné nastavit v desetinných číslech tečku místo čárky. Nastavení použijete například při exportu listu do textového souboru, kde mají být čísla s desetinnými tečkami.

Číselník Zobrazovat tento počet posledních sešitů (sekce Zobrazení) určuje počet naposledy otevřených sešitů, které se zobrazí na kartě Soubor po zadání příkazu Otevřít.

V přepínači U buněk s komentáři zobrazovat (sekce Zobrazení) je možné nastavit způsob zobrazení vytvořených komentářů (podrobněji viz podkapitolu 2.5).

Obrázek 1.3: Nastavení Excelu ve skupině Upřesnit

V sekci Možnosti pro tento list je možné provést některá nastavení pro jednotlivé listy, které vyberete v seznamu. U každého listu můžete provést nastavení jinak. V sekci lze použít tyto volby:

›Zobrazit záhlaví řádků a sloupců: zobrazení čísel řádků a písmen nad sloupci.

›Zobrazit v buňkách vzorce namísto vypočtených výsledků: v buňkách se vzorci se zobrazí text vzorce, který se normálně zobrazuje jen v řádku vzorců.

›Zobrazit konce stránek: na listu jsou zobrazena zalomení stránek při tisku směrem dolů a doprava.

›Zobrazit v buňkách nulovou hodnotu: při deaktivaci této volby nejsou v buňce zobrazeny nulové hodnoty, ať jsou zapsané jako pevná čísla, nebo vypočítané vzorcem.

›Zobrazit symboly přehledu v případě jeho využití: jestliže jsou na listu některé řádky, popřípadě sloupce seskupeny (například po vytvoření přehledu), vlevo od čísel řádků, popřípadě nahoře nad označením sloupců se zobrazí tlačítka pro seskupení, která umožňují seskupené řádky nebo sloupce zobrazit či skrýt. Při zrušení volby se tato tlačítka nezobrazují a skrytí seskupených řádků nebo sloupců není možné.

›Zobrazit mřížku: dojde k zobrazení hranic mezi buňkami.

Tlačítko Upravit vlastní seznamy (sekce Obecné) zobrazí dialogové okno pro práci s vlastními seznamy textů (viz podkapitolu 2.4).

Skupina Doplňky

V této skupině je možné aktivovat některé nástroje Excelu, které nejsou běžně dostupné, například doplňky Analytické nástroje nebo Řešitel. Klepněte na tlačítko Přejít, v dalším okně označte potřebný doplněk a potvrďte tlačítkem OK. Aktivované doplňky jsou zařazeny v seznamu Aktivní doplňky aplikací.

Obrázek 1.4: Výběr doplňků

1.2Úprava panelu Rychlý přístup

Panel Rychlý přístup je umístěn zpravidla nad záložkami karet a po instalaci kompletu MS Office obsahuje tři tlačítka: Ukládání (disketa), akce Zpět (šipka doleva) a akce Znovu (šipka doprava). Panel si můžete upravit podle svých potřeb:

1.Přejděte na kartu Soubor a vlevo dole klepněte na příkaz Možnosti.

2.V levé části zobrazeného okna klepněte na skupinu Panel nástrojů Rychlý přístup.

3.V levém velkém seznamu označte příkaz a klepněte na tlačítko Přidat. Tím se do panelu Rychlý přístup přidá nové tlačítko.

4.Tlačítkem Odebrat můžete tlačítko z panelu Rychlý přístup zase odstranit.

5.Tlačítky se šipkami, umístěnými u pravého okraje okna, je v panelu Rychlý přístup možné změnit pořadí tlačítek.

Úprava panelu Rychlý přístup je trvalé nastavení a projeví se ve všech otevřených sešitech.

Při práci s panelem Rychlý přístup je v levém rozvíracím seznamu jako výchozí nastavena volba Oblíbené příkazy. V levém velkém seznamu se tak zobrazí nejpoužívanější příkazy Excelu. Můžete také použít volbu Všechny příkazy nebo volbu Příkazy mimo pás karet, jež zobrazí právě ty příkazy, které na kartách umístěny nejsou.

Pro uvedení panelu Rychlý přístup do původního stavu klepněte na tlačítko Obnovit a použijte volbu Obnovit jenom panel nástrojů Rychlý přístup. Tato akce je jištěna kontrolním dotazem.

Obrázek 1.5: Nastavení panelu Rychlý přístup

Úprava pásu karet

Pás ovládacích karet můžete upravit dvěma způsoby:

›Do některé ze standardních karet můžete přidat novou skupinu a do této skupiny zařadit potřebná tlačítka.

›Lze také vytvořit nový pás karet s jednou nebo více skupinami.

Přidání nové skupiny do některé z karet provedete takto:

1.Přejděte na kartu Soubor a vlevo dole klepněte na příkaz Možnosti.

2.V levé části zobrazeného okna klepněte na skupinu Přizpůsobit pás karet.

3.V pravém velkém seznamu označte název karty a klepněte na tlačítko Nová skupina. Na konec zvolené karty se přidá nová skupina a zároveň se v seznamu označí. Polohu nové skupiny můžete upravit pomocí tlačítek se šipkami, umístěnými u pravého okraje okna.

4.Klepněte na tlačítko Přejmenovat a vyplňte název nové skupiny.

5.Do nové skupiny přidejte jednotlivé příkazy. Provedete to stejným postupem jako u panelu Rychlý přístup: v levém velkém seznamu označíte příkaz a tlačítkem Přidat jej zařadíte do skupiny.

6.Úpravu dokončete klepnutím na tlačítko OK.

Obrázek 1.6: Vytvoření nové skupiny karet

Obrázek 1.7: Přejmenování nové skupiny karet

Nově vytvořenou skupinu můžete dodatečně upravit, přejmenovat, přesunout nebo odstranit. Se standardními skupinami žádné úpravy provádět nelze, není do nich ani možné přidávat nové příkazy.

Vytvoření vlastní karty provedete takto:

1.Zobrazte okno pro přizpůsobení pásu karet.

2.Klepněte na tlačítko Nová karta. Tím se v pravém seznamu vytvoří nová karta a v ní jedna nová skupina.

3.V pravém seznamu označte řádek Nová karta. Pomocí tlačítek se šipkami upravte polohu nové karty.

4.Klepněte na tlačítko Přejmenovat a vyplňte název nové karty.

5.V nové kartě označte první skupinu, pomocí tlačítka Přejmenovat pro ni zadejte vhodný název a zařaďte do ni příkazy.

6.Další skupinu přidáte tlačítkem Nová skupina.

7.Potvrďte tlačítkem OK.

I nově vytvořenou kartu je možné dodatečně upravovat, popřípadě z pásu karet odstranit. Standardní karty odstranit nelze, je však možné je přejmenovat a upravit jejich polohu.

1.3Kompatibilita mezi verzemi

Sešity vytvořené verzemi Excelu 2007, 2010, 2013 a 2016 mají stejný formát. Mezi jednotlivými verzemi však není úplná kompatibilita, proto některé nástroje použité ve vyšší verzi není možné využít ve verzi nižší. Například verze 2007 nemůže používat minigrafy, verze 2007 a 2010 nemají možnost vytvářet relace mezi tabulkami a popisky datových řad u grafů nelze převzít z buněk mimo oblast grafu.

Počínaje Excelem 2003 a níže se liší i formát uložených souborů. V Excelu 2003 tedy není možné běžně otevřít sešit s příponou DOCX. Opačně to samozřejmě možné je, ale v sešitu staršího formátu nejsou přístupné některé nástroje, které starší formát sešitů neumožňuje.

Sešit v novějším formátu je možné uložit ve starším formátu pomocí příkazu Uložit jako, který naleznete na kartě SOUBOR. Při ukládání použijete seznam Uložit jako typ a nastavíte v něm volbu Sešit Excelu 97-2003. Při ukládání se provede kontrola kompatibility a často se zobrazí okno s varováním, že se při uložení některé funkce sešitu ztratí. V mnoha případech je toto varování prakticky bezvýznamné. Pro uložení klepněte na tlačítko Pokračovat.

V Excelu 2003 je možné otevřít i sešit v novém formátu, je však třeba doplnit instalaci kompletu MS Office o doplněk, který toto otevření umožní. Instalaci doplňku provede program FileFormatConverters.exe, který lze najít na internetu a zdarma stáhnout. Instalace tohoto doplňku umožní otvírat i novější dokumenty Wordu a prezentace PowerPointu.

2Základní akce s tabulkami

2.1Práce s řádky, sloupci a listy

Vkládání a odstraňování buněk

Pro vložení buněk použijete pravé tlačítko myši a příkaz Vložit buňky, nebo klávesovou zkratku CTRL++ (znamená to, že musíte současně stisknout klávesu CTRL a +). Pro odstranění buněk stiskněte pravé tlačítko myši a z místní nabídky zadejte příkaz Odstranit, nebo použijte klávesovou kombinaci CTRL+– (klávesy CTRL a –).

Jestliže předem označíte celý řádek nebo sloupec, odstraní se celý a vložení se provede nad označeným řádkem nebo napravo od označeného sloupce. Při označení jen jedné nebo více buněk se zobrazí okno, v němž rozhodnete, kam se má provést posun (odstranění vlevo nebo nahoru, vložení vpravo nebo dolů). V okně se také nabízí akce s celým sloupcem nebo řádkem.

Obrázek 2.1: Vložení buněk

Odstranění nebo vložení buněk se dá provést i s nesouvislou oblastí nebo s několika označenými řádky nebo sloupci. Označit několik řádků nebo sloupců za sebou můžete provést třemi způsoby:

›Táhnout myší se stisknutým levým tlačítkem po číslech řádků nebo označení sloupců.

›Označit jeden řádek nebo sloupec a s podrženou klávesou SHIFT použít šipku dolů nebo doprava.

›Označit první řádek nebo sloupec a s podrženou klávesou SHIFT označit poslední.

Také je možné označit nesouvislou skupinu řádků nebo sloupců: označte jeden řádek nebo sloupec a další pak označte klepnutím s podrženou klávesou CTRL.

Skrývání a zobrazení řádků a sloupců

Označíte jeden nebo více řádků, popřípadě sloupců, použijete pravé tlačítko myši a příkaz Skrýt. Zobrazit skrytý řádek nebo sloupec můžete tak, že myší ukážete mezi označení dvou sloupců nebo čísla dvou řádků, mezi nimiž je umístěn skrytý řádek nebo sloupec, a s podrženým levým tlačítkem myši zatáhnete dolů nebo doprava. Tímto způsobem se však nezachová původní výška řádku nebo šířka sloupce. Nelze také zobrazit více skrytých řádků (sloupců) najednou. Proto je lepší označit dva řádky nebo sloupce, mezi nimiž se nachází skrytá oblast, použít pravé tlačítko myši a z místní nabídky zadat příkaz Zobrazit.

Zobrazení a skrytí listu

Pravým tlačítkem myši klepněte na záložku listu a použijte příkaz Skrýt. Pokud označíte více listů klepnutím s podrženou klávesou CTRL, skryjete všechny označené listy najednou. Pro zobrazení listu klepněte pravým tlačítkem myši na záložku listu a použijte příkaz Zobrazit. Zobrazí se tak okno se seznamem skrytých listů. Zobrazit najednou několik listů možné není.

Obrázek 2.2: Seznam skrytých listů

Ruční seskupování řádků nebo sloupců

Příkladem může být tabulka, obsahující několik sloupců s dílčími údaji, další sloupec pak obsahuje součty pro jednotlivé řádky. Označíte dílčí sloupce a na kartě Data použijete tlačítko Seskupit. Nad označení sloupců se vloží další řádky s grafickým vyznačením seskupovaných sloupců. Klepnutím na tlačítko se symbolem minus se seskupované sloupce skryjí, klepnutím na tlačítko plus se zobrazí. Obdobně provedete seskupování po řádcích.

Seskupení zrušíte tak, že seskupené sloupce zobrazíte a označíte, na kartě Data pak klepnete na tlačítko Oddělit.

Obrázek 2.3: Seskupení sloupců

Ukotvení řádků a sloupců

Tuto možnost využijete při práci s velkými tabulkami. Vložte kurzor do sloupce A, na kartě Zobrazit klepněte na tlačítko Ukotvit příčky a zadejte příkaz Ukotvit příčky. Všechny řádky nad aktivním řádkem budou ukotveny, tedy budou stále viditelné a oddělené od zbytku listu čárou. Obdobně ukotvíte jeden nebo více sloupců, pokud označíte buňku v prvním řádku. Při označení buňky, která není ani v prvním řádku, ani v prvním sloupci, se ukotví najednou řádky i sloupce.

Ukotvení se ukládá společně se sešitem. Zrušíte jej klepnutím na tlačítko Ukotvit příčky a zadáním příkazu Uvolnit příčky.

Uvedené tlačítko obsahuje také příkazy Ukotvit horní řádek a Ukotvit první sloupec. Těmito příkazy se ukotví vždy první řádek nebo sloupec A, bez ohledu na pozici kurzoru. Oba uvedené příkazy se navzájem vylučují; není tedy možné je postupně použít k ukotvení prvního řádku a zároveň prvního sloupce.

Obrázek 2.4: Tlačítko pro ukotvení

Tisk rozsáhlé tabulky

Při tisku rozsáhlé tabulky se často stane, že tabulka zabere více než jednu stránku na výšku, popřípadě na šířku. Pak je vhodné zajistit, aby se na každé stránce tiskly nadpisy sloupců nebo první sloupec, označující data v jednotlivých řádcích. Provedete to následujícím postupem:

1.Na kartě Rozložení stránky klepněte na ležatý křížek ve skupině Vzhled stránky.

2.V zobrazeném okně klepněte na záložku List.

3.V položce Nahoře opakovat řádky vyznačte myší jeden nebo více řádků, které se mají při tisku zobrazit na začátku stránky.

4.Pro opakovaný tisk sloupců obdobně použijte položku Vlevo opakovat sloupce.

5.Potvrďte tlačítkem OK.

Obrázek 2.5: Opakování nadpisů sloupců

2.2Mazání a plnění buněk

Mazání

Mazání pomocí klávesy DELETE vymaže z buňky pouze obsah. Formátování a další nastavení buňky se nezmění. Pro různé způsoby mazání přejděte na kartu Domů a klepněte na tlačítko s obrázkem gumy ve skupině Úpravy. V nabídce se zobrazí příkazy Vymazat vše, Vymazat formáty, Vymazat obsah, Vymazat komentář.

Zápis do oblasti

Zápis hodnoty do celé oblasti najednou provedete tak, že vyznačíte oblast buněk, zapíšete potřebný údaj a uložení provedete klávesovou zkratkou CTRL+ENTER (pokud při uložení nepodržíte klávesu CTRL, hodnota se zapíše jen do první označené buňky). Tato akce vyplní celou označenou oblast beze změny zapsané hodnoty, hodí se tedy například pro vložení stejného kalendářního data do více buněk. Takto můžete zapisovat i do nesouvislé oblasti.

Pro zápis hodnoty pouze do prázdných buněk použijete následující postup:

1.Označte oblast pro zápis.

2.Na kartě Domů klepněte na tlačítko Najít a vybrat (s dalekohledem) a použijte příkaz Přejít na – jinak. Druhou možností je označit oblast, stisknout klávesu F5 a v zobrazeném okně klepnout na tlačítko Jinak. Oba způsoby zobrazí okno Přejít na – jinak.

3.V okně označte volbu Prázdné buňky a potvrďte tlačítkem OK.

4.Z označené oblasti zůstane označena jen nesouvislá oblast prázdných buněk. Zapište vkládanou hodnotu a potvrďte klávesovou zkratkou CTRL+ENTER.

Obrázek 2.6: Okno Přejít na – jinak

Plnění různým způsobem

Plnění oblasti provádíte nejčastěji tahem myší za pravý dolní růžek buňky. Pokud plnění provedete se stisknutou klávesou CTRL, v oblasti se vyplní vždy stejná hodnota. To můžete využít při plnění kalendářními daty nebo názvy dnů a měsíců, které při běžném plnění vytvoří textovou řadu.

Plnění můžete také využít pro tvorbu jednoduché řady: zapište první dvě hodnoty do dvou sousedních buněk, obě buňky označte a plňte tahem myší za pravý dolní růžek označené oblasti. Můžete tak vytvořit řadu číselných nebo datových hodnot; krok se automaticky vypočítá ze dvou prvních buněk.

Textovou řadu (a, b, c, atd.) plněním pomocí myši vytvořit nejde. Uvedený efekt se však při plnění uplatní i tehdy, pokud buňka obsahuje kombinaci textu a čísla. Například plněním textu „Praha 1“ dostanete text „Praha 2“ atd. Platí to i tehdy, jestliže je číselný údaj na začátku a mezi číslem a textem je mezera.

Po vyplnění oblasti jednou buňkou se vpravo dole zobrazí malé tlačítko, které umožňuje provést plnění různými způsoby. U textu máte tyto možnosti:

›Kopírovat buňky (výchozí volba) – plní se obsah i formátování.

›Vyplnit pouze formátování – z první buňky se přenese pouze formát, obsah buněk se nezmění.

›Vyplnit bez formátování – vyplní se pouze obsah, formátování buněk včetně první buňky se odstraní.

Při plnění číslem se navíc zobrazí volba Vyplnit řady, která vytvoří číselnou řadu s krokem jedna.

Při plnění kalendářními daty je nastavena výchozí volba Vyplnit řady. V nabídce jsou ještě tyto možnosti:

›Vyplnit dny – vytvoří se řada po jednom dni (stejné jako výchozí hodnota).

›Vyplnit pracovní dny – vytvoří se řada po jednom dni, s vynecháním sobot a nedělí.

›Vyplnit měsíce – ponechá se den, měsíc posune o jeden dopředu.

›Vyplnit roky – obdobně pro roky.

Obrázek 2.7: Tlačítko pro plnění buněk

2.3Vkládání různými způsoby

Při běžném kopírování a vkládání se do cílové oblasti vloží vše, tedy obsah buněk, formátování (včetně podmíněného), ověření dat a komentář. Excel rovněž umožňuje provádět kopírování a vkládání různými způsoby:

1.Výchozí buňku nebo oblast zkopírujte do schránky.

2.Označte cílovou buňku.

3.Klepněte na šipku u tlačítka pro vkládání (vlevo na kartě Domů), nebo na cílové buňce klepněte pravým tlačítkem myši a z místní nabídky zadejte příkaz Vložit jinak.

4.V zobrazeném okně zvolte potřebný způsob vkládání a potvrďte tlačítkem OK.

Po použití příkazu Vložit jinak se obsah schránky nezahodí, kolem zkopírovaných buněk jsou stále zobrazeny pohyblivé čárky. Proto je nutné obsah schránky vymazat klávesou ESC. Příkaz Vložit jinak lze použít pouze po kopírování; po vyjmutí je možné jen vkládat běžným způsobem.

Okno, zobrazené po použití příkazu Vložit jinak, obsahuje dva přepínače a dvě zaškrtávací volby.

V prvním přepínači Vložit máte tyto možnosti:

›Vše (výchozí volba): vkládání běžným způsobem.

›Vzorce: ze zkopírovaných buněk se vloží pouze obsah. Protože se obsah zkopírované buňky přenese bez formátování, hodnoty kalendářního data se vloží nenaformátované, tedy jako pořadové číslo dne od 1. ledna 1900.

Obrázek 2.8: Okno pro vkládání různými způsoby

›Hodnoty: ze zkopírovaných buněk se vloží pouze obsah s tím, že pokud zkopírované buňky obsahují vzorec, hodnota vypočtená vzorcem se vloží jako pevná hodnota. Údaje kalendářního data se vloží jako neformátované.

›Formáty: ze zkopírovaných buněk se vloží pouze formátování (včetně podmíněných formátů a rámečků). Tato volba odpovídá použití štětečku.

›Komentáře: ze zkopírovaných buněk se vloží pouze komentáře.

›Ověření: ze zkopírovaných buněk se vloží pouze ověřovací pravidla buněk.

›Použít zdrojový motiv u všech: u vkládaných buněk se použije motiv, který byl použit pro formátování zkopírovaných buněk. Nastavení motivu pro aktivní list provedete použitím tlačítka Motivy na kartě Rozložení stránky. Ve velké většině situací je výsledek této akce stejný jako u obyčejného vkládání.

›Vše kromě ohraničení: ze zkopírovaných buněk se vloží vše (obsah, formátování, ověření, komentáře), s výjimkou rámečků.

›Šířky sloupců: ze zkopírované buňky se přenese pouze šířka sloupce, v němž ležela kopírovaná buňka. Cílová buňka se nemění.

›Vzorce a formáty čísla: tato volba je obdobná jako volba Vzorce, ale ze zkopírované buňky se navíc přenese číselný formát. Hodnoty kalendářního data se tedy vloží s příslušným formátem. Ostatní formátování cílové buňky zůstane beze změny.

›Hodnoty a formáty čísla: tato volba je obdobná jako volba Hodnoty, ale ze zkopírované buňky se navíc přenese číselný formát. Hodnoty kalendářního data se vloží s odpovídajícím formátem. Ostatní formátování cílové buňky zůstane beze změny.

›Vše se sloučením podmíněných formátů (verze 2010 a výše): ze zkopírovaných buněk se vloží vše s jednou výjimkou: jestliže v cílových buňkách existují podmíněné formáty, nepřepíší se a podmíněné formáty ze zkopírovaných buněk se přidají k podmíněnému formátování v cílových buňkách.

Přepínač Operace umožňuje kombinovat vkládání číselných hodnot se zvolenou aritmetickou operací: přičtení, odečtení, násobení nebo dělení zkopírovanou hodnotou. Jestliže cílová buňka obsahuje vzorec, tento vzorec v buňce zůstane a odpovídajícím způsobem se upraví. Pokud není možné operaci provést (například výchozí nebo cílová buňka obsahuje text), akce neskončí chybou, ale neprovede se.

Volbu Vynechat prázdné můžete použít při vkládání oblasti, která obsahuje prázdné buňky. Při použití této volby se cílová oblast prázdnými buňkami nepřepíše a odpovídající buňky zůstanou beze změny. U neprázdných buněk se přenese vše.

Volbu Transponovat použijete pro otočení zkopírované oblasti o 90°, z řádků se stanou sloupce a ze sloupců řádky. Ze zkopírovaných buněk se přenese vše.

2.4Tvorba datových řad

Vytváření jednoduchých datových řad je možné pouhým tažením myší (viz podkapitolu 2.2, Mazání a plnění buněk). Druhou možností je použít nástroj pro tvorbu datových řad, který poskytuje více možností. Na kartě Domů klepněte na tlačítko Vyplnit s obrázkem šipky dolů (ve skupině Úpravy) a použijte příkaz Řady. Zobrazí se dialogové okno pro tvorbu řad. Pomocí tohoto okna můžete vytvářet čtyři typy řad:

›Lineární – řada čísel, lišících se o stálý rozdíl (1, 3, 5 …).

›Geometrický – řada čísel, lišících se o stálý násobek (1, 2, 4 …).

›Kalendářní – řada hodnot kalendářního data.

›Automatické vyplňování – řada tvořená texty (názvy dnů a měsíců).

Obrázek 2.9: Okno pro tvorbu řad

Lineární řada

Pro vytvoření lineární řady zapište do buňky počáteční hodnotu, vyznačte sloupec nebo řádek buněk tak, aby buňka s počáteční hodnotou byla v oblasti jako první, a použijete příkaz Řady. V prvním seznamu označte řádky nebo sloupce, dále vyplňte velikost kroku a potvrďte tlačítkem OK. Do pole Konečná hodnota je možné zapsat hodnotu, kterou vytvářená řada nesmí překročit; v tomto případě se nevyplní všechny označené buňky.

Jestliže označená oblast zahrnuje dvě vyplněné buňky, velikost kroku se automaticky určí podle těchto buněk. Vyplněné buňky nemusí ležet za sebou – u vyznačené oblasti je například možné vyplnit první a poslední buňku a vytvořením řady se pak hodnoty mezi ostatní buňky rovnoměrně rozpočítají.

Geometrická řada

Při tvorbě geometrické řady postupujete obdobně, jen s tím rozdílem, že do položky Velikost kroku zapíšete násobitel: například hodnota 1,1 znamená, že je každá následující hodnota vyšší o 10%. Při vyplnění dvou buněk v označené oblasti se však do položky Velikost kroku nevypočítá násobitel, ale rozdíl. Proto označíte volbu Trend. Položka Velikost kroku je pak nepřístupná a násobitel se vypočítá správně z obou vyplněných buněk.

Kalendářní řada

Při tvorbě kalendářní řady si můžete zvolit, zda velikost kroku představuje rozdíl ve dnech, měsících nebo rocích. Volba Pracovní den funguje stejně jako volba Den, ale do vyplňované řady se nezařadí dny, připadající na soboty a neděle. U kalendářní řady se velikost kroku automaticky vypočítá ze dvou sousedních vyplněných buněk. Pokud jsou v oblasti vyplněny dvě nesouvislé buňky, použije se krok vyplněný v položce Velikost kroku. I pro kalendářní řadu je možné využít položku Konečná hodnota, kam zapíšete poslední možné datum.

Textová řada

Poslední volba Automatické vyplňování funguje zcela stejně jako při plnění buněk tažením myší. Textová řada se vytvoří z názvů dnů v týdnu nebo ze jmen měsíců. Pro tvorbu textových řad můžete vytvářet také vlastní seznamy textů, například názvy dnů nebo měsíců v cizím jazyce. Postupujete takto:

1.Na listu vytvořte oblast s potřebnými texty.

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

3.V levé části zobrazeného okna klepněte na příkaz Upřesnit.

4.Ve skupině Obecné klepněte na tlačítko Upravit vlastní seznamy. Tím se zobrazí okno obsahující textové seznamy.

5.V položce Importovat seznam z buněk vyznačte oblast se zapsanými texty.

6.Klepněte na tlačítko Importovat a poté dvakrát na tlačítko OK.

Obrázek 2.10: Tvorba vlastního seznamu

Vytvořený seznam funguje stejně jako seznam dnů v týdnu a měsíců. Je součástí instalace Excelu a je možné ho využívat ve všech sešitech. Vlastní seznam můžete dodatečně upravovat nebo odstranit (odstranění je jištěno zpětným dotazem a je nevratné). Standardní seznamy, tedy názvy měsíců a dny v týdnu, nelze upravovat ani odstranit.

2.5Dynamické doplňování

Dynamické doplňování bylo do Excelu zařazeno poprvé ve verzi 2013. Umožňuje plnění sloupce podle zadaného vzoru. Pro doplňování použijete dva sloupce dat. V prvním jsou zapsány texty, druhý sloupec se bude doplňovat. Vlevo od sloupce textů by měly být prázdné buňky.

Pro doplňování zapíšete do první buňky pravého sloupce vzorek textu, podle něhož se bude doplňování provádět. Zbylé buňky ve druhém sloupci jsou prázdné. Buňku se vzorkem textu označíte a na kartě Data klepnete na tlačítko Dynamické doplňování (skupina Datové nástroje). Jestliže dynamické doplňování selže, zobrazí se informace o chybě.

Doplňování textových hodnot může fungovat dvěma způsoby:

›Texty v prvním sloupci jsou rozděleny mezerou na několik částí. Jako vzorek použijete první, druhé atd. slovo z prvního řádku levého sloupce. V pravém sloupci se pak do dalších řádků doplní odpovídající slova z levého sloupce. Texty v prvním sloupci by měly mít stejný počet částí – pokud tomu tak není, doplňování nemusí fungovat správně.

›Vzorek textu obsahuje jedno nebo více písmen, které se vyskytují v prvním řádku levého sloupce. Při tomto způsobu doplňování se zjistí poloha prvního výskytu zapsaného vzoru, u ostatních řádků se vyberou ty části textu, které jsou umístěné ve stejné poloze. Texty v prvním sloupci nemusí být děleny mezerami.

Doplňování je možné použít také u hodnot kalendářního data. Zapsané datum je chápáno jako text se třemi částmi oddělenými tečkou. Jako vzorek použijete číslo dne, měsíce nebo roku z první buňky v levém sloupci a do zbylých buněk v pravém sloupci se doplní odpovídající údaje, tedy pořadová čísla dne, měsíce nebo roku.

2.6Odstranění duplicit

U souvislé tabulky můžete odstranit řádky, které obsahují duplicitní hodnoty ve zvolených sloupcích. U tabulky postupujte takto:

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

2.Na kartě Data klepněte na tlačítko Odebrat stejné (skupina Datové nástroje).

3.V zobrazeném okně označte sloupce, které chcete použít ke kontrole duplicitních hodnot. Pro označení všech sloupců použijte tlačítko Vybrat vše, tlačítkem Zrušit výběr označení všech sloupců zrušíte.

4.Jestliže jsou v prvním řádku tabulky nadpisy sloupců, označte volbu Data obsahují záhlaví. V dialogovém okně se pak zobrazí nadpisy jednotlivých sloupců. Jestliže ponecháte volbu neoznačenou, první řádek v tabulce se považuje za řádek s daty, sloupce pro kontrolu duplicitních hodnot označíte jejich písmeny.

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

Obrázek 2.11: Odstranění duplicit

Při označení všech sloupců se z tabulky odstraní všechny identické řádky. Při označení jen některých sloupců se odstraní řádky, které mají v označených sloupcích stejné hodnoty, v tabulce se ponechá pouze první řádek. Odstranění duplicitních hodnot můžete provést i u označené oblasti buněk, což umožňuje vymazat duplicity jen z části tabulky. Volbu Data obsahují záhlaví ponecháte zpravidla neoznačenou.

2.7Práce s komentáři a hypertextovými odkazy

Komentáře

K jednotlivým buňkám je možné vkládat poznámky a další informace ve formě komentářů.

Pro vložení komentáře do buňky na ni klepnete pravým tlačítkem myši a z místní nabídky vyberete příkaz Vložit komentář. Text komentáře zapíšete do zobrazeného okénka. Buňka s komentářem je označena v pravém horním rohu červenou barvou. Komentář se zobrazí, jakmile na buňku ukážete myší.

Tento způsob zobrazení komentářů je nastaven po instalaci kompletu MS Office. Je možné jej změnit: zobrazte dialogové okno pro možnosti programu, vlevo klepněte na skupinu Upřesnit a v sekci Zobrazení nastavte potřebnou volbu v seznamu Zobrazit u buněk s komentáři.

Příkaz Vložit komentář se u buňky s vloženým komentářem změní na příkazy Upravit komentář a Odstranit komentář. Běžným mazáním pomocí klávesy DELETE se z buňky komentář neodstraní. Pokud však použijete příkaz Vymazat vše (viz podkapitolu 2.2), z buňky se odstraní všechno včetně vloženého komentáře.

Komentáře se běžně netisknou. Vytištění listu i s komentáři nastavíte následovně:

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

2.Klepněte na ležatý křížek ve skupině Vzhled stránky.

3.V zobrazeném okně přejděte na kartu List.

4.V seznamu Komentáře nastavte volbu Na konci listu.

5.Potvrďte tlačítkem OK.

Obrázek 2.12: Tisk komentářů

Tato volba zajistí, že se komentáře vytisknou na zvláštní stránce.

Hypertextové odkazy

Obsahem buňky může být také hypertextový odkaz. Buňka může obsahovat následující typy odkazů:

›Odkaz na některou buňku v sešitu – při klepnutí na buňku se kurzor přemístí na zvolenou buňku.

›Odkaz na soubor – při klepnutí na buňku se soubor otevře.

›Odkaz na webovou stránku – při klepnutí na buňku se spustí výchozí prohlížeč a otevře se nastavená webová stránka.

›Odkaz na adresu elektronické pošty – při klepnutí na buňku se spustí poštovní program a vytvoří se e-mailová zpráva se zadanou adresou. Tento typ odkazu funguje pouze u programů instalovaných přímo do počítače, jako je například MS Outlook. Elektronickou poštu otevíranou pomocí webového rozhraní takto využívat nelze.

Obrázek 2.13: Odkaz na místo v sešitu

Vytvoření hypertextového odkazu provedete takto:

1.Klepněte na buňku pravým tlačítkem myši a zadejte příkaz Hypertextový odkaz.

2.V zobrazeném okně vyplňte položku Zobrazený text. Jestliže buňka nebyla prázdná, zobrazeným textem bude obsah buňky.

3.Klepnutím na tlačítko Popis se zobrazí pomocné okno. V okně vyplňte text bublinkové nápovědy, zobrazované při najetí myší na buňku.

4.Pro tvorbu odkazu na místo v sešitu klepněte vlevo na tlačítko Místo v tomto dokumentu, označte list a do položky Zadejte odkaz na buňku zapište adresu buňky. V okně se zobrazí i názvy, definované v sešitu, pak postačí označit název.

5.Pro tvorbu odkazu na soubor klepněte vlevo na tlačítko Existující soubor nebo web. stránka. V seznamu Oblast hledání zvolte diskovou jednotku, poté vyberte adresář a soubor označte.

6.Tlačítko Existující soubor nebo web. stránka použijte i při tvorbě odkazu na webovou stránku. Adresu stránky zapište do položky Adresa. Šipka napravo od položky nabízí naposledy použité webové stránky.

7.Odkaz na adresu elektronické pošty vytvořte klepnutím na tlačítko E-mailová adresa. V okně vyplňte adresu elektronické pošty, popřípadě předmět vytvářené zprávy.

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

Obrázek 2.14: Odkaz na webovou stránku

U buňky s hypertextovým odkazem se v místní nabídce zobrazí příkazy Upravit hypertextový odkaz, Otevřít hypertextový odkaz (stejná akce jako při klepnutí na buňku) a Odebrat hypertextový odkaz. Odstraněním hypertextového odkazu se odebere nastavený link a písmo buňky se změní na normální. Obsah buňky se nezmění. Hypertextový odkaz se přenáší při kopírování a plnění buněk.

Při označení oblasti buněk, z nichž alespoň jedna obsahuje hypertextový odkaz, se v místní nabídce zobrazí příkaz Odebrat hypertextové odkazy. Tento příkaz umožňuje odstranit hypertextové odkazy u několika buněk najednou.

Hypertextové odkazy u grafických objektů

Hypertextový odkaz můžete přiřadit také grafickým objektům, například obdélníčku s textem, a vytvořit si tak tlačítko:

1.Na kartě Vložení klepněte na tlačítko Obrazce, vyberte Textové pole a tažením myší nakreslete na listu obdélníček.

2.Zapište text v obdélníčku.

3.Označte zapsaný text a na kartě Domů upravte písmo. Dále upravte barevné pozadí tlačítka a zarovnání textu ve vodorovném a svislém směru.

4.Tažením myší za úchyty upravte velikost tlačítka, klávesami s šipkami jeho polohu.

5.Nakonec klepněte na tlačítko pravým tlačítkem myši, z místní nabídky použijte příkaz Hypertextový odkaz a postupujte stejně jako u buňky. Položka Zobrazený text je v zobrazeném okně nepřístupná.

Obdobným způsobem můžete přidat hypertextový odkaz také k obrázku vloženému na list.

Konec ukázky

Table of Contents

Úvod

1 Nastavení programu

1.1 Možnosti práce programu

Skupina Obecné

Skupina Vzorce

Skupina Kontrola pravopisu a mluvnice

Skupina Uložit

Skupina Upřesnit

Skupina Doplňky

1.2 Úprava panelu Rychlý přístup

Úprava pásu karet

1.3 Kompatibilita mezi verzemi

2 Základní akce s tabulkami

2.1 Práce s řádky, sloupci a listy

Vkládání a odstraňování buněk

Skrývání a zobrazení řádků a sloupců

Zobrazení a skrytí listu

Ruční seskupování řádků nebo sloupců

Ukotvení řádků a sloupců

Tisk rozsáhlé tabulky

2.2 Mazání a plnění buněk

Mazání

Zápis do oblasti

Plnění různým způsobem

2.3 Vkládání různými způsoby

2.4 Tvorba datových řad

Lineární řada

Geometrická řada

Kalendářní řada

Textová řada

2.5 Dynamické doplňování

2.6 Odstranění duplicit

2.7 Práce s komentáři a hypertextovými odkazy

Komentáře

Hypertextové odkazy

Hypertextové odkazy u grafických objektů

3 Formátování

3.1 Základní shrnutí

3.2 Tvorba vlastních číselných formátů

3.3 Podmíněné formátování podle hodnot

4 Práce se vzorci

4.2 Odkazy na buňky a názvy buněk

4.5 Vzorce s hodnotami z různých listů

4.6 Vzorce s hodnotami z různých sešitů

5 Standardní funkce Excelu

5.1 Vkládání funkcí do vzorce

5.2 Matematické funkce

5.3 Funkce pro datum a čas

5.4 Textové funkce

5.5 Informační funkce

5.6 Logické funkce

5.7 Vyhledávací funkce

5.9 Komplexní příklady

6 Finanční výpočty a funkce

6.3 Další funkce pro úvěry

6.5 Investiční rozhodování

6.6 Výpočet odpisů

6.7 Další finanční funkce

7 Statistika

7.1 Statistické funkce

7.2 Regrese

7.3 Doplněk Analytické nástroje

7.4 Komplexní příklady

8 Ochrana a kontrola dat

8.5 Ověření vstupních dat podle hodnot

9 Sdílení sešitů mezi více uživateli

10 Tabulka jako databáze

10.1 Rozšíření možnosti řazení

10.2 Tvorba souhrnů

10.3 Možnosti automatického filtru

10.4 Databázové funkce

10.5 Slučování dat z více oblastí

10.6 Formátované tabulky

11 Export a import dat

11.1 Rozdělení textu do sloupců

11.2 Import a export do textového souboru

11.3 Spojení Excelu s Wordem

11.4 Připojení vnějších dat

11.5 Import dat ve formátu XML

11.6 Připojení vnějších tabulek pomocí dotazů

12 Kontingenční tabulky

12.1 Tvorba jednoduché kontingenční tabulky a grafu

12.2 Filtrace a řazení v kontingenční tabulce

12.3 Různé typy výpočtů a zobrazení hodnot

12.4 Výpočtová pole v kontingenční tabulce

12.5 Kontingenční tabulka z externího zdroje dat

12.6 Kontingenční tabulka s využitím relací

12.7 Využití dat z kontingenční tabulky

12.8 Komplexní příklady

13 Šablony a formuláře

13.1 Použití šablon

13.2 Práce s formulářovými prvky

13.3 Komplexní příklady

14 Analýza dat

14.1 Práce se scénáři

14.3 Řešitel

14.4 Citlivostní analýza

14.5 Analýza datových řad

15 Vizualizace dat

15.1 Pokročilé techniky při vytváření grafů

15.3 Nastavení os v grafech

15.4 Bublinové grafy

15.5 Paprskové a burzovní grafy

15.6 Grafy s podmíněným formátováním

15.8 Interaktivní grafy

15.9 Minigrafy

15.10 Podmíněné formátování s grafickými prvky

15.11 Komplexní příklady

16 PowerView