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.

Pavel Lasák

Praktické použití funkcí v Excelu

Vydala Grada Publishing, a.s.

U Průhonu 22, Praha 7

obchod@grada.cz, www.grada.cz

tel.: +420 234 264 401

jako svou 8145. publikaci

Odpovědná redaktorka Věra Slavíková

Sazba Jan Šístek

Ikony Freepik (www.flaticon.com)

Počet stran 360

První vydání, Praha 2021

Vytiskla TISKÁRNA V RÁJI, s.r.o., Pardubice

© Grada Publishing, a.s., 2021

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

Cover Photo © Depositphotos/riariu, peshkov

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-6829-3 (ePub)

ISBN 978-80-271-4393-1 (pdf)

ISBN 978-80-271-1303-3 (print)

Úvodní slovo

Kniha, kterou právě otevíráte se zabývá praktickým využitím funkcí v tabulkovém kalkulátoru Excel 2019 v českém jazyce. Použitelná je i pro Excel verze 2016, 2013, 2010, 2007 v českých i anglických verzích, případné odchylky jsou uvedeny. Navíc jsou přiloženy sešity, které po otevření v jiné, než české verzi budou mít názvy funkcí v daném jazyce (tedy nejen anglickém).

První kapitola – jde o teoretický úvod do Excelu, jak pracovat s řádky a sloupci, jak se odkazovat, co je tabulka jako tabulka a jaká úskalí vás mohou potkat, jak na hledání a opravu chyb, včetně praktické ukázky, jak do Excelu vložit vlastní funkce. Ke konci kapitoly se seznámíte s různými nastaveními, která vám práci v Excelu mohou zpříjemnit a zrychlit.

Druhá kapitola – tady se dozvíte, jak pracovat s funkcemi datum a čas. Začnete od teoretického úvodu a syntaxe, přejdeme na počty a výpočty s datum a čas, dále přes úpravy času, tvorbu kalendářů až po praktické výpočty například výpočet mzdy na základě odpracovaných hodin.

Třetí kapitola – zde se podíváte na praktickou práci s logickými a informačními funkcemi, od teorie a syntaxe, možné problémy při více logických podmínkách, zjištění typu hodnoty v buňce, zda jde o číslo, text, nebo logickou hodnotu. V případě chybových hodnot se podíváte, jak tyto chyby ošetřit. Z této kapitoly následně budete čerpat v následujících kapitolách týkajících se statistických, matematických, vyhledávacích nebo textových funkcí, stejně tak při práci s podmíněným formátováním.

Čtvrtá kapitola – tady si procvičíte praktickou práci s textovými funkcemi. Od jejich syntaxe přes práci s dynamickými texty, výpočty a podmínky nad buňkami, úpravy textů, náhrady a odstraňovaní znaků, převody typů (např. z textu vytvořit číslo) až po speciálnosti, jak textové funkce využít pro tvorbu grafů nebo vytvoření abecedy.

Pátá kapitola – tady se podíváte na práci se statistickými funkcemi, začnete u seznámení se syntaxí funkcí, přejdete na hledání a práci s minimální a maximální hodnotou, na průměrné a mediánové hodnoty na základě dalších podmínek, počty na základě podmínek, a nakonec se podíváte i na počty duplicitních nebo unikátních hodnot.

Šestá kapitola – zde se seznámíte s matematickými funkcemi, od syntaxe základních matematických funkcí, přes využití matematických funkcí u základních výpočtů, součty nad oblastí včetně podmínek, nejen číselných, ale i textových. Následně pak na goniometrické a logaritmické funkce. Dozvíte se také jak na náhodná čísla a dostanete tipy pro použití matematických funkcí.

Sedmá kapitola – tady si projdete nejen nejznámější a pro někoho i nejpoužívanější funkci SVYHLEDAT (VLOOKUP). Podíváte se na syntaxi vyhledávacích funkcí, hledaní pozice nebo hodnoty v tabulce (ať už ve sloupci, nebo řádku), také hledání v tabulce na základě kritéria pro řádek a sloupec, využití zástupných znaků (otazník, hvězdička), až po transformaci tabulek a převody písmenek sloupce na čísla sloupců a naopak.

Osmá kapitola – zde se podíváte na novou sekci funkcí pro práci s dynamickými oblastmi, od informací, jak nové funkce fungují, přes jejich syntaxe, až po praktické použití těchto funkcí, včetně možností, jak schopnosti těchto funkcí nahradit klasickými funkcemi a v neposlední řadě se podíváte i na nové chybové hodnoty, které při práci s těmito funkcemi mohou vznikat.

Devátá kapitola – tady se podíváte na maticové použití funkcí, opět začnete u syntaxe, zjistíte jak na základní výpočty – součet, rozdíl, determinant, inverzní matice. Znalosti využijete u výpočtu lineárních rovnic o dvou a třech neznámých. Dozvíte se také, jak na zajímavé maticové výpočty jako maximální a minimální hodnota rozdílu, poslední údaj ve sloupci, počet znaků v oblasti.

Desátá kapitola – zde se seznámíte, jak na podmíněné formátování, od teorie, jak ho vložit, přes nastavení formátu aplikovaného na buňku, na celý řádek (sloupec), který splňuje požadovanou podmínku, včetně tvorby dynamického kalendáře. Také se dozvíte, jak na ověření dat.

Jedenáctá kapitola – jak na ostatní funkce, aneb co se jinam nevešlo a může se hodit, počínaje vlastními funkcemi, práci s internetovými odkazy a emailovými adresami, práci se sešity a listy a v neposlední řadě převody mezi číselnými soustavami, práce s komplexními čísly.

1Zopakování základů

Pro práci vExcelu není na škodu si osvěžit základy. Pokud základy bezpečně ovládáte, můžete tuto kapitolu přeskočit arovnou se vrhnout na druhou, kde se již prakticky seznámíte sfunkcemi pro práci sdatem ačasem.

■Klávesové zkratky.

■Práce vlistech aslisty.

■Zjednodušení azpřehlednění vzorců.

■Chyby ve vzorcích.

■Vlastní funkce.

1.1Klávesnice aklávesové zkratky

Práce vExcelu se zpříjemní azrychlí, pokud začnete používat klávesové zkratky nebo klávesu Alt snásledným stiskem příslušných písmen (je to praktické, nejen pokud nefunguje myš).

1.1.1Klávesové zkratky

Vknize je pracováno stěmito vybranými klávesovými zkratkami:

■F1 – nápověda.

■F9 – přepočítat sešit, případně dílčí výpočty ve vzorcích.

■Shift+F9 – přepočítat list.

■F4 – opakovat poslední akci, nebo při zápisu vzorce Změna odkazů (relativní > absolutní > smíšené).

■F2 – vstoupit do buňky.

■Alt+Enter – tvrdý Enter (odřádkování vbuňce nebo ve vzorci).

■Ctrl+Shift+Enter – vložit jako maticový vzorec.

■Ctrl+Enter – potvrdit vzorec azůstat vbuňce.

■Ctrl+, – zobrazit nebo skrýt vzorce vbuňkách.

■Shift+F2 – vložit komentář.

■Ctrl+/ – zobrazit velikost matice (lomítko na numerické klávesnici).

■Ctrl+T – formátovat jako tabulku (Tabulka jako tabulka).

■Ctrl+F3 – spuštění Správce názvů.

■Ctrl+G – zobrazit dialogové okno: Přejít na … .

■Ctrl+S – uložit jako.

■Ctrl+Fn+S – zapnutí/vypnutí ScrLk, uněkterých notebooků může být přímo klávesa ScrLk, nebo jiná klávesová zkratka pro zapnutí/vypnutí (využijte dokumentaci kvašemu notebooku).

■Ctrl+Shift+F – zobrazit dialogové okno Formát buněk.

■Ctrl+Shift+F3 – vytvořit definovaný název zvybrané buňky nebo oblasti buněk.

■Alt+F3 – vstup do Pole názvů.

■Alt+F5 – obnovit (aktualizovat externí data).

■Alt+písmena – pohyb po pásu karet (viz dále).

Některé notebookové klávesnice mají přehozeny klávesy Ctrl aFn, voperačním systému si můžete toto rozložení kláves prohodit.

Některé klávesnice mají funkční klávesy (F1–F12) dostupné po stisku Fn.

1.1.2Znaky na české klávesnici svyužitím klávesy AltGr

Orientace vběžně užívaných znacích na klávesnici se vyžaduje jako základní uživatelská znalost. Pro pokročilejší práci je dobré vědět, kde jsou na české klávesnici umístěny speciální znaky jako např. \|{}$€, abyste nemuseli využívat Mapu znaků, klávesu Alt aASCI číslo znaku, nebo se přepínat na anglickou klávesnici. Tyto speciální znaky napíšete spřidržením klávesy AltGr (neboli pravý Alt) aklikem na klávesu spříslušným znakem dle obrázku níže.

Obrázek 1.1: Rozmístění znaků na klávesnici

Na klávesnici, která tyto znaky nemá uvedeny, je lze fixem dopsat.

1.1.3Zamknutí posunu listu – ScrLk

Někdy vás vExcelu zaskočí, že se nemůžete pohybovat po buňkách kurzorovými klávesami, jen se posunuje celý list. Příčinou je stisknutá klávesa ScrLk, nebo neúmyslný stisk například vněkterých PC jiná kombiance kláves (Ctrl+Fn+S). Kodemčení využijte program „Klávesnice“, který je součástí Windows, nebo si vnápovědě ke svému notebooku najděte, jak tuto klávesu uvolnit. Častá kombinace klávesovou zkratkou (Ctrl+Fn+S).

Obrázek 1.2: Spuštěný program Klávesnice

Nechtěné zamknutí ScrLk se může stát například při ukládání sešitu pomocí klávesové zkratky (Ctrl+S), neboť vedle klávesy Ctrl je Fn.

1.1.4Ovládaní Excelu svyužitím klávesy Alt

Při ovládání Excelu aikon zpásu karet většinou pracujete smyší, ale můžete využít iklávesu Alt. Klikem na klávesu Alt se vpásu karet zobrazí písmena, stiskem zvoleného písmene si vyberete kartu nebo ikonu. Například: Pro nastavení ověření dat stačí postupně včeské verzi Excelu stisknout klávesy Alt W Ě V (máte-li jinou jazykovou verzi, Excel vám napovídá příslušná písmena).

Obrázek 1.3: Pás karet využití Alt kovládání

Pás karet sikonami lze skrýt dvojklikem na název kterékoli karty.

1.2Práce slisty atabulkami

Pro zefektivnění práce vExcelu je vhodné pracovat slisty (duplikovat list, skrýt list), vlistech (přesunout, zkopírovat, skrýt sloupec nebo řádek) avtabulkách (označit, zkopírovat, transformovat, přesunout).

Práce slisty

Pokud vExcelu máte více listů apotřebujete se mezi nimi pohybovat, můžete kromě kliku na list využít iklávesové zkratky.

■Ctrl+PgDn – přesun olist doprava.

■Ctrl+PgUp – přesun olist doleva.

■Shift+F11 – přidání nového listu.

■Alt+Ů+Q+A – smazat list.

■Ctrl+klikem vybírat listy – označit více nesouvisejících listu klikem na příslušná ouška listů.

■Shift+klikem vybírat listy – označit více souvisejících listu vřadě za sebou.

Vybraný list lze zkopírovat (duplikovat) současným stiskem klávesy Ctrl apřesunutím označeného listu na pozici, kam chcete duplikovaný list umístit.

Přesunout nebo zkopírovat list lze též klikem pravého tlačítka myši na ouško listu avmenu vybrat "Přesunout nebo zkopírovat". Vybraný list pak jde také přesunout do jiného otevřeného sešitu nebo nového sešitu, ke zkopírování však dojde pouze při zatržení Vytvořit kopii.

Pokud je vybráno více listů, změny se provádějí ve všech listech současně!

Práce vlistu

Práci vlistu lze opět zrychlit využíváním klávesových zkratek:

■Ctrl+Mezerník – označit sloupec.

■Shift+Mezerník – označit řádek.

■Ctrl+Home – vstup do buňky A1.

■Ctrl+End – najít průsečík posledního vyplněného sloupce ařádku (pozor do úvahy se berou iformáty).

Spřidržením klávesy Shift přesunete označený sloupec (nebo řádek) na místo podle svého výběru. Spřidržením klávesy Shift+Ctrl zkopírujete na požadované místo.

Práce vtabulkách

Práci stabulkami si usnadníte klávesovými zkratkami:

■Ctrl+A – označit souvislou oblast dat (při práci stabulkou první stisk Ctrl+A označí tabulku, druhý stisk Ctrl+A označí celý list).

■Kurzorové klávesy – posun po buňkách (není-li stisknuta klávesa ScrLk).

■Ctrl+Kurzorové klávesy – přesun dle vybrané kurzorové klávesy (vpravo, vlevo, nahoru, dolů) osouvislou oblast buněk.

■Ctrl+Shift+Kurzorové klávesy – označování od aktivní buňky dál ve sloupci/vřádku souvislou oblast (podle vybrané šipky).

■Ctrl+D – voznačené oblasti sloupce dovyplní vzorce/hodnoty podle první buňky voznačené oblasti.

■Ctrl+R – voznačené oblasti řádku dovyplní vzorce/hodnoty podle první buňky voznačené oblasti.

Formátovat oblast dat jako tabulka (viz kapitola 1.3.10).

1.2.1Přejít na …

Potřebujete-li vstoupit do požadované buňky (definované oblasti, nebo Tabulky jako tabulka), například buňka AK47, můžete standardně využít menu Domů > Najít vybrat > Přejít na … . Nebo použijete klávesovou zkratku Ctrl+G. Vzobrazeném dialogovém okně si vyberete požadovanou buňku nebo oblast ze sekce Přejít na:, případně vtextovém boxu Odkaz: vepíšete přímo adresu požadované buňky (vukázce C4).

Obrázek 1.4: Přejít na konkrétní buňku

Pokud chcete vstoupit do buňky ve skrytém („superskrytém“) listu, nepřesunete se (zůstanete vpůvodní buňce).

1.2.2Přejít na – jinak …

Málo využívaná možnost, jak vybrat zoznačené oblasti požadované hodnoty kúpravě nebo kopírování, je využít možností zdialogového okna Přejít na – jinak … .

Zmenu Domů > Najít vybrat > Přejít na – jinak …, zobrazíte dialogové okno, zde jen zatrhnete, co potřebujete označit (vukázce Poznámky).

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

Podle potřeby lze zoblasti zobrazit například:

■Pouze viditelné buňky – vpřípadě kopírovaní vyfiltrovaných dat.

■Vzorce – hodí se při hledání chyb.

■Prázdné buňky.

■Aktuální matice – při práci smaticemi.

■Poznámky.

■Předchůdci.

■Následníci.

■Poslední buňka – průsečík posledního vyplněného řádku asloupce.

Uobjektů budou vybrány všechny, nejen ty nad vybranou oblastí.

Vložit

Zkopírovat data umíte Ctrl+C (potažmo Ctrl+X, když původní hodnoty chcete odstranit) aCtrl+V je vložíte. Když potřebujete vložit jinak, můžete využít menu Domů > Vložit (případně pravé tlačítko nad buňkou) azmenu vyberete, jakým způsobem je vložíte (např. pouze hodnoty, formáty, propojení).

Zajímavým způsobem vložení je například vložení Hodnoty (při kopírování zinternetu, zWordu se vloží jen text bez formátování), Formátování (vloží se pouze formát), Vložit jako obrázek (data se vloží jako obrázek) nebo Vložit jako propojený obrázek (vloží se obrázek, který se odkazuje na kopírovanou oblast, kdy vpřípadě změny vkopírovaných datech se automaticky změna propíše do obrázku).

Ctrl+Alt+V – na anglické klávesnici zobrazí dialogové okno Vložit jako… (na české klávesnici se takto vloží zavináč).

Obrázek 1.6: Vkládaní – možnosti vložení

Vložit jinak … – sekce Operace

Zmenu Domů > Vložit > Vložit jinak … máte možnost vzobrazeném dialogovém okně Vložit jinak vsekci Operace vybrat požadovanou operaci (například Přičíst), vtakovém případě se zkopírovaná hodnota aplikuje na označenou oblast aprovede se vybraná operace (hodnotám ve vybrané oblasti se přičte zkopírovaná hodnota).

Máte-li sloupec čísel, avšak některé hodnoty Excel vyhodnocuje jako text, je vaším cílem mít celý sloupec jako čísla (texty co vypadají jako číslo, převést na skutečné číslo). Elegantně můžete použít přípočet nuly, ato tak, že nulu vpomocné buňce označíte azkopírujte (Ctrl+C), následně označíte oblast, kterou chcete převést na čísla, apřes Domů > Vložit > Vložit jinak … vdialogovém okně Vložit jinak vyberete Přičíst aklik na OK.

Obrázek 1.7: Vložit jinak

Alternativně je možné uvýpočtu postupovat ipřidáním nového sloupce spříslušným vzorcem.

Transponovat

Vmenu Domů > Vložit > Vložit jinak … je také možné vzobrazeném dialogovém okně zaškrtnout možnost Transponovat.

Obrázek 1.8: Transponovat

Další možností, jak provést transponování, je použití funkce TRANSPOZICE (TRANSPOSE), nebo využití doplňku Power Query.

1.3Zjednodušení azpřehlednění tvorby vzorců

Tvorba složených vzorců je často zatížena nepřehledností, což komplikuje další práci se vzorci (doplňování, upravování, rozšiřování atd.). Vtéto kapitole se dozvíte, jak zápis vzorců zpřehlednit aušetřit si případná trápení.

1.3.1Oddělovače

Pro plnohodnotnou práci se vzorci je potřeba vědět, jak se ve vaší jazykové verzi Excelu zapisují případně zobrazují oddělovače:

■Desetinné oddělovače (desetinná tečka vs. desetinná čárka).

■Oddělovače tisíců (mezera vs. desetinná čárka).

■Oddělovače argumentů ve funkci (středník vs. desetinná čárka).

■Oddělovače vmaticích (co je řádkovým oddělovačem aco sloupcovým oddělovačem).

■Oddělovače pro datum (tečka vs. lomítko), (pořadí dne, měsíce aroku).

Zkontrolujte si, jak se zapisují jednotlivé oddělovače ve vaší verzi Excelu.

1.3.2Psaní vzorců – podbarvení

Při psaní vzorce se vložením odkazu do buňky daná buňka (oblast) podbarví. Po kliku na Enter se provede výpočet. Pokud vbuňce již vzorec je, můžete do buňky vstoupit přes klávesu F2, nebo dvojklikem na buňku, následně se opět podbarví buňky, které do vzorce vstupují.

Obrázek 1.9: Podbarvení vzorců

1.3.3Psaní vzorců – syntaxe

Někdy píšete vzorec, aniž se vám zobrazuje vbublinkové nápovědě jeho syntaxe (jaké má funkce argumenty). Pro zobrazení syntaxe, lze po dopsaní názvu funkce (např. =KDYŽ) využít klávesovou zkratku Ctrl+Shift+A, Excel přímo do vzorce dopíše argumenty funkce:

=KDYŽ(podmínka;ano;ne)

Takto lze vExcelu například zjistit, co je oddělovačem argumentů ufunkcí.

Pokud se vExcelu kontextová nápověda nezobrazuje, lze ji zapnout Soubor > Možnosti zmenu Usnadnění přístupu zatrhnout Zobrazovat popis funkcí (viz kapitola 1.6). Pomíjím možnost zobrazovat dialogová okna jednotlivých funkcí přes menu Vzorce sekce Knihovna funkcí.

Optické formátování vzorců

Vzorec vbuňce můžete opticky naformátovat doplněním mezer vsyntaxi vzorce (mezerník na klávesnici), nebo použitím klávesové zkratky Alt+Enter, který vytvoří vsyntaxi funkce nový řádek (tvrdý Enter). Nový řádek amezery vsyntaxi vzorce nemají na výpočet vliv, ale mohou funkci zpřehlednit.

Obrázek 1.10: Opticky naformátovaný vzorec

1.3.4Operátory

VExcelu se setkáte srůznými operátory (znaménky). Pojďme se na jednotlivé operátory podívat podrobněji.

Aritmetické operátory

Kprovádění matematických výpočtů jsou určeny aritmetické operace (sčítání, odčítání, násobení...). Vnásledující tabulce naleznete jejich seznam:

Operátor

Popis

Význam

Příklad

+

znaménko plus

sčítání

1+1

–

znaménko minus

odčítání

5–2

–

znaménko minus

záporné číslo

–1

*

hvězdička

násobení

2*2

/

lomítko

dělení

10/2

%

procento

procenta

10 %

^

„stříška“

umocnění

2^2

VExcelu, stejně jako vmatematice, má přednost umocňování aodmocňování před násobením adělením, ato má ještě přednost před sčítáním aodečítáním.

Unární operátor

Je operátor, který pracuje jen sjedním operandem, tj. ve výrazu 1 + (-2) je plus operátorem (binární) aminus vzávorce je unární operátor pro dvojku (jednička advojka jsou operandy). Využití nalézá primárně ufunkce SOUČIN.SKALÁRNÍ, například můžete unární operátor využít kpřevodu logické hodnoty PRAVDA (TRUE) na číslo.

=--PRAVDA >> 1

=-PRAVDA >> -1

Při matematických operacích se vdůsledku priorit vExcelu může unární operátor chovat záhadně, proto doporučuji používat závorky.

Relační operátory

Pro porovnávání dvou hodnot, kdy výsledkem je logická hodnota PRAVDA (TRUE) nebo NEPRAVDA (FALSE) se používají relační operátory.

Operátor

Popis

Význam

Příklad

=

znaménko rovná se

rovná se

A1=B1

>

znaménko větší než

větší než

A1>B1

<

znaménko menší než

menší než

A1<B1

>=

znaménko větší než arovná se

větší než nebo rovno

A1>=B1

<=

znaménko menší než arovná se

menší než nebo rovno

A1<=B1

<>

znaménko menší než, větší než

nerovná se

A1<>B1

Textový operátor

Pro spojení (zřetězení) jednoho nebo více textových řetězců slouží textový operátor &. Více se dozvíte vkapitole týkající se textových funkcí.

Operátor

Význam

Příklad

&

spojuje jednotlivé hodnoty do souvislé textové hodnoty

„Auto“&„mobil“

Příklad:

="Auto"&"mobil"

Nahrazovací operátory

Při vyhledávaní vtextu můžete použít zástupné znaky:

Znak

Význam

Příklad

?

zastupuje právě jeden znak

P?

*

zastupuje posloupnost znaků

P*

~

počítejte se speciálním znakem za tildou

~*

Odkazovací operátory

Pro označovaní oblastí je efektivní používat odkazovací operátory. Pro českou verzi Excelu platí:

Operátor

Popis

Význam

Příklad

:

dvojtečka

operátor oblasti – jeden odkaz na všechny buňky mezi dvěma odkazy včetně těchto odkazů

(B2:C5)

;

středník

operátor sjednocení – kombinuje více odkazů do jednoho odkazu (sjednocuje), vněkterých verzích je čárka

(B2:C4;C7)

mezera

operátor průniku – vytváří jeden odkaz na buňky společné dvěma odkazům (tj. průnik)

(B7:D7 C6:C8)

Použití odkazovacích operátorů je demonstrováno na následujícím obrázku.

Obrázek 1.11: Praktické ukázky použití operátoru

1.3.5Styl zobrazení A1 versus R1C1

Velkým pomocníkem při psaní vzorců je možnost přepnutí se ze stylu odkazování A1 do stylu R1C1.

Přepnutí zobrazení ze stylu A1 na R1C1

Změnu nastavení provedete vmenu Soubor > Možnosti vzobrazeném dialogovém okně, kde vyberete zmenu Vzorce azatrhnete R1C1. Sloupce jsou poté označeny čísly anikoliv písmeny (viz kap. 1.6.3.).

Obrázek 1.12: Styl A1 oproti R1C1

Jak vypadá zobrazení vzorce ve stylu A1 versus R1C1

Pojďte se podívat na rozdíl vzápisu mezi stylem A1 aR1C1. A1 představovat nemusím (první písmeno/písmena představují sloupec, číslo řádek, znak dolaru $ uzamyká sloupec nebo řádek).

Ustylu R1C1 písmeno R znamená řádek (Row), písmeno C znamená sloupec (Column), hranaté závorky [ ] znamenají, že jde orelativní odkaz (kladné číslo znamená požadovaný počet řádků/sloupců dolů/doprava, záporné číslo znamená požadovaný počet řádků/sloupců nahoru/doleva), samostatná čísla znamenají absolutní odkaz (například R3C2 znamená třetí řádek, druhý sloupec, neboli buňku $B$3).

Obrázek 1.13: Rozdíly odkazů A1, R1C1

Využitím VBA můžete pro toto přepínání vytvořit ikonu, případně si nadefinovat klávesovou zkratku (viz kapitola 1.5.4).

1.3.6Odkazy na více listů

Klasický odkaz na jiný list netřeba představovat, potřebujete-li hodnotu zbuňky A1, která se nachází na listu snázvem List01, máte množnost se odkázat ido tohoto listu, kdy odkaz obsahuje název listu, následuje vykřičník aona buňka:

=List01!A1

Pokud název listu obsahuje speciální znaky (mezery, plus, minus) je název listu vapostrofech:

=ˈList 01ˈ!A1

Výhodou je, že se můžete odkázat na více listů zároveň, předpokládejte, že máte listy pojmenovány 01 až 02 (měsíce), kdy vbuňce E5 na každém zlistu je váš měsíční výdělek. Určitě nebudete chtít sčítat hodnoty vbuňce E5 vjednotlivých listech:

=01!E5+02!E5+03!E5+04!E5+05!E5+06!E5+07!E5+08!E5+09!E5+10!E5+11!E5+12!E5

Což sice je funkční, ale nepřehledné, můžete využít efektivnější cestu, kdy sdělíte chci listy 01 až 12 asamozřejmě vložíte do funkce SUMA (SUM):

=SUMA(ˈ01:12ˈ!E5)

Obrázek 1.14: Odkaz na jiné listy

Označovat více listů lze využitím klávesy Shift pro listy pokračující za sebou.

1.3.7Definované názvy

Používáním definovaných názvů, například pro různé konstanty (kurzy, měny, fyzikální atechnické konstanty…), případně oblasti (pozice ve firmě, třídy, oddělení, kategorie...) si opět zpřehledníte práci funkcí, kdy se neodkazujete na neznámou oblast C10:C40, ale na název oblasti například PoziceFirma. Definované názvy lze použít kpojmenování buněk, případně icelých oblastí.

Pravidla pro vytvoření definovaných názvů

Pro definování názvů vExcelu existuje pár pravidel:

■Název nesmí obsahovat mezery.

■Název musí začínat písmenem, nebo podtržítkem (nesmí začínat číslem).

■Nelze používat speciální symboly (kromě podtržítka).

■Maximální délka názvu je 255 znaků.

■Lze použít ijedno písmeno (mimo C aR).

■Velikost písmen nerozhoduje (A=a).

Vytvořit Definovaný název svyužitím Pole názvů

Máte-li označenou buňku (oblast), které chcete přiřadit specifické jméno (definovaný název), klikněte do Pole názvu, zadejte požadovaný název apotvrďte Enter.

Obrázek 1.15: Definovaný název – Pole názvu

Použitím klávesové zkratky Alt+F3 se dostanete přímo do pole názvu.

Vytvořit Definovaný název využitím menu

Máte-li označenou buňku (oblast), které chcete přiřadit specifické jméno, existuje idalší způsob, jak zadat definovaný název, ato využít menu Vzorce > Definovat název, nebo klávesovou zkratku Ctrl+F3 aEnter, aby se zobrazilo dialogové okno Nový název. Vzobrazeném dialogovém okně zadejte Název: (musí respektovat pravidla pro vytvoření definovaných názvů), Obor: zvolte Sešit (definovaný název bude definován nad celým sešitem) aOdkaz na: (zde bude odkaz na buňku, kterou chcete mít takto pojmenovanou).

Obrázek 1.16: Definovaný název přes dialogové okno Nový název

Definovaný název souhrnně pro více buněk

Máte-li vtabulce vjednom sloupci názvy (například kurzů) ave druhém sloupci příslušné hodnoty (cena za nákup příslušné měny), můžete vytvořit automaticky definované názvy pro celý sloupec hodnot najednou.

Označte tabulku, kde vjednom sloupci již máte definované názvy avdruhém hodnoty. Pak přes menu Vzorce > Vytvořit zvýběru, nebo využitím klávesové zkratky Ctrl+Shift+F3 si zobrazíte dialogové okno Vytvořit názvy zvýběru. Pokud vdialogovém okně není nic zatrhnuto, vyberte, kde jsou umístěny texty pro definované názvy (vukázce levý sloupec). Hodnoty budou mít příslušné názvy zlevého sloupce (CZK_EUR, USD_CZK, ZLO_CZK).

Obrázek 1.17: Definované názvy zvýběru

Pokud Excel sám nic nezaškrtne, bude snejvětší pravděpodobností chyba voznačené oblasti (neobsahuje správné názvy nebo jsou označeny více než dva sloupce/řádky).

Pokud máte dobře zvolené definované názvy, svýhodou je využijete při tvorbě dynamických tabulek například přes funkci NEPŘIMÝ.ODKAZ (INDIRECT) (viz kapitola 7.1).

Odkazování na hodnoty zdefinovaného názvu na jednu buňku

Při tvorbě vzorců spoužitím definovaných názvů buněk stačí místo odkazu na buňku (=$C$3) použít definovaný název =CZK_to_EUR.

Větší výhodu spatříte, pokud se odkazujete na konstantu na jiném listu (=JinyList!$C$3), stále používáte definovaný název =CZK_to_EUR, tento definovaný název funguje jako absolutní odkaz, navíc se vám nemůže stát, že zapomenete buňku zamknout (vložit znaky $).

Odkazování na hodnoty zdefinovaného názvu na oblast

Při tvorbě vzorců spoužitím definovaných názvů nad oblastí potřebujete definovat, kterou položku zoblasti požadujete. Pro výběr položky zoblasti můžete využít funkci INDEX (INDEX). Pro oblast obsahující jeden sloupec využijete:

=INDEX(název_oblasti;pořadové_číslo_řádku)

Pro seznam čísel, kdy se oblast jmenuje SeznamCísel:

=INDEX(SeznamCísel;3)

Pro oblast zněkolika řádků asloupců musíte určit řádek asloupec:

=INDEX(název_oblasti;pořadové_číslo_řádku;_pořadové_číslo_sloupce)

Pro oblast snázvem TabulkaCisel, kdy potřebujete hodnotu zřádku 3 aze sloupce 6, musíte určit řádek asloupec, ztéto oblasti:

=INDEX(TabulkaCisel;3;2)

Obrázek 1.18: Odkazování se na definované názvy

Úprava definovaných názvů

Chcete-li definované názvy upravit, smazat, stačí spustit Správce názvu zmenu Vzorce > Správce názvů, nebo použít klávesovou zkratku Ctrl+F3. Kdispozici máte možnost přidat nový název (tlačítko Nový…). Upravit tlačítkem (Upravit) již existující název nebo vybraný definovaný název odstranit tlačítkem (Odstranit).

Máte-li vsešitě itabulky definované jako tabulka, klikem na Upravit nebo Odstranit, tyto tabulky neupravíte ani neodstraníte (viz kapitola 1.3.10).

Obrázek 1.19: Správce názvu – úpravy amazání

Při kopírování definovaného názvu vněkterých verzích Excelu nastane problém, kdy se definovaný název přidá jako nový definovaný název, který je ale omezen na zkopírovaný list. Při používaní definovaných názvu je následně potřeba dávat pozor, zda se odkazujete na definovaný název fungující vcelém listu, nebo na definovaný název, který je nad listem.

1.3.8Jak na tvorbu řad

Při práci stabulkami vám ušetří čas rychlé vytváření řad (ať už ve sloupci či vřádku). Řady nemusíte vytvářet ručně, ale můžete efektivně využít ifunkce. Pro výpočty je dobré umět tvořit číselné řady. Proto pár ukázek, které prakticky využijete:

■Hodnoty, které se vdalší buňce zvyšují ojedničku.

■Hodnoty sudá lichá.

■Posun o4 (roční období, čtvrtletí), 7 (5) pro dny vtýdnu (pracovní dny), 12 měsíce.

■Abeceda.

Jak vytvořit číselnou řadu

První možnost je zDomů > Vyplnit > Řady … vzobrazeném dialogovém okně vybrat:

■Co řady tvoří (Řádky, Sloupce).

■Typ.

■Velikost kroku.

■Konečná hodnota.

Druhá možnost je zapsat do buněk dvě čísla (například jedničku advojku), označit arozkopírovat. Při stisku Ctrl během kopírování se budou tato čísla opakovat, jinak se budou hodnoty zvětšovat orozdíl mezi těmito dvěma čísly.

Obrázek 1.20: Dialogové okno Řady

Třetí možnost je zapsat číslo, označit buňku apři rozkopírování pomocí myši držet klávesu Ctrl, hodnoty se budou zvětšovat ojedničku. Bez držení se bude opakovat stále stejné číslo.

Při zapsání názvů dnů, nebo názvů měsíců, funguje rozkopírování názvu jen pokud nedržíte klávesu Ctrl, při držení klávesy Ctrl se názvy nemění – opakuje se stále stejný název dne aměsíce.

Na funkci tvorby řady má vliv jazyk Windows, proto včeském Excelu nemusí fungovat české názvy měsíců, ale musíte zadat třeba anglické.

Chcete-li vlastní slovní řady, využijte seznamy (viz kap. 1.3.9.).

Vytvoření číselné řadu využitím funkce

Kzískaní hodnoty můžete využít funkci ŘÁDEK (ROW), případně od řádku odečíst požadované číslo, pokud nechcete začínat na prvním řádku apřesto chcete číslovat od jedničky.

=ŘÁDEK()

=ŘÁDEK()-2

Podobně můžete funkci ŘÁDEK (ROW) využít pro řady posunuté ox, kdy od aktuálního řádku odečtete řádek, na kterém stojí první funkce anásobíte krokem apřičtete jedničku.

=(ŘÁDEK()-ŘÁDEK($E$3))*4+1

=(ŘÁDEK()-ŘÁDEK($E$3))*7+1

Jak funguje?

Vnitřní funkce ŘÁDEK vypočte první hodnotu pořadového čísla řádku, zkteré chcete získat hodnotu vprvní buňce =({3}-{3})*4+1, ve druhé funkci spočte =({4}-{3})*4+1.

Pro tvorbu řad můžete využít inovou funkci SEQUENCE: =SEQUENCE(5;;1;4)

Obrázek 1.21: Číselná řada sfunkcí

1.3.9Vlastní seznamy

Vytvoření aodstranění vlastního seznamu. Přes pás karet Soubor – Možnosti zobdrženého dialogového okna Možnosti aplikace Excel zvolte položku vlevém menu Upřesnit aklikem na tlačítko Upravit vlastní seznamy... obdržíte dialogové okno Vlastní seznamy, ve kterém lze klikem na Přidat vytvořit vlastní seznam.

Obrázek 1.22: Vytvoření vlastních seznamů

Máte-li seznam vbuňkách, můžete jej označit aimportovat pomocí tlačítka Importovat.

Malá odbočka: Když již umíte nastavit vlastní seznamy, můžete je využít kseřazení. Zmenu Data > Seřadit otevřete dialogové okno. Vyberete, podle kterého sloupce budete řadit avPořadí zvolíte Vlastní seznam… .

Obrázek 1.23: Seřazení podle vlastního seznamu

1.3.10Tabulka jako tabulka

Dalším zjednodušením azpřehledněním práce vExcelu, je nadefinovat tabulky využitím tabulky jako tabulka. Po provedení vypadá tabulka „jen“ graficky naformátovaná sfiltry ařazením. Na druhý pohled jde odynamickou tabulku, která vaše výpočty zpřehlední azjednoduší.

Výhody tabulky jako tabulka:

■Při vložení/změně vzorce vjedné buňce se tento vzorec automaticky překopíruje/změní vcelém sloupci.

■Odkazy ve vzorcích nejsou ve stylu A1/R1C1, ale obsahují názvy sloupců, což podstatně zrychlí kontrolu.

Nevýhody tabulky jako tabulka:

■Komplikovanější zamknutí sloupce pro smíšené rozkopírování vzorce vřádku.

■Odstraněním tabulky jako tabulky zůstanou odkazy snázvem listu (což následně způsobí problémy při řazení).

Tvorba tabulky

Pokud máte aktivní buňku voblasti (tabulce) achcete ji naformátovat, můžete využít zpásu karet Domů > Tabulka jako tabulka > Vyberete si požadovaný design tabulky (nebo využít klávesovou zkratku Ctrl+T – jen design Excelu nastaví modrou barvu).

Vzobrazeném dialogovém okně Vytvořit tabulku zkontrolujte, zda odpovídá rozsah velikosti tabulky azda je zatrhnuto, že tabulka obsahuje záhlaví (předpokládám, že vaše tabulka záhlaví má). Klikem na OK se oblast naformátuje jako tabulka.

Snaformátováním tabulky se zároveň zobrazí dočasný pás karet Návrh tabulky. Zde si můžete nejen tabulku pojmenovat ➊, ale také přidat souhrny ➋, aktualizovat (Alt+F5) ➌ atd.

Obrázek 1.24: Návrh tabulky jako tabulka

Ukomplikovanějších tabulek můžete oblast tabulky, kterou chcete naformátovat jako tabulka, označit ručně. Při automatickém označování může Excel do oblasti zahrnou ořádek/sloupec navíc, který do tabulky jako tabulka zahrnout nechcete.

Odkazování vtabulce jako tabulka

Vtabulce jako tabulka je vhodné umět se odkazovat. Mějte tabulku jako tabulka pojmenovanou TabulkaOsoby, která bude obsahovat sloupce:

■Id.

■Jméno.

■Věk.

Data vtabulce jako tabulka – pokud se chcete odkázat na celou tabulku bez záhlaví využijte:

=TabulkaOsoby

Odkázat se na celou tabulku včetně záhlaví:

=TabulkaOsoby[#Vše]

Odkázat se na řádek záhlaví:

=TabulkaOsoby[#Záhlaví]

Odkázat se na položku vzáhlaví:

=TabulkaOsoby[[#Záhlaví];[Jméno]]

Rozsah vzáhlaví:

=TabulkaOsoby[[#Záhlaví];[Příjmení]:[Věk]]

Položka dat vřádku, pokud jste vdané tabulce:

=[@Jméno]

Položka dat vřádku, pokud jste vdané tabulce achcete uvést její jméno:

=TabulkaOsoby[@Jméno]

Rozsah sloupců:

=SUMA(Osoby[@[Plat1]:[Plat12]])

Zamknutí sloupce pro smíšené odkazování:

=TabulkaOsoby[@Věk]:TabulkaOsoby[@Věk]

Vněkterých verzích postačí:

=TabulkaOsoby[[@Věk]:[@Věk]]

=[@[@Věk]:[@Věk]]

Obrázek 1.25: Odkazování vtabulce jako tabulka

Ve verzi Excel 2007 bylo mírně odlišné odkazování

=TabulkaOsoby[#Tento řádek],[@Jméno]],

pro výpočet

=SUMA(TabulkaOsoby[#Tento řádek],[Plat1]:[Plat12]])

pro zamknutí sloupce

=TabulkaOsoby[#Tento řádek],[@Věk]:[@Věk]]

Vnovější verzi Excelu se může pokročilejší odkazování vrámci tabulky jako tabulka mírně změnit.

„Zrušení“ tabulky jako tabulka – převedení na oblast

Tabulka jako tabulka se dá převést na oblast („klasickou“ tabulku). Vpásu karet Návrh tabulky > Převést na oblast (převést na rozsah ve starších verzích Excelu).

Obrázek 1.26: Tabulky jako tabulka – převést na oblast

Případně přes pravý klik aTabulka > Převést na oblast.

Obrázek 1.27: Tabulky jako tabulka – převést na oblast

Neodstraňujte tabulku jako tabulka, pokud obsahuje výpočtové sloupce. Převedením vzniknou ve vzorcích odkazy snázvy na list, ve kterém se buňka nachází. Budete-li data následně řadit, dojde krozhození záznamu. Excel pak neřadí správně!

1.3.11Závislosti vzorců

Vsekci Závislostí vzorců na pásu karet Vzorce jsou ikony, které vám pomohou sorientací ve vašich vzorcích.

Obrázek 1.28: Pás karet Vzorce, sekce Závislostí vzorců

Předchůdci anásledníci

Vtéto sekci máte na výběr tři ikony:

■Předchůdci.

■Následníci.

■Odebrat šipky.

Předchůdci: Volba Předchůdci zobrazí, zkterých buněk zvolený vzorec čerpá parametry (data). Bude-li buňka čerpat data zjiného listu, lze dvojklikem na šipku zobrazit dialogové okno Přejít na. Vtomto dialogovém okně se zobrazí veškeré odkazy vstupující zjiných listů do vybrané buňky.

Následníci: Volba Následníci zobrazí, do kterých buněk vybraná buňka (vzorec) výpočet posílá. Podobně jako upředchůdců lze dvojklikem zobrazit dialogové okno Přejít na. Vtomto okně se zobrazí, kam odkazy směřují.

Odebrat šipky: Odebere zobrazené šipky.

Obrázek 1.29: Předchůdci anásledníci

Nezobrazuje-li se dialogové okno Přejít na dvojklikem myší na šipku, stiskněte navíc Ctrl.

Zobrazit vzorce

Další možností zpřehlednění při práci se vzorci je možnost zobrazit vzorce zmenu Vzorce > Zobrazit vzorce nebo pro zobrazení využít klávesovou zkratku (Ctrl+,).

Obrázek 1.30: Závislosti vzorců

Vzorce můžete mít zobrazeny včetně závislosti.

Kukátko

Kukátko pomůže skontrolou výpočtu přes více listů, zpásu karet Vzorce > Kukátko obdržíte dialogové okno Okno kukátka. Klikem na ikonu Přidat kukátko… se zobrazí dialogové okno Přidat kukátko. Zde vyberete buňku(y), které chcete sledovat. Klikem na Přidat se objeví vdialogovém okně kukátka.

Obrázek 1.31: Přidat kukátko

Po vyplnění se vOkně kukátka zobrazí údaje ovybrané buňce:

■Sešit – ve kterém sešitu se nachází.

■List – na kterém listě se buňka nachází.

■Název – pokud má buňka definovaný název.

■Buňka – adresa buňky (A1).

■Hodnota – aktuální hodnota vbuňce.

■Vzorec – pokud je vbuňce vzorec, jinak bez údaje.

Pro odstranění sledované buňky vyberte, kterou sledovanou buňku chcete zpřehledu odstranit. Pak jen klikněte na Odstranit kukátko amáte odstraněno.

Obrázek 1.32: Okno kukátka svyplněnými odkazy na buňky

1.3.12Rozdělit

Pro snadnější orientaci ve velké tabulce můžete prostor sešitu rozdělit. Využijte zpásu karet Zobrazení > Rozdělit. Rozdělit můžete Excel svisle, vodorovně nebo do kříže, podle umístění buňky. Opětovným klikem na ikonu Rozdělit, rozdělení odstraníte.

Obrázek 1.33: Rozdělit

1.4Hledání chyb ve vzorcích ajejich opravy

Při tvorbě vzorců dochází kchybám. Začnete-li vytvářet komplikovanější vzorce je vhodné chybám předejít aseznámit se snejčastějšími problémy, které vás mohou při tvorbě vzorců potkat. Vtéto kapitole se naučíte způsoby, jak chyby hledat aopravovat.

Příklady ktéto kapitole naleznete vsešitu 01_04 Chyby Vzorce.xlsx.

1.4.1Přepočet sešitu, listu, části vzorce – F9

Jako první možnost doporučuji využít klávesu F9. Kliknutím dojde kpřepočtu listu (pokud není propočet nastaven na automaticky, více onastavení vkapitole 1.6.3). Další méně známé využití této klávesy jsou dílčí výpočty ve složených vzorcích. Ve vzorci označte dílčí výpočet (argument ve funkci) astiskněte F9, tímto se označený argument (výpočet) vypočte. Vukázkovém příkladu označte ve funkci MAX argument, neboli B3:B8 astiskem F9 dojde kzobrazení {1;2;3;4;5;6}, následně vyberte celou funkci např. MIN(B3:B8) aopět klikněte na F9. Získáte číslo 1, neboli minimální číslo voznačené oblasti, podobně můžete vyzkoušet pro funkci MAX.

Ukázka, jak můžete postupně vypočítat dílčí části funkce:

=PRŮMĚR(MAX(B3:B8);MIN(B3:B8))

=PRŮMĚR(MAX({1;2;3;4;5;6});MIN({1;2;3;4;5;6}))

=PRŮMĚR(6;1)

Obrázek 1.34: Klávesa F9 – dílčí výpočty vzorce

Pokud máte označenou matici anad tou provedete dílčí výpočet, můžete získat informaci otom, jak se ve vašem Excelu zapisuje maticový výpočet (viz kapitola 9.1.2).

1.4.2Názvy funkcí aznaky při tvorbě vzorců

Při psaní vzorců, musíte mít na paměti odlišnosti:

Jazyková verze Excel

Názvy funkcí se mění podle nastavení jazykové verze Excelu (včeštině, angličtině, němčině…). Název podmínkové funkce je včeské verzi KDYŽ, vanglické případně IF.

Tečka, čárka, mezera jako oddělovač desetinných míst aoddělovače tisíců

Pro desetinná čísla se včeských verzích využívá čárka, vanglických tečka. Naproti tomu oddělovač tisíců je včeské verzi mezera aanglické tečka. Při kopírování může vzniknout chyba, kdy se desetinné číslo překopíruje jako datum.

Zápis funkcí čárka středník jako oddělovač argumentu

Když už správně použijete název funkce, může vás potkat ještě jedna záludnost, zda se argumenty oddělují středníkem nebo čárkou (nad českým Windows nejčastěji středníkem, nad anglickým čárkou).

Další komplikace nastává umaticových funkcí viz kapitola 9 omaticích amaticových vzorcích.

Neexistující funkce

Stejně jako chybu způsobí neexistující název funkce, může být problém název funkce zjiné jazykové verze, nebo název funkce zvyšší verze Excelu (vtéto knize je zmíněno pár funkcí, které jsou kdispozici vnových verzích Excelu, ale ve vaší ještě nemusí být kdispozici).

Abecední seznam názvů funkcí vCZ aodpovídajících funkcí vEN anaopak najdete vpřílohách na www.grada.cz, na stránce této knihy vsekci „Ke stažení“.

1.4.3Obsah buněk

Mezery aprázdná buňka

Mezery (tvrdé mezery) mohou vbuňkách nebo vdatech způsobovat komplikace (na konci textu je nevidíte). Vukázkách je mezera označena znakem .

■Pavel ≠ Pavel – Pavel smezerou není roven hodnotě Pavel bez mezery, podobné případy mezera včísle aoddělovač tisíců mezerou jako formát.

■Mezera vbuňce – případně ‘ apostrof – buňka není prázdná, ať se tak na první pohled může jevit.

■Výsledek buňky prázdná hodnota, nebo mezera =KDYŽ(1=1;"";"").

■Bílý text na bílém pozadí – opět na první pohled prázdná buňka, opak je pravdou.

Prázdné buňky můžete odhalit využitím funkce JE.PRAZDNÉ (ISBLANK) nebo označit oblasti, které chcete zjistit azmenu Domu > Najít avybrat > Přejít na jinak > Prázdné.

Obrázek 1.35: Mezery aprázdné buňky

Oříškem může být tvrdá mezera, kterou využívají některé databázové systémy pro export dat (na první pohled mezera, na ten druhý je užší než klasická mezera, Excel ji bere jako jiný znak) zapíše se přes Alt+0160).

Text versus číslo

1 není 1, komplikace je vtypu. Stejná jednička může být číslem (lze přidat desetinná místa) nebo textem (nelze přidat desetinná místa). Nejen ve vyhledávacích funkcích jde ojiné znaky.

Velká malá písmena

Některé funkce DOSADIT (SUBSTITUTE), STEJNÉ (EXACT) respektují velká amalá písmena. Stejně tak inástroj Power Query[1].

Formáty čísla jako datum ačas

Další komplikaci způsobuje datum ačas. Datum ačas je pro Excel číslo, které má jen formát datumu ačasu.

Obrázek 1.36: Formáty ačíslo pro datum ačas

1.4.4Vlastní formát – nevidíte skutečnou hodnotu

Aby toho nebylo málo, další komplikace může způsobit formát hodnoty. Hlavně pokud využíváte vlastní formát. Vbuňce můžete mít na první pohled zobrazenou správnou hodnotu, ale ve skutečnosti je hodnota špatná, vlastní formát vám ji přepsal (samozřejmě iopačně, kdy vbuňce na první pohled je chyba, ale jen díky vlastnímu formátu).

Obrázek 1.37: Vlastní formát

Nastavení – změna vlastního formátu

Pro nastavení vlastního formátu. Označte oblast, které chcete nastavit vlastní formát. Pravé tlačítko myši, nebo klávesová zkratka Ctrl+1, nebo pás karet Domů avsekci Číslo > Formát čísla. Vzobrazeném dialogovém okně Formát buněk si případně ještě vyberte záložku Číslo (pokud nebude vybráno). VDruh: vyberete Vlastní amůžete nastavovat:

Obrázek 1.38: Dialogové okno Formát buněk

Základní syntaxe pro vlastní formát

Ve vlastním formátu se používají základní symboly aznaky, každý má svůj specifický význam:

@zástupný symbol textu vbuňce @,

0zástupný symbol čísla vbuňce – zobrazuje inuly (lze kombinovat 0,00 nebo 000,0 atd.),

#zástupný symbol čísla vbuňce – nezobrazuje nuly (# ### ##0),

%přidá procenta ahodnotu vynásobí 100,

$symbol měny (podobně jako Euro, lze zapsat přímo),

*musí být doplněna dalším znakem, který vyplní zbytek buňky,

,desetinný oddělovač (pro anglickou verzi je to tečka),

"" pokud obsahuje text tento symbol bude zobrazen (splňuje-li kritéria),

.tečka oddělení dnů.měsíců.roků (pokud nejde odatum musí se doplnit uvozovkami "."),

:dvojtečka oddělení hodin:minut (pokud nejde ohodiny musí se doplnit uvozovkami ":"),

;oddělení sekcí/polí kladné číslo; záporné číslo; nula; text (viz dále),

?otazník,

\zpětné lomítko zobrazí následující znak (\@), aby se daly zobrazit iznaky co mají specifickou vlastnost,

[]hranaté závorky (například pro zobrazení celkového počtu hodin [h])

_ dolní podtržítko – mezera,

Všeobecný některé verze Excelu píší místo Všeobecný „š“ jako Vęeobecný „ę“ vangličtině General,

d,m,r(y),h,s pro nastavení formátu datum ačas,

Rozklad ve vlastním formátu

■Kladné.

■Záporné.

■Nula.

■Text.

Pro barvy ve vlastním formátu

Textově:

• Barva2 bíla

Jednotky

Slovní náhrady čísel

Vlastní formát akrychlové ačtvereční metry – m^2 m^3

Datum ve vlastním formátu

1.4.5Chyby ve funkcích

Logické chyby

Zdánlivé chyby

Druhy chybových hodnot

1.4.6Chybné řazení uodstraněné tabulky jako tabulka

1.4.7Nepřesnost výpočtů – počet desetinných míst

1.4.8Jak nahradit odkaz na jiný sešit

1.4.9Maticový zápis

1.5Vlastní funkce amakro vExcelu VBA

1.5.1Vytvoření makra avlastní VBA funkce

Makro pro přepínaní mezi A1 aR1C1 stylem zobrazení

Funkce

1.5.2Spuštění anastavení VBA

1.5.3Vložení makra avlastní funkce do VBA

1.5.4Použití makra

Klávesová zkratka pro makro na změnu stylu

1.5.5Použití vlastní funkce

1.6Nastavení aplikace Excel

1.6.1Nejhledanější nastavení vAplikaci Excel

1.6.2Obecné

1.6.3Vzorce

1.6.4Data

1.6.5Kontrola mluvnice apravopisu

1.6.6Ukládání

1.6.7Jazyk

1.6.8Usnadnění přístupů

1.6.9Upřesnit

1.6.10Přizpůsobit pás karet

1.6.11Panel nástrojů Rychlý přístup

1.6.12Doplňky

Zobrazit Analytické nástroje

Power Query aPower Pivot

1.6.13Centrum zabezpečení

Table of Contents

Úvodní slovo

1Zopakování základů

1.1Klávesnice a klávesové zkratky

1.1.1Klávesové zkratky

1.1.2Znaky na české klávesnici s využitím klávesy AltGr

1.1.3Zamknutí posunu listu – ScrLk

1.1.4Ovládaní Excelu s využitím klávesy Alt

1.2Práce s listy a tabulkami

Práce s listy

Práce v listu

Práce v tabulkách

1.2.1Přejít na …

1.2.2Přejít na – jinak …

Vložit

Vložit jinak … – sekce Operace

Transponovat

1.3Zjednodušení a zpřehlednění tvorby vzorců

1.3.1Oddělovače

1.3.2Psaní vzorců – podbarvení

1.3.3Psaní vzorců – syntaxe

Optické formátování vzorců

1.3.4Operátory

Aritmetické operátory

Unární operátor

Relační operátory

Textový operátor

Nahrazovací operátory

Odkazovací operátory

1.3.5Styl zobrazení A1 versus R1C1

Přepnutí zobrazení ze stylu A1 na R1C1

Jak vypadá zobrazení vzorce ve stylu A1 versus R1C1

1.3.6Odkazy na více listů

1.3.7Definované názvy

Pravidla pro vytvoření definovaných názvů

Vytvořit Definovaný název s využitím Pole názvů

Vytvořit Definovaný název využitím menu

Definovaný název souhrnně pro více buněk

Odkazování na hodnoty z definovaného názvu na jednu buňku

Odkazování na hodnoty z definovaného názvu na oblast

Úprava definovaných názvů

1.3.8Jak na tvorbu řad

Jak vytvořit číselnou řadu

Vytvoření číselné řadu využitím funkce

1.3.9Vlastní seznamy

1.3.10Tabulka jako tabulka

Tvorba tabulky

Odkazování v tabulce jako tabulka

„Zrušení“ tabulky jako tabulka – převedení na oblast

1.3.11Závislosti vzorců

Předchůdci a následníci

Zobrazit vzorce

Kukátko

1.3.12Rozdělit

1.4Hledání chyb ve vzorcích a jejich opravy

1.4.1Přepočet sešitu, listu, části vzorce – F9

1.4.2Názvy funkcí a znaky při tvorbě vzorců

Jazyková verze Excel

Tečka, čárka, mezera jako oddělovač desetinných míst a oddělovače tisíců

Zápis funkcí čárka středník jako oddělovač argumentu

Neexistující funkce

1.4.3Obsah buněk

Mezery a prázdná buňka

Text versus číslo

Velká malá písmena

Formáty čísla jako datum a čas

1.4.4Vlastní formát – nevidíte skutečnou hodnotu

Nastavení – změna vlastního formátu

Základní syntaxe pro vlastní formát

Rozklad ve vlastním formátu

Pro barvy ve vlastním formátu

Jednotky

Slovní náhrady čísel

Vlastní formát a krychlové a čtvereční metry – m2 m3

Datum ve vlastním formátu

1.4.5Chyby ve funkcích

Logické chyby

Zdánlivé chyby

Druhy chybových hodnot

1.4.6Chybné řazení u odstraněné tabulky jako tabulka

1.4.7Nepřesnost výpočtů – počet desetinných míst

1.4.8Jak nahradit odkaz na jiný sešit

1.4.9Maticový zápis

1.5Vlastní funkce a makro v Excelu VBA

1.5.1Vytvoření makra a vlastní VBA funkce

Makro pro přepínaní mezi A1 a R1C1 stylem zobrazení

Funkce

1.5.2Spuštění a nastavení VBA

1.5.3Vložení makra a vlastní funkce do VBA

1.5.4Použití makra

Klávesová zkratka pro makro na změnu stylu

1.5.5Použití vlastní funkce

1.6Nastavení aplikace Excel

1.6.1Nejhledanější nastavení v Aplikaci Excel

1.6.2Obecné

1.6.3Vzorce

1.6.4Data

1.6.5Kontrola mluvnice a pravopisu

1.6.6Ukládání

1.6.7Jazyk

1.6.8Usnadnění přístupů

1.6.9Upřesnit

1.6.10Přizpůsobit pás karet

1.6.11Panel nástrojů Rychlý přístup

1.6.12Doplňky

Zobrazit Analytické nástroje

Power Query a Power Pivot

1.6.13Centrum zabezpečení