8. Práce s tabulkovým procesorem. Tvorba tabulky, řazení a filtrování dat, funkce, grafy.

Tabulkové procesory jsou programy, které umožňují provádění automatických výpočtů v tabulkách na základě zadaných dat. Tabulky je možné použít k statistickým, matematickým, ekonomickým a jiným výpočtům. S údaji v tabulce je možné v řadě případů pracovat rovněž jako s databází a provádět třídění i výběr dat podle zadaných kritérií. Často používanou formou výstupu bývá vedle tabulky také graf.

Režimy práce
Základním pracovním režimem je režim Připraven, 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í. Pokud potřebujeme opravit chyby ve vstupních datech, můžeme využít režimu Úpravy.

Práce se sešitem, listem
Základním typem dokumentu při práci se systémem 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 WINDOWS. Sešit má své jméno a příponu .XLS(X), která je automaticky doplňována systémem. Sešit se otevírá do tzv. okna a je tvořen 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 16 listů pojmenovaných LIST1 až LIST16. Názvy listů se objevují na záložkách ve spodní části okna sešitu. Příslušný list se zobrazí do okna klepnutím na jeho záložku.
V rámci listu je k dispozici 16384 řádků a 256 sloupců. Sloupce jsou v záhlaví označovány pomocí písmen (A až Z a pokračují kombinací písmen AA až IV), řádky se označují pomocí čísel. 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řípadně pojem adresa, který je běžně známý z ostatních tabulkových kalkulátorů. Do buněk tabulky zapisujeme údaje 3 typů:

textové konstanty,
číselné konstanty,
vzorce.
Několik řádků, sloupců, případně kombinace obou se označuje jako oblast. Např. odkaz A1:A10 znamená oblast 10 řádků v prvním sloupci.

Zadávání dat, vzorců a funkcí
Vkládání a opravy dat
Buňku, do které chceme zadávat údaje, zvolíme za aktuální a začneme psát. Text, který zadáváme, vidíme jak v buňce, tak v editačním řádku. Vkládat data lze také tak, že po označení aktuální buňky, do které chceme zapisovat, klikneme myší na editační řádek. Jestliže chceme zrušit zadávání údajů, tj. ukončit psaní, aniž by se údaje vložily do buňky, stiskneme klávesu Esc.

Chceme-li řádně ukončit vkládání dat do buňky, stiskneme Enter.

Vkládání textu a čísla
Excel sám rozezná z obsahu buňky, zda jde o text nebo o číslo. Pouze v případě, že chceme zapsat číslo, které má být považováno za text (např. směrovací či telefonní číslo), musíme před ně napsat apostrof (např.‘455123). Zadaný údaj je vždy obsahem jediné buňky, i když jeho délka přesahuje šířku buňky. Pokud se do buňky nevejde text, buď přesahuje do buňky vedlejší, nebo je za ní skrytý. Podle toho, zda vedlejší buňka je či není prázdná. Příznakem toho, že se do buňky nevešlo číslo, je její vyplnění znakem #. Pokud nemáme buňku předem naformátovanou, tj. vkládáme data v tzv. všeobecném formátu, snaží se Excel číslo zobrazit. Zobrazí ho třeba zaokrouhlené nebo ve vědeckém formátu (zápis čísla 2000 ve vědeckém formátu je 2E+3).

Vkládání data
Pro datum není žádný zvláštní datový typ. Datum patří mezi čísla, je to vlastně jakési pořadové číslo. Datum musíme zadávat ve tvaru, který odpovídá nastavení formátu pro datum v systému Windows. Pokud datum zapíšeme špatně, Excel nepochopí, že jde o datum – číslo a považuje údaj za text.

Vkládání vzorců
Zápis vzorce do buňky začíná znakem ”=”. Vzorce sestavujeme z číselných či textových konstant, odkazů na buňky a úseky buněk a funkcí. Tyto prvky spojujeme obvyklými operátory: (+, -, *, /, ^, <>, <, >, <=, >=, =). Ke spojování řetězců slouží operátor &. Podobně jako v matematice používáme kulaté závorky. Příklady jednoduchých vzorců:

=A1+A2 součet obsahů buněk A1+A2

=suma(A1;A2) totéž pomocí funkce

=2*A1-A2 obsah buňky A1 se vynásobí 2 a odečte se obsah buňky A2

=2*(A1-A2) od A1 se odečte A2 a rozdíl se vynásobí 2

Jakmile ukončíme zápis vzorce, v buňce se objeví výsledná hodnota. Je-li buňka se vzorcem aktuální, pak vzorec vidíme v editačním řádku. Pokud vzorec vložený do buňky není správný, v buňce se místo výsledku objeví chybové hlášení (např. #DIV/0! – dělení nulou).

Použití tabulkových funkcí
Excel poskytuje 10 různých typů tabulkových funkcí: matematické/trigonometrické, vědecké, logické, textové, statistické, informační a vyhledávací, funkce pro datum a čas, pro databáze, finance. Obecný zápis funkce má tvar: název_funkce (argument1, argument2,……….argumentN). Co může být argumentem funkce, záleží na konkrétní funkci. Např. argumentem funkce MAX, která hledá maximální hodnotu, mohou být konstanty, adresy buněk nebo úseků, případně další funkce.

Absolutní a relativní adresy buněk
Každý vzorec může být pomocí povelů Kopíruj, Vyřízni, Přilep přenesen nebo zkopírován na libovolné jiné místo tabulky. Při standardním nastavení souřadnicového systému označuje Excel jednotlivé buňky jako A1, A56, K153 apod. Tento způsob značení se nazývá relativní adresa buňky.

Relativní adresa buňky zajišťuje pří kopírování vzorce, že se jeho nová adresa (souřadnice) relativně změní podle nového umístění vzorce. V praxi to znamená, že pokud např. zkopírujeme vzorec =A5+C5+6(B5+D5), který je umístěn v buňce E5 do buňky E6, dostaneme relativně upravený vzorec =A6+C6+6(B6+D6). Této funkce se využívá v případech, kdy je buď do několika řádků nebo sloupců potřeba umístit stejný vzorec. Vzorec z jedné buňky se prostě nakopíruje do dalších. Musí však jít o přesun pouze v jednom směru. Jinak dostaneme nekorektní výsledky.

Pravým opakem relativní adresy, kdy se vzorec složený z relativních adres při přesunu automaticky přizpůsobuje nové pozici, je absolutní adresa. Absolutní adresa zaručuje, že zkopírovaný nebo přenesený vzorec se bude stále odkazovat na stejné buňky. Typickým případem je použití konstant ve vzorci, které jsou stále na stejném místě tabulky. Pokud bychom použili ve vzorci pro práci s konstantami vzorec s buňkami s relativní adresou a zkopírovali na jiné místo tabulky, posunulo by se i místo, kde je očekávaná hodnota konstanty. Vzorec by pak nepočítal správně. Absolutní adresa je v označení buňky vyznačena znaky $, a výše uvedený vzorec z buňky E5 do kterékoli jiné buňky tabulky by byl zapsán následujícím zápisem: =$A$5+$C$5+6*($B$5+$D$5). Kromě relativních a absolutních odkazů existují ještě odkazy smíšené (např. $A1, A$1, LIST1!$C$24 apod.)

Tvorba a editace tabulky
Filtry
V Excelu můžeme pracovat s takzvanými filtry. Filtrem se rozumí podmínka, kterou je testován každý řádek. Vyhoví-li řádek podmínce, zůstane zobrazen. Pokud podmínce nevyhoví, je skryt. Filtr má velký význam u rozsáhlejších tabulek, kde se pracuje pouze s daty, které by měly vyhovovat určitým kritériím. Filtry lze i kombinovat. Potřebujeme-li například aby byly zobrazeny pouze záznamy, které vyhovují dvěma podmínkám najednou, lze nastavit dva i více filtrů zvlášť.

Formátování tabulky
Formátováním tabulky rozumíme sled úkonů, kterými dosáhneme takové grafické úpravy, aby tabulka byla co nejpřehlednější a poskytovala snadnou orientaci při čtení údajů. Excel nabízí celou řadu nástrojů pro zvýraznění důležitých údajů a zvýšení atraktivnosti tabulek. Formátování tedy znamená úpravu šířky sloupců, výšky řádků, nastavení způsobu zarovnávání obsahu buněk, volbu velikostí, fontů a stylů písma. K vytvoření opravdu působivého dokumentu lze použít i možnost volby různých typů čar a rámečků včetně barevného zvýraznění.

Formátovat lze před zadáním údajů – pak se vkládané údaje již zobrazují v požadovaném formátu, nebo kdykoli později (formát dat se mění).

Při kopírování buňky se kopíruje jak její obsah, tak i formát. Proto je vhodné části, které mají být kopírovány, nejprve zformátovat.

Tvorba a editace grafu
Grafy
Excel nabízí široké spektrum grafů a to od dvojrozměrných sloupcových přes pruhové, plošné, prstencové až po trojrozměrné kruhové, jehlanové, kuželové a další. Grafy jsou v Excelu přímo vázány na data v tabulkách. Jakákoliv pozdější změna dat v tabulce vyústí změnu grafu. Protože se graf chová jako grafický objekt, lze manipulovat s jeho velikostí, libovolně jej přesouvat či vložit jej například do Wordu. Před samotným zhotovením grafu je nutné vytvořit tabulku, ze které bude graf čerpat. Poté se aktivuje průvodce grafem. V několika krocích jsme dotázáni na typ grafu, názvy os, popisky a podobně. U trojrozměrných grafů lze nastavit jejich polohu v prostoru. S tou lze později libovolně manipulovat a graf natáčet a rotovat dle aktuálních potřeb.

Vytváření grafů
Orientace ve velkém množství dat dělá problémy každému z nás. Graf je obrázek, který nám dopomůže lépe se orientovat v nepřehledné, třeba dobře připravené, tabulce. Graf slouží pro reprezentaci dat, pro které jsou rozbory v tabulce již nepostačující a proto není od věci tyto tabulky doplnit grafem. Graf je jakási analogová informace, která je oproti tabulce na první pohled srozumitelnější.

Průvodce nám pomáhá při výběru dat, umístění grafu v sešitě, výběru typu grafu a přidání potřebných popisků a legend. Námi vytvořený graf může být umístěn na listu s daty nebo na samostatném listu, nazvaný GRAF s pořadovým číslem grafu vytvořený v sešitě. Obrázek grafu má v sešitu vlastnosti plovoucího objektu. To znamená, že ho můžeme kdykoliv přesouvat, modifikovat a měnit jeho velikosti. Graf může mít celou škálu podob, většinou se jedná o dvourozměrné nebo třírozměrné grafy. Do grafů můžeme doplňovat trendy, chybové úsečky a klouzavé průměry. Modifikace dat ve zdrojové tabulce se okamžitě promítne i do grafu.

Realizace grafu
Označíme data v tabulce, které chceme do grafu zanést, pomocí myši nebo klávesnice a spustíme proceduru průvodce grafem. To můžeme udělat několika způsoby:

stiskem tlačítka ve standardním panelu nástrojů
příkazem VLOŽENÍ | GRAFY
klávesou F11
Po provedení prvních dvou příkazů se spustí Průvodce grafem, ve kterém se nám nabízí orientační představa vytvářeného grafu. Příkazem F11 se vytvoří graf, na samostatném listu, automaticky podle již předdefinovaného typu grafu.

Po spuštění průvodce se zobrazí první dialogové okno obsahující dvě karty.

„Standardní typy“ – První karta, ve které si můžeme vybrat graf ze čtrnácti základních typů grafů. Každý typ pod sebou zahrnuje i několik podtypů.

„Vlastní typy“ – Druhá karta, která má dvě podoby. Zde záleží jaké políčko je zaškrtnuté jestli „Předefinované“ nebo „Definované uživatelem“. Po stisku se nám zobrazí, jak bude námi definovaný graf vypadat.

Po stisku tlačítka Další, se zobrazí druhé dialogové okno „Zdrojová data grafu“ obsahující dvě karty.

„Oblast dat“ – Karta, která slouží k určení toho, zda datové řady tvoří řádky nebo sloupce. Oblast dat je možno upravit zápisem z klávesnice nebo vymezení myší.

„Řada“ – Na této kartě můžeme provést další úpravy v datových oblastí. Tuto kartu můžeme většinou přeskočit.

Po odeslání se nám otevře třetí dialogový panel „Možnosti grafu“, kde se určují další parametry grafu. Počet nabídkových karet na tomto panelu závisí na typu vybraného grafu, většinou jich bývá šest.

„Název“ – Sem se zapisuje název grafu a popisky jednotlivých os. Název grafu může být pouze jednořádkový.

„Osy“ – Zde určíme, jaké osy mají být vytvořeny. Osy hodnot mohou být dvě a osy kategorií se mohou vytvářet automaticky, kategorie, časová osa.

„Mřížky“ – Tady určíme, jaké příslušné mřížky mají být zobrazeny. Většinou postačí hlavní mřížka. Vzdálenosti čar v mřížce určíme dodatečně při formátování osy na kartě Měřítko.

„Legenda“ – Ta této kartě určíme, zda se má ke grafu připojit legenda, též klíč grafu, a kde má být u grafu umístěna.

„Popisky dat“ – Zde určíme, jestli se k bodům mají připojit hodnoty nebo popisky a to buď s číslem nebo procentuálně.

„Tabulka dat“ – Zde určujeme, jestli se má ke grafu připojit tabulka s daty a zda má obsahovat klíč (značky) použité v legendě.

Poslední, čtvrté, dialogové okno „Umístění grafu“ nám pomáhá při rozhodování o umístění. V tomto okně si můžeme vybrat, jestli chceme, aby graf byl umístěn na novém listu nebo jako objekt na list se zdrojovými daty.

Tvorba grafu pomocí Průvodce není nikterak složitá, obtížnost spočívá pouze ve správném výběru grafu pro specifikovaný problém a jeho grafické zpřehlednění.

Kontingenční tabulky a grafy
Kontingenční tabulka je speciální druh tabulky, která se vytváří ze zdrojových dat seznamu. Předností tabulky je poskytování různých pohledů na data a včetně jejich úprav. Můžeme interaktivně vytvářet různé modifikace kontingenční tabulky. Z dat tabulky také můžeme vytvářet grafy. Data do kontingenční tabulky můžeme použít ze seznamu, sloučené oblasti z různých sešitů a listů, data z již existující kontingenční tabulky nebo data z vnějších databázových souborů z jiných aplikací.

K vytvoření tabulky nám pomáhá Průvodce kontingenční tabulkou stávající se ze čtyřech dialogových panelů. Ještě než zadáme příkaz VLOŽENÍ | KONTINGENČNÍ TABULKA musíme umístit buňkový kurzor do oblasti prostoru databáze, ze které chceme tabulku vytvářet.

V prvním dialogovém okně označíme druh dat, ze kterých se bude tabulka vytvářet. Vybrat si můžeme z této nabídky:

Seznam nebo databáze Microsoft Excel
Externí zdroj dat
Násobné oblasti sloučení
Jiná kontingenční tabulka
Po odeslání tlačítka další se otevře v pořadí druhém panel, ve kterým se potvrdí nebo modifikuje zdrojová oblast.

Po odeslání se otevře třetí dialogové okno, které je klíčové pro koncepci kontingenční tabulky. Hlavními oblastmi tabulky jsou:

Oblast stránek
Oblast řádků
Oblast sloupců
Oblast dat
Oblast řádků a sloupců tvoří souřadnice tabulky a oblast dat je na jejím průsečíku. Oblast stránek tvoří třetí rozměr tabulky, který nám dopomáhá k listování tabulkou. První tři oblasti jsou identifikační, do kterých je možno myší přetahovat tlačítka z pravé strany dialogového okna průvodce. Názvy těchto tlačítek jsou názvy všech polí ze seznamu. Celkový počet řádků a sloupců tabulky je omezen velikostí listu sešitu. V poslední oblasti, oblasti dat, se zobrazuje výsledek souhrnné funkce. Excel nabízí devět funkcí: součet, počet hodnot, průměr, maximum, minimum, počet čísel, odhad směrodatné odchylky, směrodatná odchylka, odhad rozptylu a rozptyl. V oblasti dat se nemůže zobrazovat text. Pro čísla je implicitně nastaven součet a pro text počet hodnot.

Každá z těchto oblastí může mít, ale nemusí, více přetažených tlačítek nebo žádné. Vy jímkou je oblast dat, která musí mít alespoň jedno tlačítko pole.

Po nadefinování struktury kontingenční tabulky a klepnutí na tlačítko „Další“ se zobrazí poslední panel průvodce tabulkou, ve kterém už jen specifikujeme umístění a nastavení parametrů tabulky. S již vytvořenou tabulkou můžeme různě manipulovat a tím měnit pohledy na data. Modifikace tabulky se okamžitě projeví v upravení a přepočítání dat v ní.