Grafy v Excelu

V tomto článku se budu zabývat tvorbou grafů. Začnu jednoduchým grafem, postupně budu článek doplňovat složitějšími postupy.
Jak tedy vytvořit jednoduchý graf?
Začnu tím, že si připravím tabulku, ze které je možné graf vyrobit. Moje vzorová tabulka, ve které sleduji počet zákazníků prodejny, je tady:

Teoreticky platí, že v jednom "rozměru" (v řádcích nebo sloupcích) mám jednotlivé budoucí sloupečky tabulky (zde jednotlivé dny) a v jednom mám veličinu, ve kterém sleduji - zde jednotlivé pobočky (zatím mám pouze pobočku Praha). A na průsečících mám příslušné hodnoty.

Následující dialog mohu nechat jak je a jen ho potvrdit "OK".

Prakticky - jednotlivé hodnoty sepíši pod sebe a doplním hodnoty. V tabulce bych vždy měl mít záhlaví sloupců i řádků. Zde jsou záhlavími řádků názvy dní a záhlavími sloupce "Zákazníci Praha".

Tabulku mám, mohu tedy mohu vytvářet graf.

Označím tabulku a kliknu na "Vložení/Insert" a "Sloupcový/Columns".

Dále vyberu přesný typ grafu, v mém případě hned ten první. 

A tady je výsledek:

Jak do grafu přidám výsledky další pobočky?
Vytvořím další sloupeček - Zákazníci v Plzni. Pak označím tabulku i s novým sloupečkem a postupuji obdobně. Výsledek vypadá takto:














A něco na závěr - graf, znázorňující pohled na pohoří přes tenisovou raketu :)

Ukončení stránky ve Wordu

Příklad

Píšu stránku ve Wordu. Nejsem ještě na konci, ale chci, aby následující text již byl na další stránce.

Návod

Řešením je klávesová zkratka Ctrl - Enter (držím Ctrl a současně zmáčknu Enter).
Tou vložíte tzv. pevný konec stránky - to znamená, že to, co je za tímto koncem, je VŽDY na nové stránce.
Cestou do pekla je používat k přechodu na novou stránku větší množství prázdných řádek (Enterů) pod sebou. To se vám při jakékoliv úpravě dokumentu před místem, kde toto použijete, vymstí - dokument se rozhodí.

Napište si o návod

Chci zde uveřejňovat návody, které čtenáře zajímají. Proto uvítám vaše náměty na články - co vám nejde, co vás zajímá, co potřebujete vyjasnit?
Budu rád, když něco z toho napíšete do diskuse.

Skalární součin

Příklad

V následující tabulce je seznam položek. U každé z nich je cena za kus a počet kusů. Potřebuji do buňky C15 dostat celkovou cenu za všechny položky.

Návod

Mohli bychom do třetího sloupečku vynásobit vždy cenu položky a počet kusů a dole pak výsledky spočítat. My ale využijeme funkci "součin.skalární", v anglické verzi. "sumproduct".
Do buňky C15 tedy vpíšeme funkci v této syntaxi "=součin.skalární(B2:B13;C2:C14)", v anglické verzi "=sumproduct(B2:B13;C2:C14)".
Tedy nejprve název funkce a dále středníkem oddělené dva sloupečky - skalární součin tyto sloupečky vzájemně pronásobí a výsledek sečte.
Výsledek vypadá takto:

Poznámka

U téhle funkce je trochu problém s názvem. 

• Zaprvé z anglického názvu SUMPRODUCT není moc dobře odvoditelný SOUČIN.SKALÁRNÍ. 
• A zadruhé je třeba dát pozor na pořadí dvou slov - já například docela často píšu chybně SKALÁRNÍ.SOUČIN místo SOUČIN.SKALÁRNÍ.

Skalární součin

Příklad

V následující tabulce je seznam položek. U každé z nich je cena za kus a počet kusů. Potřebuji do buňky C15 dostat celkovou cenu za všechny položky.

Návod

Mohli bychom do třetího sloupečku vynásobit vždy cenu položky a počet kusů a dole pak výsledky spočítat. My ale využijeme funkci "součin.skalární", v anglické verzi. "sumproduct".
Do buňky C15 tedy vpíšeme funkci v této syntaxi "=součin.skalární(B2:B13;C2:C14)", v anglické verzi "=sumproduct(B2:B13;C2:C14)".
Tedy nejprve název funkce a dále středníkem oddělené dva sloupečky - skalární součin tyto sloupečky vzájemně pronásobí a výsledek sečte.
Výsledek vypadá takto:

Poznámka

U téhle funkce je trochu problém s názvem. 

• Zaprvé z anglického názvu SUMPRODUCT není moc dobře odvoditelný SOUČIN.SKALÁRNÍ. 
• A zadruhé je třeba dát pozor na pořadí dvou slov - já například docela často píšu chybně SKALÁRNÍ.SOUČIN místo SOUČIN.SKALÁRNÍ.

Podmíněné formátování - základy

Příklad

V této tabulce potřebuji zvýraznit ceny, které jsou vyšší než 100 000 Kč. Např. tak, že tyto částky budou červeně.

Návod

Označím oblast, ve které se mají vybrat zvýrazněné hodnoty. V našem případě to bude oblast C1:C16.

V kartě "Home" kliknu na "Conditional Formating" a dále na "Highlight Cells Rules" a Greater Than". V dialogovém okně vyplním nejdříve požadovaný limit (my jsme si dali 100 000 Kč) a dále jak se mají buňky, které toto splňují, vybarvit.

Kliknu na OK a je hotovo.
Toto je jen velmi malá ukázka toho, co Excel v oblasti podmíněného formátování umí - a především verze 2007 toho umí opravdu hodně.

Sloučení a rozdělení buněk

Příklad

Potřebuji sloučit dvě buňky nebo více buněk. V našem případě půjde o buňky A1 a B1.

Návod

Označím buňky, které chci spojit.
Kliknu nahoře na záložku karty "Home" (pokud na ní už nejsem) a dále kliknu na Merge a Center.
A to je vše.

Navíc si mohu pamatovat že:

• Kromě "Merge & Center" mohu vybrat ještě "Merge Accross" pro spojení sloupečků (řádky zůstanou zachovány) nebo "Merge Cells" - obsah buněk se nevycentruje.
• Mohu také vybrat "Unmerge Cells" - to samozřejmě buňky zase rozdělí.
• Nemohu spojovat oblasti buňky, které netvoří obdélník.
• Nemohu rozdělit buňky, které předtím nebyly spojené (narozdíl třeba od Wordu).
• Pokud spojuji více buněk, ve kterých ne nějaký obsah, přenese se do nové buňky obsah té, která byla vlevo nahoře. Obsah ostatních je ztracen.
• Nová, spojená buňka se bude jmenovat tak, jak se jmenovala původní levá horní buňka.

Subtotal

Příklad

V následující tabulce potřebuji posčítat mzdové náklady dle oddělení. Bylo by možné provést to pomocí kontingenční tabulky, v tomto případě ale chceme využít funkcionalitu "Subtotal".

Návod

Protože budeme hledat součty mzdových nákladů za jednotlivá oddělení, začneme tím, že dle oddělení seřadíme řádky.
Po seřazení vypadá tabulka takto:

Nyní umístíme klikneme myší do jakékoliv buňky tabulky a klikneme na "Data" a "Subtotal" (v české verzi se toto tlačítko jmenuje "Souhrn").

V následujícím okně nemusíme nic měnit.

• V prvním řádku sdělujeme, že chceme sčítat po odděleních.
• V druhém řádku sdělujeme, že chceme opravdu sčítat - tedy použít funkci "Sum".
• A zaškrtnutím "Mzdové náklady" říkáme, že chceme sčítat právě tento sloupeček.
• Pokud bych chtěl vložit dvě úrovně souhrnu, vložím je postupně. Při vkládání druhého ale logicky musím zrušit fajfku u "Replace current subtotals" - protože ten druhý chci doplnit a ne jím nahradit ten první.

Výsledek je zde:

Subtotal

Příklad

V následující tabulce potřebuji posčítat mzdové náklady dle oddělení. Bylo by možné provést to pomocí kontingenční tabulky, v tomto případě ale chceme využít funkcionalitu "Subtotal".

Návod

Protože budeme hledat součty mzdových nákladů za jednotlivá oddělení, začneme tím, že dle oddělení seřadíme řádky.
Po seřazení vypadá tabulka takto:

Nyní umístíme klikneme myší do jakékoliv buňky tabulky a klikneme na "Data" a "Subtotal" (v české verzi se toto tlačítko jmenuje "Souhrn").

V následujícím okně nemusíme nic měnit.

V prvním řádku sdělujeme, že chceme sčítat po odděleních.

V druhém řádku sdělujeme, že chceme opravdu sčítat - tedy použít funkci "Sum".

A zaškrtnutím "Mzdové náklady" říkáme, že chceme sčítat právě tento sloupeček.

Výsledek je zde:

Funkce MAX a MIN

Příklad

Funkce MAX a MIN jsou jedny z nejčastěji používaných v Excelu. Jak s nimi správně zacházet? Nebojte, je to opravdu jednoduché... V následující tabulce mám seznam lidí a jejich věk. Do buněk B7 a B8 potřebuji doplnit hodnoty nejvyššího a nejnižšího věku.
 

Návod

V buňce B7 použiji funkci MAX. Její zápis vypadá takto:
=MAX(A2:A5)
Jednoduše napíšu "=", dále název funkce - v tomto případě "MAX" a do závorky nadefinuji oblast, ze které se má nejvyšší hodnota vybrat.



Obdobně pracuji i s funkcí MIN - u ní vypadá zápis takto:
=MIN(A2:A5)

A co kdybych chtěl najít jméno člověka, který je nejstarší nebo nejmladší? Pak použiji funkci VLOOKUP (více o ní zde).
V tomto případě napíšu do buňky B10 funkci tímto zápisem: =VLOOKUP(B7;A2:B5;2), resp. do buňky B11 zápisem =VLOOKUP(B8;A2:B5;2).
Zápis vypadá takto:

Výsledek vypadá takto:

Poznámka: Pokud potřebuji najít ne největší, ale např. třetí nebo desátou největší (resp. nejmenší) hodnotu, použiji funkce LARGE a SMALL.

Funkce MAX a MIN

Příklad

Funkce MAX a MIN jsou jedny z nejčastěji používaných v Excelu. Jak s nimi správně zacházet? Nebojte, je to opravdu jednoduché...
V následující tabulce mám seznam lidí a jejich věk. Do buněk B7 a B8 potřebuji doplnit hodnoty nejvyššího a nejnižšího věku.

Návod
V buňce B7 použiji funkci MAX. Její zápis vypadá takto:
=MAX(A2:A5)
Jednoduše napíšu "=", dále název funkce - v tomto případě "MAX" a do závorky nadefinuji oblast, ze které se má nejvyšší hodnota vybrat.

Obdobně pracuji i s funkcí MIN - u ní vypadá zápis takto:
=MIN(A2:A5)

A co kdybych chtěl najít jméno člověka, který je nejstarší nebo nejmladší? Pak použiji funkci VLOOKUP (více o ní zde).
V tomto případě napíšu do buňky B10 funkci tímto zápisem: =VLOOKUP(B7;A2:B5;2), resp. do buňky B11 zápisem =VLOOKUP(B8;A2:B5;2).
Zápis vypadá takto:

Výsledek vypadá takto:

Jak se jmenuje buňka?

Příklad

Někdy by se mi při práci s Excelem hodilo pojmenovat určitou skupinu buněk (oblast). Pak bych při výpočtech odkazoval na tuto skupinu a nemusel bych vždy znovu vypisovat všechny buňky, které do ní patří. Např. u této tabulky se jedná o skupinu buněk A2 až A6 (v praxi se tato funkcionalita používá spíše u složitějších oblastí).

Pojmenování buňky mi umožní např. při sčítání buněk A2 až A6 napsat vzorec "=SUM(cisilka)" místo složitějšího "=SUM(A2:A6)". Jistě jste správně odvodili, že "cisilka" je název oblasti.

Návod

Označím oblast, o kterou jde, v mém případě A2 až A6.
Kliknu na "Formulas" a dále na ""Define Name" a ještě jednou na "Define Name".

Kliknu na OK.
Nyní tedy kdekoliv budu pracovat s oblastí A2 až A6, mohu použít výraz "cisilka".
Např. "=AVERAGE(cisilka)" nebo "=MAX(cisilka)" atd.
Také si mohu všimnout, že při označení příslušné oblasti se mi v levé horní části obrazovky zobrazí její jméno.

Jak se jmenuje buňka?

Příklad

Někdy by se mi při práci s Excelem hodilo pojmenovat určitou skupinu buněk (oblast). Pak bych při výpočtech odkazoval na tuto skupinu a nemusel bych vždy znovu vypisovat všechny buňky, které do ní patří. Např. u této tabulky se jedná o skupinu buněk A2 až A6 (v praxi se tato funkcionalita používá spíše u složitějších oblastí).

Pojmenování buňky mi umožní např. při sčítání buněk A2 až A6 napsat vzorec "=SUM(cisilka)" místo složitějšího "=SUM(A2:A6)". Jistě jste správně odvodili, že "cisilka" je název oblasti.

Návod

Označím oblast, o kterou jde, v mém případě A2 až A6.
Kliknu na "Formulas" a dále na ""Define Name" a ještě jednou na "Define Name".

Kliknu na OK.
Nyní tedy kdekoliv budu pracovat s oblastí A2 až A6, mohu použít výraz "cisilka".
Např. "=AVERAGE(cisilka)" nebo "=MAX(cisilka)" atd.
Také si mohu všimnout, že při označení příslušné oblasti se mi v levé horní části obrazovky zobrazí její jméno.

Karta "Vývojář" v Excelu 2007

Příklad

Potřebuji pracovat s makry, ovládacími prvky, tlačítky atd. Ale nevím, kde jsou.

Návod

Abych mohl v Excelu 2007 pracovat s těmito pokročilejšími funkcemi, musím zobrazit kartu "Vývojář/Developer".
Kliknu na "Tlačítko Office" úplně vlevo nahoře a vyberu Excel Options.



Zaškrtnu "Zobrazit na pásu kartu Vývojář/Show Developer card in the Ribbon".

A ještě to samé na videu. Doporučuji zvětšit na větší velikost a zvětšit rozlišení.

Kontingenční tabulky - návod jak na ně jednoduše

Tento návod platí pro již zastaralý Excel 2007. Pokud používáte novější verze, Excel 2010 nebo Excel 2013, klikněte zde.

---

---

---

Příklad

Mám neuspořádaná data a chci z nich získat důležité informace. V tomto případě se chci (s pomocí kontingenční tabulky) dozvědět, kolik je v seznamu (nabídka autobazaru) aut značky Ford a kolik dohromady stojí.

Návod

Budeme pracovat s touto tabulkou:

Tabulka ke stažení

Začnu tak, že kliknu kamkoliv do tabulky - nemusím nic označovat. Dále kliknu nahoře na "Insert" (Vložit) a "Pivot Table" (Kontingenční tabulka).

Následující dialog mohu nechat jak je a jen ho potvrdit "OK". Pouze pokud bych chtěl použít jiná data, než mi vybral Excel, vyberu je.

O možnosti použít externí data (Use an external data source) více zde
Tím vznikne nový list s kontingenční tabulkou. Nemusím se tedy bát, že původní tabulka zmizela - mohu se k ní vždy vrátit na původní list.

Všimněte si pravého sloupečku s nabídkou - nahoře jsou v řádcích vypsané názvy sloupečků. Tím, jak je budu přesouvat do tabulky, budu upravovat kontingenční tabulku.

Mým úkolem bylo zjistit, kolik je v seznamu Fordů a kolik dohromady stojí. Udělám to tak, že v tabulce nechám vypsat součty cen za všechny značky - tedy i za Ford.

"Ford" je jedna ze značek aut v seznamu. Proto přetáhnu "Značka" z horního obdélníku vpravo do obdélníku "Drop Row Fields Here" (Řádková pole) v tabulce.

Tím se Vám v obdélníku, kam jste přetahovali, vypíší všechny značky aut v seznamu. 

Teď ještě zjistit, kolik tyto značky dohromady stojí. Přetáhnu "Cena" do "Drop Data Items Here" (Pole hodnot). 

Teď již u každé značky vidím, kolik dohromady stojí. 

Teď mě ještě zajímá, kolik aut té které značky v seznamu je.

Dvojkliknu tedy na "Sum of Cena" a v nabídce změním "Sum" (Součet) na "Count" (Počet). (Pokud bych chtěl součet i počet, přitáhnu do pole hodnot Cenu dvakrát - a jednou změním součet na počet.

A to je všechno.

Pár tipů navíc:

• Když "zmizí" okno pro tvorbu kontingenční tabulky vpravo, stačí kliknout do tabulky - a zase se objeví.
• Ve verzích Excelu od 2007 je možné místo do samotné tabulky přetahovat záhlaví sloupečků do čtyř polí dole v pravém pruhu. Pole odpovídají polím tabulky a je jedno, kam záhlaví přetáhnete - jestli přímo do tabulky nebo do "chlívečků" vpravo.
• Z tabulky je možno snadno kontingenční udělat graf - pouhým kliknutím na ikonku grafu a vybráním typu grafu.
• Pokud pracujete s kontingenční tabulkou, je dobré znát funkci ZÍSKATKONTDATA / GETPIVOTDATA

Kam dál?

Pokud máte raději videonávody, tak tady jeden je. Doporučuji zvětšit.

Máte nějaký dotaz? Napište ho do diskuse.