JAK SI JEŠTĚ VÍCE ZAMILOVAT EXCEL

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á

www.lenkamiksatkova.cz ©

2016

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 JAK PRACOVAT S TÍMTO E-BOOKEM PODMÍNĚNÉ FORMÁTOVÁNÍ

1 2 3

PRAVIDLA ZVÝRAZNĚNÍ BUNĚK PRAVIDLA PRO NEJVYŠŠÍ ČI NEJNIŽŠÍ HODNOTY DATOVÉ PRUHY BAREVNÉ ŠKÁLY SADY IKON NOVÉ PRAVIDLO VYMAZAT PRAVIDLA SPRAVOVAT PRAVIDLA

4 7 8 11 13 17 17 18

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

19 22 26

KDYŽ IFS A NEBO IFERROR

26 28 31 33 35

VYHLEDÁVACÍ FUNKCE

36

SVYHLEDAT VVYHLEDAT POZVYHLEDAT

36 43 45

MATEMATICKÉ FUNKCE

47

ZAOKROUHLIT ABS

47 49

DATUM A ČAS

51

DNES MĚSÍC DATEDIF NETWORKDAYS

51 52 53 54

TEXTOVÉ FUNKCE

56

ZLEVA ZPRAVA ČÁST HLEDAT CONCATENATE CONCAT TEXTJOIN

56 58 60 62 64 66 68

ŘAZENÍ FILTROVÁNÍ

70 72

AUTOMATICKÝ FILTR SUBTOTAL

72 75

SOUHRNY, SESKUPENÍ A ODDĚLENÍ

78

SOUHRNY SESKUPENÍ A ODDĚLENÍ

78 81

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

83 88

www.lenkamiksatkova.cz ©

2016

Jak si ještě více zamilovat Excel

Lenka Mikšátková

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

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.

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

Lenka Mikšátková

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. Věřím, že v tomto e-booku najdete spoustu zajímavých informací, které Uvádím zde nástroje a funkce, které v Excelu považuji za nejužitečnější a vám pomůžou ve vaší každodenní práci a že si ještě více zamilujete Excel  které jsou zároveň vhodné pro mírně pokročilé uživatele. 1 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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.

2 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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í.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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? 


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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 FORMÁTOVÁNÍ DATOVÉ PRUHY.

JEDNOTLIVÝM

HODNOTÁM

PŘIŘADIT

POMOCÍ

PODMÍNĚNÉHO

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í.


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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 FORMÁTOVÁNÍ BAREVNÉ ŠKÁLY.

JEDNOTLIVÝM

HODNOTÁM

PŘIŘADIT

POMOCÍ

PODMÍNĚNÉHO

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 


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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 


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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 22 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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)


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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


2016


Lenka Mikšátková

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 


2016


Lenka Mikšátková

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:


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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 (VzorceLogické-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  ).


2016


Lenka Mikšátková

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“.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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“.


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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 .


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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 


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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 


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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)“.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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 


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková

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“.)


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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“.


2016


Lenka Mikšátková

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


2016


Lenka Mikšátková

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 VzorceTextové-ZPRAVA.


2016


Lenka Mikšátková

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


2016


Lenka Mikšátková

ČÁ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 


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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. 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 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 


2016


Lenka Mikšátková


2016


Lenka Mikšátková

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ě!  ZÁKLADNÍ PŘÍKLAD: MÁME POD SEBOU TŘI BUŇKY S TEXTEM A CHCEME JE SPOJIT DO JEDNÉ VĚTY.

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 


2016


Lenka Mikšátková


2016


Lenka Mikšátková

Ř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á  ). 70 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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. 71 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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?


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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 74 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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é 


2016


Lenka Mikšátková

Další příklad na filtrování (ne, na flirtování  ) najdete opět v cvičebním souboru k tomuto e-booku. 77 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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).


2016


Lenka Mikšátková

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“.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

Ú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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

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.


2016


Lenka Mikšátková

Tak co říkáte? Není to úžasně rychlé a přehledné? Že ta kontingenční tabulka není tak hrozná, jak jste mysleli? ;-) 87 www.lenkamiksatkova.cz ©

2016


Lenka Mikšátková

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á


2016

JAK SI JEŠTĚ VÍCE ZAMILOVAT EXCEL

Recommend Documents