Datová analýza

8.1 Databázový seznam

Mnoho tabulek zpracovávaných tabulkovými procesory má podobu seznamu: seznamy klientů, úkolů, zboží, telefonní seznam. Tyto seznamy mají charakter databáze a Excel s nimi umožňuje provádět obvyklé databázové operace jako je řazení, filtrování, zjišťování souhrnných informací, prohlížení a opravování dat po záznamech. Navíc poskytuje možnost vytvořit tzv. kontingenční tabulku (viz. kapitola 8.5), která kombinuje a porovnává data v tabulce. Tím má Excel k dispozici poměrně silné nástroje pro analýzu dat.

Než se s těmito nástroji seznámíme, připomeneme si, určitá pravidla, která platí pro seznamy, se kterými chceme pracovat jako s  databázovými tabulkami. Tabulka většinou popisuje nějaký objekt (osoba, kniha, prodej). Do sloupců tabulky se zapisují vlastnosti, které nás na tomto objektu zajímají (např. jméno, adresa, plat u zaměstnance, název, autor, cena u knihy). Řádky potom tvoří záznamy těchto údajů pro jednotlivé konkrétní prvky sledovaného objektu (jednoho zaměstnance, jednu knihu). První řádek tabulky - záhlaví obsahuje popis údajů obsažených v jednotlivých sloupcích. Hodnoty jednoho sloupce ve všech řádcích tabulky jsou stejného typu (např. číselné, textové). Seznam nesmí obsahovat prázdný řádek, jinak by nebylo možné části nad a pod prázdným řádkem zpracovávat jako jeden seznam.

Databázová tabulka může být vytvořena přímo v Excelu nebo lze pracovat s tabulkou vytvořenou v nějakém databázovém systému. Např. databázový soubor vytvořený v systémech FoxBase či dBase lze přímo otevřít příkazem Soubor — Otevřít. Kromě toho umožňuje Excel přístupovat k externím databázím zadáním příkazu Data — Importovat externí data. Zde můžeme pomocí Průvodce datovým připojením nadefinovat přístup k různým databázím (MS Access, SQL server, Oracle) nebo vytvořit dotaz, který z  externího zdroje vybere požadovaná data a přenese je do Excelu.

Ukázka databázového seznamu

Na obrázku 8.1.1 vidíme část databázového seznamu. Jedná se o databázi prodejů ekonomických agend. Přitom není podstatné, zda vznikla přímo vložením dat v Excelu nebo načtením z externího zdroje. Data byla pořizována v průběhu několika let, kdy se v jednotlivých čtvrtletích zapisovaly informace o počtu realizovaných prodejů jednotlivých agend. Záhlaví tabulky (první řádek sloupců A:F) popisuje strukturu databázové tabulky. 

Taková data jsou zdrojem informací například pro rozhodování, která agenda je perspektivní z hlediska výše zisku, počtu prodejů nebo v závislosti na čase. Pokud jsou ale data neuspořádaná, je taková analýza obtížná. V dalších kapitolách si ukážeme prostředky, jak požadované informace získat.

Excel umožňuje i část tabulky na listu definovat jako databázový seznam (příkazem Data — Seznam — Vytvořit seznam). V záhlaví oblasti seznamu se objeví nastavený filtr (viz. kapitola 8.3) a poslední řádek je vyhrazen pro souhrnné funkce. To umožňuje spravovat a analyzovat data seznamu nezávisle na ostatních datech mimo seznam.

Obrázek 8.1.1 Příklad databázového seznamu

Datový formulář

Prvním krokem k zefektivnění přístupu k datům u velmi rozsáhlých tabulek s  velkým počtem řádků i sloupců (nikoli však nástrojem datové analýzy), je možnost zobrazit si všechna data jednoho záznamu (všechny sloupce jednoho řádku) v jednom dialogovém okně - formuláři. Formulář, dostupný příkazem Data — Formulář..., umožňuje procházet tabulku záznam po záznamu, dělat opravy, případně odstraňovat či přidávat záznamy. Pro zobrazování záznamů si můžeme stanovit kritérium, které omezí zobrazované záznamy. V dialogovém okně formuláře je zobrazen vždy jeden celý záznam. Každá položka je v samostatném textovém poli. Položky, které nelze opravovat (např. jsou výsledkem vzorce), jsou zobrazeny bez textového pole. Po zadání příkazu se v dialogovém okně objeví vždy první záznam databáze.

Pomocí tlačítek  a  se můžeme pohybovat po záznamech. Rychlejší listování umožňují šipky a táhlo posuvníku.Chceme-li omezit záznamy, mezi kterými se pohybujeme, můžeme stanovit podmínku pro jejich zobrazování. To lze realizovat pomocí tlačítka , kdy se zobrazí stejně vyhlížející, ale prázdný formulář. Do prázdného textového pole libovolné položky můžeme zapsat podmínku. Zápis podmínky vidíme na obrázku 8.1.3: pokud do pole rok v našem příkladu napíšeme 2001, bude se vyhodnocovat podmínka rok= 2001. Pokud budeme chtít zobrazovat jen informace o prodejích agend s cenou vyšší než 50 000, napíšeme do pole cena výraz >50000.  Zadávání podmínek ukončíme tlačítkem ,  nebo . Nadále se budou zobrazovat pouze záznamy vyhovující stanoveným podmínkám. V našem příkladu na obrázku 8.1.3 se budou vyhodnocovat podmínky obě, budou tedy zobrazovány pouze prodeje roku 2001 jejichž cena je vyšší než 50 000. Chceme-li nastavená kritéria zrušit, stiskneme nejprve tlačítko  a ve formuláři Kritéria pak tlačítko .

Tlačítko  slouží k vložení dat nového záznamu. Po jeho stisknutí jsou všechna pole formuláře prázdná, připravená pro vstup nových dat. Po položkách záznamu se pohybujeme pomocí klávesy Tab (nikoli Enter!). Klávesou Enter se vkládání nového záznamu ukončuje. Nový záznam je přidán jako nový řádek na konec tabulky.

Kdykoli při procházení jednotlivými záznamy můžeme hodnoty v textových polích opravovat. Dokud máme záznam zobrazený, můžeme provedené opravy zrušit tlačítkem . Jakmile přejdeme na jiný záznam, jsou opravy záznamu uloženy.

Zobrazený záznam odstraníme z databáze stiskem tlačítka .

Řazení je nejjednodušším způsobem datové analýzy. Data seřazená podle různých aspektů zlepšují orientaci ve velkém množství dat a urychlují vyhledávání. Příkaz Data — Seřadit... umožňuje setřídit jak celý seznam, tak libovolnou označenou souvislou oblast tabulky. Třídíme-li celý seznam, není třeba ho celý označovat, stačí označit libovolnou buňku seznamu.

V dialogovém okně příkazu Data — Seřadit... volíme sloupec, podle kterého chceme řádky (záznamy) setřídit a způsob třídění (vzestupně – sestupně). Lze určit i další dva sloupce, podle nichž se má třídit v případě shody hodnot.

Tlačítkem  se dostaneme k dalšímu dialogovému oknu, kde můžeme zvolit:

• klíč k řazení (normální pro text a čísla nebo předdefinované textové posloupnosti)

• rozlišování malých a velkých písmen

• orientaci (odshora dolů = řazení řádků podle hodnot ve sloupci, zleva doprava = řazení sloupců podle hodnot v řádku)

Při nastavení podle obrázku 8.2.1 a 8.2.2 bude naše databáze agend seřazena abecedně podle jednotlivých roků a čtvrtletí od nejstarších prodejů k nejnovějším.

Jednou z výhod zpracování dat na počítači je rychlé a snadné vyhledávání údajů splňujících určitou podmínku. Pro vyhledání a zobrazení řádků (záznamů) s určitou vlastností má Excel v nabídce hlavního menu k dispozici příkaz Data — Filtr. Seznam, ve kterém chceme vyhledávat pomocí filtrů, musí mít pojmenované sloupce.

V  běžných případech vystačíme s automatickým filtrem. Po zadání volby Data — Filtr — Automatický filtr se v záhlaví sloupců seznamu objeví tlačítka se spouštěcími šipkami. Stisk tohoto tlačítka v určitém sloupci umožní zformulování podmínky pro vyhledávání vztahující se k tomuto sloupci. Po kliknutí na stahovací šipku se zobrazí okénko se seznamem. První položky seznamu jsou Seřadit vzestupně, Seřadit sestupně, (vše), (prvních 10...) a (vlastní...), pak následuje seznam všech hodnot ve sloupci. Dřívější verze obsahovaly ještě jako poslední dvě položky (prázdné) a (neprázdné). Význam jednotlivých voleb je následující:

• Seřadit vzestupně nebo sestupně umožňuje požadovaným způsobem seřadit filtrovaná data podle vybraného sloupce.
  Totéž lze provést použitím tlačítek  nebo ze standardního panelu nástrojů (v dřívějších verzích tato volba není).

• (vše) ruší případné dřívější nastavení filtru v daném sloupci

• (vlastní...) umožní formulování podmínky pomocí operátorů = , <>, <, >, < =, > =; 
     podmínky mohou být stanoveny i dvě a to tak, že platí obě současně nebo alespoň jedna; 
     pro text lze použít zástupné znaky "*" a "?"

• (prázdné) zobrazí řádky, které nemají v tomto sloupci data

• (neprázdné) zobrazí řádky, které mají v tomto sloupci data

Filtry můžeme nastavit ve více sloupcích. Platí pak všechny současně.

Zrušení všech nastavených filtrů a odstranění spouštěcích tlačítek ze záhlaví sloupců dosáhneme stejným postupem, jako jsme automatický filtr zapnuli, tj. Data — Filtr — Automatický filtr (nebo zrušením oblasti seznamu příkazem Data — Seznam — Převést na rozsah).

Na tabulce z obrázku 8.1.1 si ukažme, jak vytvoříme vlastní filtr. Budeme chtít zobrazit pouze prodeje (řádky tabulky) agend účetnictví (UcetnictviJ a UcetnictviP). Po aktivaci automatického filtru příkazem Data — Filtr — Automatický filtr, zvolíme v rolovacím seznamu možností ve sloupci Agenda možnost (vlastní) a v dialogovém okně zadáme podmínky jako na obrázku 8.3.1.

Obrázek 8.3.1 Dialogové okno pro nastavení vlastního automatického filtru

Potřebujeme-li zadat pro vyhledávání takové podmínky, že je nejsme schopni nastavit pomocí automatického filtru, použijeme rozšířený filtr. Podmínky se zapisují přímo na list mimo seznam, se kterým pracujeme. Za tímto účelem vložíme před seznam několik prázdných řádků. Oblast podmínek je malá tabulka, v jejímž prvním řádku jmusí být názvy kritérií. U obyčejných porovnávacích kritérií se název kritéria shoduje s názvem sloupce seznamu. Vlastní podmínky jsou zapsány pod názvy sloupců. Podmínky, které mají platit zároveň, píšeme do jednoho řádku; podmínky, z nichž má platit alespoň jedna, píšeme do různých řádků. Má-li platit zároveň více různých podmínek pro jeden sloupec, musíme tento sloupec uvést tolikrát, kolik je podmínek.

Jako podmínka může být použit i vzorec, jehož výsledkem je logická hodnota. Zobrazí se pak ty řádky, pro které vyjde hodnota "0". Vzorec se musí odkazovat alespoň na jeden sloupec seznamu a to buď jeho názvem nebo relativní adresou první buňky sloupce. Použijeme-li název sloupce seznamu, objeví se v buňce se vzorcem chyba #NÁZEV?, která však nemá vliv na správné filtrování seznamu. Název kritétia se vzorcem volíme odlišný od názvů sloupců seznamu.

Jestliže máme vytvořenou oblast podmínek, označíme libovolnou buňku seznamu a zadáme příkaz Data — Filtr — Rozšířený filtr. Zobrazí se dialogové okno (viz. obrázek 8.3.2), ve kterém je vyplněna adresa oblasti seznamu. (Pokud ne, zapomněli jsme označit buňku seznamu.) Umístíme textový kurzor do políčka Oblast kritérií a označíme tuto oblast na listě. Z  obrázku vidíme, že můžeme filtrovaný seznam zobrazit přímo v seznamu nebo na jiném místě. Pokud vybereme možnost Kopírovat jinam, zpřístupní se řádek Kopírovat do, ve kterém zadáme umístění filtrovaného seznamu.

Obrázek 8.3.2 Dialogové okno pro zadání rozšířeného filtru

Ukázku filtrovaného seznamu podle definice na obrázku 8.3.2 představuje obrázek 8.3.3. Podmínky rozšířeného filtru omezují řádky na prodeje agend UcetnictviJ, UcetnictviP a mzdy realizované v roce 2002, jejichž počet prodejů byl větší než 5.

Obrázek 8.3.3 Výsledek nastavení rozšířeného filtru s oblastí kritérií, řádkem souhrnů a panelem nástrojů Seznam

K zobrazení celého seznamu se vrátíme příkazem Data — Filtr — Zobrazit vše.

Některý seznam má sloupec, ve kterém se vyskytuje jen několik různých hodnot. Přitom z praktického hlediska má smysl celý seznam rozdělit do skupin podle těchto hodnot a hodnoty v jiných sloupcích vyhodnocovat v rámci těchto skupin. Vyhodnocováním může být sčítání, hledání průměrné hodnoty, největší hodnoty ap.

Skupiny řádků se stejnou hodnotou v určitém sloupci vytvoříme setříděním podle tohoto sloupce.

Máme-li seznam setříděný, dáme příkaz Data — Souhrny... V zobrazeném dialogové okno, ve kterém určujeme:

• jméno sloupce, jehož hodnoty určují skupiny (ten, podle kterého jsme třídili)

• funkci, kterou chceme vyhodnotit pro každou skupinu (součet, průměr, maximum ap.)

• sloupce, ve kterých se má zadaná funkce vyhodnotit

• zda má právě zvolená funkce v zadaných sloupcích zaměnit předchozí (jestliže ne, pak se přidá k předchozím)

• zda má být celkový souhrn pod daty (jinak bude nad daty)

Obrázek 8.4.1 Dialogové okno pro definici souhrnu

Po ukončení příkazu se do seznamu pod každou skupinu vloží řádek, ve kterém je v určených sloupcích hodnota zvolené funkce.

Chceme-li souhrny ze seznamu odstranít, zvolímeme příkaz Data — Souhrny...  a zde použijeme tlačítko  .

V našem seznamu prodejů z obrázku 8.1.1 vytvoříme souhrn podle definice na obrázku 8.3.1, to znamená, že chceme pro jednotlivé agendy spočítat počet prodejů. Před definicí souhrnu nezapomeneme seznam setřídit podle pole Agenda. Po nastavení parametrů dostaneme seznam uspořádaný jako na obrázku 8.4.2.

Obrázek 8.4.2 Výsledek vytvoření souhrnu, který pro jednotlivé agendy sečte počty prodejů ve všech letech

Všimněme si levé části před záhlavím řádků. Zde jsou znázorněny úrovně souhrnů, které lze sbalovat a rozbalovat. Náš souhrn je dvouúrovňový a rozbalený. Kliknutím na tlačítko  lze úroveň pro větší přehlednost sbalit. Sbalený stejný souhrn ukazuje obrázek 8.4.3.

Obrázek 8.4.3 Sbalený souhrnu z obrázku 8.4.2

Pokud bychom chtěli podrobnější informace o prodejích, např. kolik prodejů se u jednotlivých agend realizovalo v jednotlivých letech, můžeme na stávajícím souhrnu definovat další. V dialogovém okně bychom zadali souhrn při změně sloupce Rok. Tentokrát budeme navazovat na předchozí souhrn, proto nezatrhneme políčko Nahradit aktuální souhrny. Výsledek (přidání další úrovně, vidíme na obrázku 8.4.4

Obrázek 8.4.4 Víceúrovňový souhrn

Kontingenční tabulka je zvláštní tabulka, která shrnuje data vybraných sloupců seznamu. Podobně jako u datových souhrnů funkci shrnující data volíme – může to být součet, průměr, maximum ap. Při vytváření tabulky určujeme, které sloupce seznamu mají svými hodnotami předznačit řádky a sloupce kontingenční tabulky a ve kterém sloupci seznamu leží data, která chceme vyhodnocovat.

Protože termín kontingenční tabulka zní napoprvé dosti cize, ukážeme si raději hned na začátku příklad této tabulky. Je to kontingenční tabulka z naší průvodní databáze prodejů. Zachycuje stejné vyhodnocení dat (součty prodejů jednotlivých agend v jednotlivých letech), jaké jsme dělali v příkladu v předchozí kapitole pomocí datových souhrnů.

Kontingenční tabulku vytvoříme pomocí Průvodce kontingenční tabulkou. Průvodce spustíme zadáním příkazu Data — Kontingenční tabulka a graf  Průvodce má tři okna, ve kterých určujeme zdrojová data a umístění kontingenční tabulky, jak ukazuje obrázek 8.5.2.

Obrázek 8.5.2 Dialogová okna Průvodce kontingenční tabulkou

 V našem výkladu se omezíme se na tvorbu kontingenční tabulky ze seznamu či databáze Excelu. V tom případě ve druhém okně určujeme oblast s daty pro tabulku. Jsme-li na listu s jediným seznamem, objeví se v textovém okénku Oblast: napsáno Databáze a můžeme pokračovat stiskem tlačítka . Pokud chceme použít jen část seznamu nebo máme v sešitě seznamů více, musíme oblast nebo list se správným seznamem označit myší. Ve třetím okně určíme umístění kontingenční tabulky a můžeme stisknout tlačítko . Průvodce nabídne oblast listu a seznam dostupných polí pro kontingenční tabulku, jak ukazuje obrázek 8.5.3. Při přetahování polí z okna seznamu do vyznačených oblastí Excel automaticky sumarizuje a vypočítá tabulku.

Obrázek 8.5.3 Definice kontingenční tabulky

Podle nápovědy v rozvržení tabulky přetáhneme do příslušných oblastí ta pole, která chceme mít v záhlaví řádků a sloupců a z nichž chceme dělat souhrny (oblast data). Pokud chceme cokoli v tabulce změnit, např. popisy nebo souhrnné funkce, stačí na příslušné pole kliknout myší a buď z místní nabídky nebo z panelu nástrojů pro úpravu kontingenční tabulky vybereme odpovídající příkaz pro úpravu, nejčastěji příkaz Nastavení pole..... Například první kontingenční tabulka z obrázku 8.5.1 je upravena do podoby druhé ukázky tak, že kliknutím na pole A1 (Součet z počtu prodejů) a výběrem příkazu Nastavení pole.... z místní nabídky se v dialogovém okně (viz. obrázek 8.5.4) změnila funkce součet na maximum a upravil se název pole. Pro skrytí pole E2 (Celkový součet) se použil příkaz Skrýt z místní nabídky (po kliknutí na pole E2).

Obrázek 8.5.4 Změna nastavení vlastností polí kontingenční tabulky

V posledním okně Průvodce na obrázku 8.5.2 vidíme tlačítko . Představuje alternativu k definici kontingenční tabulky v listu (viz obrázek 8.5.3). Po jeho stisknutí se zobrazíí dialogové okno z obrázku 8.5.5.

Obrázek 8.5.5 Definice kontingenční tabulky

Okno zobrazuje schéma kontingenční tabulky a tlačítka s názvy všech sloupců seznamu. Název sloupce, jehož hodnoty mají předznačit řádky kontingenční tabulky, odsuneme myší do oblasti ŘÁDEK. Podobně název sloupce, jehož hodnoty mají být nadepsány nad sloupci kontingenční tabulky, přesuneme do oblasti SLOUPEC. Do oblasti DATA umístíme sloupec (případně sloupce), z něhož chceme dělat souhrny. Na políčko v oblasti DATA dvakrát klikneme a vybereme funkci (součet, maximum, minimum aj.). Pokud umístíme některý sloupec seznamu do oblasti STRANA, budou hodnoty tohoto sloupce sloužit jako filtr pro data zahrnutá v kontingenční tabulce.

Další tlačítko z třetího okna Průvodce zobrazí dialogové okno, ve kterém můžeme nastavit vlastnosti kontingenční tabulky jako např. součty ve sloupcích a řádcích.

Obrázek 8.5.5 Nastavení možností kontingenční tabulky

Toto okno můžeme zobrazit kdykoli později při úpravách kontingenční tabulky příkazem z místní nabídky Možnosti tabulky.