Tabulkový program Microsoft Office Excel 2003

Vysoká škola ekonomická v Praze Univerzita třetího věku

Tabulkový program Microsoft Office Excel 2003 Učební text pro předmět U054

Ing. Helena Benáčanová, CSc.

Praha 2007

© Ing. Helena Benáčanová, CSc. – Praha 2007 ISBN 978-1-56592-479-3

3

Obsah 1.

Seznámení se základními pojmy ..................................................................................... 5 1.1 Sešit ............................................................................................................................. 5 2. Začínáme pracovat........................................................................................................... 7 2.1 Režimy práce............................................................................................................... 7 2.2 Možnosti označování oblastí buněk ............................................................................ 7 2.3 Použití příkazů ............................................................................................................ 7 2.3.1. Výběr příkazů z nabídky................................................................................ 8 2.3.2. Využití tlačítek na panelu nástrojů ................................................................ 9 2.3.3. Využití místní nabídky................................................................................. 10 3. Tvorba tabulky ............................................................................................................... 12 3.1 Vkládání hodnot do buněk ........................................................................................ 12 3.1.1. Konstanty ..................................................................................................... 12 3.1.2. Výpočetní výrazy (vzorce)........................................................................... 14 3.1.2.1. Aritmetické operace....................................................................... 14 3.1.2.2. Standardní funkce .......................................................................... 15 3.1.2.3. Chybové stavy při vkládání výpočetních výrazů........................... 18 3.2 Práce s buňkami – opravy (editace) vstupu............................................................... 20 4. Formátování tabulky...................................................................................................... 22 4.1 Formátování na úrovni buňky ................................................................................... 22 4.1.1. Formátování textu ........................................................................................ 22 4.1.2. Formátování číselných hodnot..................................................................... 22 4.1.3. Další možné úpravy – zarovnání v rámci buňky či oblasti, použití okrajů, barva vzorků................................................................................................. 23 4.1.3.1. Zarovnání....................................................................................... 23 4.1.3.2. Ohraničení, barva pozadí a barva písma........................................ 24 4.2 Formátování na úrovni sloupce či řádku................................................................... 24 5. Kopírování a přesuny..................................................................................................... 26 5.1 Kopírování................................................................................................................. 26 5.2 Přesuny...................................................................................................................... 26 5.3 Relativní a absolutní odkazy ..................................................................................... 28 6. Práce s listy ..................................................................................................................... 30 7. Grafická interpretace..................................................................................................... 32 7.1 Tvorba grafu.............................................................................................................. 32 7.2 Řešení příkladu – výsečový graf ............................................................................... 33 7.3 Úpravy hotového grafu ............................................................................................. 37 7.4 Další možné úpravy grafu ......................................................................................... 41 8. Řazení dat v tabulce ....................................................................................................... 42 9. Nastavení kontrol pro vstup dat do buněk................................................................... 44 9.1 Omezení zadání nesprávných dat do buňky.............................................................. 44 9.2 Zobrazení zprávy při zadávání dat ............................................................................ 45 9.3 Chybové hlášení při zadání nesprávného vstupu ...................................................... 46 10. Filtrování dat .................................................................................................................. 48

4

Kapitola 1: Seznámení se základními pojmy

Sešit

Sloupce a řádky Listy

Buňka

Oblast

1.

Seznámení se základními pojmy

1.1

Sešit

5

Základním typem dokumentu při práci se systémem Excel je sešit. Je to v podstatě soubor, se kterým se pracuje stejným způsobem jako s kterýmkoli jiným souborem pod operačním systémem WINDOWS - lze jej otevřít (načíst do vnitřní paměti a využít při práci s ním veškeré nabízené funkce tabulkového kalkulátoru), uložit jej do vnější paměti nebo jej zavřít. Sešit má své jméno - systém přiřadí při otevření každého nového sešitu standardní jméno SESITn, kde n je přirozené číslo. Ponechat pojmenování sešitů takto je při větším počtu souborů nevýhodné (snižuje se přehlednost a orientace v jednotlivých sešitech). Doporučuji sešit pojmenovat vlastním (zvoleným) jménem, k němuž systém automaticky doplňuje standardní příponu XLS. Se sešitem se pracuje v rámci okna, do kterého se sešit automaticky otevírá. Na obrazovce lze otevřít i více oken najednou, což umožňuje snadné formátování, opravy, porovnávání údajů jak na různých místech jednoho sešitu, tak i ve více sešitech najednou. V rámci listu je k dispozici 65536 řádků a 256 sloupců. Sloupce jsou označovány v záhlaví pomocí písmen (A až Z a pokračují kombinací písmen AA až IV), řádky se označují pomocí čísel. Třetím rozměrem v sešitu jsou listy – v jednom sešitu jich může být až 255 (jejich počet je limitován pouze velikostí vnitřní paměti). Při otevření obsahuje sešit obvykle 1 3 listy pojmenované LIST1 až LIST3. Názvy listů se objevují na záložkách (ouškách listů) ve spodní části okna sešitu. Obsah listu se zobrazí do okna klepnutím na jeho záložku (ouško). Nejmenší možná oblast pro zápis dat je buňka - určená souřadnicemi: označení listu, záhlaví sloupce a řádku. Pro toto označení se používá pojem odkaz (příp. pojem adresa, který je běžně známý z ostatních tabulkových kalkulátorů). Příklady odkazů: LIST1!A13 13. řádek prvního sloupce v listu pojmenovaném LIST1, LEDEN!G25 25. řádek sedmého sloupce v listu pojmenovaném LEDEN, Několik řádků, sloupců, případně kombinace obou (tedy obvykle množina souvislých buněk) se označuje jako oblast (blok). Např. odkaz A1:A10 znamená souvislou oblast 10 řádků v prvním sloupci. (Označení listu se neuvádí v tom případě, že odkazujeme na buňky v právě aktivním listu – v tom, ve kterém stojí kurzor). Oblast, stejně jako buňku, je možné identifikovat kromě odkazu i pomocí definice jména oblasti (např. jméno CENA přiřadíme oblasti C2:C10, ve které jsou uvedeny ceny - např. výrobků).

1

Počet listů a řada dalších nastavení je záležitostí definice předvolby. Nastavené předvolby je možné samozřejmě podle potřeby měnit (v rámci jednotlivých karet příkazu NÁSTROJE Možnosti).

Kapitola 1: Seznámení se základními pojmy

6

Kapitola 2: Začínáme pracovat

Pracovní režim

2.

Začínáme pracovat

2.1

Režimy práce

7

Základním pracovním režimem je režim Připraven (indikace provozního režimu se zobrazuje ve stavovém řádku, který je určen pro vstup hodnot). Jakmile začneme vkládat hodnoty z klávesnice, systém přechází do režimu Zadání (opravujeme-li chyby ve vstupních datech, můžeme vyvolat režim Úpravy). Pozor! Ti, kdo začínají s MS EXCEL pracovat prvně, mívají většinou problém s tím, že vstup do buňky neukončí (zůstávají stále v režimu zadání, příp. úpravy). Pak není možné s buňkou vůbec dál pracovat (např. ji formálně upravovat apod.), to lze pouze v režimu Připraven. Vstup lze ukončit nejlépe pomocí klávesy ENTER.

2.2 Označování buněk

Možnosti označování oblastí buněk

Před použitím některých operací je nutné nejprve označit buňky, se kterými budeme pracovat. Veškeré další příklady označování uvádíme pouze s použitím myši, protože je tento způsob určitě nejracionálnější. • označení buňky – klepnutí myší na buňku, • označení oblasti – tažení myší napříč z rohu do rohu oblasti, • označení celého sloupce (až do řádku 65536) – klepnutím na záhlaví sloupce, • označení celého řádku (až do sloupce IV) – klepnutím na záhlaví řádku, • označení všech buněk listu – klepnutí na políčko vlevo od záhlaví sloupců (příp. kombinace kláves CTRL A), • označení nesouvislé oblasti – označíme první část oblasti tažením myší, stiskneme a držíme klávesu CTRL a klepnutím (či tažením) označíme další buňku (či buňky) v nesouvislé oblasti, • zrušení označení buňky, oblasti, resp. listů se provádí klepnutím na neoznačenou buňku nebo ouško listu. Pozn. Pohyb v označeném úseku je možný klávesou ENTER směrem dopředu a kombinací SHIFT ENTER směrem zpět.

2.3 Příkazy

Použití příkazů

Činnost programu se řídí pomocí příkazů. Řadu operací lze provést mnoha různými způsoby: • výběrem příkazu z nabídky (buď myší nebo kurzorovými klávesami), • klepnutím na tlačítko v panelu nástrojů, • využitím tzv. místní nabídky (vyvolá se klepnutím na pravé tlačítko myši), • použitím kombinace tzv. zkratkových kláves (např. CTRL C pro vložení kopírovaného obsahu do schránky). Pozn. Zdůrazňuji, že před použitím většiny příkazů musíme nejprve označit buňky, se kterými budeme pracovat.

Kapitola 2: Začínáme pracovat 2.3.1. Výběr z nabídky

Dialogové okno

8

Výběr příkazů z nabídky

Obecně platí, že výběr příkazu z nabídky se provádí klepnutím na příslušný příkaz v nabídkovém pruhu nebo stisknutím klávesy ALT a uvedením podtrženého písmene příkazu z pruhu nabídek. Klepnutím na příkaz vybraný ze seznamu se buď příkaz přímo realizuje nebo zobrazí ještě vedlejší nabídku (je-li za jménem příkazu znak ), která obsahuje dodatečné a upřesňující příkazy (viz obr. 1). Zrušení výběru příkazu je možné klávesou ESC. Některé příkazy (v nabídce za jejich jménem následují tři tečky ...) zobrazí dialogové okno, v němž je nutné zadat další volby pro provedení dané operace. Některá dialogová okna se skládají z více karet, z nichž každá má pojmenovanou záložku. Klepnutím myší na záložku zobrazíme příslušnou kartu – viz obr. 2. Pozn. V rámci seznamu příkazů jsou pro úplnost uvedena i adekvátní tlačítka z panelu nástrojů a zkratkové příkazy (pokud existují). Příkazy, které jsou v nabídce uvedeny šedou barvou, nejsou v daném okamžiku dostupné. OBR. 1: UKÁZKA NABÍDKY (VČETNĚ ZOBRAZENÍ VEDLEJŠÍ NABÍDKY)

Výběr volby

• • •

Jednotlivé volby pak můžeme vybírat následujícími způsoby: výběrem z nabízeného seznamu (tzv. rozevírací, rozbalovací, stahovací seznam), tj. posunem po seznamu a klepnutím na vybranou položku, zaškrtnutím políčka (tzv. zaškrtávací políčko) u volby (po klepnutí se v políčku objeví znak zaškrtnutí) nebo naopak zrušením zaškrtnuté volby (opětným klepnutím do políčka tento znak zmizí), volbou jedné z možností (přepínač), přičemž ostatní se automaticky vylučují.


9

OBR. 2: UKÁZKA DIALOGOVÉHO OKNA SE ZÁLOŽKAMI JEDNOTLIVÝCH KARET

Po zadání volby lze v otevřeném dialogovém okně klepnutím myší na příslušné tlačítko (obvykle jde o tlačítko OK, které je ekvivalentem klávesy ENTER) realizovat vybranou operaci či některou z doplňkových operací (tlačítka Možnosti, Náhled, apod.). Tlačítko Storno (ekvivalent klávesy ESC) pak umožní práci s příkazem přerušit. Některé z dialogových oken mohou obsahovat i hypertextový odkaz, pomocí kterého lze vyvolat i nápovědu (např. okna průvodce funkcemi). 2.3.2. Panely nástrojů

Využití tlačítek na panelu nástrojů

Standardně jsou na obrazovce zobrazeny dva panely nástrojů - panel Standard a panel Formát. Pozn. Panely nástrojů Standard a Formát bývají při otevření Excelu umístěny v jednom řádku. Řada tlačítek se pak nezobrazuje a museli bychom je vždy na panel přidávat, takže je lepší nastavit si zobrazení panelů ve dvou řádcích – viz obr. 3. OBR. 3: MOŽNOSTI PRÁCE S TLAČÍTKY NA PANELECH NÁSTROJŮ


Tlačítka

Panely nástrojů obsahují jednotlivá tlačítka, která umožňují rychlé provedení některých často používaných příkazů (např. místo výběru příkazu BUŇKY z nabídky FORMÁT, karty Písmo v dialogovém okně a zadáním volby Tučné, můžeme pouze klepnout myší na tlačítko Tučné (Bold) v panelu nástrojů Formát). Zastavíme-li na chvíli pohyb myši na příslušném tlačítku, objeví se bublinová nápověda obsahující popis funkce tohoto tlačítka. Zobrazení dalších panelů či ukrytí některého z panelů se realizuje zaškrtnutím (výběrem) ze seznamu v nabídce ZOBRAZIT, příkaz PANELY NÁSTROJŮ nebo stisknutím pravého tlačítka myši v prostoru již zobrazených panelů nástrojů. 2.3.3.

Místní nabídka

10

Využití místní nabídky

Místní nabídka (vyvolá se klepnutím pravého tlačítka myši na objekt) obsahuje roletové menu s výběrem příkazů pro daný objekt, které jsou realizovatelné v daném kontextu. Označíme-li např. buňku či oblast, pak místní nabídka obsahuje příkazy z nabídky ÚPRAVY (kopírování, vyjímání, vkládání, odstranění řádků či sloupců, výmaz obsahu, formát buněk apod. – viz obrázek 4). Je-li kurzor umístěn v prostoru oušek, místní nabídka bude obsahovat menu pro jednotlivé operace při práci s listy. Je-li označen graf, vyvolá se opět místní nabídka příslušná k danému objektu, tedy ke grafu. OBR. 4: MÍSTNÍ NABÍDKA

Odvolání a opakování příkazu Zpět

Pokud uděláme chybu a chceme provedenou operaci anulovat, můžeme provedený příkaz odvolat pomocí příkazu ZPĚT (nejkratší způsob vyvolání je klepnutí na tlačítko Zpět v panelu nástrojů příp. použití kláves CTRL Z). Seznam předchozích operací se vyvolá klepnutím na tlačítko rozevíracího


Opakovat

11

seznamu umístěné vpravo od vlastního tlačítka Zpět. Existují však i operace, které vrátit nelze (např. všechny z nabídky SOUBOR). Pokud potřebujeme poslední zadaný příkaz zopakovat, lze klepnout na tlačítko Opakovat v panelu nástrojů.

Kapitola 3: Tvorba tabulky

Naplnění buněk

3.

Tvorba tabulky

3.1

Vkládání hodnot do buněk

Prvním krokem po otevření sešitu je obvykle naplnění jednotlivých buněk vstupními hodnotami. Buňka může mít obsah, formát, příp. komentář (tyto části může obsahovat všechny najednou nebo v různých kombinacích, nejběžnější je kombinace obsahu a formátu). Obsahem buňky mohou být: • Konstanty, • výpočetní výrazy (vzorce). 3.1.1.

Konstanty

12

Konstanty

Konstanty mohou být číselné (numerické) – např. číslo 5, 1000, 23,5, 10%, příp. datum – viz dále, Pozn. Desetinná čísla se zadávají s desetinnou čárkou, procenta se zadávají buď tak, jak je výše uvedeno (včetně symbolu %) nebo ve formě hodnoty 100x menší, která se pak na procenta upraví pomocí formátu. • abecední (textové, alfabetické) – např. Novák, květen, apod., • abecedněčíselné (alfanumerické) - např. 1. dekáda, rok 2006 apod. Při vkládání dat postupujeme následujícím způsobem: přesuneme kurzor (pomocí kurzorových šipek nebo myší) do buňky, do které chceme vkládat data. Tato buňka je nyní aktivní (je silně orámována) a lze do ní vložit vstup z klávesnice. Vstup ukončujeme klávesou ENTER a kurzor se automaticky přesouvá směrem dolů na další buňku. Vstup lze ukončit i pomocí kurzorových šipek (či pohybem myší), bez použití klávesy ENTER. Je to rychlejší zejména tehdy, když další vstup pokračuje v buňce v jiném směru než dolů. Obsah buňky se zobrazuje při vkládání dat jak v řádku vzorců (řádek nad záhlavím sloupců), 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í v ní kurzor). Při vstupu konstant je obsah na všech těchto uvedených místech totožný. Jde-li o vstup abecední nebo abecedněčíselné hodnoty, bude po ukončení vstupu hodnota v buňce automaticky zarovnána zleva. Numerické hodnoty jsou po ukončení vstupu v buňce zarovnány zprava. Tyto standardní formy uložení dat lze v buňce samozřejmě podle potřeby i změnit pomocí formátování (např. vycentrovat apod.). Pokud se při vkládání dat zmýlíme, lze vstup přerušit klávesou ESC nebo případně ještě před jeho ukončením jej opravit pomocí klávesy Backspace. Pokud vstup již ukončíme, je oprava možná jen v režimu Úprava, který musíme ze základního režimu Připraven příslušným způsobem vyvolat – viz dále kap. Úprava dat v buňkách. •


13

Práce s datumem Datum

S položkami typu datum a čas lze v tabulkových kalkulátorech pracovat dvojím způsobem: buď jako s textem nebo jako s položkou skutečně datumovou (je interpretována jako numerický vstup, se kterým lze pak provádět aritmetické operace a využít i řady standardních funkcí nabízených pro práci s datumem a časem). Vstup datumu je vyhodnocen systémem jako text buď v případech chybně zadaného vstupu (např. zapsaný vstup 11.11. nebo 11. listopadu není akceptován systémem jako datum), nebo uvedeme-li na začátku datumu např. mezeru. Obvyklejší je způsob práce s datumem ve smyslu numerickém. Datum vkládáme ve tvaru: dd.mm.rrrr, kde dd znamená dvojčíslo dne, mm dvojčíslí měsíce (nevýznamné nuly se neuvádějí) a rrrr rok. Jednotlivé části jsou od sebe odděleny tečkami (systém akceptuje jako oddělovač jednotlivých částí datumu i lomítka /, příp. lze použít i pomlčku –). Pokud uvedeme pouze tvar rr, doplní se automaticky předčíslí století, tedy 20 (6 na vstupu představuje rok 2006). Pokud rok neuvedeme vůbec, systém doplní automaticky rok současný – např. 11.1 je ekvivalentní zápisu 11.1.2006. Po vstupu hodnoty datumu se v buňce uloží numerická hodnota, která odpovídá počtu dní od 1.1.1900 (pokud není v předvolbě nastaveno jinak) do zadaného datumu (např. u datumu 15.1.2006 je to např. hodnota 38732). Zápis v buňce je však naformátován standardním formátem pro datum – tedy oddělení jednotlivých částí pomocí teček. Pokud chceme změnit formát zápisu datumu, vyvoláme příkaz BUŇKY z nabídky FORMÁT a vybereme kartu Číslo. Zadáme druh formátu Datum a můžeme vybírat z následujících možností (viz obr. 5): OBR. 5: PŘEHLED DATUMOVÝCH FORMÁTŮ


14

Příklad zápisu datumu: Do buňky vložíme 15.1.2006 nebo 15.1 nebo v obou variantách můžeme použít jako oddělovač lomítka či pomlčky. Pozn. Vstup datumu do buněk lze realizovat i přímo některou z forem uvedených ve formátech na obr. 5 – tedy např. 15.březen 2006 apod. Pokud bychom chtěli zjistit skutečnou hodnotu, která je v buňce uložena (číslo odpovídající počtu dní od začátku minulého století), zvolíme příkaz BUŇKY z nabídky FORMÁT a na kartě Číslo zvolíme druh formátu Číslo (počet desetinných míst zadáme nulový) nebo použijeme příkaz ÚPRAVY, VYMAZAT FORMÁTY. Existuje i skupina formátů pro formátování času. Jednotlivé části – hodiny, minuty a vteřiny se pak mezi sebou oddělují dvojtečkou, např. 10:20:55. 3.1.2. Výpočetní výrazy

Výpočetní výrazy (vzorce)

Pomocí výpočetních výrazů (vzorců) získáme snadno a rychle řadu nových hodnot, odvozených od původně vložených konstant. Výpočetní výrazy mohou být vyjádřeny dvojím způsobem: • pomocí zápisu příslušného aritmetického výrazu (operandy vyjádřené pomocí odkazů, proložené příslušnými aritmetickými operátory), postup výpočtu stanoví sám uživatel, • pomocí zápisu standardní funkce (postup výpočtu je uložen v systému a vyvolá se pomocí jména standardní funkce s uvedením příslušných požadovaných argumentů). 3.1.2.1.

Aritmetické operace

Vkládání vzorců

Vzorce se vkládají do příslušných buněk po uvedení znaku rovnítko (=). Pro vkládání vzorců používáme tzv. vytyčovacího módu (jde o velmi efektivní způsob), kdy z klávesnice vkládáme pouze uvozující znak = a aritmetické operátory (ve složitějších výrazech, kdy potřebujeme upravit prioritu pořadí operací to mohou být i závorky). Operandy vkládáme pomocí myši - myší postupně ukazujeme na buňky obsahující jednotlivé operandy a do výrazu se automaticky dosazují odkazy na tyto buňky. Vkládání se ukončuje klávesou ENTER, přičemž v buňce dochází ihned k výpočtu výsledné hodnoty.

Příklad V buňce A2 máme hodnotu 10 a v buňce B2 hodnotu 20. Do buňky C2 chceme vložit vzorec součtu obou buněk a do buňky D2 obě hodnoty vynásobit. Postupujeme takto: z klávesnice vložíme znak =, myší klepneme na první operand (buňka A2), z klávesnice vložíme znaménko + a klepneme na druhý operand (buňka B2)… atd. Vstup vzorce ukončíme klávesou ENTER. Podobným způsobem postupujeme v buňce D2. Po ukončení vstupu je vzorec v buňce ihned přepočítán (objeví se v ní vypočtená hodnota), v řádku vzorců však zůstává text vzorce.


15

V buňce C2 je tedy výraz =A2+B2 V buňce D2 je výraz =A2*B2 Operátory používané v EXCELu: + (sčítání) - (odčítání) * (násobení) / (dělení) ^ (umocnění) Při výpočtu vzorců se dodržují matematické priority, v případě jejich záměrného porušení je nutné použít závorky (pouze kulaté a je nutné kontrolovat jejich párovost, jinak dochází k chybám). Při vstupu vzorců je obsahem buňky zapsaný vzorec (jeho text je vidět v řádku vzorců), hodnota buňky je na základě tohoto vzorce vypočítána a po ukončení vstupu se zobrazuje přímo v buňce. 3.1.2.2. Standardní funkce

Argumenty

Průvodce funkcí

Standardní funkce

Standardní funkce lze rozdělit do několika skupin: matematické, logické, pro datum a čas, statistické, databázové, textové, finanční, informační. V rámci těchto základních skupin existuje celá řada standardních funkcí a není možné všechny zde popisovat – uvádím pouze některé příklady. Obecný zápis funkce je = NÁZEV(seznam argumentů). V závorce za jménem standardní funkce se uvádějí požadované argumenty (oddělovačem argumentů je v Excelu znak středník ;). Některé funkce nemají žádné argumenty, některé mají všechny argumenty povinné a některé mohou mít i část argumentů nepovinných. Standardní funkci lze do buňky zapsat i přímo z klávesnice – vyžaduje to však již určité znalosti (musíme znát přesné jméno funkce, charakter a pořadí jejích argumentů a pravidla pro zápis funkce – závorky, oddělovače apod.), proto tento způsob nedoporučuji. Jednodušší je vkládat standardní funkci pomocí Průvodce funkcí, kterého lze vyvolat pomocí tlačítka nebo příkazem FUNKCE z nabídky VLOŽIT. Průvodce funkcí nám umožní zadat pouze jméno funkce (jejím výběrem z nabízeného seznamu) a zadat argumenty pomocí vytyčování. Pozn. Standardní funkce mohou být i vnořovány do sebe (je povoleno max. 7 vnoření). Nejvíce používané standardní funkce jsou dostupné na tlačítku Autosum (počínaje verzí 2003). Klepnutím do jeho levé části vyvoláme funkci SUMA (součet) a klepnutím do šipky vpravo se vyvolá seznam funkcí pro průměr, minimum, maximum a četnost. Poslední řádek seznamu obsahuje odkaz na průvodce funkcí, pomocí něhož můžeme vybrat požadovanou funkci a použít ji pro výpočet v buňce.

Příklad

Tlačítko Autosum

V buňkách A1 až A4 máme 4 hodnoty a do buňky A5 je máme sečíst. Pro sčítání více než 3 operandů v souvislé oblasti (nelze použít, je-li oblast prázdná) je rychlejší a efektivnější použití standardní funkce než operace sčítání. Použijeme tedy tlačítko Autosum. Řešení má 2 možné varianty:


16

a) Kurzor umístíme do 1. volného řádku (sčítání ve sloupci) nebo sloupce (sčítání v řádku) a stiskneme tlačítko Autosum. Systém sám vloží funkci SUMA a nabídne oblast sčítanců (hranici tvoří prázdná buňka nebo buňky obsahující nenumerický údaj). Pokud souhlasíme s nabízenou označenou oblastí, klávesou ENTER potvrdíme zapsaný vzorec, v opačném případě můžeme velikost oblasti vhodně myší upravit a pak klávesou ENTER potvrdit – viz obr. 6. OBR. 6: POUŽITÍ TLAČÍTKA AUTOSUM

b) Existuje i jiná možnost: Sčítáme-li hodnoty v řádku či sloupci, můžeme pomocí myši tuto oblast hodnot označit a stisknout tlačítko Autosum. Součet se ihned vypočte a umístí do první volné buňky vpravo (u řádkového součtu) nebo dolů (u sloupcového součtu). Stejného efektu dosáhneme, když při označení hodnot ve sloupci či řádku označíme navíc ještě prázdnou buňku vpravo (u řádkového součtu) nebo pod touto oblastí (u sloupcového součtu). Sčítáme-li více sloupců či řádků najednou v souvislé oblasti, vyznačíme tažením myší všechny buňky obsahující sčítané hodnoty a navíc ještě jeden prázdný sloupec vpravo či prázdný řádek dole nebo obojí, do kterého se po stisknutí tlačítka Autosum zapíší všechny součty (řádků či sloupců). Tím odpadá i následné kopírování výrazu do dalších buněk – viz obr. 7. OBR. 7: SČÍTÁNÍ V ŘÁDCÍCH A SLOUPCÍCH S VYUŽITÍM TLAČÍTKA AUTOSUM

Pozor! Vzorec křížového součtu je po vložení výpočtu pomocí Autosum koncipován jako řádkový součet – viz obr. 8. OBR. 8: ORIENTACE KŘÍŽOVÉHO SOUČTU PŘI POUŽITÍ TLAČÍTKA AUTOSUM


17

Jak je vidět z obr. 9, nabízí seznam tlačítka Autosum i další funkce. OBR. 9: SEZNAM FUNKCÍ NA TLAČÍTKU AUTOSUM

Součet Průměr Počet Min Max

Standardní funkci Součet vyřešíme lépe využitím volby na levé části tlačítka Autosum (viz výše). Při zadání všech dalších funkcí ze seznamu na obr. 9 postupujeme opět stejným způsobem jako u součtu (viz výše popsané varianty). Funkce PRŮMĚR spočítá průměrnou hodnotu v zadané oblasti, funkce POČET vrací počet číselných hodnot v oblasti (chceme-li spočítat počet hodnot, které jsou vyjádřeny textem, musíme použít funkci POČET2). Funkce MAXIMUM a MINIMUM zjistí extrémní hodnotu v zadané oblasti. Volbou Další funkce… vyvoláme průvodce funkcemi – viz obr. 10: OBR. 10 DIALOGOVÉ OKNO PRŮVODCE FUNKCÍ

Skupiny funkcí

Na obrázku je vidět přehled skupin funkcí. Skupina Naposledy použité obsahuje názvy funkcí, které jsme již při práci s Excelem alespoň jednou použili a při novém použití je pak nemusíme hledat v příslušné skupině. Skupina Vše obsahuje seznam všech standardních funkcí seřazených abecedně. Vložení funkce pomocí průvodce si ukážeme na příkladu použití funkce ZAOKROUHLIT.


18

Příklad Máme zaokrouhlit výpočet podílu dvou buněk - B5 a C5 na 2 desetinná místa OBR. 11: VLOŽENÍ STANDARDNÍ FUNKCE ZAOKROUHLIT

Argument Číslo je hodnota, kterou zaokrouhlujeme (je vyjádřena buď pomocí adresy nebo vzorcem). Druhý argument (Číslice) určí, jak se číslo zaokrouhluje (uvedeme-li číslo větší než 0, zaokrouhlí se hodnota na tento počet desetinných míst, číslo 0 a menší než 0 znamená zaokrouhlení na řád čísla – tedy 0 na celá čísla, -1 na celé desítky, -2 na celé stovky atd.). V dialogovém okně vidíme pro kontrolu i výsledek funkce. Pomocí tlačítka OK v dialogovém okně Průvodce se zápis standardní funkce ukončí, v buňce se opět objeví vypočtená hodnota a v řádku vzorců je uveden zápis =ZAOKROUHLIT(B5/C5;2) Pozn. V rámci Excelu existuje několik variant standardních funkcí pro zaokrouhlení. Standardní funkce ZAOKROUHLIT zaokrouhluje podle běžných matematických zvyklostí. Existuje však i možnost účetního zaokrouhlování (vždy nahoru nebo dolů – funkce ROUNDUP, ROUNDDOWN – se stejným charakterem argumentů jako funkce ZAOKROUHLIT) nebo funkce u nichž je význam 2. argumentu jiný – jde o zaokrouhlování na násobky čísla (funkce ZAOKR.NAHORU a ZAOKR.DOLU). 3.1.2.3. Chyba

Chybové stavy při vkládání výpočetních výrazů

Pokud dojde při vyhodnocování výpočetního výrazu 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ů (existují ještě některé další chybové situace, zde se jimi však nebudeme zabývat): #HODNOTA! znamená, že jsme použili chybný typ operandu nebo argumentu u standardní funkce (např. ve výrazu =A3*B5 je jedním z operandů buňka obsahující nenumerická data nebo např. ve výrazu =B5:D5 chybí jméno standardní funkce),

Kapitola 3: Tvorba tabulky #NÁZEV?

#DIV/0!

19

byl použit chybný název (např. místo názvu standardní funkce ZAOKROUHLIT jsme napsali ZAKROUHLIT nebo bylo ve výrazu použito jméno oblasti (identifikátor), který nebyl v listu předtím definován apod.), došlo k pokusu o dělení nulou. K pokusu o dělení nulou může dojít buď kvůli nesprávnému použití relativních, smíšených nebo absolutních odkazů nebo tam, kde používáme operaci dělení a chybí data, na která se odkazuje jmenovatel ve výrazu. Doporučujeme proto u vzorců dělení vždy používat standardní funkci KDYŽ, která v případě nulového jmenovatele vloží do buňky buď mezeru nebo vhodný text a zamezí realizaci operace dělení.

Pozn. Zápis ####### v buňce není v pravém slova smyslu chybovým stavem, znamená, že buňka je přeplněná a je nutno ji rozšířit. Funkce KDYŽ Když

Další velmi frekventovanou standardní funkcí je Logická standardní funkce KDYŽ. Forma zápisu: =KDYŽ(podmínka; ano; ne) Funkce má 3 argumenty (oddělené středníky) (pokud ji do buňky vkládáme pomocí Průvodce funkcí, středníky jsou doplněny automaticky): Podmínka Podmíněný výraz, na jehož výsledek vyhodnocení reagují další dva argumenty, může obsahovat relační operátory <, >,<=, >=, =, <>. ano Je-li výsledkem podmíněného výrazu logické ano (PRAVDA), vloží se do buňky výraz uvedený v tomto argumentu ne Je-li výsledkem podmíněného výrazu logické ne (NEPRAVDA), vloží se do buňky výraz uvedený v tomto argumentu Argument Ano či Ne mohou být vzorce (výpočetní výrazy) nebo konstanty (ty pak musí být uvedené v uvozovkách; pokud použijeme průvodce funkcí, systém si sám uvozovky doplní).

Příklad 1 Máme dva sloupce čísel (A a B) a potřebujeme je mezi sebou ve sloupci C vydělit. Pokud si nejsme jistí, že hodnoty ve sloupci jmenovatele (sloupec B) budou vždy vyplněné (nezůstanou prázdné – data pro ně např. neexistují), nemůžeme do buňky ve sloupci C vložit přímo vzorec obsahující dělení. Je třeba vstup tohoto vzorce do buňky podmínit dotazem na to, zda jmenovatel není nulový (jinak by došlo k pokusu o dělení nulou – chybová hláška #DIV/0!). Použijeme tedy funkci KDYŽ – v případě, že jmenovatel je nulový, budeme do buňky výsledku vkládat buď pomlčku nebo mezeru (příp. nějaký vysvětlující text), v opačném případě můžeme vložit vzorec pro dělení. Zápis v buňce vypadá takto: =KDYŽ(B2=0;“ “;A2/B2) Podmínka

mezera

vzorec pro dělení


20

Příklad 2 Na základě celkového součtu bodů (sloupec B), doplňte do sloupce Výsledek (sloupec C) buď text Započteno nebo Nezapočteno (pro zápočet je nutné získat minimálně 21 bodů). Jméno Celkem Výsledek bodů Houžvička 21 započteno Hruška 22 započteno Balabán 19 nezapočteno Doudová 20 nezapočteno V první buňce sloupce Výsledek bude standardní funkce zapsána takto: =KDYŽ(B2<21;“nezapočteno“; “započteno“) nebo takto: =KDYŽ(B2>=21;“započteno“; “nezapočteno“) Pozn. V argumentu standardní funkce je v tomto případě použita konstanta. Správné řešení by mělo obsahovat možnost měnit bodovou hranici bez nutnosti upravovat vzorec standardní funkce. Proto uložíme bodovou hranici např. na buňku E5 (tj. v buňce E5 uvedeme hodnotu 21) a vzorec upravíme: =KDYŽ(B2<$E$5;“nezapočteno“; “započteno“) Odkaz na buňku E5 je nutné uvádět formou odkazu absolutního – viz výklad v kap. Relativní a absolutní odkazy. Všechny oblasti uváděné v argumentech standardních funkcí lze uvádět buď pomocí odkazů nebo s využitím identifikace jménem. Např. místo zápisu =SUMA(A2:A10) lze zapsat =SUMA(SLOUPEC_A). Předtím je však nutné buňky ve sloupci A ve druhém až desátém řádku pojmenovat. Postup je následující: označíme buňky A2 až A10. Zvolíme z nabídky VLOŽIT příkaz NÁZEV a vybereme volbu DEFINOVAT. V dialogovém okně zkontrolujeme odkaz na označenou oblast a zapíšeme text názvu - zde SLOUPEC_A. Pokud jméno nezrušíme, můžeme vždy oblast buněk A2 až A10 uvádět ve výrazech pomocí jména SLOUPEC_A.

Dnes

Nyní

Pozn. Seznam všech jmen definovaných v rámci listu získáme klepnutím na šipku rozevíracího seznamu v poli názvů. Pokud bychom potřebovali použít toto jméno ve vzorci (příp. jako argument standardní funkce), lze seznam jmen vyvolat pomocí klávesy F3 a z něj jméno přímo do vzorce vložit. Některé další standardní funkce: DNES() standardní funkce (bez argumentů), která vrací systémové datum (pokud je v počítači datum správně nastaveno, odpovídá dnešnímu datumu), NYNÍ() standardní funkce (bez argumentů), která vrací systémové datum včetně času.

3.2 Editace buňky

Práce s buňkami – opravy (editace) vstupu

Pokud vstup ukončíme, lze obsah v buňce měnit pomocí výmazu, přepisem nebo editací (příp. lze použít i fyzického odstranění celého sloupce či řádku).


21

Možnosti oprav: • výmaz obsahu buňky pomocí klávesy DEL (pokud rušíme více než jednu buňku, tedy oblast, je nutné tuto oblast před stiskem klávesy DEL celou označit),

• •

•

Pozn. Pokud má buňka kromě obsahu i nějaký formát (např. byla použita barva pro text, ohraničení apod.), tento formát po výmazu obsahu klávesou DEL v buňce i nadále zůstává. Výmaz formátu lze řešit pomocí nabídky ÚPRAVY VYMAZAT FORMÁT (vymaže pouze formát) nebo ÚPRAVY VYMAZAT VŠE (vymaže obsah včetně formátu). přepis obsahu buňky (náhrada obsahu buňky úplně jiným vstupem) – nastavíme kurzor do opravované buňky, zadáme nový vstup a původní obsah buňky se tímto novým vstupem přepíše, dílčí oprava obsahu, editace buňky (např. překlepů v textu apod.). V tomto případě musíme vyvolat režim Úpravy. Tento režim lze vyvolat třemi různými způsoby - buď klávesou F2 nebo poklepáním na opravovanou buňku nebo klepnutím myší do řádku vzorců. V prvních dvou případech se úpravy provádějí přímo v prostoru buňky, v poslední variantě editujeme obsah buňky v rámci řádku vzorců. Ve všech výše uvedených případech se pak můžeme myší či kurzorovými šipkami pohybovat po celém obsahu buňky a vhodným způsobem překlepy opravovat. Pokud potřebujeme vymazat např. více znaků najednou, lze využít i označení části textu v buňce a ten pak klávesou DEL vymažeme. Úpravy se ukončují klávesou ENTER, zrušení sloupce či řádku - lze zrušit nejen obsah v buňkách, ale je možné odstranit celý sloupec či řádek (nebo více sloupců či řádků najednou). Nastavíme kurzor kamkoli do rušeného řádku či sloupce, zvolíme příkaz ÚPRAVY ODSTRANIT a v dialogovém okně Odstranit vybereme volbu Celý sloupec nebo Celý řádek. Pozor! Volba Posunout buňky vlevo či nahoru umožní pouze přesun aktivní buňky (či označené oblasti) daným směrem.

•

Pozn. V případě, že je označen celý sloupec či řádek (klepnutím myší do jeho záhlaví), příkazem ÚPRAVY ODSTRANIT se nevyvolá dialogové okno Odstranit, ale dojde okamžitě k odstranění řádku či sloupce. Pokud je označeno více sloupců či řádků, dojde k zrušení všech označených objektů. vložení sloupce či řádku - tato funkce je opakem zrušení sloupce či řádku. Využijeme ji např. když omylem při vkládání dat přeskočíme sloupec či řádek a chceme jej do tabulky dodatečně doplnit. Sloupce se vkládají automaticky vlevo od aktivní buňky a řádky nad aktivní buňku. Znamená to tedy, že před vkládáním sloupců (řádků) je nutné vhodně umístit kurzor. Pak vyvoláme příkaz VLOŽIT a vybereme podle potřeby příkaz SLOUPEC či ŘÁDEK. Chceme-li vložit více sloupců či řádků, musíme opět před touto operací příslušný počet sloupců (řádků) označit. Příkazem VLOŽIT BUŇKY … vyvoláme dialogové okno Vložit Vložit, ve kterém můžeme jak vkládat celé řádky či sloupce, nebo můžeme posouvat aktivní buňky vpravo nebo dolů.

Kapitola 4: Formátování tabulky

4.

Formátování tabulky

4.1

Formátování na úrovni buňky

22

4.1.1. Formátování textu Formát textu • výběr příslušného fontu - klepnutím na šipku v tlačítku Písmo se zobrazí rozevírací seznam, z něhož můžeme vybrat příslušný typ písma (font), • výběr velikosti písma - klepnutím na šipku v tlačítku Velikost písma získáme rozevírací seznam, z něhož můžeme zvolit příslušnou velikost písma v bodech, vlastní, tj. nepředdefinovanou velikost písma lze do tohoto okénka i napsat (při vlastní definici používejte celá kladná čísla), • výběr způsobu zobrazení (řezu písma) - tučné písmo (Tučné), kurzíva (Kurzíva), podtržené písmo (Podtržení). 4.1.2. Formát čísel

Formátování číselných hodnot

Výchozím formátem všech buněk je tzv. obecný formát. V něm se číselné hodnoty zobrazují buď jako celá čísla (např. 345) nebo jako desetinná čísla (např. 23,59), kde se pro oddělení desetinné části používá desetinná čárka. (Pokud bychom omylem vložili desetinnou tečku, systém vyhodnotí vstup jako datum a pak by následně mohlo dojít k problémům při použití těchto hodnot v aritmetických operacích). Číselný formát buňky lze změnit s použitím následujících tlačítek na panelu nástrojů: • tlačítko Měna doplňuje k číslu 2 desetinná místa a symbol vyjádření měny (obvykle je předdefinován formát Kč zobrazovaný za číslem, který je přebírán z nastavení ve WINDOWS), • tlačítko Styl procent vynásobí výraz konstantou 100, doplní symbol % a odřízne desetinnou část (formátuje na celá čísla).

• •

Pozor! Při vstupu procentních dat do buňky můžeme vložit hodnotu včetně procentního symbolu (např. vložíme-li hodnotu 45%, hodnota vkládaná do buňky bude 0,45 avšak vstup je ihned automaticky naformátován, takže v buňce v tabulce vidíme číslo 45%). Druhá možnost je vložit do buňky číslo 0,45 a pak je naformátovat na procentní formát se dvěma desetinnými místy. Obsahem buňky je tedy vždy hodnota 0,45 a zobrazení je vždy záležitostí formátu. tlačítko styl čárky zobrazí číslo s desetinnou částí, oddělí tisíce mezerou a za číslem ponechá mezery na zobrazení symbolu měny, tlačítka Přidat/odebrat desetinné místo umožní přidávat nebo ubírat počet desetinných míst. Pozor! Všechny úpravy čísel pomocí formátu jsou jen změnou zobrazení dat na obrazovce, hodnota buňky zůstává nezměněna (a s touto hodnotou se také provádějí veškeré další výpočetní operace).. Pokud skutečně chceme zmenšit počet desetinných míst (aby obsah buňky i hodnota zobrazená na obrazovce byly totožné) je nutné použít funkci ZAOKROUHLIT.


23

Veškeré formáty nastavené pod jednotlivými tlačítky mají svým způsobem omezené možnosti. Pokud potřebujeme použít širší možnosti formátování, musíme použít příkaz BUŇKY z nabídky FORMÁT (nebo vyvoláme pravým tlačítkem myši místní nabídku) a v dialogovém okně klepneme na příslušnou záložku, zde např. Číslo. Vybereme z nabídky požadovaný druh číselného formátu, doplníme příp. další parametry (počet desetinných míst, oddělení tisíců mezerou apod.) a v rámečku Ukázka si můžeme ihned prohlédnout, jak bude číslo s vybraným formátem vypadat. 4.1.3. 4.1.3.1. Zarovnání

Svislé centrování

Zalomení textu

Další možné úpravy – zarovnání v rámci buňky či oblasti, použití okrajů, barva vzorků Zarovnání

Jak již bylo řečeno dříve, při vstupu dat do buňky dochází k jejich uložení takto: • číselné hodnoty se automaticky zarovnávají vpravo, • alfanumerické hodnoty se automaticky zarovnávají vlevo. Pokud chceme tento způsob uložení změnit příp. využít i dalších způsobů – zarovnání na střed (centrování), sloučení a zarovnání obsahu na střed, musíme buňky příslušným způsobem naformátovat. Obsah buňky můžeme zarovnávat pomocí tlačítka Zarovnat doleva, Zarovnat doprava a pro zarovnání na střed můžeme použít tlačítko Zarovnat na střed. Vycentrování lze použít jak pro centrování obsahu jednotlivých buněk (jedné nebo více buněk v celé oblasti), tak i centrování obsahu nad více sloupci najednou - např. pro vycentrování všech nadpisů sloupců. Tlačítko Zarovnat na střed centruje obsah postupně v jednotlivých buňkách na rozdíl od tlačítka Sloučit a zarovnat na střed, které sloučí obsah v označených buňkách (je jich označeno více najednou - např. nad celou šíří tabulky) a vycentruje jej. Pozn. Použijeme-li formát Sloučit a zarovnat na střed v případě, že v označené oblasti jsou kromě prvního sloupce zaplněny i některé další buňky, dojde při sloučení buněk ke ztrátě jejich obsahu (zachován zůstává pouze obsah 1. buňky v označené oblasti). Proto není možné provést vystředění nadpisu u více řádků najednou. Všechny shora uvedené způsoby se týkají centrování ve směru vodorovném. Můžeme však zvolit i centrování ve směru svislém. Pro tento formát již neexistuje tlačítko a musíme zvolit příkaz BUŇKY... z nabídky FORMÁT a v kartě Zarovnání příslušný typ formátu vybrat (ukázka okna byla na obr. 2). Pokud bychom chtěli dlouhý text v buňce rozdělit v rámci buňky do více řádků (vytvořit tak víceřádkový vstup v jediné buňce), můžeme použít tzv. zalamování textu. V nabídce FORMÁT zadáme příkaz BUŇKY.., vybereme kartu Zarovnání a zaškrtneme políčko Zalomit text. V tomto případě dojde automaticky ke změně výšky řádku. Počet řádků, do kterých se text zalomí nebo příp. nesprávné zalomení lze pak řídit vhodným rozšiřováním či zužováním sloupce. Zalamování textu používáme u nadpisů tabulek, kdy se


24

snažíme omezit zbytečně rozšiřování sloupců podle délky dlouhých textů v záhlaví tabulky. Pozn. Text lze zalomit v buňce i přímo při vkládání, a to pomocí kláves ALT ENTER. Povšimněte si, že při zalomení textu v buňce se automaticky zvyšuje výška řádku (výjimku tvoří případ, kdy před zalomením textu byla již šíře sloupce upravována – pak musíme výšku řádky nastavit následně sami). Na kartě Zarovnání je možná i orientace textu v buňce pomocí pootočení o n stupňů. 4.1.3.2. Ohraničení

Ohraničení, barva pozadí a barva písma

Můžeme opět použít tlačítek z panelu nástrojů. Pomocí tlačítka Ohraničení lze realizovat nejrůznější typy rámečků. Volbou tlačítka Barva písma získáme paletu barev, které můžeme použít pro obsah vložený do buněk (změny barvy textu, číselných hodnot apod.). Tlačítko Barva výplně funguje podobným způsobem, ale mění barvu pozadí buňky. Pozn. Barvy je třeba vybírat velmi uvážlivě, jednak z důvodů estetičnosti (i zde platí pravidlo „méně je obvykle více“) a zejména s ohledem na pozdější tisk na tiskárně. Pokud nevyberete velmi světlou barvu pozadí, vznikne tím na černobílé tiskárně méně čitelný text. Definované kombinace formátů je možné uložit pro další použití, tzn. využít tzv. styly. V nově vytvářeném sešitu jsou na počátku k dispozici pouze vestavěné styly, ale v průběhu práce si můžeme vytvořit další styly vlastní. Práce se styly je obdobná jako v textovém editoru WORD, proto se jí zde detailněji nezabývám.

4.2 Formát sloupce a řádku

Formát sloupce

Formátování na úrovni sloupce či řádku

Zde jde nejčastěji o změnu šíře sloupce či změnu výšky řádku. Každý sloupec má určitou standardní šíři, která odpovídá průměrnému počtu číslic 0-9 standardního písma. Kolik znaků lze do buňky skutečně vložit, záleží na vkládaném obsahu (vzhledem k proporcionalitě písma může být počet znaků ve stejně širokých buňkách různý). Pokud buňka vpravo od aktivní buňky obsahuje nějaký vstup, dojde k překrytí části obsahu aktivní buňky. Je tedy nutné rozsah buňky (šíři sloupce) upravit. Rozšíření či zúžení sloupce lze realizovat např. takto: myší se nastavíme do pravého okraje záhlaví (hlavičky) sloupce (symbol myši se změní na nitkový kříž) a nyní jsou 2 možnosti: • poklepáním myší se sloupec automaticky rozšíří (přizpůsobí) podle nejdelšího vstupu v daném sloupci, • tažením myší rozšiřujeme (nebo zužujeme) sloupec podle našeho vlastního uvážení. Pokud bychom chtěli rozšířit více sloupců najednou (samozřejmě na stejnou šíři), lze sloupce označit a zvolit příkaz FORMÁT SLOUPEC ŠÍŘKA a zadat požadovanou šířku pomocí celého či desetinného čísla v intervalu 1 až 255 (standardně je šířka nastavena na číslo 8,43). Jiná možnost je pomocí myši – označíme všechny sloupce v jejich záhlaví a pak postupujeme stejným


Formát řádku

25

způsobem jako kdybychom rozšiřovali první ze sloupců v označené oblasti, přičemž se všechny označené sloupce rozšíří na stejnou šíři jako první sloupec této oblasti. Podobným způsobem lze měnit i výši řádků, a to buď myší nebo pomocí příkazu FORMÁT ŘÁDEK VÝŠKA. Pozor! Šíře sloupce či výška řádku se mění v celém sloupci (ve všech jeho řádcích) nebo v celém řádku (ve všech jeho sloupcích). Pozn. Vždy bychom měli dodržovat zásadu, že šíři sloupce přizpůsobujeme datům, nikoliv nadpisům. Pokud je nadpis delší, snažíme se jej zalomit do více řádků.

Kapitola 5: Kopírování a přesuny

5.

Kopírování a přesuny

5.1

Kopírování

26

Tuto funkci nejvíce využijeme při práci s výpočetními výrazy. Výpočetní výrazy jsou totiž obvykle stejné pro celý řádek či sloupec a nebylo by efektivní vkládat principiálně stejný výpočetní výraz opakovaně do řady buněk. Vložíme tedy výpočetní výraz do první buňky ve sloupci či řádku a do zbylých buněk jej pak zkopírujeme. Kopírovat lze v podstatě dvěma způsoby: tažením myší nebo pomocí schránky. Nejjednodušší způsob (pokud kopírujeme obsah 1 buňky do řady Kopírování tažením myší dalších buněk v souvislé oblasti – což bývá nejčastější varianta) je pomocí tažení myší: • umístíme kurzor do zdrojové buňky (ta, ze které se kopíruje), do jejího pravého dolního rohu (do místa tzv. úchytu). Kurzor myši se změní na nitkový kříž, kterým pak pohybujeme levým tlačítkem ve vodorovném nebo svislém směru a tím realizujeme kopírování. Další variantou je kopírování pomocí schránky WINDOWS: Kopírování • umístíme kurzor do zdrojové buňky (ta, ze které se kopíruje), stiskneme pomocí kombinaci kláves CTRL C nebo použijeme tlačítko Kopírovat z panelu schránky nástrojů příp. příkaz ÚPRAVY KOPÍROVAT. Tím se obsah buňky zkopíruje do schránky. Zdrojová buňka je zvýrazněna pomocí pohyblivého rámečku. Myší vyznačíme oblast cíle (kam se kopíruje) a potvrdíme pomocí klávesy ENTER. Výraz se zkopíruje, pohyblivý rámeček zmizí a obsah schránky se zruší.

Kopírování

Pozn. Pokud bychom nestiskli ENTER nebo ESC, obsah schránky se neruší a můžeme jej pak vkládat na další místa (cíle) v sešitu. Opakované vkládání obsahu schránky je možné buď kombinací kláves CTRL V nebo tlačítkem Vložit nebo také příkazem ÚPRAVY VLOŽIT. Při posledním vložení kopírovaného obsahu pak opět použijeme klávesu ENTER. Pozn. Lze využívat i tzv. schránky sady Office 2000, která má nové vlastnosti oproti schránce Windows. Je společná pro všechny aplikace Office a je vhodná pro přenos dat mezi těmito aplikacemi. Při kopírování dat do schránky Windows se tato data kopírují i do schránky sady Office. Naopak při vyprázdnění schránky Windows obsah schránky Office zůstává nezměněn (umožňuje uložení až 24 položek). UPOZORNĚNÍ! Při kopírování buněk dochází ke kopírování jak obsahu, tak i formátu na úrovni buňky (formát na úrovni sloupce a řádku se nekopíruje). Je efektivnější tedy veškeré formáty buněk realizovat vždy před vlastním kopírováním.

5.2 Vyjmout – Vložit

Přesuny

Na rozdíl od kopírování není obsah duplikován, ale z původního místa je přesunut do cílové buňky či oblasti. Postup je však podobný jako


27

u kopírování. Obsah přesouvané buňky či oblasti se uloží do schránky pomocí kombinace kláves CTRL X nebo tlačítka Vyjmout nebo také příkazem ÚPRAVY VYJMOUT. Do doby, než stiskneme ENTER, lze přesouvat obsah schránky pomocí kombinace CTRL V nebo tlačítka Vložit. Stiskem ENTER se opět obsah schránky ruší. Při kopírování nebo přesunech mohu nastat obecně tyto případy: • kopírujeme (nebo přesouváme) obsah jedné buňky do jiné buňky, • kopírujeme (nebo přesouváme) obsah jedné buňky do celé oblasti (např. kopírování vzorců), • kopírujeme (nebo přesouváme) celou oblast do nové oblasti. Operace se redukuje na kopírování oblasti zdroje do jedné buňky cíle, kterou představuje levý horní roh požadovaného nového umístění (využívá se např. při práci s více listy). Pozn. Oblast může být souvislá (vyznačíme ji tažením myší) nebo nesouvislá (pak při označování myší je nutné podržet stisknutou klávesu CTRL. V případě nesouvislé oblasti lze kopírovat nebo přesouvat vždy jen nesouvislé oblasti se shodným počtem sloupců či řádků. Ve všech ostatních případech systém ohlásí chybové hlášení s textem Tento příkaz nelze použít u více výběrů.

Příklad

Varianty kopírování

Varianty kopírování nesouvislé oblasti Kopírování nesouvislé oblasti (zdroj) Kopírování nesouvislé oblasti (cíl)

Kopírování nesouvislé oblasti (zdroj)

Kopírovat nelze

Pozor! Při kopírování nesouvislé oblasti se při kopírování vzorců uloží v místě cílových buněk pouze vypočtené konstanty (nikoli původní vzorce jako je tomu u kopírování souvislé oblasti). Pozn. Pokud chceme stejného efektu dosáhnout při kopírování v souvislé oblasti (tj. v místě cíle chceme mít v buňkách místo vzorců konstanty), musíme při vkládání dat ze schránky použít místo příkazu VLOŽIT příkaz VLOŽIT JINAK a v rámci jeho dialogového okna použijeme přepínač Hodnoty.


5.3 Relativní odkaz Absolutní odkaz

Relativní odkazy Smíšené odkazy

Absolutní odkazy

28

Relativní a absolutní odkazy

Při kopírování vzorců do buněk ve sloupci či v řádku dochází k modifikaci adres u používaných operandů. Odkazům, které se kopírováním modifikují, říkáme odkazy relativní. Pokud potřebujeme, aby se odkaz ve vzorci při kopírování nemodifikoval, musíme použít tzv. smíšeného nebo absolutního odkazu. V absolutním odkazu jsou obě části fixovány, smíšený odkaz je kombinací relativních a absolutních odkazů v jeho jednotlivých částech, tedy v označení sloupce či řádku. K vyznačení absolutní části odkazu slouží znak $ (dolar). Abychom nemuseli pro vložení znaku $ přepínat mezi českou a standardní klávesnicí, je vhodné využít funkční klávesy F4. Tuto klávesu můžeme použít přímo při vkládání vzorce nebo v režimu Úpravy (vyvoláme jej poklepáním myší na příslušnou buňku nebo stisknutím klávesy F2 nebo klepnutím myší do řádku vzorců). Kurzor nastavíme k odkazu, který chceme fixovat a opakovaným stiskem funkční klávesy F4 můžeme přepínat mezi 4 možnými kombinacemi jednoho odkazu (např. bude-li kurzor u odkazu A1, budou se po stisku klávesy F4 postupně střídat odkazy $A$1, A$1, $A1, A1). Příklady zápisu relativních, smíšených a absolutních odkazů: Relativní odkazy (obě části odkazu se při kopírování příslušně modifikují) A1 při kopírování se mění např. na A2, A3, B2, B3 atd. LIST1!C24 při kopírování se mění např. na LIST1!C25, LIST1!D25 atd. Smíšené odkazy (část odkazu jíž předchází znak $, se při kopírování nemodifikuje, druhá část odkazu se mění) $A1 při kopírování se modifïkuje např. na $A10, $A11 atd. A$1 při kopírování se modifïkuje např. na B$1,C$1, D$1 atd. LIST1!C$24 při kopírování se modifïkuje. na LIST1!D$24, LIST1!E$24 atd. LIST1!$C24 při kopírování se modifïkuje. na LIST1!$C25, LIST1!$C26 atd. Absolutní odkazy (obě části odkazu jsou fixovány, při kopírování nedochází k jejich modifikaci) $A$1 při kopírování se odkazuje vždy na buňku A1 LIST1!$C$24 při kopírování se odkazuje vždy na buňku C24 v listu 1. Pozn. U odkazu na list nelze znak $ použít.

Příklad V tabulce máme např. počty obyvatel jednotlivých okresů kraje a ve sloupci D chceme spočítat, jakým procentem se podílejí jednotlivé okresy na celkovém počtu obyvatel kraje. Při vkládání vzorce do buňky D2 využijeme možnosti naformátování buněk na procentní vyjádření, proto do této buňky zadáme pouze výpočet podílu (tedy výraz D2/D9). Abychom získali procentní vyjádření, použijeme tlačítko Styl procent (výraz se vynásobí konstantou 100 a doplní symbolem % za vypočtenou hodnotou). Pokud bychom chtěli zvýšit či snížit počet desetinných míst, lze využít tlačítka Přidat/odebrat desetinná místa.

Kapitola 5: Kopírování a přesuny Okres

Kraj

České Budějovice Tábor Jindřichův Hradec Písek Strakonice Český Krumlov Prachatice Celkem

Jihočeský Jihočeský Jihočeský Jihočeský Jihočeský Jihočeský Jihočeský

29 Obyvatelstvo % podíl z kraje 180411 103469 93401 71012 70251 59976 51726 630246 100%

Pokud bychom takto zapsaný výraz zkopírovali do celého sloupce, počínaje buňkou D3, objeví se ve všech buňkách chybová zpráva #DIV/0!. Při kopírování výrazu zde dochází ke změnám relativních odkazů jak v čitateli, tak i ve jmenovateli. Adresa jmenovatele se postupně modifikuje na D10, D11 atd., a tyto buňky jsou prázdné (jsou interpretovány jako nula). Dochází proto k pokusu o dělení nulou. V našem příkladu postačí použití smíšeného odkazu, kdy pro případ kopírování potřebujeme fixovat pouze označení řádku, označení sloupce může zůstat v relativním tvaru. Výraz ve sloupci D bude tedy zapsán takto: =D2/D$9 a při jeho kopírování se bude modifikovat pouze část čitatele. Lze použít i absolutní adresy a pak by vzorec vypadal takto: =D2/$D$9 (kdybychom však vzorec potřebovali kopírovat ještě do jiného sloupce, tak by toto řešení nebylo správné (i v tom dalším sloupci bychom dělili stále celkovým počtem obyvatel). Vzorec z buňky D2 zkopírujeme do zbylých buněk ve sloupci. Při kopírování se kopíruje jak obsah, tak i formát na úrovni buňky. Protože byly obě zdrojové buňky naformátované, bude po zkopírování naformátována i celá cílová oblast. Pozn. Kopírovat lze i samotné formáty - bez obsahu (pokud např. potřebujeme stejným způsobem naformátovat více oblastí, lze to vyřešit tlačítkem Kopírovat formát. Doplněná tabulka vypadá takto: Okres Kraj Obyvatelstvo % podíl z kraje České Budějovice Jihočeský 180411 28,63% Tábor Jihočeský 103469 16,42% Jindřichův Hradec Jihočeský 93401 14,82% Písek Jihočeský 71012 11,27% Strakonice Jihočeský 70251 11,15% Český Krumlov Jihočeský 59976 9,52% Prachatice Jihočeský 51726 8,21% 630246 100,00%

Kapitola 6: Práce s listy

6. Listy

Manipulace s listy

30

Práce s listy

V Excelu můžeme pracovat jak s listy v rámci jednoho sešitu, tak i s více sešity najednou. Práci s více sešity využíváme tehdy, mají-li být tabulky sdíleny více uživateli (např. tabulky ceníků, kurzovních lístků, různých přepočítacích koeficientů apod.) a nebylo by tudíž efektivní, aby aplikace každého uživatele obsahovaly tato stejná data. Obvyklejší je využití listů v jednom sešitu. Listy jsou označeny pomocí identifikace LISTn, kde n je pořadové číslo listu. Tento standardní název listu je uveden na tzv. oušku (záložce), jak již bylo dříve uvedeno. Přechod mezi listy se realizuje klepnutím myší na ouško listu. S listy lze různými způsoby manipulovat: • vkládat další nové listy (příkaz VLOŽIT LIST – list se vloží před aktivní list, tj. ten, ve kterém stojí kurzor), • odstraňovat listy (rušit je) – ÚPRAVY ODSTRANIT LIST, • pojmenovávat (přejmenovávat) listy (nejrychlejší je poklepáním myší na ouško a vložením nového jména), • přesouvat nebo kopírovat listy. Přesun listu v rámci sešitu realizujeme takto: označíme ouško přesouvaného listu a táhneme jej podél ostatních oušek. Černý trojúhelníček ukazuje místo, kam se list vloží při uvolnění tlačítka myši. Kopírování listů je obdobné, jen při tažení myší musíme držet stisknutou klávesu CTRL. Kopie listu se pak označí jménem LISTn (číslo), kde číslo je u první kopie rovno 2. (Tedy např. první kopie listu 3 se bude jmenovat LIST3 (2), druhá LIST3 (3). Pokud potřebujeme vytvořit kopie dat z jednoho listu do více listů najednou, je výhodnější používat kopírování přes schránku (i za cenu ztráty formátu na úrovni sloupce či řádku). Přesun nebo kopie listu lze realizovat i mezi sešity. • ukrývat některé listy (FORMÁT LIST SKRÝT), příp. je opět zobrazovat (FORMÁT LIST ZOBRAZIT), • seskupovat listy pro provádění společných operací (např. stejné opravy, formátování apod.). Listy můžeme označit a zahrnout do tzv. skupiny. Pokud máme listy označené (v záhlaví aplikace je za jménem souboru text [skupina]), lze realizovat řadu operací v jediném listu, a ty se pak automaticky promítají do ostatních označených listů. Jde např. o rušení obsahu (výmaz) buněk nebo vkládání nového obsahu do buněk apod.. Označení více listů (skupiny) • sousedících - klepneme na ouško prvního listu, držíme stisknutou klávesu SHIFT a klepneme na ouško posledního listu skupiny, • nesousedících - po klepnutí na ouško prvního listu skupiny držíme stisknutou klávesu CTRL a klepeme postupně na ouška dalších nesousedících listů. Zrušení označení skupiny listů se provede klepnutím na ouško neoznačeného listu (pokud jsou však označeny všechny listy, lze klepnout na ouško kteréhokoliv listu). Pozn. Doporučuji před začátkem práce si vytvořit kopii listu se zdrojovými daty a veškeré úpravy realizovat v této kopii. Pokud by se omylem některá data

Kapitola 6: Práce s listy

31

podařilo smazat, tak můžeme původní tabulku velmi lehce zrekonstruovat (nakopírovat si znovu data z originálního listu).

Kapitola 7: Grafická interpretace

Definování grafu

Průvodce grafem

7.

Grafická interpretace

7.1

Tvorba grafu

32

Při definování grafů jde obvykle o následující posloupnost činností: • výběr jedné nebo několika datových řad (v terminologii Excelu tím rozumíme seskupení datových bodů – hodnot v jednotlivých buňkách ve sloupcích či řádcích; tyto datové řady jsou pak v grafu odlišeny buď barvou nebo vzorkem), • určení typu grafu, • doplnění náležitostí grafu (nadpis, legenda, vysvětlivky, popisy os, případně některé další úpravy - změna barev, typu písma, přemístění či změna velikosti některých prvků grafu apod.). Všechny tyto činnosti lze v Excelu maximálně zjednodušit pomocí tzv. Průvodce grafem. Jeho vyvoláním (nejlépe pomocí tlačítka na panelu nástrojů nebo příkazem VLOŽIT GRAF ) získáme řadu 4 dialogových oken, která nás krok za krokem vedou celým postupem tvorby grafu. V těchto dialogových oknech se zobrazuje i ukázka grafu, takže lze postup i určitým způsobem vhodně korigovat. Graf můžeme umístit buď na stejném listu s tabulkou nebo chceme-li zobrazit graf odděleně od příslušných hodnot v tabulce, lze vytvořit samostatný grafický list (tato varianta bývá obvykle vhodnější a má výrazně lepší grafickou úroveň). Každý vytvořený graf je samozřejmě propojen s odpovídajícími daty v tabulce a při jakékoliv jejich změně dochází k automatickému překreslení grafu. Toto propojení je oboustranné, při úpravě (překreslení datových řad v grafu) dojde k automatické změně dat v původní tabulce. Pozn. Graf nelze vytvořit jiným způsobem než s použitím Průvodce grafem. Pro celý další výklad budeme používat následující data z tabulky tržeb. Měsíc leden tržby v tis. Kč Prodejna Husova ul. Hradecká ul. Libáňská ul. Žižkovo nám. Nám. míru Celkem

1. dekáda 2. dekáda 3. dekáda 235 178 389 227 344 1373

221 212 410 210 345 1398

356 234 425 210 378 1603

Celkem měsíc 812 624 1224 647 1067 4374


7.2

33

Řešení příkladu – výsečový graf

Příklad 1

Výsečový graf

Máme vytvořit výsečový graf přehledu celkových měsíčních tržeb podle prodejen. V tabulce označíme data prvního a pátého sloupce – tedy oblast A4:A8 a E4:E8 (tedy bez nadpisů). Při označování použijeme klávesu CTRL, protože jde o nesouvislou oblast dat (nejprve označíme data ve sloupci A, pak stiskneme klávesu CTRL a označíme data ve sloupci E). Pak vyvoláme Průvodce grafem a v prvním dialogovém okně (viz obr. 12) vybereme typ grafu Výsečový – první podtyp. OBR. 12: PRVNÍ DIALOGOVÉ OKNO PRŮVODCE GRAFEM – TYP GRAFU

Tlačítkem Další přejdeme do dalšího dialogového okna – Zdrojová data grafu (viz obr. 13)


34

OBR. 13: DRUHÉ DIALOGOVÉ OKNO PRŮVODCE GRAFEM – ZDROJOVÁ DATA GRAFU, KARTA OBLAST DAT

V kartě Oblast dat zkontrolujeme, zda jsou správně zadána zdrojová data (tedy oblast A4:A8 a E4:E8). Řadu v našem případě tvoří sloupec, pokud bychom zaškrtli druhou variantu, nebude graf interpretován správně. V dialogovém okně je již vidět ukázka grafu. V kartě Řada (viz obrázek 14) není třeba nic vyplňovat. Povšimněte si, že data označená na vstupu byla rozdělena do dvou vstupních polí – do vstupního pole pro datové hodnoty – jednotlivé výseče (data ze sloupce E) a do vstupního pole pro popisky osy X (data ze sloupce A).


35

OBR. 14: DRUHÉ DIALOGOVÉ OKNO PRŮVODCE GRAFEM – ZDROJOVÁ DATA GRAFU, KARTA ŘADA

Tlačítkem Další vyvoláme třetí dialogové okno Možnosti grafu - viz obr. 15, které obsahuje pro výsečový graf karty Názvy, Legenda, Popisky dat. Zde definujeme veškeré náležitosti grafu. Na kartě Název vyplníme ve vstupním poli název grafu, v kartě Legenda určíme, zda bude v grafu použita legenda a případně kde bude umístěna. V našem případě však legendu nepoužijeme, protože vše vyřešíme lépe pomocí popisků. Na kartě Popisky dat zvolíme formu popisků jednotlivých výsečí. Zaškrtnutí políčka Název kategorie vloží názvy prodejen k jednotlivým výsečím, políčka Hodnoty a Procenta připojí vyjádření v absolutních číslech a vypočte podíl v %. Je možné zaškrtnout všechna tato 3 políčka (pokud to není na závadu přehlednosti grafu). Políčko Název řady by doplnilo ke každé výseči slovo Řada1, což není vhodné.


36

OBR. 15: TŘETÍ DIALOGOVÉ OKNO PRŮVODCE GRAFEM – MOŽNOSTI GRAFU

Tlačítkem Další přejdeme do posledního okna Průvodce grafem – Umístění grafu (viz obr. 16). V tomto okně určíme, zda bude graf umístěn na samostatný list – tzv. grafický list (systém jej automaticky nazve GRAF1 (GRAF2 až GRAFn doporučuji v rámci tohoto dialogového okna ihned grafický list přejmenovat – zlepší to orientaci v sešitu) nebo jako objekt do listu, který zadáme. Jak již bylo řečeno, graf umístěný do grafického listu vypadá obvykle lépe, zejména pokud zobrazuje větší počet datových řad i hodnot. OBR. 16 POSLEDNÍ DIALOGOVÉ OKNO PRŮVODCE GRAFEM – UMÍSTĚNÍ GRAFU

V našem příkladu umístíme graf do samostatného grafického listu (jeho název můžeme ihned v Průvodci grafem přejmenovat) a tlačítkem Dokončit je práce s průvodcem grafem ukončena. Systém přejde automaticky do grafického listu a současně otevře panel nástrojů pro graf (viz obrázek 17). Nyní můžeme graf ještě dále upravovat.


37

Pozn. V kterémkoliv kroku Průvodce grafem se lze vrátit pomocí tlačítka Zpět na předchozí dialogová okna nebo tlačítkem Storno celou akci stornovat a začít znovu. OBR. 17: UKÁZKA HOTOVÉHO GRAFU S OTEVŘENÝM PANELEM NÁSTROJŮ PRO GRAF

7.3 Úpravy grafu

Úpravy hotového grafu

Pokud chceme hotový graf nějakým způsobem upravovat, je nutné nejprve aktivovat upravovaný objekt (klepnutím do prostoru tohoto objektu myší nebo výběrem objektu z rozevíracího seznamu na prvním tlačítku panelu nástrojů Graf). U aktivního grafu umístěného jako objekt v klasickém datovém listu můžeme měnit velikost nebo umístění grafu, příp. klávesou DEL graf vymazat. Velikost grafu změníme takto: umístíme ukazatel myši do některého z rohů grafu (ukazatel myši se změní na nitkovou šipku) a tažením příslušným směrem můžeme měnit rozměry grafu. Umístění grafu (bez změny velikosti) změníme umístěním ukazatele myši do prostoru grafu a tažením příslušným směrem. Pokud je graf umístěn v grafickém listu, nelze graf přesouvat a velikost zobrazení lze změnit pouze odpovídajícím nastavením měřítka zobrazení (buď tlačítkem Měřítko zobrazení z panelu nástrojů Standard nebo výběrem příkazu LUPA z nabídky ZOBRAZIT). Jaké změny lze provést v hotovém grafu?


Formát názvu grafu

Vzhled výplně

38

a) Editace (oprava), pokud je objekt editovatelný. Editovat lze např. název grafu nebo názvy os. Editaci vyvoláme dvojím klepnutím myší do objektu (s prodlevou). b) Přemístění nebo změna velikosti objektu (přesouvat lze např. legendu, název grafu), velikost lze změnit u zobrazované oblasti. Nejprve musíme daný objekt v grafu klepnutím myší označit a pak pomocí úchytů nebo tažením myší za hranu provést příslušnou změnu. c) Formátování Dialogové okno formátu vyvoláme buď poklepáním na daný objekt (okno se ihned otevře) nebo klepnutím myší do objektu (označení objektu) pravým tlačítkem myši a výběrem příkazu Formát z místní nabídky. V našem příkladu můžeme změnit velikost písma v nadpisu (příp. barvu textu nebo pozadí) nebo upravovat popisky výsečí. Poklepeme myší na název grafu a v otevřeném dialogovém okně Formát názvu grafu vybereme kartu Písmo, vyznačíme velikost 16, zvolíme barvu písma např. červenou, na kartě Vzorky zvolíme Vlastní ohraničení, zaškrtneme políčko Stín atd. Chceme-li vyjádřit procentní podíly u jednotlivých výsečí na 2 desetinná místa, poklepeme do oblasti popisků a v dialogovém okně v kartě Číslo použijeme formát na dvě desetinná místa. V dialogovém okně Formát na kartě Vzorky lze klepnutím do tlačítka Vzhled výplně vyvolat dialogové okno Vzhled výplně umožňující další úpravy pozadí objektu pomocí výběru z karet: • Přechod (přechod barev a různé druhy stínování), • Textura (vzor pozadí – např. novinový papír, bílý mramor, světlé dřevo apod.), • Vzorek (vzorky pro barvu popředí i pozadí), • Obrázek (výběr obrázku pro výplň – např. pro tvorbu tzv. piktogramů). Chceme-li změnit barvu pozadí oblasti grafu, poklepeme do oblasti grafu a vyvoláme dialogové okno Formát oblasti grafu. Tlačítkem Vzhled výplně se dostáváme do dialogového okna, kde na kartě Textury vybereme třetí v pořadí. Pokud chceme zvýraznit jednu z výsečí (datový bod) pomocí jejího oddělení („vyříznutí z grafu“), klepneme v grafu dvakrát s prodlevou do výseče, kterou chceme oddělit. Tato výseč se označí úchyty a tažením myší ji můžeme odtáhnout. Hotový graf pak vypadá takto:


39

OBR. 18: UKÁZKA HOTOVÉHO VÝSEČOVÉHO GRAFU Celkové měsíční tržby prodejen v lednu

Husova ul.; 812; 18,56% Nám. míru; 1067; 24,39%

Hradecká ul.; 624; 14,27%

Žižkovo nám.; 647; 14,79%

Libáňská ul.; 1224; 27,98%

Příklad 2 Máme vytvořit sloupcový graf, který bude zobrazovat prodeje jednotlivých prodejen podle dekád. Pro každou prodejnu se tedy budou zobrazovat 3 sloupce vedle sebe. Označíme v tabulce data v prvních 4 sloupcích. V 1. dialogovém okně Průvodce grafem vybereme typ grafu sloupcový (3D – 4. podtyp), ve druhém potvrdíme zdroj dat, ve třetím (viz obr. 19) zadáme název grafu, názvy osy X (prodejny) a osy Z (tis. Kč). Popisky nevyužijeme, legendu (jde o 3 datové řady) musíme ponechat a příp. můžeme připojit tzv. tabulku dat (ke grafu se připojí tabulka s daty všech sloupců použitých jako zdroj dat.

Zdrojová data

Pozn. Okno Možnosti grafu má daleko širší nabídku karet než výsečový graf. Veškeré úpravy formátu prvků grafu jsou obdobné jako u výsečového grafu. Pokud jsme při tvorbě grafu zapomněli na označení dat, které budou tvořit legendu (v kartě Řada v dialogovém okně Zdrojová data zůstala pole Název u všech datových řad prázdná), systém doplní do legendy automaticky texty Řada 1, Řada 2, ... Řada n, (n je počet datových řad grafu). Tyto texty musíme v grafu následně opravit. Pozor! Legenda je jediným objektem v grafu, který nelze editovat přímo, tzn. že její obsah upravujeme prostřednictvím vlastností datové řady, ke které náleží. V našem příkladu klepneme v grafu pravým tlačítkem myši a vyvoláme příkaz ZDROJOVÁ DATA. V kartě Řada doplníme do vstupního pole pro Název odkaz do tabulky, kde leží text legendy – v našem případě pro první datovou řadu: List1!$B$3


40

nebo zapíšeme text legendy formou konstantního textu (nemusí být shodný s texty v tabulce, ale nevýhodou pak je, že jakékoliv další úpravy textů původních nadpisů v tabulce nemají na legendu vliv). V kartě Řada postupně označíme další datové řady a obdobným způsobem pro ně legendu doplníme. OBR. 19: OKNO MOŽNOSTI GRAFU U GRAFU SLOUPCOVÉHO

OBR. 20: HOTOVÝ GRAF


7.4 Přidání a odebrání datové řady

Změna dat

Přidání objektů

41

Další možné úpravy grafu

V grafu můžeme přidávat další datové řady nebo některé datové řady rušit. Datové řady zrušíme (po jejich označení) jednoduše klávesou Delete. Chceme-li naopak přidat další datovou řadu, zvolíme příkaz GRAF PŘIDAT DATA, kde označíme jak vlastní data ve sloupci tak i nadpis sloupce (pro text legendy). Popisky pro osu x se automaticky doplní podle ostatních datových řad (protože jsou pro všechny stejné). Pozor! Nová datová řada se vloží do grafu vždy jako poslední. Potřebujeme-li pak změnit pořadí zobrazovaných datových řad, musíme vyvolat pro danou řadu dialogové okno Formát datové řady (poklepáním na datovou řadu) a v kartě Pořadí řad vhodným způsobem pořadí změnit. Mezi další úpravy patří i úpravy grafu s jejich zpětným promítnutím do tabulky. Víme, že změníme-li data v tabulce, dojde automaticky k překreslení grafu. Naopak ale můžeme překreslit i graf a změny se promítnou zpětně do tabulky. Označíme graf a klepneme na příslušnou datovou řadu dvakrát s prodlevou. Umístíme-li nyní myš do místa horní hrany sloupce a zatáhneme, sloupec se automaticky zvýší (příp. sníží) a současně se opraví i hodnoty příslušného datového bodu (buňky) v tabulce (nelze realizovat u 3D grafů). Graf můžeme doplňovat i o další objekty, které můžeme vytvářet např. s pomocí nástrojů pro kreslení. Zvolíme z nabídky ZOBRAZIT příkaz PANELY NÁSTROJŮ a zaškrtneme Kreslení. Na obrazovce se otevře panel nástrojů pro kreslení a pomocí něj můžeme graf dále vylepšovat. Protože práce s tímto panelem je obdobná práci ve WORDu, nebudeme se jí zde podrobněji zabývat. Pozn. V Excelu nelze označit oblast pro grafickou interpretaci formou 3D odkazů (datové řady nemohou být tvořeny buňkami z různých listů), proto se musí nejprve vytvořit pomocná tabulka sumáře v jednom listu, ze kterého se pak může interpretovat graf.

Kapitola 8: Řízení dat v tabulce

8. Řazení dat

Seřadit podle Vzestupně Sestupně

42

Řazení dat v tabulce

Řazení umožňuje přeorganizovat pořadí řádků tabulky podle předem zvoleného kritéria – tzv. třídícího klíče. Klíče mohou být v Excelu ve 3 úrovních. Použití dalšího klíče má ovšem smysl pouze v případě, že hodnoty na úrovni vyššího klíče jsou duplicitní. Např. v telefonním seznamu pro stejné příjmení by bylo možné seřadit tabulku podle dalšího (sekundárního) klíče – např. křestního jména a v případě stejného příjmení i křestního jméno pak využít dalšího (třetího) klíče, např. adresy bydliště. Při řazení se postupuje takto: Označíme vlastní oblast dat (řádků), které chceme seřadit (bez nadpisů a bez závěrečných výpočtových řádků). Pozor! Je nutné označit oblast dat v celé šíři řádků, protože by jinak byla narušena konzistence dat – seřadila by se data pouze v označených sloupcích a ve zbylých by zůstala v původním pořadí. V našem příkladu tedy označíme oblast A6:G11. Z nabídky DATA zvolíme příkaz SEŘADIT. Klepnutím na tlačítko rozevíracího seznamu ve volbě Seřadit podle vyvoláme nabídku třídících klíčů (v našem případě jsou zde uvedeny hodnoty odpovídající názvům všech sloupců v tabulce, ze kterých vybereme tu položku, podle které chceme data seřadit), zaškrtneme volbu Vzestupně (A-Z) nebo Sestupně (Z-A) a tlačítkem OK řazení provedeme. Pozn. Pokud se nám kdykoliv podaří tabulku seřadit chybně, můžeme ihned pomocí tlačítka Zpět řazení anulovat. Pokud data v tabulce seřadíme a chtěli bychom se kdykoliv vracet k původní neseřazené tabulce, je vhodné přidat k tabulce jeden sloupec, ve kterém uvedeme pořadová čísla jednotlivých řazených řádků. Seřazením podle tohoto pořadového čísla získáme vždy pohled na původní neseřazená data.

Vyplňování řad

Pozn. Naplnění sloupce s pořadovými čísly můžeme nejefektivněji realizovat pomocí tzv. Vyplnění řady. V Excelu můžeme vyplňovat jak řady číselné (např. 1,2,3...., 1,5,9... atd.), abecedně číselné (řada 1,řada 2,řada 3...), tak i datumové (leden,únor,... pondělí,úterý...,) nebo si můžeme připravit definici jakýchkoli vlastních řad (např. seznam akademických titulů, názvů poboček apod.). Vyplňování řad je možné provádět opět různými způsoby. Nejjednodušší je asi tažení myší při stisku levého tlačítka – varianty pro jednotlivé typ řad uvádím dále. Vlastní postup Do první buňky, od které vytváříme řadu, zapíšeme první člen řady (v našem případě např. leden). Umístíme ukazatel myši do pravého dolního rohu buňky (stejným způsobem jako při kopírování), držíme stisknuté levé tlačítko myši a táhneme přes buňky, do kterých chceme řadu vyplnit (buď směrem vpravo nebo dolů). Do všech označených buněk se doplní řada určená svým prvním členem (řada měsíců). Stejným způsobem bychom postupovali při vyplnění dnů v týdnu, nebo u abecedněčíselné řady (např. 1. měsíc, 2. měsíc apod. – pozor, mezi číslem a textem musí být mezera, jinak se řada nevyplní).

Kapitola 8: Řízení dat v tabulce

43

U číselné řady by tažení levým tlačítkem realizovalo kopírování. Musíme proto vložit první dva členy řady (do dvou buněk pod sebou). Označíme obě buňky a pak opět tažením levým tlačítkem myši za pravý dolní roh spodní buňky vyplníme číselnou řadu. Tím, že vložíme první dva členy řady, určíme i požadovaný interval mezi členy řady – nemusí být vždy 1, ale např. 5, 10 apod. (platí to i u měsíců a dnů v týdnu, kde ovšem interval bývá menší). Pokud bychom měli v tabulce vyplněnou řadu měsíců nebo dnů v týdnu a chtěli bychom tabulku seřadit podle hodnot této řady, dojde k seřazení podle jejich abecedního pořadí, což není vhodné). Musíme tedy postupovat takto: v dialogovém okně Seřadit použijeme tlačítko Možnosti a v rozevíracím seznamu Hlavní klíč řazení nezvolíme klíč Normální, ale příslušný seznam, který potřebujeme - např. Leden, únor .... Řazení podle seznamu lze realizovat pouze na úrovni primárního (prvního) klíče.

Kapitola 9: Nastavení kontrol pro vstup dat do buněk

9. Ověření dat

Ověřovací kritérium

Nastavení kontrol pro vstup dat do buněk

Aplikace Microsoft Excel umožňuje nastavit kontrolu vstupních dat, které vkládáme do buněk. Příslušný dialog pro nastavení kontroly vstupu dat vyvoláme pomocí příkazu DATA – OVĚŘENÍ…. V rámci této volby můžeme specifikovat Ověřovací kritéria, definovat Zprávu při zadávání (obsahuje výzvu k zadání dat s uvedením omezujících pravidel) či Chybová hlášení spojená s chybně vloženou hodnotou.

9.1 Přípustné hodnoty

44

Omezení zadání nesprávných dat do buňky

Chceme-li provádět kontrolu zadávaných vstupních dat, můžeme před začátkem naší práce přesně vymezit, jaké hodnoty jsou pro konkrétní buňky či oblasti přípustné. Specifikovat můžeme: • typ dat (celé číslo, desetinné číslo, datum, čas, text, ..) a nastavit omezení pro tento typ (např. interval rozsahu čísla, minimální či maximální hodnotu), • seznam (vč. zdroje) platný pro zadávaná dat nebo omezit délku textu vstupních dat, • vlastní omezení – platné na základě výpočtu vzorce v jiné buňce (lze využít např. při kontrole, zda zadáním další položky nepřesáhneme stanovený limit). Pozn. V případě, kdy jsme si vědomi toho, že buňka s omezením může být prázdná, nebo pokud jako meze použijeme odkaz na buňku nebo vzorec závislý na buňkách, které jsou zpočátku prázdné, je třeba se ujistit, zda je zaškrtnuto políčko Přeskočit prázdné buňky. Naopak, budeme-li omezení aplikovat i na prázdné buňky, je třeba zrušit zaškrtnutí políčka Přeskočit prázdné buňky. Omezení aplikujeme následujícím způsobem. Nejprve označíme buňky, které chceme omezit, poté v nabídce DATA – OVĚŘENÍ… vybereme kartu Nastavení. Typ dat omezíme kritériem POVOLIT, kde si z rozvinovacího seznamu vybereme požadovaný typ dat (např. celé číslo, datum, …), v rámci kritéria Data zvolíme preferovaný operátor (je rovno, je větší než, je menší než,…) a potom v kritériu Data, (Minimum, Maximum,..) zvolíme délku (dolní mez, horní mez,..) v závislosti na vybraném operátoru. Jako meze můžeme zadat hodnoty, odkazy na buňky nebo vzorce.


45

OBR. 21: PŘÍKLAD NASTAVENÍ KONTROLY PŘI ZADÁVÁNÍ VSTUPNÍCH DAT

Seznam

Chceme-li vstupní hodnoty do buněk zadávat pomocí výběru ze seznamu, je třeba abychom si tento seznam nadefinovali v dialogu DATA – OVĚŘENÍ… – Nastavení. Jako kritérium Povolit vybereme položku Seznam. Do textového pole Zdroj doplníme odkaz na seznam platných dat (do seznamu nezahrnujte prázdné buňky). Seznam můžeme zadat také přímo do textového pole, jednotlivé položky seznamu oddělíme středníkem (např. existuje-li pro vstupní data do buňky volba mezi Ano a Ne zápis v buňce bude Ano;Ne). Jestliže chceme po klepnutí na buňku s omezením vybírat ze seznamu zadání, je třeba se ujistit, zda je zaškrtnuto políčko Rozevírací seznam v buňce. Při kontrole vstupních dat na základě vzorce postupujeme takto: vybereme buňky, které chceme omezit. V dialogu DATA – OVĚŘENÍ… – Nastavení zvolíme v kritériu Povolit položku Vlastní. Do textového pole Vzorec zadáme vzorec, jehož výsledek je logická hodnota (Pravda nebo Nepravda). Zadání pro buňku jsou neplatná, pokud má výsledek hodnotu Nepravda.

9.2 Zpráva při zadávání

Zobrazení zprávy při zadávání dat

Pokud jsme si omezení nastavili pouze u vybraných buněk, umožňuje aplikace Microsoft Excel definovat tzv. Zprávu při zadávání. Ta se objeví v okamžiku, kdy se nastavíme na buňku s aplikovaným omezením. Pokud žádnou zprávu zobrazit nechceme, zrušíme zaškrtnutí políčka Zobrazit zprávu po výběru buňky na kartě Zpráva při zadávání.


46

OBR. 22: PŘÍKLAD NASTAVENÍ ZPRÁVY PŘI ZADÁVÁNÍ DAT

OBR. 23: ZOBRAZENÍ ZPRÁVY PŘI ZADÁVÁNÍ DAT

9.3 Chybové hlášení

Chybové hlášení při zadání nesprávného vstupu

Poslední službou, kterou aplikace Microsoft Excel nabízí pro Ověření dat je definice Chybového hlášení, které systém zobrazuje v případě, že jsou zadána vstupní data, která neprojdou nastavenou kontrolou. Dialog DATA – OVĚŘENÍ… – Chybové hlášení připouští výběr ze tří typů chybových hlášení: • informativní zpráva (Styl Informace), toto chybové hlášení umožní data opravit nebo ponechat v buňce, • varovné hlášení s dotazem (Styl Varování), upozorní uživatele na chybu a položí dotaz, zda si přejeme pokračovat nebo data opravit, • stop (Styl Stop), zobrazí zprávu s vysvětlením chyby a zabrání uživateli pokračovat dokud chybu neodstraní. Text uvedený v poli Nadpis tvoří


47

záhlaví v okně chybového hlášení a vlastní text se uvádí do políčka Chybové hlášení. OBR. 24: PŘÍKLAD NASTAVENÍ CHYBOVÉHO HLÁŠENÍ PŘI NESPRÁVNÉM VSTUPU DAT

Chybové hlášení se zobrazuje ihned po zadání nesprávné vstupní hodnoty do buňky – viz obr. 25. OBR. 25: UKÁZKA CHYBOVÉHO HLÁŠENÍ (STYL STOP)

Kapitola 10: Filtrování dat

48

10. Filtrování dat Seznam – databáze

Filtrování

Některé operace (např. tvorba souhrnů, filtrování dat, databázové standardní funkce apod.) lze v Excelu provádět pouze s daty, které jsou ve tvaru tzv. seznamu, neboli databáze. Seznam představuje tabulku, kde řádky tvoří vlastně věty (záznamy) a sloupce pak jsou entitami položek (poli). Seznam musí obsahovat pouze řádky s daty stejného typu (všechny řádky tedy musí mít shodnou strukturu). První řádek seznamu musí obsahovat názvy polí (záhlaví sloupců), které musí být jedinečné (nesmí se opakovat). Tím je vlastně definována struktura seznamu. Záhlaví seznamu musí být jednořádkové (připouští se pouze zalomení textu v jedné buňce) a nesmí být od vlastních dat odděleno prázdným řádkem. Vlastní data seznamu musí být od ostatních dat v listu (např. buňky s pomocnými texty, buňky s filtrovacími kritérii apod.) oddělena alespoň jedním prázdným řádkem nebo sloupcem. Tabulka uvedená níže (je uvedena pouze část) je příkladem dat typu seznam: Okres Kraj Počet obyvatel Benešov středočeský 87996 Beroun středočeský 73645 Česká Lípa severočeský 103709 České Budějovice jihočeský 175477 Český Krumlov jihočeský 57801 Děčín severočeský 133865 Chomutov severočeský 117799 Jablonec n.Nisou severočeský 91656 Jindřichův Hradec jihočeský 93583 Kladno středočeský 149189 Kolín středočeský 90621 Kutná Hora středočeský 78695 Liberec severočeský 159168 Litoměřice severočeský 114075 Louny severočeský 89244 Mělník středočeský 94523 Mladá Boleslav středočeský 112083 Most severočeský 120277 Pozn. Obvykle se doporučuje, aby v jednom listu byl vždy jen jeden seznam. Filtrování umožňuje vyhledávání dat splňujících určitá kritéria v seznamu a práci s nimi. Ve filtrovaném seznamu se zobrazují jen ty řádky (věty), které obsahují hodnoty vyhovující zadaným kritériím. Pro filtrování můžeme použít dvou nástrojů: a) automatický filtr, b) rozšířený filtr.


49

V dalším textu uvádím pouze nejzákladnější informace o filtru automatickém, který je jednodušší. Automatický filtr vyvoláme příkazem FILTR a volbou AUTOMATICKÝ Automatický FILTR z nabídky DATA. Systém umístí do buněk s názvy sloupců tzv. stahovací filtr šipky. Klepneme-li na šipku, zobrazí se seznam tříd hodnot (kritérií filtrace) daného sloupce. Zvolíme-li některou ze tříd hodnot, všechny řádky, které tuto hodnotu neobsahují, budou ukryty. Záhlaví zobrazovaných (filtrovaných) řádků je navíc barevně (modrou barvou) zvýrazněno. Zmodrá i stahovací šipka ve sloupci, kde je použito kritérium. Ukončit filtrování můžeme buď volbou ZOBRAZIT VŠE z příkazu FILTR v nabídce DATA (filtr se odstraní, ale nastavení automatického filtru zůstane – nezmizí stahovací šipky ve sloupcích) nebo příkazem AUTOMATICKÝ FILTR z nabídky DATA (zruší se i nastavení automatického filtru a zmizí stahovací šipky). Pokud potřebujeme vyfiltrovaná data použít v dalších operacích (grafická interpretace, některé další výpočty), musíme je zkopírovat do jiné oblasti v tomtéž listu nebo v některém jiném listu.

Seřadit

Prvních 10

Vlastní filtr

Pozn. Pokud v původních datech byly v buňkách vzorce, po nakopírování vyfiltrovaného seznamu se vypočtené hodnoty uloží jako konstanty. Vyfiltrovaná data lze i pomocí operací Seřadit vzestupně nebo Seřadit sestupně ihned v rámci filtru i uspořádat jak potřebujeme (toto nabízí Excel až od verze 2003). V rozevíracím seznamu existují následující volby tříd hodnot – (vše), (prvních 10 …), (vlastní …) a dále seznam neduplicitních výskytů hodnot položek v daném sloupci. Volbou třídy hodnot Prvních 10.. se zobrazí dialogové okno Automatický filtr - prvních 10 a zde můžeme zadat, zda chceme zobrazit prvních či posledních n hodnot v daném poli. Tato volba je dostupná pouze u polí s číselnými hodnotami. Volbou vše zobrazujeme všechny řádky (věty) – anulujeme tedy kritéria filtru. Pokud potřebujeme zadat vlastní kritéria, zvolíme třídu hodnot vlastní. V dialogovém okně Vlastní automatický filtr pak vložíme kritéria. Kritéria můžeme kombinovat i mezi jednotlivými poli seznamu. Kritéria nad různými poli (sloupci) seznamu pak představují logické ano (AND). Logické nebo (OR) nad různými poli (sloupci) nelze pomocí automatického filtru vyřešit vůbec, musíme použít filtr rozšířený. Příklad Máme soubor s daty o počtu obyvatel v jednotlivých okresech republiky. Soubor obsahuje data 78 okresů ve 14 krajích. Struktura věty (záhlaví sloupců): Okres Kraj Počet obyvatel a) Vyfiltrujte data Karlovarského kraje V tomto případě použijeme stahovací šipku u pole Kraj a vybereme pomocí ní požadovaný kraj – viz obr. 26.


50

OBR. 26: VÝBĚR KRAJE V AUTOMATICKÉM FILTRU VČETNĚ VÝSLEDKU

b) Vyfiltrujte okresy Středočeského a Jihočeského kraje Před zadáním podmínky nejprve použijeme kategorii Vše, pomocí které zrušíme předchozí podmínku a můžeme nastavit podmínku novou. Zde jde o dvě podmínky, pro které musíme použít Vlastní filtr – viz obr. 27. V levé části okna vybereme odpovídající relační operátor a v pravé části zadáme názvy krajů (nemusíme je vkládat z klávesnice, ale je možné je vybrat pomocí šipky z rozevíracího seznamu). Pozor! V češtině používáme spojku a, ale ve vlastním filtru musí být použita logická operace Nebo. OBR. 27: POUŽITÍ VLASTNÍHO FILTRU S LOGICKOU OPERACÍ NEBO


51

c) Vyberte okresy s počtem obyvatel v intervalu od 100 000 do 150 000 V tomto případě se jedná o dvě podmínky spojené logickou operací A – po zobrazení všech dat využijeme opět okno Vlastního filtru u pole Obyvatelstvo. OBR. 28: POUŽITÍ OKNA VLASTNÍHO FILTRU PRO VYFILTROVÁNÍ DAT V INTERVALU

d) Vyberte všechny okresy Středočeského kraje s počtem obyvatel pod 100000. Zde jde o dvě podmínky spojené logickou spojkou A, kdy každá podmínka je zadána v jiném poli. Kraj Středočeský vybereme pomocí seznamu na stahovací šipce, pro pole počet obyvatel použijeme Vlastní filtr. OBR. 29: ZADÁNÍ DVOU PODMÍNEK VE DVOU RŮZNÝCH POLÍCH


52

Pozn. Povšimněte si, že v okně vlastního filtru kromě seznamu řady relačních operátorů (nejen pro práci s čísly ale i s textem – obsahuje, neobsahuje, má na začátku apod.) lze využívat i tzv. zástupných znaků * a ?. Pro zobrazení všech hodnot (anulaci podmínek) je nyní vhodnější zadat příkaz DATA FILTR ZOBRAZIT VŠE. e) Vyberte 3 okresy s největším počtem obyvatel Zde využijeme kategorii Prvních 10 pro pole Obyvatelstvo – viz obr. 30. V seznamu Zobrazit vybereme prvních a počitadlo nastavíme na 3. OBR. 30: AUTOMATICKÝ FILTR- PRVNÍCH 10

Pozn. V případě, že v daném poli je alespoň jedna buňka nevyplněná, objeví se na konci seznamu ještě kategorie Prázdné a Neprázdné. Je to vhodné tehdy, potřebujeme-li v rozsáhlejší tabulce doplnit chybějící data, takže si řádky s prázdnými buňkami vyfiltrujeme a nemusíme celou tabulku pracně procházet.


53

OBR. 31: FILTROVÁNÍ PRÁZDNÝCH NEBO NEPRÁZDNÝCH ŘÁDKŮ

Pozn. Pokud bychom potřebovali použít složitější podmínky (např. spojení více než dvou podmínek pro jedno pole, nebo logickou operaci Nebo pro různá pole, musíme použít rozšířeného filtru. U něj je výhodné i to, že zápis podmínek se realizuje do buněk listu (je trvalý) a můžeme je opakovaně znovu využívat. Navíc nemusíme vyfiltrovaná data kopírovat ručně, ale ve filtru lze zadat i oblast v libovolném listu, od které se data z filtru automaticky nakopírují (lze zadat i pořadí sloupců, ze kterých se kopíruje).

Seznam použité literatury [1] BENÁČANOVÁ, H., JANDOVÁ, M.: Tabulkové procesory, VŠE, Praha 2003. [2] BROŽ, M.: MS Excel 2003. Computer Press 2004.

Tabulkový program Microsoft Office Excel 2003

Recommend Documents