UP OLOMOUC 2012
Základy práce s PC Zpracování dat Marek Bednář
Obsah Předmluva ............................................................................................................................................... 2 Microsoft Word – dlouhé dokumenty ............................................................................................... 3 Číslování stránek ............................................................................................................................... 3 Popisky................................................................................................................................................ 6 Seznamy „popisků“ (obrázků a tabulek) nebo stylů .................................................................... 7 Rejstřík ................................................................................................................................................ 8 Microsoft Excel aneb zpracování dat prakticky ............................................................................. 12 Rozdíl mezi vloženou a zobrazenou hodnotou ......................................................................... 12 Rychlejší vkládání dat ..................................................................................................................... 14 Uživatelské seznamy ....................................................................................................................... 14 Řady................................................................................................................................................... 15 Grafické formátování, kopie formátu .......................................................................................... 17 Základy výpočtů .............................................................................................................................. 17 Funkce .............................................................................................................................................. 22 Podmíněné formátování ................................................................................................................ 23 Analýza shora dolů.......................................................................................................................... 24 Grafy ................................................................................................................................................. 26 Seznamy ............................................................................................................................................ 31 Maticové vzorce .............................................................................................................................. 34 Předpovědní funkce........................................................................................................................ 36 Základní statistika ........................................................................................................................... 39 Seznam příkladů:.................................................................................................................................. 52 Přílohy ................................................................................................................................................... 53 Seznam nejčastěji používaných klávesových zkratek ................................................................ 53 Doporučená literatura ......................................................................................................................... 57
1
Předmluva Tato příručka je určena jako doplňkový studijní materiál pro vysokoškolské posluchače se zaměřením na efektivnější zpracování dat s využitím nejběžnějších kancelářských programů Microsoft Word a Microsoft Excel ve verzi 2007. Publikace nenahrazuje uživatelské příručky těchto programů, spíše ukazuje, jak s jejich pomocí řešit problémy, se kterými se běžný uživatel potýká ať už při psaní textu nebo zpracování dat. Zájemce o podrobnější popis uživatelského rozhraní či dokumentaci všech funkcí programů odkazuji na publikace uvedené v přehledu literatury. Obsah publikace je rozdělen do dvou částí. V první demonstruji použití pokročilejších funkcí programu Microsoft Word (předpokládám zde určitou znalost tohoto programu alespoň na úrovni středoškolského učiva), ve druhé se zaměřuji na Microsoft Excel (tady není základní znalost nutná). V závěru v příloze uvádím ještě přehled nejdůležitějších klávesových zkratek vztahujících se nejen ke zmíněným dvěma programům, ale také k aplikacím ve Windows obecně. Struktura jednotlivých kapitol je poněkud odlišná od běžných uživatelských manuálů. Nenajdete zde popisy uživatelských rozhraní, jednotlivých tlačítek apod. Vycházím z problému nastíněného v podobě nějakého specifického příkladu a v rámci jeho řešení poukazuji na nástroje a funkce, které jsou k řešení potřeba. A pokud je k řešení příkladu zapotřebí nějaká teorie nebo vysvětlení nějakého pojmu, uvádím ji vždy jako součást řešení. Uvědomuji si, že výčet příkladů nemusí být vyčerpávající. Nelze pokrýt všechny problémy, se kterými se uživatelé potýkají, ale snad tento přístup naplní záměr celé publikace lépe než teoretické čtení o jednotlivých funkčnostech programů. Pokud čtenář objeví alespoň jeden nástroj, který mu pomůže zpracovat data efektivněji a rychleji, publikace splnila svůj účel.
Ing. Marek Bednář Katedra ekologie a životního prostředí PřF UP v Olomouci
2
Microsoft Word – dlouhé dokumenty Číslování stránek Často se dostaneme do situace, kdy musíme číslovat pouze určitou část dokumentu. Typicky první stránky knihy (práce) číslování stránek nemají. Obecně čísla stránek vkládáme pokliknutím na sekci Záhlaví nebo zápatí a následným výběrem vložení čísla stránek určitého formátu z karty Záhlaví a zápatí.
Takto umístíme čísla stránek na všechny stánky dokumentu. Na kartě Možnosti sice můžeme vybrat jiné číslování na první straně, ale to pořád neřeší případ, kdy potřebujeme, aby se stránek nečíslovalo více. Word řeší tento problém rozdělením dokumentu na tzv. oddíly. Oddíl je jakákoliv přesně určená část dokumentu vyznačující se odlišným formátováním (tedy i např. jiným číslováním stránek) než zbytek dokumentu. Je oddělená od zbytku textu koncem oddílu.
3
Konec oddílu vložíme přes záložku Rozložení stránky a kartu Vzhled stránky – nástrojem Konce. Pro daný oddíl pak vložíme nebo číslování stránek.
Nejčastěji použijeme vložení konce na Další stranu.
Uvedený postup si dokumentujeme na příkladu. Příklad č. 1: Očíslujte stránky dokumentu tak, aby se začalo číslovat až od první
kapitoly. Titulní strana a obsah musí zůstat bez číslování. Řešení: Výchozí stav dokumentu – strany 1 - 3
4
Nejdříve vložíme číslování stránek, poté rozdělíme dokument na dva oddíly. V prvním bude titulní strana a obsah, v druhém pak zbytek textu. Rozdělení provedeme vložením konce oddílu za část obsahu (pak je většinou nutné, stiskem klávesy DELETE, „přisunout“ následující text zpět. Kliknutím do sekce zápatí bude dokument vypadat nějak takto:
Stránkování nebude upraveno, přibudou jen popisky sekcí a oddílů (Záhlaví – Oddíl 1). Pokud bychom nyní číslování stránek v oddílu 1 odstranili, zmizí nám číslování i v oddílu následujícím!!! Proč? Vše má na svědomí další popisek (Stejné jako minulé), který říká oddílu, že cokoliv je provedeno v oddílu minulém, má se přenést i do oddílu následujícího. Tato funkčnost má původ v postupném vytváření dokumentu po jednotlivých kapitolách (oddílech), kdy automaticky předpokládáme pokračování číslování stránek napříč oddíly. Pokud by tato vazba neexistovala, do každého oddílu bychom museli vkládat číslování ručně. Nám se však v dané chvíli tato funkčnost nehodí, tak ji před výmazem stránek z oddílu 1, vypneme. Ujistěte se, že vám kurzor bliká v části zápatí druhého oddílu (vazba Stejné jako minulé funguje nezávisle pro záhlaví i zápatí). Poté zrušte Propojení s předchozím na kartě Navigace.
Tím zrušíte vazbu mezi zápatími a můžete bez obav smazat číslování v oddíle 1. Příklad je tím vyřešen. Další příklady k procvičení: Upravte titulní stranu tak, aby byl tex t zarovnán do středu vertikálně i horizontálně. Upravte jednu stránku dokumentu tak, aby byla naležato.
5
Příklad č. 2: Nastavte prostředí Wordu tak aby, automaticky vkládal popisek tabulky
s automatickým číslováním. Popisek bude ve formátu Tab. číslo popisek.
Popisky Popisek si můžeme představit jako jednotně definovaný formát zápisu např. obrázků nebo tabulek, který chceme v dokumentu používat. Oproti stylu obsahuje tento formát zápisu číslování položek, takže se např. nemusíme starat o číslování, případně přečíslovávání obrázků nebo tabulek. Word vše řeší za nás. Umožňuje dokonce urychlit vkládání popisků pod (nad) objekt automatickým rozpoznáním vkládaného prvku. Pro řešení našeho příkladu využijeme karty Titulky, konkrétně tlačítka Vložit titulek.
Otevře se nám okno:
Tady máme možnost vložení Nového titulku (pokud v seznamu titulků chybí), případně jejich odstranění.
6
Kliknutím na Automatický titulek můžeme příslušnému popisku vybrat, při vložení kterého objektu (v našem případě to bude Tabulka aplikace Microsoft Word) se má objevit a kde.
Při vložení jakékoliv nové nebo další tabulky je popisek automaticky přidán. Tab. 1
Seznamy „popisků“ (obrázků a tabulek) nebo stylů Někdy bývá výhodné, pro lepší orientaci v dokumentu, vytvořit samostatný přehled obrázků nebo např. tabulek. V zásadě je možné vytvořit „obsah“ libovolného textu s určitým stylem. Např. se rozhodneme pro obrázky používat styl Obrázek. Pro vložení obsahu stylů Obrázek použijeme následující postup. Využijeme karty Titulky v záložce Odkazy:
7
Po kliknutí na
se otevře okno:
Tady vybíráme patřičnou šablonu, případný popisek anebo přes tlačítko Možnosti… označujeme styl, na který se má v seznamu odkazovat. V našem případě to bude takto:
Po výběru a pokliknutí na OK již dostaneme vlastní seznam. Jinou možností je využít stávajících popisků. Tady stačí jen vybrat příslušný popisek titulku, šablonu a potvrdit.
Rejstřík Pro rychlou orientaci v delších dokumentech Word umožňuje vytvoření rejstříku. Pro vytváření máme k dispozici pás karet Rejstřík v záložce Odkazy . 8
Vytváření rejstříku je dvoukrokové, v prvním kroku musíme nějak označit položky, na které chceme v rejstříku odkazovat, v druhém kroku pak vložíme samostatný rejstřík. Označovat položky můžeme manuálně označením textu a kliknutím na ikonku Označit položku (případně využitím klávesové zkratky ALT+SHIFT+X). Otevře se následující okno, kde stačí jen určit, zdali nám jde o označení pouze této jediné položky (zvolíme Označit), nebo jestli chceme označit všechny takovéto položky v rámci celého dokumentu (zvolíme Označit vše).
Musí existovat způsob, jak si Word zapamatuje, že zrovna daný text má být položkou rejstříku. Word to řeší prostřednictvím formátovacích znaků, konkrétně pomocí kombinace { XE “označení_položky_v_rejstříku”}. Formátovací znaky se netisknou, po označení slov se však dokument Wordu přepne do režimu se zobrazením formátovacích znaků, který přepínáme prostřednictvím tlačítka . Označení položky v rejstříku nemusí odpovídat vlastnímu označenému slovu. Například označíme slovo Morava a v rejstříku chceme mít Řeky. Pak bude text při zapnutém formátování vypadat následovně: Morava { XE “Řeky“}. Pro delší dokumenty bývá výhodný jiný způsob značení, a to prostřednictvím tzv. konkordenčního souboru. Je to wordovský soubor, kde máme připraveny všechny
9
položky, které se mají hledat s jejich rejstříkovými ekvivalenty, ve formě dvousloupcové tabulky. Morava Moravy Fluvizem
Řeky Řeky Řeky:půdy:nivní
Čechy
Čechy
V prvním sloupci jsou položky, které se budou v dokumentu vyhledávat a označovat jako odkazované položky rejstříku. K vyhledané položce se přidá formátovací řetězec s odpovídajícím ekvivalentem uvedeným v druhém sloupci. Všimněte si formátování položek rejstříku v pravém sloupci. Oddělovací znak „:“ tady funguje jako oddělovač jednotlivých úrovní rejstříku. Slovo Fluvizem (přesněji strana, kde se nachází) bude přidána do příslušné rejstříkové úrovně Řeky půdy nivní číslo_strany Vlastní zobrazení může být samozřejmě jiné, záleží na výběru šablony rejstříku. Výsledný
konkordenční
soubor
načteme
do
Wordu
prostřednictvím
a po zobrazení rejstříkového okna kliknutím na Automaticky…
10
tlačítka
Poté stačí jen vybrat příslušný konkordenční soubor. Po výběru se všechny položky z tabulky automaticky vyhledávají a značkují. Rejstřík však ještě není vytvořen!!! K tomu je zapotřebí druhý krok – vlastní vložení rejstříku. Stačí opět kliknout na šablonu a potvrdit stiskem klávesy OK.
, vybrat
11
Microsoft Excel aneb zpracování dat prakticky Příklad č. 3: Vytvořte kalendář podle vzoru.
Řešení: Při řešení příkladů v Excelu se budeme držet několika zásad: •
Řešení musí být obecné. Postup řešení nesmí být vázán na vstupní hodnoty, tak aby jejich změnou bylo řešení stále platné. Tzn. používejte konstanty, jen pokud je to nezbytně nutné.
•
Snažíme se o co nejrychlejší řešení. Místo abychom vkládali vzorce do všech buněk, vložíme je pouze do jedné a zbytek nakopírujeme.
•
Pokud vám příklad připadá složitý, nebojte se jej zjednodušit a rozepsat. Buněk na to máte dost!
Rozdíl mezi vloženou a zobrazenou hodnotou Hodnoty v Excelu zadáváme do buněk. Upravovat je můžeme přímo v buňce (dvojklikem, případně stiskem F2) nebo v tzv. řádku vzorců.
12
V řádku vzorců je uvedena hodnota, kterou jsme do buňky zadali. V našem případě datum 1. 5. 2012. Odpovídající hodnota v buňce B4 však může být zobrazena jinak. Např. jako úterý nebo 1. květen 2012 v závislosti na datovém formátu buňky. Datový formát buňky nastavíme přes pravé tlačítko myši a volbu Formát buněk, případně klávesovou kombinací CTRL+SHIFT+1. Dostaneme následující okno:
Vložený text může být různého typu, proto Excel zavádí kategorie typů (obdobné datovému typu v databázích). Vstupem může být měna, čas, datum či procenta. Excel se snaží na základě vloženého textu vše rozpoznat a automaticky přiřadit, což se mu však ne vždy daří. V případě potřeby je nutné si upravit typ ve formátu buněk po svém. Výhodu použití typů dokumentujeme na příkladu měny. Pokud označíme číslo typem měny, bude se nám vedle něho automaticky zobrazovat symbol měny, ale Excel dále bude chápat hodnotu buňky jako číselnou a umožní nám provádět číselné operace. Takže místo nic neříkajících čísel např. 55, 66 uvidíme v buňce 55Kč, 66$ při zachování výpočetních možností. Navíc Excel umožňuje vložit vlastní formátování hodnoty, což využijeme v našem případě. Potřebujeme zobrazit hodnotu data ve formátu 1. 5. 2012 jako 1. května 2012 případně jako
13
úterý. Prvním řešení je pouhý výběr příslušného formátování z kategorie Datum, druhým je vlastní zformátování hodnoty pomoci předpisu: dddd, d dd ddd dddd
kde d značí dny. Počet písmen určuje formát. 1 01 út úterý
Podobně se dá pracovat i s měsíci (m) a roky (r). Příklad č. 4: Zjistěte pomocí formátu buněk, který den v týdnu jste se narodili.
Rychlejší vkládání dat Často potřebujeme vložit data s určitou logickou posloupností. Čísla zvyšující se o jedničku, data následující za sebou, dny v týdnu nebo měsíce. Excel nám usnadňuje vložení takovýchto hodnot dvojím způsobem: •
pomocí uživatelských seznamů
•
pomocí řad
Uživatelské seznamy Zadáte-li do buňky slovo pondělí a potáhnete za ouško 1 , následující buňky budou obsahovat další dny v týdnu – úterý, středa atd. Že by náznak inteligence počítače? Bohužel tomu tak není, počítač má někde uložený tzv. seznam – např. dnů v týdnu, nebo měsíců a kdykoliv zapíšeme prvek z tohoto seznamu a potáhneme, automaticky vkládá následující prvek seznamu v pořadí. Poslední prvek přechází cyklicky opět na první prvek.
Do seznamů se dostaneme volbou v levém horním rohu aplikace. Dále Možnosti aplikace Excel, položka Oblíbené, Upravit vlastní seznamy.
1
Ouško je pravý spodní roh buňky -
14
.
Tady můžeme přidávat, upravovat nebo případně odstraňovat seznamy položek, které nás zajímají. Pokud si chcete vytvořit nový seznam, klikněte na položku NOVÝ SEZNAM a vpravo napište jednotlivé položky oddělené klávesou ENTER. Rovněž můžete naimportovat seznam z vyplněných buněk kliknutím na tlačítko Importovat.
Řady Excel nabízí automatické vytvoření aritmetických, geometrických nebo třeba kalendářních řad. Nejjednodušší způsob, jak vytvořit posloupnost řekněme 1..100, je vložit jedničku a pak stiskem CTRL a tažením za ouško natáhnout oblast do požadované velikosti. Můžete rovněž řadu zadat prvními dvěma hodnotami. Např.: 1 a 3. Označíte-li obě buňky a potáhnete za ouško, dostaneme lichá čísla 1,3,5,7 … Program automaticky předpokládá, že chceme vložit součtovou řadu (řadu, kde je každá další hodnota zvýšena o nějakou konstantu). Pokud potřebujeme vložit třeba geometrickou řadu – např.: 2,4,8,16,32 …, natahujeme za ouško pravým tlačítkem myši a upřesníme typ řady, případně i krok ve formuláři:
15
Stejným způsobem je možné pracovat s kalendářními daty, což využijeme při tvorbě kalendáře.
16
Grafické formátování, kopie formátu Určitě znáte klasické kopírování hodnot, přes CTRL+C, CTRL+V. Kromě kopírování hodnot můžeme kopírovat i vlastnosti buňky – barvu písma, pozadí i datový formát. Ke kopii formátu nám stejně jako např. ve Wordu pomůže ikona „štětky“ . Formát přeneseme jednoduchým poklikem na zdrojové buňce a dalším na buňce (buňkách cílových). Dvojité pokliknutí zapíná kopii formátu do doby, než ji kliknutím vypneme. Hodí se pro kopírování formátu na více nesourodých oblastí.
Základy výpočtů Příklad č. 5: Přišli jsme k nějakým penězům (200000 Kč) a chceme je nějak zúročit.
Vybereme si banku poskytující dlouhodobý úrok 3%. Navíc každý další rok budeme k naspořené částce přidávat další sumu, řekněme 10000 Kč. Otázka zní: Kolik budu mít na konci 10, 15, 20 roku? A kolik bych musel ročně přispívat, abych měl na konci desátého roku 1000000 Kč?
Excel je velice mocný nástroj na výpočty. Už v dobách svého vzniku bylo jeho cílem co nejjednodušeji a nejrychleji počítat, zvládal nejběžnější operace typu plus, mínus, krát, děleno. Dnes umožňuje mnohem pokročilejší výpočty zavedením funkcí v nejrůznějších kategoriích. Ale nepředbíhejme. Snad nejjednodušší operace je sčítání. Nejrychlejší metodou, jak sečíst nějaká čísla, např. pod sebou, je využití ikony sumy
na záložce Domů, kartě Úpravy.
Je to vlastně jakási zkratka na vložení funkce SUMA, ale o funkcích až později. 17
Jiný způsob, jak docílit součtu argumentů je využít operaci sčítání pomocí operátoru +.
Všimněte si dvou věcí. Prvním znakem v buňce je znak = a jednotlivé hodnoty nejsou do buňky vkládány přímo, ale jako odkazy (adresy, kde hodnoty mám). Toto je typické pro Excel a je to proto, že Excel chce být co nejobecnější (resp. co nejobecnější řešení bychom měli psát my). Pokud zjistíme, že jsme se spletli a zadali 15 místo 16, stačí jen přepsat hodnotu a výsledek zůstane nezměněn!!! Toto je jedna z největších výhod Excelu. Další operátory a výsledky operací vidíme v tabulce: Operand_1 Operátor Operand_2 Vzorec
Operátory
10
^
2
100 Aritmetické
15
*
6
20
/
7 2,857142857
25
+
8
33
30
-
9
21
35
&
10 3510
Zřetězení
40
=
40
Relační
45
<
45 NEPRAVDA
50
>
45
PRAVDA
55
<=
55
PRAVDA
60
>=
60
PRAVDA
65
<>
65 NEPRAVDA
90
PRAVDA
Teď už víme dost, pro řešení našeho příkladu. Řešení: Nejdříve si do sešitu vložíme známé proměnné (úrok, počáteční částka, kolik přidáváme). Dále si připravíme tabulku, která nám umožní sledovat vývoj našich financí v letech budoucích.
18
Sloupec roků jsme vyplnili způsobem popsaným v části seznamů pomocí tlačítka CTRL a tažení za ouško. Pozn.: Výše řešený příklad by se dal řešit i pomocí nějaké finanční funkce, ale našim záměrem je v dané chvíli využívat jen operátorů. Buňka C5 obsahuje vzorec =I2 (každý vzorec je uvozen znakem =). Výhodou tohoto přiřazení je opět možnost kdykoliv měnit hodnotu v jedné buňce a vše se mi automaticky promítne i do vzorečků. Ale co s oblastí (oblast je termín pro větší skupinu buněk) D6:D14 (dvojtečka slouží k oddělení levého horního rohu a pravého spodního rohu oblasti)? Mají tady být hodnoty 10000, do buňky D6 tedy vložíme =F2 a pak potáhneme (stejně jako u seznamů) za ouško (alternativou tažení může být poklepání, které automaticky vyplní hodnoty ve sloupci až do prvního místa, kde žádná hodnota není). Výsledek vidíme na obrázku:
Místo očekávaných 10000 je 0. Jak to?
19
Klikneme-li dvakrát do buňky D7 (tímto způsobem můžeme opravit vzorec, alternativou je použití klávesy F2 nebo oprava přímo v řádku vzorců), vidíme, že se vzorec odkazuje na buňku pod buňkou, kterou chceme použít. Toto je záměr Excelu, při kopírování (natahování) vzorců dochází k automatickému posunu odkazů ve vzorci!!!
Většinou je to užitečné, ne však vždy. Jak tomu zamezit? Využitím takzvaných ukotvovacích znaků $, které vložíme před sloupec či řádek nebo obojí. Vzorec v buňce D6 by pak mohl vypadat následovně: =F2 (při posouvání nastane chyba) =$F2 (posouvám dolů, mění se řádky – tedy čísla – opět nastane chyba) =F$2 (správně) =$F$2 (rovněž dává správné výsledky, ale dolar u sloupce je zbytečný) Výsledek pak bude následovný:
Je nějaký způsob, jak vložit $ rychleji? Jde to pomocí klávesy F4, postupným mačkáním „rotujeme“ předsazení $ před sloupcem a řádkem. Kolik budu mít na konci roku (předpokládám, že úrok je roční)? Vložím vzorec: =(C5+D5)*(1+$C$2) 20
Na začátku každého dalšího roku mám to co na konci předcházejícího. Buňka C6=E5. Natáhneme za ouško a doplníme tabulku (na obrázku na levé straně je tabulka v jiné podobě – zobrazení vzorců).
Takže na konci desátého roku budu mít 373422 Kč. Poslední otázka zní: Kolik musím ročně přidávat, abych měl na konci desátého roku 1000000? Tady s výhodou využijeme analytických nástrojů, které Excel poskytuje, konkrétně nástroj
Hledání řešení (Data-Datové nástroje)
.
Dále vše nastavíme vše dále obrázku.
21
Nastavovanou buňkou je ta buňka, kde chci docílit nějaké hodnoty (konec 10. roku). Měněná buňka je ta, se kterou se bude hýbat, abych docílil výsledku – F2. Po spuštění dostanu částku: 69880 Kč.
Funkce Ne vždy si vystačíme s využitím operátorů. Excel poskytuje k dispozici celou řadu připravených funkcí, které po zadání vstupních argumentů vrátí očekávaný výsledek. Funkce jsou rozděleny do kategorií podle použití.
Každá funkce má nějaký název a vstupní parametry - nazev(par1;par2;par3…). Jednotlivé parametry jsou odděleny středníkem. Např. SUMA(A1:A10) vypočte součet buněk oblasti A1-A10. Funkci můžete zadat rovněž kliknutím na ikonu s využitím kontextové nápovědy.
v řádku vzorců nebo přímo začít psát
Příklad č. 6: Zjistěte průměr hodnot v buňkách A1:A10. Řešení: =PRUMER(A1:A10) Příklad č. 7: Vygeneruj 6 náhodných čísel v rozsahu 1-49. Vedle každého z nich vypiš,
jestli je liché nebo sudé. Použij funkce MOD. Řešení: Funkce MOD(a;b) navrací zbytek po dělení a/b.
Funkce NÁHČÍSLO() (všimněte si, že funkce nemusí mít žádné parametry) navrací hodnotu v intervalu <0,1). Funkce CELÁ.ČÁST(a) vrací celou část případného desetinného čísla. 22
Rozsah 0-0.99 řeší vzorec =NÁHČÍSLO() 0-48.999 =CELÁ.ČÁST(NÁHČÍSLO()*49) --- tady si povšimněte vložení funkce do funkce, nejdříve se vypočte desetinné číslo patřičného rozsahu, z něhož je dále vrácena pouze celá část 1-49
=1+CELÁ.ČÁST(NÁHČÍSLO()*49)
Příklad č. 8: Zjistěte průměrné povedené výkony závodníků ve skoku do dálky.
Můžete využít funkci COUNTIF.
Řešení: Do průměru nemůžeme započítávat nulu (nepovedený pokus). Průměr je vlastně součet hodnot podělený jejich počtem. Stačí tedy spočítat kolik hodnot je nenulových – pomocí funkce COUNTIF a sumu těchto skoků podělit jejich počtem.
Podmíněné formátování Excel pro usnadnění orientace ve větším množství dat umožňuje tzv. podmíněné formátování. O co jde? Jde o obarvení, změnu stylu písma či velikosti – prostě nějakou změnu grafického formátu buňky, a to buď na základě hodnoty (např. pokud je výsledek 23
záporný, obarvi hodnotu na červeno) nebo vzorce (pokud jsem v lichém řádku podbarvi mě zeleně). Příklad č. 9: Sudé řádky buněk podbarvi zeleně, liché řádky žlutě.
Řešení: Při analýze můžeme postupovat následovně. Je vždy dobré se nejdříve zamyslet než hned něco ťukat do klávesnice a ztratit se v tom!!!
Analýza shora dolů Myšlenkový postup: Potřebujeme jinak obarvit sudé a liché řádky. Jak zjistím, na kterém řádku právě jsem? A když to budu vědět, jak zjistím, jestli je to řádek lichý nebo sudý. A když to budu vědět, jak jednotně nastavit podmíněné formátování? Pro určení na kterém řádku aktuálně jsem, použijeme funkci ŘÁDEK, která vrací číslo řádku. Pro určení sudosti (lichosti) využijeme funkci MOD, když vydělíme sudé číslo 2, zbytek po dělení bude 0. V případě lichého čísla pak 1. Takže pro jednu jedinou buňku – řekněme A1 – nám vzorec =MOD(ŘÁDEK(A1);2)=0 vrací hodnotu NEPRAVDA – nejedná se tedy o sudý řádek. Jak bylo řečeno výše, buňka nebo oblast buněk se může graficky zformátovat na základě nějaké hodnoty, případně na základě nějaké podmínky. •
24
Když je zkoumaná buňka na sudém řádku, bude zelená. Jinak bude žlutá.
První krok je vybrat oblast buněk, pro které se má nastavit grafické formátování.
Dále pak na záložce Domů a kartě Styly klikneme na Podmíněné formátování a vybereme Nové pravidlo… a Určit buňky k formátování pomocí vzorce.
.
25
Podmíněné formátování pro více buněk funguje na základě zadání jednoho jediného vzorečku, a to pro buňku v levém horním rohu vymezené oblasti!!! Další buňky budou obarveny rovněž podle tohoto vzorce, s tím rozdílem, že veškeré odkazy v něm budou relativně posunuty – podobně jako bychom kopírovali vzorec sám. Např. pokud mám ve vzorci odkaz A1 – pro buňku napravo se bude počítat s odkazem B1. Platí zde samozřejmě pravidla ukotvování – pokud ve vzorci nebude A1, ale $A1 – pak i ve vedlejších buňkách napravo či nalevo bude $A1! Pro náš případ ted y stačí zad at vzorec: = =MOD(ŘÁDEK(A1);2)=0 a nastavit výplň na zelenou.
Obdobně by se přidáním dalšího pravidla pro liché řádky tyto obarvily žlutě.
Grafy Příklad č. 10: Podle předlohy vytvořte tabulku, která bude informovat o průměrných
teplotách v každém měsíci v průběhu jednoho roku. Údaje promítněte do grafu.
Řešení: Pro nejnižší, nejvyšší a průměrnou teplotu použijeme funkce postupně MIN, MAX a PRŮMĚR. Dále upravíme formát jednotlivých buněk s naměřenou teplotou. Vybereme buňky a přes pravé tlačítko myši nastavíme vlastní formát ve tvaru #” °“. Mřížka zde zastupuje číslo, další text v uvozovkách pak jednotku. Podobně v jiných případech můžeme k číslu přidat jakýkoliv text a s buňkou se dále počítá jako s číslem.
26
Pro vložení grafu stačí označit buňky, na záložce Vložení vybrat typ grafu – tady spojnicový a dostaneme následující náhled.
Teplota 40 ° 20 ° °
Teplota
-20 °
Sada Office od verze 2007 rozděluje menu do tzv. ribbonů (v textu se na ně odkazuji jako na záložky). Tento přístup osobně neshledávám příliš šťastným, nalezení konkrétní věci může být složitější než ve verzích dřívějších. Po kliknutí na objekt (v našem případě graf) se přidávají další záložky, u grafu je to Návrh, Rozložení a Formát. Nás bude zajímat část Rozložení, kde zrušíme popis legendy. Dále bychom chtěli přidat, pro lepší orientaci, vynášecí čáry. Najdeme je na kartě Analýza a Čáry. Popisky hodnot přidáme přes Popisky a Popisky dat. Výsledkem bude následující graf:
27
Teplota 40 ° 20 ° °
-3 °
-1 °
8°
17 °
18 °
23 °
27 °
24 °
19 °
14 °
9°
3°
-20 °
Někdy je potřebné odhadnout hodnoty, které se nacházejí mimo sledovaná měření. Případně je potřeba odhadnout trend určitého vývoje. Excel toto řeší zabudováním speciálních předpovědních funkcí typu LINTREND, LOGLINTREND, FORECAST a dalších, ale doplňovat trend můžeme i graficky. A to s využitím tzv. spojnic trendu. Spojnice trendu je křivka, která se snaží zachytit trend naměřených hodnot. Výběr křivky se provádí na základě minimalizace vzdálenosti naměřených hodnot od křivky (resp. mocninou jejich vzdálenosti), ale to už bychom zasahovali někam jinam. Křivku vložíme pokliknutím pravým tlačítkem myši na datovou řadu a následným výběrem Přidat spojnici trendu …
28
Typ trendu nám však Excel nevybere sám, musíme ho na základě známých hodnot stanovit. Pro správný výběr nám může posloužit hodnota spolehlivosti R, která udává procento, jakým je rozptyl naměřených hodnot (teplota) vysvětlen změnami hodnot nezávislé veličiny (čas). Koeficient nabývá hodnot od 0 do 1. Čím je vyšší, tím je model kvalitnější. V našem případě pro lineární model vychází R2=0,085, pro polynomický model řádu 2 R2=0,9481, což už je vysoká přesnost, vyšší řády by hodnotu spolehlivosti sice ještě mírně povýšily, ale za cenu přílišného zesložitění regresní rovnice, kterou si můžete volbou dole také zobrazit. Vybereme tedy polynomický model 2. řádu, okno zavřeme a dostaneme následující výsledek:
Teplota 30 ° 20 ° 10 ° ° -10 °
17 ° 8° -3 °
-1 °
18 °
23 °
27 °
y = -0,8387x2 + 11,707x - 17,5 R² = 0,9481 24 °
19 °
14 °
9°
3°
Příklad č. 11: Vytvořte graf zachycující rozlohu a hustotu obyvatel jednotlivých
kontinentů.
Řešení: Tady nejdříve musíme vybrat nesourodou oblast tabulky, ze které bude graf vytvářen. Dosáhneme toho stisknutím tlačítka CTRL při výběru nesousedící oblasti grafu:
29
Pak známým postupem vložíme sloupcový graf a dostaneme následující obrázek: 160 000 140 000 120 000 100 000 80 000 60 000 40 000 20 000 0
rozloha obyv/km2
160 000 140 000 120 000 100 000 80 000 60 000 40 000 20 000 0
30
Evropa Asie Afrika Severní Amerika Jižní Amerika Australie a… Antarktis Celkem
Vidíme, že sloupce pro hustotu nejsou téměř vidět. V tomto případě Excel přichází s možností využití vedlejší řady y na pravé straně grafu. Musíme vybrat datovou řadu hustoty (klidně i v legendě, pokud jsou sloupce téměř neviditelné) a pravým tlačítkem myši upravit její formát, v možnostech datové řady vykreslovat vše na vedlejší ose.
70,00 60,00 50,00 40,00 30,00 20,00 10,00 0,00
rozloha obyv/km2
Přesto výsledný graf zůstává dosti nepřehledný. Vhodnější by bylo zvolit pro datovou řadu hustoty jiný typ grafu. Vybereme tedy datovou řadu a tentokrát dáme změnit typ grafu – na spojnicový. Výsledkem je už čitelnější graf. 160 000 140 000 120 000 100 000 80 000 60 000 40 000 20 000 0
70,00 60,00 50,00 40,00 30,00 20,00 10,00 0,00
rozloha obyv/km2
Seznamy Excel neumí pouze počítat. Je i zajímavou alternativou pro zpracování většího množství dat, oblasti, která je spíše určená jiným, databázovým programům. Příklad č. 12: Zobrazte všechny filmy dobrodružného žánru, které natočil režisér
Spielberg,
Seznamy nebo chcete-li databáze jsou nejčastěji reprezentovány tabulkou se záhlavím. Jednoduchým přepnutím stylu zobrazení záhlaví do režimu filtrů můžeme určovat podmínky zobrazení záznamů. Přepínáme přes záložku Data ikonou . Pokliknutím na trojúhelník u záhlaví určíme potřebné podmínky (dobrodružný film a režisér).
31
Všimněte si, že vyfiltrované záznamy se zobrazují modře. Filtry můžeme kombinovat nebo rušit přes vymazání filtru
.
Někdy nám však prosté vyfiltrování nestačí. Seženeme hromadu dat s velkým množstvím atributů (záhlaví), ze kterých však potřebujeme jen některé. Příklad č. 13: Vyberte knihy, které mají v názvu moře a nestojí více než 150 Kč.
Nejdříve si připravíme oblast kritérií v podobě záhlaví nebo části záhlaví z tabulky. Měly by se tady objevit všechny atributy, na které se vztahují podmínky (Název, Cena). Tady doporučuji záhlaví spíše překopírovat, i drobný překlep zamezí správnému výběru. Dále připravíme záhlaví tabulky, kam se má kopírovat. Atributy z tabulky původní, které nepotřebujeme, zde neuvádíme!
32
Do oblasti kritérií zadáme naše podmínky (mezi podmínkami na jednom řádku platí logická vazba a zároveň; pokud máme podmínky na více řádků, platí mezi řádky logická vazba nebo). V prvním případě podmínky názvu použijeme tzv. zástupný znak *, tento znak zastupuje 0 nebo více libovolných znaků. Např. *moře* tedy najde všechny výskyty moře v názvu. Vlastní výběr zahájíme pokliknutím na . Před spuštěním je vhodné stát v původní tabulce, oblast tabulky v dalším kroku se nám určí automaticky.
V oblasti kritérií vybíráme záhlaví i podmínky, v oblasti Kopírovat do vybíráme pouze záhlaví! Našemu požadavku vyhovuje pouze jedna kniha.
Někdy chceme vybrat z tabulky data na základě kritéria argumentu, který přímo v tabulce není, ale dá se z jiného argumentu tabulky odvodit. Příklad č. 14: Dejme tomu, že máme vybrat z databáze filmů komedie s premiérou po
roce 2006. Rok premiéry v tabulce přímo není, lze však odvodit z atributu datum premiéry.
Sice bychom do tabulky mohli přidat nové pole Rok premiéry, které vypočteme s pomocí funkce ROK, a postupovat jako v předešlém případě, ale někdy není úplně vhodné měnit původní tabulku. Ukážeme si jiný způsob řešení. Tady uvidíme výhodu použití výběru podle kritérií oproti běžnému použití filtrů, kde něco podobného není možné. Řešení: Podmínku žánru zadáme jako obvykle, místo atributu Premiéra použijeme Rok (vzorec) nebo jakýkoliv jiný popis, políčko můžeme nechat i prázdné. Místo podmínky zadáme vzorec, 33
vztažený na první řádek tabulky, který vrací hodnotu PRAVDA nebo NEPRAVDA. Tentýž vzorec, s případným relativním posunem argumentů oproti prvnímu řádku, se aplikuje pro všechny záznamy. Do výsledné tabulky se dostanou pouze ty záznamy, které vrací hodnotu PRAVDA, samozřejmě pokud jsou ostatní běžné podmínky na řádku splněny.
Vlastní způsob spuštění výběru je stejné jako v předchozím případě.
Maticové vzorce Jedním z opomíjených nástrojů zpracování dat je využití maticových vzorců. Příklad č. 15: Zjistěte celkový zisk i zisk jednotlivých prodejců.
Běžně bychom příklad řešili zadáním jednoduchého vzorce součinu a protažením vzorce přes ouško s následovnou sumací pomoci funkce SUMA. Maticové vzorce řeší úkol jinak. Nemusíme vzorce kopírovat, tahat za ouško apod., vše zadáváme jen jednou. Princip je jednoduchý. Místo argumentu reprezentovaným odkazem na jedinou buňku zadáme oblast buněk. Výsledný zisk je součinem rabatu a obratu. Obrat máme v buňkách B2:B5, rabat pak v oblasti B2:B5. Před vložením výsledného vzorce musíme vybrat buňky, kde má být vzorec vložen (D2:D5)! Teprve pak vložíme vzorec a zakončíme nikoliv stiskem ENTER, ale stisknutím CTRL+SHIFT+ENTER. Podle této kombinace se někdy maticové vzorce nazývají CSE vzorci. Vzorce poznáme podle složených závorek na začátku a na konci (na obrázku je zobrazení v režimu zobrazení vzorců).
34
Větší čitelnosti můžeme dosáhnout pojmenováním oblastí. Místo rozsahu B2:B5 budeme ve vzorcích používat pojmenování Obrat. Jak tedy pojmenujeme oblast? Nejjednodušší je vybrat buňky, vepsat pojmenování do levého horního rohu a potvrdíme stiskem ENTER. K zobrazení přehledu všech pojmenování se dostaneme stiskem CTRL+F3.
Výsledný příklad pak bude vypadat takto:
Výhodou CSE vzorců je také možnost zjednodušeného zápisu funkce (např. sumy zisků) v jednom vzorci, a to dokonce bez nutnosti výpočtu dílčích zisků.
35
CSE vzorce jsou mocným nástrojem, můžeme je použít i jako alternativu výběru záznamů podle kritérií a jejich následné zpracování. Příklad č. 16: Jaký obrat měl prodejce Jirka v oblasti sportu před 31.3.2012?
Pro větší čitelnost nejdříve pojmenujeme oblasti obchodníků, činností, částek a datumů. Vše pak lze zapsat do jednoho vzorce pomocí funkce SUMA a kombinace podmínek. Využijeme zde vlastnosti Excelu, který reprezentuje hodnotu PRAVDA jako 1 a hodnotu NEPRAVDA jako 0. Kombinaci podmínek můžeme zapsat jako podm1*podm2*podm3*…
Předpovědní funkce Příklad č. 17: Jaký lze předpokládat prodej při navýšení nákladů na reklamu na 100,
110, 115 a 120 tis. Kč? Vycházejte z tabulky nákladů za reklamu a odpovídajícího prodeje.
36
Řešení: Jednu z možností jak odhadovat další vývoj situace jsme si ukázali v části věnované grafům s využitím spojnic trendu, což jsou extrapolované křivky průběhu nějaké datové řady. Excel však umožňuje vše řešit i pomocí speciálních předpovědních funkcí, se kterými se teď seznámíme. Jde o funkce FORECAST, LINTREND případně LOGLINTREND. Obecně mají podobné vstupní i výstupní parametry, pracují s množinou hodnot a většinou se využívají s maticovými vzorci, se kterými jsme se seznámili v předchozí části. Syntaktické zápis funkce včetně atributů (UrcenyVstup;ZnamyVystup;ZnamyVstup), kde
je
následující:
FORECAST
UrcenyVstup je oblast hodnot, pro které chceme zjistit nějakou předpověď(v našem případě – hodnota investice do reklamy – 100, 110, 115 a 120 tis. Kč). ZnamyVystup je oblast hodnot, které produkují výsledky uvedené v parametru ZnamyVystup (při investici tolik a tolik do reklamy (vstup), docílím takového a takového prodeje (výstup)). Takže pro naše řešení zadáme:
Funkce LINTREND je funkcí lineárního trendu a je velice podobná funkci FORECAST. Jediným rozdílem je, že na rozdíl od funkce FORECAST umí zohlednit více vstupních parametrů. Otázkou je, proč vůbec používat funkci FORECAST, když funkce LINTREND řeší totéž a více. Příklad č. 18: Jak velký můžeme očekávat prodej zmrzliny, při stanovaném počtu
zákazníků, teploty vzduchu, vody a délce slunečního svitu? Vycházejte z následující tabulky.
37
Řešením je prostřednictvím funkce LINTREND jednoduché.
Všechny tyto příklady samozřejmě předpokládají lineární závislost mezi vstupními a výstupními parametry. Čím více vložím do reklamy, tím vyšší výnos očekávám. Ve skutečnosti samozřejmě všechno bývá trošku jinak. Pokud od začátku víme, že vztah mezi vstupními a výstupními parametry není lineární, ale logaritmický, použijeme funkci LOGLINTREND.
38
Základní statistika Lze Excel využít pro získání základních statistických údajů jako je průměr, medián, odchylka apod.? Samozřejmě vždy můžete využít statistické funkce typu PRŮMĚR nebo MIN, ale pro získání celkového přehledu by byl tento postup zbytečně zdlouhaví. Excel pamatuje na zpracování dat a zavádí speciální nástroje v podobě doplňků, které slouží k řešení podobných a nejen statistických problémů. Pro naše potřeby využijeme doplněk Analýza dat. Největším problémem pro studenty je toto nastavení najít (standardně po instalaci totiž zapnuto není). Zapíná se přes kliknutí na , poté zvolíte možnost Možnosti aplikace Exel, Doplňky, Přejít a zatržení vlastního doplňku Analytické nástroje.
39
Pokud vše provedete správně, k záložce Data se připojí nová karta Analýza. Kliknutí na Analýzu dat pak ukáže nejrůznější možnosti, které Excel pro analýzu dat nabízí.
40
Příklad č. 19: Z poskytnutého vzorku dat – přehledu vítězek Oskara v letech 1970-2001
a věku, kdy tohoto ocenění dosáhly, zjistěte následující základní statistické údaje – průměr, medián, max, min, směrodatnou odchylku věku ocenění.
41
Řešení: Použijeme nástroj popisné statistiky, vyplníme patřičné kolonky podle obrázku a dostaneme požadovaný přehled.
Příklad č. 20: Nabízí se otázka typu, existuje nějaké typické období v životě herečky,
kdy má větší šanci získat Oskara? Řešení:
K zodpovězení tohoto typu dotazu můžeme použít Histogram, případně Krabicový diagram, o kterém se zmíníme později. Histogram je graf, který nám ukazuje četnost výskytu jevu v určitých definovaných skupinách. Jev je v našem případě věk ocenění, skupiny jsou určité intervaly věku, které si stanovíme. Pohled do tabulky nám ukazuje, že nejmladší herečka, která obdržela Oskara měla 21let, nejstarší pak 80. Zvolíme tedy rozsah po pěti letech od 20 do 80.
42
1) Někde v Excelu si předepíšeme hodnoty 20, 25 …80
2) Z Analýzy dat vybereme nástroj Histogram. Vyplníme vstupní data a hranice tříd (intervalů), zatrhneme vytvoření grafu a potvrdíme stiskem tlačítka OK. Protože histogram by neměl mít sloupce oddělené mezerami, klikneme na datovou řadu pravým tlačítkem myši, vybereme formát datové řady a nastavíme šířku mezery na 0. Upravíme výplň na prázdnou a nastavíme styl a barvu obrysové čáry. Výsledek zachycuje následující obrázek.
Z histogramu vidíme, že největší šanci získat Oskara mají ženy herečky v období od 30.-35. roku života. Histogram je natočený doleva, což znamená, že větší šance získat Oskara mají ženy v mladším věku. Mohli bychom učinit závěr, že Hollywood oceňuje herečky mladé a atraktivní.
43
Příklad č. 21: Zjistili jsme, jak jsou na tom ženy, herečky s jejich šancemi na získání
Oskara, ale co muži. Tedy muži v porovnání s ženami. Existují zde nějaké výrazné statistické odlišnosti?
Řešení: Tabulka rozšířená o mužské vítěze nyní vypadá jako na obrázku níže. Oproti původní je zde navíc rozlišující sloupec pohlaví. Pro porovnání sad dvou kvantitativních sad hodnot se často využívají tzv. BoxPlot diagramy nebo také Krabicové diagramy. Běžně se k jejich vytvoření používá specializovaný software typu Statistika, ale jednoduchý krabicový diagram si můžeme vytvořit i v Excelu. Nejdříve však několik slov ke krabicovému diagramu. Krabicový diagram nám zřetelně ukazuje charakter rozložení zkoumané veličiny, a to v podobě rozsahu (Range), maxima, minima, prvního a třetího kvartilu (Q1, Q3), mediánu a tzv. odlehlých bodů (na obrázku označené hvězdičkami). Dva nebo více takovýchto krabicových diagramů vedle sebe nám umožňuje efektivně porovnat distribuci sledované veličiny a sady hodnot navzájem.
44
Při vytváření krabicového diagramu využijeme následujícího postupu. 1. Nejdříve vytvoříme tabulku se základními statistickými údaji pro ženy a muže. Tady je výhodné označit si oblasti věku žen a mužů specifickými názvy – třeba ženy pro oblast D1:D33 a muži – pro oblast D34:D65, neboť tyto oblasti budeme používat v tabulce vícekrát. Pojmenování oblasti jménem opět provedeme tak, že nejprve příslušnou oblast 45
označíme a pak do levého horního rohu vložíme název vyznačené oblasti.
2. Bohužel Excel nedokáže vytvořit krabicový diagram automaticky. Musíme jej vytvořit prostřednictvím sloupcových grafů, které vhodně pozměníme a doplníme. Nejdříve však musíme vytvořit ještě jednu, podkladovou tabulku pro sloupcový graf. Tabulka v podstatě zachycuje vzdálenosti jednotlivých hodnot od sebe: 25 percentil = Q1 50 percentil = Median-Q1 75 percentil=Q3-Median Spodní limit=Q1-Min Horní limit=Max-Q3
46
Způsob výpočtu je označen níže pomocí relativního zápisu RC (row, column), kde např. R[-1]C odkazuje na buňku o řádek níže ve stejném sloupci. Způsob zápisu vzorců lze přepnout v Možnostech nastavení programu Excel.
3. Nyní již můžeme přistoupit k tvorbě grafu. Označte první čtyři řádky výsledné tabulky včetně záhlaví (tj. až po 75. Percentil) a vytvoříme skládaný sloupcový graf. 60 50 40 75. percentil
30
50. percentil
20
25. percentil
10 0 Ženy
Muži
Nyní přidáme chybové úsečky pro spodní a horní limity a jednoduše odstraníme spodní box. Klikneme na horní datovou řadu (zelená) a na kartě Nástroje Grafu->Rozložení->Chybové úsečky->Další možnosti chybových úseček zadejte vlastní hodnoty podle obrázku.
47
Dostaneme: 90 80 70 60 50
75. percentil
40
50. percentil
30
25. percentil
20 10 0 Ženy
Muži
Obdobně vytvoříme spodní chybové úsečky tentokrát s volbou plus – dle obrázku.
48
Spodní box pak nastavíme bez výplně a docílíme výsledné podoby. 90 80 70 60 50
75. percentil
40
50. percentil
30
25. percentil
20 10 0 Ženy
Muži
Výsledný graf si můžete samozřejmě graficky přizpůsobit, ale v dané podobě už nám stačí pro vzájemné porovnání. Z grafu vidíme, že muži získávají v průměry Oskara později než ženy (medián žen=35 < medián mužů=42,5 let). Dokonce můžeme konstatovat, že i třetí kvartil v distribuci věku žen (41,3 let) je nižší než medíán mužů. Takže první závěr zní, že všeobecně získávají ženy Oskara mnohem dříve než ženy. Možná i to, že u žen rozhoduje krása a mládí, u mužů pak zkušenost. Dále je patrné, že rozsah věku, kdy je možné získat Oskara je mnohem větší u žen (59let) než u mužů (45let). 49
Příklad č. 22: Na základě údajů o pohybu cen akcií zjistěte, kdy je výhodné
nakupovat a kdy naopak prodávat.
Poslední příklad, který zmíním, se dotýká statistiky, grafů i předpovědních funkcí. Jde o možnost využití tzv. klouzavých průměrů pro odhad vývoje situace. Klouzavý průměr je průměr, který se používá pro jednoduché odhadování trendu časové řady. Počítá se jako průměr stejného počtu za sebou jdoucích období. Na internetu dnes máte možnost stáhnout tabulky vývoje cen vybraných akcií (viz. např. http://www.akcie.cz/). Údaje zde bývají uvedeny v tabulkové podobě, asi takové jako vidíme na obrázku.
Po odstranění nepotřebných řádků získáme:
Z této tabulky snadno vytvoříme graf závislosti kurzu na datu s jedinou datovou řadu kurzu akcie (vzhledem k nesouvislé časové posloupnosti musíme vybrat graf XYbodový). Klouzavý průměr přidáme pokliknutím pravým tlačítkem myši na křivce kurzu a výběrem počtu období (pro náš případ jsme vybrali 10 a 20 období)
50
Místa, kde se čáry klouzavých průměrů kříží, označují okamžik, kdy máme prodat nebo nakupovat. Okamžik nejvhodnější pro nákup je v okamžiku, kdy se klouzavý průměr s kratším časovým obdobím (tedy citlivější) dostává nad křivku klouzavého průměru s delším časovým obdobím. U prodeje je to naopak. O tom jak spolehlivě to funguje, si můžete udělat obrázek z detailního obrázku pro vybrané období.
51
Seznam příkladů: Příklad č. 1: Očíslujte stránky dokumentu tak, aby se začalo číslovat až od první kapitoly. Titulní strana a obsah musí zůstat bez číslování...................................................................................... 4 Příklad č. 2: Nastavte prostředí Wordu tak aby, automaticky vkládal popisek tabulky s automatickým číslováním. Popisek bude ve formátu Tab. číslo popisek. ..................................... 6 Příklad č. 3: Vytvořte kalendář podle vzoru. ............................................................................... 12 Příklad č. 4: Zjistěte pomocí formátu buněk, který den v týdnu jste se narodili. ........................... 14 Příklad č. 5: Přišli jsme k nějakým penězům (200000 Kč) a chceme je nějak zúročit. Vybereme si banku poskytující dlouhodobý úrok 3%. Navíc každý další rok budeme k naspořené částce přidávat další sumu, řekněme 10000 Kč. Otázka zní: Kolik budu mít na konci 10, 15, 20 roku? A kolik bych musel ročně přispívat, abych měl na konci desátého roku 1000000 Kč? ...................................... 17 Příklad č. 6: Zjistěte průměr hodnot v buňkách A1:A10. .............................................................. 22 Příklad č. 7: Vygeneruj 6 náhodných čísel v rozsahu 1-49. Vedle každého z nich vypiš, jestli je liché nebo sudé. Použij funkce MOD. ................................................................................................. 22 Příklad č. 8: Zjistěte průměrné povedené výkony závodníků ve skoku do dálky. Můžete využít funkci COUNTIF. ........................................................................................................................ 23 Příklad č. 9: Sudé řádky buněk podbarvi zeleně, liché řádky žlutě................................................ 24 Příklad č. 10: Podle předlohy vytvořte tabulku, která bude informovat o průměrných teplotách v každém měsíci v průběhu jednoho roku. Údaje promítněte do grafu. ....................................... 26 Příklad č. 11: Vytvořte graf zachycující rozlohu a hustotu obyvatel jednotlivých kontinentů. ....... 29 Příklad č. 12: Zobrazte všechny filmy dobrodružného žánru, které natočil režisér Spielberg, ........ 31 Příklad č. 13: Vyberte knihy, které mají v názvu moře a nestojí více než 150 Kč. .......................... 32 Příklad č. 14: Dejme tomu, že máme vybrat z databáze filmů komedie s premiérou po roce 2006. Rok premiéry v tabulce přímo není, lze však odvodit z atributu datum premiéry. ........................ 33 Příklad č. 15: Zjistěte celkový zisk i zisk jednotlivých prodejců. .................................................... 34 Příklad č. 16: Jaký obrat měl prodejce Jirka v oblasti sportu před 31.3.2012? .............................. 36 Příklad č. 17: Jaký lze předpokládat prodej při navýšení nákladů na reklamu na 100, 110, 115 a 120 tis. Kč? Vycházejte z tabulky nákladů za reklamu a odpovídajícího prodeje. ......................... 36 Příklad č. 18: Jak velký můžeme očekávat prodej zmrzliny, při stanovaném počtu zákazníků, teploty vzduchu, vody a délce slunečního svitu? Vycházejte z následující tabulky. ....................... 37 Příklad č. 19: Z poskytnutého vzorku dat – přehledu vítězek Oskara v letech 1970-2001 a věku, kdy tohoto ocenění dosáhly, zjistěte následující základní statistické údaje – průměr, medián, max, min, směrodatnou odchylku věku ocenění. ........................................................................................ 41 Příklad č. 20: Nabízí se otázka typu, existuje nějaké typické období v životě herečky, kdy má větší šanci získat Oskara? .................................................................................................................. 42 Příklad č. 21: Zjistili jsme, jak jsou na tom ženy, herečky s jejich šancemi na získání Oskara, ale co muži. Tedy muži v porovnání s ženami. Existují zde nějaké výrazné statistické odlišnosti? ........... 44 Příklad č. 22: Na základě údajů o pohybu cen akcií zjistěte, kdy je výhodné nakupovat a kdy naopak prodávat....................................................................................................................... 50
52
Přílohy Seznam nejčastěji používaných klávesových zkratek
Klávesové zkratky Pohyb a výběr v dokumentu CTRL+šipka doprava/doleva – skákání po slovech. CTRL+šipka nahoru/dolů – skákání po odstavcích. CTRL + HOME - skok na začátek CTRL + END - skok na konec HOME - začátek řádku END - konec řádku CTRL+PAGE UP – Přejde na předchozí místo výskytu hledané položky. CTRL+PAGE DOWN – Přejde na následující místo výskytu hledané položky. CTRL+G – Otevře okno Přejít na – přejde na stránku, záložku, poznámku pod čarou, tabulku,
komentář, obrázek nebo jiné místo. CTRL+A – Výběr všeho
SHIFT + CTRL + ... předchozí - označení textu
Formátování písma CTRL+D nebo CTRL+SHIFT+P – Otevře dialogové okno Písmo za účelem změny formátování
znaků.
CTRL+B – Nastaví tučné písmo. CTRL+I – Nastaví kurzívu. CTRL+U – Nastaví podtržené písmo. CTRL+SHIFT+D – Nastaví dvojité podtržení textu. CTRL+SHIFT+W – Podtrhne slova, ale ne mezery. CTRL+SHIFT+A – Nastaví velká písmena. CTRL+SHIFT+K – Nastaví kapitálky
53
SHIFT+F3 – Změní velikost písmen (přepíná mezi malými písmeny, velkými a slovy s velkým
počátečním písmenem)
CTRL+ROVNÁ SE – Nastaví dolní index (automatické mezery mezi řádky). CTRL+SHIFT+ROVNÁ SE – Nastaví horní index (automatické mezery mezi řádky). CTRL+SHIFT+, – Zvětší velikost písma o jednu hodnotu. CTRL+SHIFT+) – Zmenší velikost písma o jeden bod. CTRL+) – Zvětší velikost písma o jeden bod. CTRL+SHIFT+Q – Změní text výběru na písmo Symbol. CTRL+SHIFT+á – Zobrazí netisknutelné znaky. CTRL+MEZERNÍK – Odstraní ruční (lokální) formátování odstavce nebo znaků.
Formátování odstavců CTRL+SHIFT+1 (na hlavní části klávesnice) – Jednoduché řádkování CTRL+SHIFT+2 (na hlavní části klávesnice) – Dvojité řádkování CTRL+SHIFT+5 (na hlavní části klávesnice) Řádkování 1,5 CTRL+L – Zarovná odstavec doleva. CTRL+E – Zarovná odstavec na střed. CTRL+J – Zarovná odstavec do bloku. CTRL+R – Zarovná odstavec doprava. CTRL+M – Odsadí odstavec zleva. CTRL+SHIFT+M – Odstraní odsazení odstavce zleva. CTRL+T – Předsadí první řádek. CTRL+SHIFT+T – Zmenší předsazení prvního řádku. CTRL+Q – Odstraní formátování odstavce (ale ponechá lokální formátování znaků). CTRL+SHIFT+S – Otevře podokno úloh Použít styl. ALT+CTRL+SHIFT+S – Otevře podokno úloh Styly. CTRL+SHIFT+N – Použije styl Normální (přebije lokální formátování).
54
CTRL+ALT+SHIFT+1 (na hlavní části klávesnice) – Použije styl Nadpis 1. CTRL+ALT+SHIFT+2 (na hlavní části klávesnice) – Použije styl Nadpis 2. CTRL+ALT+SHIFT+3 (na hlavní části klávesnice) – Použije styl Nadpis 3. ALT+CTRL+K – Spustí příkaz Automatický formát.
Psaní speciálních znaků ALT+CTRL+KLÁVESA MINUS (na numerické kláv.) – M pomlčka CTRL+KLÁVESA MINUS (na numerické kláv.) – N-pomlčka CTRL+POMLČKA – Volitelný rozdělovník (skrytý znak – zajišťuje přednostní dělení slova v
daném místě)
CTRL+SHIFT+POMLČKA – Pevný spojovník (na konci řádku nedovolí rozdělení slov spojených
tímto znakem – např. Brno-venkov)
CTRL+SHIFT+MEZERNÍK – Pevná mezera (na konci řádku nedovolí rozdělení slov spojených
touto mezerou – např. k výtahu, 10 km)
ALT+CTRL+R – Symbol registrované ochranné známky ® ALT+CTRL+T – Symbol ochranné známky ™ ALT+CTRL+TEČKA – Výpustek … (trojtečka)
Ukončení (zalomení) SHIFT+ENTER – Konec řádku CTRL+ENTER – Konec stránky CTRL+SHIFT+ENTER – Konec sloupce
Funkce programu ALT+BACKSPACE – Krok zpět CTRL + S - Uložení dokumentu ALT+F4 - Zavření aplikace CTRL+F4 - Zavření dokumentu ALT+TAB - přepínání mezi aplikacemi CTRL+F6 - přepínání mezi okny aplikace dopředu
55
CTRL+SHIFT+F6 - přepínání mezi okny aplikace dozadu CTRL+SHIFT+C – Kopíruje pouze formát CTRL+SHIFT+V – Vloží pouze formát (jen v kombinaci s CTRL+SHIFT+C) CTRL+SHIFT+G – Otevře dialogové okno Počet slov. CTRL+F – Funkce Najít – vyhledá text, formátování a speciální položky. CTRL+H – Funkce Najít a nahradit – nahradí text, formátování a speciální položky. ALT+CTRL+Y – Zopakuje hledání (po zavření okna Najít a nahradit). ALT+CTRL+Z Přepíná na režim zobrazení Koncept SHIFT+F5 – Přepne mezi posledními čtyřmi upravovanými místy. ALT+CTRL+HOME – Otevře seznam možností procházení. Požadovanou možnost vyberte
stisknutím kláves se šipkami a poté stiskněte klávesu ENTER. ALT+CTRL+I – Přepne do náhledu tisku a zpět.
Práce s poli CTRL+F9 – Pole ALT+SHIFT+D – Vloží pole DATE (datum). ALT+CTRL+L – Vloží pole LISTNUM – číslo položky seznamu (odstavec přitom není položkou
seznamu).
ALT+SHIFT+P – Vloží pole PAGE – číslo stránky. ALT+SHIFT+T – Vloží pole TIME (čas). F9 – Aktualizuje vybraná pole. ALT+F9 - Přepínání mezi zobrazením pole a textem CTRL+SHIFT+F9 - Převedení pole na statický text
56
Doporučená literatura Bříza V. 2007. Excel 2007 podrobný průvodce. Grada Publishing. Carnegie Mellon University. Probability & Statistics. Free Online Course. http://oli.cmu.edu/courses/free-open/statistics-course-details/ Dixon, H. 2007. Excel 2007: Beyond the Manual. Springer-Verlag. New York. Pecinovský J. 2007. Office 2007 – podrobný průvodce. Pecinovský J. 2009. Word 2007 pro pokročilé. Grada Publishing. Pírková K. 2007. Microsoft Office Word 2007: podrobná uživatelská příručka. Computer Press. Schels, I. 2008. Excel 2007: vzorce a funkce. Grada Publishing. Voglová, B. 2004. Excel a Access: efektivní zpracování dat na počítači. Grada Publishing.
57
