V rámci projektu Kvalifikovaný pracovní tým v Auto-Poly spol. s r.o. Kurz 15 MS EXCEL

V rámci projektu „Kvalifikovaný pracovní tým v Auto-Poly spol. s r.o.“

Kurz 15 MS EXCEL

Lektor: Jan Vostatek

Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

1

Smyslem projektu „Kvalifikovaný pracovní tým v Auto-Poly spol. s r.o.: je stírání generačních a vědomostních rozdílů pracovního kolektivu. Smyslem kurzu: je rozvoj efektivní komunikace, odhad potřeb kupujících, včasné rozpoznání problémových zákazníků a zvládání konfliktů s jediným cílem – neztratit žádného zákazníka. Cíle: na konci kurzu budou účastníci  Vědět jaké existují zkratky pro funkční klávesy  Umět formátovat  Umět pracovat s listy  Umět pracovat v listech  Umět kopírovat, vkládat, přesouvat, odstraňovat – buňky, řádky a sloupce  Orientovat se v typech grafů  Umět využívat logickou funkci KDYŽ  Umět vytvářet kontingenční tabulky  Vytvářet výpočtové pole v kontingenčních tabulkách  Pracovat s nesouvislými daty v kontingenčních tabulkách  Vytvářet kontingenční graf

Moje cíle (aneb co chci od kurzu):


2

Harmonogram 1 den Činnost

Čas

Prezentace účastníků, objasnění cílů kurzu, osobní cíle účastníků

7:00 – 8:00

Excel – úvod, seznámení s úvodní obrazovkou, zkratky pro funkční klávesy, tabulka

8:00 –9:00

Formátování, Práce s listy a práce v listech

9:00 –10:30

Kopírování, vkládání, styly, úpravy a funkce

10:30 –11:30

Přestávka na oběd

11:30 –12:00

Podmíněné formátování, grafy

12:00 – 13:00

Kontingenční tabulka

13:00 – 15:30

Závěrečná reflexe, úkoly, ukončení 1 dne

15:30 – 16:00


3

Harmonogram 2 den Činnost

Čas

Prezentace účastníků, co nás zaujalo minule…

7:00 – 8:00

Diskuze o úkolech

8:00 – 9:30

Supervize doručených souborů

9:30 – 11:30

Přestávka na oběd

11:30 –12:00

Supervize doručených souborů

12:00 – 15:00

Závěrečná diskuze, ukončení kurzu

15:00 – 16:00


4

1. Úvod Nejprve spusťte Excel, a to buď ikonou na ploše, nebo přes nabídku Start Všechny programy - Microsoft Office - Excel 2010.

Po spuštění bude zobrazena prázdný sešit se třemi listy (pokud správce nenastavil jinak).

Uložení provedete jednoduše. Karta Soubor a menu Uložit jako. Dokument pojmenujte: Moje-tabulka a uložte tlačítkem Uložit.


5

Úvodní obrazovka

Toto programové okno aplikace obsahuje:       

Panel nástrojů pro Rychlý přístup (Quick Access Toolbar) Pás karet (Ribbon) Svislý a vodorovný posuvník (Vertical/Horizontal Scroll Bar) Tlačítka pro pohyb mezi listy + ouška listů (Tab Scrolling Buttons + Sheet Tab) - Pole pro přepínaní listů Stavový řádek (Status Bar) Řádek vzorců (Formula Bar) Aktivní list sešitu (Worksheet) - Aplikační okno - Buňka(y)

Panel nástrojů - Rychlý přístup Pás rychlých nabídek. Tento pás si můžete upravit k obrazu svému, ale i v základním nastavení je bez problému použitelný.

1. Ikona aplikace Excel (Control Button) - otevírá se systémové menu (minimalizovat, přesunou.) 2. Ikona (tlačítko) Přizpůsobit panelu nástrojů Rychlý přístup (Customize Quick Access Toolbar Button) - můžete si tak upravit a přizpůsobit přímo pro vás. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

6

Pás karet (Ribbon)

Vodorovný a svislý posuvník Slouží k pohybu po listu nahoru, dolů, vpravo, vlevo.

Pole přepínaní listů Pomocí "oušek se dá přepínat mezi jednotlivými listy (grafy). Jména listů (grafů) nemusí mít standartní názvy "List1" ale můžete je pojmenovat dle svých potřeb, např. hypotéka.

Pokud máte v Excelovském sešitu více listu můžete listy přesouvat pomocí šipek. Zobrazit poslední listy či první listy pomocí šipek.

Řádek vzorců V tomto okně zadáváme naše vzorce a zadáváme do jednotlivých buněk potřebné hodnoty.

Stavový řádek Pro další dodatečné informace. Jako novinku obsahuje stavový řádek lupu, kdy si okno se vzorci můžete rychle a efektně přizpůsobit svým požadavkům.

Aktivní list (Aplikační okno) - Buňka(y) Toto okno je pro nás nejdůležitější. V tomto okně budeme tvořit naše výpočtové výtvory. Okno se skládá z jednotlivých listů. Každý list se skládá z jednotlivých buněk. Buňka je nejmenší jednotka, které nese informaci (číslo, vzorec, atd.). Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

7

2. Vytvoření tabulky Excel se skládá z listu. List se skládá z buněk. Představit si to můžete jako čtverečkovaný sešit, kdy listy jsou jednotlivé listy v sešitě. Jednotlivé čtverečky odpovídají buňkám. Do buněk se mohou vkládat čísla, vzorce, texty, (dokonce i minigrafy), atd. Každá buňka má svou adresu (pozici), takže umístění není neznámé. V Excelu jsou v základním nastavení sloupce označeny písmeny, řádky čísly. Takže první buňka má adresu A1, druhá buňka v první řádku B1. Buňka ve třetím sloupci a třetím řádku C3. Příklad tabulky


8

3. Zkratky pro funkční klávesy F1 Nápověda F2

Úprava aktivní buňky

F3 Vložit název

Alt + Vložit graf do listu Uložit jako –

Opakovat operaci F4 odkaz- absolutní Zavřít Excel relativní F5 Přijít na – Přepnutí další F6 – podokno F7 Kontrola pravopisu – F8 Rozšířit výběr Makro – F9 Výpočet listu – F10 Aktivace pásu karet – Otevřít Visual F11 Vložit graf nový list Basic F12 Uložit jako –

Ctrl + Minimalizovat pás karet

Shift + –

Vložit do buňky komentář Dialogové okno – Správce Vložit funkci – názvu dialogové okno Náhled před tiskem

Uložit změny?

Přesun na další sloupec

Zmenšit okno sešitu

Najít a nahradit Přepnutí předchozí Další otevřený sešit podokno Přesun okna sešitu Zdroje informací Změnit velikost okna Přidat k výběru Minimalizovat okno sešitu Přepočet aktuální list Maximalizovat okno sešitu Zobrazit nabídku Vložit nový list

Nový list

Otevřít – Dialogové okno

Uložit sešit

Zkratky s Ctrl CTRL + C, CTRL + V, CTRL + P (známá zkratka ze školy) a protokol je hotov :) ...               

Ctrl + A Označení všech buněk Ctrl + B Tučné/netučné písmo (funguje pro následující psané znaky) Ctrl + C Zkopíruje obsah označených buněk do schránky Ctrl + D Kopírování obsahu a formátu horní buňky do buňky pod ní Ctrl + F Najít (otevře dialogové okno) Ctrl + G Přejít na (otevře dialogové okno) Ctrl + H Nahradit (otevře dialogové okno) Ctrl + I Písmo kurzivou/nekurzivou (funguje pro následující psané znaky) Ctrl + K Vloží hypertextový odkaz (otevře dialogové okno) Ctrl + N Otevře nový sešit Ctrl + O Otevře soubor Ctrl + P Vytiskne Ctrl + S Uložení souboru Ctrl + T Zobrazení dialogového okna: Vytvořit tabulku Ctrl + U Podtrženo/Nepodtrženo


9

            

Ctrl + V Vloží do buňky obsah schránky Ctrl + X Vyjme obsah označených buněk Ctrl + Home Přesun do buňky A1 Ctrl + End Přesune kurzor na poslední plnou buňku Ctrl + Enter Do všech označených buněk stejný obsah Ctrl + PageDown Přepnutí na další list Ctrl + PageUp Přepnutí na předcházející list Ctrl + Mezerník Výběr celého(celých) sloupce(ů) Ctrl + Shift + Mezerník Výběr celého listu Ctrl + F1 Zobrazení/skrytí - pás karet Ctrl + F2 Náhled před tiskem Ctrl + F3 Správce názvu - dialogové okno Ctrl+Shift+Enter Vložení maticového vzorce

Zkratky s Alt  

Alt + Enter Zahájení nového řádku ve stejné buňce (vcelku šikovná pomůcka) Alt + = Vložení vzorce SubTotal

Zkratky pro funkční klávesy       

F1 Nápověda nebo pomocník (stejné jako u jiných aplikací např. Word) F2 Změna v buňce F4 Opakování poslední akce o F4 - odkaz - změna typu odkazu (absolutní, relativní, smíšený, tj $) F5 Dialogové okno: Přejít na F7 Dialogové okno: Kontrola pravopisu F9 Přepočet všech listů F12 Uložit jako dialogové okno



Home Přesunutí na začátek řádku

Navigace uvnitř listu Klávesové zkratky pro navigaci uvnitř listu v sešitu Excel       

Alt + Page Down - Pohyb v listu o jednu obrazovku vpravo Alt + Page Up - Pohyb v listu o jednu obrazovku vlevo Alt + šipka dolů - Zobrazení seznamu pro automatický filtr Ctrl + End - Přesun do poslední buňky s obsahem Ctrl + F - Zobrazí se dialogové okno Najít Ctrl + H- Zobrazí dialogové okno Nahradit Ctrl + Home - Přesun na začátek listu (A1)


10

              

Ctrl + G - Zobrazit dialogové okno Přejít na (také zkratka F5) Ctrl + šipky - Přesun na okraji další oblasti dat Ctrl + šipka vlevo - Uvnitř buňky: Přesun o jedno slovo doleva Ctrl + šipka doprava - Uvnitř buňky: Přesun o jedno slovo doprava End - Uvnitř buňky: přesunout na konec buňky F2 - Kurzor na poslední pozici v buňce F5 - Zobrazit dialogové okno Přejít na (také zkratka Ctrl + G) Home - Přesun na začátek řádku v kterém se nachází kurzor (tj. do sloupce A) Home - Uvnitř buňky: přesunout na začátek buňky. Page Down - Přesun o jednu obrazovku dolů Page Up - Přesun o jednu obrazovku dolů Shift + Tab - Přesunutí o jednu buňku vlevo Shift + F4 - Opakujte poslední nález Šipky - Přesun o jednu buňku nahoru, dolů, doleva doprava Tab - Přesunutí o jednu buňku vpravo

Výběr buněk v listu Klávesové zkratky pro výběr buněk v listu (listech) v sešitu Excel              

Ctrl + * (hvězdička) - Vybere aktuální oblast kolem aktivní buňky (tabulku) Ctrl + A - Vybere aktuální oblast kolem aktivní buňky (druhý stisk Ctrl + A celý list) Ctrl + Mezerník - Vybere sloupec Ctrl + Shift + End - Rozšíření výběru k poslední použité buňce Ctrl + Shift + Home - Rozšíření výběru k začátku listu Ctrl + Shift + Mezerník - Vybere celý list Ctrl + Shift + O - Výběr všech buněk s komentáři Ctrl + Shift + PgUp - Vybere aktuální a předchozí list v sešitu Ctrl + Shift + PgDn - Vybere aktuální a následující list v sešitu Ctrl + Shift + Šipky - Rozšíření výběru k okrajové buňce s obsahem Esc - Zrušit výběr Shift + Home - Rozšíření výběru k začátku řádku Shift + Mezerník - Vybere řádek Shift + Šipky - Rozšíření aktuálního výběru o jednu buňku


11

4. Data v buňkách - co obsahuje, co lze vkládat Každá jednotlivá buňka, může obsahovat: 

    

Hodnota - číselné, textové, datum čas, ..... o Číslo - číselné hodnoty o Text - Jakýkoliv text o Datum, čas Vzorec (funkce) - v buňce se provede výpočet – k dispozici jsou funkce statistické, finanční, textové,... Místo správného výpočtu může být obsahem chybová hodnota. Formát (automaticky, vlastní, podmíněný) - například barva, ohraničení (obsahem buňky může být něco jiného, než je zobrazeno, například vzorec, datum, čas, ...) Hypertextový odkaz - například klikatelný odkaz na web www.auto-poly.cz :) Komentář - komentář (klasický žlutý lístek) můžete upravovat (přiřadit mu tvar barvu) Minigraf - od Verze Excel 2010 může být v buňce i graf (minigraf).

Hodnota Zadávat lze různé hodnoty (číslo, text, datum, čas, procenta). Toto lze povést:    

Ručně - zadá se ručně Automaticky - klávesovou zkratkou - klávesovou zkratkou lze vložit aktuální datum čas (viz dále) Automatická změna formátu - po zadání hodnoty data, času ve správném času dojde k automatické změně formátu (= pro vkládání vzorce). Ruční změna formátu - pomocí menu můžete nastavit příslušné formáty

Ručně Ruční zadávaní, nejčastější, do aktivní buňky zadáte požadovanou hodnotu. Pro specifická zadání se provede automatický formát (o tom podrobněji v další kapitole). 

Ctrl + V – Vložení dat ze schránky

Poznámka: Pokud data mají jiný rozměr, může při vkládání dojít k chybě. Změna formátu (automatická ruční) Při vložení některých specifických hodnot dojde k automatické změně formátu, když vkládáte datum, čas, procenta. Například: 1.1.2013


12

Poznámka: Vložená hodnota se automaticky změní na formát datum (a patřičně se naformátuje číslo v buňce). Na první pohled sice stále vidíte ono datum, ale ve skutečnosti je v buňce číslo, reprezentující dané datum. V případě, že zadáte například pouze „1.5“, doplní se automaticky současný rok a do buňky se vloží „1.5.2014“. Viz také další kapitola o formátu. K další automatické změně dojde při vložení symbolu = (rovná se). Excel automaticky předpokládá vložení funkce (popis funkci následující kapitola). ' apostrof Při použití apostrofu bude Excel ignorovat použití automatického formátu. Takto můžete zobrazit například '=A10+A12, bez použití apostrofu se provede výpočet. Apostrof se v buňce nezobrazí.

Funkce (vzorce) - zadávaní/úprava Jak jsem psal výše, aby Excel poznal, že jde o vzorec (funkci), musíte jej uvodit znakem rovno =. Poté již můžete vkládat vzorec. Pro vložení funkce (vzorce) máte opět několik možností:    

Ručně Myší ze schránky - Ctrl + V VBA - makro

Ručně Nejrychlejší možnost, pokud znáte příslušnou funkci, můžete začít ihned psát, například: =KDYŽ(A10=1;A3;A5)

Poznámka: Klávesová zkratka F4 při psaní přepíná odkazování. Příklady funkcí (vzorců):   

=A1 - odkaz na buňku A1, pokud v ní bude hodnota 24, bude i v buňce (B2) hodnota 24 (nemáte-li změněno formátování) =1+1 - nemusíte se vůbec nikam odkazovat a Excel Vám spočítá, kolik je 1+1 =A1+A2 - spočtete součet, který dávají čísla v buňkách A1 a A2 (pokud v buňkách nebude číslo, obdržíte chybovou hodnotu)

Poznámka: - Podrobnější popis funkcích (vzorcích) je v sekci věnované vzorcům: funkce. Myší Pokud funkci neznáte, můžete pomocí myši, vybrat z pásu karet Vzorce sekce knihovna funkcí ikona Vložit funkci v zobrazeném dialogovém okně si již vyberete, co potřebujete. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

13

Klávesová zkratka (Ctrl + V)

Podobně jako u vložení hodnot, pokud máte ve schránce příslušný funkci (vzorec) tento bude vložen do buňky (buněk).


14

5. Základní formátování Formátování buněk je nedílnou součásti hodnoty. Každá hodnota je po zadání automaticky naformátována. Tento formát pak můžete nastavit (změnit). Excel automaticky formátuje zadávané hodnoty (převádí na číslo, datum,) Zkuste zadat a uvidíte co se stane:    

A - zůstane tak jak je 23 - automaticky se zarovná doprava (jedná se o číslo) 12.1.2012 - automaticky se převede na datum 12:12 - automaticky se převeden na čas

Poznámka: - při použití apostrofu ' nebude použito automatické formátování, text zůstane tak jak jej napíšete (například vzorec, datum, čas, ...).

Odkazování na definovaný název Pokud je pojmenována jedna buňka odkáže se jednoduše rovná se a její název (např. = DPH). Pokud máte definovanou název pro oblast buněk a chcete se na ní odkázat musíte použít maticový zápis. Označíte stejně velkou oblast (kam se má přenést) a použijete klávesovou zkratku Ctrl+Shift+Enter, která provede zápis maticového vzorce.

Hypertextový odkaz V Excelu se můžete odkazovat na web. Například na stránky www.auto-poly.cz  Nejjednodušeji v buňce, do které potřebujete zadat hypertextový odkaz. Pravým tlačítkem a vyberete hypertextový odkaz.


15

Do Zobrazený text: zadáte co chcete, aby se zobrazilo v buňce, např: www.autopoly.cz a do Adresa: zadáte www.auto-poly.cz/excel/ kliknete OK a máte hotovo.

Komentáře Do buňky se dají vkládat komentáře. Nejjednodušeji přes pravý klik na buňce a vložit komentář. Nebo na kartě revize sekce komentáře. Poznámka: - Buňka, která obsahuje komentář má v pravém horním rohu červený praporek.

Minigrafy Ukázka buňky s minigrafem.


16

6. Práce s listy v MS Excel Úvodem k listům S listy se dá provádět mnoho různých činností (vkládat, kopírovat, skrývat, atd.).          

Výběr (volba) listu - Zobrazení vybraného listu, přesun mezi listy Skrýt/zobrazit listy Super skrytý list - speciální skrytí listu Vložit list - jeho pojmenování Odstranit / Smazat list Přesunování listu Kopírování listu Úprava záložek listů - změna názvu, barvy, skrytí Přejmenovat list Zobrazit 2x stejný list (dva pohledy) v jednom okně

Výběr (volba) listu Otevřením sešitu, se Vám zobrazí zároveň jednotlivé listy (v záložkách vidíte jejich jména). V jednu chvíli si můžete prohlížet jen jeden list.

Pokud se chcete přesunout na druhý. Lze to provést klikem na příslušnou záložku, kdy se Vám zobrazí Vámi vybraný list. Pokud je listů více můžete se na další (předchozí dostat klikem na posuvník). Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

17

Posuvník umožní přesun na první list, na předchozí list, na další list a na poslední list. Další možností jak zvolit požadovaný list, je klik pravým tlačítkem myší na jednu z šipek a zobrazí se Vám všechny listy k dispozici (pak si jen vhodný vyberete).

Poznámka: Minimálně jeden list je v každém sešitě viditelný.

Skrýt/zobrazit listy V Excelu můžete skrývat/zobrazovat listy. Máte-li na jednom listě výpočty, které chcete mít na první pohled skryté. Máte dvě možnosti: Klik na záložky V záložkách listu kliknete pravým tlačítkem myší na záložku listu, který chcete skrýt a vyberete Skrýt list


18

Skrýt list (využití pásu karet) V pásu karet Domů sekce Buňky ikona Formát z menu vyberete Skrýt a zobrazit - Skrýt list

Zobrazit list V pásu karet Domů sekce Buňky ikona Formát z menu vyberete Skrýt a zobrazit - Zobrazit skrytý list v zobrazeném dialogovém okně si vyberete list, který chcete zobrazit. Pokud je super skrytý list tak jej v přehledu neuvidíte.


19

Poznámka: Další možnost jak skrýt list je super skrytý list. Což je sepsáno v další kapitole. Poznámka: Pokud sešit obsahuje jen jeden list (nelze již vybrat jiný list). Jinak minimálně jeden list musí být zobrazen (i když další listy jsou skryté, jak zobrazit skryté listy viz dále Skrýt/zobrazit listy). Pokud budete chtít odstranit poslední list, obdržíte chybovou hlášku.

Vložit list Jak vložit nový list. Pokud Vám nestačí tři listy, lze vložit nový list. 1) klávesovou zkratkou Shift + F11 2) Klikem na přidávací tlačítko v liště s názvy listů.

3) Přes pás karet Domů sekce Buňky ikona vložit - Vložit list.

Poznámka: Po otevření sešitu v Excelu máte v sešitu 3 listy (standardní nastavení Excelu, pokud si změníte, může jich být zobrazen i jiný počet). Toto můžete jednoduše změnit. Změna počtu vložených listu při otevření MS Excelu. Přes pás karet Soubor menu Možnosti v dialogovém oknu změníte. Zahrnout počet listů:


20

Odstranit / Smazat list 1) Klik pravým tlačítkem myší na záložce listu, který chcete odstranit (smazat) a vybrat Odstranit. V případě že list obsahuje data se Excel zeptá, zda opravdu chcete odstranit. Pokud ano klik na Odstranit.

2) Přes pás karet Domů sekce Buňky ikona Odstranit - Odstranit list. V případě že list obsahuje data se Excel zeptá, zda opravdu chcete odstranit. Pokud ano klik na Odstranit.


21

Přesunování listu Pokud potřebujete uspořádat listy. Můžete zvolit Klikem myší na záložku Stisk levého tlačítka myši na zvolené záložce listu, který chcete přesouvat a přesunutí. U záložek se objeví černá šipka, která znázorňuje kam bude list přesunut.

Kopírování listu Klikem myší na záložku Stisk levého tlačítka myši na zvolené záložce listu, který chcete zkopírovat a za současného stisku klávesy Ctrl přesunout na místo kam si přejete vložit kopie listu. U záložek se objeví černá šipka, která znázorňuje, kam bude list kopírovat.

V dialogovém okně vyplníte, kam chcete zkopírovat vybraný list(y). Nesmíte zapomenout Zatrhnout Vytvořit kopii


22

Úprava záložek listů Pro lepší přehlednost pří více listech, můžete upravit barvu záložek. Změnu můžete provést dvěma způsoby: Klikem myší na záložku Klik pravým tlačítkem myši na zvolené záložce a z kontextového menu vyberete barva karty a barvu.

Ukázka

Přejmenovat list Pokud se Vám nelíbí přednastavený název listu "List1" můžete si zvolit vlastní. Klikem myší na záložku Klik pravým tlačítkem myši na zvolené záložce a z kontextového menu vyberete Přejmenovat a v záložce zadejte nové jméno..


23

Poznámka: Každý název listu musí být jedinečný, tj. dva listy se nesmí jmenovat stejně.

Zobrazit 2x stejný list (dva pohledy) v jednom okně V Excelu si můžete pod sebe zobrazit 2x stejný list z jednoho sešitu. Kdy v každé části daného listu se pohybujete nezávisle. Postup pro údaje pod sebou V pravé části pod řádkem vzorců, najet myší nad obdélník až změní tvar ukazatele myší ze šipky na "rovná se se šipkou nahoru a dolů" podržet levé tlačítko myší a přesunout kam potřebujete.


24

Vedle sebe Podobně to jde, i když údaje chcete mít 2x vedle sebe. Tam se jen kliká na obdelníček ve spodní stavové liště.

Odstranění Myší najet nad středovou čáru, které dělí dvě zobrazení listu a vrátit zpět.


25

7. Práce v listech v MS Excel Jednotlivé možností práce v listu v MS Excelu (co, proč, jak).              

Pohyb v listu - přesun na konec, začátek listu. nalezení poslední obsazené buňky Označovaní listů, řádků, tabulek - například pro kopírování Označit celou tabulku - zkratka Skrývat řádky sloupce (Zobrazit jen tabulku) Zobrazit skryté řádky sloupce Kopírovat - buňky, řádky, sloupce Vložit - řádky, sloupce (ať už prázdné, nebo vložit hodnoty Přesun - buňky, řádky, sloupce Odstranit (Smazat) - řádky sloupce Příčky ukotvení - co je ukotvení (řádkové, sloupcové) Úpravy listů posuvníky, mřížky Záhlaví a zápatí - vložit, upravit, odstranit Šířky sloupců / výšky řádků Zamykání buněk

Pohyb v listech Když už jste na správném listu, naučte se po tomto listu pohybovat. Pokud máte větší tabulky, většinou se na Vaši obrazovku nevejdou.   

myš kurzorové klávesy klávesové zkratky

Případně kombinace výše uvedeného. Myš Klikem na pravítka se posunete do požadované oblasti a klikem vyberete požadovanou buňku.


26

Označit oblast: Stisk levého tlačítka myši a pohybem můžete vybrat oblast buněk. Kurzorové klávesy Pomocí kurzorových kláves se můžete přesunout na požadovanou buňku. Využití kurzorových kláves (vpravo, vlevo, nahoru, dolů), je velice jednoduché.

Poznámka: Stisk klávesy Shift a pomocí kurzorových kláves vyberete požadovanou oblast. Pokud se potřebujete rychleji přesunout dolů/nahoru je možnost použít klávesy (Pgup, Pgdn), takto se posunete o stánku dolů (stránku nahoru). Klávesové zkratky Asi nejrychlejší možnost přesunu na rozlehlé tabulce. 

Ctrl+G - můžete zadat příslušnou adresu buňky na kterou se potřebujete přesunout (pokud používáte pojmenované buňky/definované oblasti jsou v tomto seznamu)  Ctrl+Home - buňka A1  Ctrl+ šipka vpravo - poslední vyplněná buňka v daném řádku, nebo poslední buňka  Ctrl+ šipka vlevo - první vyplněná buňka v daném řádku, nebo poslední buňka  Ctrl+ šipka nahoru - první vyplněná buňka v daném sloupci, nebo poslední buňka  Ctrl+ šipka dolů - poslední vyplněná buňka v daném sloupci, nebo poslední buňka Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti 27

Ctrl + šipka vpravo a Ctrl + šipka dolů - Takto zjistíte poslední sloupec (řádek) v prázdném listu. - Oblíbené to dotazy přednášejících :) Kombinace kláves a myší Nejčastěji používaná je kombinace myší a klávesových zkratek. Budete-li Excel používat častěji, zvládnete tento způsob v závislosti, co potřebujete provést. Finta - nepravidelná oblast Při stisknuté klávese Ctrl, můžete označit nepravidelnou oblast.

Označovaní buněk, řádků, sloupců Pokud zvládáte pohyb po listech je vhodné umět onačit část buněk (například pro kopírování, přesun). Co tedy můžete označit:      

jednu buňku celý list řádek/řádky sloupec/sloupce souvislou oblast nesouvislé oblasti

Označit jednu buňku Většinou je aktivní jen jedna buňka. Má kolem sebe silnější ohraničení. Takže klikem na požadovanou buňku jste ji zároveň vybrali.

Označit celý list klikem do levého horního rohu označíte celý list.


28

Označit řádek/řádky Řádek označíte klikem na číslo řádku. V případě požadavku na více řádku označujete, při stisknuté klávese Shift při stisknuté klávese Ctrl můžete označit nesouvislé řádky. Případně můžete souvislou oblast řádku označovat za současného držení levého tlačítka myši.

Označit sloupec/sloupce Sloupec označíte klikem na písmenu sloupce. V případě požadavku na více sloupců označujete při stisknuté klávese Shift při stisknuté klávese Ctrl můžete označit nesouvislé sloupce. Případně můžete souvislou oblast sloupců označovat za současného držení levého tlačítka myši.

Označit souvislou oblast Klikem na buňku, od které chceme začít a při držení levého tlačítka myši nebo držení klávesy Shift se přesunete na konec oblasti.

Označit nesouvislé oblasti Klik myší na požadované buňky za držení tlačítka Ctrl. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

29

Označit celou tabulku Ctrl + * Potřebujete-li označit celou tabulku stačí mít aktivní buňku v této tabulce a využít klávesovou zkratku Ctrl + *. Poznámka tabulka musí být souvislou oblasti. Lze využít i klávesovou zkratku Ctrl + A - pokud ale není aktivní buňka v souvislé oblasti (tabulce), vybere se celý list. Doplněno na zákaldě dotazů v emailu.

Skrývat řádky sloupce - Zobrazit jen tabulku Pro BFU (Běžný Franta Uživatel) stačí skrýt sloupce/řádky s výpočty a tento uživatel si ji již nezobrazí. Takto si můžete skrývat řádky (sloupce) i sami, pokud si nechcete nechtěně přepsat nějaké výpočty. Skrývat sloupce Označíte požadovaný sloupec(e) a pravým tlačítkem a z nabídky vyberte Skrýt.


30

Skrývat řádky Označíte požadovaný řádek/řádky a pravým tlačítkem a z nabídky vyberte Skrýt.

Zobrazit jen tabulku (skrýt ostatní) V předchozí kapitole jsme probrali jak skrývat řádky/sloupce. Co když ale potřebujete z listu zobrazit jen tabulku. Tj. musíte skrýt řádky a sloupce od konce tabulky doprava a dolů. Musíte vybrat všechny sloupce/řádky za/pod tabulkou a poté je skrýt. Takže postup: 

1a) - označíte první prázdný sloupce za tabulkou a klávesou Ctrl + Shift + šipka doprava.  1b) - pravým tlačítkem a z nabídky vyberte Skrýt  1a) - označíte první prázdný řádek za tabulkou a klávesou Ctrl + Shift + šipka dolů.  2b) - pravým tlačítkem a z nabídky vyberte Skrýt Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti 31

Zobrazit řádky/sloupce. Skrýt řádky (sloupce) umíte. Zobrazit řádky/sloupce provedete karta Domů sekce Buňky ikona Formát z menu Skrýt a zobrazit - zobrazit skryté řádky nebo zobrazit skryté sloupce


32

8. Kopírovat - buňky, řádky, sloupce Jak zkopírovat řádek, sloupce, buňku. Na výběr máte ze tří možnosti provedení dané operace:   

Klávesová zkratka Ikony na pásu karet Myš

Kopírovat buňku Klávesové zkratky: Označte buňku kterou chcete zkopírovat, stisk klávesové zkratky Ctrl+C. Máte hotovo (ve schránce máte požadované údaje). Vkládaní viz další kapitola. Poznámka: Klávesová zkratka pro vložit a stiskněte Ctrl+V, klávesová zkratka Ctrl+X je pro vyjmout (tj. kopírovaná buňka zmizí, je pouze ve schránce). Ikona Na kartě Domů skupina Schránka ikona Kopírovat, a data máte ve schránce. Vkládaní je popsáno v další kapitole, zde ve zkratce přesunete se na místo, kam chcete zkopírovanou buňku vložit a opět na kartě Domů skupina Schránka, ale ikona Vložit a máte hotovo.

Myš označenou buňku klik pravým tlačítkem a v zobrazeném menu vybrat Kopírovat a opět mate data ve schránce. Kopírovat řádek(y), sloupec (sloupce) Obdobně jako kopírovaní buňky (buněk), označíte požadovaný řádek, sloupec posléze pomocí klávesových zkratek, nebo pomocí pasu karet provedete zkopírování.


33

9. Vložit - buňky, řádky, sloupce Jak vložit řádek, sloupce, buňku. Vkládat můžete:  

Hodnoty ze schránky Prázdné řádky/sloupce

Pro vkládání hodnot předpokládám, že buňku, řádek(y), sloupec máte zkopírované ve schránce (jak na to jsem popsal v předchozím odstavci). Případně ve schránce máte vkládané hodnoty z jiné aplikace. Opět jako u kopírování máte pro vložení na výběr:   

Klávesová zkratka Ikony na pásu karet Myš

Vložit buňku Klávesové zkratky: Označte buňku, kde chcete vložit a stiskněte Ctrl +V. Máte hotovo. Ikona Přesunete kurzor na místo, kam chcete zkopírovanou buňku vložit a opět na kartě Domů skupina Schránka, ale ikona Vložit a máte hotovo.

Myš označenou buňku klik pravým tlačítkem a v zobrazeném menu vybrat Vložit a opět data ze schránky budou vložena. Vložit řádek/sloupec Podobně jako u vložení buňky u vkládaní řádků/sloupců nejprve označíte místo kam chcete vložit a pak vložíte, jak Vám vyhovuje (myš, klávesová zkratka, pás karet).


34

10. Přesun - buňky, řádky, sloupce Jak přesouvat řádek, sloupce, buňku. Pokud potřebujete přemístit buňky, řádky, sloupce. Pokud se vám nehodí kopírování a vkládání. můžete dané části přesouvat. Na výběr máte stejně jako u kopírování a vkládaní několik možnosti:   

Klávesové zkratky Ikony na pásu karet Myš

11. Odstranit (Smazat) - Buňky, řádky, sloupce Jak odstranit řádek, sloupce, buňku ve vaší excelovské tabulce. Nejjednodušší je použít klávesovou zkratku, označit buňku a stisk Delete. Ikona na pásu karet Na kartě Domů skupina Schránka ikona Vyjmout. Případně na kartě Domů sekce Buňky Myš označenou buňku klik pravým tlačítkem a v zobrazeném menu vybrat Odstranit... ikona Odstranit z nabízeného menu vyberte co chcete odstranit zda řádek/sloupc/buňku.

Příčky ukotvení Ukotvení příček se používá pro rozsáhlejší tabulky, kdy chcete mít k dispozici záhlaví tabulek (neplést si se záhlavím listů). Pro ukotvení označte buňku nad kterou chcete řádky ukotvit. Na kartě Zobrazení sekce Okno ikona Ukotvit příčky z nabídnutého menu vyberte, jak chcete příčky ukotvit: Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

35

  

ukotvit příčky Ukotvit horní řádek Ukotvit první sloupec

12. Záhlaví a zápatí Pás karet Vložení sekce Text ikona Záhlaví. Zobrazí se Vám možnost vkládat záhlaví a zápatí.


36

13. Úpravy listu - posuvníky, mřížky Nastavování se provádí na kartě Zobrazení v sekci Zobrazit. Vybrat (zatrhnout můžete):    

Pravítko řádek vzorců Mřížka Záhlaví

14.Šířky sloupců / výšky řádků Pro změnu šířky řádku sloupce. Požadované řádky (sloupce) označíte. Změnu lze provést:  

Pomocí menu (ikony) na kartě Myši

Ikonou na kartě Karta Domů - sekce Buňky Formát - Podle požadavku vybrat Výška řádku nebo šířka sloupce v zobrazeném dialogovém okně natavit se požadované hodnoty (pro výšku šířku).


37

Myši Jakmile máte označeny sloupce (řádky) přesunete se kurzorem myši mezi sloupce. Ikona kurzoru se změní. Klik a roztažení (stažení) velikosti. Všechny označené sloupce (řádky) budou mít stejnou šířku (výšku).


38

15. Podmíněné formátování Na kartě Domů vybereme Podmíněné formátování.

Na výběr máme z několika možností:

       

Zvýraznit pravidla podle buněk Nejpoužívanější či nejméně používaná pravidla Datové řady Barevné škály Sady ikon Nové pravidlo - přidat Vymazat pravidla Správa pravidel

Správa pravidel


39

Pro nový list je správa pravidel prázdná. Jelikož není k dispozici žádné pravidlo, je k dispozici tlačítko Nové pravidlo. To přidá první pravidlo.

V případě, že je k dispozici nějaké pravidlo, je možnost:   

Upravit pravidlo Odstranit pravidlo Nové pravidlo – přidání pravidla

Budete-li mít nastaveno velké množství pravidel podmíněného formátování, můžete si pravidla filtrovat.


40

Formátování nového pravidla (1) Formátovat všechny buňky na základě hodnot

U formátování podle tohoto pravidla lze nastavit: 



  

Styl formátování o Dvoubarevná škála o Tříbarevná škála o Datová řada o Sada ikon Typ - Dle vybraného stylu formátování o Číslo o Procenta o Vzorec Barva Náhled - jak bude formátování vypadat Ikona - pro styl formátování sada ikon o Styl ikony o Potřebné podmínky

Ukázka nastavení formátování pro styl - sada ikon


41

(2) Formátovat pouze buňky obsahující

Lze formátovat buňky podle: 







Hodnota buňky o Je mezi o Není mezi o Je rovno o Není rovno o Je větší než o Je menší než o Je větší než nebo rovno o Je menší než nebo rovno Určit text o Obsahuje o Neobsahuje o Má na začátku o Nemá na začátku Data připadající na následující dny o Včera o Dnes o ... Prázdné buňky


42

  

Jiné než prázdné buňky Chyby Bez chyb

(3) Formátovat pouze hodnoty zařazené jako první nebo poslední

Možnost formátovat hodnoty z rozsahu nebo určitou procentní část. (4) Formátovat pouze hodnoty nad nebo pod průměrem


43

Pro formátování hodnoty máme na výběr:          

Nad Pod Rovno nebo nad Rovno nebo pod 1 směrodatná odchylka nad 1 směrodatná odchylka pod 2 směrodatná odchylka nad 2 směrodatná odchylka pod 3 směrodatná odchylka nad 3 směrodatná odchylka pod

(5) Formátovat pouze jedinečné nebo duplicitní hodnoty

Na výběr máme:  

Duplikovat hodnoty ve vybrané oblasti Jedinečné hodnoty ve vybrané oblasti


44

(6) Určit buňky k formátování podle vzorce

Pokud nám nevyhovuje žádná z předešlých možností, můžeme si vytvořit vlastní vzorec. Formát Vedle náhledu je k dispozici Formát. Kde dále můžete upravit a nastavit přesněji jak má požadovaná buňka vypadat. Toto nastavení někdy způsobí, že se marně pokoušíte změnit podmíněně formátovánou buňku a ono to nejde.


45

16. Styly buňky Pokud pro jednu buňku nastavujete nějaké složitější formátování, můžete s výhodou použít styl buněk (místo kopírování formátu). Pod pojmem styl buňky si můžete přestavit nějakou sadu charakteristik (parametrů) buňky jako je například velikost písma, typ písma (kurzíva), barva písma, ohraničení buňky, zarovnání textu, formát čísla, zamykání). Styl můžete nadefinovat také pro celou tabulku.

Jak přiřadit styl buňce Začněme tím nejjednodušším, použijeme přednastavené styly, které jsou v Microsoft Excelu k dispozici. Nejprve vyberete buňku (buňky) kterým chcete přiřadit styl. Přiřazení stylu vybraným buňkám provedete na kartě Domů sekce styly ikona Styly buňky a vybrat požadovaný styl. V mojí ukázce Zvýraznění 1

HOTOVO


46

Poznámka: styl normální odstranit nelze.

17. Styly tabulky Pro automatické naformátování tabulky můžete s výhodou použít styl tabulky (místo složitého ručního formátování). Pod pojmem styl si můžete přestavit nějakou sadu charakteristik (parametrů) tabulky jako je například velikost písma, typ písma (kurzíva, tučné), barva písma, ohraničení tabulky, zarovnání textu, formát čísla, atd. Výhody   

Doplnění vzorce jen jednou - do zbytku se překopíruje automaticky Nemusí se kontrolovat vzorce - celý sloupec má "konzistentní vzorec" strukturované odkazy

Nevýhody 

Celý sloupce jednotný vzorec - nelze některým buňkám ve sloupci přiřadit odlišný vzorec

Poznámka Styl můžete nadefinovat nejen pro celou tabulku, ale také pro jednotlivou buňku.

Přiřadit tabulce styl S chuti do toho půl je hotovo. Dost bylo teorie jdeme na styly prakticky. Ukázková tabulka Pro další práci předpokládám, že máte k dispozici obdobnou "zdrojovou" tabulku.


47

Použití stylů budu demonstrovat na této zdrojové tabulce, které přiřadíme styl. Přiřazení stylu Nejprve celou tabulku označte. Přiřazení stylu této vybrané tabulce provedete na kartě Domů sekce Styly ikona Formátovat tabulku jako

a vyberete požadovaný styl pro tabulku.


48

Zobrazí se dialogové okno Formátovat jako tabulku.


49

Pokud tabulka obsahuje záhlaví, nezapomeňte, v tomto dialogovém okně zaškrtnout Tabulka obsahuje záhlaví. Pak jen klik na OK a je hotovo. Hotovo Při správném nastavené obdržíte tabulku se styly, bude vypadat podobně:

18. Vložení snímku obrazovky Na kartě Vložení v sekci Ilustrace kliknete na tlačítko Snímek obrazovky ikona Výřez obrazovky.

Vybraná oblast se vloží přímo do Microsoft Excelu na pozici kurzoru.


50

19. Vložit symbol Karta vložit Z karty Vložit (Insert) vybereme Symbol.


51

20. Funkce v MS Excel Seznam kategorií            

Databáze Logické Datum a čas Informace - Informační Vyhledávací Textové Matematické a trigonometrické Práce s krychlemi Inženýrské (konstrukce) Finanční Statistické Vlastní

KDYŽ / IF - funkce logická - MS Excel Na praktických příkladech ukázat použití jedné z nejdůležitějších a nejpoužívanějších funkcí KDYŽ / IF v MS Excelu.        

 

Syntaxe a popis parametru KDYŽ - jednoduchý příklad - pro její pochopení ukázka pro jednu podmínku Obrácena logika funkce KDYŽ - možnost negovat tuto funkci KDYŽ - více podmínek - aneb jak vnořovat funkce KDYŽ Možné chyby ve funkci KDYŽ - jak hledat a odstraňovat chyby Kombinace s jinými funkcemi - například s A, NEBO, atd. Ošetřování chyb v buňkách a nejen chyb - s pomocí funkci informace např. JE.CHYBA, JE.CHYBHODN, atd. KDYŽ ve spojení datum a čas o KDYŽ s časem - rozhodování podle času o KDYŽ s datum - rozhodování podle data / datumu o Hledání věkové hranice - ve spojení s funkcí CONCATENATE, atd. KDYŽ a MIN (MAX) ... - jak na kombinaci funkci HODNOTA.NA.TEXT - kombinace funkcí KDYŽ a HODNOTA.NA.TEXT (správné zobrazení roku pro EN/CZ Excel)

KDYŽ - IF ... syntaxe Vrátí zadanou hodnotu, pokud je zadaná podmínka vyhodnocena jako PRAVDA, a jinou hodnotu, pokud je zadaná podmínka vyhodnocena jako NEPRAVDA. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

52

Syntaxe v češtině =KDYŽ(Podmínka; Ano; Ne) Syntaxe v angličtině =IF(Podmínka; Ano; Ne) Poznámka: funguje i v google dokumentech. Popis parametru:   

Podmínka - libovolná hodnota (výraz), který může nabývat hodnotu PRAVDA nebo NEPRAVDA Ano - je vrácená zadaná hodnota, je-li hodnota argumentu podmínka PRAVDA. Ne - je vrácená hodnota, je-li hodnota argumentu podmínka NEPRAVDA.

IF(Podmínka, Ano, Ne) Parametr - Podmínka - může být:      

= je rovno <> není rovno > je větší než < je menší než >= je větší nebo rovno <= je menší nebo rovno

Teorií máte za sebou a jdeme na praktické příklady.

KDYŽ jednoduchý příklad (jedna podmínka). Začneme jednoduchým úkolem. Pokud hodnota v požadované buňce A9 je jedna 1 (zadána číslem) potřebujete vypsat: jednička. Řešení: =KDYŽ(A9=1;"jednička";"jiné číslo")

Je funkce pochopitelná tak pokračujme.

KDYŽ - použití dvou podmínek - jak vnořovat funkce Do jedné buňky A26 se má zadat jednička 1 a do buňky A27 zadejte slovo test. Pokud je zadána jen jednička obdržíte info Zadána jen jednička., pokud jen slovo test zadáno jen Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

53

slovo test, pokud jednička i slovo test Zadána jednička a slovo test. pokud nic Nezadáno nic co je požadováno. Řešení: =KDYŽ(A26=1;KDYŽ(A27="test";"Zadána jednička a slovo test.";"Zadána jen jednička.");KDYŽ(A27="test";"zadáno jen slovo test";"Nezadáno nic co je požadováno."))

Řešení další - lze využít i dalších logických funkcí jako A a NEBO což ukáži na dalších příkladech. Poznámka: Funkce můžete vnořovat více. Od MS Excel 2007 (do verze 2003 je vnoření pouze 7) vnoření může být až 64! Nejvíce jsem použil 10 vnoření, ale třeba někdy v budoucnu.


54

21. Grafy Typy grafů v Excelu 2010 V aplikaci Excel 2010 máme k dispozici řadu různých typů grafů, které můžeme použít k prezentaci dat. Seznam grafů, které jsou v MS Excel k dispozici:           

Sloupcový graf - Column Chart Spojnicový graf (čárový) - Line Chart Pruhový graf - Bar Chart Plošný graf - Area Chart XY bodový graf - XY (Scatter) Chart Burzovní graf - Stock Chart Povrchový graf - Surface Chart Prstencový graf - Doughnut Chart Bublinový graf - Bubble Chart Výsečový graf - Pie Chart Paprskové grafy - Radar Chart

Pokud si nevybereme ani jeden grafů, můžeme vytvořit vlastní, který si můžeme uložit do šablon pro pozdější použití.

Jednotlivé typy grafů podrobněji Vybrat graf můžeme přes kartu Vložit (Insert). - Všechny typy grafů. (All Chart Types.)


55

Sloupcový graf - Column Chart Hodnoty zobrazeny jako sloupce. Výška sloupce dle datových hodnot.

Sloupcové grafy se mimo jiné hodí pro zobrazení datových hodnot v závislosti na časovém období (stoupající cena akcií či zisku), nebo kategorie (například počet obyvatel v krajských městech, atd.). K vizuálnímu zobrazení hodnot, které porovnáváte (Porovnávaní dvou výrobků). Na vodorovné ose je čas, kategorie a na svislé příslušné hodnoty. Podobný typ grafu nalezneme pod názvem pruhový (jeho popis je níže).

Spojnicový (čárový) graf - Line Chart Hodnoty zobrazeny jako body (možnost spojit čárou). X-ová osa rovnoměrně rozdělená.


56

Jejich použití je výhodné nejenom pro souvislá data, která jsou rovnoměrně rozdělená. Jsou ideální pro přehledné porovnání trendů (např. pravidelné měření teploty). Tento graf je ekvivalentem bodového grafu.

Výsečový graf - Pie Chart Hodnoty zobrazeny jako poměr ze 100% z kruhu. Výseče představují poměr z celkových 100%.

Výhodné pro data, jež máme v jednom sloupci (řádku). Ve výsečových grafech lze rozbalit pouze jednu datovou řadu, kde se zobrazují jako procentní částka celku (tj. 100% odpovědí, 50% mužů, 50% žen). Prezentace dotazníku, statistik (počet mužů a žen, věkové rozpoložení atd.). Při vhodném použití jsou tyto grafy velice přehledné. Mnohdy je používán v televizi jako prezentace odpovědi na dotazníkové šetření. Obdobou tohoto grafu vychází graf prstencový (který je popsán níže).

Pruhový graf - Bar Chart Hodnoty zobrazeny jako pruhy. Šířka pruhu dle datových hodnot.


57

Využití pro porovnání položek (např. průměrná mzda v jednotlivých státech EU, atd.). Někdy vhodnější než sloupcové, pokud jsou popisky os jsou příliš dlouhé. Podobný typ grafu je pod názvem sloupcový (jeho popis je uveden výše).

Plošný graf - Area Chart Hodnoty zobrazeny jako body spojené čarou a s výplní pod touto čarou. X-ová osa rovnoměrně rozdělená.

Vycházejí částečně ze spojnicových (čárových) grafů. Jen jsou designově upraveny tak, aby zaujaly pozornost na plochu. Využít lze pro demonstraci poměru obratu a zisku společnosti, atd.


58

XY bodový graf - XY (Scatter) Chart Výhodné pro zobrazení průběhu funkce y=f(x). Pro jednu x-ovou osu může být zobrazeno několik průběhu y (až dvě nezávislé osy y).

Tento typ grafů je mnou nejpoužívanější, lze volit měřítko (tj. graf umí zobrazit logaritmickou osu, případně 2 y-ové osy, atd.), zvládá nestejnoměrné rozložení hodnot. Pro určité hodnoty lze vytvořit čárový graf, některé hodnoty nechat pouze bodové, atd.

Povrchový graf - Surface Chart

Vychází z plošného grafu. Pohled na data ve 3D. Pro modelaci speciálních matematických funkcí. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

59

Prstencový graf - Doughnut Chart

Vychází z grafu výsečového, tj. hodí se, pro stejné účely jen design navenek vypadá jinak. Volba mezi tímto a výsečovým grafem spíše závisí na estetickém cítění, než na vstupních datech (ty v obou případech mohou být stejné). V prstencových lze do jedné výseče umístit několik datových řad.

Řazení dat    

Proč řadit data Zdrojová tabulka A/Z - Z/A Seřadit

Proč řadit data Možná si pokládáte otázku proč řadit data? Důvodů může být mnoho, mezi základní patří:   

Přehlednost - například abecední seznam pracovníku (rychleji najdete hledaného zaměstnance) Podmínka pro správné fungování - pro správnou funkci některých funkcí je potřeba, aby data v tabulce byly řazeny vzestupně (sestupně) Podklady pro grafy


60

Zdrojová tabulka Pro praktickou ukázku, jak funkce seřadit v Excelu funguje, doporučuji vytvořit (nebo stáhnout viz dále) následující tabulku. Na této tabulce ukáži jednotlivé možnosti řazení.

A/Z nebo Z/A (vzestupně / sestupně) Pro rychlé seřazení podle vybraného sloupce. Před seřazením doporučuji označit celou tabulku, kterou chcete seřadit (bez záhlaví). Ikony se nachází na kartě Data v sekci Seřadit a filtrovat. Klik na příslušnou ikonu A/Z nebo Z/A. Excel seřadí se veškerá data v tabulce podle prvního sloupce (vzestupně / sestupně).

Poznámka: pokud máte zároveň nastaven v tabulce filtr, stačí označit buňku ve sloupci, podle kterého chcete data seřadit. Ovládání klávesnici Pro řazení lze použít ovládání pomocí klávesnice (tj. postupný stisk kláves): Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

61

 

Alt - W - Q Alt - W - S

Seřadit Před seřazením doporučuji označit celou tabulku, kterou chcete seřadit (bez záhlaví). Ikona se nachází na kartě Data v sekci Seřadit a filtrovat. Klik na ikonu seřadit, obdržíte dialogové okno Seřadit

Karta Domů - sekce Úpravy Poznámka: Řazení se nachází i na kartě Domů v sekci Úpravy ikona Seřadit a filtrovat a v zobrazeném menu vybrat Vlastní řazení zobrazí se stejné dialogové okno Seřadit.


62

22. Kontingenční tabulka Hledáte-li v čem vytvořit interaktivní report, provést sumarizaci dat z rozsáhlých tabulek, nehledejte. Použijte kontingenční tabulku. Pomocí kontingenční tabulky můžete data analyzovat, provádět souhrny, třídění, výpočty, tvořit kontingenční grafy. Prezentovaná data budou snáze pochopitelná a nejen pro prezentování, ale také pro následné rozhodování. Předpoklad V následujících kapitolách předpokládáme, že budete mít k dispozici následující tabulku se zdrojovými daty.


63

Vytvoření první kontingenční tabulky Cílem je vytvořit kontingenční tabulku. Potřebujeme seznam rozčlenit podle kategorií a v jednotlivých kategoriích mít přehledně uspořádání výrobky. Ve zdrojové tabulce si vyberte nějakou buňku. Na kartě Vložení sekce Tabulky sekce Kontingenční tabulka a z menu vybrat Kontingenční tabulka.

Vytvořit kontingenční tabulku Obdržíte dialogové okno Vytvořit kontingenční tabulku.

V dialogovém okně Vytvořit kontingenční tabulku nastavíte (něco je nastaveno automaticky)   

Zvolte data, která chcete analyzovat: - je zaškrtnuto Vybrat tabulku či oblast - toto je přednastaveno automaticky Zvolte umístění sestavy kontingenční tabulky: - pro přehlednost zaškrtněte Existující list a do Umístění: zadejte horní levý roh pro kontingenční tabulku Zadani!$M$4 OK - potvrdit


64

Seznam polí kontingenční tabulky

V seznam polí kontingenční tabulky stačí zaškrtnout tato pole:    

Kategorie Produkt Ks Cena

Zaškrtnutím se automaticky tvoří kontingenční tabulka (průběžně jak zaškrtáváte, jde o interaktivní tvorbu)


65

Kontingenční tabulku máte hotovou.


66

Trochu úprav kontingenční tabulky


67

V kontingenční tabulce klik na Popisky řádků a zaškrtněte jen Horolezectví kontingenční tabulka se vyfiltruje:

Dále můžete do kontingenční tabulky přidat další sloupec v Seznam polí kontingenční tabulky zaškrtněte Prodej kontingenční tabulka se rozšíří o další sloupce.


68

Počítané (výpočtové) pole - Kontingenční tabulka Při tvorbě kontingenčních tabulek, je někdy potřeba vložit další výpočty. Jelikož chceme mít kontingenční tabulku interaktivní, budeme výpočty zadávat jako další pole. V Excelu 2010 pomocí Počítané pole v Excelu 2007 pomocí Výpočtové pole. V dalším textu tedy budu zmiňovat Počítané pole, ale myslím tím zároveň výpočtové pole pro Excel 2007. Předpoklad V textu předpokládáme, že máte hotovou kontingenční tabulku pro tuto zdrojovou tabulku. Pro názornost má Popisky řádků filtr na Horolezectví.

Vytvoření výpočtového pole Cílem je přidat pole Nákup Prodej * 0,8 a zisk Prodej - Nákup. Na dočasné kartě Nástroje kontingenční tabulky karta Možnosti sekce Výpočty vybrat Pole, položky, sady a z menu Počítané pole.

Obdržíte dialogové okno Vložit počítané pole. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

69

V tomto okně nastavíte:    

Název - místo přednastaveného popisu zadáte Nákup Vzorec V Pole: vybrat Prodej klik na Vložit pole a k Vzorec přidat * 0,8 Přidat - pole se automaticky přidá (tlačítku se změní popis z přidat na změnit) OK - pokud již nechcete přidávat další pole, stačí jen klik na OK

Obdobně přidáte Pole Zisk. Kontingenční tabulka bude doplněna o vaše počítané pole  

Nákup Zisk


70

Změnit/odstranit počítané (výpočtové) pole Počítané pole již umíte vložit. Jeho změna/odstranění je stejně jednoduché. Na dočasné kartě Nástroje kontingenční tabulky karta Možnosti sekce Výpočty vybrat Pole, položky, sady a z menu Počítané pole. Obdržíte dialogové okno Vložit počítané pole, kde v název vyberete počítané pole, které chcete změnit/odstranit a provedete změnu/odstranění.

Seznam vzorců Máte-li v kontingenční více vzorců - je vhodné mít v nich přehled. Tuto akci opět můžete provést automaticky. Na dočasné kartě Nástroje kontingenční tabulky karta Možnosti sekce Výpočty vybrat Pole, položky, sady a z menu Seznam vzorců. Na dalším listu obdržíte přehled vzorců (funkcí) ve vašich Počítaných polích.

Násobení dvou polí Budete-li v počítaném poli násobit dvě pole ze zdrojové tabulky, možná se vám bude zdát, že Excel počítá nesprávně. Jenže počítá správně, násobí pole * pole, což provádí i pro pole součtová (takže v součtových polích nejde o součet, ale stále o násobení těchto dvou součtových polí ze základní tabulky.) Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

71

Možná lépe pochopíte na ukázce:

Jak z toho ven? Například provést toto vynásobení v zdrojové tabulce a pak se na tento sloupec jen odkázat.

Nastavení polí hodnot - Kontingenční tabulka Jak v kontingenční tabulce nastavit pole hodnot? Místo automatických součtu nastavit průměry, maximální, minimální hodnoty, jak přepočítat na procenta, atd. Kontingenční tabulky jsou velice mocný nástroj, při započtení jejich jednoduché a rychlé tvorby jsou obrovským pomocníkem. Jen je potřeba vědět pár věcí, kde, co a jak nastavit.


72

Zdrojová kontingenční tabulka

Úpravy vlastní kontingenční tabulky Cílem je upravit pole v kontingenční tabulce z důvodu potřeby prezentována data dostat do názornější pochopitelnější podoby. Redukce (filtr) kontingenční tabulky Naučíme se filtrovat data v kontingenční tabulce. V kontingenční tabulce klik na Popisky řádků a zaškrtnete jen Horolezectví, kontingenční tabulka se vyfiltruje:


73

Přidání sloupce kontingenční tabulky Dále můžete do kontingenční tabulky přidat další sloupec v Seznam polí kontingenční tabulky zaškrtnete Prodej, kontingenční tabulka se rozšíří o další sloupce.

Ubrání sloupce kontingenční tabulky Ubraní/odstranění sloupce se provede přesně naopak - odškrtne se požadovaný sloupec v Seznam polí kontingenční tabulky.

Nastavení polí hodnot v kontingenční tabulce Poznámka: Data v kontingenční tabulce jsou z důvodů přehlednosti filtrovaná. V Seznam polí kontingenční tabulky do pole Hodnoty (klik na šipku) z menu vybrat Nastavení polí hodnot.


74

V zobrazeném dialogovém okně Nastavení polí hodnot Záložka Souhrn dat lze zvolit kritéria pro shrnutí polí:   

Součet Počet Průměr

Dále můžete změnit: Vlastní název.


75

Poznámka: Směrodatná odchylka je tam opravdu 2x, ale počítají rozdílně. Tlačítko Formát čísla umožňuje nastavit jak jinak formát čísla.

Záložka Zobrazit hodnoty jako umožňuje nastavit zobrazení hodnot jako: Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

76

    

Žádný výpočet % z celkového součtu % ze součtu sloupce % ze součtu řádků %z

V ukázkovém příklad nastavit Pro Počet z ks nastavte Počet Pro Součet z ks nastavte % ze součtu sloupce Výsledek


77

Kontingenční tabulka - rozdělené tabulky dat Pro spojení několika tabulek do jedné kontingenční se používá Průvodce kontingenční tabulkou a grafem. Jak správně se Průvodce kontingenční tabulkou a grafem spustit: Excel 2010 CZ 1. Použít klávesovou zkratkou ALT, V, Y 2. Další možností je přidání průvodce na panel nástrojů: Rychlý přístup. Excel 2007 CZ V Excel 2007 je nutno původce přidat na panel nástrojů: Rychlý přístup. Takže víte, jak průvodce spustit v další kapitole spojíme několik tabulek se zdrojovými daty do jedné v následující, které si zatím připravíte. Předpoklad Pro jednoduchost tabulku rozkopírujte na další listy (třeba dva)


78

Kontingenční tabulka pro nesouvislá data Tabulky máte na několika listech, například měsíční reporty a potřebujete pro ně vytvořit kontingenční tabulku. Pomocí klávesové zkratky ALT, V, Y zobrazíte Průvodce kontingenční tabulkou a grafem Průvodce kontingenční tabulkou a grafem Průvodce se skládá z několika kroků. Krok 1: Nejprve vyplníte: 

Kde se nacházejí data - Násobné oblasti sloučení


79



Jaký typ kontingenční tabulky - Kontingenční tabulka

Klik na Další

Krok 2a: Vyberete: 

Vytvořit jediné stránkové pole

Klik na Další

Krok 2b: Zadáte:  

Oblast - vložíte první oblast Přidat - oblast bude přidána


80



Chcete-li vložit další oblast, postup opakujte:

Máte-li přidána všechny oblasti klik na Další Krok 3/3: Zadáte místo kam chcete kontingenční tabulku umístit:  

Nový list - bude vytvořen nový list Existující list - odkážete se kam v existujícím listu se má kontingenční tabulka vložit

Sestavu dokončíte klikem na Dokončit.

Hotova sloučená kontingenční tabulka Kontingenční tabulku máte hotovou


81

Přidat Průvodce kontingenční tabulkou na panel: Rychlý přístup Excel 2010 CZ Na panelu nástrojů Rychlý přístup klik na šipku a zobrazit menu Přizpůsobit panel nástrojů Rychlý přístup a z menu vybrat Další příkazy.


82

V zobrazeném dialogovém okně Možnosti aplikace Excel nastavit: V rozbalovacím menu Zvolit příkaz z: Všechny příkazy. Ve všech zobrazených příkazech najít Průvodce kontingenční tabulkou a klik na Přidat >> se tato přidá na panel nástrojů Rychlý přístup pak jen potvrdit klikem na OK.

Ikona je k dispozici na panelu: Rychlý přístup Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

83

Seskupování dat v kontingenční tabulce - Excel Jednou z možností je využít klasických filtrů, což představuje přidat další sloupec do zdrojové tabulky (tato operace je pracná, zdlouhavá, neefektivní a zbytečná). Efektivní možností je využít seskupování, které je k dispozici v pásu karet.


84

Seskupení dat Cílem je v kontingenční tabulce seskupit data:  

měsíčně týdně

Seskupení dat - měsíčně Klik na některý z datumu v popisu řádku (potřebná ikona se stane přístupnou). Na kartě Nástroje kontingenční tabulky - Možnosti sekce Skupina ikona Skupinové pole. Obdržíte dialogové okno.


85

Dialogové okno - Seskupit Obdržíte dialogové okno Seskupit ve kterém zatrhnete Měsíce a potvrdit OK.

Kontingenční tabulka - měsíce - hotovo V kontingenční tabulce máte popisy řádku seskupené na měsíce.

Seskupení dat - týdenní Týdenní seskupení opět přes kartu Nástroje kontingenční tabulky - Možnosti sekce Skupina ikona Skupinové pole. V dialogovém okně Seskupit zatrhnete Dny a Počet dnů: vložit 7 pak jen potvrdit OK.


86

Poznámka: V dialogovém okně Souhrny můžete zadat Začátek nebo Konec pro další omezení zobrazených dat. Kontingenční tabulka - týdenní - hotovo V kontingenční tabulce máte popisy řádku seskupené na týdenní (7 dnů).


87

Průřezy (Slicers) - Filtry - Kontingenční tabulka Pro filtrování dat existují filtry, ale stačí jednou v Excelu 2010 vyzkoušet Průřezy (Slicers) a už vám tato funkce bude ve starších verzích Excelu chybět. V dřívějších verzích Excelu 2007 (2003), jste mohli využívat filtry (což je velký pomocník). Pokud ale bylo dat hodně, nastavit tyto filtry zabralo značný čas a jejich přehlednost nebyla až tak velká. Což se změnilo od Excel 2010, kde přibyla ikona Vložit průřez, v EN verzi Excelu je pod názvem Slicers.

Vložení průřezu (Slicers) do kontingenční tabulky Cílem je vyfiltrovat například dle Prodejce, Kategorie, Produktu. Začínáme: Vyberte kontingenční tabulku. Na dočasné kartě Nástroje kontingenční tabulky - Možnosti sekce Seřadit a Filtrovat ikona Vložit průřez a z menu vybrat Vložit průřez.

Obdržíte dialogové okno Vložit průřezy. V tomto okně vyberte:


88

  

Prodejce Kategorie Produkt

Klik na OK potvrdíte.

Excel automaticky vloží dané kategorie v samostatných oknech:


89

V jednotlivých dialogových oknech průřezu vyberte požadovaný filtr např. Horolezectví. Dojde ke změně kontingenční tabulky a zároveň vidíte, které položky jsou k dispozici, které nejsou, které se dají vybrat.

Klasický filtr


90

Jen pro zopakování, použití klasických filtrů v kontingenční tabulce. Stačí klik na šipku u popisky řádků a z rozbalovacího menu vybrat požadované.

Možnosti kontingenční tabulky (nastavení) Kontingenční tabulku si můžete v možnostech nastavit tak jak potřebujete. K dispozici jsou možností nastavení:      

Rozložení a formát Součty a filtry Zobrazení Tisk Data Alternativní text

V zahraniční literatuře se kontingenční tabulky vyskytují pod pojmem Pivot Tables.

Zobrazení dialogového okna možnosti Klik na vybranou kontingenční tabulku (můžete jich mít na listu více). Na dočasné kartě Nástroje kontingenční tabulky - Možnosti sekce Kontingenční tabulky ikona Možnosti. Pro jistotu můžete zkontrolovat Název kontingenční tabulky: tj. například Kontingenční tabulka 1.

V následujícím textu jsou podrobněji popsány jednotlivé záložky:

Rozložení a formát Pro nastavení formátu a rozložení, jak se tabulka bude chovat například po aktualizaci.


91

Rozložení  

Sloučit a vycentrovat buňky s popisky

Formát    

Pro chybové hodnoty zobrazit: Pro prázdné buňky zobrazit: Při aktualizaci automaticky upravit šířky sloupců Při aktualizaci zachovat formát buněk

Součty a filtry V tomto okně můžete nastavit, jak se v kontingenční tabulce zachovají součty, filtry, řazení.


92

 

Zobrazit celkové součty řádků Zobrazit celkové součty sloupců

Filtry  

Souhrn vyfiltrovaných položek stránky Povolit více filtrů u jednoho pole

Řazení 

Při řazení použít vlastní seznamy

Zobrazení Nastavení možností zobrazení - některé často používané volby jsou přímo na dočasném pasu karet. Nástroje kontingenční tabulky - Možnosti - sekce Zobrazit či skrýt. A seznam polí.


93

Zobrazení      

Zobrazit tlačítka pro rozbalení a sbalení Zobrazit kontextové popisky Zobrazit v popisech vlastnosti Zobrazit titulky a rozevírací nabídku filtru Zobrazit rozložení kontingenční tabulky Zobrazit řádek hodnot

Zobrazení  

Seřadit od A do Z Seřadit podle zdroje dat

Tisk Možností nastavení pro tisk.


94

  

Vytisknout tlačítka pro rozbalení a sbalení Opakovat popisky řádků na každé stránce Nastavit názvy při tisku

Data Možností nastavení při práci s daty.


95

  

Uložit zdrojová data se souborem - pokud se neuloží - zmenší se velikost ukládaného souboru Povolit funkci Zobrazit podrobnosti Aktualizovat data při otevření souboru

ZÍSKATKONTDATA funkce pro kontingenční tabulku Jak na funkce (vzorce) pro výpočty s kontingenčními tabulkami. Pokud potřebujete dále pracovat s hodnotami v kontingenční tabulce, je potřeba si uvědomit že daná hodnota není stále ne jednom místě, při aktualizaci se změní její umístění (stejně tak při použití filtrů). Použitím klasického odkazu - =P6+P7 vypadá na první pohled použitelně, ale jen do první aktualizace tabulky (už se odkazujete na jinou hodnotu). Proto je potřeba se odkazovat nezávisle na aktuálním stavu tabulky. Pro tyto případy je k dispozici funkce ZÍSKATKONTDATA která je představena v tomto článku. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

96

Teoreticky ZÍSKATKONTDATA (GETPIVOTDATA) Stačí klik a funkce je zadána. Popis funkce Funkce ZÍSKATKONTDATA vrátí data uložená v kontingenční tabulce. Pomocí funkce ZÍSKATKONTDATA lze načíst i souhrnná data z kontingenční tabulky, pokud jsou tato data k dispozici. Syntaxe ZÍSKATKONTDATA(Datové_pole; Kontingenční_tabulka; [Pole1; položka1; pole2; položka2];

ZÍSKATKONTDATA argumenty:   

Datové_pole - Povinný argument. Název datového pole, která chcete načíst. Zadává se v uvozovkách. Kontingenční_tabulka - Povinný argument. Odkaz na libovolnou buňku, oblast buněk nebo pojmenovanou oblast buněk v kontingenční tabulce Pole1, položka1, pole2, položka2 - Nepovinný argument. 1 až 126 dvojic názvů polí a názvů položek popisujících data, která chcete načíst o Pole* - podle čeho se třídí - "Prodejna" o Položka* - co se vybírá - "Brno"

Ukázka Ukázka použití pro kontingenční tabulku z úvodu: =ZÍSKATKONTDATA("Součet z Prodej";$M$4;"Prodejna";"Brno";"Kategorie ";"Fotbal")

Zadávání funkce ZÍSKATKONTDATA (GETPIVOTDATA) Nejrychleji se vloží funkce ZÍSKATKONTDATA tak, že napíšete = a kliknete na buňku z kontingenční tabulky. Ostatní se přidá automaticky samo.

Kontingenční graf - vytvoření Kontingenční graf jde o divný název (stejně jako kontingenční tabulka), ale zdání klame, tvorba interaktivních kontingenčních grafů je jednoduchá a intuitivní. Můžete rychle a efektně měnit pohled na zobrazená data. Když se k tomu přičte, že při změnách v kontingenčním grafu (tabulce) nezničíte zdrojová data, jde o další nespornou výhodu. Stejně jako u kontingenční tabulky můžete data analyzovat, provádět souhrny, třídění, výpočty, a z těchto dat poté vytvořit kontingenční graf. Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

97

Jako teoretický úvod předpokládám, že postačí a ukážeme si praktické použití.

Vytvoření kontingenčního grafu Naším cílem je vytvořit kontingenční graf. Vytvořit kontingenční graf není o nic těžší než vytvořit kontingenční tabulku. Začínáme, ve zdrojové tabulce si vyberte nějakou buňku. Na kartě Vložení sekce Tabulky sekce Kontingenční tabulka a z menu vybrat Kontingenční graf.

Vytvořit kontingenční tabulku s kontingenčním grafem Obdržíte dialogové okno: kontingenční tabulku s kontingenčním graf.

V tomto dialogovém okně nastavíte (něco je nastaveno automaticky)   

Zvolte data, která chcete analyzovat: - je zaškrtnuto Vybrat tabulku či oblast - toto je přednastaveno automaticky Zvolte umístění sestavy kontingenční tabulky: - pro přehlednost zaškrtněte Existující list a do Umístění: zadejte horní levý roh pro kontingenční tabulku Zadani!$M$4 OK - potvrdit


98

Seznam polí kontingenční tabulky (grafu) V seznamu polí kontingenční tabulky stačí zaškrtnout tato pole:  

Kategorie Prodej

Tvoříte graf, tak z důvodu přehlednosti vyberte jen klíčové položky. Zaškrtnutím se automaticky tvoří kontingenční tabulka a včetně kontingenčního grafu.

Kontingenční tabulku máte hotovou.

Zdroj: Pavel Lasák Evropský sociální fond Praha a EU – Investujeme do vaší budoucnosti

99

V rámci projektu Kvalifikovaný pracovní tým v Auto-Poly spol. s r.o. Kurz 15 MS EXCEL

Recommend Documents