Návod: Jak na Microsoft Excel

Popis nejčastěji používaných funkcí a úprav formátování v tabulkovém procesoru Excel. (Postupy jsou pro verzi 2010, ale bez problémů lze většinu z nich použít i ve verzi 2007 a 2013).

Třetí díl minisérie, zaměřené na popis ovládání programů sady Microsoft Office. Další díly naleznete zde:

Stručná historie tabulkových procesorů

Microsoft Excel je v současnosti aplikací, která definuje kategorii aplikací, označovaných jako tabulkové procesory. Jedná se v zásadě o ten typ programů, které se snaží co nejvíce zjednodušit a automatizovat zpracování tabulkově zadávaných dat. Prvním programem tohoto typu byl VisiCalc, vytvořený roku 1979. Jednalo se o první řešení problému, kdy jste při změně jedné buňky nemuseli přepisovat celý list sešitu ručně, ale vše se v aplikaci přepočítalo samo. V 80. letech 20. století byl nejpoužívanějších programem na tyto operace Lotus 1-2-3 (značku Lotus později koupila IBM), který ale nezvládl přechod z MS Dos na Windows a tak se v 90. letech na přední pozici vedral Microsoft Excel ze sady Office.

U verzí Excelu 97 až 2003 bylo používáno poměrně podobné rozhraní, kde každá nová funkce byla přidávána pomocí nového tlačítka a bylo potřeba poměrně přesně si pamatovat, kam kliknout. Dalším charakteristickým znakem těchto verzí pak byl formát používaného souboru – xls. Ve verzi 2007 došlo k radikální změně rozhraní – menu se transformovalo v typický pás karet, tzv. ribbon – a také se změnil výchozí typ souborů na xlsx. Pokud ale i dnes chcete mít stoprocentní jistotu, že se soubor vytvořený v Excelu půjde otevřít kdekoliv, je lepší ukládat ve formátu xls.

Sešit a listy

Ve Wordu máme dokumenty a stránky, v Powerpointu prezentace a slidy, jak je to v Excelu? Velmi podobně – výchozí pracovní plochou je list – což v zásadě není nic jiného, než pracovní plocha, kde můžete manipulovat s buňkami. Těchto listů můžete mít kolik chcete, stačí si je přidat na spodní hraně pracovní plochy kliknutím na tlačítko k tomu určené. Více listů pak tvoří – sešit.

Buňky

Základní jednotkou, oním okýnkem, ze kterých je tvořena hlavní pracovní plocha, je buňka. Když do buňky začneme psát bez nějakých jiných manipulací text, je

formátování buněk

interpretován jako textový řetězec. Jiná je však situace tehdy, když jako první znak uvedeme =. Tím je dán Excelu signál, že má vše za rovnítkem chápat jako vzorec a snažit se ho vypočítat.

Příklad:
  • Zadám do buňky 3+3 >> obsahem buňky je textový řetězec 3+3
  • Zadám do buňky =3+3 >> obsahem buňky je 6
Další věcí, která neovlivňuje samotnou hodnotu v buňce, je možnost nastavení formátování buňky. Nastavíte ho tak, že kliknete pravým tlačítkem myši na buňku a vyberete, zda se má zobrazit např. v účetním formátu (tj. xx,xx Kč), jako text, číslo nebo třeba zlomek. Buňky lze dále formátovat podmíněně, např. pokud chcete vyznačit jen tu buňky, které jsou větší než 10, označte oblast, klikněte na tlačítko Podmíněné formátování a zadejte pravidlo “větší než.”
Ve vzorcích pak můžeme využívat jak čísla, tak hodnoty jiných buněk. Při odkazování na buňky můžeme využít několik způsobů:
  • Relativní adresování– je výchozím typem adresování v Excelu. Hodí se tehdy, když využíváme funkci automatického
    čtvereček pro automatické doplňování

    doplňování vzorců. Funkci aktivujete tak, že kliknete na buňku, kde máte nějaký vzorec, podržíte myší malý čtvereček v pravém dolním rohu této buňky a táhnete horizontálně nebo vertikálně – prostě tím směrem, kde chcete automaticky doplnit hodnoty podle vzoru.
    Relativní adresování vypadá např. takto: A1

  • Absolutní adresování – hodí se tehdy, když některé hodnoty v rámci automatického doplňování chcete ponechat. Můžete tak mít adresu řádkově absolutní (A$1), sloupcově absolutní ($A1) a nebo absolutní zcela ($A$1).
  • Odkazy na jiné listy – při odkazování se nemusíte omezovat pouze na aktuální list. Při odkazu na buňku v jiném listu používejte formát: Jménolistu!buňka – např.: List2!A1
  • Odkazy na jiné sešity – v tomto případě je potřeba udat ještě adresu souboru na disku. Pozor, pokud přenášíte data mezi více počítači, nemusí pak být propojení funkční – např.: C:\Dokumenty\Excel\[priklad.xls]List1!A1

 Operátory

  • + plus, – minus, = rovná se – fungují stejně jako všude v matematice
  • / slouží k dělení, * k násobení (a doplňování)
  • znak % lze využít v výpočtu procent
  • stříška ^ (pravý Alt+3) slouží k mocnění (2^3)
  • znak < se používá jako menší než, > jako větší než
  • <= menší nebo rovno, >= větší nebo rovno
  • <> znamená nerovná se
  • znak ampersand & slouží ke spojení řetězců („auto”&„mobil” = automobil)
  • : odkaz na všechny buňky mezi dvěma odkazy (B5:B15)
  • ; sjednocení = více odkazů co jednoho (SUMA(B5:B15;D5:D15))
  • (mezera) =  průnik – odkaz na buňky společné dvěma odkazům (B7:D7 C6:C8)

Základní funkce

Aby se všechno nemuselo řešit pouze pomocí operátorů, existuje v Excelu navíc řada přednastavených funkcí. Každá funkce má nějak definováno to, jak se do ní mají zapsat hodnoty a pak podle toho nám vrátí nějakou výslednou hodnotu.

  • SUMA
    Součet všech vybraných hodnot (buněk)
    Formát: SUMA(číslo1;číslo2;…)
    Příklady
    SUMA(3;2) = 5
    SUMA(D1:F1) = D1 +E1 + F1
    SUMA(D1:F1;2) = D1 + E1 + F1 + 2
  • PRŮMĚR
    Výpočet průměrné hodnoty člena výběru
    Formát: PRŮMĚR(číslo1;číslo2;…)
    Příklady
    PRŮMĚR(3;8;25;2;56) = 18,8
    D1 = 6 E1 = 7 F1 = 5
    PRŮMĚR(D1:F1) = 6
    PRŮMĚR(D1:F1;0) = 4,5
  • POČET
    Vypíše počet buněk s numerickou hodnotou ve výběru
    Formát: POČET(číslo1;číslo2;…)
    Příklady
    POČET(slovo;1;3) = 2
    POČET(78;1;3) = 3
    POČET(A1:D2) = …
  • MIN
    Zobrazení nejmenší hodnoty z výběru
    Formát: MIN(číslo1;číslo2;…)
    Příklady
    MIN(3;8;25;2;56) = 2
    D1 = 6 E1 = 7 F1 = 5
    MIN(D1:F1) = 5
    MIN(D1:F1;0) = 0
  • MAX
    Zobrazení největší hodnoty z výběru
    Formát: MAX(číslo1;číslo2;…)
    Příklady
    MAX(3;8;25;2;56) = 56
    D1 = 6 E1 = 7 F1 = 5
    MAX(D1:F1) = 7
    MAX(D1:F1;0) = 7
  • ZAOKROUHLIT
    Zaokrouhlení na daný počet desetinných míst
    Formát: ZAOKROUHLIT(číslo;počet míst)
    Příklad
    ZAOKROUHLIT(4,513;2) = 4,51
  • KDYŽ
    Vypíše hodnotu při splnění či nesplnění dané podmínky
    Formát: KDYŽ(podmínka;co vypsat při splnění;co vypsat při nesplnění)
    Příklady
    KDYŽ(2<3;”2 je méně než 3″;”2 není méně, než 3″)
    KDYŽ(A1=C3;”jsou stejné”; “nejsou stejné”)
    KDYŽ(A1<58;1;0)
  • COUNTIF
    Vypíše počet hodnot ve výběru splňující danou podmínku
    Formát: COUNTIF(oblast;kritérium)
    Příklady
    A1 =  5 B1 = 8 C1 = 9 D1 = 45 E1 = slovo
    COUNTIF(A1:E1;8) = 1
    COUNTIF(A1:E1;”slovo”) = 1
    COUNTIF(A1:E1;”>5″) = 3
    COUNTIF(A1:E1;”*lov*”) = 1
  • Datum a čas
    DNES() – aktuální datum
    NYNÍ() – aktuální datum a čas
    ROK() – vypíše rok z data

Formátování tabulky

Tabulku vytvoříte z nějaké oblasti dat na listu následujícím postupem:

  1. Označte oblast – například tažením levého tlačítka myši.
  2. Pokud první řádek vybrané oblasti obsahuje popisná data, zaškrtněte volbu: Tabulka obsahuje záhlaví
  3. Z vybrané oblasti se vytvoří tabulka, v níž pak můžete formátovat data, případně řadit nebo filtrovat.

Jak na filtry a řazení

Filtrování a řazení dat můžete uplatnit na oblast, která je chápána jako jedna tabulka – filtry umožní vypsat jen ty řádky, které splňují určitou danou podmínku, řazení pak seřadí hodnoty v řádcích podle vybraného sloupce (např. od nejmenší hodnoty k největší).

Filtry použijeme takto:

  1. Vyberte oblast a jděte do karty Data > Filtr a nebo klikněte na šipku v záhlaví tabulky
  2. Vyberte položku Filtry čísel
  3. Nadefinujete dané pravidlo (např. : zobrazit řádky větší než 26)
  4. Vypíší se pouze řádky splňující dané pravidlo

Řazení se pak provádí velmi obdobně:

  1. Klikněte na šipku v záhlaví tabulky nebo označte oblast a v menu zvolte Seřadit a filtrovat
  2. Vyberte podle čeho se mají dané hodnoty seřadit

Grafy a minigrafy

V Excelu máte dvě možnosti, jak graficky reprezentovat data z tabulek. První možností jsou minigrafy. Jedná se jsou grafy o velikosti jedné buňky, reprezentující vývoj hodnot v rámci řádku. Např.: máte řadu hodnot A1 až E1, kde jsou hodnoty 1,2,3,4,5. Označíte tuto řadu, na kartě Vložit v podoblasti Minigrafy vyberete typ Spojnicový,  a udáte buňku, kam se má minigraf umístit.

Další možností jsou pak klasické grafy. Těch je na výběr poměrně široká paleta typů a vzhledů, například: sloupcové (hodí se při srovnávání vývoje dvou a více číselných řad), spojnicové (pro sledování trendů), výsečové (pro srovnání zastoupení skupin ve vybraném vzorku), pruhové, plošné (např. pro srovnání vývoje zisk vs. obrat), bodové (nejčastěji používané), burzovní (vývoj akcií, cen komodit nebo měn, potřebují ale mít data zadána ve správném formátu), povrchové (rozšíření plošných grafů), prstencové (obdoba výsečových), bublinové (používané např. v psychologii a sociologii), paprskové (používané např. pro grafickou reprezentaci multikriteriálního výběru).

Graf vytvoříte takto:

  1. Označte zdrojová data
  2. Na kartě Vložení vyberte vhodný typ grafu
  3. Jeho vzhled a zdrojová data pak můžete upravovat na kartě Nástroje grafu

Jak na CSV

Pokud vám nějaká aplikace nebo webová služba vygeneruje data ve formátu CSV, je Excel právě tím programem, který vám s tímto záhadným formátem souboru pomůže manipulovat. CSV v zásadě není nic jiného, než uložení alfanumerických dat v textové podobě mezi oddělovači. Oddělovačem může být například čárka, středník, nebo tabulátor. Poradíte si s ním v Excelu tak, že otevřete CSV souboru nebo zkopírujete data, pak zvolíte na kartě Data > Text do sloupců > Zadat oddělovač a zadáte znak, kterým jsou jednotlivé hodnoty v daném CSV souboru od sebe odděleny.

Komentáře k článku. Přidejte i váš!

  1. Dobrý den,
    potřebuji poradit, jak docílím u tohoto vzorce, že výsledek nebude nikdy větší než 1000? Jedná se o procentuální hodnocení, které má být pouze degresivní – nikoliv progresivní.
    =(G36/F36)*1000 ( kdy 1000, je výsledek % váhy)

    Děkuji Petr Vašek

  2. zdravim,
    muzete mi nekdo prosim poradit jak podminit bunku barevne pri zmene datumu. Potreboval bych, aby se mi zvyraznila bunka automicky tri mesice pred datem ktery je v bunce zapsany. priklad v bunce B2 je datum 12.5.2017 a potreboval bych, az kdyz bude den 12.2.2017 aby se bunka zvyraznila cervene.
    predem dekuji za pomoc

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *