M4
TABULKOVÝ PROCESOR zpracoval: Ing. Marek Herman
ÚVOD DO MS EXCEL 2010 Spuštění programu Vytvoření nového sešitu: Soubor -> Nový
CTRL+N
Vkládání dat Uložení sešitu (ikonka s disketou, Soubor -> Uložit jako) Zavírání dokumentů – Soubor -> Zavřít
CTRL+S
ALT+F4
Pro přenos dat mezi různými tabulkovými procesory se používá textový soubor CSV. Umožňuje přenášet pouze hodnoty (nikoliv vzorce nebo formátování) pouze z jednoho listu.
5. 3. 2013
2
Uložení sešitu jako šablony
Šablona – zvláštní typ sešitu s určitým obsahem a formátováním, které můžeme použít jako vzor pro ostatní podobné sešity.
Lupa – slouží k rychlému zvětšení nebo zmenšení tabulky.
Zobrazení -> Lupa (nebo ve Stavovém řádku). 5. 3. 2013
3
Popis prostředí
Sloupce (A…) Pole názvu Řádky (1…)
Vložit funkci
Řádek vzorců
Aktivní buňka Listy
5. 3. 2013
Lupa
4
BUŇKY
Buňka – místo, informace.
v
němž
jsou
uchovávány a
zobrazovány
Oblast – souvisle označená skupina buněk (klepnu, držím, táhnu, pustím -> označená oblast změní barvu), zápis: např. A1:B8 – k označení skupiny nesousedících buněk používáme
CTRL
Sešit – soubor, se kterým v Excelu pracujeme. Výběr řádku nebo sloupce -> klepneme na záhlaví řádku (např. na číslo 8 -> označí se celý řádek), obdobně u sloupce
Vložení řádku a sloupce – klepneme pravým tlačítkem myši na záhlaví řádku -> Vložit buňky, obdobně u sloupce
5. 3. 2013
5
Odstranění řádku nebo sloupce - klepneme pravým tlačítkem myši na záhlaví řádku -> Odstranit, obdobně u sloupce
DELETE pokud stiskneme klávesu -> řádek v tabulce zůstane, ale budou vymazány všechny v něm uložené údaje
Změna šířky sloupce a výšky řádku – klepneme pravým tlačítkem myši na záhlaví řádku -> Výška řádku, analogicky u sloupce – šířku sloupce a výšku řádku lze změnit i tak, že najedeme myší na průsečík řádku (sloupce) -> zobrazí se dvojšipka -> táhnu
Vymazání obsahu buněk – obsah aktivní buňky vymažeme stiskem klávesy DELETE
5. 3. 2013
6
Kopírování buněk
Př. Zkopírujte oblast buněk B2:I4 do buňky C6
– Postup:
označíme si oblast, kterou chceme zkopírovat
pravé tlačítko myši -> Kopírovat anebo
CTRL+C
– ve vybrané oblasti se po okrajích buněk zobrazí „pohybující se čárky“
5. 3. 2013
klepneme do buňky B6 -> pravé tlačítko myši -> Vložit anebo CTRL+V 7
po napsání znaku do další buňky „pohybující se čárky“
Přesouvání buněk
– 1. možnost - analogie s kopírováním
pravé tlačítko myši -> Vyjmout
CTRL+X
poté klepneme do nové buňky -> pravé tlačítko myši -> Vložit
– 2. možnost – označíme si oblast buněk, klepneme k levému hornímu rohu první buňky (zobrazí se 4 šipky -> klepnu, držím, táhnu, pustím)
5. 3. 2013
8
Datová řada
doplnění textu (měsíce, dny) -> pravý křížek v dolním rohu buňky
čísla -> CTRL + pravý křížek v dolním rohu buňky (anebo vyplnit hodnotu min. do dvou buněk + pravý křížek v dolním rohu buňky)
5. 3. 2013
9
Vyhledání obsahu buňky, nahrazení obsahu buňky – Domů -> Najít a vybrat anebo
Najít:
CTRL+F
Nahradit:
CTRL+H
5. 3. 2013
10
Třídění dat – při třídění dat je nutné označit všechny sloupce obsahující související hodnoty, a ne pouze sloupec, podle kterého chceme údaje utřídit
Panel nástrojů -> Seřadit a filtrovat -> Vlastní řazení
– př. Seřaďte občany v tabulce podle výše výdělku vzestupně (tj. od nejmenšího k největšímu)
5. 3. 2013
11
pokud chceme tabulku utřídit podle hodnot několika sloupců, vybereme postupně jejich jména ze seznamu v polích Přidat úroveň Př. Seřaďte hráčky v tabulce podle získaných bodů sestupně (tj. od největšího čísla ve sloupci Body po nejmenší).
5. 3. 2013
12
PRACOVNÍ LIST
Přejmenování pracovního listu – dvakrát klepneme na daný list, napíšeme nový název -> Enter
Vložení listu – Vložit -> List
Odstranění listu – pravé tlačítko myši -> Odstranit
Přesun listu – pravé tlačítko myši -> Přesunout nebo zkopírovat
přesun mezi listy vložení nového listu 5. 3. 2013
13
Kopírování listu
5. 3. 2013
14
FORMÁTOVÁNÍ
Pravé tlačítko myši -> Formát buněk (Řádek nabídek Domů)
označíme si oblast buněk, ve které chceme provést formátování
5. 3. 2013
15
Záložka Číslo – – – –
Číslo – desetinná místa, oddělovat 1000 Měna – desetinná čísla, symbol (Kč) Datum Procenta (%)
Záložka Zarovnání
– Zarovnání textu (vodorovně, svisle), Orientace textu – Zalomení textu – zobrazí veškerý obsah v buňce na více řádcích (je možno zalomit i tím způsobem, že klepneme do Řádku vzorců a před každým slovem použijeme
ALT+ENTER+ENTER – Sloučit buňky – umožňuje sloučit vybrané buňky do jedné. 5. 3. 2013
16
Záložka Písmo
Záložka Ohraničení a Výplň
5. 3. 2013
17
VZORCE A FUNKCE
vzorec musí začínat znaménkem =
Možnosti tvoření vzorců:
– sečteme jednotlivé hodnoty, v buňkách, např. = 1+2+3
5. 3. 2013
které
se
nacházejí
tento způsob není vhodný, protože když pozměníme hodnotu jedné z buněk, výsledný součet v buňce se nezmění -> nepoužíváme!
18
– sečteme jednotlivé buňky, v níž jsou umístěny dané hodnoty, např. = D3+D4+D5
5. 3. 2013
pokud budeme mít obsáhlou tabulku, tento postup by byl velmi zdlouhavý
19
Funkce – vestavěná procedura, která provádí speciální operace, např. sčítá oblast buněk nebo počítá aritmetický průměr hodnot v buňkách -> nejlepší řešení pro výpočet
5. 3. 2013
Vzorce -> Vložit funkci
klepnout na ikonu fx (před Řádkem vzorců)
20
Operátory
Aritmetické operátory – – – –
sčítání (+) odčítání (-) násobení (*) dělení (/)
Relační operátory – – – – – –
menší (<) větší (>) rovný (=) menší nebo rovný (<=) větší nebo rovný (>=) nerovný (<>)
5. 3. 2013
21
Matematické funkce
= ZAOKROUHLIT = SUMIF (sečte buňky vybrané podle zadaných kritérií)
= = = = = =
Statické funkce: PRŮMĚR (aritmetický průměr oblasti – bloku) POČET (počet čísel v oblasti – bloku) MAX (maximální hodnota) MIN (minimální hodnota) SUM (součet údajů v oblasti – bloku) COUNTIF (kolikrát se vyskytuje stejná hodnota v buňkách) Logické funkce:
= KDYŽ 5. 3. 2013
22
Chybová hlášení ve vzorcích
###### - nedostatečná šířka sloupce
#HODNOTA! – ve vzorci jsou použity chybné typy argumentů (jedna buňka obsahuje text, druhá číselnou hodnotu). #DIV/0! – dělení nulou (0 ve jmenovateli zlomku); hlášení se zobrazí i v případě, kdy buňka, kterou dělíme, je prázdná.
#NÁZEV? – chybný neexistující název funkce
#REF! – vymazaná oblast původního odkazu
5. 3. 2013
23
Někdy se může zobrazit chyba ve formě zeleného trojúhelníku v levém horním rohu buňky. Upozorňuje na okolní buňky, které může chtít zahrnout do výpočtu. Pokud chceme trojúhelník odstranit, tak zvolíme nabídku Ignorovat chybu.
5. 3. 2013
24
RELATIVNÍ A ABSOLUTNÍ ODKAZ
Relativní odkaz – kopírování vzorce do jiné buňky. př. Tabulku s danými údaji (mimo hodnotu v buňce E7) přepište do Excelu. Do buňky E7 vložte vzorec. V dalších buňkách (E8 – E11) bychom mohli postupovat analogickým postupem při výpočtu, postup lze však urychlit kopírováním vzorce
5. 3. 2013
25
Postup při kopírování vzorce – označíme si buňku, ve které chceme kopírovat vzorec, klepneme pravým tlačítkem myši a zvolíme možnost Kopírovat anebo klepneme levým tlačítkem myši a zvolíme CTRL+C – následně označíme buňky a zvolíme Vložit anebo
5. 3. 2013
CTRL+V
26
Rychlejší postup – klikneme do pravého dolního rohu požadované buňky -> objeví se malý černý křížek -> klepnu, držím, táhnu do dalších buněk, pustím tlačítko myši
5. 3. 2013
27
Nyní vložím vzorec do buňky F7 (Tržba včetně DPH) – =E7+(E7*E4)
tržba+(tržba*sazba DPH)
– nakopíruji vzorec pro další buňky ve sloupci
Jsou nakopírované hodnoty správné? 5. 3. 2013
28
Kdybychom klepli do každé z buněk v oblasti F7:F11 a podívali jsme se do řádku vzorců, zjistíme, že výpočet není správný
– Např. se podívejte do buňky F8 -> sazba DPH by měla být v buňce E4 a nikoliv v buňce E5
– v těchto případech používáme Absolutní odkaz 5. 3. 2013
29
Absolutní odkaz – přesná adresa buňky ve vzorci – bez ohledu na polohu buňky, která vzorec obsahuje (F4). Postup: klikneme do buňky F7 a napíšeme vzorec:
– = E7+(E7*E4 nyní zmáčkneme klávesu F4) a pak Enter
5. 3. 2013
30
Nyní jsou hodnoty ve sloupci Tržba včetně DPH správné
5. 3. 2013
31
Nyní vypočteme položku Celkem – klepneme do buňky C12 -> Vzorce -> Vložit funkci – Vybereme funkci SUMA, stiskneme OK – daná oblast výpočtu buněk souhlasí -> OK
5. 3. 2013
32
Nyní vypočteme položku Průměr Oblast výběru nesouhlasí, myší označíme oblast buněk, kterou chceme zahrnout do výpočtu -> pak OK
5. 3. 2013
33
Tabulka je kompletně hotová
5. 3. 2013
34
Př. Vytvořte následující tabulku (včetně formátování). V řádku 8 vypočtěte hodnoty.
Výsledek:
– výpočet v B8: =SUMA(B4:B7) – nakopírování vzorce z B8 do buněk C8:I8
5. 3. 2013
35
Př. Vytvořte následující tabulku (včetně formátování). Vypočtěte hodnoty ve sloupcích E a G a dále v buňce E18. V buňce E18 a G18 použijte funkci průměr.
5. 3. 2013
36
Výsledek předcházejícího úkolu:
Výpočet Výpočet Výpočet Výpočet Výpočet
5. 3. 2013
v v v v v
E4 =D4/C4 G4 =F4/C4 C18: =SUMA(C4:C17) D18: =SUMA(D4:D17) E18: =PRŮMĚR(E4:E17)
Výpočet v F18: =SUMA(F4:F17) Výpočet v G18: =PRŮMĚR(G4:G17)
37
Př. Vytvořte následující tabulku (včetně formátování). Vypočtěte hodnoty ve sloupcích B:E.
5. 3. 2013
38
Výsledek předcházejícího úkolu:
Výpočet Výpočet Výpočet Výpočet Výpočet Výpočet
5. 3. 2013
v v v v v v
B9: =SUMA(B5:B8) B11: =MIN(B5:B8) B13: =MAX(B5:B8) B15: =PRŮMĚR(B5:B8) F9: =SUMA(B9:E9) Výpočet v F13: =MAX(B13:E13) F11: =MIN(B11:E11) Výpočet v F15: =PRŮMĚR(B15:E15) 39
Př. Vytvořte následující tabulku (včetně formátování). Vypočtěte hodnoty ve sloupcích E, G a I. V buňce
5. 3. 2013
40
Výsledek předcházejícího úkolu:
Výpočet v E4: =C4*D4 Výpočet v G4: =E4-(E4*F4) Výpočet v I4: =G4+(G4*H4)
5. 3. 2013
v E20: =SUMA(E4:E19) v G20: =SUMA(G4:G19) v I20: =SUMA(I4:I19)
41
Vytvořte následující tabulku a vypočtěte hodnoty ve sloupcích D – G. (v buňce F5 a F6 si dejte pozor na %, % jsou umístěna v jiné buňce, při tvorbě vzorce na to pamatujte).
5. 3. 2013
42
Výsledek předcházejícího úkolu:
Nákup Kč: (výpočet v C9) =C9*$F$4 Zisk: (výpočet v D9) =(D9*$F$5)/100 Prodejní cena: (výpočet v F9): =D9+E9 Prodejní cena s DPH: (výpočet v G9) =F9+(F9*$F$6)/100
5. 3. 2013
43
Funkce ZAOKROUHLIT
= ZAOKROUHLIT
Př. Do buňky C2 vložte takovou funkci, která zaokrouhlí částku v buňce B2 (123,45 Kč) na celé koruny. Funkci z buňky C2 nakopírujte do buněk C3:C4.
Pokud bychom do pole Číslice napsali číslo 1 -> zaokrouhlení na jedno desetinné místo, pokud bychom zapsali číslo -1 -> zaokrouhleno směrem doleva od desetinné čárky… 5. 3. 2013
44
Př. Do buňky D2 vložte funkci, která zaokrouhlí částku z buňky B2 na jedno desetinné místo. Funkci z buňky D2 nakopírujte do buněk D3:D4.
5. 3. 2013
45
Funkce POČET a POČET 2
= POČET = slouží ke zjištění počtu čísel ve vybrané oblasti buněk. = POČET2 = slouží ke zjištění počtu buněk ve vybrané oblasti, které nejsou prázdné. Př. Do buňky B13 vložte takovou funkci, která zjistí počet všech buněk, ve kterých jsou uvedeny číselné hodnoty.
5. 3. 2013
46
Funkce SUMIF a COUNTIF
= SUMIF (sečte buňky vybrané podle zadaných kritérií) = COUNTIF (kolikrát se vyskytuje stejná hodnota v buňkách) Př. Do buňky B13 vložte takovou funkci, která sečte hodnoty ve všech buňkách obsahují číslo 2.
5. 3. 2013
47
Př. Do buňky B13 vložte takovou funkci, která zobrazí počet buněk obsahují číslo 1.
5. 3. 2013
48
Logická funkce KDYŽ – používá se při testování hodnot a vzorců
– obsahuje 3 základní argumenty:
5. 3. 2013
podmínku
akci, která bude provedena, pokud podmínka platí
akci, která bude provedena, pokud podmínka neplatí
49
Př. Do buňky B3 vložte funkci, která ověří, zdali je v buňce B2 zadaná částka rovna anebo větší hodnotě 25 000 Kč. Pokud podmínka bude splněna, napište – podmínka je splněna, pokud podmínka nebude splněna, napište – podmínka není splněna.
5. 3. 2013
50
Př. Do buňky C2 vložte funkci, která zobrazí text nadnormální, pokud průměrná denní teplota, která je uvedena v buňce B2 bude větší než 18,2, v opačném případě zobrazí text podnormální. Následně zobrazte tyto podmínky v buňkách C3 – C7.
5. 3. 2013
51
Ukotvení (uzamčení) řádků
symbol ukotvení
– Zobrazení -> Ukotvit příčky
5. 3. 2013
umožňuje mít zobrazenou část listu a současně zbytek listu procházet (vhodné např. u dlouhých seznamů) pokud chceme např. v následující tabulce ukotvit záhlaví tabulky (tj. řádek 7), musíme tedy klepnout do řádku 8 a pak přejdeme na nastavení Zobrazení -> Ukotvit příčky
v dané nabídce je možné také Ukotvit horní řádek (první sloupec)
Zrušení ukotvení – Zobrazení -> Ukotvit příčky -> 52 Uvolnit příčky
GRAFY
slouží pro vizuální zobrazení dat nejprve vždy vyznačíme oblast dat, ze které chceme vytvořit graf
Základní typy grafů: -
sloupcový – používá se především pro porovnání položek v rámci jedné nebo více kategorií prvků
-
spojnicový – zobrazuje souvislou závislost dvou veličin ve formě křivky, která propojuje datové body. Je vhodný např. pro znázornění trendu v čase
-
výsečový – znázorňuje poměr jednotlivých částí k celku.
5. 3. 2013
53
Př. Z následující tabulky vytvořte sloupcový graf tržeb (včetně DPH) jednotlivých druhů růží.
Nebudeme označovat celou tabulku (viz. zadání). Označíme si tedy následující oblasti:
5. 3. 2013
54
Vložit -> Grafy (sloupcový)
– nyní provedeme další úpravy v grafu (po kliknutí na graf se v Řádku nabídek zobrazí Nástroje grafu -> Návrh, rozložení, formát) – nejprve provedeme změnu v legendě (vidíme, je nevyhovující) tím, že klepneme na položku Formát -> klepneme 2X na první sloupec grafu -> Výplň obrazce -> Červená 5. 3. 2013
55
legenda
klepneme postupně na další sloupce a změníme v nich barvy, aby legenda byla přehledná
5. 3. 2013
56
Nástroje grafu -> Rozložení – provedeme další úpravy – Název grafu, Názvy os, Legenda, Popisky dat, Tabulka dat Název grafu Popisky dat
Osa hodnot (Y)
legenda
Název hlavní svislé osy osy
Osa kategorií (X) 5. 3. 2013
Název hlavní vodorovné osy
57
Osy, Mřížka – možné úpravy
Výplň obrazce
5. 3. 2013
Obrys obrazce
Mřížka
58
Př. Vytvořte následující tabulku (včetně formátování) a dále vytvořte graf, který je zobrazen níže.
5. 3. 2013
59
Řešení: – vybrali jsme si následující buňky (oblast buněk B3:F3 a B9:F9)
– typ grafu -> spojnicový
5. 3. 2013
60
nyní vytvoříme Název grafu, Název os
legendu řady přejmenujeme na Součet (pravým tlačítkem myši klepneme na oblast řady a vybereme položku -> Vybrat data -> Upravit
5. 3. 2013
61
jako poslední krok provedeme Výplň obrazce, graf je kompletní
5. 3. 2013
62
Př. S tabulkou, kterou jste si vytvořili, budeme nadále pracovat. Vytvořte graf, který je zobrazen níže (vložte jej pod předcházející graf)
5. 3. 2013
63
Řešení: – vybrali jsme si následující buňky (oblast buněk B3:F3 a B9:F9)
Typ grafu Sloupcový
5. 3. 2013
64
Nyní vytvoříme Název grafu, název os
jako poslední krok provedeme Výplň obrazce a orámujeme legendu, graf je kompletní – Orámování legendy: klepneme do oblasti Legendy -> Formát -> Styly obrazců
5. 3. 2013
65
5. 3. 2013
66
Př. Vytvořte následující tabulku (včetně formátování). Vypočtěte hodnoty v buňce C17:F17. Vytvořte spojnicový graf se značkami, který bude znázorňovat rentabilitu cizího a celkového kapitálu od ledna po prosinec. Vložte název grafu – Rentabilita cizího a celkového kapitálu a legendu. Další úpravy proveďte podle svého uvážení.
Výsledek: 5. 3. 2013
67
Výsledný graf předcházejícího úkolu
5. 3. 2013
68
Změna názvu hlavní vodorovné osy a vedlejší svislé osy – klepnu na danou oblast a napíšu nový název -> Enter
Kopírování, přesouvání a změna velikosti grafu
5. 3. 2013
69
Formát osy – klepneme pravým tlačítkem myši na osu hodnot (Y) – na ose (Y) budeme chtít např. zobrazit pouze částky v rozmezí 4 000,0 Kč -> provedeme úpravu na částku 2000,0
5. 3. 2013
70
5. 3. 2013
71
Př. Vytvořte následující tabulku (včetně formátování). Vypočtěte hodnoty ve sloupcích D a F. Vytvořte rozložený výsečový graf s prostorovým efektem znázorňující poměr prodeje jednotlivých vozů (nadpis, zobrazte popisky – Za zakončením, výplň obrazce – světle zelená barva, obrys obrazce (zelená barva, tloušťka čáry 3 cm), barevný okraj legendy – oranžová barva). Ukotvěte záhlaví tabulky.
5. 3. 2013
72
Výsledek předcházejícího úkolu
5. 3. 2013
73
Př. Vytvořte tabulku (včetně formátování). Vytvořte sloupcový graf znázorňující výdaje položek v jednotlivých měsících. Vložte vhodné funkce pro výpočet položek celkové výdaje a průměrné výdaje. Dále do buňky G5 vložte vhodnou funkci pro zaokrouhlení, zaokrouhlete danou hodnotu na celé koruny (bez desetinných míst) a nakopírujte do dalších buněk (G6:G9). Ukotvěte záhlaví tabulky. Graf pojmenujte – Rodinné výdaje. Orámujte legendu.
5. 3. 2013
74
Výsledky předcházejícího úkolu
5. 3. 2013
75
TISK
Rozložení stránky -> Vzhled stránky
5. 3. 2013
76
Náhled sešitu (Soubor -> Tisk)
Záložka okraje
Záhlaví a zápatí
CTRL+P
– vlastní záhlaví (zápatí) -> možnost vkládání např. čísla stránky, počet stránek, datumu, času… Vlastní záhlaví (zápatí)
5. 3. 2013
77
List – Oblast tisku
– Tisk: Mřížka, Černobíle, Koncept, Záhlaví řádků a sloupců
5. 3. 2013
Př. Vytiskněte pouze následující tabulku (tabulku jsme si již vytvořili). Vyberte také, aby se tabulka vytiskla černobíle se záhlavím řádků a sloupců
Řešení: Klepněte na záložku List a vyberte oblast buněk, které chcete vytisknout (pro kontrolu se podívejte do Náhledu). Potom zatrhneme položku Černobíle a Záhlaví řádků a sloupců 78
Náhled tabulky
5. 3. 2013
79
Pro zajímavost
Chtěli byste si spočítat v Excelu, kolik dnů jste již na světě?
Řešení: Výpočet v buňce C4: =C2-C3
– Dne 29.2. 2012 jsem již 10 693 dnů na světě.
5. 3. 2013
80
POUŽITÁ LITERATURA
Štěpánková, O.; Vladyková, G. aj. S počítačem do Evropy, Computer Press, Brno, 2007 Chábera, J.; Dannhoferová, J. aj. ECDL – přípravou na testy, Computer Press, Brno, 2012
Průvodce
Hortai, František. Učebnica ECDL – sylabus v5, HFCOMP, 2010
Roubal, Pavel. Informatika a výpočetní technika (2. díl), Computer Press, Praha, 2000 Internet, Nápověda ve Windows 7
5. 3. 2013
81
