Menu
Skripta 1. díl Skripta 2. díl Rychlé návody Prostředí excelu Kurzor Automatické řady Podmíněný formát Vlastní formát buňky Práce s listy Práce s okny Úvod do výpočtů Chyby a jejich oprava Automatický filtr Finanční výpočty Absolutní odkaz Půjčky a úroky Bud_hodnota Concatenate Kontingenční TAB Souhrny Databázové funkce Funkce když |
Pravidla pro vytváření číselných formátů Příklad výpočtu kořene kvadratické rovnice
Pokročilé formátování buňky
Ú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,26E+08, -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).
Pravidla pro vytváření číselných formátů
Příklady využití klávesových zkratek při formátování buněk: Obecný číselný formát : Ctrl+Shift+~ (příklad – číslo 2545,55, výsledek 2545,55) Měna se dvěmi desetinnými čísly (záporná čísla červeně): Ctrl+Shift+$ (příklad – 2545,55 Kč) Procenta bez desetinných míst: Ctrl+Shift+% (příklad – 254555% Číslo se dvěma desetinnými místy a oddělovačem tisíců: Ctrl+Shift+! (příklad - 2 545,55)
Příklady formátování buněk – „Vlastní formát“
Chceme-li zobrazit datum 20.11.2005 ve tvaru listopad 2005, ve výběru Formát buněk| Vlastní, zadáme (mmmmm yyyy), pro zobrazení 2005-20-11, zadáme (yyyy-mm-dd)
Pokud potřebujeme zapisovat např. čísla IČO a chceme, aby se zobrazovalo ve formátu 00000258, zadáme ve výběru Formát buněk| Vlastní ( 0000000#), poté se bude zobrazovat před číslicemi požadovaný počet nul. Postup můžeme použít k zobrazování pořadových čísel faktur apod.
Pro zobrazení PSČ ve formátu s mezerou 708 00 použijeme ve výběru Formát buněk| Vlastní (0##_##)
Podmíněné formátování buněk:
Pro rychlou orientaci v listu při zobrazení údajů, můžeme použít vybarvení buněk barevně, popřípadě podtržením, barvou textu, kurzívou apod. Postup je tento: Nejprve vybereme buňky, které chceme formátovat po zvolení Formát | Podmíněné formátování, můžeme nastavit písmo, ohraničení buňky a vzorky. Nejprve zadáme hodnotu, nebo vzorec podle kterých jsme se rozhodli formátovat vybranou oblast buněk. Vzorec musí vykazovat pravdivou nebo nepravdivou hodnotu, následně zvolíme tzv. relační operátor, v němž nastavíme vztah mezi oběma podmínkami. Pokud zvolíme možnost Je mezi, musíme ve vzorcích nebo hodnotách, které podmínku vymezí, zadat i horní hodnotu oblasti (vpravo). Stiskem Formát vybereme způsob, který chceme použít. V buňce můžeme použít pouze jedno podmíněné formátování, pokud chceme přidat (odstranit) další podmínku, stiskneme tlačítko Přidat nebo Odstranit.
Nepoužívejte podmíněné formátování v kontingenční tabulce, může to ovlivnit výsledky! Pokud použijeme podmíněné formátování ve sdíleném sešitě před jeho sdílením, zůstávají stále platné, není je možné upravit nebo využít formáty nové!
Podmíněné formátování umožňuje pouze zadání tří formátů, potřebujeme-li více formátů pro barevné odlišení, musíme již použít naprogramování ve Visual basicu - VBA. Jeden příklad: máme vozový park automobilů např. škoda, opel, volvo, fiat, seat, nissan, audi, renault. Můžeme využít malý prográmek, který zapíšeme do listu a to
tak, že poklepem pravým tlačítkem myši na záložku listu vybereme volbu Zobrazit kód
Program napsán ve VBA
Práce s oblastmiOblast na listu, která obsahuje vložená data, se nazývá datová oblast. Oblasti se využívají při tvorbě kontingenční tabulky. Pole a položky určují uspořádaní dat v kontingenční tabulce. Zvolením Polí a Položek, které se mají objevit v kontingenční tabulce, určíme, která data v ní budou zahrnuta a jak budou uspořádána. Pole představuje třídu dat, jako např. Rok, Oblast nebo cena. Položka představuje podtřídu v poli. např. východ, západ, sever a jih v poli Oblast, nebo 2004 a 2005 v poli Rok. V seznamu programu Excel názvu pole odpovídá název sloupce.
Pole
V kontingenční tabulce lze použít dva typy polí ze zdrojového seznamu nebo tabulky:
Lze samozřejmě použít libovolný počet polí za zdrojové tabulky.
Rok se v kontingenční tabulce zobrazí jako řádkové pole, Oblast jako sloupcové.
Z položek zdrojové tabulky se stanou názvy řádků nebo sloupců ve výsledné kontingenční tabulce. Tyto názvy – neboli položky kontingenční tabulky – jsou podtřídami pole v kontingenční tabulce, podobně jako jsou podtřídami pole ve zdrojové tabulce.
Změna číselného formátu v datové oblasti: Když vytvoříme kontingenční tabulku, použije se pro datovou
oblast všeobecný číselný formát daný pro příslušný list. Formátování čísel
v datové oblasti je možné změnit pomocí tlačítka Číslo v dialogovém okně Znak. Např. lze použít formát měny. Označením pole celkový
součet se aktivuje tlačítko
Oblast grafu a oblast kresbyOznačit lze celý graf (Oblast grafu) což použijeme např. tehdy, když jej chceme zkopírovat na jiný list. Oblast grafu zahrnuje vlastní graf s datovými řadami a osami, včetně nadpisu grafu, legendy a popisu os. Označit však můžeme také pouze oblast kresby, chceme-li změnit její velikost nebo formát. V 2D grafu je oblast kresby vymezena osami. Ve 3D grafu zahrnuje oblast kresby názvy kategorií,, popis osových značek a popis os. V následujících obrázcích je znázorněn rozdíl mezi oblastí kresby a grafu.
Oblast grafu Oblast kresby
Práce s více listyPři řešení prostorových tabulek často potřebujeme používat ve vzorcích odkazy na jiné listy. Při plnění nebo kopírování takového vzorce se ale mění pouze odkazy na buňku, název listu zůstává stejný.Pro názornost uvedu příklad cestovní kanceláře: Jednotlivé listy pojmenujte podle let (cesty 1996;cesty1997 atd.) Na každém listě pak soustřeďte Názvy a údaje do stejných buněk. Na samotném souhrnném listě (souhrn) pak automaticky sečteme výsledky. Data v něm umístěte tak, aby byla nejlépe ve stejné poloze jako na ostatních listech. V buňce B7 souhrnu budeme chtít zobrazit součet za jednotlivé roky. Pokud do buňky E7 vložíte prosté propojení na list cesty1996 kliknutím myší na konkrétní buňku v listu cesty1996 bude zápis buňky vypadat takto (=List5!B7), nebude možné tento vzorec kopírovat ani vyplnit vpravo do dalších let. Proto vzorec musí obsahovat i název listu (=Cesty1996!B7+Cesty1997!B7+…)
Funkce a vzorceUžitečné numerické funkce jsou: ABS(číslo) vrátí absolutní hodnotu čísla AVERAGEA(pole) vrátí průměrnou hodnotu argumentů (aritmetický průměr) COS(číslo) argument je číslo v radiánech, s předponou A je inverzní funkce, s příponou H hyperbolická EXP(číslo) vrátí e (základ přirozeného logaritmu) umocněné na zadané číslo IF(podmínka; výsledek při splnění podmínky; výsledek při nesplnění podmínky) vrátí výsledek na základě splnění či nesplnění podmínky FAKTORIÁL(číslo) vrátí faktoriál čísla LOG(číslo; základ) vrátí logaritmus při daném základě, LN() přirozený logaritmus, LOG10() dekadický MAX(pole) vrátí nejvyšší hodnotu z množiny hodnot. Ignoruje logické hodnoty a text. MIN(pole) vrátí nejnižší hodnotu z množiny hodnot. Ignoruje logické hodnoty a text. MOD(dělenec; dělitel) vrátí zbytek po dělení čísla PI() vrátí hodnotu p s přesností na 15 číslic POWER(číslo;mocnina) umocní číslo na zadanou mocninu ROUND(číslo; počet číslic) zaokrouhli číslo k nejbližší hodnotě na „počet číslic“ za desetinnou čárkou ROUNDDOWN(číslo; počet číslic) zaokrouhlí číslo směrem dolů k nule na „počet číslic“ za desetinnou čárkou ROUNDUP(číslo; počet číslic) zaokrouhlí číslo směrem nahoru od nuly na „počet číslic“ za desetinnou čárkou SUM(pole) spočítá všechny čísla v rozsahu buněk
Další příklady funkcí:
Spojení buněk v jednu
1. řešení je zdlouhavé 2. řešení je praktické
=(-B1+(B1^2-4*A1*C1)^0,5)/(2*A1),
Příklad výpočtu kořene kvadratické rovnice
=HODNOTA.NA.TEXT}A1;”dddd”) Funkce KDYŽ slouží k vyhodnocení definované podmínky. Můžeme např. sledovat navýšení prémií, pokud pracovník splní určité přesčasové hodiny; stanovit výši procenta slevy, když obchodní obrat dosáhne stanovené hranice aj. Syntaxe funkce: = KDYŽ (podmínka;ano;ne) Argument „ano“ se provede, jestliže je definovaná podmínka splněna. Obsahem tohoto argumentu bude často text, který se vypíše. V argumentu můžeme použít vzorec. Argument „ne“ se naopak provede, pokud definovaná podmínka splněna není. Obsahem tohoto argumentu může být opět text nebo vzorec. Příklad: Funkci vložíme do buňky D4, aby se provedla pro prvního pracovníka uvedeného v tabulce a po zapsání ji zkopírujeme ve směru sloupce. Pro funkci definujeme následující argumenty:
Vnořená funkce V řadě případů je třeba testovat několik podmínek současně. V tabulce, kterou jsme vytvořili, potřebujeme stanovit procento prémie. Platí následující podmínky: 1. Při přesčasu 20 – 30 hodin – prémie 20 % 2. Při přesčasu 10 – 20 hodin – prémie 15 % 3. Při přesčasu 5 – 10 hodin – prémie 10 % 4. Při přesčasu do 5 hodin – bez nároku
Otestujeme pomocí vnořené funkce tyto čtyři podmínky. Funkce testuje logické podmínky zleva doprava. Jestliže vyhodnotí první podmínku jako nepravdivou, provede se další vnořená funkce. Budeme tedy testovat podle vzorce: =KDYŽ(C4>=30;"20 %";KDYŽ(C4>=20;"15 %";KDYŽ(C4>=10;"10 %";KDYŽ(C4<5;"bez navýšení")))) Poznámka: funkce KDYŽ může testovat maximálně 7 vnořených podmínek OdkazyOdkazy 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. 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.
Práce s rozsáhlými tabulkamiPříklad: Máme dlouhou, případně i širokou tabulku. Pokud posuneme jejím obsahem, schovají se nám nadpisy sloupců nebo popisky řádků. Jak je zafixovat? Na listě zaměstnanci je rozsáhlá tabulka se standardní
strukturou – tj. v horním řádku jsou nadpisy sloupců, v prvních dvou
sloupcích nadpisy řádků. Pokud takovouto tabulkou budeme posunovat, stane se,
že nadpisy nebudou vidět na obrazovce. Příčka nám umožní nadpisy „zafixovat“ –
uvidíme je tedy, i když posuneme obsahem listu.
Uchopíme myší
vodorovnou příčku a se stisknutým levým tlačítkem ji přesuneme pod nadpisy (tj.
na rozhraní 1 a 2 řádku). Stejným způsobem uchopíme svislou příčku a posuneme
je za sloupec Jméno (tj. na rozhraní sloupce B a C). Příčky můžeme ještě
zafixovat pomocí příkazu Ukotvit příčky z nabídky Okno. Tím
se změní na vlasovou čáru a myší s nimi již nebudeme moci pohybovat.
Budeme-li posunovat obsahem listu, nadpisy se neodsunou mimo viditelnou oblast
a budeme je mít stále na očích. TIP: Příčky můžeme na pracovní plochu také umístit pouze pomocí myši. Pro umístění vodorovné příčky uchopíme malou šedivou plošku v pravém horním rohu (těsně nad šipkou posuvnému) a se stisknutým levým tlačítkem ji táhneme směrem dolů. Obdobně umístíme svislou příčku, která se skrývá v pravém dolním rohu (napravo od pravé šipky posuvníku).
ŠablonaVytvoříme knihu jízd – resp. šablonu. Kniha jízd, kterou zpracujeme, je určena pro evidenci jízd soukromého vozidla pro firmu. Pomocné údaje Začneme
tím, že se podíváme na list Údaje. Zde jsme si uložili údaje, se kterými budeme
provádět výpočty na listech za jednotlivé měsíce. První dva údaje – Sazba
1 km, Průměrná cena benzínu – vycházejí z momentálně platných předpisů.
Budeme je muset obměňovat v souladu se změnou předpisu o cestovních
náhradách a se stanovenou průměrnou cenou benzínu (který ve vozidle
používáme, zde je uložena cena platná pro Natural 95 pro rok 2004). Spotřebu na
100 km zadáme dle technického průkazu vozidla. Cena PHM na 1 km je vypočtena ze zadaných předchozích údajů. Výpočty na listu Všechny listy mají stejnou strukturu, měsíce jsme očíslovali římskými číslicemi. Uvedeme si nyní několik postupů pro list obecně. Do sloupců A a B budeme zapisovat datum a den jízdy. Do sloupce C zapíšeme pouze výchozí stav tachometru (do buňky C5). V jeho dalších řádcích se počáteční stav rovná konečnému stavu v předchozím řádku.
Proto
můžeme od buňky C6 počínaje používat odkaz, tak jak vidíme na obrázku.
Vytvoření šablony Hotový sešit uložíme pomocí příkazu Uložit jako. Nazveme jej např. jizdy. Typ souboru nastavíme – šablona (xlt). Tím se otevře složka, kde jsou uloženy šablony. Tato šablona potom bude dostupná v dialogovém okně po použití příkazu Soubor – Nový.
Obecně: Vytvořte sešit, který bude mít listy, výchozí text (např. záhlaví stránek a popisky sloupců a řádků), vzorce, makra, styly a ostatní formátování, které mají obsahovat nové sešity vytvořené na základě šablony. V nabídce Soubor klepněte na příkaz Uložit jako. V rozevíracím seznamu Typ souboru klepněte na složku, do níž chcete šablonu uložit. Šablonu výchozího sešitu vytvoříte vybráním složky XLStart nebo alternativní spouštěcí složky. Složka XLStart je obvykle umístěna v cestě: C:/os/Profiles/jméno_uživatele/Application Data/Microsoft/Excel/XLStart kde „os“ představuje složku operačního systému např. Windows. Chcete-li vytvořit vlastní šablonu sešitu, vyberte složku Templates, která bývá umístěna v C:/os/Profiles/jméno_uživatele/Application Data/Microsoft/Templates. Zadáním názvu sešitu do textového pole Název souboru vytvoříte šablonu výchozího sešitu. Zadáním libovolného platného jména souboru vytvoříte vlastní šablonu.
Práce s databázíPoslední dobou se hodně hovoří v Excelu o kontingenčních tabulkách (PivotTable). Tyto tabulky jsou velmi efektní pro třídění dat z velkých seznamů/tabulek (např. sklady, seznamy osob, obraty zboží apod.). Jsou to vlastně jakési dvouúrovňové souhrny tabulek z dat (seznamů), které jsou v jiné tabulce nebo na jiném listu a jsou s nimi datově provázány. Takže při změně původních dat se automaticky mění i data v těchto kontingenčních tabulkách
Tabulka má celkem 600 řádků
Na listu A máte na listu data seznam obratů jednotlivých zboží včetně cen, datumů a obchodníků. Tento seznam je úplný, ale nepřehledný. Potřebovali bychom jasně vidět, za kolik který obchodník prodal jednotlivých typů zboží, neboť se jména objevují nepravidelně, podle data prodeje. A právě takové pohledy nám dává kontingenční tabulka.
Postup vytvoření:
3. Excel nám automaticky označil naši tabulku (v případě, že se tomu nestane, musíme ji označit ručně) 4. Nakonec zvolíme umístění tabulky (doporučuji nový list) a potvrdíme tlačítkem dokončit:
5. Kontingenční tabulka je hotova:
Zatím je bez dat, které do ní musíme nastavit podle našich představ. Vpravo se nám objevil seznam polí z naší tabulky a tato pole do kontingenční tabulky umístíme. Požadujeme stejnou tabulku jako v naší ukázce, tedy: a)
- na řádková pole chceme umístit obchodníky - pomocí myši přetáhneme na „sem přetáhněte sloupcová pole“ pole „typ“
c) –
nakonec do datových položek (uvnitř tabulky) chceme umístit cenu
Kontingenční tabulka je hotova:
Uvedená kontingenční tabulka lze dále upravovat a rozšiřovat. Postupy nejsou jednoduché a doporučují se pro případ hloubější práce s těmito tabulkami podívat do odborné literatury.
Souhrny
V databázovém listu údajů v tabulce (tzv. databáze nebo seznam) můžeme pro lepší přehlednost vytvářet tzv. souhrny. Jsou to uspořádané souhrnné údaje podle společných hodnot v jednom ze sloupců.(např. součty, průměry, počty hodnot a pod…) Ukážeme si to na příkladu excelového souboru ceníku, kde bychom rádi zjistili, kolik kusů jednotlivých typů zboží máme v ceníku (standardně jsem schopen z tabulky zjistit pouze celkový počet řádků) .Výsledkem tedy bude tabulka údajů jednotlivých typů spolu se souhrnným výpočtem počtu jednotlivých typů – počtů hodnot) Předpoklady správné funkčnosti: 1. Tabulka musí být kompaktní, nesmí v ní být prázdné sloupce ani řádky – jinak nebudou fungovat tyto vlastnosti a funkce, které pro tzv. seznamy poskytuje Excel. 2. Protože Excel bude procházet tabulku podle zvolené hodnoty směrem dolů a vždy, když se změní hodnota v prohledávaném sloupci, tak provede souhrn –MUSÍME tabulku seřadit podle prohledávaného sloupce 3. Úkol: - chci vytvořit souhrn, který mi řekne, kolik kusů mám jednotlivých typů zboží – viz ukázka výsledku.
Výsledek :
Původní stav:
Postup řešení: 1. Zkontrolujeme tabulku, zda není přerušená prázdným sloupcem nebo řádkem (v každém řádku včetně záhlaví musí být alespoň jedna hodnota). 2. Seřadíme tabulku abecedně podle typu (ikonkou „A-Z“ na panelu nástrojů nebo z menu „Data-seřadit“). 3. Z menu Data zvolíme příkaz souhrny – objeví se následující dialogové okno:
4. V dialogovém okně zvolíme následující:
5. Potvrdíme tlačítkem OK a zkontrolujeme si výsledek. Měl by vypadat takto:
Používání souhrnů: Skrývání/zobrazení úrovní souhrnů jednotlivých položek: - Pomocí tlačítka +/- vlevo před záhlavím řádku Skrývání/zobrazení celé úrovně souhrnů - Pomocí tlačítka 1-2-3 v levém horním rohu listu. Doplnění dalších souhrnů do již existujících: Lze pouze za předpokladu souhrnu podle stejného sloupce nebo předem setříděného (jinak budou výsledkem nesmyslné hodnoty). Vytvoříme první souhrny. Následně vytvoříme druhé, při kterých musíme vypnout položku „nahradit aktuální souhrny“. Odstranění souhrnů: Z menu Data zvolíme příkaz Souhrny a z následujícího dialogového okna zvolíme příkaz odstranit vše. V přiloženém souboru jsou vzory i výsledky uvedeného příkladu.
Tipy:
Kontingenční grafy
Pokud budeme zpracovávat rozsáhlou
tabulku, z níž máme graficky prezentovat určité podskupiny dat, bude pro nás
kontingenční graf právě tím optimálním nástrojem. Zdrojem dat může být tabulka, která je připojena v příkladu viz dále. Sledujeme zde 3 závody – Praha, Kolín a Liberec, každý ze závodů je členěn do tří středisek. Na každém ze středisek máme sledovat určitý typ nákladů. Požadavky
na graficky prezentovaná data mohou být různé, např. potřebujeme vytvořit
následující grafy: Takových
kombinací lze vymyslet celou řadu. Vytvoření kontingenčního grafu
Datovým zdrojem je seznam umístěný přímo v sešitu Excel, jako typ kontingenční sestavy označíme Kontingenční graf (viz obr.).Pokud jsme kurzor umístili předem do oblasti tabulky, v další části průvodce bude tato oblast automaticky nastavena:
V poslední
části průvodce označíme, zda bude graf umístěn na novém listě (předvolba) nebo
na některém z již existujících listů a použijeme tlačítko Dokončit.
a současně základ grafu s vyznačením, kam lze umístit jednotlivá pole ze seznamu.
Jednotlivá
pole uchopíme přímo v seznamu a myší je rozmístíme na vyznačená místa. V grafu se zobrazí sloupce. V oblastech, kam jsme umístili jednotlivá pole, vzniknou ovládací prvky
Kliknutím na šipku ovládacího prvku Ovládací prvek Sledované náklady zobrazíme seznam položek v příslušném poli. můžeme nastavit třeba takto:
Kdykoli
změníme nastavení jednotlivých ovládacích prvků, vygeneruje se graf
odpovídající tomuto nastavení.
Zabezpečení dokumentuV některých případech je vhodné některé oblasti sešitu
chránit proti přepsání a přitom umožnit zapisování do některých oblastí
zapisování. Otevřeme-li nový sešit v Excelu, můžeme pochopitelně zapisovat do veškerých buněk. Jestliže bychom vytvořili tabulku, mohli bychom celý list zamknout. Potom by nebylo možné na tento list cokoli zapsat. Je to z toho důvodu, že všechny buňky v sešitě jsou původně nastavené jako zamčené, ale jejich zámek se projeví teprve poté, když zamkneme list. List se
zamkne následujícím postupem: Zpřístupnění oblastí pro zápis Jestliže je žádoucí, aby do některých buněk bylo možné
zapisovat, musíme je ještě před zamčením listu zpřístupnit.
Zamknutí sešitu I pro zamknutí sešitu používáme nabídku Nástroje. Zde použijeme příkazy Zámek | Zamknout sešit. Zamknutí sešitu může být opět opatřeno heslem, které zadáme do následného dialogového okna. Tento druh zámku zamezí uživatelům manipulaci s listy. Nebudou je moci přejmenovávat, přesunovat nebo kopírovat, odstranit nebo vkládat, ani zobrazovat skryté listy či je skrývat (nabídka Formát | List | Skrýt.). Ve verzi XP nebudou moci ani měnit šířku sloupců nebo výšku řádků. Jestliže nastavíme okno souboru do pracovní velikosti a ponecháme je v určité poloze, můžeme při zamknutí sešitu označit zaškrtávací pole Okno. Pak nebude možné měnit ani velikost ani polohu tohoto okna. Zámek sešitu odstraníme pomocí příkazu Nástroje| Zámek| Odemknout sešit.
Po pokusu o zápis do buňky se objeví upozornění:
Vyhledat
Odkazy na buňky: Při oddělování jednotlivých argumentů u funkcí používáme středník, dvojtečku mezi odkazy na dvě buňky vkládáme i všechny buňky mezi těmito dvěma, např. A2:C3 je totéž jako A2;A3;B2;B3;C2;C3. Namísto explicitního zadání odkazů je možné po výběru funkce tyto buňky jednoduše vysvítit myší, odkazy se doplní automaticky.
Funkce nemusí ani nic počítat, mohou např. vypsat text při splnění podmínky (vzorec byl zapsaný jen do C2, do C3 byl zkopírovaný). Při kopírování vzorců s odkazy se automaticky mění i odkazy, tedy když máme v buňce C2 vzorec =IF(A2-B2>=0;A2-B2;"Bankrot") a tento vzorec zkopírujeme do buňky C3, odkazy na buňky A2 a B2 se změní na A3 a B3, tedy v C3 bude vzorec =IF(A3-B3>=0;A3-B3;"Bankrot"). Kdybychom tento vzorec zkopírovali ne do buňky C3, ale do buňky D2, odkazy na buňky A2 a B2 se změní na B2 a C2, tedy v D2 bude vzorec =IF(B2-C2>=0;B2-C2;"Bankrot"). Pokud ale nechceme, aby se při kopírování odkaz na nějakou buňku změnil, zadáme na ni absolutní odkaz, který vyrobíme přidáním dolarových znaků před písmeno sloupce a číslo řádku, tedy např. místo A1 bude $A$1. To je absolutní odkaz, který se při kopírování vzorce už nemění. Odkaz bez dolarů A1 se jmenuje relativní odkaz. Existuje ještě ale i smíšený odkaz, kde je dolarový znak pouze před písmenem nebo jen před číslem, tedy např. $A1 nebo A$1. Odkaz $A1 zůstává beze změny při kopírování doprava, mění se jen při kopírování dolů. Odkaz A$1 se mění při kopírovaní doprava, nemění se jen při kopírovaní dolů.
Nač se smíšené odkazy dají použít? Jako příklad si představme, že máme vygenerovat malou násobilku. Nejdříve si vygenerujeme čísla v prvém řádku a prvém sloupci, přičemž prvek A1 necháme prázdný. Potom do B2 vložíme vzorec =B$1*$A2 a zkopírujeme ho do celé tabulky. Vidíme, že když se posuneme např. na C3, tak se vzorec změní na =C$1*$A3 ,co je přesně to co jsme chtěli, odkazovat se jen na první řádek a na první sloupec. Relativní odkazy se využijí například u výpočtu Fibonacciho čísel . Ty jsou definované jako fib(0) = 0, fib(1) = 1, fib(n) = fib(n-1) + fib(n-2) pro větší n, teda "sčítej poslední dvě, aby si dostal další". Jak Exelem vyřešit problém, kolik je fib(12) ? Do buňky A1 dáme 0, do buňky B1 dáme 1, do buňky C1 dáme =A1+B1 a potažením za pravý dolní roh kopírujeme doprava.
Odkazy buněk mohou vést aj na jiné listy, dokonce i na jiné soubory, např. odkaz '[Ročník I.xls]Skupina I'!$B$2:$B$4;' odkazuje na buňky B2-B4 v listu pojmenovaném Skupina I a v souboru Ročník I.xls.
Práce s listemVýchozí sešit obsahuje při otevření 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 klávesy. Pohyb po listu: 1. Pomocí rolovacích lišt ( vodorovně, svisle) 2. Zadáním odkazu na buňku či oblast v orientačním poli. 3. Vyhledáním obsahu buňky – menu Úpravy / Najít
Buňka B2 Oblast buněk B2:D4
Vkládání listu : provedeme pomocí menu Vložit. Zde můžeme rovněž určit typ vkládaného listu ( např. list s grafem přes položku Graf) Odstranění listu provedeme z menu Úpravy / Odstranit list. Přejmenování listu provedeme dvojklikem na záložku příslušného listu ( max. 31 znaků) Přesun listů provedeme pomocí myši – přetažením záložky na nové místo. Kopie listů provádíme dvojklikem na list a výběrem z nabídnutého menu.
Kopie listu Tabulka a buňkyTabulka je složena z nejmenších částí tzv. buněk. Každá buňka je definována souřadnicemi (adresou) tj. označením sloupce a řádku ( např. A3, C256, AB13, atd.). Při tvorbě můžeme pracovat s více buňkami najednou :
Obsah a formát buňkyKaždá buňka má svůj obsah a formát (styl) Obsahem buňky je :
Základy práce s grafem
Základem je zpracovaná tabulka 1. Do připravené tabulky umístíme kurzor (1) (není to nezbytně nutné, ale je to lepší, protože hned v dalším kroku si budeme moci vytvářený graf prohlížet v náhledu). 2. V nabídce Vložit použijeme příkaz Graf (2) nebo místo toho použijeme rovnou ikonu (3).
Průvodce grafem 1. V první části průvodce určíme, jaký typ grafu bude pro naše údaje nejvhodnější. Ne každý typ se totiž hodí pro každé údaje.V seznamu Typ grafu označíme myší požadovaný typ grafu (1). V pravé části dialogu si můžeme zvolit pro graf ještě vhodný podtyp (2). Jeden z podtypů je již vždy přednastaven – černé pole). Stisknutím a podržením tlačítka (3) si můžeme zobrazit ukázku grafu. Pokračujeme tlačítkem Další (4).
2. V další části grafu se zobrazil náhled (1). Z náhledu je zřejmé, že by bylo lépe z grafu vyloučit datovou řadu Celkem, kterou Excel automaticky zahrnul do vybrané oblasti (2). Pomocí přepínače Řady tvoří (3) můžeme zvolit, zda sloupce grafu budou znázorňovat údaje situované do sloupců nebo do řádků (v našem příkladu jsme nechali volbu Sloupce, takže graf bude zobrazovat situaci podle jednotlivých měst). Upravíme oblast grafu tak, že klikneme na tlačítko (4).
3. Dialog se nyní zmenšil (1) a případně jej můžeme za modrý pruh uchopit myší a přemístit tak, aby nám nepřekrýval tabulku. Poté tažením myši označíme v tabulce tu část, která má být zobrazována v grafu (2). Odkaz se nám ve zmenšeném dialogu automaticky přepíše. Dialog opět zvětšíme kliknutím na tlačítko (3) a pokračujeme tlačítkem Další.
4.
V další
části průvodce využijeme nyní kartu Název, abychom mohli pojmenovat graf a jeho
osy. Do jednotlivých polí vepíšeme tedy názvy (1,2,3), které se nám hned
zobrazují v náhledu vpravo. Dostali jsme se do poslední části průvodce. Tam máme možnost volby, jestli graf má být umístěn na stejný list jako tabulka (přednastaveno - vhodné u menších tabulek), případně na některý jiný list v našem sešitě – výběr listů zobrazíme kliknutím na šipku (3). Je- li tabulka rozsáhlá, použijeme přepínač (4) a vytvoří se nový list, který můžeme pojmenovat zápisem názvu do vedlejšího pole (4). My volbu nebudeme měnit a použijeme tlačítko Dokončit.
Upravení grafuGraf byl umístěn na pracovní plochu našeho aktivního listu. Zpravidla nebývá v této fázi jeho umístění příliš vhodné, uchopíme jej tedy myší kdekoliv uvnitř bílé plochy a přesuneme jej na vhodné místo. Proporce – velikost grafu dále upravíme pomocí objektových značek (1,2,3 – nebo i ostatních) tak, že myš umístíme na některou ze značek a se stisknutým tlačítkem táhneme ve směru šipek.
Graf je objekt, pokud bude vybrán, bude obklopen po obvodě černými objektovými značkami. Jestliže klikneme myší mimo oblast grafu, tyto objektové značky zmizí a objekt přestane být aktivní. Další podrobnosti 1. Graf se jako objekt skládá z řady různých podobjektů, které se dají samostatně upravovat (např. mřížka, barva sloupců, pozadí grafu aj.) 2. Pokud potřebujeme graf smazat, vybereme jej kliknutím a použijeme klávesu Delete (Del). 3. Kdykoliv potřebujeme graf upravovat (např. změnit typ grafu, oblast zobrazených dat, umístit graf na jiný list), klikneme na graf a poté znovu spustíme průvodce (jako když jsme graf vytvářeli), pak můžeme rovnou pomocí tlačítek Další přejít do libovolného dialogu průvodce a změnit jakékoliv nastavení. Průvodce nemusíme procházet celého a můžeme jej kdykoliv potvrdit tlačítkem Dokončit. 4. Data v tabulce jsou automaticky propojena s grafem, při každé změně v tabulce se tedy obsah grafu automaticky přizpůsobí obsahu tabulky.
|