05 – Maticové vzorce, kontingenční tabulky – Učební materiály z geoinformatiky

Soubory ke cvičení:

1. Maticové vzorce v MS Excel

Maticový vzorec může provést několik výpočtů a potom vrátit jeden nebo několik výsledků. Maticové vzorce počítají na základě dvou nebo více množin hodnot neboli maticových argumentů. Každý maticový argument musí obsahovat stejný počet řádků a sloupců. Maticové vzorce vytvoříte stejně jako jiné vzorce. Jediný rozdíl spočívá v tom, že se vzorec zadává stisknutím kláves CTRL+SHIFT+ENTER.

2. Příklady

Příklad 2.1: Pracujte se souborem ZIS_EXCEL_PRIKLADY.xlsx, listem Příklad 2.1. Pomocí plnění dílčích úkolů uvedených v daném listu určete průměrnou míru nezaměstnanosti v daných obcích a graficky prezentujte závislost počtu uchazečů o zaměstnání na počtu ekonomicky aktivních obyvatel v daných obcích.

K pojmenování oblasti využívejte Pole názvů.
Počet neprázdných buněk v rozsahu je výstupem funkce POČET2(pole).
Pro výpočet Míry nezaměstnanosti (%) pomoci pojmenovaných názvů využijte maticový vzorec {=100*UC1/EAC1}. (Složená závorka se u vzorce vloží automaticky poté, co jej uložíte pomocí Ctrl + Shift + Enter).
{=POČET2(Obce_1r) }
{=ZAOKROUHLIT(PRŮMĚR(EAC_1r);presnost_e1r) }
{=ZAOKROUHLIT(EAC_1r/UC_1r;presnost_mn1r) }

Příklad 3.1: Určete medián míry nezaměstnanosti v obcích v analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx. K výpočtu použijte pouze proměnné UCDOS (počet dosažitelných uchazečů) a EAC (celkový počet ekonomicky aktivních obyvatel).

Pojmenujte oblasti, v nichž jsou uvedeny celkové počty uchazečů (UC3) a celkové počty ekonomicky aktivních obyvatel (EAC3).
Obvykle postupujeme tak, že do pomocného třetího sloupce zapíšeme míru nezaměstnanosti MN (%) a následně určíme medián proměnné MN.
Do pole F1 zapíšeme = MEDIAN(UC_3/EAC_3)
Stiskneme současně CTRL+SHIFT+ENTER

Příklad 3.2: V analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx určete medián míry nezaměstnanosti v obcích, které mají méně než 500 ekonomicky aktivních obyvatel. K výpočtu použijte pouze proměnné UCDOS (počet dosažitelných uchazečů) a EAC (celkový počet ekonomicky aktivních obyvatel).

Příslušné oblasti UC_3 a EAC_3 již máte pojmenovány.
Je zřejmé, že použijeme-li funkci MEDIAN, pak je problém vybrat odpovídající vstupní hodnoty.
K řešení lze použít podmíněný maticový vzorec.
Do buňky F6 zapíšeme = MEDIAN(KDYŽ(EAC_3<500;UC_3/EAC_3))
Stiskneme současně CTRL+SHIFT+ENTER

Příklad 3.3:

V analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx určete medián míry nezaměstnanosti v obcích, které mají více než 200 a nejvýše 500 ekonomicky aktivních obyvatel. K výpočtu použijte pouze proměnné UC_3 (počet dosažitelných uchazečů) a EAC_3 (celkový počet ekonomicky aktivních obyvatel).

Chceme-li použít větší množství podmínek pro výpočet požadované funkce, píšeme všechny podmínky do závorek a mezi ně píšeme znak pro násobení (*).
Obdobně jako v předcházejícím případě je modře zvýrazněná část vzorce odpovídající podmínkám (uvědomte si, že nejvýše 500 EAC je totéž co méně než 501 EAC). Do výpočtu mediánu nyní budou zařazeny pouze ty hodnoty UC3/EAC3, v jejichž řádku jsou splněny obě uvedené podmínky, tj. EAC_3>200 a zároveň EAC_3<501. Po ukončení zadání vzorce nezapomeňte stisknout CTRL+SHIFT+ENTER.

Příklad 3.4: V analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx, listu Příklad 3.1-3.4 určete průměrnou míru nezaměstnanosti, průměrnou míru nezaměstnanosti v obcích, které mají méně než 500 ekonomicky aktivních obyvatel a průměrnou míru nezaměstnanosti v obcích, které mají více než 200 a nejvýše 500 ekonomicky aktivních obyvatel.

Při hledání průměrné míry nezaměstnanosti nelze postupovat tak, že do pomocného třetího sloupce zapíšeme míru nezaměstnanosti MN (%) a následně určíme průměr proměnné MN. Průměrná míra nezaměstnanosti je rovna poměru celkového počtu uchazečů o zaměstnání (UC_3) a celkového počtu ekonomicky aktivních obyvatel (EAC_3).
Průměrná míra nezaměstnanosti =SUMA(UC_3)/SUMA(EAC_3).
Průměrnou míru nezaměstnanosti v obcích, které mají méně než 500 ekonomicky aktivních obyvatel určíme pomocí podmíněného maticového vzorce (viz příklad 3.2). Jen je nutné si uvědomit, že podmínku je třeba uvést vždy, když ve vzorci používáme název nějaké oblasti. SUMA(KDYŽ(EAC_3<500;UC_3))/SUMA(KDYŽ(EAC_3<500;EAC_3))
I pro splnění posledního úkolu použijeme podmíněný maticový vzorec. Tentokrát půjde o dvě podmínky, obdobně jako v příkladu 3.3., SUMA(KDYŽ((EAC_3>200)* (EAC_3<501);UC_3))/SUMA(KDYŽ((EAC_3>200)* (EAC_3<501);EAC_3)).
Po ukončení zadání vzorců nezapomeňte stisknout CTRL+SHIFT+ENTER.
{=MEDIAN(UC_3r/EAC_3r)}
{ =MEDIAN(KDYŽ(EAC_3r<500;UC_3r/EAC_3r))}
{ =MEDIAN(KDYŽ((EAC_3r>200)*(EAC_3r<501);UC_3r/EAC_3r))}
{ =SUMA(UC_3r)/SUMA(EAC_3r)}
{ =SUMA(KDYŽ(EAC_3r<500;UC_3r))/SUMA(KDYŽ(EAC_3r<500;EAC_3r))}
{=SUMA(KDYŽ((EAC_3r>200)*(EAC_3r<501);UC_3r))/SUMA(KDYŽ((EAC_3r>200)*(EAC_3r<501);EAC_3r))}

Příklad 4.1: V analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx na listu Příklad 4.1 zobrazte pouze údaje o obcích z okresu Benešov.

Proměnná NAZEV obsahuje nejen informaci o názvu obce, ale i informaci o okresu, v němž obec leží.
Rozdělte tyto informace do dvou sloupců. Je-li text složen z jednotlivých částí, které jsou odděleny oddělovači (mezera, čárka, středník…), lze v MS Excel tento text jednoduše (pomocí funkce Text do sloupců, kterou najdete v záložce DATA v části Datové nástroje) rozdělit na dílčí části, které jsou zapsány v jednotlivých sloupcích.
Nejdříve je nutno text upravit tak, aby dílčí části byly odděleny pouze jedním oddělovačem. V našem případě je název obce a okresu oddělen nejen čárkou, ale čárkou a mezerou. Označte všechny hodnoty proměnné NAZEV (využijte CTRL+SHIFT+) a následně použijte proto funkci Nahradit (záložka DOMŮ, část Úpravy, Najít a vybrat) – nahraďte „čárku s mezerou“ pouze „čárkou“.
V dalším kroku si musíte připravit prostor pro rozdělení textového řetězce.
Označte všechny hodnoty proměnné NAZEV (využijte CTRL+SHIFT+) a použijte funkci Text do sloupců (záložka DATA, část Datové nástroje).

Příklad 4.2: Rozdělte obce zařazené v analyzovaném souboru ZIS_EXCEL_PRIKLADY.xlsx v listu Příklad 4.2-4.3 do kategorií podle počtu obyvatel („nejvýše 300“, „301-500“; „501-800“; „více než 800“).

Pojmenujte oblast s údaji o počtu ekonomicky aktivních obyvatel EAC_4.
Přiřaďte jednotlivým obcím správnou variantu proměnné Kategorie obcí.
Je zřejmé, že když EAC_4 bude menší než 301, pak by obec měla mít přiřazenu variantu „nejvýše 300“. Pokud obec nespadá do této kategorie, pak v případě že má méně než 501 EAC_4, pak spadá do kategorie „301-500“, …
Pro přiřazení správné varianty kategorie obcí proto použijeme několik vnořených funkcí KDYŽ. (Nezapomeňte po zadání funkce zmáčknout CTRL+SHIFT+ENTER – jde o maticový vzorec.)
={KDYŽ(EAC_4<301;”nejvýše300″;KDYŽ(EAC_4<501;”301-500″;KDYŽ(EAC_4<801;”501 – 800″;”více než 800″)))}

Příklad 4.3: Určete četnosti jednotlivých variant proměnné Kategorie obcí z předcházejícího příkladu samostatně. Následně zobrazte sloupcový a výsečový graf.

Vypište všechny varianty proměnné Kategorie obcí. Tento úkol můžete splnit buď tak, že všechny názvy variant vypíšete ručně, nebo využijete možností MS Excel.
Označte všechny hodnoty proměnné Kategorie obcí a stiskněte CTRL+C.
Vložte tyto hodnoty do pole E7 jako Hodnoty (to jsme se naučili v předcházejícím příkladu – kliknutí pravou myší Vložit jinak Hodnoty).
Na kartě Data zvolte Odebrat stejné, označte Pokračovat s aktuální oblastí a stiskněte tlačítko Odebrat duplicity.
Pokud chcete varianty seřadit, dopište do předcházejícího sloupce (v našem případě D) pořadí, označte data obsahující pořadí a varianty a na kartě Data zvolte položku Seřadit. Následně v poli Seřadit podle zvolte Sloupec C a stiskněte OK.
Pojmenujte oblast s kategoriemi obcí Kategorie_4.
Pro doplnění četnosti využijeme funkci COUNTIF, která vrací počet buněk v zadané oblasti, které splňují požadované kritérium.
=COUNTIF(Kategorie_4r;E8(až11))

Příklad 4.4 a 4.5: Určete základní číselné charakteristiky proměnné MN (Míra nezaměstnanosti) uvedené v souboru ZIS_EXCEL_PRIKLADY.xlsx, v listu Příklad 4.4-4.5 a vytvořte histogram s hranicemi tříd danými na listu.

Pojmenujte oblasti s údaji o počtech ekonomicky aktivních obyvatel (EAC_5), počtech uchazečů o zaměstnání (UC_5) a mírou nezaměstnanosti (MN_5).
Pro stanovení základních charakteristik míry nezaměstnanosti použijte následující funkce MS Excel.
={ZAOKROUHLIT(100*UC_5/EAC_5;1)}
=100*SUMA(UC_4r)/SUMA(EAC_4r)
=MIN(MN_4r)
=MAX(MN_4r)
=QUARTIL.INC(MN_4r;1)
=QUARTIL.INC(MN_4r;3)
={ZAOKROUHLIT(SUMA((MN_5-PRUM_5)^2)/(POČET2(MN_5)-1);1)}
=ODMOCNINA(H15)
=H16/H10
Nyní označte všechna pole, v nichž chcete četnosti vypočíst, napište =ČETNOSTI( a vyvolejte průvodce funkcí (kliknutí na f(x) před řádkem vzorců).
Jako Data zadejte MN_5, jako Hodnoty zadejte hranice tříd (H24:H28). Práci v průvodci funkcí ukončete stisknutím OK.
Vzhledem k tomu, že funkce ČETNOSTI je maticová funkce, musíte funkci zadat stisknutím CTRL+SHIFT+ENTER.
Nyní můžeme vykreslit histogram – zvolte dvojrozměrný sloupcový graf.

Příklad 5.1 a 5.2: Analyzujte závislost mezi mírou nezaměstnanosti a počtem ekonomicky aktivních obyvatel na základě dat uvedených v souboru ZIS_EXCEL_PRIKLADY.xlsx v listu Příklad 5.1. Analýzu proveďte na základě kategorizovaných proměnných (kontingenční tabulka, a 100% skládaný pruhový graf.

Vytvoříme kontingenční tabulku.
Označte všechny hodnoty proměnných ve sloupcích B-E (včetně názvů). Důležité je, aby byly označeny hodnoty analyzovaných proměnných, včetně popisků.
Na kartě Vložení zvolte, v záložce Grafy zvolte položku Kontingenční graf a tabulka. Pro umístění tabulky zvolte aktuální list, pole.
Upravte pořadí variant v kontingenční tabulce. (Zadávání proměnných proveďte „přetahováním myší“.)
Seřaďte smysluplně názvy variant jednotlivých proměnných v kontingenční tabulce.
Všimněte si, že názvy Kategorií obcí jsou seřazeny podle abecedy, což není v našem případě vhodný způsob. Pro větší přehlednost grafu, který budeme vytvářet, je vhodné, abyste Popisky řádků seřadili smysluplně.
Seřazení položek lze provést ručně. Označte pole s popiskem, které chcete přemístit a pomocí myši jej přetáhněte na zvolenou pozici. To provádějte tak dlouho, dokud nebudete se seřazením Popisků řádku spokojeni.
Vytvořte 100% skládaný pruhový graf.
Na kartě Návrh zvolte položku Změnit typ grafu, typ grafu Pruhový, 100% skládaný pruhový.
Aktivujte graf a upravte jeho vzhled, tj.
doplňte název grafu,
doplňte název svislé osy,
přesuňte legendu pod graf,
doplňte popisky dat
skryjte tlačítka polí (na kartě Analyzovat, Tlačítka polí, Skrýt vše).

3. Časové řady

Časová řada je numerická proměnná, jejíž hodnoty podstatně závisí na čase, v němž byly získány (posloupnost chronologicky uspořádaných pozorování). Časové okamžiky, kdy byla data získána, jsou od sebe většinou stejně vzdáleny. Jde například o

počty nezaměstnaných v jednotlivých měsících,
počty automobilových nehod na Barandovském mostě v jednotlivých měsících,
denní produkce mléka Veselé krávy.

Časové řady lze klasifikovat podle různých hledisek, např.: podle charakteru dat, jejichž hodnoty tvoří časovou řadu.

časové řady intervalové – data závisí na délce intervalu, který je sledován (např. měsíční výroba cementu v ČR).
časové řady okamžikové – data se vztahují k určitému okamžiku (počet nezaměstnaných v ČR v jednotlivých měsících)

Podle periodicity, s jakou jsou data sledována:

časové řady údajů ročních.
časové řady krátkodobé.

Podle druhu sledovaných dat:

časové řady absolutních ukazatelů – např. počet obsloužených klientů za měsíc
časové řady odvozených charakteristik – např. časová řada kumulativní (kumulativní časové řady, které vznikají postupným načítáním (kumulováním) jednotlivých hodnot (u okamžikových časových řad nemají smysl, neboť výše jejich hodnot nezávisí na daném časovém intervalu, např. aktuální počet obsloužených klientů od začátku roku)

Očištění časové řady o důsledky kalendářních variací:

Chceme-li porovnávat jednotlivé hodnoty u intervalových krátkodobých časových řad, musí se tyto hodnoty vztahovat ke stejně dlouhým časovým intervalům.

Očištění na měsíce:

standardní měsíc o délce 30 dnů – údaj za každý měsíc se vydělí počtem dnů v měsíci a vynásobí se 30, součet měsíčních údajů za rok potom odpovídá „roku“ o délce 360 dní
standardní měsíc o délce 365/12 dnů – součet měsíčních údajů za rok odpovídá délce roku 365 dní

Očištění na pracovní dny – provádí se obdobně jako očištění na měsíce.

Základní informace pro analýzu časových řad získáme ze spojnicových grafů. Jejich princip spočívá v zakreslení jednotlivých hodnot časové řady do souřadnicového systému. Na osu horizontální se vynáší časová proměnná a na osu vertikální hodnoty časové řady nebo její funkce. Do spojnicového grafu můžeme zakreslit i více časových řad. V případě, že zobrazujeme např. dvě časové řady lišící se měřítkem, je možné použít kromě levé i pravou vertikální osu. Speciálním případem spojnicového grafu dvou a více časových řad je graf ročních hodnot sezónních časových řad. Tento graf zobrazuje hodnoty časové řady uspořádané podle roků a tak charakterizuje, jak se v jednotlivých letech liší úroveň hodnot v daných sezónách za celou časovou řadu.

Příklad 7.1 a 7.2: Převeďte měsíční řady míry nezaměstnanosti v okresech Středočeského kraje z datové matice do standardního datového formátu, který je vhodný pro následnou statistickou analýzu. Data jsou uvedena v souboru ZIS_EXCEL_PRIKLADY.xlsx v listu Příklad 7.1-7.2.

V případě, že v pásu karet nemáte k dispozici Průvodce kontingenční tabulkou, zobrazte jej.
Klikněte na ikonu Přizpůsobit panel nástrojů Rychlý přístup a zvolte položku Další příkazy.
V poli Zvolit příkazy zvolte Všechny příkazy, v nabízených příkazech vyberte Průvodce kontingenční tabulkou, klikněte na Přidat a své rozhodnutí potvrďte kliknutím na tlačítko OK.
V pásu karet se objevila ikona reprezentující Průvodce kontingenční tabulkou.
Klikněte na ikonu Průvodce kontingenční tabulkou a v prvním kroku zvolte položku Násobné oblasti sloučení.
Ve druhém kroku potvrďte, že chcete vytvořit jedno stránkové pole a v následujícím kroku přidejte oblast datové matice.
V posledním kroku určete, kam se má standardní datový formát zapsat (vy zvolte aktuální list, buňku A25).
Přetažením polí kontingenční tabulky upravte design tabulky tak, jak vidíte na obrázku.
Dvakrát klikněte (double click) na pole, v němž je uveden Součet z hodnot (buňka A26).
V souboru ZIS_EXCEL_PRIKLADY.xlsx se objevil další list, v němž najdete data v požadovaném formátu.
Ve standardním datovém formátu upravte názvy proměnných (sloupců) – Obec; Datum; MN.
POZOR! Pro funkčnost uvedeného postupu je nutné, aby datová matice měla právě jeden identifikátor v řádku a právě jeden identifikátor ve sloupci.
Z proměnné Datum vytvořte proměnné Měsíc a Rok. (Využijte funkce MĚSIC a ROK)
Data z nového listu zkopírujte do listu Příklad 7.1 (do buněk A28:E1468).
Pro transformaci dat do vhodného formátu použijeme opět Kontingenční tabulky.
Označte data ve standardním datovém formátu (buňky A28:E1468).
Na kartě Vložení v záložce Grafy zvolte položku Kontingenční graf a kontingenční tabulka.
Kontingenční tabulku vložte na list Příklad 7.1 do pole G28.
Jako Filtr sestavy zadejte název obce, jako Popisky řádků Měsíc a jako Popisky sloupců Rok. Jako zadejte Součet míry nezaměstnanosti (MN). (Na možnost Součet z MN přejdete kliknutím na šipku vedle nápisu Počet z MN a volbou příslušné možnosti v položce Nastavení polí hodnot).
Volbou konkrétní obce ve filtru kontingenční tabulky (viz obrázek) získáte požadovaný formát tabulky.
Uvědomte si, že celkové součty nejsou v tomto případě relevantní údaje.)
Na závěr upravíme grafický výstup.
Pro grafickou prezentaci měsíčních časových řad v průběhu několika let je vhodné použít spojnicové grafy. Změňte proto typ kontingenčního grafu na spojnicový. (Graf aktivujte (klikněte na něj) a na kartě Návrh v záložce Typ zvolte Změnit typ grafu).
Chcete-li, odstraňte z grafu tlačítka. (Graf aktivujte (klikněte na něj) a na kartě Analyzovat v záložce Zobrazit nebo skrýt zvolte Tlačítka polí. Označte Skrýt vše).
Popište osy grafu (Měsíc, Míra nezaměstnanosti (%)).
Budete-li nyní měnit ve filtru kontingenční tabulky název obce, bude se vám automaticky překreslovat graf a přepočítávat kontingenční tabulka.
Vytvořte dynamický název grafu, který bude obsahovat název obce vybrané ve filtru kontingenční tabulky.
V poli N45 vytvořte dynamický název grafu: „Vývoj míry nezaměstnanosti (Název obce)“. Využijte funkci Concatenate( ), která umožňuje slučovat textové řetězce (viz obrázek).
Přidejte do grafu název grafu formou odkazu na buňku N45 a přidejte do grafu názvy os.