Teorie

3. Adresování buněk
3.1 Typy odkazů na buňku
Už víme, že adresa buňky je dána označením řádku a sloupce, tedy např. adresa buňky ve sloupci B a řádku 4 je B4. Tento způsob odkazu na buňku však není jediný. Odkazy mohou být:
  
Relativní
A2
  
Absolutní
$A$2
  
Smíšené
$A2 nebo A$2
  
Externí
List1!A2 nebo 'C:\zrus\[Sesit1.xls]List1'!A2
  
Odkaz na oblast  
A1:D4 (oblasti lze pojmenovat a odvolat se na ně jménem)
Relativní adresy
Při kopírování vzorců jste si možná všimli tohoto:Kopírujeme vzorec =B1-A1 z buňky C1 do buněk C2:C5. Když se podíváme na obsah buňky C2, bude tam vzorec =B2-A2. Podobně v buňce C3 je =B3-A3 atd.
Adresa napsaná tak, jak ji známe doposud, se při kopírování vzorce mění a to způsobem, který odpovídá přesunu vzorce. O kolik sloupců či řádků se liší pozice zkopírovaného vzorce od pozice původního vzorce, o tolik sloupců či řádků se liší adresy obsažené v zkopírovaném vzorci od adres v původním vzorci. Adresy skládající se pouze ze jména řádku a sloupce (A1, B4, D7 ap.) nazýváme relativní.
Absolutní adresy
Je zřejmé, že změna adresy při kopírování vzorce nemusí být vždy žádoucí. Jestliže chceme, aby byla adresa neměnná při kopírování, napíšeme před jméno sloupce i řádku znak $ ($A$1, $B$4, $D$7 ap.). Takto zapsané adresy nazýváme absolutní.
Smíšené adresy
Je také možné napsat $ pouze před řádkem nebo pouze před sloupcem. V tom případě část adresy s $ je absolutní a část adresy bez $ je relativní. Takovéto adresy nazýváme  smíšené.
Poznámka:Pro snadné vložení znaku $ na české klávesnici můžeme použít klávesu F4 Tato klávesa funguje jako přepínač mezi relativní, absolutní a smíšenými adresami buňky. Jestliže jsme vložili relativní adresu, prvním stiskem se změní na absolutní, dalším na smíšenou.
Odkazy na oblast buněk
V mnohých případech je zapotřebí odkázat se na celý úsek buněk. Odkaz na úsek buněk sestává z adres dvou protilehlých rohů (zpravidla levého horního a pravého dolního) mezi něž napíšeme dvojtečku. Např.:
/obrazky/3.1b2c4.jpg" \* MERGEFORMATINET
úsek B2:C4
Důležitým jednotlivým buňkám i úsekům tabulky můžeme přiřadit název. Máme-li buňku či úsek označené, volíme z hlavní nabídky Vložit — Název — Definovat... a v zobrazeném dialogovém okně napíšeme název buňky či úseku. Na pojmenované úseky se můžeme místo adresami odvolávat jejich názvem.
Externí odkazy
Protože buňky mají stejné adresy na všech listech v každém sešitě, je třeba se při práci s více listy nebo sešity odkazovat na buňky nejen jejich absolutní či relativní adresou, ale i jménem listu, případně sešitu (souboru).
Mezi název listu a adresu buňky či úseku píšeme " !". Např. na buňku B5 listu List2 se odkážeme List2!B5. Je-li list přejmenován a má v názvu mezeru, musíme dát jméno listu do apostrofů, např. 'Přehled A'!B5. Odkaz na buňku B5 na listech List1 až List3 napíšeme takto: List1:List3!B5.
Pokud se odvoláváme na údaje z jiného sešitu (tj. souboru), musí být v adrese buňky obsažen i název souboru. Odkaz na úsek A2:B12 na listě Výdaje v souboru TAB1 tedy vypadá takto: ='[tab1.xls]Výdaje!$A$2:$B$12'. V zadání jména souboru může být obsažena i cesta k tomuto souboru: = 'a:\prehledy\[tab1.xls]Výdaje'!$A$2:$B$12.
3.2 Příklad tabulky se vzorci
Celá tato kapitola je současně cvičením na zopakování znalostí a dovedností z předchozích kapitol. Naším cílem je vytvoření následující tabulky:
Obrázek 3.2.1: Příklad tabulky se vzorci a funkcemi
Začněte tím, že napíšete do tabulky seznam osob s jejich platy a příplatky. Chcete-li vložit 10 jmen a příjmení tak, jak je tomu na obrázku, rychlý postup je například tento: Kliknětě na buňku A4 a označte oblast buněk A4 až D13. Nyní můžete psát jméno, příjmení, plat, příplatek, jméno, příjmení, plat, příplatek atd., každý zápis do buňky ukončete klávesou Tab. Máte-li celý seznam napsaný, označte sloupce A a B a zadejte příkaz Formát — Sloupec — Přizpůsobit. Šířka sloupců A a B se přizpůsobí délce jmen a příjmení, která jste do nich vložili.
Vyplňte názvy sloupečků v tabulce. Klikněte na buňku C2 a napište Plat. Stisknětě klávesua napište Osobní příplatek. Stejným způsobem postupujte dále, až nadepíšete záhlaví všech sloupečků. Označte právě vyplněné buňky C2 až H2. Zadejte příkaz Formát — Buňky... a zobrazte si kartu Zarovnání. Zvolte vodorovné i svislé zarovnání Na střed a zaškrtněte políčko Zalomit text. Ukončete příkaz stiskem tlačítka xcel/obrazky/2.6tl_OK.jpg" \* MERGEFORMATINET . Pokud je text v buňce H2 je zalomen do tří řádků, což nevypadá pěkně, uchopte myší hranici mezi sloupci H a I a rozšířte sloupec H tak, aby v buňce H2 byly dva řádky.
Označte myší buňku E3 a napište 8%, stiknětě klávesu  , napište 4,5%. Stiknětě dvakrát klávesu  , napište 2200 a ukončete vkládání tlačítkem . Buňka H3 je aktuální. Stiskněte tlačítko na panelu nástrojů. Text v buňce se zarovná na střed. Označte buňky E3 a F3, zadejte příkaz Formát — Buňky... a zvolte kartu Číslo. Vyberte druh procenta a nastavte počet desetinných míst na 1. Dále označte kartu Zarovnání a na ní zvolte vodorovné zarovnání Na střed. Tlačítkem ukončete příkaz.
Doplňte nadpis tabulky. Text nadpisu vložte do buňky A1. Zápis ukončete stiskem tlačítka , aby buňka zůstala aktuální. Pomocí panelu nástrojů zvětšíte velikost písma v buňce. Pak označte buňky A1 až H1 a na kartě Zarovnání příkazu Formát — Buňky... zvolte vodorovné zarovnání Na střed výběru a svislé zarovníní Na střed. Je vhodné ještě zvětšit výšku prvního řádku.
Sloupec E obsahuje sociální pojistné, jehož velikost v procentech je v buňce E3. Do buňky E4 je tedy třeba vložit vzorec, který vypočte výraz (C4+D4)*E3. V buňce E5 musí být (C5+D5)*E3, v buňce E6 (C6+D6)*E3 atd. Je tedy vidět, že vzorec do buňky 4 musíme zapsat tak, aby se adresa E3 při jeho kopírování do dalších buněk sloupce E neměnila. Zapíšeme ji tedy jako adresu absolutní, tj. $E$3. Došli jsme tedy ke vzorci (C4+D4)*$E$3. Vypočtené pojistné se však zaokrouhluje, a to vždy nahoru na celé koruny. Funkce pro zaokrouhlování nahoru se jmenuje ROUNDUP a má dva parametry. Prvním parametrem je číslo, které chceme zaokrouhlit, druhý parametr určuje řádovou přesnost zaokrouhlení. Je-li druhým parametrem 0, zaokrouhlí se na celá čísla, je-li druhým parametrem např. 2, zaokrouhlí se na setiny a je-li druhý parametr např. -3, zaokroulí se na celé tisíce. V našem případě bude prvním parametrem funkce ROUNDUP výraz (C4+D4)*$E$3, druhým parametrem 0.
Označte buňku E4 a umístěte kurzor do editačního řádku. Psaní vzorců v editačním řádku je totiž přehlednější než přímo v buňce. Napište vzorec: =ROUNDUP((C4+D4)* $E$3;0). Hledání znaku $ se vyhnete, jestliže místo $E$3 napíšete E3 a pak bezprostředně stisknete klávesu F4. Vkládání ukončete tlačítkem . V buňce se objeví číslo – výsledná hodnota vzorce. Pokud tomu tak není, zkontrolujte si zápis vzorce.
V buňce F4 bude vzorec velmi podobný vzorci v buňce E4. Je třeba sečíst obsah buněk C4 a D4, vynásobit hodnotu v F3 a zaokrouhlit na celá čísla. Do buňky F4 tedy patří vzorec =ROUNDUP((C4+D4)*$F$3;0). Abyste si ušetřili psaní celého vzorce, zkopírujte obsah buňky E4 do buňky F4. Buňka E4 by měla být stále aktuální, takže stačí stisknout klávesu Ctrl, přesunout kurzor myši na okraj označené buňky, stisknout levé tlačítko myši a posunout o jednu buňku vpravo. Uvolněte nejdříve tlačítko myši, pak teprve klávesu Ctrl. V editačním řádku vidíte obsah aktuální buňky F4: =ROUNDUP((D4+E4)*$E$3;0). Umístěte kurzor do editačního řádku a opravte adresu E4 ve vzorci na C4 a adresu $E$3 na $F$3 . Ukončete stiskem tlačítka .
Poznámka:Kdybychom mysleli více dopředu a vzorec v buňce E4 zapsali se smíšenými adresami ve tvaru =ROUNDUP(($D4+$E4)* E$3;0), nemuseli bychom po jeho zkopírování do buňky F4 dělat žádnou opravu.
Ve sloupci G je základ daně, což je součet platu a osobního příplatku snížený o sociální a zdravotní pojistné. Výsledná hodnota se opět zaokrouhluje směrem nahoru, a to na celé stovky. Do buňky G4. tedy zapište vzorec =ROUNDUP(C4+D4-E4-F4;-2). V sloupci H je základ daně snížený o 2200, což je obsah buňky H3. Do buňky H4 tedy vložte jednoduchý vzorec bez funkce: =G4-$H$3.
Označte oblast buněk E4 až H4. Přesuňte kurzor na tažný úchyt v pravém dolním rohu označené oblasti (změní se na tenké +), stisknětě levé tlačítko myši a tahem dolů kopírujte obsah označené části až po třináctý řádek.
Podíváte-li se na cílovou tabulku, vidíte, že zbývá vložit do buňky A15 text průměrný plat:, do buňky A17 text největší příplatek:, do C15 vzorec =PRŮMĚR(C4:C13) a do buňky D17 vzorec =MAX(D4:D13).
Posledním úkolem je pro vás orámování buněk tabulky podle vzoru.
 

8. Datová analýza
8.1 Databázový seznam
Mnoho tabulek zpracovávaných tabulkovými procesory má podobu seznamu: seznamy klientů, úkolů, zboží, telefonní seznam. Tyto seznamy mají charakter databáze a Excel s nimi umožňuje provádět obvyklé databázové operace jako je řazení, filtrování, zjišťování souhrnných informací, prohlížení a opravování dat po záznamech. Navíc poskytuje možnost vytvořit tzv. kontingenční tabulku (viz. kapitola 8.5), která kombinuje a porovnává data v tabulce. Tím má Excel k dispozici poměrně silné nástroje pro analýzu dat.
Než se s těmito nástroji seznámíme, připomeneme si, určitá pravidla, která platí pro seznamy, se kterými chceme pracovat jako s  databázovými tabulkami. Tabulka většinou popisuje nějaký objekt (osoba, kniha, prodej). Do sloupců tabulky se zapisují vlastnosti, které nás na tomto objektu zajímají (např. jméno, adresa, plat u zaměstnance, název, autor, cena u knihy). Řádky potom tvoří záznamy těchto údajů pro jednotlivé konkrétní prvky sledovaného objektu (jednoho zaměstnance, jednu knihu). První řádek tabulky - záhlaví obsahuje popis údajů obsažených v jednotlivých sloupcích. Hodnoty jednoho sloupce ve všech řádcích tabulky jsou stejného typu (např. číselné, textové). Seznam nesmí obsahovat prázdný řádek, jinak by nebylo možné části nad a pod prázdným řádkem zpracovávat jako jeden seznam.
Databázová tabulka může být vytvořena přímo v Excelu nebo lze pracovat s tabulkou vytvořenou v nějakém databázovém systému. Např. databázový soubor vytvořený v systémech FoxBase či dBase lze přímo otevřít příkazem Soubor — Otevřít. Kromě toho umožňuje Excel přístupovat k externím databázím zadáním příkazu Data — Importovat externí data. Zde můžeme pomocí Průvodce datovým připojením nadefinovat přístup k různým databázím (MS Access, SQL server, Oracle) nebo vytvořit dotaz, který z  externího zdroje vybere požadovaná data a přenese je do Excelu.
Ukázka databázového seznamu
Na obrázku 8.1.1 vidíme část databázového seznamu. Jedná se o databázi prodejů ekonomických agend. Přitom není podstatné, zda vznikla přímo vložením dat v Excelu nebo načtením z externího zdroje. Data byla pořizována v průběhu několika let, kdy se v jednotlivých čtvrtletích zapisovaly informace o počtu realizovaných prodejů jednotlivých agend. Záhlaví tabulky (první řádek sloupců A:F) popisuje strukturu databázové tabulky. 
Taková data jsou zdrojem informací například pro rozhodování, která agenda je perspektivní z hlediska výše zisku, počtu prodejů nebo v závislosti na čase. Pokud jsou ale data neuspořádaná, je taková analýza obtížná. V dalších kapitolách si ukážeme prostředky, jak požadované informace získat.
Excel umožňuje i část tabulky na listu definovat jako databázový seznam (příkazem Data — Seznam — Vytvořit seznam). V záhlaví oblasti seznamu se objeví nastavený filtr (viz. kapitola 8.3) a poslední řádek je vyhrazen pro souhrnné funkce. To umožňuje spravovat a analyzovat data seznamu nezávisle na ostatních datech mimo seznam.
ATINET
Obrázek 8.1.1 Příklad databázového seznamu
Datový formulář
Prvním krokem k zefektivnění přístupu k datům u velmi rozsáhlých tabulek s  velkým počtem řádků i sloupců (nikoli však nástrojem datové analýzy), je možnost zobrazit si všechna data jednoho záznamu (všechny sloupce jednoho řádku) v jednom dialogovém okně - formuláři. Formulář, dostupný příkazem Data — Formulář..., umožňuje procházet tabulku záznam po záznamu, dělat opravy, případně odstraňovat či přidávat záznamy. Pro zobrazování záznamů si můžeme stanovit kritérium, které omezí zobrazované záznamy. V dialogovém okně formuláře je zobrazen vždy jeden celý záznam. Každá položka je v samostatném textovém poli. Položky, které nelze opravovat (např. jsou výsledkem vzorce), jsou zobrazeny bez textového pole. Po zadání příkazu se v dialogovém okně objeví vždy první záznam databáze.
 
materialy/excel/obrazky/8.1dbFormular1.jpg" \* MERGEFORMATINET
Obrázek 8.1.2 Ukázka datového formuláře
 
Obrázek 8.1.3 Nastavení kriteria ve formuláři
Pomocí tlačítek si.jpg" \* MERGEFORMATINET a  se můžeme pohybovat po záznamech. Rychlejší listování umožňují šipky a táhlo posuvníku.Chceme-li omezit záznamy, mezi kterými se pohybujeme, můžeme stanovit podmínku pro jejich zobrazování. To lze realizovat pomocí tlačítka , kdy se zobrazí stejně vyhlížející, ale prázdný formulář. Do prázdného textového pole libovolné položky můžeme zapsat podmínku. Zápis podmínky vidíme na obrázku 8.1.3: pokud do pole rok v našem příkladu napíšeme 2001, bude se vyhodnocovat podmínka rok= 2001. Pokud budeme chtít zobrazovat jen informace o prodejích agend s cenou vyšší než 50 000, napíšeme do pole cena výraz >50000.  Zadávání podmínek ukončíme tlačítkem , nebo . Nadále se budou zobrazovat pouze záznamy vyhovující stanoveným podmínkám. V našem příkladu na obrázku 8.1.3 se budou vyhodnocovat podmínky obě, budou tedy zobrazovány pouze prodeje roku 2001 jejichž cena je vyšší než 50 000. Chceme-li nastavená kritéria zrušit, stiskneme nejprve tlačítko a ve formuláři Kritéria pak tlačítko mazat.jpg" \* MERGEFORMATINET .
Tlačítko slouží k vložení dat nového záznamu. Po jeho stisknutí jsou všechna pole formuláře prázdná, připravená pro vstup nových dat. Po položkách záznamu se pohybujeme pomocí klávesy Tab (nikoli Enter!). Klávesou Enter se vkládání nového záznamu ukončuje. Nový záznam je přidán jako nový řádek na konec tabulky.
Kdykoli při procházení jednotlivými záznamy můžeme hodnoty v textových polích opravovat. Dokud máme záznam zobrazený, můžeme provedené opravy zrušit tlačítkem . Jakmile přejdeme na jiný záznam, jsou opravy záznamu uloženy.
Zobrazený záznam odstraníme z databáze stiskem tlačítka .
8.2 Řazení údajů
Řazení je nejjednodušším způsobem datové analýzy. Data seřazená podle různých aspektů zlepšují orientaci ve velkém množství dat a urychlují vyhledávání. Příkaz Data — Seřadit... umožňuje setřídit jak celý seznam, tak libovolnou označenou souvislou oblast tabulky. Třídíme-li celý seznam, není třeba ho celý označovat, stačí označit libovolnou buňku seznamu.
V dialogovém okně příkazu Data — Seřadit... volíme sloupec, podle kterého chceme řádky (záznamy) setřídit a způsob třídění (vzestupně – sestupně). Lze určit i další dva sloupce, podle nichž se má třídit v případě shody hodnot.
Tlačítkem se dostaneme k dalšímu dialogovému oknu, kde můžeme zvolit:
klíč k řazení (normální pro text a čísla nebo předdefinované textové posloupnosti)
rozlišování malých a velkých písmen
orientaci (odshora dolů = řazení řádků podle hodnot ve sloupci, zleva doprava = řazení sloupců podle hodnot v řádku)
ium/materialy/excel/obrazky/8.2razeni.jpg" \* MERGEFORMATINET
 
Obrázek 8.2.1 Dialogové okno pro řazení tabulky
 
Obrázek 8.2. Rozšířené možnosti řazení
Při nastavení podle obrázku 8.2.1 a 8.2.2 bude naše databáze agend seřazena abecedně podle jednotlivých roků a čtvrtletí od nejstarších prodejů k nejnovějším.
8.3 Filtrování údajů
Jednou z výhod zpracování dat na počítači je rychlé a snadné vyhledávání údajů splňujících určitou podmínku. Pro vyhledání a zobrazení řádků (záznamů) s určitou vlastností má Excel v nabídce hlavního menu k dispozici příkaz Data — Filtr. Seznam, ve kterém chceme vyhledávat pomocí filtrů, musí mít pojmenované sloupce.
V  běžných případech vystačíme s automatickým filtrem. Po zadání volby Data — Filtr — Automatický filtr se v záhlaví sloupců seznamu objeví tlačítka se spouštěcími šipkami. Stisk tohoto tlačítka v určitém sloupci umožní zformulování podmínky pro vyhledávání vztahující se k tomuto sloupci. Po kliknutí na stahovací šipku se zobrazí okénko se seznamem. První položky seznamu jsou Seřadit vzestupně, Seřadit sestupně, (vše), (prvních 10...) a (vlastní...), pak následuje seznam všech hodnot ve sloupci. Dřívější verze obsahovaly ještě jako poslední dvě položky (prázdné) a (neprázdné). Význam jednotlivých voleb je následující:
Seřadit vzestupně nebo sestupně umožňuje požadovaným způsobem seřadit filtrovaná data podle vybraného sloupce.Totéž lze provést použitím tlačítek nebo ze standardního panelu nástrojů (v dřívějších verzích tato volba není).
(vše) ruší případné dřívější nastavení filtru v daném sloupci
(vlastní...) umožní formulování podmínky pomocí operátorů = , , , < =, > =;    podmínky mohou být stanoveny i dvě a to tak, že platí obě současně nebo alespoň jedna;    pro text lze použít zástupné znaky "*" a "?"
(prázdné) zobrazí řádky, které nemají v tomto sloupci data
(neprázdné) zobrazí řádky, které mají v tomto sloupci data
Filtry můžeme nastavit ve více sloupcích. Platí pak všechny současně.
Zrušení všech nastavených filtrů a odstranění spouštěcích tlačítek ze záhlaví sloupců dosáhneme stejným postupem, jako jsme automatický filtr zapnuli, tj. Data — Filtr — Automatický filtr (nebo zrušením oblasti seznamu příkazem Data — Seznam — Převést na rozsah).
Na tabulce z obrázku 8.1.1 si ukažme, jak vytvoříme vlastní filtr. Budeme chtít zobrazit pouze prodeje (řádky tabulky) agend účetnictví (UcetnictviJ a UcetnictviP). Po aktivaci automatického filtru příkazem Data — Filtr — Automatický filtr, zvolíme v rolovacím seznamu možností ve sloupci Agenda možnost (vlastní) a v dialogovém okně zadáme podmínky jako na obrázku 8.3.1.
Obrázek 8.3.1 Dialogové okno pro nastavení vlastního automatického filtru
Potřebujeme-li zadat pro vyhledávání takové podmínky, že je nejsme schopni nastavit pomocí automatického filtru, použijeme rozšířený filtr. Podmínky se zapisují přímo na list mimo seznam, se kterým pracujeme. Za tímto účelem vložíme před seznam několik prázdných řádků. Oblast podmínek je malá tabulka, v jejímž prvním řádku jmusí být názvy kritérií. U obyčejných porovnávacích kritérií se název kritéria shoduje s názvem sloupce seznamu. Vlastní podmínky jsou zapsány pod názvy sloupců. Podmínky, které mají platit zároveň, píšeme do jednoho řádku; podmínky, z nichž má platit alespoň jedna, píšeme do různých řádků. Má-li platit zároveň více různých podmínek pro jeden sloupec, musíme tento sloupec uvést tolikrát, kolik je podmínek.
Jako podmínka může být použit i vzorec, jehož výsledkem je logická hodnota. Zobrazí se pak ty řádky, pro které vyjde hodnota "0". Vzorec se musí odkazovat alespoň na jeden sloupec seznamu a to buď jeho názvem nebo relativní adresou první buňky sloupce. Použijeme-li název sloupce seznamu, objeví se v buňce se vzorcem chyba #NÁZEV?, která však nemá vliv na správné filtrování seznamu. Název kritétia se vzorcem volíme odlišný od názvů sloupců seznamu.
Jestliže máme vytvořenou oblast podmínek, označíme libovolnou buňku seznamu a zadáme příkaz Data — Filtr — Rozšířený filtr. Zobrazí se dialogové okno (viz. obrázek 8.3.2), ve kterém je vyplněna adresa oblasti seznamu. (Pokud ne, zapomněli jsme označit buňku seznamu.) Umístíme textový kurzor do políčka Oblast kritérií a označíme tuto oblast na listě. Z  obrázku vidíme, že můžeme filtrovaný seznam zobrazit přímo v seznamu nebo na jiném místě. Pokud vybereme možnost Kopírovat jinam, zpřístupní se řádek Kopírovat do, ve kterém zadáme umístění filtrovaného seznamu.
Obrázek 8.3.2