Analýza dat s využitím MS Excel Seminář aplikované statistiky Martina Litschmannová
Několik „fíglů“ na úvod Absolutní vs. relativní adresování – změna pomocí F4 =$H$20 =H$20 =$H20 =H20 Posun po souvislé oblasti – CTRL + šipky Výběr souvislé oblasti – CTRL+SHIFT+šipky Ukotvení příčky – Zobrazení Ukotvit příčky
Jak analyzovat kategoriální data? Tabulka četností Druh sportu Tenis Golf Hokej
četnost 24 1096 1677
rel. četnost [%] 0,9 39,2 60
Sloupcový graf, Výsečový graf Počet prodaných výrobků
2000
Počet prodaných výrobků
1500
1677; 60%
1000 1096; 39%
500
24; 1%
0 Tenis
Golf
Hokej
Tenis
Golf
Hokej
Jak analyzovat kategoriální data? Tabulka četností Druh sportu Tenis Golf Hokej
Počet prodaných výrobků 24 (0,9%) 1096 (39,2%) 1677 (60%)
Sloupcový graf, Výsečový graf Počet prodaných výrobků
2000
Počet prodaných výrobků
1500
1677; 60%
1000 1096; 39%
500
24; 1%
0 Tenis
Golf
Hokej
Tenis
Golf
Hokej
Kontingenční tabulka Co je to kont. tabulka? Tabulka, která se používá k přehledné vizualizaci vzájemného vztahu dvou statistických znaků. Prodavač Fantomas Krakonoš Monika Pavel Rakosniček Celkem
Golf 94 86 83 79 88 430
Hokej 124 134 116 130 148 652
Tenis 5 2 2 1 3 13
Celkem 223 222 201 210 239 1095
Kontingenční tabulka Jaká je struktura kont. tabulky v Excelu?
Pro nejjednodušší kont. tabulku je nutno vyplnit oblast řádků, resp. sloupců a oblast dat. Chceme-li použít kont. tabulku, musíme mít jasnou představu požadovaného výstupu!!!
Úkol 1
Úkol 1 Analyzujte proměnnou Druh sportu, tj. zjistěte příslušné počty prodaných položek dle druhu sportu (četnosti), … Popisky řádků Tenis Golf Hokej Celkový součet
Počet z Druh
sportu 24 1096 1677 2797
Počet z Druh 0,86% 39,18% 59,96% 100,00%
Úkol 1 Analyzujte proměnnou Druh sportu, tj. zjistěte příslušné počty prodaných položek dle druhu sportu (četnosti), … Popisky řádků Tenis Golf Hokej Celkový součet
Počet z Druh
sportu 24 1096 1677 2797
Počet z Druh 0,86% 39,18% 59,96% 100,00%
Postup: Vyberte data, která chcete vložit do kontingenční tabulky (sloupec Druh sportu). Vložení Kontingenční tabulka (skupina Tabulky)
Popisky řádků Golf Hokej Tenis Celkový součet
Nastavení polí hodnot
Počet z Druh
sportu 1096 1677 24 2797
Nastavení polí hodnot
Zobrazit hodnoty jako
% z celkového součtu Popisky řádků Golf Hokej Tenis Celkový součet
Počet z Druh
sportu Počet z Druh 1096 39,18% 1677 59,96% 24 0,86% 2797 100,00%
Kopie kontingenční tabulky Popisky řádků Golf Hokej Tenis Celkový součet
Druh sportu Tenis Golf Hokej
Druh sportu Tenis Golf Hokej
Počet z Druh
sportu Počet z Druh 1096 39,18% 1677 59,96% 24 0,86% 2797 100,00%
četnost 24 1096 1677
rel. četnost [%] 0,9 39,2 60
Počet prodaných výrobků 24 (0,9%) 1096 (39,2%) 1677 (60%)
=ZÍSKATKONTDATA("Počet z Druhsportu";$A$5;"Druhsportu";"Tenis")
=ZÍSKATKONTDATA("Počet z Druhsportu";$A$5;"Druhsportu";A13)
=ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";"Tenis")
=ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";A13)
=ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";A13)
=100*ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";A13) převod na procenta
=100*ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";A13)
=ZAOKROUHLIT(100*ZÍSKATKONTDATA("Počet z Druh";$A$5;"Druhsportu";A13);1) zaokrouhlení na 1 des. místo
24
(
0,9
%)
=CONCATENATE(B13;" (";C13;"%)") Sloučení textových řetězců
Úkol 4
Zavedení doplňku Analytické nástroje
Zdroj: http://office.microsoft.com/cs-cz/excel-help/zavedeni-doplnku-analyticke-nastrojeHP010021569.aspx
Zisk [Kč] Rozsah výběru Průměr Minimum Dolní kvartil Medián Horní kvartil Maximum Směr. odchylka Rozptyl Variační koeficient Šikmost Špičatost Vnitřní hradby Dolní mez Horní mez
2797 135,6 30,0 52,5 148,4 201,4 292,5 80,0 6 400,4 59% 0,2 -1,3 -170,9 424,8
Úkol Data-kont
Standardní datový formát (SDF)
Jak převést data do standardního datového formátu? 1.
Očíslujte si všechny sloupce tabulky.
Posun v řádku –TAB (tabelátor)
Jak převést data do standardního datového formátu (SDF)? 1.
Připravte si základ SDF a přidejte sloupec „Číslo sloupce“.
Číslo sloupce v základní části SDF odpovídá číslu sloupce, v němž je první typ výsledku, jenž budeme chtít do SDF doplnit.
Jak převést data do standardního datového formátu (SDF)? 1.
Připravte si základ SDF a přidejte sloupec „Číslo sloupce“.
Číslo sloupce v základní části SDF odpovídá číslu sloupce, v němž je první typ výsledku, jenž budeme chtít do SDF doplnit.
Jak převést data do standardního datového formátu (SDF)? 2.
Pomocí odkazů na předcházející buňky kopírujte potřebný počet základních částí SDF. (Číslo sloupce v každé kopii navyšujte o 1.)
=P5+1
Jak převést data do standardního datového formátu (SDF)? 3.
Přidejte sloupec „Výsledek“, do nějž pomocí vyhledávací funkce SVYHLEDAT doplníte hodnotu, která je pro příslušné ID uvedena v daném sloupci .
ID
Měsíc
Rok
1 2
11 12
2005 2005
Číslo Výsledek sloupce 4 9,1 4 11,1
Jak převést data do standardního datového formátu (SDF)? 3.
Přidejte sloupec „Výsledek“, do nějž pomocí vyhledávací funkce SVYHLEDAT doplníte hodnotu, která je pro příslušné ID uvedena v daném sloupci .
ID
Měsíc
Rok
1 2
11 12
2005 2005
Číslo Výsledek sloupce 4 9,1 4 11,1
Jak převést data do standardního datového formátu (SDF)? 4.
Rok 2005 2005
Přidejte sloupec „Typ výsledku“, do nějž pomocí vyhledávací funkce VVYHLEDAT doplníte hodnotu, která je pro příslušné číslo sloupce uvedena ve 2. řádku tabulky .
Číslo Typ Výsledek sloupce výsledku 4 9,1 Test A 4 11,1 Test A
Nyní lze k rychlému provedení analýzy použít kontingenční tabulky.
Užitečné odkazy Klávesové zkratky http://wall.cz/excel-navod/klavesove-zkratky-pro-ms-excel Seznam funkcí http://www.office4you.cz/rady-a-navody-ms-excel/275-excelseznam-funkci.html Tipy a triky http://office.lasakovi.com/excel/ http://www.office4you.cz/rady-a-navody-ms-excel.html
Děkuji za pozornost!
