Vysoká škola ekonomická v v Praze Univerzita třetího věku. Microsoft Excel Učební text pro předmět U054. Ing. Helena Benáčanová, CSc

Vysoká Vysokáškola školaekonomická ekonomickávvPraze Praze Univerzita Univerzitatřetího třetíhověku věku

Microsoft MicrosoftExcel Excel2010 2010 Učební Učebnítext textpro propředmět předmětU054 U054

Ing. Ing.Helena HelenaBenáčanová, Benáčanová,CSc. CSc.

Praha Praha2011 2011

Určeno pro studenty Univerzity třetího věku VŠE v Praze

© Helena Benáčanová, 2011 Typography © Helena Benáčanová, 2011 This edition © Tribun EU, 2011 ISBN 978-80-263-0069-4

3

Obsah Obsah .................................................................................................................. 3 1.

Základní pojmy ........................................................................................... 4

2.

Úvod do práce v EXCELU ......................................................................... 6

3.

2.1

Základní režimy práce.......................................................................... 6

2.2

Označování oblastí buněk .................................................................... 6

2.3

Příkazy a jejich použití......................................................................... 7

2.3.1.

Pás karet ........................................................................................ 7

2.3.2.

Panel nástrojů Rychlý přístup ....................................................... 9

Tvorba tabulky .......................................................................................... 11 3.1

4.

Vstup dat ............................................................................................ 11

3.1.1.

Vstup a kontrola konstantních dat .............................................. 11

3.1.2.

Další možnosti zadávání dat ....................................................... 15

3.1.3.

Vkládání výpočetních výrazů (vzorců) ....................................... 15

3.1.4.

Příklady vzorců ........................................................................... 16

3.1.5.

Relativní a absolutní odkazy....................................................... 19

3.1.6.

Chybová hlášení při vkládání vzorců ......................................... 20

3.1.7.

Standardní funkce ....................................................................... 21

Formátování tabulky ................................................................................. 30 4.1

Formát číselných hodnot .................................................................... 30

4.2

Formát textu ....................................................................................... 31

4.2.1.

Formát písma .............................................................................. 31

4.2.2.

Zarovnání textu ........................................................................... 31

4.2.3.

Ohraničení, barva výplně, barva písma ...................................... 32

4.2.4.

Podmíněné formátování .............................................................. 33

5.

Práce s listy ............................................................................................... 34

6.

Tvorba a úpravy grafů .............................................................................. 36 6.1

Tvorba grafu ....................................................................................... 36

6.1.1.

Graf výsečový ............................................................................. 36

6.1.2.

Graf sloupcový............................................................................ 39

6.2

Další možné úpravy grafů .................................................................. 39

7.

Řazení dat ................................................................................................. 41

8.

Filtrování dat ............................................................................................. 43

Kapitola 1: Základní pojmy

1.

Sešit

4

Základní pojmy

Základním dokumentem při práci se systémem Excel je sešit. Je to v podstatě soubor, se kterým pracujeme stejným způsobem jako s kterýmkoli jiným souborem pod operačním systémem – můžeme jej otevřít, uložit, zavřít, vytisknout. Sešit musí mít jméno – systém při otevření každého nového sešitu (volba SOUBOR NOVÝ PRÁZDNÝ SEŠIT) přiřadí standardní jméno SEŠITn, kde n je přirozené číslo. V žádném případě nedoporučuji toto implicitní pojmenování ponechat, ale ihned soubor uložit pod novým jménem (pomocí SOUBOR ULOŽIT JAKO). Systém automaticky přiřazuje příponu souboru (v případě verze 2007 a 2010 je to .xlsx, u verzí starších .xls). Sešit se automaticky otevírá do okna – viz obr. 1, na obrazovce je možné otevřít i více oken najednou a tak pracovat jak s různými částmi jednoho sešitu nebo současně i s více sešity. OBR. 1 OKNO APLIKACE MS EXCEL 2010

Panel Rychlý přístup

Pás karet

Záhlaví aplikace s názvem souboru

Řádek vzorců

Záhlaví sloupců Záhlaví řádků

Záložky listů

Stavový řádek s režimem

Vložit list

V rámci listu je k dispozici 1 048 576 řádků a 16 384 sloupců (od verze 2007 došlo tedy k výraznému zvýšení kapacity sešitu). Řádky jsou označovány pomocí čísel, sloupce pomocí písmen (A az Z a pokračují kombinací písmen AA až XFD). Dalším rozměrem v sešitu jsou listy – v jednom sešitu jich může být prakticky neomezeně – je to limitováno pouze kapacitou paměti. Při otevření nového sešitu obsahuje tento 3 listy pojmenované List1 až List3 (tento počet lze samozřejmě nastavit i jinak). Názvy listů jsou uvedeny na záložkách listů ve spodní části okna. Mezi listy se přepínáme klepnutím na tuto záložku.

Kapitola 1: Základní pojmy Buňka, odkazy

Buňka, obsah

5

Nejmenší oblast pro vstup dat je buňka – je průsečíkem řádku a sloupce. Abychom mohli s buňkami dále pracovat, je nutné je identifikovat. Používá se k tomu tzv. odkaz (někdy se používá i pojem adresa). Skládá se ze jména listu, vykřičníku a označení sloupce a čísla řádku. Příklady: LIST1!B25 25. řádek 2. sloupce v listu se jménem List1 (malá a velká písmena v názvu listu se nerozlišují) Výdaje!C1 1. řádek 3. sloupce v listu se jménem Výdaje Lze označit a pracovat i s více řádky či sloupci najednou, pak se tento prostor obvykle definuje jako oblast (blok). Např. odkaz A1:C10 znamená souvislou oblast 10 řádků v prvních 3 sloupcích. V případě, že odkazujeme na buňky v aktivním listu (ve kterém momentálně stojí kurzor), nemusíme uvádět odkaz na jméno listu. Oblast lze identifikovat jak pomocí výše uvedeného odkazu, tak ji lze pojmenovat i jménem (např. oblasti, ve které máme seznam našich přátel, můžeme přiřadit jméno Seznam). Co vše může buňka obsahovat? • Hodnotu - do buňky můžeme vložit buď číselnou hodnotu (celé číslo 100, desetinné 2,5, příp. další), nebo datum (obvykle ve tvaru dd.mm.rr – např. 15.9.2011) nebo text (např. Příjmení a jméno, rok 2011, 1. měsíc atd.). Pokud ponecháme buňku prázdnou, tak je ve smyslu čísla interpretována jako hodnota nula, ve smyslu textu jako prázdný text (nulový řetězec textu). • Vzorec – s konstantními hodnotami (uvedenými výše) lze provádět řadu operací – pokud jsou číselného charakteru, můžeme je sčítat, odčítat, násobit, dělit. Můžeme využít i řadu tzv. standardních funkcí – to jsou algoritmy výpočtů již v systému uložené a vkládají se pomocí svého jména a uvedení příslušných argumentů. Pokud jde o text, lze jej různě slučovat, rozdělovat, zjišťovat délku textu apod. • Formát (v buňce můžeme měnit barvu textu, barvu pozadí, můžeme ji orámovat, číselnou hodnotu můžeme vyjádřit např. v měně - € 15, nebo v procentech - 15 %, můžeme změnit formát vloženého datumu, např. na 15.IX, text lze různě zarovnávat apod.). • Komentář – poznámka k buňce, která se objeví při přiblížení kurzoru k buňce ve formě textového pole s vloženým obsahem.

Kapitola 2: Úvod do práce v EXCELu

Pracovní režimy

Vstup do buňky

Úpravy vstupu

2.

Úvod do práce v EXCELU

2.1

Základní režimy práce

Základním režimem, ze kterého vždy vycházíme je režim Připraven (indikace režimu je zobrazována v levé části stavového řádku ve spodní části okna). Při prvotním vkládání hodnot z klávesnice se režim Připraven mění na režim Zadání, při úpravách hodnot v buňkách se mění na režim Úpravy. Pozn. Nezapomínejte (na rozdíl od textového editoru), že každý vstup do buňky je nutné ukončit a pokračovat přesunem na další buňku. Lze to udělat pomocí klávesy ENTER (potom se automaticky kurzor přesouvá na další buňku ve směru, jak je to v systému přednastaveno – pokud si to uživatel nezmění, jde o přesun na další buňku ve sloupci směrem dolů). Pokud chceme pokračovat v jiném směru, lze klávesu ENTER vynechat a použít pro ukončení kurzorovou šipku příslušného směru. Obsah buňky se zobrazuje při vkládání dat jak v řádku vzorců, tak i ve vlastní buňce. Po vložení či ukončení příp. editace se obsah objevuje v řádku vzorců pouze tehdy, je-li buňka aktivní (tzn. stojí-li v ní kurzor.). Než vstup do buňky ukončíme, lze vkládané hodnoty jakkoliv ještě upravit a vstup příp. zrušit klávesou ESC. Po ukončení vstupu jsou opravy možné několika způsoby: • pokud chceme původní hodnotu přepsat úplně novou hodnotou (většinou u čísel), lze novou hodnotu vložit a tato přepíše hodnotu původní, • hodnotu můžeme i vymazat – pokud chceme smazat pouze obsah, lze využít klávesu DEL, pokud má hodnota ještě např. formát, musíme již použít příkaz – viz dále kap. 3, • pokud pouze vloženou hodnotu upravujeme, je nutné pro opravy vyvolat režim Úpravy. Lze to buď klávesou F2 nebo poklepáním na buňku (pak se kurzor umístí do buňky a vloženou hodnotu lze modifikovat), nebo klepnutím do řádku vzorců (řádek těsně nad záhlavím sloupců), pak se kurzor přemístí do tohoto řádku a úpravy můžeme provést. Pozn. Ukončení úprav je možné pouze klávesou ENTER (neplatí varianta kurzorové šipky jako u vstupu hodnot).

2.2 Označování buněk

6

Označování oblastí buněk

U některých operací potřebujeme před zadáním příslušného příkazu označit více buněk, pro které se bude operace provádět. Uvedeme si některé příklady (lze to realizovat myší, ale v některých případech je efektivnější využití zkratkových kláves): • označení buňky – klepnutí myší na buňku, • označení oblasti – tažení myší napříč z rohu do rohu (lepší je začít od pravého spodního rohu, zejména pokud oblast přesahuje rámec obrazovky). Pokud je oblast souvislá (nepřerušená prázdnými řádky či sloupci), je jednodušší použít kombinaci kláves CTRL *,


7

•

označení celého sloupce/řádku - klepnutím do záhlaví sloupce či řádku, • označení všech buněk v listu (klepnutím na políčko vlevo mezi záhlavím sloupců a řádků (příp. lze použít i klávesy CTRL A), tento případ používejte pouze výjimečně, protože označuje celý list, • označení nesouvislé oblasti – první část označíme tažením myší, pak stiskneme a držíme klávesu CTRL a pokračujeme tažením v označování další/dalších buněk či oblastí. Zrušení označení lze realizovat klepnutím myší do kterékoliv buňky ať v označené či neoznačené oblasti.

2.3 Příkazy

Příkazy a jejich použití

Samotný vstup hodnot je jen začátek práce v Excelu. S těmito hodnotami chceme nadále pracovat, upravovat je, sumarizovat, příp. graficky interpretovat. K tomu potřebujeme příkazy. Příkazy najdeme jednak • Na nabídce na pásu karet, kde jsou rozdělené do jednotlivých logických skupin soustředěných pod tzv. kartami. Každá karta pak odpovídá určitému typu činnosti, např. Zobrazení, Vložení, Soubor apod. – viz obr. 2. Některé příkazy jsou umístěny duplicitně na více kartách. OBR. 2 PÁS KARET

• •

Možnosti pásu karet

Při využití tzv. místní nabídky (vyvolává se pravým tlačítkem myši a zobrazuje nabídku příkazů vždy kontextově). Při využití tzv. zkratkových kláves (např. CTRL S pro uložení soubor nebo CTRL C pro kopírování apod.).

2.3.1. Pás karet Pás karet, který se objevil poprvé ve verzi Excel 2007, usnadňuje vyhledání příkazů a funkcí, které byly v minulosti ukryty ve složitých nabídkách a panelech nástrojů. Pás karet lze různě měnit a přizpůsobovat, to ovšem je určeno spíše zkušenějším uživatelům. Jednotlivé karty (Soubor, Domů, Vložení, Rozložení stránky, Vzorce, Data, Revize, Zobrazení) jsou členěny do skupin, jejichž název je uveden ve spodní části karty. Skupiny pak obsahují: • Tlačítka s ikonkou pro bezprostřední provedení určité konkrétní akce (např. část karty Zobrazení – viz obr. 3.


8

OBR. 3 TLAČÍTKA S IKONOU

•

Tlačítka s ikonkou a rozbalovací šipkou, která zobrazí další detailnější nabídku, viz obr. 4.

OBR. 4 TLAČÍTKA

•

ROZBALOVACÍ ŠIPKOU

Tlačítka v pravé dolní části karty, pomocí kterých se zobrazí dialogové okno pro výběr všech operací uvedených pod jednotlivými tlačítky v kartě nebo pro zobrazení příslušného podokna (např. pro schránku), viz obr. 5.


9

OBR. 5 UKÁZKA TLAČÍTKA V PRAVÉ DOLNÍ ČÁSTI KARTY

•

Příp. políčka s číselníkem (např. měřítko pro nastavení tisku apod.) – viz obr. 6.

OBR. 6 POLÍČKO S ČÍSELNÍKEM

Pozn. Přidržením myši u konkrétního objektu na kartě se objeví „bublinka“ nápovědy k akci příslušející tomuto objektu. 2.3.2.

Panel nástrojů Rychlý přístup

Panel nástrojů Rychlý přístup Panel nástrojů Rychlý přístup (viz obr. 7) je přizpůsobitelný panel nástrojů obsahující sadu příkazů, které nezávisí na kartě aktuálně zobrazené na pásu karet. Tento panel lze přesunout z jednoho ze dvou možných umístění (nad a pod pásem karet) a lze na něj příp. přidávat i další tlačítka představující příkazy. OBR. 7 PANEL NÁSTROJŮ RYCHLÝ PŘÍSTUP

Na tomto panelu jsou standardně umístěna 3 tlačítka • Uložit (CTRL S) - uloží soubor pod zadaným jménem, na místo, odkud byl otevřen – pokud soubor ještě nebyl uložen, nabídne okno Uložit jako. • Zpět Psaní (CTRL Z) (tlačítko umožňuje vrátit zpět jednu nebo více akcí – pokud chcete vybrat více akcí, musíte kliknout na


10

šipku vpravo od tlačítka a vybrat akce ze seznamu – až 100 možností). Jde o velmi užitečnou a často využitelnou funkci, např. provedeme-li nějaký chybný krok a chceme tuto operaci anulovat. Bohužel existují však i operace, které vrátit nelze (např. z nabídky SOUBOR). • Opakovat Psaní – zopakuje poslední provedenou akci. Pozn. Termín Psaní v popisku na tlačítku je trochu zavádějící, nejde jen o obnovení či anulaci psaní, ale obecně jakékoliv akce (pokud je to povoleno) – např. anulace výmazu, nastavení formátu, seřazení dat apod.

Kapitola 3: Tvorba tabulky

3.

Tvorba tabulky

3.1

Vstup dat

3.1.1.

Typy konstant

Rozšíření sloupce

Práce s datumem

11

Vstup a kontrola konstantních dat Nejprve musíme tabulku naplnit daty. Do buněk lze vložit buď konstantní data nebo výpočetní výrazy. Konstanty mohou být • Číselné (numerické) – např. celá nebo desetinná čísla (zadávají se s desetinnou čárkou – např. 0,5), procenta (nejjednodušší je vkládat přímo včetně symbolu % - např. 50%, 2,5% apod.). Mezi číselné konstanty lze zařadit i datum – viz dále. • Textové – ty lze rozdělit na abecední (alfabetické) – např. Jméno, Rok narození apod., které jsou kombinací písmen a příp. mezer nebo abecedněčíselné (alfanumerické), které jsou kombinací písmen a číslic (např. rok 2011, 1. měsíc apod.). Vkládání dat již bylo zmíněno v kap. 2.1. Při vstupu konstant je obsah řádku vzorců i buňky totožný. Vkládané konstanty jsou ihned v buňce automaticky zarovnávány – číselné hodnoty zleva a ostatní zprava. Toto zarovnání lze ovšem dodatečně podle potřeby i změnit. Pozn. Zejména u vkládání abecedních či abecedněčíselných hodnot se může stát, že standardní šíře sloupce nepostačuje. Pokud v buňce vpravo od tohoto vloženého textu nejsou žádná data (zůstala prázdná), text se zobrazí celý (problém vznikne pouze při použití ohraničení, které text neprotne), jinak se „uřízne“. Je tedy nutné sloupec rozšířit. Kurzor myši nastavíme na pravou hranici sloupce v záhlaví sloupců, kurzor se změní na oboustrannou šipku a tou lze při stisknutém levém tlačítku pohybovat oběma směry a tak šíři sloupce upravovat. Pokud myší na tuto hranici poklepeme, sloupec se automaticky rozšíří podle největší hodnoty, která byla ve sloupci nalezena. Podobným způsobem lze řešit i výšku řádku (pouze kurzor myši umístíme do spodní hranice v záhlaví řádku). Pozor! Sloupce nerozšiřujeme podle záhlaví tabulky (nadpisů sloupců), ale podle hodnot v tabulce. Nadpisy sloupců se spíše snažíme v buňce zalomit (viz kap. 4.2.2). Jak se pracuje s datumem? S hodnotami typu datum lze pracovat dvěma způsoby • buď jako s textem (pak slouží spíše pro identifikaci, nelze s ním pracovat jako se skutečným datumem) – např. 10.10. nebo 10. října – pokud datum vložíme v této podobě, systém nerozpozná, že jde o skutečné datum, • nebo jako s položkou skutečně datumovou – pak je nutné datum vložit ve správném formátu. Nejpřirozenější je vkládání datumu ve formátu dd.mm.rrrr, tedy jednotlivé části (dd - den, mm měsíc, rrrr – rok) oddělené tečkami. Pokud vynecháme část roku, jde o aktuální rok a pak musíme vynechat tečku za měsícem, např. 10.9, 24.12 apod. Datum lze vložit i v kterémkoliv


12

z následujících formátů (viz dialogové okno Formát buňky Číslo Datum) 10.9.11 10-9-2011 10-9. (u tohoto formátu je nutné tečku uvést) 10. září (aktuální rok lze vynechat) září 10 10. září 10:30 (/tj. včetně uvedení času – zde je oddělovačem obvykle dvojtečka). Po vstupu do buňky se uvnitř buňky uloží numerická hodnota, která odpovídá počtu dní od 1.1.1900 (pokud není v nastavení systému změněno), ale na obrazovce (jak v buňce tak i v řádku vzorců vidíme vložené datum). Např. u datumu 15.9.2011 je to hodnota 40801. Pokud bychom tuto hodnotu chtěli zjistit, lze vymazat formát buňky (na kartě Domů v sekci Úpravy použitím tlačítka Vymazat – a po kliknutí na šipku vpravo vybrat ze seznamu Vymazat formáty). Pokud bylo datum zadáno skutečně ve formě datumu, lze jednotlivé hodnoty využít ve vzorcích (např. mezi sebou odečítat, přičítat k nim konstanty apod.) Pozn. Někdy se stává, že systém po vstupu vzorce do buňky, uvede v buňce automaticky datum (přestože očekáváme např. číselnou hodnotu). Pak je nutné buď buňku naformátovat jako číslo (karta Domů sekce Buňky tlačítko Formát záložka Číslo volba Číslo a upravit počet desetinných míst – standardně se nabízejí dvě), nebo formát vymazat (karta Domů, sekce Úpravy, tlačítko Vymazat, volba Vymazat formáty). Též je vhodné, pokud mažeme buňky obsahující datum, nepoužít jen klávesu DEL, která maže pouze obsah, ale použít tlačítko Vymazat a zvolit Vymazat vše. Kontrola vstupních dat Vložená data je třeba řádně zkontrolovat, protože sebelepší algoritmus Jak data zkontrolovat výpočtu z chybných dat správný výsledek nezajistí. Kontrolu dat lze přenechat systému. Označíme oblast dat, kde bude nastavena kontrola a zvolíme kartu Data a v ní sekci Datové nástroje a tlačítko Ověření dat. Zobrazí se dialogové okno dle obr. 8. Výběrem položky ze seznamu Povolit můžeme nastavit konkrétní typ kontroly (záleží tedy na typu vstupní hodnoty). Pokud vybereme celé, nebo desetinné číslo, datum nebo čas, systém nabídne možnost zadání podmínky (rovno, menší, menší nebo rovno zadané hodnotě apod. nebo zadání rozsahu od do, kde se zadává minimální a maximální povolená hodnota). Kontrola na délku textu se využívá v Excelu minimálně, volba Vlastní je určena již pokročilým uživatelům, kdy kontrolní algoritmus zadávají pomocí vzorce. Zajímavá je volba Seznam, kterou ukážeme na dalším příkladu.


13

OBR. 8 DIALOGOVÉ OKNO OVĚŘENÍ DAT

Příklad 1

Kontrola na seznam

Příklad 1 Vytváříme tabulku přehledu výdajů na domácnost a do jednoho ze sloupců chceme vložit typ výdaje. Abychom zajistili, že všechny stejné typy výdajů budou zadány shodně (např. jídlo, auto, ostatní a ne jednou jídlo, jednou potraviny jednou výdaje na jídlo apod., protože pak bychom následně data velmi těžko zpracovávali), zadáme kontrolu vstupu na výčet těchto hodnot. Povolené hodnoty typu výdajů zadáme do pomocného sloupce (tento sloupec musí být ve stejném listu, jako je kontrolovaná tabulka. Pak opět vyvoláme funkci Ověření dat, zvolíme Povolit Seznam a ve vstupním poli označíme oblast povolených typů výdajů, kterou máme v listu vloženu – viz obr. 9. Ve sloupci, ve kterém je tato kontrola nastavena se v buňkách objeví stahovací šipky. Kliknutím na tuto šipku se rozvine seznam povolených hodnot a uživatel pak může požadovanou hodnotu klepnutím myší vybrat (nemusí tedy nic vkládat a navíc je zajištěna kontrola, že na vstupu se neobjeví žádná hodnota mimo povolený seznam – viz obr. 10). Pokud se uživatel pokusí zadat hodnotu, která kontrolou neprojde, objeví se dialogové okno z obr. 11.


14

OBR. 9 NASTAVENÍ KONTROLY NA SEZNAM

OBR. 10 ZADÁVÁNÍ HODNOT ZE SEZNAMU

OBR. 11 UPOZORNĚNÍ NA CHYBU VSTUPU

Toto je předdefinovaná forma hlášení chyby, kterou je možné i změnit. Další možná V dialogovém okně Ověření dat jsou celkem 3 záložky. V první záložce se nastavení v rámci nastavuje forma kontroly, ve druhé (Zpráva při zadávání) lze určit text, který kontroly se bude objevovat v kontrolované oblasti u jednotlivých buněk ve formě „bublinové nápovědy“, třetí záložka (Chybová hlášení) umožňuje nastavit „přísnost“ kontroly a příp. změnit i předdefinované chybové hlášení, které jsme viděli na obr. 11. Existují celkem tři možnosti reakce na chybu – stop (nepřísnější, systém nedovolí v žádném případě vložit jinou než správnou hodnotu, příp. lze vstup jedině zrušit), varování (systém po počátečním varování umožní přesto vložit i hodnotu, která původně kontrolou neprošla) a informace (nejmírnější forma – pouze na chybu upozorní, ale vstup ponechá).


15

3.1.2.

Další možnosti zadávání dat V EXCELU lze využít i možnosti zadávat různé posloupnosti dat. Vyplňování Číselné řady se většinou používají pro označení pořadí např. v 1. sloupci řad tabulky (tímto způsobem lze zadat i posloupnost datumů). Popíšeme si jeden z možných způsobů. Vložíme první dvě čísla (např. čísla 1 a 2 příp. datumy 1.1.2011 a 2.1.2011 do sloupce pod sebe). Obě čísla označíme a tažením levým tlačítkem myši za úchyt v pravé spodní části okraje buňky číselnou řadu vytvoříme (směrem dolů řada narůstá, směrem nahoru klesá, obdobně je to ve směru doprava a doleva, pokud máme hodnoty vloženy v řádku). Pokud bychom zadali dvě čísla s intervalem vyšším než jedna (např. 1 a 5), bude se vytvářet řada s tímto intervalem (1,5,9,13,17 apod.). Pokud chceme vytvářet řadu abecedněčíselnou (např. 1. měsíc, 2. měsíc), stačí vložit první hodnotu (pokud nechceme interval vyšší než 1) a tažením myší za pravý spodní úchyt příslušným směrem řadu vytvoříme. Pozor! Pokud zapíšeme text 1.měsíc bez mezery, text se bude pouze kopírovat (pokud je číslo za textem, tak toto omezení neplatí). Předdefinované Lze vkládat i celé seznamy. V EXCELu jsou 4 seznamy předdefinovány – seznam měsíců (v plném tvaru, nebo číselně) a dny v týdnu (opět ve dvou seznamy formách). Stačí tedy opět zadat první název měsíce (např. leden) a tažením myší opět vytvoříme řadu všech měsíců. Uvedeme-li dva první měsíce s větším intervalem, pak se opět seznam vytváří s tímto definovaným intervalem (např. každý čtvrtý měsíc – tedy začátky čtvrtletí). Jméno měsíce je nutné zadat správně (bez překlepu a nepřechýleně), jinak se seznam nevytváří a text se kopíruje. Zkušenější uživatelé si mohou vytvářet i vlastní seznamy (na kartě Soubor pod tlačítkem Možnosti (zde jsou uvedeny všechny přednastavené vlastnosti systému a je možné je i podle potřeby měnit) v dialogovém okně vybereme volbu Upřesnit a zde najdeme oddíl Obecné a tlačítko Upravit vlastní seznamy. 3.1.3. Vzorce

Vkládání výpočetních výrazů (vzorců) Z vložených konstantních dat můžeme pomocí vzorců získat řadu dalších hodnot. Vzorce jsou dvojího charakteru: • Aritmetické výrazy zapsané do buňky po uvedení znaku rovnítko (=) pomocí příslušného aritmetického výrazu (operandy vyjádřené pomocí odkazů – adres, propojené příslušnými operátory). Operátory používané v EXCELu: + (sčítání), - (odčítání), * (násobení), / (dělení), ^ (mocnění) Pro slučování (spojování) textů se používá operátor &. V relačních výrazech se používají relační operátory <,>,<= >= a <> (nerovno). Musí se dodržovat matematické priority, v případě jejich porušení je nutné použít kulaté závorky (systém kontroluje jejich párovost, jinak ohlásí chybu). • Standardní funkce vložené do buňky (jde o algoritmy vložené v systému a jejich vyvolání se realizuje uvedením jména funkce a zapsáním požadovaných argumentů). Standardní funkce se vkládají obvykle výběrem ze seznamu funkcí, který se vyvolá buď klepnutím na tlačítko Fx ve střední části řádku vzorců nebo


16

na kartě Vzorce klepnutím na první tlačítko zleva Fx (vložit funkci). Příklady vzorců Abychom si přiblížili vkládání vzorců, využijeme jednoduchou tabulku obsahující čísla a EXCEL ve funkci kalkulačky – viz ukázka. Příklad 2 3.1.4.

Příklad 2

Hodnota 1 Hodnota 2 Součet 10 5 5 2 4 8 8 9

Rozdíl

Součin

Podíl

Nejprve vložíme záhlaví jednotlivých sloupců (můžeme však vyplňovat vstupy i po jednotlivých sloupcích). Do buňky A1 vložíme text Hodnota 1, vstup ukončíme buď klávesou ENTER (pak se ovšem kurzor přesune do A2 a musíme jej přesunout zpět do B2). Lepší a rychlejší je ukončit vstup pomocí kurzorové šipky vpravo a tím se dostáváme přímo do buňky B2. Vložíme opět text (pokud použijeme tzv. výplň řady – viz kap. 3.1.2, nemusíme druhý text vůbec z klávesnice zadávat, ale pouze ho tažením vyplníme). Stejným způsobem pokračujeme u všech dalších sloupců. Pak vyplníme hodnoty v prvních 2 sloupcích - lze vkládat po řádcích nebo v rámci sloupce. 3.1.4.1.

Způsob vkládání vzorců

Kurzor nastavíme do buňky C2 a vyplníme první vzorec sčítání hodnot v prvních dvou sloupcích. Vložíme z klávesnice rovnítko (=), pak klepneme do buňky obsahující první číslo ve sloupci A (do buňky se vloží adresa A2), vložíme z klávesnice znaménko + (pro sčítání) a klepneme do prvního čísla ve sloupci B (do buňky se vloží odkaz B2). Celý vzorec tedy vypadá takto: =A2+B2 Klávesou ENTER vstup potvrdíme – v buňce C2 se výpočet ihned provede a objeví číslo 15, které je součtem čísel v prvním a druhém sloupci, obsahem řádku vzorců zůstává vložený vzorec. Obdobným způsobem postupujeme v ostatních sloupcích, pouze ve vzorci dochází vždy k příslušné změně aritmetického operátoru (viz 3.1.2). 3.1.4.2.

Možnosti kopírování

Vložení vzorců

Kopírování vzorců

Nyní máme vyplněny výpočty v prvním řádku a potřebujeme je vložit do zbývajících 3 řádků. Opakovaně je vkládat by bylo velmi neefektivní, předpokládejme, že reálné datové tabulky v EXCELu jsou řádově mnohem větší. Využijeme tedy pro vložení stejného vzorce do celého sloupce funkci kopírování (stejným způsobem lze kopírovat i v řádku). Lze nakopírovat vzorce pouze v jednom samotném sloupci, v našem případě je však efektivní kopírovat vzorce ve všech 4 sloupcích najednou. Protože jde o souvislou oblast, je nejlépe použít kopírování tažením myší. Kurzor postavíme do buňky C2 (první vzorec sčítání) a při stisku tlačítka myši označíme všechny buňky od C2 do F2. Myší uchopíme buňku F2 v jejím pravém spodním okraji (v místě


Kopírování pomocí schránky

Kopírování nesouvislé oblasti

17

tzv. bodu úchytu) a táhneme směrem dolů až do F5. Vzorce ve všech 3 sloupcích se nakopírují. Kopírování lze realizovat dvěma způsoby: tažením myší (viz náš příklad) nebo pomocí schránky (pokud máme problém s myší nebo v těch případech, kdy nekopírujeme v souvislé oblasti – např. potřebujeme kopírovat ze sloupce F do sloupce K). Kurzor umístíme do buňky F2, stiskneme kombinaci kláves CTRL C nebo použijeme tlačítko Kopírovat na kartě Domů v sekci Schránka (příp. použijeme volbu Kopírovat z místní nabídky na pravém tlačítku myši). Obsah buňky se uloží jak do schránky Windows, tak do schránky Office. Rozdíl mezi nimi je v tom, že schránka Windows může obsahovat pouze 1 vložený objekt, zatímco schránka Office umožňuje vložení více objektů, které si můžeme zobrazit v seznamu vyvolaném na kartě Domů v sekci Schránka. Tato schránka se však využívá spíše ke kopírování mezi aplikacemi Office, v samotném Excelu se prakticky moc nepoužívá. Nyní přesuneme kurzor do místa cíle (např. buňka K2, nebo chceme-li kopírovat do celého sloupce, tak označíme oblast K2:K5) a klávesou ENTER obsah buňky F2 vložíme. Schránka Windows se vymaže, ale ve schránce Office uložen obsah zůstává i nadále. V místě cíle kopírování můžeme použít i operaci Vložit (pomocí kláves CTRL V nebo tlačítkem Vložit na kartě Domů). Pak se ani obsah schránky Windows nesmaže a můžeme označovat další cíle a daný obsah znovu a znovu kopírovat. Pozn. Pokud kopírujeme např. obsah celého sloupce/řádku, pak tento před kopírováním označíme, ale v místě cíle umístíme kurzor pouze do levého horního rohu kopírované oblasti (nevyznačujeme opět celý sloupec/řádek). Pozn2. Pokud jsou kopírované buňky i naformátovány, kopíruje se i formát na úrovni buňky (ohraničení, barva pozadí, textu apod.). Je vhodné proto před vlastním kopírováním mít formáty již hotové. Nekopíruje se však formát na úrovni šíře sloupce či výšky řádku. Pozn3. Pokud bychom potřebovali zkopírovat data z nesouvislé oblasti (např. ze sloupce A a C), pak se zdrojová data pro kopírování označují s pomocí klávesy CTRL. EXCEL umožňuje kopírovat pouze nesouvislé oblasti, které mají stejný rozměr (tj. stejný počet řádků či sloupců). Jinak se objeví chyba uvedená na obr. OBR. 12 CHYBA PŘI KOPÍROVÁNÍ NESOUVISLÉ OBLASTI

3.1.4.3.

Kopírovat vložit jinak

Další možnosti kopírování

Nejčastější využití kopírování je pro kopírování vzorců. Pokud kopírujeme vzorec, tak v místě cíle se uloží opět vzorec a přepočítá nové vstupní hodnoty. Někdy je však třeba kopírovat vypočtené hodnoty jako


18

konstanty, aby se v místě cíle již nepřepočítávaly. Postup je stejný, pouze při vložení v cíli využijeme možnosti Vložit jinak. Na kartě Domů v sekci Schránka klepnutím na šipku pod tlačítkem Vložit vyvoláme seznam možných akcí – viz obr. 13. OBR. 13 RŮZNÉ MOŽNOSTI VLOŽENÍ KOPÍROVANÝCH DAT

Význam jednotlivých ikonek lze zjistit přidržením ukazatele myši u ikonky. U prvního oddílu – Vložit je zajímavá ikonka Ponechat šířky sloupců zdroje, která zkopíruje včetně obsahu a formátu i šíři sloupce. Poslední volba Transponovat zamění řádky a sloupce kopírované oblasti. V druhém oddílu Hodnoty je důležitá ikonka Hodnoty, která kopíruje do místa cíle vypočtené hodnoty, nikoliv vzorce. V třetím oddílu je zajímavá ikonka Formát, která slouží ke kopírování pouze formátu buňky (nikoliv obsahu). Funkci této ikonky lze však nalézt jednodušeji přímo v sekci Schránka na kartě Domů pod ikonkou tzv. štětečku. Klepnutím na štěteček se formát uloží do schránky a opětovným klepnutím myší v místě cíle se formát zkopíruje. Pokud na tlačítko štětečku poklepeme, můžeme kopírovat opakovaně do dalších a dalších cílových buněk až do stisknutí klávesy ESC (nebo opětovným klepnutím do štětečku). Poslední oddíl Vložit jinak – klepnutím se vyvolá dialogové okno (známé z předchozích verzí (zejména Excel 2003), kde je řada akcí stejných jako na výše uvedených ikonkách a obsahuje i další možnosti, které však pro začátky práce s Excelem nejsou nijak významné – viz obr. 14. 3.1.4.4.

Přesuny dat

Přesuny

Obdobným způsobem jako při kopírování se postupuje při přesunech. V místě zdroje použijeme klávesy CTRL X nebo tlačítko Vyjmout na kartě Domů v sekci Schránka (příp. na pravém tlačítku myši). Výsledek akce na rozdíl od kopírování je takový, že přesouvaný obsah v původním místě zmizí a přesune se do místa cíle.


19

OBR. 14 DIALOGOVÉ OKNO VLOŽIT JINAK

3.1.5.

Druhy odkazů

Relativní a absolutní odkazy Jistě jste si povšimli, že při kopírování vzorců se v místě cíle odkazy na jednotlivé operandy modifikují. Pokud jsme kopírovali vzorec ze sloupce F (=A2/B2) do sloupce K, pak v buňce K2 se objeví vzorec =F2/G2. Odkazy na operandy se přizpůsobily místu cíle – tomuto typu odkazů se říká relativní odkazy. Příklad relativního odkazu Zdrojový odkaz Kopírování ve sloupci Kopírování v řádku A1 A2 B1 B1 B2 C1 Ne vždy nám však tento způsob vyhovuje. Musíme proto použít buď odkazy smíšené nebo absolutní. Znamená to, že část nebo celý odkaz se při kopírování nemění. Části odkazu, která musí zůstat fixní, předchází symbol $. Tento symbol se nevkládá z klávesnice, ale využije se funkční klávesy F4, která postupně při klepnutí na tuto klávesu mění odkaz (např. A1) takto: $A$1 A$1 $A1 A1 Příklady smíšených odkazů Zdrojový odkaz Kopírování ve sloupci Kopírování v řádku $A1 A2 A1 $B1 B2 B1 A$1 A1 B1 B$1 B1 C1 Příklad absolutního odkazu Zdrojový odkaz Kopírování ve sloupci Kopírování v řádku $A$1 A1 A1 $B$1 B1 B1 Uvádím příklad nezbytného použití smíšeného (příp. absolutního) odkazu. Počítáme % podíly jednotlivých pracovníků na celkové mzdě. Vzorec v buňce C2 je =B2/B6*100 (v kap. o formátování si pak uvedeme i vhodnější zápis vzorce)

Kapitola 3: Tvorba tabulky Příklad 3

Příklad 3 Pracovník Bartoníček Bínová Cincibus Koudelka Celkem

Použití smíšených a absolutních odkazů

Pojmenování oblasti

Mzda 15000 14200 8000 18000 55200

% podíl na celk. mzdě 27,17 #DĚLENÍ_NULOU! #DĚLENÍ_NULOU! #DĚLENÍ_NULOU! #DĚLENÍ_NULOU!

V prvním řádku se vzorec vypočítá správně, ovšem při nakopírování do dalších řádků se objeví chybová zpráva (nejprve se objeví ####### a teprve po rozšíření sloupce se objeví v buňce shora uvedená zpráva). Znamená to, že při nakopírování vzorce s relativními odkazy se odkaz ve jmenovateli změnil na buňku B7 a ta je prázdná (je Excelem interpretována jako hodnota nula). Dochází tedy k pokusu o dělení nulou, což ani EXCEL nedokáže. Je tedy třeba vzorec opravit. V buňce C2 vyvoláme editaci (nejlépe klepnutím do řádku vzorců), nastavíme kurzor na odkaz B6 a stiskneme klávesu F4. Tím se z odkazu relativního stane odkaz absolutní (jsou doplněny symboly $ před obě části odkazu – $B$6) a po nakopírování vzorce se tento odkaz na dalších řádcích ve vzorci nemění. Dělíme tedy vždy číslem v buňce B6. V tomto příkladu lze použít i smíšený odkaz, kdy symbol $ umístíme před číslo řádku (B$6), a to stiskem klávesy F4 dvakrát. Všechny ostatní varianty by byly v našem případě chybné. 3.1.6.

Chybové stavy

20

Chybová hlášení při vkládání vzorců Pokud dojde při vyhodnocování vzorce vloženého do buňky k chybě, objeví se v buňce místo očekávané vypočtené hodnoty některý z následujících zápisů (uvádíme pouze ty nejčetnější): #HODNOTA! Byl použit chybný typ operandu (většinou jsme ve výrazu použili odkaz na buňku obsahující text, nikoliv číselnou hodnotu). Zajímavé je, že při použití standardní funkce je buňka obsahující text v argumentu ignorována a chyba se neobjeví. #NÁZEV? Ve vzorci jsme použili chybné jméno (název). V EXCELU rozlišujeme jména standardních funkcí a oblastí buněk. Pokud tedy vkládáme jméno standardní funkce z klávesnice (nevyužijeme okno Vložit funkci), pak může dojít k překlepu a systém na to reaguje touto chybou. V EXCELU můžeme i buňky či celé oblasti pojmenovat jménem (odkazujeme se na ně pak ve vzorcích nikoliv pomocí odkazů, což je také možné, ale pomocí tohoto jména – pokud použitý název neodpovídá, dochází opět ke shora uvedené chybě). V řadě případů jsou tyto vzorce pak přehlednější). Jméno přiřadíme oblasti takto: oblast si označíme pomocí myši a na kartě Vzorce použijeme tlačítko Definovat název v sekci Definované názvy – viz obr. 15.


21

OBR. 15 DIALOGOVÉ OKNO PRO DEFINOVÁNÍ NÁZVU OBLASTI

V tomto okně pak zadáme název oblasti (ve vstupním poli Odkaz na vidíme odkazy na námi vybranou oblast). Pokud potřebujeme jméno oblasti do vzorce vložit, nevkládáme jej zápisem z klávesnice, ale využijeme výběr ze seznamu jmen, který vyvoláme klávesou F3 – viz obr. 16. OBR. 16 DIALOGOVÉ OKNO VLOŽIT NÁZEV

Správce názvů

Pokud potřebujeme názvy odstraňovat či redefinovat, lze využít tlačítko Správce názvů na kartě Vzorce. Definovat nový název lze i zde. #DĚLENÍ_NULOU! Dochází k pokusu o dělení nulou. Může k němu dojít buď kvůli nesprávnému použití relativních či smíšených odkazů ve vzorci nebo i tam, kde používáme operaci dělení, ale data v buňce jmenovatele nejsou naplněna. Doporučujeme proto vždy u vzorců dělení použít funkci KDYŽ, která v případě nulového jmenovatele výpočet neprovede – viz dále kap. 3.1.7.2. 3.1.7.

Standardní funkce Jak již bylo dříve řečeno, standardní funkce představují určité algoritmy výpočtu uložené v systému. Je jich celkem 341 a jsou rozděleny do několika skupin – viz obr. 17. Dialogové okno uvedené na obrázku se vyvolá tlačítkem Fx z řádku vzorců nebo na kartě Vzorce prvním tlačítkem Fx Vložit funkci. Obecný zápis funkce je =Název funkce (seznam argumentů).

Kapitola 3: Tvorba tabulky Vkládání standardních funkcí

22

Argumenty jsou v seznamu oddělovány pomocí znaku středník (;), některé funkce mají argumentů více, některé mohou být i část argumentů nepovinnou a existují i funkce bez argumentů. Pokud vkládáme funkci pomocí výběru z dialogového okna Vložit funkci, pak se nemusíme o pravidla zápisu funkce nijak starat a pouze vyplňujeme jednotlivá vstupní pole, jak nám je systém nabízí. Musíme pouze vědět, kterou funkci vybrat a zadat argumenty v požadované formě a počtu. Pomocí standardních funkcí se vytvářejí i velmi složité vzorce (kombinované příp. i s klasickými aritmetickými operacemi) a funkce se dají do sebe i vnořovat (v Excelu 2010 až 64 vnoření). OBR. 17 DIALOGOVÉ OKNO VLOŽIT FUNKCI

3.1.7.1.

Tlačítko AUTOSUM

Standardní funkce SUMA a další funkce na tlačítku AUTOSUM

Snad nejvíce používanou standardní funkcí je funkce SUMA. Právě proto je jí přiřazeno speciální tlačítko AUTOSUM (na kartě Domů v sekci Úpravy). Pokud klepneme na šipku pod tlačítkem, lze vyvolat seznam dalších frekventovaných funkcí a na posledním řádku je možné vyvolat i okno dalších funkcí – viz obr. 18. OBR. 18 SEZNAM FUNKCÍ NA TLAČÍTKU AUTOSUM

Příklad 4

Příklad 4 V buňkách B2 až B5 máme 4 číselné hodnoty a v buňce B6 je chceme sečíst. Pro součet více než 2-3 operandů v souvislé oblasti je rychlejší a efektivnější použít standardní funkci (s využitím tlačítka AUTOSUM) namísto aritmetické operace sčítání. Jsou dvě možnosti řešení:


23

a) Žádná data neoznačujeme, kurzor postavíme do buňky, ve které chceme realizovat součet a stiskneme AUTOSUM. Systém sám vloží funkci SUMA a nabídne oblast sčítaných hodnot (hranici tvoří prázdná buňka nebo buňka obsahující text). Souhlasíme-li s nabízeným rozsahem, potvrdíme vzorec klávesou ENTER, nebo můžeme nabízenou oblast myší upravit – viz obr. 19. OBR. 19 UKÁZKA VLOŽENÍ FUNKCE SUMA

b) Sčítáme-li hodnoty v souvislé oblasti, můžeme tuto oblast dopředu označit a teprve pak stisknout tlačítko AUTOSUM. V našem případě tedy označíme oblast buněk B2:B5 a stiskneme AUTOSUM. Součet se umístí do první prázdné buňky pod vyznačenou oblastí. Sčítáme-li více sloupce (příp. i řádků) v souvislé oblasti (musí být však naplněna daty), lze označit celou oblast a přidat prázdný sloupec vpravo a prázdný řádek dole, do kterých se po stisknutí tlačítka AUTOSUM zapíší všechny řádkové i sloupcové součty. Tím se zjednoduší práce, protože odpadá následné kopírování, pokud bychom vložili funkci pouze do prvního řádku či sloupce – viz obr. 20. Pozn. Je třeba dát pozor na to, že křížový součet je v tomto případě řádkový (nesmíme ho tedy omylem nakopírovat do dalších sloupců, kde je též součet požadován) – viz obr. 21. OBR. 20 RYCHLÉ VLOŽENÍ ŘÁDKOVÝCH I SLOUPCOVÝCH SOUČTŮ


24

OBR. 21 UPOZORNĚNÍ NA KŘÍŽOVÝ SOUČET

U všech dalších funkcí na tlačítku AUTOSUM postupujeme stejným způsobem (Průměr, Počty, Maximum, Minimum). Pozn. Funkce Počty zjistí počet numerických hodnot ve vyznačené oblasti. Pokud bychom chtěli zjistit četnost nenumerických hodnot, pak musíme vybrat funkci POČET2 (přes volbu Další funkce). 3.1.7.2.

Funkce KDYŽ

Příklad 5

Standardní funkce KDYŽ

Další velmi frekventovanou funkcí je logická funkce KDYŽ. Forma zápisu: =KDYŽ (podmínka; ano; ne) Funkce má 3 argumenty (pokud ji vkládáme pomocí okna Vložit funkci, nemusíme vkládat oddělovač argumentů středník a nemusíme hlídat ani závorky). Význam jednotlivých argumentů Podmínka Podmíněný výraz ve formě relace – např. A1<0, B2<>0 apod.), na jehož výsledek reagují akce uvedené v obou dalších argumentech. Ano Je-li výsledkem relace v podmíněném výrazu logická hodnota PRAVDA (podmínka je splněna), vloží se do buňky obsah tohoto argumentu. Ne Je-li výsledkem relace v podmíněném výrazu logická hodnota NEPRAVDA, vloží se do buňky obsah tohoto argumentu. V argumentu Ano či Ne mohou být vzorce (jak aritmetické operace, tak i další standardní funkce – např. další vnořená KDYŽ) nebo konstanty – ty pak musí být uvedené v uvozovkách (pokud pracujeme s oknem Vložit funkci, systém opět sám tyto uvozovky doplní). Příklad 5 Ve sloupci D potřebujeme vložit podíl čísel ve sloupcích B a C. Pokud si nejsme jisti, že čísla ve sloupci jmenovatele (C) budou vždy vyplněna, nesmíme použít přímo vzorec dělení, ale musíme hodnotu jmenovatele ve vzorci otestovat, zda není nulová. V kladném případě do buňky vložíme znak pomlčka – (lze podle potřeby vložit i mezeru nebo nějaký vysvětlující text). Použijeme funkci KDYŽ – tlačítkem Fx v řádku vzorců vyvoláme okno Vložit funkci, vybereme skupinu logické (pokud funkci již jednou použijeme, najdeme ji ve skupině Naposledy použité) a dohledáme funkci KDYŽ. Vyplníme argumenty – viz obr. 22. Po ukončení vstupu je v řádku vzorců vidět vzorec:


25

=KDYŽ(C2=0;“-“;B2/C2) a v buňce se objeví vypočtená hodnota (příp. pomlčka v případě, že buňka C2 bude prázdná). OBR. 22 POUŽITÍ FUNKCE KDYŽ

Příklad 6

Příklad 6 Ve sloupci B jsou uvedeny náklady na výrobu v jednotlivých měsících (v tis. Kč), ve sloupci C tržby, do sloupce D máme spočítat zisk nebo ztrátu (v případě rovnosti obou hodnot pak ponecháme v buňce mezeru). V tomto případě nestačí jedna funkce KDYŽ, ale musíme vnořit další. OBR. 23 UKÁZKA VNOŘENÍ DALŠÍ FUNKCE KDYŽ – VNĚJŠÍ FUNKCE

Vnořování funkcí

V případě, že rozdíl tržeb a nákladů je vyšší než 0, pak jde o zisk, což vyplníme dle výše uvedeného obrázku. V případě opačném je nutné vložit další KDYŽ, které vyřeší hodnoty rozdílu menší než 0 a rovné nule. Při vnořování funkcí musíme sledovat řádek vzorců – viz obr. 23. V jeho levé části je uvedena funkce, jejíž okno je právě otevřené (v našem případě KDYŽ), v pravé části řádku vzorců se postupně objevují vložené argumenty funkce. V argumentu Ne potřebujeme vložit další funkci KDYŽ, takže klepneme do levé části řádku vzorců na tlačítko KDYŽ a otevře se dialogové okno nové vnořené funkce. V ní doplníme argumenty takto (v argumentu Ano jsme vložili mezeru) – viz obr. 24:


26

OBR. 24 UKÁZKA VNOŘENÍ DALŠÍ FUNKCE KDYŽ – VNOŘENÁ FUNKCE

Pokud bychom potřebovali vnořit jinou funkci než ta, jejíž název je v levé části řádků vzorců viditelný, klepneme na rozbalovací šipku vpravo a objeví se seznam funkcí ze skupiny Naposledy použité. V posledním řádku je pak opět odkaz na další funkce, který otevírá známé okno Vložit funkci. Příklad se dá řešit i tak, že nejprve vložíme sloupec výpočtu výsledku hospodaření (sloupec D) a ve sloupci zisk/ztráta (sloupec E) pak porovnáváme již tyto vypočtené hodnoty s nulou. Mění se pak jen zápis relace v argumentu Podmínka. Pozn. I v argumentech funkcí lze využít pojmenované oblasti. Pokud např. sčítáme 10 hodnot ve sloupci A, můžeme ve funkci SUMA uvést odkaz na oblast buněk (např. A1:A10) nebo oblast A1:A10 můžeme pojmenovat (např. jménem MZDY a pak se v argumentu funkce použije toto jméno (vložíme ho pomocí funkční klávesy F3) =SUMA(MZDY) 3.1.7.3.

Zaokrouhlování

Standardní funkce ZAOKROUHLIT

Zaokrouhlení hodnot v EXCELU nelze řešit pomocí formátování (to změní jen zobrazení na obrazovce, v buňce zůstává původní hodnota). Musíme proto použít standardní funkci. Funkce má dva argumenty. První argument (Číslo) je hodnota, kterou zaokrouhlujeme. Argument může obsahovat odkaz na buňku nebo vzorec, a to buď aritmetickou operaci nebo vnořenou standardní funkci (obvyklý je např. PRŮMĚR). Druhý argument určuje způsob zaokrouhlení. Může obsahovat hodnoty na celé číselné ose (nejčastější je interval mezi -2 a +2). Kladné číslo určuje, na kolik desetinných míst se hodnota zaokrouhlí, číslo 0 a záporné zaokrouhluje na řád čísla, tj. 0 na celá čísla, -1 na desítky, -2 na stovky atp. Např. zápis =ZAOKROUHLIT(B2/B6;2) zaokrouhlí výsledek podílu buněk B2 a B6 na 2 desetinná místa. Pozor! Pokud zaokrouhlujeme hodnotu, která bude následně vyjádřena v %, musíme 2. argument vždy zvýšit o 2. Formát procent vynásobí hodnotu konstantou 100 a my bychom pak 2 místa v zaokrouhlované hodnotě ztratili.


27

Pozn. V EXCELu je několik variant funkcí pro zaokrouhlení. Funkce ZAOKROUHLIT zaokrouhluje přesně podle matematických pravidel. Existuje však i účetnické zaokrouhlení, tj. vždy nahoru nebo vždy dolů (např. 0,1 na 1 nebo naopak 0,9 na 0), to jsou funkce ROUNDUP a ROUNDDOWN, které mají stejný charakter argumentů jako funkce ZAOKROUHLIT. Dále existují ještě funkce, které zaokrouhlují např. na násobky čísla, ty ale nejsou tak časté. 3.1.7.4.

Funkce DNES a NYNÍ

Příklad 7

Další užitečné standardní funkce

Funkce DNES a NYNÍ Obě funkce jsou zajímavé tím, že nemají argumenty. Pokud je vkládáme přímo z klávesnice, musíme za jejich jménem uvést dvojici kulatých závorek (). Funkce DNES() vrací aktuální datum a funkce NYNÍ() datum včetně času (obojí však z hodin počítače). Pro některé další funkce využijeme níže uvedenou tabulku. Příklad 7 Model auta Škoda Fabia VW Golf Peugeot 207 Škoda Fabia Škoda Fabia VW Golf Škoda Fabia VW Golf Škoda Fabia Peugeot 207 Peugeot 207 Škoda Fabia Škoda Fabia Peugeot 207 Peugeot 207

Funkce pro podmíněné výpočty

Prodáno v měsíci leden leden leden leden únor únor únor únor únor únor únor březen březen březen březen

Tržba v tis. Kč 256 354 378 260 260 380 260 395 245 356 356 220 238 389 395

Funkce SUMIF spočítá sumu na základě dat, které vyhovují zadané podmínce. Máme spočítat celkovou tržbu za Peugeot 207. 1. argument obsahuje rozsah dat, nad kterými je stanovena podmínka (1. sloupec), 2. argument obsahuje zápis vlastní podmínky (“Peugeot 207“) a 3. argument obsahuje odkaz na vlastní sloupec sčítaných hodnot – viz obr. 25.


28

OBR. 25 UKÁZKA FUNKCE SUMIF

Funkce SUMIF

Funkce SUMIFS

Funkce SUMIFS umožňuje zadat více podmínek v různých sloupcích. Máme spočítat tržbu za Škoda Fabia v měsíci lednu. V 1. argumentu je oblast sčítaných hodnot, další argumenty jsou vždy dvojicí označení sloupce, nad kterým je stanovena podmínka a zápisu vlastní podmínky – viz obr. 26. OBR. 26 UKÁZKA FUNKCE SUMIFS

Protože další funkce jsou již analogií SUMIF a SUMIFS, uvádíme pouze výsledný zápis vzorce. Funkce COUNTIF počítá četnost záznamů vyhovujících zadané Funkce COUNTIF a podmínce. Máme zjistit celkový počet kusů prodaných Fabií. =COUNTIF(A2:A16;"Škoda Fabia") COUNTIFS COUNTIFS počítá četnost záznamů vyhovujících více podmínkám v různých sloupcích. Máme zjistit počet prodaných Peugeotů v březnu. =COUNTIFS(A2:A16;"peugeot 207";B2:B16;"březen")


29

Funkce AVERAGEIF počítá podmíněný průměr, AVERAGEIFS podmíněný průměr na základě více podmínek. Máme spočítat průměrnou tržbu za Fabie celkem a průměrnou tržbu za Fabie v lednu. =AVERAGEIF(A2:A16;"Škoda Fabia";C2:C16) =AVERAGEIFS(C2:C16;A2:A16;"Škoda Fabia";B2:B16;"leden") Funkce CONCATENATE slouží pro spojování (sčítání) textů. Velmi Funkce často získáme seznam lidí, kde jméno a příjmení jsou v oddělených sloupcích. CONCATENATE Nám by někdy lépe vyhovovalo mít je spojené v jedné buňce. Použijeme proto výše uvedenou funkci a obsahy obou buněk sloučíme. Jednotlivé argumenty funkce jsou odkazy na spojované texty, přičemž nesmíme zapomenout mezi nimi vložit alespoň jednu mezeru, aby se text „neslil“ dohromady (křestní jméno je v buňce G1 a příjmení v buňce H1). Proto, aby data bylo možné následně seřadit, na prvním místě při spojování volíme příjmení a pak teprve po mezeře křestní jméno. =CONCATENATE(H1;" ";G1) Místo funkce lze použít i operátor &, pak zápis vypadá takto: =H1&“ “&G1 Funkce VELKÁ, VELKÁ2 nahrazují to co je ve WORDu možné Funkce provést příkazem, tj. převod malých písmen na velká a naopak. Funkce VELKÁ, VELKÁ nahrazuje všechna malá písmena velkými, funkce VELKÁ2 VELKÁ2 ponechává velké písmeno pouze na začátku slov. Funkce A, NEBO, NE – v EXCELu chybí logické operátory, kterými se Logické spojují relační výrazy. Protože se musí použít logické standardní funkce. funkce Např. místo zápisu nahrazující =Podmínka1 AND Podmínka2 logické se v EXCELu použije zápis operátory =A(Podmínka1;Podmínka2) kdy jednotlivé relace (Podmínka1,2) jsou argumenty funkce. Funkce AVERAGEIF a AVERAGEIFS

Kapitola 4: Formátování tabulky

4. Formáty na úrovni buňky

30

Formátování tabulky

Po vložení dat a vzorců je vhodné tabulky formálně upravit naformátovat. V EXCELu rozlišujeme dvě úrovně formátů – na úrovni buňky a na úrovni řádku či sloupce. Zde uvedeme pouze formáty na úrovni buňky, druhá úroveň (šíře sloupce a výška řádku) již byla zmíněna v kap. 3.1.1. Pozn. Pokud potřebujeme v buňkách formát zrušit, lze použít tlačítko Vymazat (na kartě Domů v sekci Úpravy) a zvolit Vymazat formáty. Obsah buněk zůstává beze změny a ruší se pouze formát (pokud je nakombinovaných formátů více, zruší se všechny – např. ohraničení i barva výplně i naformátovaný počet desetinných míst apod.).

4.1

Formát číselných hodnot

Po vložení číselných hodnot je automaticky nastaven tzv. obecný Formátován formát, který je však vhodné v řadě případů měnit. Změnu můžeme realizovat í čísel dvěma možnými cestami – buď vybereme na kartě Domů v sekci Buňky tlačítko Formát buňky a pak se otevře dialogové okno Formát buněk – viz obr. 27. Formát čísel pak měníme v záložce Číslo. Druhou cestou je využití rychlých tlačítek na kartě Domů v sekci Číslo: •

Tlačítko Účetnický číselný formát umožňuje doplnit k číslu měnu (přednastavena je Kč). • Tlačítko Styl procent vynásobí hodnotu v buňce konstantou 100, doplní symbol % a odřízne desetinnou část (formátuje na celá procenta). Pozn. Vrátíme-li se k příkladu 3 (kap. 3.1.6), můžeme vzorec procentního podílu vložit i takto (bez násobení konstantou 100) =B2/$B$6 a následně jej naformátovat na procenta (doplní se i symbol %, což je vždy v tabulce přehlednější). Pak stačí využít tlačítko Přidat desetinná místa, abychom docílili požadované přesnosti. Posledním tlačítkem je tlačítko Odebrat desetinná místa, které funguje opačně. • Tlačítko Styl čárky – zobrazí číslo s desetinnou částí, oddělí tisíce mezerou a za číslem ponechá mezery na příp. zobrazení symbolu měny Pokud využijeme dialogové okno Formát buněk, vybíráme jednotlivé druhy formátu z nabízeného seznamu, který obsahuje i volby, pro která tlačítka neexistují (např. formát datumu a času, zlomky, nebo speciální formáty – pro PSČ, či telefonní čísla). Lze nastavovat i formáty vlastní, kde lze např. k číselné hodnotě přidat měrnou jednotku (kg, litry apod.). Pozn. Použitím formátu měníme pouze zobrazení dat na obrazovce, hodnota uvnitř buňky zůstává nezměněna (s ní se i provádí veškeré další výpočty). Změna počtu desetinných míst neznamená tedy ve skutečnosti zaokrouhlení, pokud chceme skutečně číslo zaokrouhlit, musíme použít standardní funkci (viz kap. 3.1.8.3). Totéž je např. u procent, pokud číslo naformátujeme na procenta, je toto opět záležitost pouze zobrazení, v buňce je vždy hodnota 100x nižší.


31

OBR. 27 DIALOGOVÉ OKNO FORMÁT BUNĚK

4.2

Formát textu

4.2.1. Formát písma

Formát písma Na kartě Domů v sekci Písmo lze vybrat tyto možnosti (příp. lze využít záložku Písmo v dialogovém okně Formát buněk): • • • 4.2.2.

Zarovnání textu

Zalomení textu

Výběr fontu a velikosti písma. Zvětšení či zmenšení písma (v podstatě řeší to, co velikost písma, nikoliv však výběrem ze seznamu, ale postupně klepáním na tlačítko). Výběr tzv. řezu písma (tučné, kurzíva, podtržení – jednoduché nebo dvojité).

Zarovnání textu Po vložení hodnot do buňky jsou tyto automaticky zarovnány – číselné hodnoty zprava, textové hodnoty zleva. Pokud potřebujeme formát změnit, můžeme opět využít dialogové okno Formát buněk, kde využijeme záložky Zarovnání. Můžeme využít i rychlých tlačítek na kartě Domů v sekci Zarovnání. Ikony na jednotlivých tlačítkách pak názorně ukazují formu zarovnání (lze zarovnávat zleva, zprava, na střed nebo nahoru, dolů a na střed, příp. měnit úhel pootočení textu). Velmi užitečný formát (zejména pro nadpisy sloupců tabulky) je zalomení textu (text se podle šíře sloupce zalomí v 1 buňce do více řádků). Text lze zalamovat i přímo při vkládání pomocí kláves ALT ENTER. V kombinaci se zarovnáním vodorovně i svisle na střed je to velmi vhodná úprava nadpisů tabulky. Poslední tlačítko Sloučit a zarovnat na střed je vhodné tehdy, pokud potřebujeme upravit text nadpisu, aby byl umístěn a vycentrován nad více sloupci – viz 1. řádek dále uvedené tabulky.

Kapitola 4: Formátování tabulky Náklady Mzdové Materiální

32

Ostatní

4.2.3.

Ohraničení buněk

Ohraničení, barva výplně, barva písma Data v tabulce j vhodné doplnit i o rámečky – zejména při tisku je to přehlednější. Šipka na tlačítku Ohraničení rozvine seznam z obr. 28, ve kterém můžeme zvolit vhodný způsob ohraničení. Nejčastější je volba Všechna ohraničení. Ohraničení můžeme „vylepšit“ i změnou barvy čáry příp. stylu (tenká, tlustá, čárkovaná, čerchovaná apod.). OBR. 28 VARIANTY OHRANIČENÍ BUNĚK

Barva textu a barva výplně

Styly

Tlačítko Barva výplně obsahuje seznam tzv. barev motivů, standardních barev, příp. je možné namixovat si i vlastní kombinaci barev z nabízeného barevného spektra. Existuje i volba Bez výplně, která naopak barvu ruší. Pozn. Barvy je třeba volit velmi uvážlivě (určitě zde platí méně je více, tabulka hýřící pestrými barvami není moc přehledná), a to zejména s ohledem na následný tisk. Pokud navolíte příliš tmavou barvu výplně, je nutné změnit inverzně barvu textu, protože jinak se obě barvy slijí (a to nejen při tisku, ale i na obrazovce). Tlačítko Barva textu vyvolá podobný seznam jako Barva výplně, chybí volba Bez výplně, ale naopak je přidána standardní volba – Automatická, což je barva černá, která vlastně příp. zvolenou barvu ruší. I v EXCELU je možné kombinace formátů uložit pro další využití a pracovat tak se styly, podobně jako ve WORDu. V EXCELU je řada již předdefinovaných stylů, které také s výhodou můžeme při formátování tabulky využít. Nejdeme je na kartě Domů v sekci Styly pod tlačítkem Styl buňky – viz obr. 29. Zde také můžeme nové styly vytvářet či upravovat. OBR. 29 NABÍZENÉ STYLY BUNĚK


33

4.2.4.

Podmíněné formátování

Formátování záporných čísel

Podmíněné formátování Buňky můžeme formátovat výše uvedeným způsobem, pokud však chceme formát změnit, musíme buď použít jiný styl nebo buňku přeformátovat. Buňky se však dají formátovat i podmíněně, v závislosti na obsahu buňky (využití je především u buněk obsahujících vzorce, při přepočítání vzorce pak dochází i k následné změně formátu). Na kartě Domů v sekci Styly využijeme tlačítko Podmíněné formátování (obr. 30). OBR. 30 NABÍDKA PODMÍNĚNÉHO FORMÁTOVÁNÍ

Jak je vidět z obrázku, existuje zde řada možností. Nejdůležitější je nastavení pravidla pro formátování, tj. stanovení podmínky, za které se příslušný formát přiřadí. Doporučuji vyzkoušet, vzhledem k omezenému rozsahu textu nebudeme jednotlivé možnosti detailněji rozebírat. Pokud potřebujeme formátovat záporná čísla červenou barvou textu, pak není nutné používat Podmíněné formátování, ale v dialogovém okně Formát buněk ve stylu Číslo (příp. Měna) vybereme vhodnou volbu ze seznamu v oddílu Záporná čísla. Pozor, abychom vybrali tu správnou se znaménkem minus.

Kapitola 5: Práce s listy

5.

Názvy listů

Operace s listy

34

Práce s listy

V řadě případů nám pro práci nestačí pouze buňky v jednom listu. List je tedy dalším rozměrem, pomocí kterého můžeme vytvářet tabulky např. v časové řadě (každý list představuje např. 1 měsíc). Pokud data sdílí větší počet uživatelů, je vhodnější místo listů použít různé soubory (např. soubory obsahující základní data a 1 sdílený soubor obsahující kurzovní lístek apod.). Sešit obsahuje ve výchozím nastavení 3 listy. Každý list musí mít své jméno (při založení/otevření nového sešitu je to LISTn, kde n je pořadové číslo listu). Tento název je uveden na záložce listu a přechod mezi listy se realizuje klepnutím myší na tuto záložku. S listy lze provádět řadu operací, které najdeme v místní nabídce vyvolané na pravém tlačítku myši nad oblastí záložek. Všechny akce lze realizovat i pomocí tlačítek na pásu karet, jejichž použití zde neuvádíme. • Vložení listu – pro vložení klasického datového je nejjednodušší klepnout na tlačítko umístěné za poslední záložkou listu. Pokud chceme vložit jiný typ listu (grafický, dialogový apod.), musíme použít příkaz z Vložit z nabídky na pravém tlačítku myši (v tom případě se nový list vloží před list, který je momentálně aktivní – stojí v něm kurzor). • Odstranění listu – pokud jsou v listu již data, systém na to upozorní a operaci lze přerušit. • Přejmenování listu – jednodušší než z nabídky na pravém tlačítku myši, je vyvolání editace poklepáním na záložku. Pozor – dva listy nesmí mít stejné jméno. Jména volte uvážlivě, abyste pro přehlednost viděli na obrazovce co největší počet záložek. • Přesun nebo kopie listu – nejjednodušší způsob je opět tažením záložky listu myší nad ostatními záložkami (šipka pak ukazuje, kam se list umístí). Kopie se realizuje při stisknuté klávese CTRL – zkopírovaný list má název shodný se zdrojovým listem, za kterým je v závorce uvedeno pořadové číslo kopie (např. LIST1 (2) apod.). Kopie je identická, zachovává formáty na obou úrovních. Pokud chceme vytvořit více kopií původního listu najednou, je lepší použít kopírování přes schránku, v tom případě však ztrácíme formáty na úrovni šíře sloupců a výšky řádků. Pokud použijeme místní nabídku, pak lze kopírovat a přesouvat listy i mezi sešity. • Skrytí, příp. opětovné zobrazení listu. • Vytvoření skupiny listů – tato operace je vhodná tehdy, pokud potřebujeme realizovat stejnou operaci pro více listů najednou, např. opravy, formátování apod. Předpokládá to ovšem, že tabulky ve všech listech mají shodnou formu. Listy musíme označit (u sousedících klepneme na záložku prvního listu a při stisknuté klávese SHIFT klepneme do poslední záložky listu ve skupině, u nesousedících listů klepeme na další záložky listů ve skupině se stisknutou klávesou CTRL). Záložky listů jsou

Kapitola 5: Práce s listy

35

zobrazeny inverzně a v záhlaví aplikace za názvem souboru se objeví text [skupina]. Zrušení skupiny lze realizovat klepnutím na kteroukoliv neoznačenou záložku. Pokud jsou všechny listy ve skupině označeny, tak klepnutím na jakoukoliv záložku. Práce se skupinami je na jedné straně mocný nástroj ulehčující spoustu práce, na druhé straně při nevhodném použití si můžeme způsobit řadu chyb. Pozn. Pokud zkoušíte s daty realizovat různé operace, pro jistotu bych doporučila si vytvořit kopii listu se zdrojovými daty, abychom se v případě nouze (např. po provedení některé z nevratných operací) k nim mohli kdykoliv vrátit. Samozřejmě u důležitých souborů je třeba věnovat pozornost častému ukládání sešitu a příp. vytvoření zálohy.

Kapitola 6: Tvorba a úpravy grafů

6.

Tvorba a úpravy grafů

6.1

Tvorba grafu

36

Grafická interpretace je vhodným a názorným doplněním dat uložených Zásady při v tabulkách. Při tvorbě grafů je třeba dodržovat určité zásady: tvorbě grafů • Vždy je třeba vybrat vhodný typ grafu podle charakteru dat – pro data tvořící podíl na celku se používají obvykle grafy výsečové, pro sledování vývoje dat (např. v čase) grafy spojnicové, univerzálním grafem je graf sloupcový (se všemi svými modifikacemi – kuželový, válcový apod.) nebo pruhový. Existují i specifické typy grafů. • Grafy by neměly zobrazovat příliš velké množství dat (20 výsečí v grafu už nic neřekne). • Vždy bychom měli dávat přednost přehlednosti před pestrostí a využitím množství efektů. • Grafy musí být doplněny veškerými náležitostmi – především by měly mít vypovídající název grafu, názvy os, příp. legendu. Tvorba grafu byla od verze EXCEL 2007 výrazně zjednodušena – odpadlo využití tzv. Průvodce grafem. Graf je po výběru dat ihned vytvořen a následně je možné jej doplnit a upravit. Celou tvorbu grafu si ukážeme na příkladech, pro které budeme používat následující tabulku: Prodejce leden Bína 100 Cahlík 80 Kašpar 140 Moulis 200 Nováček 100 Rychtářová 210 Vaněk 180 Zezulka 180 Celkem 1190

Příklad 8

Tržby v tis. Kč únor březen Celkem 250 150 500 200 150 430 140 150 430 225 260 685 120 120 340 22 200 432 190 200 570 170 140 490 1317 1370 3877

6.1.1. Graf výsečový Příklad 8 Máme vytvořit graf přehledu tržeb za celé čtvrtletí podle jednotlivých prodejců. Označíme tedy data v prvním (prodejci) a posledním sloupci (celkem), bez záhlaví a posledního součtového řádku. Nejprve označíme první sloupec a pak při stisknuté klávese CTRL sloupec poslední. Označení dat v součtovém řádku Celkem by bylo velkou chybou, protože by celková suma tvořila jednu z výsečí a tím celý graf znehodnotila. Na kartě Vložení v sekci Grafy klepneme na šipku u tlačítka Výsečový a vybereme vhodný podtyp grafu – viz obr. 31 (zvolíme prostorový výsečový). Pozn. Pokud nevybereme pro graf kromě sloupce s číselnými hodnotami i sloupec obsahující texty (důležité buď pro legendu ve výsečovém grafu nebo


37

pro popisky ve všech typech grafů), v legendě nebo popiscích se objeví číselné hodnoty počínaje číslem 1, což není vhodné. Legenda je needitovatelná, proto musíme hodnoty pro ni zadat na kartě Návrh v sekci Data pomocí tlačítka Vybrat data. Zobrazí se dialogové okno, kde pomocí tlačítka Přidat zadáme chybějící oblast s textovými daty pro legendu. OBR. 31 MOŽNÉ PODTYPY VÝSEČOVÉHO GRAFU

Umístění a úpravy výsečového grafu

Název grafu

Legenda a popisky dat

Graf se ihned vloží do listu a můžeme jej začít upravovat. Na pásu karet se kontextově otevřela nabídka pro práci s grafem (nástroje grafu) s kartami Návrh, Rozložení a Formát. Nejprve zvážíme možnost přesunutí grafu do tzv. grafického listu (to je typ listu, který slouží pouze pro vložení grafu, neobsahuje žádná data). Grafika je v grafickém listu vždy značně lepší, proto, pokud nepotřebujeme na listu vidět současně tabulku i s grafem, dáváme přednost umístění do grafického listu. V pásu karet Návrh klepneme do posledního tlačítka Přesunout graf a vybereme variantu Nový graf (ve vstupním poli se nabízí název listu Graf1 až n a zde je vhodné hned grafický list přejmenovat). Pokud změnu jména listu nerealizujeme v tomto dialogovém okně, můžeme list přejmenovat kdykoliv později (provedení je stejné jako u datového listu). Pozn. Do jednoho grafického listu lze umístit i více grafů (v tom případě je nejprve nutné vložit prázdný grafický list a do něj pak postupně přemísťovat jednotlivé grafy (u tlačítka Přesunout list je to volba Objekt v, kde uvedeme název vloženého grafického listu). Do grafu musíme přidat název – na kartě Rozložení vybereme v sekci Popisky tlačítko Název grafu a na jeho šipce zvolíme variantu Nad grafem. V grafu se objeví pole s textem Název grafu a tento text přepíšeme – Tržby v 1. čtvrtletí podle jednotlivých prodejců. Dojde-li k chybě při vložení názvu, editaci lze vyvolat dvojím klepnutím do názvu s prodlevou. Pokud bychom chtěli název grafu nějak formátovat, klepneme znovu do šipky a vybereme volbu Další možnosti nadpisů a můžeme např. měnit barvu textu, barvu výplně, volit způsob ohraničení atd. Přehled jednotlivých prodejců vidíme v zobrazené legendě. Vhodnější je však umístit popisky k jednotlivým výsečím, ve kterých budou jména prodejců uvedena a příp. je doplnit i číselným údajem (jak ve formě absolutní číselné hodnoty, tak i relativně v %). Na kartě Rozložení v sekci Popisky nejprve zrušíme legendu (pod tlačítkem Legenda zvolíme variantu Žádná). Jednodušší je legendu v grafu označit a klávesou DEL vymazat. Zůstaneme v sekci


„Vyříznutí“ výseče

38

Popisky a zvolíme šipku pod tlačítkem Popisky. Musíme zvolit umístění popisků a jejich obsah. Nejlepší je zvolit variantu Další možnosti popisků dat, kde ve volbě Možnosti štítku zaškrtneme 3 volby: Název kategorie (zobrazí se jména prodejců), Hodnoty (zobrazí se hodnoty prodejů) a Procenta (zobrazí se % podíly tržeb jednotlivých prodejců). Umístění zvolíme Za zakončením a příp. ještě můžeme vybrat oddělovač mezi jednotlivými typy popisků (ponecháme středník). Pokud bychom chtěli popisky i formátovat, můžeme využít několika dalších voleb, které jsou v okně Formát popisků dat dostupné. Nakonec by bylo asi vhodné graf trochu zmenšit, příp. zvýraznit nejmenší či největší hodnotu prodeje (tzv. vyříznutím z grafu). Pro změnu velikosti grafu musíme označit v grafu tzv. zobrazovanou oblast – na kartě Rozložení v sekci Aktuální výběr klepneme na šipku stahovacího seznamu a zvolíme variantu Zobrazovaná oblast. Ta se označí rámečkem, za jehož úchyty můžeme velikost grafu upravovat. Vyříznutí nejmenší výseče z grafu realizujeme tažením myší za označenou výseč (předtím jsme do ní klepli dvakrát s prodlevou). Podle toho, jak daleko výseč vysuneme, mění se automaticky i velikost zobrazované oblasti. Výsledný graf vypadá takto – viz obr. 32. OBR. 32 VÝSLEDNÝ GRAF Z PŘÍKLADU 8

Pozn. Při úpravách grafu je dobré si pamatovat následující postupy. V grafu pracujeme s jednotlivými objekty (datová řada, zobrazovaná oblast, popisky dat apod.). Pokud chceme měnit velikost objektu (příp. objekt přesouvat, pokud to lze), klepneme do objektu myší jednou (nebo vybereme objekt v sekci Aktuální výběr na kartě Rozložení). Chceme-li objekt editovat (pokud to lze, např. legendu editovat nelze), klepneme do něj myší dvakrát s prodlevou. Chceme-li objekt formátovat, poklepeme na něj myší, nebo jej opět vybereme v sekci Aktuální výběr a pak zvolíme tlačítko Formátovat výběr.


39

6.1.2.

Graf sloupcový Příklad 9. Pomocí sloupcového grafu chceme zobrazit prodeje Příklad 9 jednotlivých prodejců podle měsíců. Graf tedy bude obsahovat pro každého prodejce 3 sloupce prodejů podle jednotlivých měsíců. Označíme tedy data ve všech sloupcích (mimo posledního sloupce Celkem) – na rozdíl od předchozího příkladu je označíme včetně nadpisů sloupců (jejich obsah je totiž u osově orientovaných grafů důležitý pro legendu). Pokud bychom nadpisy nezahrnuli, legenda by obsahovala texty Řady1, Řady 2 atd., což není správné. Pokud chceme sloupcový graf umístit do grafického listu, urychlíme jeho vložení po označení dat stiskem klávesy F11. Vloží se nový grafický list a do něj se graf (vždy sloupcový) ihned umístí (pokud bychom potřebovali použít jiný typ grafu, mohli bychom pak na kartě Návrh v sekci Typ tlačítkem Změnit typ grafu vybrat graf požadovaný). Pro vložení grafu do datového listu – tzv. plovoucí graf, bychom použili kombinaci kláves ALT F1. Graf musíme opět upravit – doplnit název grafu, příp. názvy os. Zvolíme Úpravy tlačítko Název grafu na kartě Rozložení v sekci Popisky a postupujeme sloupcového obdobným způsobem jako v předchozím příkladu. Pak vložíme názvy obou os grafu – hlavní vodorovná – Prodejci, hlavní svislá - v tis. Kč. Samozřejmě můžeme dále graf upravovat - měnit barvy jednotlivých datových řad (sloupců), velikost zobrazované oblasti, její barevnou výplň. Lze doplnit i tabulku hodnot, ze kterých je graf vytvořen. Zobrazíme ji pomocí tlačítka Tabulka dat v sekci Popisky na kartě Rozložení. Hotový graf pak vypadá takto – viz obr. 33: OBR. 33 VÝSLEDNÝ GRAF Z PŘÍKLADU 9

6.2 Přidání a odstranění datové řady

Další možné úpravy grafů

V grafech (mimo výsečového) můžeme přidávat další datové řady nebo naopak některé datové řady rušit. Pokud bychom v našem grafu z obr. 33 chtěli vidět pouze měsíce leden a únor, můžeme poslední datovou řadu (sloupec)


40

zrušit. Klepneme do kteréhokoliv 3. sloupce v pořadí (označíme tím datovou řadu) a klávesou DEL ji odstraníme. Pokud bychom to provedli omylem, lze využít tlačítka Zpět a operaci anulovat. Pokud bychom už na začátku tvorby grafu na některý měsíc (např. březen) zapomněli, lze jej do hotového grafu doplnit. Vyvoláme tlačítkem Vybrat data (na kartě Návrh v sekci Data) dialogové okno Vybrat zdroj dat – viz obr. 34. OBR. 34 DIALOGOVÉ OKNO VYBRAT ZDROJ DAT

Použitím tlačítka Přidat se vyvolá okno Upravit řady - viz obr. 35. Zde musíme doplnit odkaz na buňku obsahující data pro legendu (buňka obsahující nadpis Březen) a odkazy na vlastní číselné hodnoty ve sloupci březen. OBR. 35 NASTAVENÍ HODNOT PRO NOVĚ VKLÁDANOU DATOVOU ŘADU

Aktualizace grafu

Datová řada se přidá vždy jako poslední. V našem případě je to v pořádku. Pokud bychom někdy potřebovali změnit pořadí řad (sloupců), použijeme v okně Vybrat zdroj dat (viz obr. 34) šipky směrem nahoru nebo dolů a tím posouváme pořadím té datové řady, na které stojí v okně kurzor. Pokud se změní zdrojová data v tabulce, která jsou využita v grafu, dojde k okamžitému překreslení grafu. Na rozdíl od předchozích verzí odpadla opačná varianta, a to možnost úpravy dat v grafu s následným promítnutím těchto změn do tabulky. Do grafu můžeme přidávat i další grafické objekty, např. šipky, textová pole s komentáři, příp. různé obrázky (např. logo apod.). Na kartě Vložení v sekci Ilustrace můžeme použít tlačítka Obrázek, Klipart, Obrazce. I zde však platí, že bychom měli zvažovat vhodnost dalších doplňovaných objektů, aby graf zůstal přehledný.

Kapitola 7: Řazení dat

7. Řazení dat

41

Řazení dat

Řazení dat umožňuje měnit pořadí řádků na základě zvoleného kritéria – tzv. třídícího klíče. Je to operace, která patří v EXCELu mezi velmi časté. V předchozích verzích bylo možné zvolit maximálně 3 úrovně třídících klíčů, od verze 2007 je řazení víceméně neomezené. Nastavení další úrovně třídícího klíče je nutné pouze tehdy, pokud se hodnoty na předchozí úrovni opakují, takže ve většině případů stejně vystačíme max. se 2 klíči. Před vlastním řazením je třeba data pro řazení označit, a to v celé šíři sloupců (jinak by se porušila konzistence dat), vynecháme pouze záhlaví sloupců a poslední řádky se sumárními výpočty. Řazení je dostupné na kartě Data v sekci Seřadit a filtrovat. Zde jsou dvě rychlá tlačítka, která řadí podle prvního sloupce v označeném výběru v jednom či druhém směru (od A do Z, od Z do A). Pokud potřebujeme nastavit nějaký jiný způsob, musíme použít tlačítko Seřadit, které vyvolá dialogové okno – viz obr. 36 (pro příklad řazení použijeme tabulku z příkladu 8 v tabulce 6.1. – řadit budeme podle sloupce Celkem od nejmenší k největší hodnotě). OBR. 36 UKÁZKA ŘAZENÍ DAT

V oddílu Sloupec vybereme ze seznamu název sloupce, podle kterého se bude řadit (pokud sloupce nemají název, objevují se zde označení sloupců pomocí písmen). V oddílu Řazení se automaticky nastavila volba Hodnoty, můžeme však ze seznamu vybrat i řazení podle barvy buňky, barvy písma nebo ikony v buňce (doplněné pomocí automatického formátování). V oddílu Pořadí můžeme vybrat směr řazení (u textu se nabízí od A do Z a od Z do A, u čísel od nejmenšího k největšímu a naopak), příp. možnost řazení podle seznamu. Pokud např. řadíme data podle názvů měsíců a nezadáme volbu Vlastní seznam, měsíce se seřadí podle abecedy, nikoliv logicky podle svého pořadí. Pokud se hodnoty na úrovni 1. klíče budou opakovat (což není náš Řazení podle případ), můžeme tlačítkem Přidat úroveň založit v okně další řádek, do více klíčů kterého nastavíme řazení podle dalšího klíče. Naopak kdykoliv můžeme Volby řazení

Kapitola 7: Řazení dat

Vložení a odstranění sloupce/řádku

42

některou z úrovní tlačítkem Odstranit z nastavení odebrat. Tlačítka šipek umožňují měnit pořadí zadaných úrovní klíčů. Volba Možnosti… nabízí řazení i zleva doprava (tedy podle sloupců), což lze využít např. pro záměnu dvou sloupců obsahujících konstantní data. Pokud jsou ve sloupcích již vzorce, dojde ke změnám jejich odkazů a špatnému výpočtu, proto nedoporučuji. Pozn.1. Pokud se nám podaří kdykoliv seřadit tabulku chybně, lze operaci vrátit tlačítkem Zpět. Pozn.2. Pokud máme tabulku ve formě tzv. seznamu (obsahuje vlastní data bez sumárních řádků, v záhlaví jsou nadpisy sloupců – pro každý sloupec jedinečné a v jediné buňce – viz dále kap. 8), pak odpadá označování dat před řazením. Pokud kurzor umístíme do některého ze sloupců a zvolíme rychlé tlačítko, pak se data seřadí podle tohoto sloupce. Pokud použijeme tlačítko Seřadit, systém data pro řazení sám na pozadí automaticky označí. Pokud bychom se po seřazení dat chtěli kdykoliv vrátit k původní neseřazené tabulce, je vhodné před operací řazení vložit před první sloupec ještě jeden sloupec, obsahující pořadová čísla (využijeme tzv. výplň řady – viz kap. 3.1.3). Jak vložíme sloupec? Sloupec je vkládán před sloupec, který je aktivní (stojí v něm kurzor nebo je označen). Nejlepší je označit celý sloupec, před který budeme vkládat (klepnutím do jeho záhlaví) a použít tlačítko Vložit v sekci Buňky na kartě Domů. Lze umístit kurzor i do jedné buňky ve sloupci, ale pak musíme klepnout na šipku u tlačítka Vložit a zvolit variantu Vložit celý sloupec. Jinak se vloží pouze jediná buňka a zbytek se posune, což nám tabulku rozhodí. Podobným způsobem vkládáme i řádek (vkládá se vždy nad aktivní řádek). Opačná operace je odstranění sloupce či řádku. Postupujeme shodným způsobem, pouze použijeme tlačítko Odstranit. Sloupec či řádek se skutečně fyzicky odstraní (na rozdíl od pouhého výmazu dat, kdy sloupec fyzicky v tabulce zůstává). Pozor! Při neopatrném provedení těchto operací můžeme data v tabulce znehodnotit (u vzorců, které budou odkazovat na odstraněné řádky či sloupce se objeví chybová hláška #ODKAZ!) a zpětná rekonstrukce vzorců bývá někdy dost obtížná.

Kapitola 8: Filtrování dat

8.

Data typu seznam

43

Filtrování dat

Filtrování dat představuje rychlý a snadný způsob vyhledání dat odpovídajících zadanému kritériu (podmínce) v oblasti buněk nebo celé tabulce. Lze rychle zobrazit a odstranit i duplicitní hodnoty. Po filtrování dat v oblasti buněk (tabulce) lze filtr buď použít znovu pro aktualizaci výsledků nebo lze filtr vymazat a znovu zobrazit všechna data. Kritéria pro filtrování lze různě kombinovat a vytvářet tak průnik či sjednocení dat. Po nastavení filtru se zobrazí jen ty řádky, které vyhovují zadanému kritériu (vyznačí se modře v záhlaví řádků), ostatní řádky se skryjí a v záhlaví filtrovaného pole (sloupce) se objeví ikona filtru. Pro operace filtru (a jak bylo řečeno výše i pro zjednodušení řazení dat) je třeba mít data v tabulce typu seznam. Pro vytváření seznamů platí určitá pravidla: • Na listu vytváříme zpravidla jen jeden seznam, od ostatních dat je oddělen aspoň jedním prázdným řádkem a sloupcem. • První řádek seznamu musí obsahovat názvy polí. Tyto musí být v jediné buňce (je povoleno zalomení textu, není povoleno slučování názvů ve více buňkách)). Název pole musí být pro každé pole (sloupec) jedinečný. • V jednom poli (sloupci) musí být data stejného datového typu (např. texty, čísla, stejný typ vzorce) – znamená to tedy, že řádky osahující sumární výpočty, musí být od seznamu odděleny např. prázdným řádkem. Tabulka, kterou budeme používat v dalším výkladu je ukázkou tzv. seznamu. Datum Datum Země odjezdu příjezdu 3180 Apeniny 27.5.11 5.6.11 Itálie 1229 Bretaň 21.7.11 2.8.11 Francie 1243 Gaskoňsko 30.6.11 12.7.11 Francie 4332 Holandsko 8.7.11 16.7.11 Holandsko 1201 Horní Provence 2.6.11 14.6.11 Francie 5616 Hvar 21.9.11 26.9.11 Chorvatsko 4321 Irsko 12.6.11 25.6.11 Irsko 4325 Irsko 15.8.11 28.8.11 Irsko 1303 Irsko 20.8.11 4.9.11 Irsko 4315 Island 23.7.11 7.8.11 Island 100 Jadran 29.5.11 5.6.11 Chorvatsko 1501 Jadran 9.9.11 2.10.11 Chorvatsko 3020 Korfu 27.4.11 8.5.11 Řecko 3023 Korfu 1.7.11 11.7.11 Řecko 4303 Korfu 10.9.11 25.9.11 Řecko 4306 Korsika 22.5.11 4.6.11 Itálie 4336 Lucembursko 29.7.11 7.8.11 Lucembursko 1105 Polární kruh 1.7.11 18.7.11 Norsko 1217 Provence 16.6.11 28.6.11 Francie 1106 Skandinávie 29.7.11 10.8.11 Švédsko 4311 Skotsko 1.7.11 16.7.11 V. Británie

Id. číslo

Název

Cena 16660 18980 19380 17690 18580 14160 22990 29800 20580 37480 15200 26950 18680 18980 19790 20490 17770 23490 18680 18380 20990

Kapitola 8: Filtrování dat Druhy filtrů

Nastavení filtru

44

Pro filtrování dat máme dvě možnosti: • Filtr – jednodušší varianta, která má některá omezení (zejména v tom, že kritéria se při opakování filtru musí nastavovat stále znovu). • Upřesnit – tzv. rozšířený filtr, kde se kritéria zapisují přímo do buněk v listu (dají se neomezeně opakovaně použít) a kritéria mohou být výrazně složitější. Tento typ filtru vyžaduje již zkušenější uživatele, proto se mu v tomto textu nevěnujeme. Filtr (dříve uváděný pod pojmem automatický filtr) se vyvolá tlačítkem Filtr na kartě Data v sekci Seřadit a filtrovat (kurzor musíme předtím umístit dovnitř seznamu, jinak systém ohlásí chybu). Systém umístí do buněk záhlaví seznamu stahovací šipky. Klepnutím na šipku např. v poli Země zobrazíme seznam možností nastavení filtrovacích kritérií včetně možnosti seřazení dat. Pokud vybereme volbu Filtry textu, systém nabídne různé možnosti zadávání podmínek pro text - viz obr. 37. OBR. 37 NABÍDKA FILTRU VE SLOUPCI ZEMĚ

Pomocí kterékoliv volby se vyvolá dialogové okno Vlastní automatický filtr – viz obr. 38, ve kterém je již vybraná relace nastavena a očekává se zadání (výběr) porovnávané hodnoty (u textů je možné ji vybrat z nabízeného seznamu). Jak je vidět z obrázku, lze v tomto okně zadat i logickou operaci a či operaci nebo nad dvěma podmínkami.


45

OBR. 38 ZADÁNÍ VLASTNÍ PODMÍNKY PRO FILTR

Zrušení filtru

Prvních 10

Filtr Prázdné

Ve spodní části nabídky je seznam jedinečných hodnot z filtrovaného pole doplněný zaškrtávacími tlačítky. Klepneme-li do Vybrat vše, všechna zaškrtnutí se zruší a můžeme zaškrtnout jen požadované země ze seznamu. Ty pak pomocí tlačítka OK vyfiltrujeme. Ve stavovém řádku se ihned objeví informace o tom, kolik zemí bylo na základě tohoto kritéria z celkového seznamu vyfiltrováno (např. pro zemi Irsko je to text 3 z 22 záznamů nalezeno). Tak má uživatel ihned k dispozici informace o četnosti vybraných záznamů. Nastavení filtru lze zrušit v tomto okně volbou Vymazat filtr ze Země nebo klepnutím na tlačítko Filtr na pásu karet Data. V prvním případě zůstanou stahovací šipky u jednotlivých záhlaví sloupců nastaveny a můžeme ve filtrování pokračovat. Nastavení kritérií můžeme i kombinovat – ve sloupci Země vybereme např. země Řecko a Itálie a ve sloupci cena zájezdy s cenou nižší než 19000. Po klepnutí na šipku ve sloupci Země klepneme do Vybrat vše a potom zaškrtneme pouze země Řecko a Itálie. Ve sloupci Cena nastavíme kritérium ceny menší než zadaná hodnota. Vyfiltrují se celkem 3 záznamy. Kritérium je tedy ve smyslu průniku nad dvěma sloupci. V těch sloupcích, ve kterých je nastaveno kritérium, se v záhlaví objeví symbol filtru. Pokud je nastaveno více kritérií ve více sloupcích, je lepší filtr zrušit pomocí tlačítka Vymazat na pásu karet Data. Na obr. 39 jsou uvedeny varianty filtru pro číselné hodnoty: Zajímavá je varianta Prvních 10, která umožňuje vyfiltrovat prvních nebo posledních n hodnot. Všimněte si, že u textových hodnot se tato volba nenabízí. Nově je doplněna varianta filtru nad a podprůměrných hodnot, která ve starších verzích byla realizovatelná pouze pomocí rozšířeného filtru. Pokud v některém sloupci zůstaly řádky s hodnotami prázdné, objeví se na seznamu u zaškrtávacích políček i položka Prázdné. Tak se dají např. z rozsáhlého seznamu vybrat jen řádky, kde data chybí a mohou se efektivně a rychle vložit, aniž bychom pracně prohledávali celý seznam.


46

OBR. 39 FILTR PRO ČÍSELNÉ HODNOTY . MOŽNOSTI

Zjištění a odebrání duplicit

Pozn. Ke zjištění (příp. odebrání) duplicitních záznamů je nutné použít rozšířený filtr nebo tlačítko Odebrat duplicity v sekci Datové nástroje na kartě Data. Vyfiltrovaná data lze buď prohlížet operativně na obrazovce, nebo je možné je nakopírovat např. do jiného listu (příp. sešitu) a uchovat je pro pozdější využití.

Závěrem Celý text je věnován MS EXCEL verze 2010, i když až na malé výjimky (nabídka SOUBOR) jsou všechny uváděné příklady použitelné i ve verzi 2007. Pokud se v textu uvádí pojem starší verze, mám tím na mysli verzi 2003 a starší. Doporučuji text nejen pročítat, ale současně si všechny uváděné akce zkoušet současně na počítači, příp. si zkusit příklady obměnit a vyzkoušet na nich nabyté vědomosti. Nejvíce se lze naučit pak praktickým používáním. Přeji mnoho zdaru ve zvládání tabulkového kalkulátoru.

Seznam literatury [1] BROŽ, M.: EXCEL 2007 podrobná uživatelská příručka, Computer Press, Brno 2007, ISBN 978-80-251-1822-1 [2] PECINOVSKÝ, J.: Microsoft Excel 2007/2010, Computer Press, Praha 2011, ISBN 978-80-251-3575-4 [3] BENÁČANOVÁ, H.: Tvorba aplikací v MS EXCEL, 1. část – Možnosti MS EXCEL (verze 2003), VŠE, Praha 2007, ISBN 978-80-245-1271-6

Vysoká škola ekonomická v v Praze Univerzita třetího věku. Microsoft Excel Učební text pro předmět U054. Ing. Helena Benáčanová, CSc

Recommend Documents