Jak si ješte více zamilovat Excel - mameradiexcel.cz¡t... · PROHLÁŠENÍ: Tento materiál je...

www.lenkamiksatkova.cz © 2016

JAK SI JEŠTĚ VÍCE ZAMILOVAT EXCEL

aneb 12 tipů pro mírně pokročilé, díky kterým ušetříte hodiny času

Lenka Mikšátková

http://www.lenkamiksatkova.cz/

PROHLÁŠENÍ: Tento materiál je informačním

produktem. Vznikl na základě mých pracovních

zkušeností a stál mě mnoho úsilí. Respektujte, proto

prosím, autorský zákon a nešiřte jeho části, ani celek,

bez mého souhlasu. Stažením tohoto materiálu

zároveň berete na vědomí, že jako autorka nenesu

žádnou zodpovědnost za jakékoli použití informací z

tohoto e-booku a bonusů k němu, ani za žádné

úspěchy či neúspěchy, které z něj vyplynou. Děkuji

vám za pochopení a respektování tohoto sdělení.

Pokud budete chtít tento e-book komukoliv doporučit,

budu moc ráda, pokud ho pošlete na moje stránky

www.lenkamiksatkova.cz . Děkuji



OBSAH

NA ÚVOD 1 JAK PRACOVAT S TÍMTO E-BOOKEM 2 PODMÍNĚNÉ FORMÁTOVÁNÍ 3

PRAVIDLA ZVÝRAZNĚNÍ BUNĚK 4

PRAVIDLA PRO NEJVYŠŠÍ ČI NEJNIŽŠÍ HODNOTY 7

DATOVÉ PRUHY 8

BAREVNÉ ŠKÁLY 11

SADY IKON 13

NOVÉ PRAVIDLO 17

VYMAZAT PRAVIDLA 17

SPRAVOVAT PRAVIDLA 18

VLOŽENÍ TABULKY 19 VLOŽENÍ KOMENTÁŘE 22 LOGICKÉ FUNKCE 26

KDYŽ 26

IFS 28

A 31

NEBO 33

IFERROR 35

VYHLEDÁVACÍ FUNKCE 36

SVYHLEDAT 36

VVYHLEDAT 43

POZVYHLEDAT 45

MATEMATICKÉ FUNKCE 47

ZAOKROUHLIT 47

ABS 49

DATUM A ČAS 51

DNES 51

MĚSÍC 52

DATEDIF 53

NETWORKDAYS 54

TEXTOVÉ FUNKCE 56

ZLEVA 56

ZPRAVA 58

ČÁST 60

HLEDAT 62

CONCATENATE 64

CONCAT 66

TEXTJOIN 68

ŘAZENÍ 70 FILTROVÁNÍ 72

AUTOMATICKÝ FILTR 72

SUBTOTAL 75

SOUHRNY, SESKUPENÍ A ODDĚLENÍ 78

SOUHRNY 78

SESKUPENÍ A ODDĚLENÍ 81

ÚVOD DO KONTINGENČNÍCH TABULEK 83 ZÁVĚREM 88


Jak si ještě více zamilovat Excel Lenka Mikšátková

1


NA ÚVOD

Dnes miluji práci v Excelu. Pokaždé, když tvořím nový report, si užívám

vymýšlení kombinací různých funkcí a nejraději mám moment, když vše

dodělám a ono to funguje! Když vím, že až příště bude můj klient report

potřebovat, jen vyplní vstupní data a zobrazí se přesně ty údaje, které

potřebuje znát.

Než jsem se ale naučila vše, co znám dnes (a to určitě není úplně všechno,

co Excel umí, je potřeba stále se učit ), často jsem propadala skepsi.

Pokaždé, když jsem nastoupila do nové práce, cítila jsem se k ničemu. Jako

naprostý začátečník, který vůbec neví, která bije. Trávila jsem hodiny a

hodiny tím, abych zjistila, k čemu jaká funkce slouží, jak fungují jednotlivé

nástroje, vše mi trvalo dlouho a měla jsem pocit, že za celý den jsem ani nic

pořádně neudělala.

A proto jsem napsala tento e-book. Abyste nemuseli zbytečně ztrácet

drahocenné hodiny vašeho času a mohli je věnovat něčemu užitečnějšímu.

Uvádím zde nástroje a funkce, které v Excelu považuji za nejužitečnější a

které jsou zároveň vhodné pro mírně pokročilé uživatele.

Věřím, že v tomto e-booku najdete spoustu zajímavých informací, které

vám pomůžou ve vaší každodenní práci a že si ještě více zamilujete Excel

Již mnoho let je mou velkou vášní Excel. Naplňuje mě

ukazovat ostatním lidem jak ho zvládnout, aby jim

usnadňoval práci a šetřil jim čas.

Lenka Mikšátková



2


JAK PRACOVAT S TÍMTO E-BOOKEM

Tento e-book je rozdělen do 12 kapitol podle jednotlivých témat. V každé kapitole najdete základní návod,

k čemu daný nástroj nebo funkce slouží a také základní příklad. Všechny tyto základní příklady najdete

v bonusu k tomuto e-booku – ve cvičebním souboru v Excelu, který jste si také stáhli. Tyto základní příklady

jsou vždy na samostatném listu a pojmenovány podle názvu kapitoly nebo podkapitoly doplněné informací

základní příklad. Při otevření Excelového souboru je nutné povolit makra, jinak byste neviděli některé ukázky.

E-book je připraven pro verzi Excel 2016, ve starších verzích je ale vše velmi podobné (jen některé z nových

funkcí 2016, které v tomto e-booku zmiňuji, ale bohužel prozatím nevyužijete). Pokud by vám však přesto bylo

cokoliv nejasného, neváhejte mi napsat, ráda vám pomůžu.


http://lenkamiksatkova.cz/kontakt/


3


PODMÍNĚNÉ FORMÁTOVÁNÍ

Podmíněné formátování je úžasný nástroj, který vám zpřehlední vaše soubory. Jedná se o to, že se např. buňky, které jsou menší než nula, zbarví červeně. Můžete si pro vybrané buňky nastavit jakýkoliv formát, ikonu nebo třeba barevný pruh, když splní nějakou podmínku. Pojďme si rovnou říct, jaké všechny možnosti máme. Podmíněné formátování nalezneme v nabídce Domů –Podmíněné formátování.



4


PRAVIDLA ZVÝRAZNĚNÍ BUNĚK

V této nabídce můžete vybrat formát pro buňky, které jsou větší nebo naopak menší než jiná buňka, mezi dvěma hodnotami, rovnají se určitému číslu, obsahují nějaký text nebo datum, nebo např. duplicitní hodnoty (zvýraznění duplicitních hodnot se v dlouhém seznamu obzvlášť hodí ;-))

Základní příklad 1: Chceme zvýraznit všechny buňky, které jsou větší než 0 šedou výplní a zeleným textem.

Označíme si buňky, kterých se má podmíněné formátování týkat, vybereme nabídku Domů-Podmíněné formátování – Pravidla zvýraznění buněk - >Větší než

Otevře se formulář pro zadání naší podmínky. Do pole „Formátovat buňky s hodnotou VĚTŠÍ NEŽ: zadáme 0 a rolovacím seznamu vedle si buď zvolíme některou přednastavenou možnost, nebo můžeme dole vybrat Vlastní formát a vybrat si jinou barvu, která se nám líbí. Naše volba (šedá výplň a zelený text) přednastavené není a proto zvolíme Vlastní formát.



5


Otevře se nám standardní nabídka formátování buněk. Můžeme si zde zvolit formát čísla, barvu písma, ohraničení i výplň. Nejdříve tedy vybereme kartu Písmo a zvolíme zelenou barvu.



6


Nyní přejdeme na kartu Výplň a zvolíme šedou barvu. Odsouhlasíme OK a čísla se nám krásně obarví podle našich požadavků. Není to nádhera?



7


PRAVIDLA PRO NEJVYŠŠÍ ČI NEJNIŽŠÍ HODNOTY

Tato pravidla nám umožňují formátovat buňky zase z jiného úhlu pohledu. Přednastavené možnosti jsou formátování prvních 10 položek, prvních 10 %, posledních 10 položek, posledních 10 %, buňky, kterou jsou nad průměrem anebo naopak pod průměrem vybrané oblasti.

ZÁKLADNÍ PŘÍKLAD 2: CHCI ZVÝRAZNIT ČERVENOU BARVOU JEN TA ČÍSLA, KTERÁ JSOU VĚTŠÍ NEŽ PRŮMĚR

Z VYBRANÉ OBLASTI.

Vybereme si myší oblast, kterou chceme formátovat, opět půjdeme na kartu Domů-Podmíněné formátování, tentokrát zvolíme nabídku Pravidla pro nejvyšší či nejnižší hodnoty a vybereme Nad průměrem.



8


Z rozbalovacího seznamu vybereme styl formátování (nebo si opět můžeme nastavit vlastní formát), odsouhlasíme a je to

DATOVÉ PRUHY

Dále pomocí podmíněného formátování můžeme v buňkách zobrazit tzv. Datové pruhy, které porovnají hodnoty ve všech buňkách ve výběru a každé buňce přiřadí tak dlouhý pruh, jaká je hodnota buňky ve vztahu k těm ostatním.

ZÁKLADNÍ PŘÍKLAD: CHCEME JEDNOTLIVÝM HODNOTÁM PŘIŘADIT POMOCÍ PODMÍNĚNÉHO

FORMÁTOVÁNÍ DATOVÉ PRUHY.

Vybereme oblast buněk, které chceme přiřadit datové pruhy, potom na kartě Domů vybereme Podmíněné formátování - Datové pruhy a zvolíme výplň, která se nám líbí.



9


Pokud se vám žádná výplň nelíbí, zvolte možnost Další pravidla, kde si můžete zvolit vlastní barvy, ohraničení a také např., že chcete zobrazit pouze datové pruhy bez uvedení hodnot (možnost Zobrazit pouze čáru).



10




11


BAREVNÉ ŠKÁLY

Podobně jako datové pruhy fungují i Barevné škály. Rozdíl je v tom, že v buňce nezobrazují různě dlouhé pruhy, ale různé barvy, podle toho, jak si my určíme.

ZÁKLADNÍ PŘÍKLAD: CHCEME JEDNOTLIVÝM HODNOTÁM PŘIŘADIT POMOCÍ PODMÍNĚNÉHO

FORMÁTOVÁNÍ BAREVNÉ ŠKÁLY.

Postupovat budeme téměř stejně jako v případě datových pruhů. Nejdříve vybereme buňky, které chceme formátovat, poté půjdeme na kartu Domů-Podmíněné formátování, ale tentokrát zvolíme Barevné škály. Opět si zvolíme styl, který se nám líbí a máme to



12


Pokud si z nabídky škál nevyberete, můžete si samozřejmě navolit svoje vlastní škály. Stačí, když místo volby nějaké výplně zvolíte možnosti Další pravidla. Zde si můžete vybrat jiný styl formátování, typ hodnot, na kterých je formátování založené, navolíte si svoje barvy atd.



13


SADY IKON

U podmíněného formátování máme ještě k dispozici (moje nejoblíbenější ) Sady ikon. V buňkách se nám potom zobrazují např. barevné semafory, šipky apod., opět podle toho, jakou ikonu a barvu přiřadíme.

ZÁKLADNÍ PŘÍKLAD: MÁME 10 PROJEKTŮ V RŮZNÉ FÁZI ROZPRACOVANOSTI. CHCEME POMOCÍ IKON

ZOBRAZIT, V JAKÉ FÁZI ROZPRACOVANOSTI SE NACHÁZÍ JAKÝ PROJEKT. POKUD JE ROZPRACOVANOST

MENŠÍ NEŽ 50 %, ZOBRAZÍ SE ČERVENÝ PRAPOREK, POKUD MEZI 50 % - 90 % JE TO ŽLUTÝ PRAPOREK A

POKUD JE PROJEKT JIŽ DOKONČENÝ NA VÍCE NEŽ 90 %, UVIDÍME PRAPOREK ZELENÝ.

Nejdříve tedy vybereme oblast buněk, půjdeme na kartu Domů-Podmíněné formátování, zvolíme možnost Sady ikon

a zvolíme ikony, které se nám líbí. My chceme pro tentokrát barevné praporky. Vidíme, že se praporky přiřadily, ale

podle jiných hodnot, než chceme my. Musíme si tedy zobrazení upravit.



14




15


Na kartě Domů-Podmíněné formátování zvolíme možnost Spravovat pravidla.

Dále vybereme Upravit pravidlo a po otevření okna napíšeme, pro jaké hodnoty

chceme zobrazit jakou barvu praporku. U zelené tedy doplníme hodnotu 90 a u

žluté 50. Potvrdíme a máme to přesně tak, jak jsme chtěli



16




17


NOVÉ PRAVIDLO

Pod touto nabídkou si můžete definovat svoje vlastní pravidlo ze všech možností, které Excel nabízí. Pokud tedy přesně nevíte, kde možnost, kterou potřebujete najít, bude určitě tady

VYMAZAT PRAVIDLA

Zde si můžeme zvolit, že

chcete smazat

podmíněné formátování

jen z nějakých buněk,

z celého listu nebo

případně z tabulky nebo

kontingenční tabulky.



18


SPRAVOVAT PRAVIDLA

Pod nabídkou Spravovat pravidla najdeme všechna pravidla, která se týkají buňky, na které stojíme (aktuální výběr) nebo si můžeme z rolovacího seznamu vybrat jinou oblast, nějaký list nebo celý sešit se kterým pracujeme. Zde si můžeme všechna pravidla nejen zobrazit, ale i upravit nebo smazat.



19


VLOŽENÍ TABULKY

Znáte možnost vložení tabulky? Ne? Tak to o hodně přicházíte! Vložení tabulky Vám dokáže ušetřit spoustu času a výrazně, ale opravdu výrazně vše zjednodušit. Nejenže je ve vteřině hezky naformátovaná, ale pro další výpočty se krásně přizpůsobuje v případě vložení dalších sloupců nebo řádků, sama ví, kam až chcete protáhnout vzorec. A pokud z ní poté chcete vytvořit kontingenční tabulku (ne, neomdlívejte, základy kontingenčních tabulek se budeme učit nakonec a budete je milovat ), nemusíte v případě přidání dalšího řádku nebo sloupce opravovat oblast, odkud má kontingenčka čerpat Je to takový malý velký zázrak Pojďme si tedy ukázat, jak na to…

ZÁKLADNÍ PŘÍKLAD: CHCEME VLOŽIT TABULKU

Postavíme se do jakékoliv buňky, kde máme naše data, půjdeme na kartu Vložení – Tabulka (nebo stiskneme klávesovou zkratku Ctrl+T), objeví se dialogové okno, kde zkontrolujeme, jestli je správně vybraná oblast v tabulce, případně zaškrtneme/odškrtneme, jestli data obsahují záhlaví a stiskneme Ok.



20


Vidíme, že tabulka se sama zformátovala a objevila se nám nová karta „Návrh“, kde si můžeme tabulku pojmenovat, odebrat v ní duplicity, vložit průřez (takový druh filtru, v tomto e-booku se mu věnovat nebudeme, možná příště ;-)), můžeme si zde vložit souhrny nebo nastavit jiný styl barev atd. Zkuste si teď cvičně napsat cokoliv do buňky I7. Co se stalo? Tabulka se sama rozšířila, pojmenovala sama sloupec a obarvila všechny řádky.



21


Ještě si teď vyzkoušejte vložit do buňky I7 jakýkoliv vzorec, např. =1+1. Tabulka spočítá, že je to 2 a zároveň tenhle vzorec zkopíruje do všech řádků tabulky. To ušetří času, že? ;-)

Pokud chceme tuhle „chytrou“ tabulku převést zpátky na „obyčejnou“, zvolíme na kartě Návrh možnost Převést na rozsah.



22


VLOŽENÍ KOMENTÁŘE

Někdy je potřeba trochu dovysvětlit, co se v určité buňce nachází, nebo co potřebujete, aby tam někdo zapsal. A

přesně k tomu slouží komentáře. Jsou to takové ty bubliny, které se zobrazí, když najedete na nějakou buňku.

ZÁKLADNÍ PŘÍKLAD: CHCEME VLOŽIT KOMENTÁŘ, DO KTERÉHO VEPÍŠEME TEXT: „TAKHLE SE PÍŠE DO

KOMENTÁŘE“.

Komentář vložíme přes kartu Revize-Nový komentář. Zobrazí se žlutý obdélník se šipkou, do kterého můžeme začít

psát.

Když máme komentář dopsaný a klikneme do buňky,

u které je vložený, můžeme s ním dále pracovat. Na

kartě Revize se nám nyní zobrazily možnosti Upravit

komentář, Odstranit a také Zobrazit nebo skrýt



23


komentář (pokud kliknete na tuhle možnost a komentář skryjete, zůstane u buňky viditelný jen červený růžek vpravo

nahoře a komentář se zobrazí pouze, pokud na buňku najedete myší).

Když klikneme na okraj komentáře, aby se nám

okolo něj ukázaly čtverečky (znamení, že jsme ho

správně označili) a klikneme pravým tlačítkem myši,

můžeme vybrat možnost Formát komentáře, kde se

dá nastavit formát písma, barvy, zarovnání a další

možnosti.



24


Komentáře ale nemusí být jen nudné žluté obdélníky, místo nich si

můžeme zvolit jiný tvar i barvu komentáře, nebo třeba místo klasického

tvaru můžeme vložit obrázek. Jak na to si ukážeme ve cvičebním souboru

k tomuto e-booku.

Schválně si teď vyzkoušejte vložit komentář do jakékoliv buňky a zkuste si stránku vytisknout. Nepůjde to, že? Žádný

komentář pro tisk se nezobrazil. Musíme si to nejdříve nastavit. Půjdeme na kartu Rozložení stránky – Tisk názvů

(stejné nastavení najdete, i když půjdete na kartu Soubor-Tisk-Vzhled stránky)



25


Zobrazí se nám dialogové okno, kde si na kartě List z rolovacího

seznamu vybereme, jestli chceme vytisknout komentář u buňky,

u které je napsaný (volba Podle zobrazení na listu), nebo jestli

má být seznam všech komentářů až na konci listu.



26


LOGICKÉ FUNKCE

Logické funkce slouží k různým logickým operacím, např. k ověření, jestli byly splněny nějaké podmínky. Pojďme se

na některé z nich podívat.

KDYŽ

KDYŽ je jedna z mých nejoblíbenějších funkcí. Zjišťuje, jestli je splněná nějaká podmínka. Pokud splněná je, něco se

stane (to, co my řekneme) a pokud není, stane se něco jiného. Pojďme si to vysvětlit na příkladu ze života.

ZÁKLADNÍ PŘÍKLAD: RODIČE ŘEKNOU DÍTĚTI: „POKUD PŘINESEŠ ZE ŠKOLY JEDNIČKU, PŮJDEME SPOLU DO

KINA. POKUD NE, BUDEME DOMA A BUDEŠ SE UČIT“. CO SE STANE, POKUD DOSTANE DÍTĚ DVOJKU?

Dostal jsi jedničku?

ANO -> půjdeme do

kina NE -> budeš se učit



27


Napíšeme si tedy do buňky C6 číslo 2, stoupneme si do jiné buňky, např. D6 a zadáme funkci KDYŽ. Funkci KDYŽ

nalezneme na kartě Vzorce – Logické – ze seznamu vybereme KDYŽ. Poté se nám zobrazí tabulka pro zadání

(argumenty funkce).

Pro náš příklad tedy zadáme následovně:

Podmínka – >pokud je v buňce C6 napsaná jednička-> C6=1

Ano –> pokud je v buňce C6 zapsaná jednička chceme, aby Excel napsal, že půjdeme do

kina -> „Půjdeme do kina“

Ne –> pokud v buňce C6 není jednička, chceme, aby Excel napsal, že se budeš učit ->

„Budeš se učit“

Už jen odsouhlasíme zadání stiskem OK a

uvidíme výsledek. My jsme v buňce C4 neměli

zadanou jedničku a proto vyšlo, že nebude

žádné kino, ale budeme se učit



28


Funkce KDYŽ se dá výborně kombinovat i s dalšími funkcemi (např. do podmínky můžeme zadat jakoukoliv další

funkci, často se používají např. funkce A nebo NEBO viz. dále), do argumentů „ANO“ a „NE“ můžeme klidně vložit

další funkci KDYŽ (nebo opět jakoukoliv další funkci) a tím rozšířit varianty (skvělé využití např. u intervalů) atd. Více

využití této velmi užitečné funkce najdete opět v cvičebním souboru k tomuto e-booku.

IFS

IFS je nová funkce Excelu 2016, v předchozích verzích Excelu ji tedy nenajdete a jedná se o takovou vícenásobnou KDYŽ. Pokud máte více podmínek, dříve se musely vnořovat další a další funkce KDYŽ do sebe a tím člověk mohl trochu ztratit přehled. Zadání IFS je o něco jednodušší a přehlednější.

ZÁKLADNÍ PŘÍKLAD: BUDEME VYCHÁZET ZE STEJNÉHO PŘÍKLADU JAKO PRO FUNKCI KDYŽ, JEN HO LEHCE

ROZŠÍŘÍME. KDYŽ DOSTANE DÍTĚ VE ŠKOLE 1, PŮJDEME DO KINA, KDYŽ 2, SMÍ SE DÍVAT NA TELEVIZI, KDYŽ

3 BUDE SE HODINU UČIT, KDYŽ 4 BUDE SE UČIT CELÝ VÍKEND, KDYŽ 5 BUDE SE UČIT CELÝ VÍKEND A CELÝ

TÝDEN DOKUD SI ZNÁMKU NEOPRAVÍ.

Funkci IFS najdeme opět na kartě Vzorce-Logické-IFS.

Otevře se nám už známé dialogové okno, kam postupně zadáme všechny logické testy a podmínky:



29


Teď už si můžete hrát s číslem, které napíšete do buňky A2 a Excel podle toho sám vyhodnotí, jak to bude s tím kinem a učením Tenhle i další příklad na funkci IFS opět naleznete v přiloženém cvičebním souboru.



30




31


A

Další z logických funkcí, jak jsem již zmínila, je výborná pro kombinaci s funkcí KDYŽ. Potřebujeme jí k tomu, abychom zjistili, jestli jsou všechny buňky, které vybereme, pravdivé.

ZÁKLADNÍ PŘÍKLAD:ZŮSTANEME VE ŠKOLNÍM PROSTŘEDÍ A ZNÁMEK. CHCEME ZJISTIT, JESTLI NAŠE DÍTĚ

DOSTALO ZE VŠECH PŘEDMĚTŮ JEDNIČKU.

Zapíšeme si tedy do jednotlivých buněk následující předměty a známky a vložíme do buňky H7 funkci A (Vzorce-Logické-A). Otevře se naše staré známé dialogové okno, kam postupně zadáme všechny podmínky, které ověřujeme, tedy, že v buňce D7 je napsaná 1, v buňce E7, že je také napsaná 1 atd. Takových podmínek můžeme zadat až 255 (to jsem zvědavá, kolik z nás to využije ).



32


Zadání odsouhlasíme stiskem OK a v buňce H7 se nám zobrazí vyhodnocení „NEPRAVDA“. To znamená, že všechny podmínky, které jsme zadali, splněné nejsou, takže ze všech předmětů jedničku nemáme. Kdybychom změnili dvojku z přírodopisu na jedničku, vyhodnocení se změní na „PRAVDA“.



33


NEBO

Další z kategorie logických funkcí, opět je výborná pro kombinaci s funkcí KDYŽ. Potřebujeme jí k tomu, abychom zjistili, jestli je alespoň jedna buňka z našeho výběru pravdivá. Vložíme ji opět přes kartu Vzorce-Logické a ze seznamu vybereme NEBO.

ZÁKLADNÍ PŘÍKLAD: KDYŽ UŽ NÁM TO S TĚMI ZNÁMKAMI TAK JDE, ZŮSTANEME JEŠTĚ STÁLE U NICH. TEĎ

CHCEME ZJISTIT, JESTLI NAŠE DÍTĚ DOSTALO JEDNIČKU ALESPOŇ Z JEDNOHO PŘEDMĚTU.

Zapíšeme si tedy do jednotlivých buněk následující předměty a známky a vložíme do buňky J7 funkci NEBO. Opět se otevře dialogové okno, kam postupně zadáme všechny podmínky, které ověřujeme, tedy, že v buňce F7 je napsaná 1, nebo v buňce G7, že je napsaná 1 atd.



34


Zadání odsouhlasíme stiskem OK a v buňce J7 se nám zobrazí vyhodnocení „PRAVDA“. To znamená, že alespoň z jednoho předmětu dostalo naše dítě jedničku. Pokud jedničku z matematiky změníme na dvojku, vyhodnocení se zase změní na „NEPRAVDA“.



35


IFERROR

V Excelu je několik funkcí, které umí ošetřit nějakou chybu, např. že se nenašla zadaná hodnota. Pokud totiž v buňce necháte chybovou hlášku, už potom těžko budete s buňkou dále počítat. IFERROR slouží jako univerzální funkce na ošetření chyb a funguje tak, že pokud zjistí, že výsledek v buňce bude nějaká chyba, napíše tam hodnotu, kterou určíte (např. 0).

Tuto funkci najdeme opět na kartě Vzorce-Logické-IFERROR. Po otevření dialogového okna zadáme do políčka Hodnota tu buňku, nebo třeba funkci, u které chceme zjistit, jestli vrátí nějakou chybu. A v případě, že v ní chybu nalezne, uvedeme do políčka Hodnota_v_případě_chyby to, co chceme, aby Excel napsal, tedy např. 0.

Tato funkce má využití především v kombinaci s vyhledávacími funkcemi (viz. další kapitola), proto si příklad ukážeme až dále (a samozřejmě ho najdete také v excelovém souboru).



36


VYHLEDÁVACÍ FUNKCE

Vyhledávací funkce vás naučí Excel využívat úplně v jiné dimenzi. Když jsem je poznala, byla jsem naprosto nadšená,

co všechno se může přestat dělat ručně. Oceníte je, pokud často vyhledáváte údaje ve velkých tabulkách a zabírá

vám to spoustu času. Některé vybrané vyhledávací funkce najdete v této kapitole.

SVYHLEDAT

Funkce SVYHLEDAT (v AJ verzi Excelu VLOOKUP) umí přiřadit údaji v jedné tabulce hodnotu z jiné tabulky. Například

najde telefonní číslo podle zadaného jména, jméno zákazníka podle čísla objednávky nebo cokoliv jiného, co budete

potřebovat.

HLEDÁNÍ PŘESNÉ HODNOTY

Funkce SVYHLEDAT dokáže vyhledat buď přesnou hodnotu, kterou hledáme, nebo nejbližší hodnotu. Častější je, že

hledáme přesnou hodnotu, tedy chceme v tabulce najít přesné jméno, číslo apod., proto touto možností začneme.

ZÁKLADNÍ PŘÍKLAD: MÁME TABULKU S TRŽBAMI A NÁKLADY PO JEDNOTLIVÝCH MĚSÍCÍCH A JÁ V NÍ

POTŘEBUJI VYHLEDAT TRŽBY ZA ÚNOR.



37


Do buňky F19 zadáme funkci SVYHLEDAT. Půjdeme na kartu Vzorce - Vyhl. A Ref.- v seznamu najdeme SVYHLEDAT.

Otevře se nám šedivé okno pro zadání argumentů funkce. Tady zadáme všechno, co Excel potřebuje vědět, aby nám

tržby našel správně.

Do pole „Hledat“ zadáme CO má Excel hledat v tabulce. Má tam

hledat tržby. Buď tedy přímo napíšeme „tržby“ anebo lepší způsob

je, jen kliknout do buňky D19, kde máme položku již napsanou. Do

pole „Tabulka“ zadáme, KDE, tedy v jaké tabulce má Excel to tržby

hledat. Vybereme tedy myší celou tabulku (buňky C1:G4). Do políčka „Sloupec“ napíšeme, kolikátý sloupec z tabulky

chceme tržbám přiřadit, tedy v kolikátém sloupci máme napsané tržby za únor. V našem případě je to třetí sloupec

tabulky, proto napíšeme číslo 3. Do posledního políčka „Typ“ se zadává logická hodnota – buď 0, to znamená, že

chceme hledat podle přesné hodnoty (ano, to chceme, Excel nám má najít přesně tržby) anebo se zde zadá číslo 1,

potom by Excel vrátil nejbližší hodnotu (to pro náš příklad nemá ale žádný význam). Nyní již jen potvrdíme stisknutím

OK a už víme, za kolik jsme v tom únoru prodali .



38




39


OŠETŘENÍ CHYBY

Zkuste teď schválně napsat do políčka D19 napsat nějaké slovo, které v tabulce není, třeba zisk. Co se stane? Funkce

SVYHLEDAT nám napíše, že takový údaj v tabulce nenašla, objeví se nám chybová hláška #NENÍ_K_DISPOZICI.

Abychom se takovým hláškám vyhnuli a místo toho měli

v buňce napsané např.“není v tabulce“, nebo třeba „0“

použijeme funkci IFERROR, kterou jsem zmínila na konci

minulé kapitoly. Vložíme tedy do buňky F10 funkci

IFERROR, do políčka Hodnota teď vložíme funkci

SVYHLEDAT tak, jak jsme to udělali před chvilkou a do

políčka Hodnota_v_případě_chyby vepíšeme „0“.

Komu se takhle zdá složité (jako třeba mně ), stačí jen kliknout do políčka, kde máme zapsanou funkci SVYHLEDAT

a nahoře v okně, v řádku vzorců, kde se mi zobrazuje zápis celé funkce, tam funkci IFERROR dopsat.



40


V řádku vzorců prostě klikneme hned za „=“, napíšeme „IFERROR(“ – tím vložíme funkci IFERROR a řekneme jí, že

má zjistit, jestli naše funkce SVYHLEDAT vyjde s chybou nebo ne (naše funkce SVYHLEDAT je pro funkci IFERROR to,

co bychom zapsali do políčka Hodnota). Potom klikneme na konec a napíšeme středník, kterým Excelu řekneme, že

teď budeme psát, co se má v buňce zobrazit, pokud vyjde chyba (tedy Hodnota_v_případě_chyby ve funkci IFERROR),

my zapíšeme za středník třeba 0 a ukončíme závorku.

Pokud se naučíte takhle ošetřovat chyby, už nikdy neuvidíte nevzhledné chybové hlášky



41


HLEDÁNÍ PŘIBLIŽNÉ HODNOTY

Na předchozím příkladu jsme si ukázali, jak se hledá, pokud chceme najít přesnou hodnotu. Co když ale hledáme

hodnotu, která spadá do nějakého intervalu?

ZÁKLADNÍ PŘÍKLAD: TYPICKÝM PŘÍKLADEM JE TABULKA, KDE PODLE OBRATU PRODEJE PŘIŘAZUJEME

ODBĚRATELI NĚJAKOU SLEVU. KDYŽ ODBĚRATEL ODEBERE ZA 125 895, JAKOU SLEVU MU PŘIŘADÍTE?

Pokud bychom použili funkci SVYHLEDAT tak, jak jsme si

již ukázali, nedostali bychom žádný výsledek, ale pouze

chybovou hlášku #NENÍ_K_DISPOZICI (nebo 0, pokud

bychom chybu ošetřili pomocí funkce IFERROR) a to

prostě proto, že číslo 125 895 v naší tabulce s přehledem

obratu a výše slevy není. Jak tedy výši slevy zjistíme?

Do buňky G2 si vložíme funkci SVYHLEDAT úplně stejně jako v předchozím případě. Vzorce-Vyhl. a Ref. –

SVYHLEDAT, opět se nám zobrazí dialogové okno pro zadání parametrů funkce.



42


Do pole Hledat zadáme, CO hledáme – klikneme do buňky,

kde je výše obratu, tj. do buňky F4, Tabulka – KDE, to

hledáme – vybereme myší oblast buněk B5:C10, Sloupec –

v kterém sloupci tabulky se nachází hledaná hodnota? Výše

slevy je ve 2. sloupci, napíšeme tedy číslo 2, Typ – a tady

bude ten rozdíl, oproti předchozímu příkladu, pokud

bychom chtěli najít v tabulce přesnou hodnotu, zadáme 0,

to my ale nechceme, protože taková hodnota v tabulce

není. Proto nyní zadáme číslo 1 a zobrazí se nám nejbližší odpovídající hodnota ze seznamu, tj 0,02 (tedy 2 %,

zobrazení čísla na procenta si upravíme na kartě Domů nebo ve Formátu buněk).



43


VVYHLEDAT

Funkce VVYHLEDAT (v AJ verzi Excelu HLOOKUP) je obdobou funkce SVYHLEDAT s tím rozdílem, že nehledá hodnotu

v nějakém sloupci, ale naopak v řádku.

ZÁKLADNÍ PŘÍKLAD: VRÁTÍME SE K TABULCE S TRŽBAMI A NÁKLADY PO JEDNOTLIVÝCH MĚSÍCÍCH. NYNÍ

POTŘEBUJI V ÚNORU VYHLEDAT VÝSLEDEK HOSPODAŘENÍ.

Vložím tedy funkci VVYHLEDAT (Vzorce-Vyhl. a Ref. –

VVYHLEDAT), po zobrazení dialogového okna zadám do pole

Hledat, CO hledám. Já teď hledám údaje za únor, proto kliknu

do buňky, kde mám zapsané únor (nebo můžu napsat i ručně).

Do pole Tabulka opět vyberu myší celou tabulku (tedy oblast C1:G4). DO pole řádek zadám, v kterém řádku tabulky

je hodnota, kterou chci napsat. Výsledek hospodaření je napsaný ve čtvrtém řádku tabulky, proto zde napíšu číslo

4. A do pole Typ zadám 0, protože chci najít přesnou odpovídající hodnotu. Už jen odsouhlasíme a máme výsledek.



44




45


POZVYHLEDAT

Teď jsme se naučili vyhledávací funkce pro hledání v řádcích a sloupcích, ale určitě vás při tom napadlo, že je pěkně nepohodlné ručně počítat, které číslo sloupce (nebo řádku) vlastně chceme doplnit. Navíc pokud chceme k nějaké hodnotě doplnit víc sloupců (řádků), tak pokaždé musíme manuálně číslo přepsat, tzn. kopírováním do dalších buněk se nemění. A právě na to, abychom nemuseli ručně doplňovat čísla, máme funkci POZVYHLEDAT (v AJ verzi Excelu MATCH). Najdeme ji jak jinak než na kartě Vzorce-Vyhl. a Ref. – POZVYHLEDAT.

ZÁKLADNÍ PŘÍKLAD: OPĚT VYUŽIJEME NAŠI TABULKU S TRŽBAMI A NÁKLADY. TEĎ BYCHOM CHTĚLI ZJISTIT,

JAKÉ ČÍSLO SLOUPCE MÁ DUBEN.

Nejdříve do buňky F8 vložíme funkci POZVYHLEDAT, v otevřeném

dialogovém okně potom vyplníme do políčka „CO“ položku, kterou

hledáme (tedy duben). Do pole Prohledat vybereme řádek, ve

kterém se slovo „duben“ nachází (POZOR – nevybíráme celou

tabulku!) a do pole Shoda napíšeme opět 0, protože chceme

přesně údaj duben a nic jiného, odsouhlasíme a už vidíme, že duben najdeme v 5. sloupci tabulky.



46




47


MATEMATICKÉ FUNKCE

Matematické funkce slouží především k různým matematickým operacím. S jednou z nich (SUBTOTAL) se seznámíme

v jedné z dalších kapitol o filtrech. Některé další, které jsou vhodné k běžnému použití, najdete níže.

ZAOKROUHLIT

Zaokrouhlování se při výpočtech asi nevyhne nikomu z nás. Častý omyl je, že stačí nastavit zaokrouhlení čísla ve Formátu buněk. Ve Formátu buněk jen nastavujeme zobrazení, ale ve skutečnosti hodnota zaokrouhlená není. Na to, abychom skutečně dostali zaokrouhlenou hodnotu, máme funkci ZAOKROUHLIT. (v AJ verzi ROUND) Najdeme ji v záložce Vzorce-Mat. a trig. – ZAOKROUHLIT.

ZÁKLADNÍ PŘÍKLAD: CHCEME ZAOKROUHLIT ČÍSLO 314,5268 NA DVĚ DESETINNÁ MÍSTA.

Do buňky B4 vložíme funkci ZAOKROUHLIT. Po otevření dialogového okno do pole „Číslo“

klikneme na buňku A4 a do pole Číslice zadáme, na kolik desetinných míst chceme

zaokrouhlit, tj. napíšeme číslo 2 a klikneme na OK. A máme zaokrouhleno



48




49


ABS

Funkce ABS je skvělá pomůcka, pokud máte v buňkách čísla jak kladná, tak záporná, ale potřebujete jen jejich hodnotu bez ohledu na znaménko. Tuto funkci opět najdeme na kartě Vzorce – Mat. a Trig. – ABS

ZÁKLADNÍ PŘÍKLAD: CHCEME MÍT V BUŇCE JEN ČÍSLA BEZ OHLEDU NA JEJICH ZNAMÉNKA, TEDY JEN JEJICH

ABSOLUTNÍ HODNOTU.

Ve sloupci A máme různá čísla a do buňky B8 si vložíme funkci ABS. Po zobrazení dialogového okna jen klikneme na buňku, kde chceme zjistit absolutní hodnotu a potvrdíme OK. Poté jen vzorec zkopírujeme do dalších řádků (koho napadlo si do oblasti vložit tabulku, tomu se vzorec zkopíroval již sám ;-) ). Kdo raději vzorce píše, tak pouze do buňky B8 zapište „=ABS(A8)“.



50




51


DATUM A ČAS

Tato kategorie funkcí slouží k různým operacím s daty a časem, k výpočtům rozdílů dat, převádění na jiné formáty

atd. Pojďme si ukázat několik vybraných z nich.

DNES

DNES je jednoduchá funkce, která pokaždé zobrazuje aktuální datum. Typické využití v praxi je např. pro zjištění

částky faktur po splatnosti. Funkci DNES najdeme na kartě Vzorce – Datum a čas – Dnes a potvrdíme Ok, nebo přímo

napíšeme do buňky =DNES(). Tato funkce jako jedna z mála nemá žádné argumenty, které bychom museli zadávat,

protože prostě vždycky napíše, kolikátého je.

ZÁKLADNÍ PŘÍKLAD: JAKÝ JE DNEŠNÍ DATUM?

Do buňky, do které chceme, zapíšeme =DNES() a je to



52


MĚSÍC

Funkce MĚSÍC (v AJ verzi Excelu MONTH) se používá, když chceme zjistit z celého data jen, o jaký měsíc se jedná.

Typické využití je, pokud chcete sčítat např. jen objednávky za březen, ale máte seznam se spoustou objednávek

s přesným datem. Stačí vložit funkci MĚSÍC a hned si jednoduše březen vysčítáme.

ZÁKLADNÍ PŘÍKLAD: POTŘEBUJEME Z DATA ZJISTIT JEN MĚSÍC.

Do buňky C14 vložíme funkce MĚSÍC (Vzorce- Datum a čas-MĚSÍC),

v dialogovém okně jen klikneme do buňky, kde máme zapsaný datum a

potvrdíme. Nebo jen do buňky zapíšeme =MĚSÍC(B14).



53


DATEDIF

Funkce Datedif slouží k výpočtu rozdílu mezi jednotlivými daty. Můžeme si zvolit, jestli chceme rozdíl zobrazit ve dnech, měsících nebo letech. Z nějakého neznámého důvodu se tato funkce nedá najít v seznamu funkcí, je nezařazená a prostě o ní musíte vědět (nebo si vygooglit, že potřebujete funkci na rozdíl datumů ) Zadává se následovně: =DATEDIF(dřívější datum;novější datum;jednotka).

ZÁKLADNÍ PŘÍKLAD: CHCI ZJISTIT, KOLIK DNÍ, MĚSÍCŮ A LET UPLYNULO OD VAŠEHO NAROZENÍ.

Zapíšeme si tedy do buněk datum narození, do další buňky vložíme funkci DNES, abychom zjistili dnešní datum. Počet dnů bychom získali i obyčejným rozdílem, jak je vidět v tabulce. Pokud ale chceme i počet měsíců nebo let, použijeme právě funkci DATEDIF.

Zapíšeme jí následovně: =DATEDIF(vaše datum narození;dnešní datum;“M“) -> (pro zjištění počtu měsíců, napíšeme do uvozovek „M“, pro počet dnů bychom zapsali „D“ a pro počet let potom „Y“.)



54


NETWORKDAYS

Už umíme spočítat počet dnů mezi jednotlivými daty, ale co když potřebujeme jenom počet pracovních dnů? Od toho máme funkci NETWORKDAYS – podle mě výborná pomůcka např. pro mzdové účetní. Tato funkce totiž umí spočítat, kolik pracovních dnů uběhlo mezi dvěma daty a zohlední i počet svátků, který zadáme. Najdeme ji na kartě Vzorce-Datum a čas-NETWORKDAYS

ZÁKLADNÍ PŘÍKLAD: CHCEME ZJISTIT, KOLIK PRACOVNÍCH DNŮ UBĚHLO OD 1.1.2016 DO 31.1.2016.

Do buňky C11 si vložíme funkci NETWORKDAYS, po zobrazení dialogového okna do políčka Start vložíme odkaz na první den, který jsme si připravili v tabulce, tedy na 1.1.2016. Do pole Konec klikneme na buňku, kde máme zapsaný poslední den, tedy 31.1.2016. A do pole Svátky označíme data se svátky, v lednu je jen

jeden svátek a to hned 1.1.2016, proto klikneme do buňky, kde ho máme zapsaný. Teď už jen potvrdíme a máme hotovo.



55




56


TEXTOVÉ FUNKCE

Textové funkce nám umožňují různé možnosti, jak pracovat s textem. Jak ho oříznout z různých stran, měnit velikost, spojovat, převádět na jiné formáty a spoustu dalšího. Pojďme si ukázat některé z textových funkcí, které považuji za nejužitečnější.

ZLEVA

Funkce ZLEVA (v AJ verzi LEFT) nám „ořízne“ text v buňce z levé strany a napíše nám tolik znaků, kolik chceme.

ZÁKLADNÍ PŘÍKLAD: MÁM V BUŇCE NAPSANÉ CELÉ JMÉNO JOSEF NOVÁK, ALE CHCI ZOBRAZIT JEN

PRVNÍCH 5 ZNAKŮ.

Do buňky D8 tedy vložíme funkci ZLEVA. Najdeme ji na kartě Vzorce-Textové-ZLEVA

A zase se nám otevře dialogové okno, kde do pole Text zadáme text, který chceme oříznout (můžeme ho zde přímo napsat, nebo kliknout do buňky, kde ho již máme) a do pole Znaky napíšeme, kolik znaků od začátku textu chceme. My zadáme 5. Po odsouhlasení tlačítkem OK se nám do pole D8 napíše prvních 5 znaků, tedy „Josef“.



57


Kdo nemá rád zbytečné klikání, může funkci do buňky D8 rovnou zapsat a to =ZLEVA(C8;5).



58


ZPRAVA

Funkce ZPRAVA naprosto překvapivě „ořízne“ text v buňce z pravé strany a opět o tolik znaků, kolik určíme.

ZÁKLADNÍ PŘÍKLAD: MÁM V BUŇCE NAPSANÉ CELÉ JMÉNO JOSEF NOVÁK, ALE CHCI ZOBRAZIT JEN

POSLEDNÍCH 5 ZNAKŮ.

Do buňky D8 vložíme funkci ZPRAVA, kterou najdeme v nabídce Vzorce-

Textové-ZPRAVA.



59


Opět, pokud neradi klikáte, stačí do buňky zapsat „=ZPRAVA(C8;5)



60


ČÁST

Tato funkce dokáže oříznout hodnotu v buňce jen na ty údaje, které chcete znát. Můžete si nastavit, že má např.

vypsat jen 4.- 8. pozici v buňce. Také užitečné, ne? Musíme jí jen říct, kde má začít a kde skončit. Najdeme ji opět na

kartě Vzorce-Textové – Část.

ZÁKLADNÍ PŘÍKLAD: MÁME V BUŇCE NAPSANOU VĚTU A CHTĚLI BYCHOM VYPSAT JEN DRUHÉ SLOVO, TJ.

„RÁDI“

Vložíme do buňky funkci Část, v dialogovém okně do políčka Text

označíme buňku, v které máme uvedenou naši větu, do pole Start

napíšeme, od kolikátého znaku chceme, aby Excel začal (počítají se i mezery), tj. první chceme vidět 6. znak a do pole

Počet-znaků uvedeme, kolik znaků od Startu chceme vypsat. My tedy zadáme 4, protože slovo „rádi“ má 4 písmena.

Odsouhlasíme OK a máme to



61




62


HLEDAT

Potřebujete zjistit, jestli buňka obsahuje nějaké slovo nebo znak? Pak nutně potřebujete funkci HLEDAT (v AJ verzi

Excelu SEARCH) Tahle funkce umí zjistit, jestli se nějaký znak nebo text nachází v zadané buňce a pokud ano, napíše

nám pořadové číslo znaku v buňce, kde hledaný text začíná. Pokud tam to, co hledáme, nenajde, napíše chybovou

hlášku. Ale s tou už si taky umíme poradit, už přece umíme funkci IFERROR ;-) Tuto funkci najdeme opět v seznamu

textových funkcí na kartě Vzorce.

ZÁKLADNÍ PŘÍKLAD: POTŘEBUJEME ZJISTIT, JESTLI JE V BUŇCE NAPSANÉ SLOVO „RÁDI“

Do buňky D8 vložíme funkci HLEDAT. Vyplníme, CO hledáme (tedy slovo

„rádi“, nebo můžeme vložit odkaz na nějakou buňku, kde máme hledané

slovo/hodnotu/znak vyplněné), KDE to hledáme (v buňce C8). Políčko Start vyplňovat nemusíme, vyplnili bychom ho

v případě, že bychom nechtěli, aby funkce prohledala celou buňku, ale jen od určitého znaku. Odsouhlasíme a

vidíme, že slovo „rádi“ začíná v buňce na 6. pozici (opět se pořadí znaku počítá včetně mezery).



63




64


CONCATENATE

Skvělá funkce, když potřebujete spojit hodnoty z několika buněk do jedné. Najdeme ji jako všechny funkce na kartě Vzorce, potom Textové – CONCATENATE.

ZÁKLADNÍ PŘÍKLAD: MÁME POD SEBOU TŘI BUŇKY S TEXTEM A CHCEME JE SPOJIT DO JEDNÉ VĚTY.

Do buňky D8 si vložíme funkci CONCATENATE, tedy Vzorce-Textové-

CONCATENATE. (Pokud používáte Excel 2016, již ji nenajdete

v rozbalovacím seznamu u textových funkcí, protože je nahrazena funkcí

CONCAT, kterou vysvětluji dále, ale samozřejmě stále funguje.) Klikneme

do první buňky, kterou chceme spojit, do pole Text2 vložíme uvozovky a mezi ně mezeru, jinak bychom neměli slova

oddělená, ale „nalepená“ na sobě. Do Pole Text3 klikneme na druhou buňku, kterou chceme spojit, do pole Text4

opět vložíme mezeru mezi uvozovkami atd. Potom potvrdíme stiskem OK a text se nám krásně spojil.



65


Nevýhodou této funkce je, jak už jste si asi všimli, že nemůžete vybrat oblast buněk najednou, ale musíte do každé buňky kliknout. Tuto nevýhodu ale odstraňuje nová funkce v Excelu 2016, kterou už jsem zmínila a to CONCAT.



66


CONCAT

Nová funkce v Excelu 2016, nahrazuje funkci CONCATENATE, její výhodou oproti CONCATENATE je, že můžete vybrat celou oblast buněk, kterou chcete spojit najednou a nemusíte klikat do jednotlivých buněk!

Najdeme ji v textových funkcích, tedy Vzorce-Textové-CONCAT.


Do buňky B16 vložíme funkci CONCAT. Do pole Text1 označíme celou

oblast, kterou chceme spojit, nemusíme tedy klikat do jednotlivých buněk.

ALE….. Stále je tu jedna nevýhoda….Pokud chceme mít slova oddělená

mezerou (a to my chceme), musíme za každé slovo, které chceme spojit,

kliknout a udělat mezeru, aby funkce CONCAT fungovala, tak jak potřebujeme. (Pokud mezeru nebo jiný znak

nechcete a stačí vám mít hodnoty z buněk vedle sebe, potom je CONCAT oproti CONCATENATE velkým

zjednodušením.) Pro náš příklad ale bude úplně nejvhodnější funkce TEXTJOIN (také novinka v Excelu 2016), kterou

se hned naučíme



67




68


TEXTJOIN

Další nová funkce Excelu 2016, také dokáže spojit jednotlivé buňky tím, že vyberete celou oblast, ale navíc si ještě můžete určit, jak chcete jednotlivé buňky od sebe oddělit, tzn. vložit mezi ně čárku, mezeru, pomlčku…. Hurá, konečně!


Vložíme funkci TEXTJOIN. Půjdeme na kartu Vzorce-Textové-TEXTJOIN.

V zobrazeném dialogovém okně napíšeme, jaký chceme použít Oddělovač

(my chceme mezeru – nezapomeňte ji dát do uvozovek, místo mezery ale

můžeme samozřejmě dát čárku, středník, cokoliv, co potřebujeme), Dále zadáme, jestli chceme ignorovat prázdné

buňky (my žádné nemáme, proto můžeme nechat prázdné, pokud bychom chtěli ignorovat prázdné buňky, zapíšeme

do tohoto pole PRAVDA) a nakonec do pole Text1 vybereme oblast buněk, kterou chceme spojit. Teď už jen potvrdit

a máme to Konečně dokonalá funkce na spojení hodnot z několika buněk



69




70


ŘAZENÍ

Pokud máme větší tabulku, je velmi užitečné naučit se data různě řadit, abychom neztráceli přehled. V řazení nabízí Excel opravdu hodně možností, můžeme řadit podle jednoho sloupce, ale také klidně můžeme přidat více úrovní řazení, takže se data seřadí nejdříve podle jednoho sloupce, potom podle druhého, třetího atd. Také si můžeme zvolit řazení podle barvy buňky nebo písma (to mám moc ráda, barvy, to je moje ), anebo např. podle ikony v buňce. Všímáte si, jak se dá řazení krásně spojit s podmíněným formátováním? ;-) Můžeme si dokonce i nastavit, že chceme, aby Excel bral jako rozdíl velké a malé písmeno a řadil stejná slova, která se liší právě jen velikostí prvního písmene, odděleně. A co podle mě většina lidí neví, dá se řadit nejen ve sloupcích odshora dolů, ale také zprava doleva. Anebo když si nevybereme, můžeme si nastavit svůj vlastní seznam toho, jak se naše data mají řadit. Tak si to pojďme ukázat

ZÁKLADNÍ PŘÍKLAD: MÁME SEZNAM OKRESŮ ČR A JEDNOTLIVÉ OBCE, KTERÉ DO OKRESU SPADAJÍ,

V DALŠÍCH SLOUPCÍCH JE UVEDENÝ CELKOVÝ POČET OBYVATEL V KAŽDÉ OBCI A POTOM POČET MUŽŮ A

ŽEN. MY BYCHOM TEĎ CHTĚLI SEŘADIT TABULKU TAK, ABY OBCE BYLY PODLE ABECEDY.

Vybereme celou naši tabulku a půjdeme na kartu Data – Seřadit. (Nelekněte se, že je tabulka opravdu obsáhlá, určitě se i s tolika daty někdy setkáte. Abyste ale nemuseli vybírat tabulku myší, poradím vám šikovnou klávesovou zkratku. Stoupněte si do první buňky tabulky, zmáčkněte Ctrl+Shift+šipku dolů -> tím vyberete celý první sloupec, Ctrl+Shift pořád držte a teď zmáčkněte šipku doprava -> tabulka je celá vybraná ).



71


Otevře se dialogové okno, kde v rolovacím seznamu vybereme, že chceme tabulku seřadit podle Názvu obce, řadit chceme podle hodnot v buňkách a pořadí má být A až Z.

Teď jen potvrdíme svoji volbu

tlačítkem OK a máme seřazeno

Ani to nebolelo, že? Více možností řazení, najdete v cvičebním souboru k tomuto e-booku.



72


FILTROVÁNÍ

Data v tabulce už si umíme seřadit, ale co když chceme vidět jen ty buňky, které splňují nějakou podmínku? Od toho

máme filtry V tomto e-booku se budeme věnovat jen automatickému filtru, pokročilejší možnosti si necháme na

příště.

AUTOMATICKÝ FILTR

Filtrovat můžeme v jednom sloupci, ale klidně i ve všech sloupcích zároveň. Nebo si můžeme vybrat, že chceme vidět

jen prázdné buňky v nějakém sloupci (třeba, kdo nám ještě nezaplatil ). Můžeme filtrovat podle barvy buňky nebo

písma (znovu připomínám podmíněné formátování, je skvělé to všechno propojit ).

Filtrování se dá nastavit také jen podle toho, jestli daná buňka obsahuje část textu, podle její hodnoty, nebo podle

toho, jestli je ta hodnota větší (menší, stejná atd.) než jiná hodnota. Prostě můžeme si s filtry vyhrát, jak chceme. Jak

si tedy ten automatický filtr vložíme?



73


ZÁKLADNÍ PŘÍKLAD: BUDEME VYCHÁZET ZE STEJNÉ TABULKY JAKO PŘI ŘAZENÍ. ROZHODNEME SE, ŽE

CHCEME VIDĚT JEN TY OBCE, KDE JE POČET OBYVATEL VĚTŠÍ NEŽ 10 000.

Postavíme se do tabulky a půjdeme na

kartu Data - Filtr, po stisknutí tlačítka se

nám u buněk v hlavičce tabulky zobrazí

takové malé šipečky, které můžeme

rozkliknout a v nich si zvolit, co chceme

a co nechceme vidět. My chceme

zobrazit jen obce, které mají počet

obyvatel větší než 10 000, rozklikneme

tedy šipku u sloupce Počet obyvatel –

celkem, vybereme Filtry čísel – Větší

než, do okna, které se nám otevřelo, doplníme číslo 10000 a stiskneme OK.



74


A máme to . Kdybyste chtěli, můžete si filtr vložit i do dalšího sloupce, např. jen pro určitý počet mužů apod.

Všimněte si, že po tom, co jsme nastavili filtr, se čísla řádků zbarvily namodro. Podle toho vždy poznáte, že pracujete

se zafiltrovanými daty (a někdy může

pořádně potrápit, když si toho

nevšimnete . Když už filtr nechcete,

jen na kartě Data znovu stiskněte

tlačítko Filtr a on zmizí.

Ještě jedna poznámka na závěr

kapitoly, pokud chcete v zafiltrované

oblasti sčítat, nebo dělat jiné výpočty,

Excel bude stejně počítat ze VŠECH

dat, ne jen z těch, které jsme si



75


zobrazili. Pokud chceme počítat jen s zafiltrovanými daty, máme na to jednu šikovnou funkci, která se jmenuje

SUBTOTAL. Tato funkce spadá do matematických funkcí, ale protože se používá právě pro práci s zafiltrovanými daty,

zařadila jsem ji do této kapitoly. Pojďme si ji ukázat.

SUBTOTAL

Jak už jsem uvedla, filtrování je jistě velmi užitečné, ale má jednu malou nevýhodu. Pokud máte zafiltrovaná data a

chcete s těmito daty nějak dál počítat, máte smůlu, protože Excel bude dál sčítat, násobit, používat jakékoliv funkce

ze VŠECH dat, ne jen z těch, která jsme si zobrazili. Což je tedy docela problém. My ho ale za malou chvilku budeme

umět vyřešit. Existuje funkce SUBTOTAL, která dělá přesně to, co potřebujeme. Počítá pouze ze zobrazených dat.

Pomocí této funkce můžeme vložit funkce PRŮMĚR, POČET,POČET2, MAX, MIN,SOUČIN, SMODCH.VÝBĚR, SMODCH,

SUMA, VAR.VÝBĚR A VAR. Najdeme ji na kartě Vzorce-Mat. a Trig. – SUBTOTAL.



76


ZÁKLADNÍ PŘÍKLAD: OPĚT SE VRÁTÍME K NAŠÍ TABULCE A ZKUSÍME SPOČÍTAT, KOLIK JE CELKEM OBYVATEL

V OKRESE BEROUN.

Vložíme si do buňky C3, tedy do sloupce s Počtem obyvatel celkem funkci

SUBTOTAL. Po zobrazení dialogového okna, napíšeme do pole Funkce číslo funkce,

kterou chceme využít, viz. tabulka vpravo. (Vypadá to možná složitě, ale nebojte,

stačí si pamatovat, že průměr má číslo 1 a suma má číslo 9, ve většině případů

ostatní funkce pravděpodobně nevyužijete nebo alespoň já využívám při filtrování

jiné funkce velmi sporadicky). Do pole Odkaz1 potom označíme celý sloupec

tabulky. Nyní si vložíme filtr a vybereme, že chceme zobrazit jen okres Beroun a

máme to spočítané



77


Další příklad na filtrování (ne, na flirtování ) najdete opět

v cvičebním souboru k tomuto e-booku.



78


SOUHRNY, SESKUPENÍ A ODDĚLENÍ

Pokud pracujete s větší množstvím dat, určitě už jste si říkali, jak by bylo příjemné, kdyby šlo pod nějakou skupinu

údajů vložit mezisoučty. A v případě potřeby detail skrýt a zase rozbalit. Anebo třeba žádné mezisoučty

nepotřebujeme, ale skrýt detail v nějaké skupině buněk by vše zpřehlednil. Hurá! Máme řešení!

SOUHRNY

Tím řešením je vložení souhrnů. Jak jsem již uvedla výše, pokud potřebujeme vložit mezisoučty k nějaké skupině dat a potřebujeme detail skrýt a později zase zobrazit, jsou souhrny ideální věc. Jak to tedy provedeme….

ZÁKLADNÍ PŘÍKLAD: OPĚT SI VEZMEME NAŠÍ TABULKU S OKRESY, OBCEMI A POČTY OBYVATEL. TEĎ

BUDEME CHTÍT ZJISTIT, KOLIK JE POČET OBYVATEL CELKEM, MUŽŮ A ŽEN PO JEDNOTLIVÝCH OKRESECH.

První a nejdůležitější věc je, že data seřadíme podle sloupce, který nám označuje skupinu, kterou budeme chtít sčítat (pro náš příklad tedy okres). Proč to děláme? Souhrn pracuje tak, že vloží mezisoučet za každý řádek, kde dojde ke změně ve skupině (tedy v názvu okresu), pokud si tedy data neseřadíme, budeme mít mezisoučet pod každým řádkem a to je nám potom souhrn úplně k ničemu. (Při výběru tabulky nezapomeňte na klávesovou zkratku Ctrl+Shift+šipka dolů -> Ctrl+Shift+šipka doprava, ať vás nebolí ruka ). Seřazeno? Nyní klikneme na kartu Data a zvolíme Souhrn.



79


Otevře se nám tabulka, která se ptá, kdy chceme vložit souhrn (U každé změny ve sloupci) – tady vybereme sloupeček, u kterého je důležitá jeho změna (tedy Okres). V dalším rozbalovacím seznamu vybereme, v jaké formě má mezisoučet být, jestli to má být součet, nebo průměr ze skupiny, maximum, minimum atd. Vybereme, co potřebujeme vědět, my chceme součet. A v posledním poli zaškrtneme všechny sloupce, u kterých chceme mezisoučet vložit, tedy Počet obyvatel celkem, Počet obyvatel – muži i Počet obyvatel - ženy. Stiskneme OK a už se můžeme kochat tím, jak máme krásně vložené mezisoučty (někdy to chvíli trvá, tak dopřejte Excelu trochu času).



80


Všimněte si znamének „-“, která se nyní objevila u čísel řádků. Když je stisknete, skupina se „zabalí“ a uvidíte pouze

právě vložený mezisoučet. Po stisknutí znaménka „+“, si zase rozbalíte detail. Stejně tak můžeme skupinu sbalit a rozbalit na různé úrovně stisknutím jednoho z čísel, které se zobrazilo vlevo vedle názvů sloupců.

Pokud už vám souhrny překážejí a chcete je odstranit, nebojte, nemusíte mazat řádek po řádku. Půjdeme opět na kartu Data-Souhrny a po otevření tabulky klikneme na tlačítko „Odebrat vše“.



81


SESKUPENÍ A ODDĚLENÍ

Už umíme vložit souhrn pod nějakou skupinu dat. Ale co když nechci vložit žádný mezisoučet, ale jen chci skupinu sbalit a rozbalit podle potřeby? Na to nám slouží Seskupování.

ZÁKLADNÍ PŘÍKLAD: OPĚT SI VEZMEME NAŠÍ TABULKU S OKRESY, OBCEMI A POČTY OBYVATEL. NYNÍ

CHCEME SESKUPIT VŠECHNY OBCE S OKRESU BENEŠOV A NECHCEME VLOŽIT ŽÁDNÝ MEZISOUČET.

Vybereme řádky nebo sloupce, které chceme seskupit, půjdeme opět na kartu Data, ale tentokrát zvolíme tlačítko Seskupit.

Opět se nám u vybrané oblasti zobrazilo znaménko „-“.

Po kliknutí na něj se nám celá skupina zabalí a zobrazí se znaménko „+“, kterým si celou skupinu opět rozbalíme. Je možné seskupovat až do 8 úrovní, což většina z nás nejspíš ani nevyužije.



82


Když už se vám seskupení nelíbí, jednoduše se ho zbavíte tak, že opět vyberete oblast a stisknete tlačítko „Oddělit“, které se nachází jak jinak než na kartě Data.



83


ÚVOD DO KONTINGENČNÍCH TABULEK

A je to tady! Kontingenční tabulka! Z nějakého důvodu se při vyslovení jejího názvu lidé orosí, oklepou a mají strach!

Nebojte se, podle mě je důvod toho strachu v jejím názvu, protože si nikdo neumí představit, co to vlastně je a proto

je to nejspíš hrozně složité a nepochopitelné. Ale opak je pravdou! Kdo se jednou naučí používat kontingenční

tabulky (a to trvá asi tak 5 minut ), už nikdy nebude chtít jinak a nebude chápat, jak to dělal předtím

K čemu tedy taková kontingenční tabulka je? Umí souhrnně zobrazit opakující se údaje. To znamená, že už nikdy více

nebudete muset ručně sčítat např. objednávky od jednotlivých zákazníků. Jen si zvolíte, že v řádku bude zákazník,

ve sloupci součet jeho objednávek a voilá, je to tam Pojďme si to tedy ukázat, bude se vám to určitě líbit

ZÁKLADNÍ PŘÍKLAD: VRÁTÍME SE TEĎ K TABULCE, NA KTERÉ JSME SE UČILI ŘADIT, FILTROVAT A VKLÁDAT

SOUHRNY – K TABULCE, KDE JSOU UVEDENY OKRESY, OBCE A POČTY OBYVATEL. BUDEME OPĚT CHTÍT

ZJISTIT POČET OBYVATEL CELKEM I PODLE POHLAVÍ PODLE OKRESŮ.

Jak budeme postupovat…Vybereme si myší celou oblast tabulky (nebo naše data převedeme

na „chytrou“ tabulku, kterou jsme se učili na začátku tohoto e-booku – pomocí klávesové

zkratky Ctrl+T) , půjdeme na kartu Vložení a vybereme Kontingenční tabulka.



84


Pozor, je důležité, aby všechny sloupce v tabulce byly pojmenované. Pokud nějaký pojmenovaný nebude,

kontingenční tabulka nepůjde vložit!

Objevilo se nám okno pro vytvoření kontingenční tabulky. Nejdříve

zkontrolujeme, jestli je správně vybraná oblast (v našem případě, kdy

jsme vytvořili oblast pomocí „chytré tabulky“ se zobrazí jen název

Tabulka5, pokud jste vybrali oblast myší, tady si zkontrolujte, jestli je

to v pořádku), dále si můžeme vybrat, jestli kontingenční tabulku

chceme vložit na nový list, nebo někam do již existujícího listu. My se

např. rozhodneme, že chceme vložit novou kontingenční tabulku do

listu, který už máme (jmenuje se kontingenční tabulka-základ) a to do

buňky L5, nyní můžeme potvrdit OK.



85


Teď se nám zobrazilo okno, které děsí spoustu lidí, ale

uvidíte, jak je jednoduché s ním pracovat. Vlevo se bude

ukazovat, jak zrovna vypadá naše kontingenční tabulka

a vpravo si můžeme volit, jaký sloupec z původní

tabulky se má kde zobrazit. Pro náš příklad chceme, aby

Okres byl v řádku a v polích pro Hodnoty aby byly

uvedeny počty obyvatel celkem i podle pohlaví. Jak tam

ty jednotlivá pole dostaneme? Jednoduše levým

tlačítkem myši kliknu na nápis Okres a tahem ho

přemístím do bílého pole pod nápisem Řádky. Úplně

stejně přemístím i nápisy Počet obyvatel – celkem,

Počet obyvatel – muži a Počet obyvatel – ženy, ale

tentokrát do pole pod nápisem Hodnoty.



86


Vidíte? Excel sám

rovnou vložil součty pro

jednotlivá pole (sloupce

se jmenují Součet

z Počet obyvatel….).

Součet se dá

samozřejmě změnit i na

jinou funkci.

Uděláme to tak, že pro každý sloupec si rozklikneme šipku u jeho názvu v bílém poli pod nápisem Hodnoty – zvolíme

Nastavení polí hodnot a po otevření okna vybereme, kterou funkci chceme použít, např. průměr. Rovnou si zde

můžeme nastavit i formát čísla. Odsouhlasíme a máme hotovo.



87


Tak co říkáte? Není to úžasně rychlé a přehledné? Že ta

kontingenční tabulka není tak hrozná, jak jste mysleli? ;-)



88


ZÁVĚREM

Právě jste došli na konec tohoto e-booku. Věřím, že jste se naučili spoustu nových věcí a že je začnete postupně

zavádět do vašeho pracovního nebo i soukromého života.

Psala jsem tento e-book proto, abych vám pomohla ušetřit čas od věcí, které se dají udělat jednodušeji. Budu moc

ráda, pokud mi napíšete, jak se vám e-book líbil a jestli splnil účel, pro který jsem ho psala. Pokud jste byli spokojeni,

sledujte dál moje stránky www.lenkamiksatkova.cz, najdete na nich množství různých tipů a postupně budou

přibývat i další e-booky a výuková videa. Pokud budete chtít, připojte se ke mně také na Facebooku.

Ještě jednou vám děkuji, že jste si e-book zakoupili a že se učíte, jak si ještě více zamilovat Excel.

Lenka Mikšátková


http://lenkamiksatkova.cz/kontakt/


https://www.facebook.com/MameRadiExcel/

Date post:	11-Jan-2019
Category:	Documents
Upload:	ngominh
View:	223 times
Download:	0 times

Jak si ješte více zamilovat Excel - mameradiexcel.cz¡t... · PROHLÁŠENÍ: Tento materiál je...

Documents