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 2016 a 2019 – 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
jako svou 7428. publikaci

Spoluautor Michal Bureš
Odpovědný redaktor Petr Somogyi
Sazba Petr Somogyi
Formát ePub Štěpán Böhm
Ikony Freepik (www.flaticon.com)
Počet stran 256
První vydání, Praha 2020
Vytiskla tiskárna PBtisk s.r.o., Příbram

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

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-1078-0 (ePub)
ISBN 978-80-271-1077-3 (pdf )
ISBN 978-80-271-2471-8 (print)

Úvod

Kniha, kterou právě otvíráte, je určena uživatelům Excelu, kteří již určité zkušenosti s tímto programem mají a chtěli by své znalosti dále rozšířit. Výklad je přednostně zaměřen na Excel 2019. Kniha však poslouží i uživatelům starších verzí programu (Excel 2013 a 2016). Pokud se u některých postupů v dřívějších verzích Excelu objevují odchylky nebo omezení, bude to v textu 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. V samostatných kapitolách věnovaných finančním a statistickým výpočtům jsou pak probrány funkce určené pro finanční a statistické výpočty.

Další kapitoly jsou věnovány ochraně dat, jejich exportu a importu a práci s připojenými daty. Databázové pojetí Excelu, kontingenční tabulky a grafy jsou popsány v samostatných kapitolách. Tyto kapitoly nemohou – vzhledem k omezenému rozsahu knihy – zachytit tuto problematiku v celé její šíři. Pro podrobnější seznámení s tématem proto odkazujeme na jinou naši knihu: Excel 2016 – práce s databázemi a kontingenčními tabulkami, kterou vydalo nakladatelství Grada Publishing v roce 2017.

Závěrečné kapitoly se pak 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í. Součástí knihy je i popis práce se dvěma užitečnými doplňky: Analytické nástroje a Řešitel.

Výklad je doplněn i řadou příkladů k procvičení. Sešity s příklady si můžete stáhnout na webových stránkách nakladatelství Grada Publishing (www.grada.cz) v sekci věnované této knize. Najdete zde sešity ve dvou verzích: v pracovních souborech jsou pouze zadání příkladů, v kontrolních souborech jsou pak příklady již vyřešeny.

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

•Názvy ovládacích karet, příkazy, názvy tlačítek, názvy oken, texty voleb v ovládacích seznamech, názvy listů v sešitech a parametry funkcí jsou zvýrazněny tučným písmem.

•Názvy souborů jsou zvýrazněny tučnou kurzívou.

•Označení kláves je zvýrazněno KAPITÁLKAMI.

Ať se vám tedy práce s Excelem nadále daří!

Autoři

1Nastavení programu

1.1Možnosti programu

Po instalaci balíku MS Office se u všech jeho komponent (tedy i u Excelu) nastaví výchozí způsob práce. Toto nastavení můžete do určité míry změnit a přizpůsobit tak program svým potřebám. Nastavení programu je trvalé a neváže se jen na otevřený sešit. Excel obsahuje celou řadu nastavení, která zpravidla ponecháte beze změny. V této kapitole se zmíníme pouze o těch nastaveních, jež bude zapotřebí v některých případech změnit.

Pro nastavení Excelu klepnete na záložku karty Soubor a vlevo dole klepnete na příkaz Možnosti. Tím se zobrazí okno Možnosti aplikace Excel, ve kterém budete nastavení práce Excelu provádět. V levé části okna vždy klepnete na zvolenou skupinu, v pravé části provedete potřebná nastavení a potvrdíte tlačítkem OK.

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

Skupina Obecné

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

Skupina Vzorce

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

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é si aktualizaci vynutit klávesou F9. Toto nastavení je vhodné použít u rozsáhlých sešitů, které obsahují řadu velký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. Nesmíte však zapomenout, že – jako všechna nastavení – je i zrušení automatického přepočtu trvalé. Po skončení práce s velkým sešitem je tedy třeba automatickou aktualizaci sešitu opět aktivovat.

Označení volby Styl odkazu R1C1 způsobí, že se v nadpisech sloupců na listu zobrazí pořadová čísla sloupců místo písmen.

Obrázek 1.2 Nastavení Excelu ve skupině Vzorce

Skupina Data

Tato skupina je v Excelu 2019 novinkou. Volby v této skupině použijete především pro výchozí nastavení kontingenční tabulky vkládané do sešitu. Z tohoto důvodu bude tato skupina podrobněji popsána v kapitole 12, která se věnuje kontingenčním tabulkám.

Skupina Kontrola pravopisu a mluvnice

Kontrola pravopisu je společným nástrojem celého balíku MS Office a funguje ve všech programech tohoto kompletu jednotně. Proto se změny provedené v této skupině projeví u všech programů MS Office. Protože se kontrola pravopisu používá především v textovém editoru Word, je lepší provést případné změny v nastavení u kontroly pravopisu právě v tomto programu.

Skupina Ukládání

Otevřený sešit Excelu se automaticky ukládá vždy po deseti minutách. Ve skupině pro ukládání můžete toto nastavení případně změnit. Dále zde můžete změnit výchozí umístění pro ukládání a otvírání sešitů (standardně je nastavena složka Dokumenty), což provedete v položce Výchozí místní umístění souborů. Údaje v této položce je nutné zapsat pomocí klávesnice.

Skupina Jazyk

Pro tuto skupinu platí totéž, co bylo řečeno v souvislosti s kontrolou pravopisu a mluvnice. Nastavení je společné pro celý balík MS Office a je lepší ho provést ve Wordu.

Skupina Upřesnit

Tato skupina obsahuje řadu prvků, které jsou rozdělené do sekcí. Pro praxi by mohly mít význam především nastavení uvedená dále.

V sekci Možnost úprav:

•Po stisknutí klávesy Enter přesouvat výběr: výchozí volba Dolů způsobí 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.

•Automaticky vložit desetinnou čárku: označení této volby 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í.

•Používat oddělovače ze systému: pokud tuto volbu vyřadíte, odblokuje se položka Oddělovač desetinných míst, kde je možné nastavit v desetinných číslech tečku místo čárky. Tuto úpravu použijete například při exportu listu do textového souboru, kde mají být čísla s desetinnými tečkami.

V sekci Zobrazení:

•Zobrazovat tento počet posledních sešitů: číselník určuje počet naposledy otevřených sešitů, které se zobrazí na kartě Soubor při použití příkazu Otevřít.

•U buněk s komentáři zobrazovat: v přepínači nastavíte způsob zobrazení vytvořených komentářů (podrobněji viz kapitolu 2.5).

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 můžete 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 vyřazeni této volby nejsou v buňce zobrazeny nulové hodnoty (zapsané jako pevná čísla i 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ů (případně nahoře nad označením sloupců) se zobrazí tlačítka pro seskupení, která umožňují seskupené řádky nebo sloupce zobrazit nebo skrýt. Při zrušení této volby se tato tlačítka nezobrazují a skrytí seskupených řádků nebo sloupců není možné.

•Zobrazit mřížku: zobrazení hranic mezi buňkami.

V sekci Obecné je umístěno tlačítko Upravit vlastní seznamy. Toto tlačítko vyvolá dialogové okno pro práci s vlastními seznamy textů (viz kapitolu 2.4).

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

Skupina Doplňky

Některé méně používané nástroje Excelu nejsou po instalaci programového balíku MS Office ihned dostupné. Instalují se ve formě doplňku, který je nutné aktivovat. Aktivaci doplňku provedete těmito kroky:

1.Zobrazte okno pro nastavení Excelu a vlevo klepněte na skupinu Doplňky.

2.Klepněte na tlačítko Přejít.

3.V dalším zobrazeném okně označte potřebný doplněk.

4.Potvrďte tlačítkem OK.

Aktivované doplňky jsou zařazeny v seznamu Aktivní doplňky aplikací. Po aktivaci doplňku se zpravidla na některou kartu přidá tlačítko pro jeho spuštění. Nejpoužívanějšími doplňky jsou Analytické nástroje (viz kapitolu 7.3) a Řešitel (viz kapitolu 13.3).

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 balíku 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 vlastní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 (najdete je u pravého okraje okna) je možné v panelu Rychlý přístup 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 rozevíracím seznamu jako výchozí nastavena volba Oblíbené příkazy. Ta zajistí, že se v levém velkém seznamu zobrazí nejpoužívanější příkazy Excelu. Druhou možností je nastavit v tomto seznamu volbu Příkazy mimo pás karet, jež zobrazí ty příkazy, které na kartách umístěny nejsou. Příkladem může být tlačítko Publikovat jako webovou stránku.

Pokud potřebujete vrátit panel 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

1.3.Úprava pásu karet

Kromě panelu Rychlý přístup můžete měnit i nastavení ovládacích karet. Také toto nastavení je trvalé a projeví se v každém sešitu.

Pás ovládacích karet můžete upravit třemi způsoby:

•některou z karet zobrazit nebo skrýt,

•do některé ze standardních karet přidat novou skupinu a zařadit do ní potřebná tlačítka,

•vytvořit novou vlastní kartu s jednou nebo více skupinami.

Pro zobrazení nebo skrytí některé karty provedete tyto kroky:

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 seznamu Hlavní karty, umístěném v pravé části okna, označte kartu, kterou chcete zobrazit (nebo naopak zrušte označení u karty, kterou chcete skrýt).

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

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

1.Zobrazte okno pro nastavení Excelu a skupinu Přizpůsobit pás karet.

2.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. Polohu nové skupiny můžete upravit pomocí tlačítek se šipkami, které najdete u pravého okraje okna.

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

4.Do nové skupiny přidejte jednotlivé příkazy. Postupujte stejně jako u panelu Rychlý přístup: v levém velkém seznamu příkaz označíte a tlačítkem Přidat jej zařadíte do skupiny. Do skupiny můžete přidat libovolné tlačítko (oblíbené nebo mimo pás karet).

5.Pokud potřebujete ze skupiny některé tlačítko vyřadit, označte jej a klepněte na tlačítko Odebrat.

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

Popsaným způsobem můžete přidávat tlačítka pouze do nově vytvořené skupiny. Nově vytvořenou skupinu můžete dodatečně upravit, přejmenovat, přesunout nebo odstranit. Se standardními skupinami není možné úpravy provádět.

Vytvoření vlastní karty provedete takto:

1.Zobrazte si 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 jí přidejte vhodný název a zařaďte do ní příkazy.

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

7.Potvrďte tlačítkem OK.

Nově vytvořenou kartu je možné dodatečně upravovat, případně z pásu karet odstranit. Standardní karty odstranit možné není.

1.4.Kompatibilita mezi verzemi

Sešity vytvořené v Excelu verzí 2007 až 2019 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 a 2010 nemají možnost vytvářet relace mezi tabulkami a popisky datových řad u grafů se nemohou 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 xlsx. Opačně to samozřejmě možné je, ale v sešitu staršího formátu jsou znepřístupněny 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í. Pro uložení klepnete na tlačítko Pokračovat.

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+PLUS. Pro odstranění buněk použijete pravé tlačítko myši a příkaz Odstranit, nebo klávesovou zkratku CTRL+MINUS.

Pokud předem označíte celý řádek nebo sloupec, bude se vkládat nebo odstraňovat celý řádek nebo sloupec. Vložení nového řádku se provede nad označeným řádkem, vložení nového sloupce vlevo od označeného sloupce. Při označení jen jedné nebo více buněk se zobrazí okno, v němž určíte, 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. Pro vložení nebo odstranění celého řádku nebo sloupce je však výhodnější provést akci s označeným celým řádkem nebo sloupcem.

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 třemi způsoby:

•Zatáhnout myší se stisknutým levým tlačítkem na čí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í.

Můžete také označit jeden řádek nebo sloupec a další pak označit klepnutím se stisknutou klávesou CTRL. Tímto způsobem označíte nesouvislou skupinu řádků nebo sloupců.

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

Pro skrytí označíte jeden nebo více řádků nebo sloupců, na řádku nebo sloupci pak klepnete pravým tlačítkem myši a zadejte příkaz Skrýt.

Zobrazit skrytý řádek nebo sloupec je možné 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 se stisknutým levým tlačítkem myši zatáhnete dolů nebo doprava. Tento způsob však není příliš výhodný, protože se nezachová původní výška řádku nebo šířka sloupce a nelze zobrazit více skrytých řádků nebo sloupců najednou. Lepší postup je označit dva řádky nebo sloupce, mezi nimiž se nachází skrytá oblast, do označené oblasti klepnout pravým tlačítkem myši a použít příkaz Zobrazit.

Skrytí sloupců nebo řádků má smysl například u tisku. Excel se důsledně řídí zásadou „vytisknu jen to, co vidíš“, takže skryté buňky se v tištěném výstupu neobjeví.

Zobrazení a skrytí listu

Pravým tlačítkem myši klepnete na záložku listu a z kontextové nabídky zadáte příkaz Skrýt. Pokud klepnutím se stisknutou klávesou CTRL označíte více listů, skryjete všechny označené listy najednou.

Pro zobrazení listu klepnete pravým tlačítkem myši na záložku některého listu a použijete příkaz Zobrazit. Tím se zobrazí okno se seznamem skrytých listů. V seznamu označíte skrytý list a klepnete na tlačítko OK. Tímto způsobem je možné zobrazit pouze jeden skrytý list. Jestliže potřebujete zobrazit několik skrytých listů, musíte akci opakovat.

Obrázek 2.2 Seznam skrytých listů

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

V praxi často používáte tabulky, které mají v prvním sloupci nadpisy, následuje několik sloupců s dílčími údaji a poslední sloupec v tabulce obsahuje součty pro jednotlivé řádky. U takovéto tabulky je někdy třeba sloupce s dílčími údaji skrýt (například pro tisk) a jindy je zase zobrazit. Pro snadné skrývání a zobrazování několika sloupců můžete do tabulky vložit seskupování.

Označte dílčí sloupce a na kartě Data použijte tlačítko Seskupit (je umístěné ve skupině Přehled). 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, pak je označíte a na kartě Data klepnete na tlačítko Oddělit.

Obrázek 2.3 Seskupení sloupců

Ukotvení řádků a sloupců

Při práci s velkými tabulkami je nepříjemné, že při odrolování více řádků směrem dolů již nejsou vidět nadpisy jednotlivých sloupců. Proto je vhodné řádek s nadpisy sloupců ukotvit:

1.Umístěte kurzor do sloupce A.

2.Na kartě Zobrazit klepněte na tlačítko Ukotvit příčky.

3.Použijete 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 vodorovnou čárou. Pokud označíte buňku v prvním řádku, ukotvíte tímto postupem jeden nebo více sloupců, které budou při pohybu kurzoru doprava stále viditelné. Při označení buňky, která není ani v prvním řádku, ani v prvním sloupci, se najednou ukotví řádky i sloupce.

Ukotvení se ukládá spolu se sešitem. Zrušíte jej klepnutím na tlačítko Ukotvit příčky a použití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í

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

Tisk rozsáhlé tabulky

Při tisku rozsáhlé tabulky se často stane, že zabere více než jednu stránku na výšku (případně na šířku). Pak je vhodné zajistit, aby se nadpisy sloupců označujících data v jednotlivých řádcích (nebo první sloupec) vytiskly na každé stránce. 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.

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 nijak nezmění. Pro různé způsoby mazání přejdete na kartu Domů a klepnete na tlačítko Vymazat ve skupině Úpravy (jedná se o tlačítko s obrázkem gumy). V nabídce můžete použít příkazy Vymazat vše, Vymazat formáty, Vymazat obsah a Vymazat komentáře.

Zápis do oblasti

Excel nabízí šikovný způsob, jak zapsat stejnou hodnotu do několika buněk najednou. Vyznačíte oblast buněk, zapíšete potřebný údaj a pro uložení použijete klávesovou zkratku CTRL+ENTER. Tato akce vyplní celou označenou oblast beze změny zapsané hodnoty. Hodí se tedy například pro vložení stejného data do více buněk. Můžete takto zapisovat i do nesouvislé oblasti. Jestliže při uložení nestisknete klávesu CTRL, hodnota se zapíše jen do první označené buňky.

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

Další možností je zápis hodnoty pouze do prázdných buněk ve vyznačené oblasti. Použijete tento postup:

1.Označte oblast pro zápis.

2.Na kartě Domů stiskněte tlačítko Najít a vybrat ve skupině Úpravy (s obrázkem lupy).

3.V zobrazené nabídce vyberte příkaz Přejít na – jinak.

4.Druhou možností je označit oblast, stisknout klávesu F5 a v zobrazeném okně klepnout na tlačítko Jinak. Oba tyto způsoby zobrazí okno Přejít na – jinak.

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

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

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. Můžete to využít při plnění stejnou hodnotou kalendářního data nebo pro 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é číselné řady nebo řady kalendářních dat. Do dvou sousedních buněk zapíšete první dvě hodnoty řady, obě buňky označíte a naplníte dolů tahem myší za pravý dolní růžek označené oblasti. Takto můžete vytvořit řadu číselných hodnot nebo řadu kalendářních dat; potřebný krok se přitom automaticky vypočítá ze dvou prvních buněk. Textovou řadu (a, b, c, atd.) plněním pomocí myši vytvořit nelze. Změna obsahu buňky se však při plnění uplatní také 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 (pokud je číslice na konci, mezi textem a číslicí mezera být nemusí).

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 se nezmění.

•Dynamické doplňování: pro plnění oblasti se uplatní nástroj Dynamické doplňování (o tomto způsobu plnění pojednává podkapitola 2.5).

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ím datem je výchozí volba Vyplnit řady, což znamená vytvoření datové řady po jednom dni. V nabídce jsou ještě tyto možnosti:

•Kopírovat buňky: plní se stejným datem.

•Vyplnit pouze formátování: přenese se pouze formátování, obsah buněk se nezmění.

•Vyplnit bez formátování: do plněných buněk se nepřenese formátování (tedy například odlišné písmo). Nepřenese se však ani numerický formát z první buňky. Výsledkem je, že se sice vytvoří řada po dnech, ale plněné buňky mají obecný formát a místo dat jsou zobrazena čísla.

•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 také 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 použijte 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é pouze vkládat běžným způsobem.

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

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

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. 1. 1900.

•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. Kalendářní data se vloží jako nenaformá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, ve které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 datumový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 příslušný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ů: 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. Vkládané podmíněné formátování se přitom zařadí na první místo a původní podmíněné formátování z cílové buňky se odsune na druhé místo.

Pro rychlý přenos formátování z jedné buňky do druhé můžete také použít tlačítko Kopírovat formát (s obrázkem štětečku), které najdete na kartě Domů ve skupině Schránka. Označíte naformátovanou buňku, klepnete na tlačítko se štětečkem a poté na buňku, kterou chcete naformátovat. Štětečkem namočeným do formátu můžete přetřít celou oblast a naformátovat tak více buněk najednou.

Přepínač Operace umožňuje kombinovat vkládání číselných hodnot se zvolenou aritmetickou operací (tedy přičtením, odečtením, násobením nebo dělením se 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 se neprovede.

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

V podkapitole 2.2 byla popsána tvorba jednoduchých číselných řad a řad kalendářních dat pomocí tažení myší. Více možností pak poskytuje nástroj pro tvorbu datových řad. Na kartě Domů klepnete na tlačítko Vyplnit s obrázkem šipky dolů (ve skupině Úpravy) a použijete příkaz Řady. Tím se zobrazí 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ích dat.

•Automatické vyplňování – řada tvořená texty (názvy dnů a měsíců, případně dalších textů, které do Excelu zadáte).

Obrázek 2.9 Okno pro tvorbu řad

Lineární řada

Lineární číselnou řadu vytvoříte tímto postupem:

1.Do některé buňky zapište počáteční hodnotu.

2.Vyznačte sloupec nebo řádek buněk tak, aby buňka s počáteční hodnotou byla ve vyznačené oblasti jako první.

3.Klepněte na tlačítko Vyplnit a použijte příkaz Řady.

4.V přepínači Typ označte volbu Lineární.

5.V položce Velikost kroku zapište potřebný rozdíl mezi plněnými buňkami.

6.Do položky Konečná hodnota je možné zapsat hodnotu, kterou vytvářená řada nesmí překročit; v tomto případě se nemusí vyplnit všechny označené buňky.

7.Potvrďte tlačítkem OK.

Jestliže označená oblast zahrnuje dvě vyplněné buňky, velikost kroku se automaticky určí z nich. 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í.

Jestliže při tvorbě řady máte v úmyslu použít položku Konečná hodnota, musíte vyznačit dostatečně velkou oblast. Při tvorbě řady je možné provést plnění pouze do označených buněk.

Geometrická řada

Geometrickou řadu vytvoříte obdobným způsobem. Rozdíl spočívá pouze v tom, že v přepínači Typ vyberete volbu Geometrický a do položky Velikost kroku zapíšete odpovídající násobitel. Například hodnota 1,1 znamená, že se při plnění každá následující hodnota zvětší o 10 %.

Při vyplnění dvou buněk se však do položky Velikost kroku nevypočítá násobitel, ale rozdíl mezi buňkami, a to i při tvorbě geometrické řady. V tomto případě označíte volbu Trend. Položka Velikost kroku je pak nepřístupná a násobitel se správně vypočítá z obou vyplněných buněk.

Při tvorbě geometrické řady můžete také využít konečnou hodnotu, ale pouze v případě, kdy do položky Velikost kroku zapíšete násobitele. Při označení volby Trend je položka Konečná hodnota nepřístupná.

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, v položce Velikost kroku se zobrazí jednička a krok je třeba zadat z klávesnice. Druhá vyplněná buňka se pak při plnění přepíše. U kalendářní řady můžete také 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ší. Z názvů dnů v týdnu nebo jmen měsíců se vytvoří textová řada.

Pro tvorbu textových řad si 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 zapište oblast s potřebnými texty.

2.Na kartě Soubor použijte 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ů. Seznam se stane součástí instalace Excelu a můžete 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) upravovat ani odstranit nelze.

2.5Dynamické doplňování

Dynamické doplňování umožňuje automatické plnění sloupce texty podle zadaného vzoru. Doplňování se provádí ve dvou sloupcích umístěných vedle sebe. V prvním sloupci jsou zapsány texty, druhý sloupec se bude doplňovat. Vlevo od sloupce textů by měly být prázdné buňky.

Do první buňky pravého sloupce zapíšete vzorek textu, podle které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ěny 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, 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

Někdy potřebujete z tabulky odstranit řádky, které obsahují ve zvolených sloupcích duplicitní hodnoty:

1.Označte potřebnou oblast buněk.

2.Na kartě Data klepněte na tlačítko Odebrat duplicity (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.

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. Jestliže je upravovaná tabulka souvislá (tedy neobsahuje prázdné řádky), pro odstranění duplicit není třeba označovat celou oblast, ale postačí klepnout kamkoliv do tabulky.

Obrázek 2.11 Odstranění duplicit

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

Komentáře

K jednotlivým buňkám můžete rovněž připojit komentář, který obsahuje doplňkové informace o buňce.

Při vkládání 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. V prvním řádku okénka se automaticky zobrazí přihlašovací jméno uživatele, tento řádek je možné vymazat.

Obrázek 2.12 Tisk komentářů

Způsob zobrazení komentářů v buňkách je možné nastavit podle potřeby. Otevřete dialogové okno pro možnosti programu, vlevo klepnete na skupinu Upřesnit a v sekci Zobrazení nastavíte potřebnou volbu v seznamu Zobrazit u buněk s komentáři. Při použití volby Pouze indikátory; komentáře při přechodu je buňka s komentářem označena červenou barvou v pravém horním roku a okno s komentářem se objeví, jakmile na buňku ukážete myší. Volba Komentáře a indikátory znamená, že buňka s komentářem je označena a okno s komentářem je stále zobrazeno.

Při klepnutí pravým tlačítkem myši na buňku s vloženým komentářem je v místní nabídce příkaz Vložit komentář nahrazen 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.

Komentáře se vytisknou na zvláštní stránce.

Vložení hypertextového odkazu do buňky

Text zapsaný do buňky může vytvořit hypertextový odkaz. Do buňky je možné vložit tyto 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.

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