Home › Základy informatiky › 04 – Základy práce v Excelu

04 – Základy práce v Excelu

Soubory ke cvičení:

EXCEL_UKOLY

ZIS_EXCEL_DATA

ZIS_EXCEL_PRIKLADY

1. Adresování buněk v MS Excel

Při tvorbě tabulek se vzorci je běžné, že potřebujete jeden vzorec vytvořit pro celý sloupec, resp. Řádek hodnot. K tomu použijete kopírování buňky se vzorcem do sloupce (řádku). Pro hromadné zadávání vzorců je důležité správně rozlišovat odkaz absolutní, relativní a smíšený. Kopírujeme-li tažením za vyplňovací úchyt buňku se vzorcem, který se odkazuje například na buňku A1, mohou podle typu odkazu nastat tyto situace:

  • Je-li odkaz zapsán jako A1, mluvíme o relativním odkazu. Při kopírování se v odkazu systematicky mění označení sloupců a čísla řádek podle aktuální pozice kopie vzorce.
  • Je-li odkaz zapsán jako $A$1, mluvíme o absolutním odkazu. Při kopírování se každá kopie vzorce odkazuje na buňku A1.
  • Je-li odkaz zapsán jako $A1 nebo A$1, mluvíme o smíšeném odkazu. Při kopírování vzorce zůstává zachována ta část odkazu, před kterou je umístěn znak dolaru. U prvého uvedeného příkladu se tedy nebude měnit číslo sloupce, u druhého příkladu číslo řádku.

Aby nebylo nutné při tvorbě absolutních nebo smíšených odkazů zapisovat znak dolaru ručně z klávesnice, je možné využít tento postup:

  • Při tvorbě vzorce nejprve vytvoříte odkaz na buňku běžným klepnutím. Tím se ve vzorci vytvoří relativní odkaz.
  • Stisknete klávesu F4. Prvním stisknutím se odkaz přemění na absolutní a vloží se do něj oba znaky dolaru.
  • Dalším použitím klávesy F4 vytvoříte smíšený odkaz: druhé stisknutí vytvoří znak dolaru pouze u čísla řádku a třetí stisknutí pouze u označení sloupce.
  • Čtvrtým stisknutím klávesy F4 se odkaz přemění opět na relativní.
  • Po nastavení potřebného typu odkazu pokračujete ve tvorbě vzorce.

Odkaz na buňku ve vzorci můžete pozměnit i dodatečně:

  • Klepnete na buňku se vzorcem.
  • Pomocí klávesy F2 nebo klepnutím do řádku vzorců spustíte editační režim buňky.
  • Ve vzorci postavíte kurzor do potřebného odkazu. Nezáleží přitom, jestli je kurzor těsně před odkazem, těsně za ním nebo uprostřed odkazu.
  • Pomocí klávesy F4 upravíte typ odkazu. Takto můžete např. ve vzorci dodatečně vytvořit absolutní odkaz nebo jej naopak ze vzorce odstranit (pro změnu absolutního odkazu na relativní je třeba stisknout klávesu F4 několikrát, jediným stisknutím se vytvoří odkaz smíšený).

Jednotlivé typy odkazu se uplatní zcela stejně i při vzorcích, které se odvolávají na buňky z jiného listu. Jestliže při tvorbě vzorce vytvoříte odkaz tak, že klepnete nejprve na záložku potřebného listu a poté na buňky, do vzorce se vloží relativní odkaz tvaru „List!A1“. Při kopírování buňky se ve vzorci mění označení sloupců a čísla řádek, zatímco označení listu zůstává stejné. Pro tvorbu absolutního nebo smíšeného odkazu použijete opět klávesu F4.

2.    Pojmenování oblastí v MS Excel

Pojmenování buněk a oblastí oceníte hlavně při tvorbě složitých vzorců. Místo odkazování se na buňku pomocí odkazu (např. =A1) se odkážete přímo na její název (který si můžete zvolit např. DPH). Poté použijete jako odkaz toto jméno (=DPH). Pro přidělování názvů oblasti v Excelu existuje pár pravidel:

  • Název nesmí obsahovat mezeru.
  • Název nesmí začínat číslem.
  • Název musí začínat písmenem, nebo podtržítkem.
  • V názvech nelze používat symboly (kromě podtržítka).
  • Pro název lze použít i jedno písmeno (mimo C a R).
  • Velikost písmen v názvech nerozhoduje.
  • Název nesmí být shodný s názvem funkce.

Klikněte myší na konkrétní buňku, případně vyberte celou oblast buněk, kterou si přejete pojmenovat. Do Pole názvů vložte nový název buňky či oblasti a stiskněte tlačítko Enter. Nebo vyberte kartu Vzorce a v části Definované názvy stiskněte tlačítko Správce názvů. Zobrazí se nový dialog, ve kterém můžete pojmenované oblasti vytvářet, upravovat nebo mazat. Zvolíme-li Nový nebo Upravit, objeví se vám další okno, v němž již můžeme zadat nebo upravovat požadované údaje:

  • Název: – požadovaný název respektující pravidla.
  • Obor: – informace, zda je název platný pro celý sešit nebo jen daný list.
  • Komentář: – poznámka.
  • Odkaz na: – odkaz na dotyčnou oblast.

Budeme-li chtít použít název oblasti ve vzorci, Excel vám bude automaticky nabízet podle vložených znaků zabudované funkce, ale nově také vytvořené názvy oblastí. Pokud máme definovaný název (např. EAC) pro oblast buněk a chceme se na ní odkázat, musíme použít maticový zápis. Označíme stejně velkou oblast (kam se má původní oblast buněk přenést), do řádku vzorců zadáme odkaz na název oblasti (=EAC) a místo klávesy Enter použijete klávesovou zkratku Ctrl+Shift+Enter, která provede zápis maticového vzorce.

3.    Typy proměnných v MS Excel

Proměnná kategoriální (kvalitativní, slovní) je proměnná, kterou nemůžeme měřit, můžeme ji pouze zařadit do tříd. Varianty kvalitativní proměnné nazýváme kategoriemi, jsou vyjádřeny slovně a podle vztahu mezi jednotlivými kategoriemi se dělí na dvě základní podskupiny.

  • Proměnná nominální nabývá rovnocenných variant; nelze je smysluplně porovnávat ani seřadit (např. pohlaví, národnost, značka hodinek).
  • Proměnná ordinální tvoří přechod mezi kvalitativními a kvantitativními proměnnými; jednotlivým variantám lze přiřadit pořadí a vzájemně je porovnávat nebo seřadit (např. známka ve škole, velikost oděvů (S, M, L, XL), velikost obce).

Jiným způsobem dělení kvalitativních proměnných je dělení podle počtu variant, jichž proměnné mohou nabývat. Pak rozlišujeme:

  • Proměnná alternativní nabývá pouze dvou různých variant (např. pohlaví, zapnuto/vypnuto, živý/mrtvý)
  • Proměnná množná nabývá více než dvou různých variant (např. vzdělání, jméno, barva očí).

Proměnné kvantitativní jsou proměnné měřitelné. Jsou vyjádřeny číselně a dělí se na:

  • Proměnné diskrétní nabývající konečného nebo spočetného množství variant (např. měsíční příjem v tis. Kč, věk v letech).
  • Proměnné spojité nabývající libovolných hodnot z (poznámka: označujeme množinu reálných čísel) nebo z nějaké podmnožiny (např. průměrný měsíční příjem).

4.    Základní typy grafů v MS Excel

Sloupcový:

  • Skupinový sloupcový graf a prostorový skupinový sloupcový graf: Skupinové sloupcové grafy porovnávají hodnoty mezi různými kategoriemi. Skupinový sloupcový graf zobrazuje hodnoty jako dvojrozměrné svislé obdélníky. Prostorový skupinový sloupcový graf pouze zobrazuje data v trojrozměrné perspektivě. Třetí osa (hloubková) se nepoužívá.
  • Skládaný sloupcový graf a skládaný sloupcový graf v prostorovém zobrazení: Skládané sloupcové grafy znázorňují vztah jednotlivých položek k celku s porovnáním podílu každé z hodnot na celkové hodnotě v různých kategoriích. Skládaný sloupcový graf zobrazuje hodnoty jako dvojrozměrné svislé skládané obdélníky. Skládaný sloupcový graf v prostorovém zobrazení pouze zobrazuje data v trojrozměrné perspektivě. Třetí osa (hloubková) se nepoužívá.
  • 100% skládaný sloupcový graf a 100% skládaný sloupcový graf v prostorovém zobrazení: 100% skládané sloupcové grafy a 100% skládané sloupcové grafy v prostorovém zobrazení porovnávají procentuální podíl jednotlivých hodnot na celkové hodnotě v různých kategoriích. 100% skládaný sloupcový graf zobrazuje hodnoty jako dvojrozměrné svislé 100% skládané obdélníky. 100% skládaný sloupcový graf v prostorovém zobrazení pouze zobrazuje data v trojrozměrné perspektivě. Třetí osa (hloubková) se nepoužívá.

Spojnicový:

  • Spojnicový a spojnicový se značkami: Spojnicové grafy zobrazené se značkami označujícími jednotlivé datové hodnoty nebo bez nich jsou užitečné pro zobrazení trendů v průběhu času nebo pořadí kategorií, zvláště pokud je počet datových bodů velký a pořadí, v jakém jsou uvedeny, je důležité. Pokud existuje mnoho kategorií nebo jsou hodnoty pouze přibližné, použijte spojnicový graf bez značek.
  • Skládaný spojnicový a skládaný spojnicový se značkami: Skládané spojnicové grafy zobrazené se značkami označujícími jednotlivé datové hodnoty nebo bez nich lze použít k zobrazení trendů příspěvku jednotlivých hodnot vzhledem k času nebo seřazeným kategoriím, ale protože skládané čáry je obtížné sledovat, zvažte místo něj použití jiného typu spojnicového grafu nebo skládaného plošného grafu.
  • 100% skládaný spojnicový a 100% skládaný spojnicový se značkami: 100% skládané spojnicové grafy zobrazené se značkami označujícími jednotlivé datové hodnoty nebo bez nich jsou vhodné k zobrazení trendů příspěvku jednotlivých hodnot vzhledem k času nebo seřazeným kategoriím v procentech. Pokud existuje mnoho kategorií nebo jsou hodnoty pouze přibližné, použijte 100% skládaný spojnicový graf bez značek.

Výsečový:

  • Výsečový a prostorový výsečový: Výsečové grafy zobrazují příspěvek jednotlivých hodnot k celku ve formátu 2-D nebo 3-D. Můžete ručně vysunout výseče výsečového grafu pro zdůraznění výsečí.
  • Výsečový s dílčí výsečí a výsečový s dílčími pruhy: Výsečový graf s díly výsečí nebo výsečový graf s dílčími pruhy jsou výsečové grafy s hodnotami definovanými uživatelem, které jsou extrahovány z hlavního výsečového grafu a kombinovány do vedlejšího výsečového grafu nebo do skládaného pruhového grafu. Tyto typy grafů jsou užitečné v případě, že chcete usnadnit rozlišení malých výsečí v hlavním výsečovém grafu.

XY bodové grafy:

  • Bodový pouze se značkami: Tento typ grafu porovnává dvojice hodnot. Pokud používáte mnoho datových bodů a spojovací tratě by ztížila data číst, použijte bodový graf s datovými značkami, ale bez řádků. Tento typ grafu můžete použít také, pokud není nutné zobrazit připojení datových bodů.
  • Bodový s vyhlazenými spojnicemi a bodový s vyhlazenými spojnicemi a se značkami: Tento typ grafu zobrazí plynulou křivku spojující datové body. Vyhlazené spojnice lze zobrazit se značkami nebo bez značek. Pokud je počet datových bodů velký, použijte vyhlazené čáry bez značek.
  • Bodový s rovnými spojnicemi a bodový s rovnými spojnicemi a se značkami: Tento typ grafu zobrazí rovné čáry mezi datovými body. Rovné čáry lze zobrazit se značkami nebo bez značek.

5.    Základní míry a ukazatele v MS Excel

  • Maximum – MAX()
  • Minimum – MIN()
  • Aritmetický průměr – MEAN()
  • Harmonický průměr – pro výpočet průměru v případech, kdy proměnná má charakter části z celku (úlohy o společné práci) – HARMEAN()
  • Geometrický průměr – pracujeme-li s kladnou proměnnou představující relativní změny – GEOMEAN()
  • Modus – pro diskrétní proměnnou je modus nejčetnější varianta proměnné – MODE()
  • Dolní kvartil – rozděluje datový soubor tak, že 25% hodnot je menších než tento kvartil a zbytek, tj. 75% větších (nebo rovných) – QUARTIL()
  • Medián – rozděluje datový soubor tak, že polovina (50%) hodnot je menších než medián a polovina (50%) hodnot větších (nebo rovných) – MEDIAN()
  • Horní kvartil – rozděluje datový soubor tak, že 75% hodnot je menších než tento kvartil a zbytek, tj. 25% větších (nebo rovných) – QUARTIL()