hosting menu left
hosting menu right

top of hosting header
www.efektivne.eu
Microsoft Excel Excel 2013 I.
  • Délka školení - 10 hodin
  • Účastnická cena - 2450 Kč
  • Skupinové slevy
  • Vstupní test znalostí
  • Školení kdekoli v ČR
  • Možnost školení v angličtině
Microsoft Project Project 2013 I.
  • Délka školení - 10 hodin
  • Výborná cena 2950 Kč/osoba
  • Garantovaná kvalita
  • ZDARMA měsíční podpora
  • Školení kdekoli v ČR
  • Možnost školení v angličtině
Microsoft Visio Visio 2013 I.
  • Délka školení - 5 hodin
  • Účastnická cena 1950 Kč
  • Přizpůsobní požadavkům
  • Vstupní test znalostí
  • Školení kdekoli v ČR
  • Možnost školení v angličtině


bottom of hosting header
website hosting main area top

Poslední komentáře

Školení MS Excel 2010 - Poznámky
Napsal uživatel Daniel Mikláš   
pondělí, 25. dubna 2011 00:00

MS Excel 2010 - logoTento dokument si klade za cíl shrnout většinu běžně probíraných témat na našich školeních MS Excel 2010. Předpokládáme, že si účastníci na školení zapisují poznámky, které mohou s tímto dokumentem během nebo po školení konfrontovat. Dokument sám o sobě není dostatečně detailní, nepopisuje všechny možné postupy a řešení, ale vždy se snaží účastníka školení navést k cíli. Pokud si jako účastník/účastnice našeho školení s některým tématem nevíte rady, máte možnost se na nás obrátit a vyžádat si podrobnosti. Jestliže máte potřebu tištěných návodů, na některé kvalitní publikace o Excelu upozorňujeme ve zvláštním článku. Tyto knihy je možno dle dohody na školení zakoupit. Dokument je zaměřen na českou verzi MS Excelu 2010, pro větší názornost jsou klíčové anglické pojmy uvedeny v závorkách.

Dokument shrnuje následující témata, která jsou součástí většiny námi poskytovaných základních, pokračovacích i speciálních školení MS Excel 2010:


  • Praktické základy ovládání
  • Efektivní postupy vkládání dat
  • Práce se schránkou
  • Práce se soubory a zabezpečení dat
  • Formátování
  • Podmíněné formátování
  • Základní vzorce a funkce
  • Maticové funkce – práce s poli
  • Grafy a minigrafy
  • Databázové nástroje
  • Importy dat
  • Analytické nástroje
    • Citlivostní analýza
    • Hledání řešení
    • Scénáře
    • Řešitel
  • Tvorba maker a základy VBA
  • Ovládací prvky
  • Tisková nastavení
  • Novinky v MS Office 2010

Popis obrazovky

 

Excel 2010 - popis obrazovky - poznámky ze školení

Obrázek 1 - Popis obrazovky

Důležité pojmy:

  1. Nabídka Soubor (File)
  2. Panel snadné spuštění (Quick Access Toolbar)
  3. Pás karet (Ribbons)
  4. Pole názvů (Name Box)
  5. Řádek vzorců (Formula Bar)
  6. Horizontální a vertikální rozdělení okna (Split Window)
  7. Lupa (Zoom)
  8. Různá pracovní zobrazení (Views)
  9. Stavový řádek (Status Bar)
  10. Listy a jejich ovládání (Worksheets and Worksheets Buttons)

Základní operace:

Označování a přesuny buněk

Buňky se označují nejčastěji myší. Stačí přidržet levé tlačítko myší a táhnout požadovaným směrem. Alternativou může být označování přes klávesnici, kdy lze použít stisk SHIFT+kurzorové šipky. Pro nesouvislé označování buněk nebo oblastí buněk je vhodné přidržet CTRL a klasicky označovat. Pro označení celého řádku je vhodné provést klávesovou kombinaci SHIFT+Mezerník, pro označení sloupce zase CTRL+Mezerník.

Pozor, takto označujete 16 384 buněk v řádku, resp. 1 048 676 buněk ve sloupci.

Pro přesun buňky nebo skupiny buněk stačí buňku nebo skupinu chytit za hranu a přesunout na požadované místo. Pro přesun na jiný list si lze pomoci přidržením klávesy ALT.

Šířka sloupců, výška řádků

Šířku sloupců a výšku řádků změníte přetažením za přechodové místo mezi sloupcem nebo řádkem. Pro optimalizaci šířky nebo výšky vůči obsahu lze na přechodové místo provést dvojklik.

 

Úprava výšky řádku a šířky sloupce v Excelu 2010 - poznámky ze školení

Obrázek 2 - Úprava výšky řádků nebo šířky sloupců

Přesnou výšku řádku nebo šířku sloupce lze provést kliknutím pravou myší na záhlaví řádku nebo sloupce (na číslo řádku nebo písmeno sloupce) a následně vybrat příkaz Výška řádku, resp. Šířka sloupce (Row Height or Column Width).

Přidávání, odebírání řádků a sloupců

Řádky se vždy vkládají nad pozici aktuální buňky, sloupce zase vždy nalevo do pozice aktuální buňky. Lze tak provést např. klávesovou zkratkou CTRL+Plus, pro odstranění zase CTRL+Minus. Jiné varianty těchto operací jsou přes místní nabídku v záhlaví řádku nebo sloupce, případně na kartě Domů, ve skupině Buňky, příkazy Vložit a Odstranit (Home, Cells, Insert and Delete).

 

Nabídka pro vkládání a odstraňování řádků nebo sloupců v Excelu 2010

Obrázek 3 - Nabídky Vložit a Odstranit

Práce s listy

Mezi listy se přepínáte v pravé dolní části obrazovky. Listy lze dvojklikem na název (ouško) přejmenovat, případně pravou myší na názvu přebarvit přes příkaz Barva Karty (Tab Color).

 

Barva karty listu v Excelu 2010 - poznámky ze školení

Obrázek 4 - Barva karty listu

List lze uchopením za jeho název přesunout, případně s přidrženou klávesou CTRL vytvořit jeho identickou kopii. Kliknutím pravou myší na navigaci v listech lze zobrazit nebo přejít na jeden z 15 nejbližších listů.

 

Vyber Listu v Excelu 2010 - poznámky ze školení

Obrázek 5 - Výběr listů lze vyvolat pravým tlačítkem myši

Pro vložení nového listu použijte ikonu na konci seznamu listů nebo klávesovou kombinaci SHIFT+F11. Počet listů v Excelu 2010 je omezen pouze velikostí dostupné paměti RAM.

Ukládání do souboru a práce se soubory

Excel 2010 přináší nový formát ukládání dat, který umožňuje váš excelový soubor uložit s koncovkou XLSX. Formát XLSX je xml formát komprimovaný zipem, pro běžného uživatele Excelu je ale primární výhodou jeho automatická komprimace.

Pro uložení aktuálního souboru stačí přes nabídku Soubor (File) vybrat volbu Uložit jako (Save as), kde kromě výchozí volby Sešit aplikace Excel (Excel Worksheet) naleznete v rozbalovací nabídce Uložit jako typ (Save as type) všechny ostatní podporované formáty.

 

Dialogové okno Uložit jako v Excelu 2010 - poznámky ze školení

Obrázek 6 - Uložit jako

Otevírání souborů a práce s šablonami

Pro otevírání excelového souboru lze použít nabídku Soubor, volbu Otevřít (File, Open), kde lze snadno vybrat příslušný soubor. Pokud máte v úmyslu vytvářet nový excelový soubor, je vhodné nahlédnout do šablon. Šablony jsou připravené vzory dokumentů nebo tabulek, do kterých zadáte vlastní údaje. Do přehledu šablon se dostanete přes nabídku Soubor, volbu Nový a zde vyberete buď z již nainstalovaných šablon, nebo z šablon, které jsou k dispozici on-line na webu Microsoftu.

 

Šablony v Excelu 2010 - poznámky ze školení

Obrázek 7 - Šablony v MS Excel 2010

Efektivní postupy vkládání vstupních dat

 

Přehled základních vstupních údajů do Excelu 2010 - poznámky ze školení

Obrázek 8 - Základní vstupy

Poté, co do buňky vložíte jakýkoli vstup, proběhne kontrola vstupních dat. Pokud vložíte text, buňka se automaticky zarovná vlevo. Pokud vložíte jakékoli číslo, buňka se opět automaticky zarovná, nyní vpravo. Této vlastnosti lze částečně využít po importu dat ze složitých webových tabulek nebo některých méně kompatibilních firemních systémů – snadno poznáte, které buňky obsahující čísla jsou ve skutečnosti texty.

Datum

Pokud potřebujete do buňky vložit jakékoli běžné datum, můžete si pomoci numerickou klávesnicí, díky které snadno oddělíte dny, měsíce a roky lomítkem (dělítkem). Po zadání, např. 12/9/11 a ukončení Entrem, se tento vstup v buňce změní na správné datum 12.9.2011. Excel bohužel standardně datum neodděluje mezerami, což není správně česky, ale na druhou stranu ušetříte dvě mezery na šířce sloupce. Pokud byste přece jenom vyžadovali správný český zápis, lze jej upravit ve vlastních formátech čísla ve formátu buňky (Format Cells…|Number|Custom).

Velkou pomůckou je klávesová kombinace CTRL+; (středník), která do aktivní buňky vloží aktuální datum. Toto datum se nikdy samo od sebe nemění. V případě automatické aktualizace lze použít datumovou funkci DNES (TODAY). Zápis funkce je =DNES(), v anglickém prostředí =TODAY(). Tato funkce vždy zobrazí aktuální systémové datum.

Podobnou funkcí je NYNÍ. Zápis funkce je =NYNÍ(), v anglickém prostředí =NOW(), která kromě aktuálního data zobrazuje i aktuální čas.

Automatickou aktualizaci lze odstranit převodem funkce na hodnotu. Na buňce s funkcí stačí stisknout funkční klávesy F2, následně F9 a vše potvrdit Entrem.

Jak Excel zpracovává datum? Datum je číslo v rozsahu 1 (1. ledna 1900) až 2 958 465 (31. prosince 9999) a lze jej běžně používat v základních i pokročilých výpočtech.

Čas

Oddělovníkem časových údajů je : (dvojtečka). Pokud potřebujete zadat čas 8 hodin a 21 minut, do buňky jednoduše uvedete 8:21. Třetí běžnou úrovní časových údajů jsou vteřiny. Zápis je následující 8:21:10 (8 hodin 21 minut 10 vteřin). Pokud potřebujete zadat čas pouze v minutách a vteřinách, je nezbytné vždy uvádět i hodiny. Např. 0:21:10 (0 hodin 21 minut 10 vteřin).

Toto uspořádání je nezbytné pro časové výpočty. Pokud se odchýlíte, vstup bude počítán nesprávně nebo bude považován za text a nebude počítán vůbec. Pokud budete čas sčítat, ve výchozím nastavení se bohužel bude po dosažení součtu nulovat (12:00 + 13:00 se rovná 01:00). Pro kumulaci časových údajů při součtech nastavte vlastní formát čísla (času) do hranatých závorek – např. [h]:mm.

Klávesová zkratka pro vložení aktuálního času je CTRL+SHIFT+: (dvojtečka). Zobrazení aktuálního času lze provést úpravou masky čísla ve formátu buňky a funkcí NYNÍ (NOW). Převod na statickou hodnotu provedete podobně jako v případě data (viz výše).

Jak Excel zpracovává čas? Čas je zpracován jako zlomek. Jedna hodina je zpracována jako 1/24.

Zlomek

Pokud máte v úmyslu zadat zlomek a vložíte jej „běžným“ způsobem, např. jako 1/3 (jedna třetina), Excel z tohoto zápisu vytvoří datum. Je to dáno / (lomítkem), které Excel primárně považuje za oddělovník data.

Pro vložení zlomku je nutné zadat celé číslo, mezeru a následně zlomek – např. 0 1/3. Ve svém základu se opět jedná o číslo. Toto číslo lze zobrazit, pokud buňku se zlomkem naformátujete na číslo nebo obecný formát.

Vyšší zlomky se automaticky zjednodušují. Zápis 0 260/8 bude po ukončení entrem zjednodušen na 32 1/2.

Komentář

Komentář lze do buňky vložit např. přes pravé tlačítko myši nebo klávesovou zkratkou SHIFT+F2. Komentáře v buňkách lze zobrazovat ve třech režimech: a) přesunem myši na buňku s červeným indikátorem komentáře, b) přepnutím Zobrazit/Skrýt (Show/Hide Comments) přes pravé tlačítko myši na buňce s komentářem nebo c) přepínačem na kartě Revize, skupina Komentář, tlačítko Zobrazit všechny komentáře (Review, Comment, Show All Comments).

 

Úprava komentáře v Excelu 2010

Obrázek 9 - Úprava komentáře

Vnitřní plochu komentáře je možné přebarvit. Pro přebarvení doporučuji zobrazit všechny komentáře: karta Revize, skupina Komentář, tlačítko Zobrazit všechny komentáře (Review, Comment, Show All Comments). Poté je nutné kliknout pravým tlačítkem myši přesně na hranu komentáře a zvolit příkaz Formátovat komentář (Format Comment). V okně Formát komentáře (Format Comment) na kartě Barvy a čáry (Colors and Lines) je možné vybrat jednu ze základních barev, pokročilých barev (More Colors), nebo nastavit barvu výplně (Fill Effects).

Komentář nemusí mít základní obdélníkový tvar, ale je možno jej změnit. Pro změnu tvaru je nutné na Panel snadný přístup (Quick Access Toolbar) přidat tlačítko Změnit obrazec(Change Shape), které se nachází v seznamu Panelu snadný přístup (Quick Access Toolbar), volba Všechny příkazy (All Commands).

Řady, seznamy a vlastní seznamy

Do Excelu lze vložit některé textové nebo číselné vstupy, které Excel umí snadno rozvinout. Textové údaje se nazývají seznamy, číselné řady. Mezi předdefinované seznamy a řady patří:

  • Dny v týdnu
  • Zkratky dnů v týdnu
  • Měsíce
  • Římské číslování v rozsahu I. – XII.
  • Datum
  • Číslo

 

Seznamy a řady v Excelu 2010 - poznámky ze školení

Obrázek 10 - Usnadnění práce za použití seznamu

Pro použití stačí do buňky zapsat příslušnou hodnotu a za aktivní bod buňku vpravo dole uchopit a táhnout. Tuto operaci je možno provést i pravým tlačítkem myši, čímž dostanete vždy na výběr z několika možností.

 

Tvorba řady v Excelu 2010 - poznámky ze školení

Obrázek 11 - Varianty při tvorbě číselné řady

Excel 2010 nabízí tvorbu vlastních seznamů. Tyto seznamy je možno nadefinovat v nastavení Excelu, do kterého se dostanete přes nabídku Soubor, Možnosti a téměř dole najdete tlačítko Upravit vlastní seznamy (File, Options, Customize, Edit Custom Lists).

 

Vlastní Seznamy v Excelu 2010 - poznámky ze školení

Obrázek 12 - Tvorba a správa vlastních seznamů v Excelu 2010

Práce se schránkou

Pro kopírování mezi Excelem a dalšími aplikacemi lze použít běžné zkratky CTRL+C (kopírování), CTRL+X (vyjmutí) a CTRL+V (vložení). Stačí tedy ve Wordu, Powerpointu, internetovém prohlížeči nebo jiné aplikaci vámi požadovanou tabulku označit, zkopírovat a následně v Excelu vložit. Pokud se tabulka nevloží dle vašich požadavků, lze ji ihned po vložení upravit přes tlačítko možností.

 

Tlačítko možností pro úpravu vložených dat do Excelu 2010

Obrázek 13 - Tlačítko možností

Jestliže kopírujete více nesouvislého obsahu (např. při tvorbě prezentace), můžete si pomoci zapnutím vícenásobné schránky neboli schránky sady Office. Zapíná se na kartě Domů, skupina příkazů Schránka, tlačítko Schránka (Home, Clipboard, Clipboard).

 

Schránka sady Office 2010

Obrázek 14 - Schránka sady Office

V aplikacích MS Office lze kopírovat nejenom obsah (tabulky, texty, obrázky, grafy), ale i formáty (barva buňky, písma atd.). Pro kopírování formátů lze snadno použít tlačítko Kopírovat formát (Format Painter), které se nachází na kartě Domů ve skupině Schránka (Home, Clipboard).

 

Kopírování formátů v Excelu 2010 - poznámky ze školení

Obrázek 15 - Kopírovat formát

Pokud ikonu zvolíte pouze jednou, kopírujete formát jednorázově, tedy po označení první skupiny buněk již není možno formát bez následného kopírování formátu použít. Ikonu lze taktéž aktivovat dvojklikem, čímž kopírování formátu zapínáte trvale. Jeho zrušení lze provést buď klávesou Esc, nebo opětovným tisknutím tlačítka Kopírovat formát.

Jak má vypadat optimální excelová tabulka?

Ideální excelová tabulka by měla mít záhlaví, tedy řádek, který jasně pojmenovává, jaký typ dat se v konkrétním sloupci nachází. Záhlaví by mělo být vždy na jeden řádek. Pokud bude na více, excel jako záhlaví vždy identifikuje pouze řádek první.

Tabulky s daty by mezi sebou neměly mít prázdné řádky nebo sloupce. Pokud tomu tak bude, Excel to bude chápat jako seskupení několika samostatných tabulek.

Označování tabulek a rychlá navigace v rozsáhlých tabulkách

Pro rychlé označení tabulky stačí mít aktivní buňku v tabulce a zmáčknout klávesovou zkratku CTRL+A. Pokud se tabulka neoznačí včetně prvního řádku (záhlaví), zmáčkněte CTRL+A ještě jednou. A pokud chcete označit nikoli tabulku ale celý list, zmáčkněte CTRL+A potřetí.

Při označování tabulky kombinací CTRL+A zůstává aktivní buňka na své výchozí pozici. Naopak kombinací CTRL+* se po označení tabulky přesune aktivní buňka na pozici prvního řádku a sloupce ve výběru.

Pokud jste kdekoli v tabulce, klávesovými kombinacemi CTRL+kurzorové šipky se rychle přesunete k nejbližší prázdné nebo obsazené buňce, často na první nebo poslední řádek či sloupec v tabulce.

Klávesová kombinace CTRL+SHIFT+kurzorové šipky umožní označit oblast buněk od místa, kde se zrovna nachází aktivní buňka, před nejbližší obsazenou nebo prázdnou buňku.

Klávesová kombinace CTRL+Home vás přesune na první pohyblivou buňku ve všech směrech. Většinou to bude buňka A1. V případě ukotvení příček to ale bude buňka nacházející se nejblíže vlevo a nahoře k ukotvovacím příčkám. Klávesová kombinace CTRL+End přesouvá aktivní buňku na průsečík posledního obsazeného řádku a sloupce.

Rychlé formátování

Pro rychlé formátování stačí být kdekoli v tabulce a zvolit příkaz Formátovat jako tabulku (Format as Table), který se nachází na kartě Domů ve skupině příkazů Styly (Home, Styles).

 

Formátova jako tabulku - Nástroj Excelu 2010 pro rychlé formátování tabulek - poznámky ze školení

Obrázek 16 - Formátovat jako tabulku

Před samotným naformátováním vás nechá Excel 2010 potvrdit vstupní oblast buněk a po potvrzení je vaše tabulka okamžitě naformátovaná. Pokud se vám formát nelíbí, lze jej na stejném místě snadno změnit na jiný styl.

Styly buněk

Pro opakované formátování buněk nabízí Excel 2010 použití stylů buněk. Styl buňky je uložené formátování, často rozsáhlejší, které lze snadno aplikovat výběrem z nabídky. Styly (Cell Styles) buněk jsou k dispozici na kartě Domů, skupina příkazů Styly (Home, Styles), kde je lze vybírat, případně vytvářet vlastní.

 

Styly buněk v Excelu 2010 - poznámky ze školení

Obrázek 17 - Styly buněk

Formátování buněk

 

Formát buňky v Excelu 2010 - poznámky ze školení

Obrázek 18 - Formát buněk

Do okna pro podrobné formátování buněk se kromě běžných způsobů dostanete i klávesovou kombinací CTRL+SHIFT+1, v anglickém prostředí pouze CTRL+1, v obou případech na alfanumerické klávesnici. Zde lze velmi podrobně nastavit podobu:

  • Čísla
  • Zarovnání
  • Písma
  • Ohraničení
  • Výplně
  • Zámku

Mezi běžné způsoby formátu čísla patří volba formátu měny, data, času, zlomku a některých dalších čísel. Praktickou volbou jsou vlastní formáty (Custom Formats), v kterých si můžete nastavit vlastní masku čísla. Zde uvádím přehled základních operátorů pro tvorbu vlastních formátů:

Operátor v českém prostředí

Operátor v anglickém prostředí

Zobrazí se

#

 

Číslice kromě nuly

0

 

Číslice včetně nuly

mezera

, (čárka)

Oddělovač tisíců

, (čárka)

. (tečka)

Desetinný oddělovač

@

 

Text

" "

 

Text

d

 

Číslo dne 1-31

dd

 

Číslo dne 01-31

ddd

 

Zkratka dne (po,…)

dddd

 

Název dne

m

 

Číslo měsíce 1-12

mm

 

Číslo měsíce 01-12

mmmm

 

Název měsíce

mmmmm

 

První písmeno z názvu měsíce

rr

Yy

Číslo roku 00-99

rrr

yyyy

Číslo roku (2010)

h

 

Hodina 0-23

hh

 

Hodina 00-23

m

 

Minuta 0-59

mm

 

Minuta 00-59

s

 

Vteřiny 0-59

ss

 

Vteřiny 00-59

[h]:mm

 

Kumulovaný čas v hodinách a minutách

 

Ve vlastních formátech čísla je možno vytvářet i jednoduché podmínky: [červená][<100]0,000;[zelená][>1000]0000000,00;[modrá]0

Pro základní formátování čísla bude ale většině uživatelům stačit rozbalovací nabídka z karty Domů, ve skupině Číslo (Home, Number).

 

Formát čísla v Excelu 2010 - poznámky ze školení

Obrázek 19 - Rychlý způsob volby formátu čísla

Na kartě Zarovnání stojí za zmínku možnost zarovnání textu buněk vodorovně a svisle. Kromě základních voleb zde můžete narazit na Distribuované (Odsazení), což znamená rovnoměrné upořádání obsahu o více slovech (se stejnými mezerami).

V části Orientace lze měnit úhel, pod kterým se zobrazují buňky. Taktéž je zde možnost zobrazení obsahu buňky vertikálně, což se hodí u některých tabulek při popisování kategorií.

 

Formát buňky v Excelu 2010, karta Orientace - poznámky ze školení

Obrázek 20 - Vertikální zobrazení textu v tabulkách

Ostatní ovládací prvky snad nepotřebují výraznější vysvětlení. Pro ještě jednodušší ovládání jsou nejpodstatnější příkazy uvedeny na kartě Domů, skupina příkazů Zarovnání (Home, Alignment).

 

Zarovnání buněk na kartě Domů - poznámky ze školení

Obrázek 21 - Skupina příkazů Zarovnání na kartě Domů

Další karta v okně Formát buněk (Format Cells) se nazývá písmo (Font). Zde stojí za zmínku možnost formátovat obsah do horního nebo dolního indexu (Superscript, Subscript), případně různé styly podtržení písma.

Karta Ohraničení (Border) nabízí specifické způsoby ohraničování buňky nebo skupiny buněk.

Na kartě Výplň (Fill) můžete volit barvu výplně nebo barvu a styl vzorku v základní množině 80 barev, nebo můžete přes tlačítko Další barvy namíchat jakoukoli barvu z dostupné 24bitové palety barev (16 777 216). Zde lze dosáhnout zajímavých a originálních kombinací barev výplně buněk, stylů a vzorků.

Karta zámek (Protection) slouží pro nastavení buněk, které nebude možno zamknout. Jedná se pouze o přepínač, který určuje, zda bude buňka zamykatelná (výchozí stav všech buněk v Excelu) nebo ne. Samotné zamykání se prování na hlavní kartě Revize, příkaz Zamknout list (Review, Protect Sheet).

Excel 2010 nabízí nejfrekventovanější příkazy na kartě Domů, do okna Formát buněk zde nemáte důvod vstupovat tak často.

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

Podmíněné formátování je velmi jednoduchým a praktickým nástrojem pro zvýrazňování klíčových údajů v buňkách. Pro správné použití je vždy nutné vybrat buňku nebo oblast buněk. Následně stačí na kartě Domů, ve skupině příkazů Styly aktivovat tlačítko Podmíněné formátování (Home, Styles, Conditional Formatting).

 

Podmíněné formátování v Excelu 2010

Obrázek 22 - Podmíněné formátování buněk

Na výběr je z pěti kategorií:

  • Zvýraznit pravidla buněk
  • Nejpoužívanější či nejméně používaná pravidla
  • Datové pruhy
  • Barevné škály
  • Ikony

Kromě základních způsobů tvorby podmíněného formátování lze vytvářet vlastní podmínky, které nebudou závislé na obsahu buněk, ale na výsledcích vzorců. Tyto vlastní formáty lze vytvářet přes tlačítko Nové pravidlo v základní nabídce nebo přes Správce pravidel podmíněného formátování (Conditional Formatting Rules Manager). Mezi další praktické vlastnosti patří možnost podmíněně naformátovat (zvýraznit) duplicitní hodnoty a hodnoty nad průměrem a pod průměrem.

 

Podmíněné formátování buněk v Excelu 2010 - poznámky ze školení

Obrázek 23 - Podmíněné formátování nezávislé na obsahu buňky, ale na výsledku výpočtu

Výpočty

Jakýkoli výpočet v Excelu je doporučeno začínat značkou = (rovná se). Podle této značky Excel pozná, že se jedná o výpočet. Značku = lze nahradit za + (plus) nebo (mínus), což nedoporučuji, protože značkou + (plus) je definován vždy kladný vstup, naopak značkou – (mínus) záporný vstup. Pokud se v buňce A1 bude nacházet hodnota -10, vzorec -A1 zapsaný do buňky A2 vrátí nikoli -10, ale 10.

Excel 2010 rozlišuje tři hlavní typy vzorců:

  • Aritmetické
  • Logické (neboli porovnávací)
  • Textové

Aritmetické vzorce v Excelu převažují, v praxi jsou používány nejvíce. Tento typ vzorců v Excelu využívá operátory:

  • +             (plus)
  • -              (minus)
  • *             (krát)
  • /             (děleno)
  • ^             (mocnina – lze zadat přes klávesovou zkratku Pravý Alt + š nebo přes ACSII kód jako Levý Alt + 94)
  • Operátor odmocniny v Excelu není, je ale k dispozici funkce ODMOCNINA (pouze druhá), případně lze umocnit na zlomek, nebo použít funkci POWER a opět umocnit na zlomek.

Logické neboli porovnávací operátory jsou velmi častým pomocníkem, např. při využití funkce KDYŽ (IF). Jsou následující:

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

Tyto operátory lze snadno zadávat přes českou klávesnici kombinacemi Pravý Alt + , (čárka) a Pravý Alt + . (tečka).

Textový operátor je pouze jeden, nazývá se ampersand a zapisuje se takto: &. Tento operátor se používá pro řetězení textových vstupů a lze jej zastoupit textovou funkcí CONCATENATE. Použití ampersandu je ale ve většině případů jednodušší.

Při tvorbě vzorců je vhodné dodržet následující pravidla:

  1. Vyberte buňku, kde potřebujete provést výpočet
  2. Vložte = (rovná se)
  3. Označte nejlépe myší první vstup (nebo jej zapište z klávesnice)
  4. Vložte potřebný aritmetický, porovnávací nebo textový operátor
  5. Označte nejlépe myší druhý vstup (nebo jej zapište z klávesnice)
  6. Kroky 4 a 5 opakujte tolikrát, kolik potřebujete mít ve vzorci vstupů
  7. Výpočet ukončete stisknutím klávesy Enter

Do vzorce je většinou výhodné vkládat nikoli samotnou číselnou hodnotu, ale adresu buňky, v které se hodnota nachází. Výhoda je v tom, že při změně stačí přepsat hodnotu v buňce a není nutné následně upravovat vzorec samotný.

Vzorec v buňce poznáte mj. tak, že po výběru buňky se v řádku vzorců objeví nikoli hodnota, ale vzorec.

 

Vzorec v Excelu 2010 - poznámky ze školení

Obrázek 24 - Základní ukázka vzorce

Pro zobrazení vzorce v buňce nebo pro jeho změnu zmáčkněte na buňce se vzorcem funkční klávesu F2. Nyní můžete snadno provést změnu vzorce, kterou potvrdíte Enterem. Jestliže nepotřebujete vzorec měnit, lze z buňky bezpečně odejít klávesou Esc.

Adresace buněk

Při tvorbě vzorců nebo funkcí je nutné znát typy adresování buněk. Znalost adresace buněk usnadní a urychlí tvorbu rozsáhlých vzorců, které umožní replikovat jednoduchým kopírováním.

Relativní adresování buněk je výchozí. Znamená to, že relativně adresovaný vzorec si při kopírování načte adresy buněk přesně tím směrem, kterým je vzorec kopírován.

 

Relativně adresovaný vzorec v Excelu 2010 - poznámky ze školení

Obrázek 25 - Relativně adresovaný vzorec

Absolutně adresovaná buňka se ve vzorci při kopírování nemění. Pokud je ve vzorci alespoň jedna buňka absolutně adresovaná, mluvíme o tzv. absolutně adresovaném vzorci. Výhodou absolutně adresovaných vzorců je snadná možnost provázání na jeden vstup. Např. v rozpočtu lze snadno všechny vzorce ovlivnit hodnotou kurzovního lístku nebo jinou konstantou.

 

Absolutně adresovaný vzorec v Excelu 2010 - poznámky ze školení

Obrázek 26 - Absolutně adresovaný vzorec

Dva výše popsané způsoby adresace buněk se při běžné práci v Excelu používají nejčastěji. Při složitějších a hlavně rozsáhlejších tabulkách lze efektivně využít třetí způsob adresace buněk s názvem smíšená adresace buněk.

Smíšená adresace buněk způsobuje, že při změně buňky ve vzorci se změní pouze jedna část adresy – buď jenom řádek, nebo jenom sloupec.

 

Smíšeně adresovaný vzorec v Excelu 2010 - poznámky ze školení

Obrázek 27 - Smíšená adresace buněk

Typ adresace lze ovlivnit přidáním $ (dolaru). Tento znak je možné nejjednodušeji přidat funkční klávesou F4. Do buňky vložíte rovnítko, určíte první vstupní buňku, a pokud má být absolutně nebo smíšeně adresovaná, zmáčknete F4. Funkční klávesa F4 funguje jako přepínač, první zmáčknutí dodá dolary na obě pozice adresy buňky, druhé zmáčknutí zamyká pouze řádek, další pouze sloupec a další $ odstraní a nastaví adresu na výchozí relativní adresaci.

Kopírování vzorců

Vzorce se dají kopírovat do plochy buněk několika způsoby.

  1. Tažením za pravý dolní úchopový bod aktivní buňky;
  2. Dvojklikem na pravý dolní úchopový bod buňky (funguje pouze, pokud se vzorec „dotýká“ vstupní tabulky;
  3. Označením oblasti buněk s budoucími výpočty, zadáním výpočtu do aktivní buňky a ukončením klávesovou kombinací CTRL + Enter;
  4. Přes schránku, např. za pomoci klávesových zkratek CTRL +C a CTRL +V.

Funkce

Funkce v Excelu zastupují většinou rozsáhlejší výpočet, který je dán názvem a argumenty. Název udává, co funkce počítá (např. průměr, počet atp.), argumenty jsou vstupy, tedy jaké vstupní hodnoty se mají vypočítat. Excel 2010 nabízí 408 funkcí.

Funkce je možné vkládat přes průvodce, kterého vyvoláte buď tlačítkem Fx (na začátku řádku vzorců) nebo klávesovou kombinací SHIFT + F3.

Pokud víte, jak se funkce, kterou potřebujete použít, nazývá, lze ji mnohem rychleji zapsat do příslušné buňky. Stačí začít rovnítkem a hned pokračovat názvem. Excel 2010 nabízí tzv. našeptávač, který umožní snadnější výběr příslušné funkce.

Pokud nevyužijete ani našeptávače, lze po správném zapsání funkce (např.: =SVYHLEDAT) zmáčknout kombinaci SHIFT + F3 nebo CTRL + A. Tyto zkratky ihned zobrazí okno s argumenty funkce.

Převod vzorce nebo funkce zpět na hodnotu

Pokud se potřebujete výpočtů trvale zbavit a nahradit je pouze výsledky, můžete si pomoci několika způsoby. Nejjednodušší je oblast s výpočty označit, zkopírovat a ihned na stejné místo vložit. Excel nabídne přes tlačítko možností volbu Pouze hodnoty.

 

Převod vzorců a funkcí na hodnoty - poznámky ze školení - Excel 2010

Obrázek 28 - Převod vzorců a funkcí na hodnoty

Pro převod jedné jediné buňky se vzorcem na hodnotu stačí tuto buňku vybrat, zmáčknout F2 (vstup do buňky) a následně F9 (převod vzorce nebo funkce na hodnotu). Tímto se celý výpočet stává obyčejnou hodnotou.

Pokud byste potřebovali převést jen jeden argument (adresu buňky nebo rozsah) ve výpočtu, stačí jej ve vzorci označit a opět zmáčknout F9.

Pokud potřebujete zamezit přístupu do souboru zcela, uložte jej jako PDF.

Vyhodnocování vzorců a funkcí

Při hledání souvislostí ve složitějších výpočtech může dobře pomoci zobrazení následníků a předchůdců, tedy vstupů do výpočtů a jejich výstupů. Stačí být na vzorci, který si potřebujte ujasnit a na kartě Vzorce ve skupině příkazů Závislosti vzorců (Formulas, Formula Auditing, Trace Precedents or Trace Dependents).

 

Předchůdci a následníci v Excelu 2010

Obrázek 29 - Zobrazení vstupů a výstupů do a z vzorce

Dvojklik na modré spojovací čáry vás přesune do vstupní buňky nebo zase zpět do výsledku výpočtu. Toto se hodí, pokud vstupy nejsou na jedné viditelné obrazovce nebo jsou na více listech.

Jiný způsob sledování souvislostí ve složitém vzorci je připojení klíčových buněk do nástroje Kukátko (Watch Window).

Pro hledání chyb na listu se hodí příkaz Kontrola chyb a pro pochopení souvislostí komplexních vnořených funkcí je velmi nápomocen nástroj Vyhodnocení vzorce. (Error Checking and Evaluate Formula).

 

Nástroj Vyhodnotit vzorec

Obrázek 30 - Vyhodnocování komplexních výpočtů v Excelu

Maticové vzorce a funkce

Méně známou formou výpočtů jsou tzv. maticové vzorce, které pracují s poli dat. Rozdíl oproti běžným výpočtům je v tom, že maticový výpočet má na vstupu nikoli jednu buňku, ale vždy oblast buněk. Výstup z maticového vzorce směřuje do jedné buňky nebo do matice výsledků. Maticové vzorce a funkce je vždy nutné ukončit klávesovou zkratkou CTRL + SHIFT + Enter.

Grafy

 

Přehled grafů v Excelu 2010

Obrázek 31 - V Excelu 2010 je 73 podtypů grafů a další si můžete vytvářet dle svých potřeb

Pro tvorbu grafu potřebujete vstupní data. Pokud je nemáte, není co v grafu zobrazovat. Vstupní tabulka dat by měla být vhodně uspořádána, měla by mít záhlaví sloupců a řádků. Sloupcová data se v grafech nazývají Kategorie, řádky Hodnoty.

Skupina stejných vstupů se nazývá datová řada (tvoří např. stejně barevné sloupce). Jedna hodnota se nazývá datový bod (např. jeden sloupec).

Po správném označení vstupní tabulky lze graf vytvořit několika jednoduchými způsoby. Nejviditelnější způsob se nachází na kartě Vložit ve skupině příkazů Grafy (Insert, Charts), kde lze příslušný podtyp grafu vybrat a Excel následně vytvoří nový grafický objekt přímo na listu.

V Excelu je k dispozici několik desítek vzorů grafů. Jeden z nich lze označit jako výchozí. Výchozím grafem většinou bývá základní skupinový sloupcový graf. Výchozí typ grafu lze změnit v okně Změnit typ grafu, tlačítko Nastavit jako výchozí graf (Change Chart Type, Set as Default Chart).

 

Jak nastavit výchozí graf v Excelu 2010

Obrázek 32 - Definice výchozího grafu

Jiným způsobem tvorby grafů jsou klávesové kombinace. Po správném označení vstupní datové tabulky je možno stisknout funkční klávesu F11, která automaticky vytvoří graf na novém listu. Kombinace Levý Alt + F1 vytvoří stejný výchozí graf, ale na listu aktivním.

Pokud jste graf vytvořili na samostatném listu a potřebujete jej mít jako objekt v existujícím listu nebo naopak, změnu provedete v Nástrojích grafu, kartě Návrh, kde najdete tlačítko Přesunout graf (Chart Tools, Design, Move Chart).

 

Změna úmístění grafu v Excelu 2010

Obrázek 33 - Změna umístění grafu

Po vytvoření grafu je možno jej snadno formátovat, nejlépe přes Nástroje grafu, kartu Návrh a skupinu Styly grafů (Chart Tools, Design, Chart Styles).

 

Formát grafu za pomoci stylů - poznámky ze školení MS Excel 2010

Obrázek 34 - Formátování grafu přes styly grafu

Každý graf je tvořen skupinou objektů. Mezi objekty patří osy, legenda, popisky, hodnoty a další prvky. Pokud by vám základní formátování grafů nevyhovovalo, lze v objektu grafu vybrat jakýkoli prvek a jednoduše jej změnit. Výběr probíhá myší a poté stačí na prvku kliknout pravým tlačítkem a zvolit příkaz Formát a příslušný název prvku. Nebo můžete do objektu jednoduše provést dvojklik levou myší.

 

Formát prvků v grafu - poznámky ze školení Excel 2010

Obrázek 35 - Formát prvků grafu

Dalším způsobem formátování prvků v grafu je výběr příslušného prvku v rozbalovací nabídce v Nástrojích grafu, kartě Rozložení, skupině příkazů Aktuální výběr (Chart Tools, Layout, Current Selection).

 

Formátování výběru grafu

Obrázek 36 - Formát oblasti grafu

Praktické postupy při úpravě grafů

Kombinace několika grafů v jedné grafické sestavě

Klikněte pravou myší na datovou řadu, kterou potřebujete převést na jiný typ grafu a vyberte příkaz Změnit typ grafu řady (Change Series Chart Type).

 

Tvorba kombinovaného grafu s vedlejší osou Y v Excelu 2010 - poznámky ze školení

Obrázek 37 - Tvorba kombinovaného sloupcového a spojnicového grafu

Graf s vedlejší osou Y

Pokud potřebujete graficky zobrazovat hodnotově zásadně odlišné údaje, např. koeficienty vůči zaznamenaným údajům, můžete jednu datovou řadu proporčně zvýšit nebo snížit. Toto provedete vyvedením popisků na vedlejší osu. Klikněte pravou myší na datovou řadu, pro kterou chcete mít popisky na vedlejší ose, z místní nabídky vyberte příkaz Formát datové řady a v následném okně zvolte přepínač Vedlejší osa (Format Data Series, Plot Series on Secondary Axis).

 

Graf s vedlejší osou Y v Excelu 2010

Obrázek 38 - Tvorba grafu s vedlejší osou Y

Predikce a další analytické nástroje pro grafy

Při odhadování budoucích stavů je možno k jednotlivým datovým řadám grafu přidat spojnici trendu. Stačí vybrat datovou řadu a v Nástrojích grafu, na kartě Rozložení ve skupině příkazů Analýza vybrat ikonu Spojnice trendu (Chart Tools, Layout, Analysis, Trendline).

 

Analytické nástroje grafů

Obrázek 39 - Spojnice trendu

Při vkládání spojnice trendu je často vhodné v grafu zobrazit rovnici regrese, která trend definuje. Pro její zobrazení klikněte pravou myší na zobrazený trend a vyberte příkaz Formát spojnice trendu (Format Trendline). V okně následně zatrhněte volbu Zobrazit rovnici grafu(Display Equation on chart).

 

Formát spojnice trendu v Excelu 2010 - poznámky ze školení

Obrázek 40 - Zobrazení rovnice regrese (trendu) v ploše grafu

Na stejném místě (ve skupině příkazů Analýza) se nachází příkazy pro vynášecí čáry, spojnice extrémů, sloupce vzrůstu a poklesu a chybové úsečky.

Vlastní šablony grafů

Pokud vytvoříte komplexní graf s vlastními prvky, grafikou a dalšími nastaveními, je možné sadu ucelených nastavení uložit do šablony (samostatného souboru) s koncovkou crtx, který můžete kdykoli příště použít nebo jej poslat kolegovi e-mailem pro další využití.

Pro uložení šablony grafu aktivujte příslušný graf (klikněte do něj) a v Nástrojích grafu na kartě Návrh zvolte ve skupině příkazů Typ ikonu Uložit jako šablonu (Chart Tools, Design, Save As Template).

 

Tvorba šablony grafů crtx v Excelu 2010 - poznámky ze školení

Obrázek 41 - Tvorba šablony grafů

Další praktické informace, rády, návody a postupy ke grafům vám rádi poskytneme na školení MS Excel 2010 - Grafy.

Minigrafy (Sparklines)

Minigrafy jsou nástrojem pro zobrazení aktuálního vývoje řady čísel. Jednoduchým spojnicovým, sloupcovým nebo vzestupovým/poklesovým grafem ukážete vývoj celé datové řady. Minigrafy vytváříte na kartě Vložení (Insert, Sparklines) označením příslušné datové řady a výběrem typu minigrafu. Minigrafy mají svoji vlastní kartu nástrojů (Sparkline Tools).

Minigrafy

Minigrafy jsou nativně integrovány až v Excelu 2010, ale lze je použít i ve starších verzích Excelu.

Kontingenční tabulky a kontingenční grafy

Kontingenční tabulka je jednoduchý, ale přitom velmi praktický nástroj na analýzu dat, tedy většinou velkých tabulek. Kontingenční tabulky využijete v účetnictví, finančním, skladovém plánování a controllingu obecně.

Kontingenční tabulka je nástroj Excelu, který sumarizuje data. Umí z velké vstupní tabulky zobrazit jen potřebná pole, případně je křížově provázat s jinými důležitými poli a data následně sumarizuje jedním z 11 dostupných výpočetních postupů. Kontingenční tabulka je zjednodušeně řečeno chytrou kombinací nástrojů filtr, řazení a souhrn, aniž by byla jakkoli změněna originální tabulka dat.

Pokud máte vstupní data se správným upořádáním (popisky sloupců, rozložení dat spíše v řádcích, nikoli sloupcích), stačí pro aktivaci kontingenční tabulky být ve vašich datech a na kartě Vložení vybrat první tlačítko Kontingenční tabulka (Insert, Pivot Table).

 

Vytvoření kontingenční tabulky v Excelu 2010

Obrázek 42 - Jeden ze způsobů, jak se kontingenční tabulka spouští

Nová prázdná kontingenční tabulka se vytvoří na nový list, kde s ní přes pravé podokno manipulujete. Klíčové kategorie dat (pole) vkládáte do Popisků řádků (Row Labels), číselné údaje do Hodnot (Values) a v případě rozsáhlých vstupních dat využíváte Popisky sloupců (Column Labels) a Filtr sestavy (Report Filter) nebo Průřezy (Slicers).

Kontingenční tabulka a Průřezy

Průřezy (Slicers) jsou lépe zpracovanou částí Filtru sestavy. Vytváříte je v Nástrojích kontingenční tabulky na kartě Možnosti přes příkaz Vložit Průřez (Pivot Table Tools, Options, Insert Slicer). V případě potřeby jich můžete vybrat více. Mají své vlastní nástroje (Slicer Tools). Výběr položek průřezů probíhá souvisle za pomoci přidržení klávesy SHIFT nebo nesouvisle přes CTRL.

Kontingenční tabulka - Průřezy

Pokud potřebujete upravit pohled na vaše data, přesouváte jednotlivá pole v pravém podokně.

 

Kontingenční tabulka v Excelu 2010

Obrázek 43 - Klíčová manipulace s poli kontingenční tabulky

Jednotlivá pole je možno snadno řadit a filtrovat dle běžných pravidel Excelu. Tyto operace jsou dostupné v rozbalovacím menu klíčových polí.

Pokud je v poli opravdu mnoho položek a vy znáte názvy těch důležitých, můžete je zadat do vyhledávacího políčka.

 

Kontingenční tabulka v Excelu 2010 - Řazení a filtrování dat

Obrázek 44 - Řazení a filtrování polí v kontingenčních tabulkách

Výsledkem kontingenční tabulky je zobrazení dat, kterému se říká sestava nebo report. Tento sumarizační report lze ovlivnit nastavením hodnotového pole (např. v Nástrojích kontingenční tabulky, karta Pole, skupina příkazů Aktivní pole nebo místní nabídkou na poli hodnot (Pivot Table Tools, Active Field, Field Settings)).

 

Kontingenční tabulka v Excelu 2010

Obrázek 45 - Volba sumarizačního výpočtu v číselném poli hodnot kontingenční tabulky

Kromě základních sumarizačních výpočtů je zde možno výsledky zobrazovat jako např. procentuelní poměry vůči klíčovému poli. Toto se volí ve stejném okně, jen na záložce Zobrazit hodnoty jako (Show values as).

Uspořádání dat v kontingenční tabulce lze snadno měnit v Nástrojích kontingenční tabulky na kartě Návrh ve skupině příkazů Rozložení (Pivot Table Tools, Design, Layout).

 

Kontingenční tabulka v Excelu 2010 - poznámky ze školení

Obrázek 46 - Možnosti rozložení dat v kontingenční tabulce

Měli byste vědět:

  • Položky v kontingenčních tabulkách je možno seskupovat do vlastních skupin (u datumových položek např. měsíce, kvartály, týdny)
  • Položky v polích lze myší přesouvat nahoru a dolů nebo doleva a doprava
  • Je možno vytvářet vlastní výpočty, tzv. výpočtová pole a výpočtové položky
  • Kontingenční tabulky v Excelu 2010 lze zobrazovat „postaru“, tak jak to nabízely starší verze. Přepnutí lze dosáhnout v Možnostech kontingenční tabulky. Přepnutí se děje automaticky, pokud otevíráte kontingenční tabulku v režimu kompatibility.
  • Kontingenční tabulka neovlivní zdrojová data, jedná pouze o jednosměrný tok dat
  • Při změně dat ve zdrojové tabulce nezapomeňte kontingenční tabulku aktualizovat
  • Z dat v kontingenční tabulce můžete snadno vytvořit kontingenční graf, který se chová velmi podobně jako běžný graf v Excelu s rozdílem, že nemůžete měnit zdrojová data
  • Kontingenční tabulka i kontingenční graf jsou propojeny. Změna v jednom ovlivní druhé
  • Pokud postrádáte průvodce ze starších verzí Excelu, podívejte se na postup, jak aktivovat Průvodce kontingenční tabulkou v Excelu. Hodí se, pokud potřebujete pracovat s násobnými oblastmi sloučení, tedy s tabulkami na více listech, které je nutné sloučit do jedné kontingenční tabulky.
  • Další informace získáte na našem školení Excel 2010 - kontingenční tabulky.

Databázové nástroje

Databázové nástroje Excelu 2010 se nachází na kartě Data.

 

Karta Data v Excelu 2010

Obrázek 47 - Přehled databázových nástrojů

Filtrování a řazení

Na kartě Data se nachází všechny jednoduché i rozšířené nástroje filtrování a řazení, nicméně nejjednodušší způsob pro rychlé vyfiltrování nebo seřazení nabízí místní nabídka v klíčovém poli tabulky, kterou vyvoláte pravým tlačítkem myší.

 

Filtrování a řazení dat v Excelu 2010

Obrázek 48 - Nejrychlejší způsob filtrování nebo řazení dat v tabulce

Automatický filtr je taktéž možno zapnout nebo vypnout klávesovou zkratkou CTRL + SHIFT + L.

Při práci s rozsáhlými tabulkami se může hodit Rozšířený filtr, který se skrývá na kartě Data pod tlačítkem Upřesnit (Advanced). Podobně lze u větších tabulek uplatnit rozšířené řazení, které je dostupné pod tlačítkem Seřadit (Sort).

 

Rozšířé řazení dat v Excelu 2010

Obrázek 49 - Rozšířené řazení dokáže seřadit tabulku podle kritérií až v 64 sloupcích

 

Rozšířený filtr v Excelu 2010

Obrázek 50 - Rozšířený filtr umí pracovat s logickými operátory AND a OR

Skupiny a automatické přehledy

Díky skupinám dokážete rychle zobrazovat nebo skrývat potřebné sloupce nebo řádky. Stačí označit celý sloupec (nebo více sloupců), případně celý řádek (nebo více řádků) a příkazem Seskupit (Group) vytváříte skupinu. Sousedním tlačítkem Oddělit (Ungroup) označené řádky nebo sloupce ze skupiny odstraňujete, případně rušíte celou skupinu.

Skupinu lze po označení řádku nebo sloupců vytvořit klávesovou kombinací SHIFT + Levý ALT + kurzorová šipka doprava a zkratkou SHIFT + Levý ALT + kurzorová šipka doleva skupinu rušíte.

Automatický přehled (Auto Outline) lze uplatnit jen v tabulkách, které mají pravidelné rozložení vzorců v řádcích nebo sloupcích. Tento excelový nástroj předpokládá řádky nebo sloupce se souhrnnými výpočty. Nástroj Automatický přehled dokáže tyto řádky nebo sloupce identifikovat a podle nich vytvořit automatické skupiny.

 

Automatický přehled v Excelu 2010 - poznámky ze školení

Obrázek 51 - Automatický přehled lze zrušit tlačítkem Oddělit a volbou Vymazat přehled (Clear Outline)

Souhrny

Souhrn (Subtotal) je nástroj pro sumarizaci kategorických dat v tabulkách. Pro jeho správné použití je nejdříve nezbytné tabulku seřadit podle kategorie nebo kategorií, které potřebujete sumarizovat.

Souhrny lze vnořovat do sebe, případně dle potřeb kombinovat. Pozor, pro práci se souhrnem musíte být v tabulce, kterou sumarizujete. Jestliže je z vaší tabulky vytvořena oblast (Nástroj tabulka), souhrny není možno v této podobě použít. Je ale možno z této tabulky vytvořit kontingenční tabulku a další procesy snadno analyzovat skrze nástroje kontingenční tabulky.

 

Souhrny v Excelu 2010 - poznámky ze školení

Obrázek 52 - Pro zrušení souhrnu buďte v souhrnné tabulce, aktivujte tlačítko Souhrn a potvrďte Odebrat vše

Další nástroje na kartě Data

Text do sloupců

Text do sloupců (Text to Columns) je nástroj, který dokáže nesourodý datový vstup, pokud se v něm nachází systém pravidel (oddělovačů), uspořádat a rozdělit do pravidelných sloupců, tak aby bylo možno s daty dále pracovat.

 

Průvodce importem textu

Obrázek 53 - Excel 2010 - Průvodce převodem textu do sloupců

Tento příkaz je vhodné použít, pokud se data již nacházejí na listu (jsou důsledkem importu nebo výpočtu). Pokud se data nachází v externím csv (Comma Separated Values) nebo txt souboru, tento průvodce se automaticky vyvolá po otevření souboru přes nabídku Soubor Otevřít (File, Open). V dialogovém okně Otevřít (Open) je jen nutné vybrat typ souborů „Všechny soubory“ (All files).

 

Dialogové okno pro import dat do Excelu 2010 - poznámky ze školení

Obrázek 54 - Výběr datového typu souboru

Sloučit

Nástroj Sloučit (Consolidate) je dalším sumarizačním nástrojem Excelu. Tento nástroj dokáže data z více stejných tabulek sloučit do jedné datové oblasti. Jádro tohoto nástroje je použito v průvodci kontingenční tabulkou.

Nástroj sloučit dokáže sloučit data z více listů a používá sadu 11 běžných souhrnných funkcí: součet, počet, průměr, maximum, minimum, součin, počet čísel, směrodatná odchylka z celého rozsahu dat, směrodatná odchylka ze vzorku dat, odhad rozptylu a rozptyl.

 

Nástroj Sloučit

Obrázek 55 - Slučování dat z více destinací

Odebrat stejné

Odebrat stejné (Remove Duplicates) je nástrojem Excelu 2010, který usnadňuje práci s duplicitami.

 

Nástroj Odebrat duplicity v Excelu 2010 - poznámky ze školení

Obrázek 56 - Jednoduchý způsob, jak se zbavit duplicit

Stačí zatrhnout sloupec, v kterém se mají duplicity vyřadit a nástroj Odebrat duplicity všechny druhé a další výskyty stejného záznamu odstraní. Duplicitní kritérium je možno stanovit na jeden nebo více sloupců.

Ověření dat

Ověření dat (Validation) je nástroj, který umí zkontrolovat uživatelem zadané vstupy. Lze nastavit, aby do určitých buněk byly zadávány pouze hodnoty v požadovaném rozsahu.

 

Ověření dat v Excelu 2010

Obrázek 57 - Ověření dat, nástroj umožňující limitovat vstupy do buněk

Omezující parametry lze zadat jako hodnoty, oblasti buněk, pojmenované oblasti a vzorce. Taktéž lze ovlivnit míru vynucení omezující informace. Buď bude absolutní (stop), nebo jsou k dispozici méně obligátní omezení typu varování a informace.

Pokud předpokládáte, že byly vloženy údaje mimo povelený rámec, lze je graficky zvýraznit příkazem Zakroužkovat neplatná data (Circle Invalid Data).

Pod tlačítkem Analýza hypotéz (What-If Analysis) se nachází skupina praktických nástrojů, mezi které patří:

Scénáře

Scénář (Scenario) lze považovat za rozšířenou paměť, která si pamatuje určitý počet hodnot, které se dají zobrazit nebo použít ve výpočtu pro jednu nebo několik konkrétních situací. Scénáře se můžou hodit, pokud klientovi na schůzce nebo kolegům na poradě potřebujete ukázat více variant kalkulace. Buď můžete připravit více listů s odlišnými vstupy, nebo budete vstupy zadávat při potřebě změny ručně (může vznikat chybovost), nebo si připravíte scénáře.

 

Scénáře v Excelu 2010

Obrázek 58 - Správce scénářů s 6 připravenými situacemi

Scénář si dokáže pamatovat až 32 hodnot v souvislých či nesouvislých buňkách, ale hodnoty můžou být jen číselné, textové nebo datumové. Není možné do scénáře uložit vzorec nebo funkci. Počet scénářů není omezen.

Předem připravené vstupy ve scénářích lze exportovat do souhrnu nebo kontingenční tabulky, a to i včetně variantních výsledků výpočtů. Díky scénářům můžete získat přehled jen klíčových údajů, tedy pouze všech vstupů a pouze všech výstupů (výsledků výpočtů).

Scénář je možno v Excelu 2003 přidat na samostatný panel nástrojů, v Excelu 2007 a 2010 na Panel snadný přístup (Quick Access Toolbar).

Hledání řešení

Nástroj Hledání řešení (Goal Seek) je jednodušší podobou Řešitele (Solver). Hledání řešení se pokouší na principu cyklicky opakujících se smyček (iterací) vypočítat vstupy pro výpočet. Zjednodušeně řešeno, tento nástroj umí např. odpovědět na otázku, jaká by byla pravidelná výše splátky hypotéky, jestliže si chceme půjčit např. 1 000 000 Kč. Hledání řešení je velmi praktický nástroj.

 

Hledání řešení (Goal Seek) v Excelu 2010 - poznámky ze školení

Obrázek 59 - Hledání řešení - způsob jak analyzovat výpočet směrem ke vstupům

Tabulka dat

Nástroj Tabulka (Data Table) lze použít pro tzv. citlivostní analýzu. Tento nástroj dokáže na principu křížové tabulky analyzovat jeden nebo více vzorců a zobrazit variantní výsledky k různým krokům vstupů (tzv. citlivostním krokům). Nástroj se hodí při rozsáhlých analýzách, kdy šetří čas tvorby variantních výpočtů. Citlivostní analýzu lze provádět s jednou proměnnou, při které je možno analyzovat jeden nebo více výpočtů, nebo pro dvě proměnné, při které lze ale analyzovat pouze jeden výpočet. Důležitým vstupním prvkem je substituční buňka, které do výpočtu nepřímo, ale klíčově, vstupuje. Citlivostní analýzu předvádíme mj. na postupech výpočtů ukazatelů rentability ROA, ROE, ROB a dalších.

 

Citlivostní analýza v Excelu 2010

Obrázek 60 - Popis citlivostní analýzy

Excel nabízí mnoho dalších analytických nástrojů. Tyto nástroje ale nejsou běžně přístupné, je nutné je aktivovat. Aktivaci lze provést přes nabídku Soubor, Možnosti, v levé navigaci vyberte skupinu Doplňky, kde v části Spravovat: Doplňky aplikace Excel zvolte tlačítko Přejít (File,  Options, Add-Ins, Excel Add-Ins, Go).

 

Doplňky (Add-Ins) v Excelu 2010

Obrázek 61 - Okno doplňky umožní aktivaci méně běžných nástrojů Excelu

Po zatržení a potvrzení Analytických nástrojů a Řešitele se na kartě Data zcela vpravo objeví nová skupina ikon. Najdete tam mj. Analýzu dat (Analysis) a Řešitele (Solver).

Analýza dat nabízí celkem rozsáhlý přehled specifických analytických nástrojů. Pro běžnou práci manažera nebo ekonoma stojí za zmínku např. nástroje Histogram, Klouzavý průměr a Generátor pseudonáhodných čísel.

 

Analýza dat v Excelu

Obrázek 62 - Přehled analytických nástrojů pro analýzu dat v Excelu

Řešitel

Řešitel (Solver) je analytický nástroj sloužící k řešení optimalizačních úloh. Příkladem úloh, které lze takto řešit, jsou např.:

  • kapacitní úlohy - řeší např. výrobní program - v jakém množství vyrábět více druhů výrobků, abychom dosáhli maximálního výnosu, jsme-li omezeni kapacitou strojů, pracovníků apod.
  • řezné úlohy - při řezání materiálu na kusy různých rozměrů, hledáme minimální odpad
  • distribuční úlohy - např. optimální rozdělení činností mezi stroje. Známou úlohou je dopravní problém, při kterém hledáme vhodnou trasu tak, aby její délka byla minimální
  • směšovací úlohy - hledáme optimální směs několika různých materiálů při minimálních nákladech, která by splňovala požadované vlastnosti, například aby krmná směs obsahovala potřebné živiny.

 

Řešitel v Excelu 2010

Obrázek 63 - Vstupní parametry řešitele

 

Řešitel v Excelu 2010

Obrázek 64 - Možnosti nastavení

Import dat do Excelu

Importovat data do Excelu je vcelku snadná záležitost. Importovat data lze z MS Access, internetu (webové stránky), souboru (txt, csv, a dalších formátů) nebo z jiných zdrojů (databázového úložiště SQL, XML, Microsoft Query).

V případě importu tabulky z internetu lze využít vlastnosti Internet Exploreru, kdy na stránce s tabulkou, kterou potřebujete importovat, kliknete pravou myší přesně do tabulky a zvolíte příkaz Importovat do aplikace MS Office Excel. Po zvolení příkazu a bezpečnostním potvrzení na chvíli problikne Excel a vrátí se zpět okno Internet Exploreru. Pokud se nyní přepnete do Excelu, uvidíte tam vaši internetovou tabulku, kterou můžete běžně upravovat.

 

Import dat z webu do Excelu

Obrázek 65 - Jednoduchý a rychlý export dat z webové tabulky do Excelu 2010.

Tato tabulka je však stále propojena se svým internetovým zdrojem a vy ji můžete v Exelu průběžně pravou myší a příkazem Obnovit (Refresh) synchronizovat s internetovým obsahem. Pokud by bylo potřeba propojení zrušit, stačí opět do tabulky kliknout pravou myší a zvolit příkaz Vlastnosti externích dat (Data Range Properties). Nyní zrušte zatržítko Uložit definici dotazu (Save query definition) a operaci potvrďte. Tím se z propojené tabulky stává statická excelová tabulka.

 

Vlastnosti oblasti externích dat v Excelu 2010 - poznámky ze školení

Obrázek 66 - Vlastnosti propojené tabulky

Tento způsob importu v ostatních prohlížečích (Opera, Chrome, Firefox, Safari) není dostupný, alespoň ne bez příslušných doplňků.

Pokud by import dat z prohlížeče neproběhl správně, lze se pro externí data vydat z Excelu přes příkaz Z webu, který se nachází na kartě Data (Data, Get External Data, From Web). Zobrazí se velmi zjednodušený webový prohlížeč, který umožňuje přes žluté šipky vybrat konkrétní tabulky k importu. Po tomto importu se taktéž vloží propojená tabulka, u které lze propojení zrušit stejně jako v předešlém případě.

Potíž je v tom, že se dnes prvky webových stránek netvoří z tabulek <table>, ale z blokových elementů <div>, které tento jednoduchý webový prohlížeč neumí zvýraznit.

 

Interní webový prohlížeč v Excelu 2010 pro import dat - poznámky ze školení

Obrázek 67 - Jednoduchý webový prohlížeč pro označení tabulky pro import

Import dat z textu otevírá Průvodce importem textu (Text Import Wizard), v kterém je nutno označit typ oddělovače dat nebo nastavit pevnou šířku sloupců.

Ovládací prvky formuláře

V Excelu 2010 si můžete pomoci sadou formulářových prvků (Forms), mezi které patří především:

  • Tlačítko
  • Pole se seznamem
  • Zaškrtávací políčko
  • Číselník
  • Seznam
  • Přepínač
  • Skupinový rámeček
  • Popisek
  • Posuvník

 

Ovládací prvky formuláře v Excelu 2010 - poznámky ze školení

Obrázek 68 - Přehled ovládacích prvků formuláře na kartě Vývojář

Ovládací prvky formuláře se nachází na kartě Vývojář (Developer), která není ve výchozím stavu Excelu 2010 na obrazovce. Je nutné ji přes nabídku Soubor, Možnosti, Přizpůsobit pás karet aktivovat (File, Options, Customize Ribbon).

 

Karta Vývojář v Excelu 2010 - poznámky ze školení

Obrázek 69 - Zapnutí karty Vývojář v Excelu 2010

Poté, co jsou ovládací prvky dostupné, stačí si kterýkoli z nich vybrat a nakreslit jej (tažením myší). Poté, co je ovládací prvek součástí listu, je nutné jej propojit na buňky nebo oblasti buněk. Toto lze provést přes Vlastnosti objektu, do kterých se dostanete kliknutím pravé myši na ovládací prvek a volbou příkazu Formát ovládacího prvku (Format Control).

 

Formát ovládacího prvku v Excelu 2010 - poznámky ze školení

Obrázek 70 - Formátování ovládacího prvku

Podobným způsobem je možno k ovládacímu prvku formuláře připojit makro.

Ovládací prvky formuláře takto lze využít jako jednoduché vstupní volby pro vzorce nebo grafy. Máte zvýšenou pravděpodobnost zadávání méně chybových vstupů.

 

Propojení ovládacích prvků formuláře na funkci INDEX

Obrázek 71 - Ukázka propojení ovládacího prvku (Rozbalovacího seznamu) na funkci INDEX a následné grafické znázornění

Makra

Makro je zkratkou pro makropříkaz. V Excelu se makra používají dvojím způsobem. Prvním je skupina zaznamenaných kroků, které se často opakují. Makrem je můžete jednou zaznamenat a v dalších situacích jednoduše spustit buď připraveným tlačítkem, nebo klávesovou zkratkou. Toto je nejběžnější způsob, jak zaznamenat jednoduchá makra.

Druhý způsob tvorby makra je jeho psaní v editoru jazyka Visual Basic, který je běžnou součástí každého Excelu a vyvolá se např. klávesovou zkratkou Levý ALT + F11. Alternativní způsob spuštění se nachází na kartě Vývojář, skupina příkazů Kód, tlačítko Visual Basic (Developer, Code, Visual Basic).

Jak zaznamenat makro v Excelu 2010?

Připravte se na to, že v průběhu záznamu makra nesmíte dělat chyby nebo zbytečné kroky navíc. Pokud se tak stane, budete muset makro editovat nebo jej pravděpodobně začít zaznamenávat znovu. Záznam makra lze sice v průběhu pozastavit, ale především u mnohokrokových maker je důležité si celý postup dobře promyslet, případně předem procvičit.

Záznam makra se hodí v mnoha případech, pro přehlednost uvádím tento: pravidelně každý týden musíte provést import dat ze SAPu do Excelu, kde potřebujete data pravidelně a rutinně upravovat tak, že ze 100 sloupcové tabulky musíte vždy cca 70 sloupců odstranit, doplnit nové sloupce se vzorci a souhrny, naformátovat a vytisknout. Tato činnost vám zabírá každý týden půl hodiny, je rutinní a může se u ní vyskytovat chybovost.

Excel nabízí tuto situaci pro jednou zaznamenat za pomoci záznamníku makra, přes který zaznamená opravdu všechny kroky, které se dají v příští podobné situaci pohodlně vyvolat tlačítkem nebo klávesovou zkratkou a to, co jste ještě nedávno prováděli desítky minut, za vás Excel provede během několika vteřin.

Pro začátek záznamu záznamu makra zvolte ikonu v levém dolním rohu obrazovky (případně si ji aktivujte na stavovém řádku, pokud ji nevidíte)

 

Záznamník makra v Excelu 2010

Obrázek 72 - Tlačítko pro aktivaci záznamníku makra

nebo na kartě Zobrazení ve skupině příkazů Makra

 

Záznamník makra

Obrázek 73 - Záznam makra na kartě Zobrazení

nebo na kartě Vývojář.

Po aktivaci příkazu se objeví okno, v kterém lze makro pojmenovat, přiřadit mu klávesovou zkratku, místo uložení (může to být aktivní sešit, nový sešit, případně osobní sešit maker). Při důležitějších nebo složitějších makrech je vhodné je pro přehlednost popsat v komentáři.

 

Záznamník maker v Excelu 2010 - poznámky ze školení

Obrázek 74 - Okno záznamníku makra

Po provedení výše uvedených činností a potvrzení tlačítkem OK spouštíte záznamník, který zaznamenává jakoukoli vaši činnost v Excelu, kromě pohybu myši. Zaznamenávají se tzv. činnosti nebo události, což jsou jednoduše řečeno kliky myší.

Poté, co je záznamník spuštěn, zaznamenáváte jednotlivé kroky. Záznamník je možno vypnout na stejných místech, kde jste jej zapnuli. Tím záznam ukončíte nebo pozastavíte. Při záznamu lze zapnout relativní odkazy, což znamená, že se makro bude spouštět relativně od pozice aktivní buňky. Tato vlastnost se dá v průběhu záznamu dle potřeby průběžně přepínat.

 

Záznamník maker v Excelu 2010 - použití relativních odkazů - poznámky ze školení

Obrázek 75 - Použití relativních odkazů v makru

Poté, co jste makro zaznamenali, nezapomeňte vypnout záznamník. Pokud tak neučiníte, Excel bude zbytečně zaznamenávat stovky kroků až do okamžiku vypnutí programu.

Jak spustit zaznamenané makro?

Poté, co jste makro zaznamenali a soubor s makrem uložili, stačí přes klávesovou zkratku Levý ALT + F8, nebo na kartě Zobrazení, skupina Makra, vyvolat okno Makro (View, Macros, View Macros), kde příslušné makro vyberete a spustíte.

Jak makro editovat?

Makro lze editovat nebo napsat od začátku v editoru jazyka Visual Basic. Tento editor vyvoláte např. klávesovou zkratkou Levý ALT + F11. Editor jazyka Visual Basic je jen v angličtině, není možné jej přepnout do češtiny.

Makra najdete v sešitu, do kterého jste je při vytváření ukládali, konkrétně v adresáři Modules, kde se nachází jednotlivé moduly maker.

 

Editor jazyka Visual Basic v Excelu 2010 - umístění maker - poznámky ze školení

Obrázek 76 - kód makra, které odstraňuje několik sloupců, další sloupec přídává, do něj vkládá výpočet a formátuje celou tabulku

Pokud byste chtěli psát vlastní makro nebo funkci, v editoru jazyka VBA zvolte Insert a příkaz Module, kterým vytvoříte nový prázdný modul pro váš vlastní kód.

 

Tvorba modulů a procedur v editoru VBA v Excelu 2010 - poznámky ze školení

Obrázek 77 - Vytvoření modulu a procedury

Do prázdného modulu vložte Proceduru (Insert, Procedure)

 

Tvorba procedury v editoru VBA v Excelu 2010

Obrázek 78 - Nastavení procedury

a určete, zda se bude jednat o Sub (subrutina, čili makro), nebo Function (nejčastěji vlastní funkce).

Ukázky:

 

Makro pro přepínání mřížky

Obrázek 79 - Ukázka makra, které klávesovou zkratkou přepíná mřížku

 

Ukázka vlastní funkce vytvořené ve VBA - pouznámky ze školení MS Excel 2010

Obrázek 80 - Ukázka vlastní uživatelské funkce (bez deklarace proměnných a ošetření chybových vstupů)

 

Funkce, které si v editoru jazyka VBA vytvoříte, jsou plně k dispozici v běžném prostředí Excelu. Na základě výše uvedené funkce pro výpočet BMI (Body Mass Index) lze do jakékoli excelové buňky zadat funkci se vstupy – např.: =BMI(80;180).

Seznam vlastních funkcí je taktéž k dispozici v okně pro vložení funkce (Fx).

 

Výběr vlastní VBA funkce v Excelu 2010

Obrázek 81 - Výběr vlastní funkce v Excelu 2010

 

Při práci s makry či jejich tvorbě je vhodné znát míru zabezpečení makra dle jeho umístění, které se nastavuje např. na kartě Vývojář, skupina příkazů Kód, tlačítko Zabezpečení maker (Developer, Code, Macro Security).

 

Nastavení úrovně zabezpečení maker v Excelu 2010 - poznámky ze školení

Obrázek 82 - Míra zabezpečení makra dle umístění

Taktéž je vhodné znát soubor s koncovkou xlsm, do kterého se makra v Excelu 2010 ukládají.

 

Ukládání maker do souboru xlsm

Obrázek 83 - Uložení souboru s makry

Pokud vás zajímá problematika maker, doporučujeme naše školení Excel - makra a formuláře nebo školení Excel - Visual Basic for Applications - VBA.

Tisková nastavení

Náhled i tisková nastavení provádíte příkazem Tisk (Print), který najdete v nabídce Soubor (File) nebo na panelu Rychlý přístup (Quick Access Toolbar).

Při tisku menších tabulek pravděpodobně výraznější problémy nezaznamenáte. Většinou je dostatečné se dobře zorientovat v základním okně tisku, které vyvoláte i klávesovou kombinací CTRL + P.

 

Tisk

Obrázek 84 - Základní nastavení tisku

V tomto dialogovém okně stojí za zmínku možnost tisknout pouze výběr, tedy pokud odznačíte tabulku o 30 buňkách a zde vyberete Tisknout Výběr (Selection), vytiskne se jen to, co jste označili. Před samotným vytištěním si lze tuto nebo jakoukoli jinou volbu ověřit v Náhledu (Preview).

V případě potřeby tisku větších sestav je možno zvolit Vytisknout Celý sešit (Entire workbook) a vytiskne se vám najednou třeba všech 40 listů.

Pokud máte individuální představu o podobě vytištěné stránky, např. používáte pevnou Oblast tisku (Print Area), je doporučeno přizpůsobit jednotlivé možnosti na kartě Rozložení stránky (Page Layout).

 

Rozložení stránky v Excelu 2010 - poznámky ze školení

Obrázek 85 - Rozložení stránky

Pro detailní úpravu stránky, záhlaví a zápatí nebo listů vstupte do dialogového okna Vzhled stránky (Page Layout).

 

Vzhled stránky v Excelu 2010

Obrázek 86 - Vzhled stránky: zde můžete mj. nastavit opakování tisku řádků a sloupců, tisk komentářů, chyb a další tisková nastavení tisku v Excelu 2010

Pro úpravu před tiskem se může hodit zcela nové praktické zobrazení Rozložení stránky (Page Layout), které je podobné náhledu, oproti kterému umožňuje změny v buňkách, okrajích a přístup do záhlaví a zápatí.

 

Rozložení stránky v Excelu 2010

Obrázek 87 - Změna zobrazení na Normální, Rozložení stránky a Zobrazit konce stránek

Pokud tisknete rozsáhlé tabulky, je důležité se dobře zorientovat v posledním zobrazení s názvem Zobrazit konce stránek (Page Break Preview). Toto zobrazení umožňuje tažení vodorovných a svislých modře přerušovaných čar, které určují hranice tisku.

Změnu zobrazení a další příkazy pro práci se zobrazením můžete najít na kartě Zobrazení (View).

 

 


Postrádáte podrobný postup řešení, či si nevíte rady s důležitým úkolem? Přijďte k nám na školení nebo si objednejte konzultaci, rádi Vám vyjdeme vstříc.


 

Přidat komentář


Bezpečnostní kód
Obnovit

Vyhledávání

Anketa

Jaký Office používáte nejčastěji?




website hosting main area bottom

Založeno na Joomla!. Designed by: Free Joomla 1.5 Theme, .org.uk tld hosting. Valid XHTML and CSS.