MS EXCEL část 1. 1 Tabulkové procesory. 2 Základní údaje o MS Excelu. 2.1 Charakteristika. 2.2 Spuštění a ukončení programu

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem

MS EXCEL část 1. Ing. Petr Votava 2002

1 Tabulkové procesory Tabulkové procesory (kalkulátory) 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í (jeden řádek tabulky = jednomu záznamu v databázi) a provádět třídění (indexaci) i výběr (filtraci) dat podle zadaných kritérií. Často používanou formou výstupu bývá vedle tabulky také graf. Moderní tabulkové procesory poskytují náročnějším uživatelům nejrůznější analytické nástroje, umožňují řešit složité problémy formou modelů, sdílet data v tabulkách v síti a vytvářet výstupy pro Internet. Tabulkové procesory mají za sebou více než pětadvacetiletou historii, přesto zůstávají principiálně stejné. Pracovní plocha tabulkového procesoru je tvořena buňkami, do kterých se zapisují čísla, text, datum a čas, vzorce s odkazy i funkcemi. Z počátku byl každý soubor jeden list (worksheet) obsahující buňky adresovatelné sloupci a řádky. V současné době představují soubory v tabulkových procesorech sešity složené z listů. Klasickým tabulkovým kalkulátorem byl program LOTUS 1-2-3, který se stal vzorem pro další programy tohoto typu - např. Quattro Pro, SuperCalc, Calc602, MS Excel.

2

Základní údaje o MS Excelu

2.1 Charakteristika Tabulkový procesor, který uživateli umožňuje: • Vytvářet tabulky a vyhodnocovat je pomocí vzorců a funkcí. • Vytvářet grafy nejrůznějších typů a doplňovat je případně o trendy včetně předpovědi vývoje a perspektivy. • Pracovat s tabulkou jako s databází, řadit jednotlivé záznamy a vybírat je podle určitých kritérií. • Vkládat mezisoučty do seznamů. • Ze seznamů vytvářet tzv. kontingenční tabulky a kontingenční grafy, umožňující dynamický pohled na zadaná data. • Analyzovat data a vytvářet modely řešení.

2.2 Spuštění a ukončení programu Program lze spustit dvojím kliknutím na ikonu Microsoft Excel, která se nachází nejčastěji v nabídce Start / Programy, případně ve složce MS Office. Program lze také spustit souborem Excel.exe. Ukončení programu Excel provedeme pomocí menu Soubor / Konec nebo kombinací kláves Alt+F4.

2.3 Prostředí a ovládání programu MS Excel se zobrazí v okně, které tvoří: Panel nabídek: Obsahuje příkazy pro ovládání programu. Menu je možné ovládat myší nebo stiskem klávesy Alt v kombinaci s příslušnou aktivační klávesou (v menu jsou aktivační klávesy podtrženy). Panely nástrojů: Ikonky pro ovládání programu myší. Ponecháme-li kurzor myši položen na některé z ikonek, objeví se kontextová nápověda. Řádek vzorců: Obsahuje adresu aktuální buňky a její obsah. Zobrazení řádku potlačíme v menu Zobrazit / Řádek vzorců. Sešit s jednotlivými listy: Každý list je samostatnou tabulkou tvořenou buňkami. Listy je možné volit kliknutím na záložky listů v dolní části sešitu. Stavový řádek: Poslední zobrazený řádek v okně MS Excel obsahující informace o stavu programu a nastavení některých přepínačů, např. indikace zapnutého režimu přepisování znaků, psaní velkých písmen (CapsLock), zapnutí ScrollLock a NumLock, vstupu na pevný počet desetinných míst (přepínač FIX). Zobrazení stavového řádku můžeme potlačit v menu Zobrazit / Stavový řádek.

1

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem Místní nabídka: Zobrazí se při stisku pravého tlačítka myši. Obsahuje příkazy použitelné v daném okamžiku. Nápověda a volby uživatelského prostředí: Práce s nápovědou i možnosti nastavení uživatelského prostředí zůstávají v MS Excel prakticky shodné s ostatními programy rodiny Office. Předvolby pro zobrazování prvků programu můžeme měnit buď v menu Zobrazit, nebo příkazem Nástroje / Možnosti a volbou Zobrazení. Karta Obecné ve shodném menu obsahuje nastavení některých základních vlastností programu: např. počet zobrazovaných názvů souborů, s nimiž uživatel v poslední době pracoval, počet zobrazených listů v sešitu, určení standardního písma, výchozí umístění souborů nebo jméno uživatele. Shodně jako v MS Word lze rovněž nastavovat podobu nástrojových panelů a panelů nabídek: využíváme k tomu menu Nástroje / Vlastní.

3 Práce s listy 3.1 Sešit - dokument Excelu Sešit je základním typem dokumentu programu Excel. Otevíráme-li nový soubor, zakládáme tak nový sešit. Je elektronickou obdobou kroužkového bloku. Sešit obsahuje listy, což bývají nejčastěji tabulky nebo grafy. Název každého listu se objeví v záložce v dolní části sešitu. Jednotlivé listy v sešitě lze zkopírovat nebo přemístit i do jiného sešitu. Listy je možné do sešitu přidávat nebo ze sešitu je odstranit. V jednom okamžiku můžeme mít otevřeno více sešitů najednou.

3.2 Operace s dokumenty Při práci s dokumentem MS Excelu (sešitem) využíváme následujících operací z menu Soubor: Nový: Vytvoření nového sešitu (také ikona Nový). Otevřít: Otevření existujícího sešitu (také ikona Otevřít). Zavřít: Příkazem uzavřeme aktivní dokument. Uložit: Uložení sešitu také (ikona Uložit). V menu Nástroje / Automatické ukládání lze zadat časový interval, po kterém bude uložení sešitu provedeno automaticky. Pokud příkaz Automatické ukládání není obsažen v menu Nástroje , je třeba jej nainstalovat prostřednictvím menu Nástroje / Doplňky. Sešit je běžně ukládán do souboru s příponou .XLS. Je-li zaškrtnuto políčko Vždy vytvořit záložní kopii v menu Soubor / Uložit jako / Volby , pak se ve stejném adresáři vytváří záložní kopie sešitu s příponou .BAK. Po stisku tlačítka Možnosti... se objeví dialogové okno, v němž můžeme mimo jiné nastavit hesla k zabezpečení čtení a zapisování do souboru. Uložit jako: Sešit lze uložit také v jiném typu formátu (např. ve formátu .DBF). Uložit jako HTML: Automatické vygenerování a uložení tabulky nebo grafu v HTML kódu (podoba webové stránky). Uložit prostor: Do pracovního prostoru (soubor s příponou .XLW) se ukládají informace o uspořádání souborů a o složkách, kde jsou uloženy. Neukládají se v něm samotné soubory, proto je nemůžeme, bez ztráty možnosti je znovu vyvolat pomocí pracovního prostoru, přemístit do jiné složky. Vlastnosti: Poskytuje souhrnné informace o pracovním sešitu, statistické údaje, obecné informace o souboru a nastavení vlastního uživatelského prostředí. Pro uspořádání sešitů v okně slouží menu Okno: Okno / Nové okno: Vytvoření dalšího okno pro aktivní sešit Okno / Uspořádat: Uspořádání oken na obrazovce Okno / Rozdělit: Rozdělení aktivního dokumentu vodorovnou příčkou na dvě oblasti - v každé můžeme vidět jinou část dokumentu.

3.3 Práce s listy Výchozí sešit obsahuje při otevření standardně 3 listy, které lze zobrazovat klepnutím na příslušnou záložku. Můžeme pracovat s více listy najednou, když na záložky vybíraných listů klepneme myší při současném stisku Ctrl. Pohyb po listu: Pomocí rolovacích (přetáčeních) lišt. Zadáním odkazu na buňku či oblast v orientačním poli. Vyhledáním obsahu buňky - menu Úpravy / Najít. Vkládání listů provádíme pomocí menu Vložit. Zde můžeme rovněž určit typ vkládaného listu (např. list s grafem přes položku Graf ).

2

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem Odstranění listů provedeme z menu Úpravy / Odstranit list. Přejmenování listu provedeme dvojklikem na záložku příslušného listu. Jméno listu může obsahovat maximálně 31 znaků. Přesun listů provedeme pomocí myši - přetažením záložky na nové místo. Kopie listů se provádí obdobně - přidáme pouze stisk Ctrl. Přesouvání a kopírování listů do jiných sešitů provedeme přes menu Úpravy / Přesunout list .

4 Tabulka a buňky Základem každého tabulkového procesoru je tabulka. Tabulka je složena z nejmenších částí - tzv. buněk. Tabulku MS Excel tvoří 255 sloupců a 65536 řádků buněk. Každá buňka je definována souřadnicemi (adresou) - tj. označením sloupce a řádku (např. A8, C256, AB13 atd.). Souvislý blok buněk tvoří tzv. oblast (např. A1:C10). Výčet několika buněk tvoří tzv. seznam (např. A1;B8;C10;F18).

4.1 Obsah a formát buňky Každá buňka má svůj obsah a formát (styl). Obsahem buňky je: • Konstanta - hodnota, kterou zapisujeme přímo do buňky. Může to být numerická hodnota, datum a čas, logická hodnota nebo text. Konstantní hodnoty můžeme změnit pouze označením příslušné buňky a přepsáním dané hodnoty. • Vzorec - začíná vždy rovnítkem (=) a provádí různé operace s buňkami, obsahujícími konstantní hodnoty (např. =A4+A5+B6+B5; vzorec provádí sečtení hodnot v buňkách A4, A5, B6 a B5). V buňce, která obsahuje vzorec, je běžně zobrazován výsledek vzorce. Výsledek vzorce se mění v závislosti na změnách ostatních hodnot v tabulce. Formátem buňky rozumíme především vzhled buňky (typ písma, zarovnání, orámování, vyplnění, formát čísla atd.). Změna obsahu buňky neovlivňuje styl buňky a naopak.

4.2 Označování buněk a vkládání údajů Před vkládáním údajů nebo použitím většiny příkazů musíme nejprve označit buňky nebo objekty, se kterými chceme pracovat: Označení 1 buňky - klepnutí na příslušnou buňku. Označení 1 oblasti buněk - tažení myši z levého horního do pravého dolního rohu oblasti. Označení více oblastí buněk - před označováním nesousedící oblasti stiskneme a držíme klávesu Ctrl. Označení řádku - klepnutí na jeho číslo. Označení sloupce - klepnutí na jeho záhlaví. Označení všech buněk - klepnutí na políčko vlevo od záhlaví sloupců. Kromě buněk můžeme rovněž označit více listů v sešitu - stiskem Ctrl a klepnutím na záložky příslušných listů. V tomto případě můžeme např. formátovat buňky a oblasti v několika listech najednou, zadávat společné údaje apod. Označení vybraných listů zrušíme klepnutím na libovolnou záložku.

4.3 Úpravy v tabulce Opravy v buňce provádíme poklepáním myší na buňku, přepsáním údaje a potvrzením klávesou Enter. Pro úpravy v buňce můžeme také využívat běžných možností Windows, které poskytuje práce se schránkou - v menu Úpravy. Kopírování nebo přesun buněk můžeme provádět také přímo pomocí myši, když v menu Nástroje / Možnosti / Úpravy je zaškrtnuto políčko Povolit přetahování buněk myší. Přesun buněk na nové umístění provádíme pak pouhým přetažením myši, pro kopírování držíme stisknutou klávesu Ctrl. Varování: Umístíme-li kopii na místo neprázdných buněk, budou staré buňky nahrazeny novými! Chceme-li vložit přesouvané buňky mezi stávající, stiskneme Shift, u kopírovaných buněk Ctrl Shift. U takto kopírovaných nebo přesunutých buněk dochází ke změně tzv. relativních odkazů (viz níže). Při všech těchto akcích můžeme rovněž využít místní nabídky. Buňky zkopírované do schránky můžeme vkládat do tabulky rovněž příkazem Úpravy / Vložit jinak, který umožňuje vložit na nové místo např. pouze formáty, hodnoty, vzorce nebo poznámky; nebo provést určitou matematickou operaci vůči hodnotám buněk, které leží ve vkládané oblasti. Políčko Transponovat umožní převést buňky v řádcích do sloupců a naopak. Políčko Vynechat prázdné umožní vložit pouze buňky s určitou hodnotou.

3

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem Vkládání řádků nebo sloupců. Program Excel odsune okolní řádky nebo sloupce tak, aby vzniklo místo pro nově vložený řádek nebo sloupec. Označíme počet sloupců nebo řádků, který chceme vložit a použijeme místní nabídku Vložit. Vložené řádky budou umístěny nahoře od označených řádků, vložené sloupce vlevo od označených sloupců. Do tabulky můžeme také vložit prázdné buňky, když použijeme místního menu Vložit / Buňky. Odstranění a vymazání buněk. Odstraněním buněk tyto buňky z listu úplně zmizí, při vymazání buňky v listu zůstávají, ale zmizí buď jejich formát, nebo jejich hodnota, anebo obojí. Pro odstranění buněk zadáváme příkaz Odstranit v menu Úpravy. Vymazání obsahu označených buněk provedeme stiskem klávesy Del. Vyhledávání a záměnu textu, čísel nebo buněk provádíme přes menu Úpravy příkazem Nahradit nebo Najít. Pomocí menu Nástroje / Pravopis můžeme provést kontrolu pravopisu textů v listu i grafech.

4.4 Vkládání údajů do buněk Údaje do tabulky vkládáme vždy do aktivní buňky, zadaný údaj potvrzujeme klávesou Enter nebo použijeme kurzorových kláves, pomocí nichž potvrdíme zadaný údaj a přesuneme se na další buňku. Chceme-li již zadaný údaj přepsat nebo doplnit, označíme zvolenou buňku myší a stiskneme F2. Do tabulky můžeme vkládat tyto typy dat: • Text. Textem je libovolná kombinace písmen, číslic a jiných znaků. Za text je považována každá posloupnost znaků zadaných do buňky, kterou program nepochopí jako číslo, vzorec, datum, čas, logickou hodnotu nebo chybovou hodnotu. Při zadání textu se znaky zarovnají k levému okraji buňky. Text může být delší než samotná šířka buňky. • Číslo. Čísla můžeme zapisovat v běžném tvaru (např. 15; 123,14; -18,26) nebo v tzv. semilogaritmickém tvaru (např. 14,26E08, -15,002E-23 atd.). Při zadávání čísel se vyhýbáme oddělování řádů mezerami (tedy místo 1 000 000 zapisovat 1000000), v některých tabulkových procesorech je takto mezerami dělené číslo považováno za text! Konečná podoba čísla může být určena formátem, který je součástí stylu buňky. Standardně mají buňky všeobecný číselný formát, v němž se čísla zobrazují co nejpřesněji. Zadáme-li za číslo symbol měny (např. Kč) nebo znak pro procenta (%), program buňce automaticky přiřadí formát "měna" nebo "procenta". Čísla se při zadávání zarovnávají k pravému okraji buňky. Přesahuje-li číslo šířku buňky, program místo něj zobrazí řetězec znaků "#" (např. ########). V tomto případě je potřeba buňku rozšířit. • Datum a čas. Datum a čas lze zobrazit v několika standardních formátech, ale program Excel ukládá datum vždy jako pořadové číslo dne a čas jako desetinné číslo menší než 1. Datum a čas jsou chápány jako čísla, takže je lze sčítat, odčítat a používat ve výpočtech. Datum nebo čas lze zobrazit jako pořadové číslo nebo desetinný zlomek změnou číselného formátu buňky na "všeobecný". Při zápisu data oddělujeme jednotlivé části tečkou (např. 20.6.1996), při zápisu času používáme dvojtečky (např. 15:48:32). Datum i čas můžeme zapsat do jedné buňky, ale musíme je oddělit mezerou (např. 20.6.1996 15:48:32). • Logické a chybové hodnoty. Logické hodnoty jsou obvykle výsledkem vzorců obsahujících logickou funkci nebo rovnici. Platné logické hodnoty jsou PRAVDA a NEPRAVDA. Chybová hodnota se zobrazí v případě, že zadaný vzorec nelze vypočítat. Chybové hodnoty začínají znakem #. • Vzorce. Začínají symbolem "=" a zobrazují výslednou hodnotu (viz dále).

4.5 Vytváření posloupných řad Při zadávání údajů můžeme využít možností, které Excel poskytuje pro vyplňování sousedících buněk a vytváření posloupných řad. Chceme-li kopírovat obsah jedné buňky do určité oblasti, označíme buňku, uchopíme ji za pravý dolní výplňový úchyt a táhneme požadovaným směrem. (Alternativně můžeme také dopředu označit oblast, zadat do jedné z buněk požadovaná data a použít příkazu z menu Úpravy / Vyplnit). Poklepáním na výplňový úchyt se rozšíří označená oblast z aktivní buňky až po řádku na konci sousední oblasti a do této oblasti se doplní data. Pro vyplnění určité oblasti údaji, které mají tvořit určitou posloupnost (řadu), např. leden, únor, březen nebo 1,2,3 postupujeme obdobně, pouze stiskneme podle potřeby Ctrl (Excel automaticky tvoří řadu např. v případě měsíců - chceme-li tvoření řady zabránit a použít prostého kopírování buněk, stiskneme rovněž Ctrl). Pro tvoření klesajících řad musíme táhnout výplňovým úchytem v opačném směru (tj. buď nahoru nebo doprava).

4

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem Stiskneme-li při táhnutí výplňovým úchytem pravé tlačítko myši, objeví se místní nabídka s možnostmi kopírovat do sousedních buněk např. pouze formáty nebo jen hodnoty, případně můžeme použít pro vyplnění buněk i tzv. lineární a rostoucí trend. Pokud používáme často určité vlastní řady dat (např. neměnící se seznamy výrobků), můžeme použít automatického vyplňování - v menu Nástroje / Předvolby / Seznamy vytvoříme a přidáme vlastní uživatelský seznam. Seznam může mít maximálně 2000 položek a jedna položka 80 znaků.

4.6 Zadávání vzorců do tabulky Díky vzorcům můžeme vyhodnocovat a provádět nejrůznější operace s daty v tabulce.Každý vzorec začíná znakem "=". Vzorec může obsahovat: konstantní hodnoty a operátory (např. 12+14,18*21,6/2 atd.). odkazy na jiné buňky, oblasti buněk a seznamy buněk (např. =A5+B8; =suma(A1:B12); =průměr(B2;C3;D8)). funkce (např. ABS - výpočet absolutní hodnoty, SUMA - součet buněk atd.). texty a názvy. Při vkládání vzorce do buňky je možné uvedené prvky kombinovat. Zadávané vzorce se zobrazují v řádku vzorců v horním řádku tabulky. Tento řádek obsahuje odkaz nebo název právě aktivní buňky, rušící tlačítko pro odvolání vstupu zadaného v řádku vzorců a tlačítko pro potvrzení zadání.

4.7 Průvodce funkcí V buňkách se obvykle zobrazují výsledky vzorců. Chceme-li zobrazovat vzorce samotné, musíme v nabídce Nástroje / Předvolby / Volby okna zaškrtnout políčko Vzorce. Operátory - slouží k provedení určité operace mezi prvky vzorce. Dělíme je na: • Aritmetické operátory - provádějí základní matematické operace (+,-,*,/,%,^- umocnění). • Logické operátory - porovnávají dvě hodnoty (=,>,<,>=,<=,<>). • Textové operátory - slouží ke spojení dvou nebo více textových hodnot do jedné (např. "auto"&"mobil" = "automobil"). Priorita operátorů určuje v jakém pořadí budou dané operátory vyhodnocovány. Pro Excel platí tato priorita operátorů (-,%,^,*, /, +, -, &, logické operátory). Pokud vzorec obsahuje operátory se stejnou prioritou, program Excel je vyhodnocuje zleva doprava. Chceme-li pořadí výpočtu změnit, použijeme závorek.

4.8 Odkazy Odkazy umožňují použít hodnotu určité buňky ve vzorci. Typy odkazů: Relativní odkazy - umístění odkazované buňky vzhledem k buňce se vzorcem. Změníme-li polohu vzorce, změní se i odkaz. Absolutní odkazy - zapisujeme $A$1 - odkaz na buňku A1 se nemění ani při změně polohy vzorce. Smíšené odkazy - např. A$1 nebo $A1 - nemění se buď řádek nebo sloupec. Typ odkazu můžeme změnit klávesou F4.

4.9 Odkazové operátory Oblast - dvojtečka - A1:B2 Sjednocení (seznam) - středník -D1;B3;B8:B11 Průnik - mezera - B7:D7 C6:C8 Odkaz na celý sloupec - A:A, na celý řádek 1:1, celé řádky 1:3, celý list A:IV nebo 1:16384 Odkazy na buňky v jiných listech - např. List1!A5 3D odkazy - zahrnují buňky 2 nebo více listů sešitu (např. =Suma(List1:List4!A1:B8) Odkaz je nejsnadnější zadat tak, že zapíšeme "=" a poté ukážeme na buňku či oblast, na níž chceme ve vzorci odkazovat. Zadávání názvů buněk a oblastí usnadní a zpřehlední práci v tabulce - názvy lze zadávat pomocí menu Vložit / Název. Názvy buněk se objevují v orientačním poli. Pojmenovat lze i 3D odkazy. Název nesmí být zaměnitelný s odkazem, prvním znakem musí být písmeno nebo znak podtržení, nejsou povoleny mezery, název může obsahovat maximálně 25 znaků. Pomocí menu Vložit / Název a položky Definovat můžeme pojmenovat vzorec, konstantu nebo celý blok buněk a poté je využít v jiných vzorcích.

5


4.10 Kruhové odkazy Jsou-li 2 hodnoty na sobě závislé, vytvářejí tzv. kruhový odkaz (iterace=opakování). Chceme-li kruhový odkaz použít záměrně, zvolíme menu Nástroje / Možnosti / Výpočty a zaškrtneme políčko Iterace. Příklad kruhového odkazu: A B Hrubý příjem 1000 Čistý příjem =Hrubý příjem-Prémie Prémie =Čistý příjem*10% Varování: Pokud skutečně nepracujeme s iteračními algoritmy, měla by být iterace vždy vypnuta. Jen tak zajistíme, že o cyklickém odkazu, této velice nebezpečné chybě, budeme informováni! Při odhalování možných problémů ve složitějších tabulkách je vhodné využít příkazu Nástroje / Závislosti, jehož jednotlivé panely dovolují zadat grafické zvýraznění vazby mezi buňkami. Vazby se znázorňují šipkami (trasovači): vazby vzorců jsou označeny modrou barvou, chyby červenou a při vazbě na jiný sešit je čára doplněna ikonou tabulky. Šipky ukazují směr podle zadání, zda chceme zvýraznit předchůdce nebo následovníky. Lze tak snadno zjistit buňky na sebe navazující a odhalit tak případnou logickou chybu modelu.

4.11 Funkce Funkce jsou zvláštní případ předem připraveného vzorce, který na základě vstupních hodnot vykoná určitou operaci a vrátí její výsledek. U funkcí musíme dodržovat jejich syntax - tj. zápis podle daných pravidel. Při použití funkcí je výhodné používat Průvodce funkcí, ve kterém si můžeme vybrat funkce podle určité skupiny a poté přepsat názvy argumentů skutečnými hodnotami. Do jedné funkce můžeme postupně vnořit až sedm úrovní funkcí.

4.12 Propojení sešitů Vzorce uložené v buňkách můžeme dynamicky propojit se zdrojovými údaji umístěnými v jiném sešitě. Příklad vzorce s vnějším propojením: ='C:\Prodej\[Excel.XLS]List1'!A8 Upozornění: Při zadávání cesty musíme použít apostrofy!

4.13 Řízení výpočtu Výpočet v tabulce se standardně provádí automaticky. Chceme-li výpočet provést ručně zvolíme menu Nástroje / Možnosti / Výpočty a vybereme volbu Ruční. Hodnoty jsou obvykle ukládány s přesností na 15 číslic (tzv. plná přesnost) bez ohledu na nastavený formát zobrazení. V menu Nástroje / Možnosti / Výpočty lze však nastavit položku Přesnost podle zobrazení, která přizpůsobí výpočet podle zobrazovaných hodnot. Chceme-li některý vzorec nahradit jeho výslednou hodnotou, použijeme menu Úpravy / Vložit jinak a klepneme na tlačítko Hodnoty.

4.14 Práce s poli Pole vzorců lze chápat jako jeden vzorec s více hodnotami. Jedno pole vzorců může zabírat několik buněk. Pole se může skládat až z 6500 prvků. Pole vzorců zapisujeme takto: {=SUMA(B2:D2*B3:D3)}.

4.15 Formátování Formátování tabulky slouží ke konečné úpravě vzhledu tabulky, přičemž se nemění její obsah. Změnu formátu provádíme pomocí menu Formát nebo místní nabídky. Nejpoužívanější úpravy lze provést také pomocí tlačítek v Panelu nástrojů. Před změnami formátů je nutné označit si buňku či buňky, jichž se mají změny týkat. Volby menu Formát: Změna šířky sloupců a výšky řádků. Nejsnadněji se provádí tažením myší pravého okraje záhlaví sloupce nebo spodního okraje záhlaví řádku. Změnu velikosti sloupce i řádku můžeme provést také zadáním čísel v menu Formát / Sloupec respektive Formát / Řádek.

6

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem Příkaz Přizpůsobit umožňuje automatické nastavení šířky či výšky buněk podle nejdelší položky. Nastavujeme zde rovněž případné skrytí celých řádků a sloupců (skryté sloupce a řádky se netisknou!). Pozn. Zakreslování skrytých sloupců a řádků do grafu se určuje v menu Nástroje / Možnosti a kartě Graf označením položky Kreslit jen viditelné buňky. Formát / List. Můžeme přejmenovat nebo skrýt list, případně nastavit obrázek do jeho pozadí. Formát / Buňky / Číslo. Vybíráme nejprve druh formátu podle charakteru dat v zarovnávaných buňkách (např. číslo, měna, datum, text apod.) a poté upřesníme typ formátu nabízených v pravém seznamu. Kromě předloženého výběru můžeme vytvářet také vlastní formáty (druh Vlastní viz kapitola „Vlastní číselné formáty“). Formát / Buňky / Zarovnání. Obsah buňky můžeme zarovnat ve vodorovném i svislém směru, případně nastavit úhel, pod nímž bude text v buňce zarovnáván. Speciálními případy jsou zarovnání obsahu buňky vyplněním (obsah buňky je opakovaně vkládán na celou šířku buňky), zarovnání do bloku (u víceslovný textu budou roztaženy mezery a bude zarovnán od levého k pravému okraji buňky), zarovnání na střed výběru (text v první označené buňce bude zarovnán do středu bloku vhodné pro nadpisy), zalomení dlouhého textu (tak, aby se vešel do šířky sloupce - bude rozdělen do více řádků), sloučení označených buněk nebo zarovnání s nastaveným odsazením. Formát / Buňky / Písmo. Běžný dialogový panel s možnostmi pro nastavení písma. Formát / Buňky / Ohraničení. Určujeme způsob ohraničení buněk, typ a barvu hraniční čáry. Formát / Buňky / Vzorky. Nastavujeme barvu, případně typ vzorku pro označené buňky. Formát / Buňky / Zámek. Můžeme uzamknout buňky a zabránit tak nežádoucím zásahům. Implicitně jsou všechny buňky uzamčené, zámek se však aktivuje až po volbě příkazu Nástroje /Zámek / Zamknout list. Formáty buněk můžeme kopírovat na jiné buňky tak, že klepneme na vzorovou buňku, poté na tlačítko Kopie formátu a tažením označíme oblast, v níž chceme daný formát aplikovat. Konečná úprava tabulky může spočívat ve skrytí mřížky - provedeme v menu Nástroje / Předvolby / Zobrazení.

4.16 Vlastní číselné formáty Zobrazení textu i čísel: Jestliže chceme v buňce zároveň zobrazit text a čísla, uzavřeme text do uvozovek (" ") nebo před znaky umístíme zpětné lomítko (\). Znaky zadáme do odpovídající části kódu. Zadáme-li například formát 0,00 Kč" přebytek";-0,00 Kč" nedostatek", zobrazí se kladná částka jako řetězec „125,74 Kč přebytek“ a záporná částka jako řetězec „-125,74 Kč nedostatek“. Následující znaky nemusí být uzavřeny v uvozovkách: Kč - + / ( ) : ! ^ & ' (jednoduchá levá uvozovka) ' (jednoduchá pravá uvozovka) ~ { } = < > a znak mezery. Zahrnutí textové části: Textová část, pokud je zahrnuta, je vždy poslední částí kódu číselného formátu. Do textové části zadejte znak @ v místě, ve kterém chcete zobrazit text zadaný v buňce. Pokud v textové části vynecháte znak @, zadaný text nebude zobrazen. Jestliže chcete určité znaky zobrazit se zadaným textem pokaždé, napište tyto znaky do uvozovek (" "), například "hrubé příjmy "@. Pokud formát neobsahuje textovou část, není zadaný text tímto formátem ovlivněn. Přidání mezery: Mezeru o šířce jednoho znaku vytvoříte v číselném formátu pomocí znaku podtržení (_) následovaného daným znakem. Jestliže například zadáte za znakem podtržení pravou závorku ( _) ), budou kladná čísla zarovnána se zápornými čísly uzavřenými v závorkách. Opakování znaků: Zadáním hvězdičky (*) do formátu čísla se následující znak opakuje, dokud nezaplní celou šířku sloupce. Pokud například zadáme formát 0*-, budou za číslem až do vyplnění buňky opakovány pomlčky.

4.16.1 Desetinná místa, mezery, barvy a podmínky zástupných symbolů číslic do příslušné části kódu. Pokud číslo obsahuje vpravo od desetinné čárky více číslic, než je ve formátu zástupných symbolů, bude zaokrouhleno na definovaný počet míst. Jestliže číslo obsahuje více číslic vlevo od desetinné čárky, bude zobrazeno celé. Obsahuje-li formát vlevo od desetinné čárky pouze znaky #, budou čísla menší než jedna začínat desetinnou čárkou. Symbol # zobrazuje pouze platné číslice, nezobrazuje nevýznamné nuly. Symbol 0 (nula) zobrazuje nevýznamné nuly v případě, že má číslo méně desetinných míst, než je ve formátu nul. Symbol ? přidá na obou stranách desetinné čárky místo nadbytečných nul mezery, takže pokud je u údajů použito písmo s pevnou šířkou znaků, například Courier New CE, budou zarovnány desetinné čárky. Tento symbol můžete také použít pro zlomky s proměnlivým počtem číslic.

7


Zobrazený formát

Použitý kód

1234,59 jako 1234,6

####,#

8,9 jako 8,900

#,000

0,631 jako 0,6

0,#

12 jako 12,0 a 1234,568 jako 1234,57

#,0#

44,398, 102,65 a 2,8 se zarovnanými desetinnými čárkami

???,???

5,25 jako 5 1/4 a 5,3 jako 5 3/10 se zarovnanými symboly dělení #" "???/??? Oddělovač tisíců: Pokud chceme zobrazit mezeru jako oddělovač tisíců nebo zobrazit číslo jako násobek tisíců, použijte ve formátu čísla mezeru. Zobrazený formát

Použitý kód

12000 jako 12 000

# ###

12000 jako 12

# (následovaný mezerou)

12200000 jako 12,2

0,0 (následovaný dvěma mezerami)

Barvy: Pokud chceme části formátu barevně odlišit, zadejte do dané části název jedné z osmi následujících barev v hranatých závorkách. Kód barvy musí být první položkou v příslušné části kódu. [Černé]

[Modré]

[Azurové]

[Zelené]

[Purpurové]

[Červené]

[Bílé]

[Žluté]

Podmínky: V případě, že chceme nastavit číselný formát, který bude použit pouze pokud bude dané číslo splňovat požadované podmínky, zadejte podmínku do hranatých závorek. Podmínka obsahuje relační operátor (Relační operátor: Znak sloužící v porovnávacích kritériích k porovnání dvou hodnot. Mezi operátory patří: = Rovná se, > Větší než, < Menší než, >= Větší než nebo rovno, <= Menší než nebo rovno, <> Nerovná se.) a hodnotu. Následující formát zobrazí například čísla menší než nebo rovná 100 červeně a čísla větší než 100 modře. [Červené][<=100];[Modré][>100]. Chceme-li u buněk použít podmíněné formáty (Podmíněný formát: Formát, například stínování nebo barva písma, který je v aplikaci Excel automaticky použit u buněk, u kterých je splněna určená podmínka.), například barevné stínování v závislosti na hodnotách buněk, použijte příkaz Podmíněné formátování v nabídce Formát.

4.16.2 Měna, procenta a matematický zápis Symboly měny: Chceme-li zadat jeden z následujících symbolů měn do číselného formátu, zapneme klávesu NUM LOCK a pomocí číselné klávesnice napište kód ANSI požadovaného symbolu. První tři z následujících symbolů měn lze zadat pouze v případě, že přepneme z české klávesnice na anglickou. Symbol měny

Podržte klávesu ALT a zadejte kód

¢

0162

£

0163

8


¥

0165

euro

0128

Komentář: Vlastní formáty jsou ukládány společně se sešitem. Pokud chceme, aby se v aplikaci Microsoft Excel vždy zobrazoval určitý symbol měny, změníme před spuštěním aplikace vybraný symbol měny v Ovládacích panelech Windows na panelu Místní nastavení. Procenta: Chceme-li zobrazit čísla v procentech, použijeme ve formátu symbol procent (%). Číslo 0,08 bude například zobrazeno jako 8% a číslo 2,8 jako 280%. Matematický zápis: Čísla v matematickém formátu zobrazíte pomocí kódů exponentu E-, E+, e- nebo e+. Jestliže formát obsahuje znak 0 (nula) nebo # (symbol čísla) napravo od kódu exponentu, bude v aplikaci Excel zobrazeno číslo v matematickém formátu a vložen znak E nebo e. Počet nul nebo znaků # napravo od kódu určuje počet číslic exponentu. Pomocí znaku E- nebo e- vložíme znaménko minus u záporných exponentů. Pomocí znaku E+ nebo e+ vložíte znaménko minus u záporných exponentů a znaménko plus u kladných exponentů.

4.16.3 Kalendářní data a časové údaje Dny, měsíce a roky: Jestliže použijeme kód m bezprostředně po kódu h nebo hh nebo před kódem ss, zobrazí se v aplikaci Microsoft Excel minuty místo měsíců. Zobrazený formát

Použitý kód

Měsíce jako čísla od 1 do 12

m

Měsíce jako čísla od 01 do 12

mm

Měsíce jako římské číslice od I do XII

mmm

Měsíce jako text leden-prosinec

mmmm

Měsíce jako první písmeno názvu měsíce

mmmmm

Dny jako čísla od 1 do 31

d

Dny jako čísla od 01 do 31

dd

Dny jako zkratky názvů dnů po-ne

ddd

Dny jako text pondělí-neděle

dddd

Roky jako čísla od 00 do 99

rr

Roky jako čísla od 1900 do 9999

rrrr

Hodiny, minuty a sekundy Zobrazený formát

Použitý kód

Hodiny jako čísla od 0 do 23

H

Hodiny jako čísla od 00 do 23

hh

Minuty jako čísla od 0 do 59

m

Minuty jako čísla od 00 do 59

mm

Sekundy jako čísla od 0 do 59

s

Sekundy jako čísla od 00 do 59

ss

Hodiny ve formátu 4 dop.

h dop./odp.

9


Čas ve formátu 4:36 odp.

h:mm dop./odp.

Čas ve formátu 4:36:03 dop.

h:mm:ss dop./odp.

Uplynulý čas v hodinách, například 25.02

[h]:mm

Uplynulý čas v minutách, například 63:46

[mm]:ss

Uplynulý čas v sekundách

[ss]

Zlomky sekundy

h:mm:ss.00

Dop. a odp.: Pokud formát obsahuje zkratky dop. nebo odp., je zobrazení času založeno na dvanáctihodinovém systému, kde kódy DOP, dop., D nebo d označují dobu od půlnoci do poledne a kódy ODP, odp., O nebo o označují dobu od poledne do půlnoci. V ostatních případech je zobrazení času založeno na čtyřiadvacetihodinovém systému. Kód m nebo mm musí následovat bezprostředně po kódu h nebo hh nebo bezprostředně před kódem ss. V opačném případě se zobrazí měsíce místo minut.

4.17 Automatické formáty Umožňují rychlé formátování tabulek podle předem definovaných formátů. Označíme upravovanou oblast, vyvoláme menu Formát / Automatický formát a vybereme z nabízených předdefinovaných formátů. Pro využití automatických formátů musíme nejprve označit požadovanou buňku nebo oblast a poté zvolit menu Formát / Automatický formát. V seznamu Formát tabulky označíme vyhovující formát. Chceme-li později automatický formát zrušit, zvolíme ve stejném menu ze seznamu Formát tabulky první položku - "žádný".

4.18 Styly Slouží k uložení formátu buněk a jejich pozdějšímu použití v jiných buňkách nebo oblastech. Pro vytvoření nového stylu využijeme menu Formát / Styl, kde nejprve dáme novému stylu název a poté nastavíme požadované formáty. Uložené styly pak nejsnadněji použijeme označením buněk a volbou stylu v nástrojovém panelu. Chceme-li styly nastavené v jednom sešitu využívat i sešitu jiném, využijeme menu Formát / Styl, klepneme na tlačítko Sloučit a označíme sešit, z něhož hodláme styly kopírovat. Definice stylu můžeme přes stejné menu libovolně měnit, čímž budou změněny všechny buňky formátované daným stylem. Styl je možné rovněž odstranit, buňky, který tento styl používaly, se navrátí k Normálnímu stylu.

4.19 Podmíněné formátování Podmíněným formátováním zlepšujeme přehlednost tabulky, neboť nám umožňuje zvýraznit data, kterým máme věnovat zvýšenou pozornost (např. červené zobrazení záporných čísel v účetnictví). V panelu Podmíněné formátování (příkaz Formát / Podmíněné formátování) můžeme pro konkrétní buňku nastavit až tři podmínky (např. Hodnota buňky je menší než 0) a doplnit je nastavením formátu použitého při splnění podmínky (nastavujeme písmo, ohraničení a vzorky). Tlačítkem Vymazat vracíme formát vždy do původního stavu.

5 Grafy v Excelu Pro prezentaci dat, hlavně však pro jejich rozbory, jsou většinou pro svoji malou názornost tabulky nedostačující, a proto je vhodné je doplnit grafem. Graf je vytvářen na základě zvolených zdrojových dat a automaticky reaguje na jejich změny. Můžeme volit mezi dvojrozměrnými nebo trojrozměrnými typy grafů (třetí rozměr, hloubku, tvoří počet datových řad) podle charakteru dat. Graf může být samostatný nebo s připojenými daty pod grafem. Do grafu můžeme nechat doplnit trendy, klouzavý průměr a chybové úsečky. V Excelu se graf vytváří pomocí Průvodce grafem, který v postupných krocích uživateli nabízí 4 dialogové panely pro nastavení příslušných hodnot.

10


5.1 Postup při tvorbě grafu Označení oblasti tabulky, kterou chceme do grafu zakreslit. Může být vymezena i nespojitá oblast (klávesou Ctrl), datové řady však musí být stejně dlouhé. Oblast by měla zahrnovat i nadpisy sloupců a řádků, které budou použity pro popis os a legendu. Klepneme na ikonu Průvodce grafem ve standardním panelu nástrojů, případně zadáme příkaz Vložit / Graf. Zobrazí se první panel Průvodce grafem, z něhož vybíráme typ grafu. Vedle standardních typů můžeme definovat také vlastní typy a využívat je jako šablony grafů. Jeden typ je definován jako výchozí: podle něj se vytvoří graf automaticky po stisku klávesy F11. V dalším dialogovém panelu upřesňujeme Zdrojová data grafu. Na první kartě je vymezena oblast dat grafu a můžeme zde rovněž určit, zda datové řady tvoří řádky nebo sloupce. Oblast dat můžeme definovat buď zadáním vzorce nebo tažením myši přímo v tabulce. Na druhé kartě, řada, se nám po výběru řady v políčkách Název a Hodnoty zobrazí oblasti náležející příslušné datové řadě. Na třetím panelu Možnosti grafu nastavujeme parametry vytvářeného grafu: Názvy (název grafu a označení os), Osy, Mřížky, Legenda, Popisky dat a Tabulka dat. V posledním kroku rozhodujeme, kde má být graf umístěn. Můžeme jej vložit buď na samostatný list nebo jako objekt do některé z tabulek. Graf vložený k datům má charakter plovoucího objektu, je možno s ním tak jako s jakýmkoli jiným objektem pohybovat, měnit jeho rozměry, kopírovat, mazat apod. Graf umístěný na listu s daty můžeme tisknout samostatně nebo spolu s tabulkou. Graf v samostatném okně může být zobrazen ve dvou provedeních: v běžné velikosti (automatická velikost, dodržují se proporce) nebo ve velikosti podle okna, kdy graf vždy zaplní celé okno (nejsou dodrženy proporce). Tato dvě základní provedení mohou být ve velikosti dokumentového okna nebo na celou plochu obrazovky. Graf v samostatném okně je vhodný mimo vlastního tisku též při projekci, k prezentační grafice.

5.2 Výběr typu grafu Při výběru vhodného typu grafu se řídíme především charakterem dat a požadovaným znázorněním. Co chceme znázornit Vhodný typ grafu průběh změny v čase spojnicový, sloupcový, plošný, pruhový stav (hodnoty) v daném bodě sloupcový, pruhový výsečový, prstencový, procentní plošný, relativní podíly na celku procentní sloupcový, procentní pruhový skládaný plošný, skládaný sloupcový, relativní vzájemné podíly skládaný pruhový, paprskový, bublinový vztahy mezi proměnnými XY bodový, sloupcový toleranční pole burzovní symetrie údajů paprskový symetrie kolem počátku sloupcový, pruhový rozložený výsečový 3D, válcový, kuželový, jehlanový a další 3D grafy kombinované (několik grafů v jednom) doplněné obrázky na pozadí nebo jako datové body zachycení tří proměnných bublinový, povrchový dvě sk. dat obdobné velikosti graf s vedlejší osou Y, výsečový s dílčí výsečí nebo dílčími pruhy V grafech, které zobrazují některou hodnotu v čase jsou kategoriemi vždy časové intervaly: dny, týdny, apod. Nemůžete-li rozlišit datové řady od kategorií, zkuste si jedním slovem odpovědět na otázku "Co data zobrazují?". Vaše odpověď bude téměř jistě popisovat datovou řadu. 3D grafy jsou mnohdy názornější a působivější než 2D grafy. Mají však svá úskalí. Např. výsečový graf se nám zobrazí jako elipsa a dále při nevhodně zvolené posloupnosti řad může dojít k jejich vzájemnému překrytí a graf se stane nečitelným. Bývá nejlepší jako první zvolit datovou řadu s nejmenšími hodnotami, aby nedošlo k zakrytí sloupců stojících za nimi. efektní pro prezentaci

11


Někdy si můžeme pomoci úpravou elevate, natočení grafu a hloubky perspektivy. Všechny 3D grafy můžeme pozorovat z libovolné perspektivy.

5.3 Speciální možnosti Graf s druhou osou Y umožňuje zakreslit do jednoho grafu průběhy, které jsou řádově odlišné (např. náklady a počty pracovníků, součet a jednotlivé složky atd. v závislosti na čase - např. vývoj po měsíci). Při vytváření tohoto typu grafu můžeme použít Průvodce grafu, kde jsou jako vlastní typy uvedeny dva grafy s druhou osou Y (spojnicový + sloupcový 2 osy a spojnicový - 2 osy). Druhou osu můžeme vytvořit také dodatečně v již hotovém grafu, když po označení datové řady, kterou chceme mít v měřítku druhé osy Y, vybereme v místní nabídce příkaz Formát datové řady. Zvolíme kartu Osa a na ní označíme políčko řady - na vedlejší ose. Tento postup můžeme opakovat pro všechny řady, které mají být do grafu zakresleny v měřítku druhé osy Y. Kombinované grafy pomáhají analýze dat, kdy je pro přehlednost v jednom grafu vhodné vykreslit dva typy grafů (např. spojnicový + plošný). Podle potřeby je možné využít již připravené kombinované typy grafů v Průvodci grafem nebo si vyrobit vlastní kombinace grafů. Ve druhém případě označíme v již hotovém grafu řadu, u níž požadujeme změnit typ grafu a zadáme příkaz Graf / Typ grafu. Varování: Nelze kombinovat 3D grafy, ani vzájemně 3D a 2D grafy!

5.3.1 Vytváření trendů a klouzavých průměrů Do grafu lze doplnit tzv. trendy a klouzavé průměry, které umožňují analyzovat data a pomoci v odhadu budoucího vývoje nebo (v případě klouzavého průměru) vyhladit křivku grafu, který obsahuje příliš rozkolísané údaje. Tyto křivky můžeme doplnit do grafu sloupcového, pruhového, spojnicového, XY bodového, bublinového a prvního podtypu plošného grafu. Nelze je použít u žádného z 3D grafů! U trendů i klouzavých průměrů je většinou třeba experimentovat a hledat takový průběh, který daný děj popisuje co nejvěrněji. Trendy a klouzavé průměry přidáme do grafu volbou příkazu Graf / Přidat spojnici trendu. Na první kartě určujeme typ trendu (např. lineární, mocninný, polynomický, logaritmický apod.), na kartě Možnosti pak trend pojmenujeme, určíme počet period pro odhad, průsečík s osou Y, zda se má rovnice, podle které se trend vykreslil zobrazit a zda se má vypsat odhadovaná spolehlivost. Pro určený počet period je možné graf doplnit o retroperspektivu (tj. pohled na předpokládanou minulost děje) nebo perspektivu (odhadovaná budoucnost děje).

12


5.3.2 Chybové úsečky Chybové úsečky používáme, jde-li nám o zjištění, jak se některé údaje liší od požadovaného průběhu. Vyjadřují graficky možné hodnoty chyby ke každému datovému bodu řady. Mohou nám pomoci při rozboru dat, kdy vidíme, která data překročila zadané hranice. Postup při zakreslení chybových úseček: Označíme řadu, ke které mají být chybové úsečky přidány. Zadáme příkaz Formát / Vybraná datová řada nebo v místní nabídce Formát datové řady. Na panelu Formát datové řady vybereme kartu Chybové úsečky Y, respektive u grafu XY bodového Chybové úsečky X. Doplníme parametry - co mají chybové úsečky vyjadřovat a potvrdíme nastavení tlačítkem OK.

5.4 Objekty grafu Každý graf v Excelu se skládá ze samostatných objektů. Objekty můžeme označit klepnutím kurzoru myši nebo výběrem objektu v panelu nástrojů Graf. S označeným objektem můžeme dále pracovat přesouvat, měnit velikost, mazat, kopírovat.

5.5 Formátování grafu Všechny objekty grafu můžeme formátovat podle našich požadavků. Formátování můžeme provádět dvěma způsoby: Souhrnně. Měníme všechny parametry zadané Průvodcem grafu (viz příkazy v menu Graf: Typ grafu, Zdrojová data, Možnosti grafu, Umístění). Detailně. Formátujeme jednotlivé objekty grafu. Nejrychlejší přístup k formátování určitého objektu získáme jeho označením a vyvoláním místní nabídky (kliknutím pravým tlačítkem myši). Zde najdeme příkazy, které umožňují nastavení formátu.

6 Seznamy v Excelu Seznamy jsou informace uspořádané do polí a záznamů a jsou tedy analogické databázovým tabulkám, které používají běžné databázové systémy jako dBASE, Paradox, Fox Pro a Access. Oproti databázovým systémům však Excel neumožňuje vytvářet relační databáze, kdy lze pracovat s více soubory najednou tak, že související informace je vyhledávána prostřednictvím společného pole. Další rozdíl je v tom, že databázové soubory mají větší rozsah typů polí; mohou např. obsahovat i tzv. memo položky (text neomezené délky) nebo OLE objekty (obrázky, zvuky, klipy apod.). Seznam v Excelu má, stejně jako databáze, v prvním řádku záhlaví sloupců (názvy polí) a další řádky jsou potom položky seznamu (záznamy databáze). V každém sloupci seznamu jsou data stejného typu (formátu). Mohou to být čísla, texty, datum, logické hodnoty nebo výrazy (respektive funkce), které vrací některý z předcházejících typů pole. Excel umožňuje přímý export seznamu do databázového souboru (příkaz Soubor / Uložit jako) a přímý import databázového souboru (příkaz Soubor / Otevřít). Do Excelu lze importovat také z textových souborů, které mohou některé databázové systémy vytvářet. Průvodce převodem textu do sloupců vyvoláme příkazem Data / Text do sloupců. Pro vytváření seznamů v Excelu platí následující zásady: Seznam nemusí začínat na prvním listu a ani na první buňce. Záhlaví seznamu musí být jednořádkové, zalomení textu v buňce nevadí. Názvy polí se přebírají z prvního řádku seznamu. Mezi seznamem a dalšími daty na listu musí být alespoň jeden prázdný sloupec nebo řádek. Seznam nemůže obsahovat prázdný záznam, ten by seznam rozdělil na dva seznamy. Na listu může být více seznamů, ale musí být odděleny minimálně jedním prázdným řádkem nebo sloupcem. Vhodnější je vytvářet další seznam na samostatném listu. Bude-li se používat filtrování dat, není vhodné umísťovat data po pravé ani levé straně seznamu, neboť dojde k zakrytí řádků. V seznamu lze použít různé fonty, jejich barva ani barva pozadí buňky nemá na práci se seznamem vliv. V jednom sloupci musí být data shodného formátu. Názvy polí mohou být v seznamu shodné, v odkazech se potom nabídne rozlišení doplňkovou číslicí, např. Cena1, Cena2 apod. Tabulka může být pro zvýšení přehlednosti ohraničena čarami. Pod posledním záznamem bychom však čáru dělat neměli, pokud budeme přidávat záznamy příkazem Data / Formulář.

13


6.1 Seřazení seznamu Seřazení (sort = řazení, třídění) je přeorganizování seznamu nebo jakékoli jiné tabulky podle klíčů. Klíč představuje vybrané pole seznamu, podle kterého se na základě zvoleného kritéria záznamy v seznamu přemístí. Seznam lze při jednom zadání seřadit nejvýše podle tří klíčů. Seznam v Excelu můžeme seřadit podle sloupců nebo podle řádků, vzestupně či sestupně, případně podle předdefinovaných seznamů (např. dnů v týdnu, názvů měsíců apod.). Varování: Nelze seřadit nesouvislou oblast záznamů! Postup při seřazení seznamu: Umístíme buňkový kurzor do prostoru seznamu, nejlépe do pole, podle kterého budeme chtít seznam seřadit (stane se prvním klíčem). Excel sám vybere seznam. Pouze v komplikovanějších případech (např. záhlaví představuje více řádků) je vhodnější, aby uživatel označil sám oblast seznamu. Řazení se zadává příkazem Data / Seřadit. Po zadání příkazu se zvýrazní seřazovaná oblast a zobrazí se dialogový panel, kde zadáváme názvy polí, které mají sloužit jako klíč a požadovaný způsob řazení (vzestupně nebo sestupně). Tlačítkem Možnosti vyvoláme panel, v němž můžeme dále upřesnit nastavení řazení (např. rozlišení velkých a malých písem nebo orientaci řazení). Pokud seznam vyhovuje automatickému označení oblasti (má jednořádkové záhlaví a data uspořádaná do sloupců), můžeme využít i tlačítka ve Standardním panelu nástrojů pro automatické řazení. V tomto případě bude seznam uspořádán podle sloupce, ve kterém je právě umístěn buňkový kurzor. Upozornění: Zatímco data ve skrytých sloupcích mohou být řazena stejně jako ostatní pole záznamu, skryté řádky (tedy jednotlivé záznamy) seřazení nepodléhají a po zobrazení je nalezneme na jejich původním místě! řazení nelze aplikovat na seznam, ve kterém byly vytvořeny souhrny (ty budou před provedením řazení nenávratně odstraněny).

6.2 Prohlížení a opravy seznamu Příkazem Data / Formulář můžeme prohlížet a opravovat jednotlivé záznamy v seznamu. Tlačítkem Nový můžeme přidat do seznamu nový záznam, tlačítkem Odstranit zase záznam vymazat (bez možnosti obnovy). Stiskem tlačítka Kritéria se otevře dialog, v němž můžeme zadat kritéria, podle nichž se budou zobrazovat záznamy. Varování: V dialogovém okně prohlížeče může být zobrazeno maximálně 32 polí: pokud je seznam složen z většího počtu polí, prohlížeč nebude spuštěn!

6.3 Filtrování dat Filtrace umožňuje zobrazit jen požadovaná data bez rušivých vlivů ostatních záznamů.Oproti třídění nedochází k přemístění záznamů.Filtrovat lze jen záznamy (řádky) podle polí (sloupců) a nelze tedy filtrovat pole podle záznamů. Filtr se zpravidla provádí přímo v daném seznamu. Z vyfiltrovaných záznamů můžeme vykreslit graf, ale po zrušení filtru nebo zadání jiných podmínek se musí zadat nové vykreslení grafu. Upozornění: Při filtrování se skrývají celé řádky listu: není proto vhodné umisťovat další data po stranách seznamu, protože může dojít k jejich skrytí. Excel nabízí dva způsoby filtrace dat: automatický filtr a rozšířený filtr.

6.4 Automatický filtr Zadáváme příkazem Data / Filtr / Automatický filtr. Varování: Opětné zadání příkazu ruší dříve nastavené filtry! U každého názvu pole se po pravé straně doplní tlačítko se šipkou, po jehož stisknutí myší se zobrazí setříděný seznam položek pole. Po klepnutí myší na některé položce seznamu se zobrazí jen záznamy pro označenou položku, ostatní záznamy jsou skryty. Ve stavovém řádku je uvedena statistika, kolik záznamů má celý seznam a kolik jich bylo vybráno. U použitého filtru se změní barva šipky ze šedé na modrou. Rovněž tak čísla řádků u vybraných záznamů jsou nyní v modré barvě. Po prvním výběru máme možnost označit myší další tlačítko a v seznamu vybrat další položku - zobrazí se jen ty položky, které zbyly z předchozího výběru.

14

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem V seznamu Automatického filtru nalezneme také následujících 5 položek: vše - zobrazí se všechny záznamy, rušíme tak nastavený filtr prvních 10 - lze použít pouze u číselných nebo datových položek, vybíráme prvních nebo posledních n položek vlastní - můžeme zadat vlastní kritéria pro filtraci, případně je navzájem propojit logickými operátory "a" či "nebo" prázdné - zobrazí se všechny záznamy, které toto pole nemají vyplněné neprázdné - zobrazí se všechny záznamy, které toto pole vyplněné mají

6.5 Rozšířený filtr Zadáváme příkazem Data / Filtr / Rozšířený filtr Příkaz předpokládá, že je vytvořena oblast kritérií (podmínek), což jsou zvláštním způsobem uspořádané buňky v tabulce. V tomto případě můžeme nastavit výstup filtrace i na jiný list sešitu zaškrtnutím položky Kopírovat jinam a správným zadáním oblasti jiného listu v položce Kopírovat do.

6.6 Vytváření souhrnů Příkazem Data / Souhrny vložíme do seřazeného seznamu mezivýsledky za skupiny záznamů a výsledek za celý seznam. Tyto mezivýsledky se automaticky aktualizují. Skupiny záznamů jsou reprezentovány tlačítky a jejich stiskem můžeme zobrazit seznam v potřebné míře podrobnosti. Souhrny využijeme např. pro zjištění souhrnných údajů o prodeji za jeden den. V zobrazeném dialogovém panelu se jako první parametr musí určit klíčové pole, podle kterého se mají souhrny vytvářet. Druhým parametrem je funkce, která se má pro vytváření souhrnů použít (např. součet, počet hodnot, průměr apod.). Třetím parametrem jsou pole, na které má být funkce aplikována. Pro jedno klíčové pole lze označit více polí, pro která se má souhrn spočítat. Souhrn se automaticky vytvoří při každé změně klíčového pole. Dalšími parametry můžeme zadat , že aktuální souhrny budou nahrazeny nebo že mají být vytvořeny celkové souhrny pro všechna data. Tlačítek Odstranit vše se všechny souhrny ruší. Upozornění: Důležitou podmínkou vytvoření souhrnů je seřazení seznamu podle pole, které má být klíčem pro souhrny!

6.7 Vytváření skupin a přehledů Příkazem Data / Skupina a přehled vytvoříme v tabulkách obsahujících výpočty skupiny dat reprezentovaných tlačítky. Stiskem těchto tlačítek zobrazíme nebo skryjeme souhrnné nebo detailní informace o daných skupinách. Této možnosti můžeme využít nejen v seznamech, ale ještě častěji ve výpočtových tabulkách. Předpokladem je uspořádaná tabulka, ve které odkazy na buňky ve vzorcích v řádku nebo sloupci musí dodržovat stejný směr a tyto řádky (sloupce) musí být shodně umístěny, a to stejně v celé tabulce. Přehledy můžeme vytvářet: Automaticky. Kurzor umístíme na libovolné místo listu a zadáme příkaz Data / Skupina a přehled / Automatický přehled. Tabulka se doplní symboly přehledu a můžeme vytvářet variantní pohledy na data. Manuálně. Tento způsob umožňuje doplnit přehledy jen tam, kde je potřebujeme. Označíme oblast buněk, ze které chceme vytvořit skupinu a zadáme příkaz Data / Skupina a přehled / Seskupit. Zobrazí se dialogový panel pro určení směru vytvoření skupiny. Při seskupování řádků (sloupců) vybereme pouze řádky (sloupce) s podrobnými daty, které vstupují do vzorců. Do výběru nezahrnujeme příslušné souhrnné řádky (sloupce) se vzorci.

6.8 Slučování seznamů Příkazem Data / Sloučit můžeme slučovat seznamy z více zdrojů (např. údaje z různých sešitů sloučit do jednoho). Při slučování se operuje s pojmy: • Zdrojová oblast. Mohou jí být samostatné sešity, listy v aktivním sešitu nebo aktivní list. Tyto tři zdroje mohou být kombinovány, maximálně však může být sloučeno 255 oblastí. Buňky s textem s výjimkou názvů polí a záznamů jsou pokládány za prázdné. U buněk obsahující výrazy jsou přenášeny jen výsledky.

15

Ing. Petr Votava KIT FSE UJEP Ústí nad Labem •

Cílová oblast. Oblast, do které hodláme umístit výsledky po slučování. Jako cílová oblast může být označena jedna buňka, jeden řádek (převezmou se jen pole uvedená ve vybraném řádku a všechny záznamy), jeden sloupec (oblast se rozšíří doprava, převezmou se jen názvy ve sloupci a všechna pole u každého záznamu), oblast (převezmou se jen shodné popisy a data na jejich průsečíku). • Funkce. Způsob sloučení seznamů. Implicitně je nastavena funkce sčítání, k dispozici je však ještě deset dalších funkcí. Lze např. určit maximum a v cílové oblasti obdržíme maximální hodnotu vybranou na příslušném místě ze všech slučovaných oblastí. • Názvy polí (sloupců) a záznamů (řádků), podle nichž jsou data identifikována. Upozornění: Názvy záznamů musí být prvním polem seznamu!

6.9 Vytváření kontingenční tabulky nebo grafu Kontingenční tabulka je speciální druh interaktivní tabulky (změny se projevují ihned), která se vytváří ze zdrojových dat uspořádaných do určitého seznamu pomocí Průvodce kontingenční tabulkou a grafem. Tato tabulka nebo graf poskytuje různé pohledy na zdrojová data včetně možnosti rychlých úprav těchto pohledů. Pole tabulky (grafu) můžeme přesunovat myší, data různě formátovat, doplnit souhrnnou funkci, vlastní připravenou funkcí apod. Jedná se o moderní a přehledný způsob práce s daty. Jako zdroj dat pro kontingenční tabulku můžeme použít buď list sešitu Excel obsahující seznam, nebo databázový soubor. Průvodce kontingenční tabulkou vyvoláme příkazem Data / Kontingenční tabulka a graf nebo kliknutím na ikonu v Panelu nástrojů. MS Excel disponuje celou řadou nástrojů pro analýzu dat. Dovoluje například: • Vypočítat proměnnou ve vzorci k dosažení požadovaného výsledku • Vytvořit citlivostní tabulku zobrazující změny jedné nebo dvou proměnných řešit rovnice • Úlohy lineárního (i nelineárního) programování • Pomáhat při simulaci modelů vytvořením scénářů situací • Zpracovat statistické charakteristiky souborů dat • Generovat proud pseudonáhodných čísel různého rozdělení pro simulaci • Jako doplňky lze k Excelu doinstalovat (příkazem Nástroje / Doplňky) Průvodce vyhledáváním a Průvodce podmíněným součtem.

16


7 Obsah MS EXCEL část 1.................................................................................................................................... 1 1 Tabulkové procesory ........................................................................................................................ 1 2 Základní údaje o MS Excelu ............................................................................................................ 1 2.1 Charakteristika............................................................................................................................ 1 2.2 Spuštění a ukončení programu .................................................................................................. 1 2.3 Prostředí a ovládání programu................................................................................................... 1 3 Práce s listy ...................................................................................................................................... 2 3.1 Sešit - dokument Excelu............................................................................................................. 2 3.2 Operace s dokumenty ................................................................................................................ 2 3.3 Práce s listy ................................................................................................................................ 2 4 Tabulka a buňky ............................................................................................................................... 3 4.1 Obsah a formát buňky ................................................................................................................ 3 4.2 Označování buněk a vkládání údajů .......................................................................................... 3 4.3 Úpravy v tabulce......................................................................................................................... 3 4.4 Vkládání údajů do buněk............................................................................................................ 4 4.5 Vytváření posloupných řad......................................................................................................... 4 4.6 Zadávání vzorců do tabulky ....................................................................................................... 5 4.7 Průvodce funkcí.......................................................................................................................... 5 4.8 Odkazy ....................................................................................................................................... 5 4.9 Odkazové operátory ................................................................................................................... 5 4.10 Kruhové odkazy ..................................................................................................................... 6 4.11 Funkce.................................................................................................................................... 6 4.12 Propojení sešitů ..................................................................................................................... 6 4.13 Řízení výpočtu ....................................................................................................................... 6 4.14 Práce s poli ............................................................................................................................ 6 4.15 Formátování ........................................................................................................................... 6 4.16 Vlastní číselné formáty........................................................................................................... 7 4.16.1 Desetinná místa, mezery, barvy a podmínky .................................................................... 7 4.16.2 Měna, procenta a matematický zápis ................................................................................ 8 4.16.3 Kalendářní data a časové údaje ........................................................................................ 9 4.17 Automatické formáty ............................................................................................................ 10 4.18 Styly...................................................................................................................................... 10 4.19 Podmíněné formátování....................................................................................................... 10 5 Grafy v Excelu ................................................................................................................................ 10 5.1 Postup při tvorbě grafu ............................................................................................................. 11 5.2 Výběr typu grafu ....................................................................................................................... 11 5.3 Speciální možnosti ................................................................................................................... 12 5.3.1 Vytváření trendů a klouzavých průměrů .......................................................................... 12 5.3.2 Chybové úsečky .............................................................................................................. 13 5.4 Objekty grafu ............................................................................................................................ 13 5.5 Formátování grafu .................................................................................................................... 13 6 Seznamy v Excelu.......................................................................................................................... 13 6.1 Seřazení seznamu.................................................................................................................... 14 6.2 Prohlížení a opravy seznamu................................................................................................... 14 6.3 Filtrování dat............................................................................................................................. 14 6.4 Automatický filtr ........................................................................................................................ 14 6.5 Rozšířený filtr............................................................................................................................ 15 6.6 Vytváření souhrnů .................................................................................................................... 15 6.7 Vytváření skupin a přehledů..................................................................................................... 15 6.8 Slučování seznamů .................................................................................................................. 15 6.9 Vytváření kontingenční tabulky nebo grafu .............................................................................. 16 7 Obsah ............................................................................................................................................. 17

17

MS EXCEL část 1. 1 Tabulkové procesory. 2 Základní údaje o MS Excelu. 2.1 Charakteristika. 2.2 Spuštění a ukončení programu

Recommend Documents