1 POPISNÁ STATISTIKA V PROGRAMU MS EXCEL

Elena Mielcová, Radmila Stoklasová a Jaroslav Ramík; Statistické programy

1 POPISNÁ STATISTIKA V PROGRAMU MS EXCEL RYCHLÝ NÁHLED KAPITOLY Žádný výzkum se v dnešní době nevyhne statistickému zpracování dat. Je jedno, zda se jedná o medicínu, biologii, nebo ekonomii případně marketing. Jmenovitě ekonomické disciplíny patří mezi ty obory, pro které je zpracování dat nevyhnutelné. Pro složitější testování a sofistikované ekonometrické modely ekonomové určitě zvolí speciální statistický software, ale pro základní charakteristiky dat, statistické testy a jednoduché regresní modely je možné použít také tabulkový procesor. To, jak pro základní statistické operace využít tabulkový procesor Excel je stručně popsané v prvních čtyřech kapitolách studijního textu. Předpokládáme přitom, že čtenář má k dispozici verzi Excel 2007, eventuálně vyšší. Pro zjednodušení práce je vhodné mít aktivovaný doplněk „Analýza dat“ ve složce „Data“ (viz Obr.1.1) Obrázek 1.1

Zdroj: Vlastní zpracování.

V případě, že tento doplněk není ve složce „Data“, lehce ho nainstalujete tímto postupem: „Tlačítko Office“  „Možnosti aplikace Excel“  „Doplňky“  „Přejít…“ a v dialogovém okně zaškrtnout položku „Analytické nástroje“ (viz Obr. 1.2). Obrázek 1.2


Kromě doplňku „Analýza dat“ tabulkový procesor MS Excel disponuje širokým spektrem statistických funkcí. Všechny funkce procesoru MS Excel použité v následujícím textu budou vyznačeny ve tvaru: =FUNKCE(proměnná1;…; proměnnáN) se znaménkem „=“ na začátku; použití analytického nástroje bude vyznačeno podobným způsobem, například Histogram.

-9-

1 Popisná statistika v programu MS Excel

Základní metodou zpracování velkého rozsahu číselných dat je metoda rozdělení četnosti, a jeho zobrazení pomocí sloupcového grafu – histogramu četnosti. Dalším krokem je obvykle výpočet základních charakteristik souboru a případné znázornění dat pomocí grafů, aby bylo možné odhadnout případné závislosti v souboru. Konstrukcí histogramu četnosti a výpočtem základních charakteristik dat se zabývá další část této kapitoly.

1.1 HISTOGRAM ČETNOSTI Histogram četnosti je sloupcový graf, znázorňující rozdělení četností číselných dat v třídách – nepřekrývajících se stejně širokých intervalech. Optimální počet tříd k v histogramu lze stanovit pomocí tzv. Sturgersova pravidla k  Round (3,3.log10 (n))  1 , kde n je počet údajů v souboru. Funkce Round ( ) označuje zaokrouhlení argumentu funkce na nejbližší celé číslo. Počet tříd v histogramu se může mírně lišit od optimálního hlavně z důvodů většího přehledu a logiky v datech. Například časy příchodů zákazníků do prodejny sledované po dobu jednoho týdne je logické do histogramu seřadit v závislosti na velikosti souboru po dnech, případně po hodinách, a nesnažit se uměle vytvořit třídy, které nekorespondují s obvyklým časovým členěním týdne (například 1,8 dne, nebo 3,48 hodiny) Je-li stanoven počet tříd, pak šířku třídy lze určit jako podíl rozpětí souboru a počtu tříd. Za rozpětí souboru považujeme rozdíl nejmenší a největší hodnoty souboru. Tabulkový procesor MS Excel umožňuje vytvoření histogramu přímo z dat pomocí analytického nástroje Histogram. Jako vstupní údaj stačí zadat pouze soubor číselných dat a horní hranice požadovaných tříd. Použití tohoto analytického nástroje demonstruje následující příklad: ŘEŠENÝ PŘÍKLAD 1.1 Následující tabulka obsahuje počty bodů, které získali studenti na testu ze statistiky. 48 62 78 56 74 23 2 48 99 100 59 25 34 36 70 0 24 36 48 59 52 38 47 23 88 78 67 68 20 11 a) Vypočítejte optimální počet tříd pomocí Sturgersova pravidla. b) Zobrazte histogram četnosti pro počet tříd z příkladu a). c) Zobrazte histogram četnosti pro pět tříd. Řešení: a) Optimální počet tříd závisí na celkovém počtu pozorování (údajů) – v zadání je výsledek třiceti písemných prací, tedy n  30 . Optimální počet tříd: k  Round (3,3.log10 (30))  1  Round (3,3.1,4771)  1  Round (4,8745)  1  5  1  6 b) Rozpětí souboru R zjistíme jako rozdíl maximální a minimální hodnoty v datech. Tento rozdíl je: R  max( xi )  min( xi )  100  0  100 Šířka třídy bude tedy 100/6 = 16,7. Pro sestrojení histogramu četnosti je nutné připravit data a horní hranice tříd (viz Obr. 1.3).

- 10 -

Elena Mielcová, Radmila Stoklasová a Jaroslav Ramík; Statistické programy Obrázek 1.3


Po otevření nástroje Histogram (Data  Analýza dat  Histogram) lze zadat vstupní oblast dat, horní hranice tříd a označit, že program má vytvořit graf (Obr. 1.4). Obrázek 1.4


Program vygeneruje na nový list požadované četnosti a také histogram (Obr.1.5). Obrázek 1.5


- 11 -


c) V případě, že histogram bude mít pět tříd je šířka třídy 20. Připravené zadání (Obr.1.6) Obrázek 1.6


Výsledný histogram (obr 1.7): Obrázek 1.7


_______________________________________________________________________

1.2 ZÁKLADNÍ CHARAKTERISTIKY DAT Číselné charakteristiky jsou numerickým vyjádřením nejzákladnějších vlastností statistického souboru. Podle toho, které vlastnosti popisují, je lze rozdělit na charakteristiky polohy a charakteristiky variability. Mezi základní charakteristiky polohy patří modus, medián a průměr. Mezi základní charakteristiky variability patří rozptyl, směrodatná odchylka, šikmost a špičatost. Modus xˆ představuje nejčetnější hodnotu, tedy takovou hodnotu, která se v souboru vyskytuje nejčastěji. Je zcela nezávislý na ostatních hodnotách, které se mohou libovolně měnit, aniž se modus změní. Modus v programu MS Excel vypočítáme pomocí statistické funkce = MODE(číslo1;číslo2;...). V případě že je v souboru více modů (multimodální soubor), funkce zobrazí první (nejmenší) modus v pořadí.

- 12 -


Medián x~ představuje prostřední hodnotu v souboru hodnot, tedy takovou hodnotu, kdy existuje stejný počet menších (nebo stejných) a stejný počet větších (nebo stejných) hodnot. Při sudém počtu hodnot se medián definuje jako aritmetický průměr z nejvyšší hodnoty dolní poloviny a nejnižší hodnoty horní poloviny hodnot uspořádaných podle velikosti. Takto funguje např. statistická funkce =MEDIAN(číslo1;číslo2;...) v Excelu. Lze se setkat též s definicí mediánu coby 50% kvantilu. V tom případě je medián největší hodnotou v dolní polovině uspořádaných hodnot. Aritmetický průměr (zkráceně: průměr) obdržíme jako součet jednotlivých výsledků měření nebo zjišťování vydělený celkovým počtem výsledků. Rozlišujeme přitom aritmetický průměr z celého souboru údajů, nebo jen z určitého vzorku - výběru. Ten první nazýváme populačním průměrem a označujeme jej řeckým písmenem , pro ten druhý používáme označení x a nazýváme jej výběrovým průměrem. Zda se jedná o výběrový nebo populační průměr, závisí na konkrétní situaci. Matematické vyjádření je následující: populační průměr   výběrový průměr

x

N

1 N

x

1 n

x

i

,

i

.

i 1 n

i 1

Přitom N představuje počet údajů celého souboru, n představuje počet údajů z příslušného výběru. K výpočtu aritmetického průměru se používá funkce =PRŮMĚR(číslo1;číslo2;…), která počítá pouze s číselnými údaji, ostatní údaje včetně prázdných buněk ignoruje. Aritmetický průměr dává stejnou důležitost (váhu) každému z údajů, avšak údaje někdy stejnou důležitost nemají. Proto je v těchto případech vhodné použít vážený aritmetický průměr pomocí vah wi . V Excelu není k dispozici speciální funkce pro výpočet váženého aritmetického průměru, k výpočtu je třeba napsat vhodný vzorec. vážený aritmetický průměr

xw 

n

1 n

w

w x

i i

.

i 1

i

i 1

V ekonomické oblasti se často počítá s různými indexy, např. cenovými. Pro výpočet průměrného indexu za určité období se používá geometrický průměr, který se vypočítá jako n -tá odmocnina ze součinu kladných hodnot x1.x2  xn : geometrický průměr xg  n x1.x2  xn . K výpočtu geometrického průměru se používá funkce =GEOMEAN(číslo1;číslo2;…). Rozptyl je aritmetickým průměrem kvadrátů odchylek od aritmetického průměru. Podle toho, zda se jedná o rozptyl z celého souboru – celé populace, nebo jen rozptyl z jistého vzorku – výběru z této populace, rozlišujeme populační rozptyl, kterému říkáme jednoduše rozptyl, značíme  2 , a výběrový rozptyl, označujeme jej s 2 : Vzorce vypadají následovně: 1 N (populační) rozptyl  2   ( xi   ) 2 , v Excelu funkce = VAR(číslo1;číslo2;...), N i1 1 n ( xi  x ) 2 , v Excelu = VAR.VÝBĚR( (číslo1;číslo2;...). výběrový rozptyl s 2   n  1 i1 Číslo n-1 se nazývá počet stupňů volnosti. Směrodatná odchylka je druhou odmocninou z rozptylu. Ve shodě s předchozí terminologií rozlišujeme populační směrodatnou odchylku, označujeme ji , které říkáme prostě směrodatná odchylka, a výběrovou směrodatnou odchylku, která je odmocninou z výběrového rozptylu, označujeme ji s. V Excelu lze vypočítat populační směrodatnou

- 13 -


odchylku pomocí funkce =SMODCH(číslo1;číslo2;...) a výběrovou směrodatnou odchylku pomocí funkce =SMODCH.VÝBĚR(číslo1;číslo2;...). Šikmost je charakteristikou, popisující symetrii pravděpodobnostního rozdělení vzhledem k aritmetickému průměru, v Excelu se používá funkce = SKEW(číslo1;číslo2;...). Nulová šikmost značí, že hodnoty souboru jsou rovnoměrně rozděleny vlevo a vpravo od průměru. Kladná šikmost značí, že vpravo od průměru se vyskytují odlehlejší hodnoty nežli vlevo a většina hodnot se nachází vlevo od průměru. U záporné šikmosti je tomu naopak. Špičatost je charakteristika rozdělení hodnot souboru, která porovnává dané rozdělení s tzv. normálním rozdělením. V Excelu se pro výpočet špičatosti používá funkce =KURT(číslo1;číslo2;...). Hodnoty s tzv. normovaným normálním rozdělením (které má průměr roven nule a směrodatnou odchylku rovnu jedné) mají koeficient špičatosti roven nule. Rozdělení s kladným koeficientem jsou špičatější než normované normální rozdělení, tedy hodnoty jsou více koncentrovány v blízkosti průměru. Naopak rozdělení se záporným koeficientem šikmosti jsou plošší než normované normální rozdělení. ŘEŠENÝ PŘÍKLAD 1.2 Následující tabulka obsahuje počty bodů, které získali jednotliví studenti z testu z mikroekonomie:

a) b) c) d) e) f) g) h) i) j)

48

62

78

56

74

23

2

48

99

100

59

25

23

36

70

0

24

36

48

23

52

38

47

23

88

78

67

68

20

11

57

37

23

59

3

23

11

17

78

67

Vypočítejte průměrný počet bodů. Nalezněte modus souboru. Vypočítejte medián souboru. Vypočítejte výběrový rozptyl souboru. Vypočítejte výběrovou směrodatnou odchylku souboru. Vypočítejte populační rozptyl. Vypočítejte populační směrodatnou odchylku souboru. Vypočítejte šikmost souboru. Špičatost souboru. Načrtněte histogram četnosti pro 5 tříd.

Řešení: Pomocí funkcí Excelu postupně dostaneme výsledky (Obr. 1.8):

- 14 -

Elena Mielcová, Radmila Stoklasová a Jaroslav Ramík; Statistické programy Obrázek 1.8


________________________________________________________________________ Statistické funkce nejsou jedinou možností, kterou Excel v souvislosti s popisnou statistikou nabízí. Tabulkový procesor MS Excel umožňuje výpočet celého souboru výběrových základních charakteristik přímo z dat pomocí položky hlavního menu záložky Data: Analýza dat (pozor, musí být nainstalována, viz text pod obrázkem 1.1), analytický nástroj Popisná statistika. Použití tohoto analytického nástroje demonstruje následující příklad: ŘEŠENÝ PŘÍKLAD 1.3 Následující tabulka (stejná jako v Příkladu 1.2) obsahuje počty bodů, které získali jednotliví studenti z testu z mikroekonomie: 48

62

78

56

74

23

2

48

99

100

59

25

23

36

70

0

24

36

48

23

52

38

47

23

88

78

67

68

20

11

57

37

23

59

3

23

11

17

78

67

Vypočítejte průměrný počet bodů, nalezněte modus souboru, vypočítejte medián souboru, vypočítejte výběrový rozptyl a směrodatnou odchylku souboru. Vypočítejte šikmost a špičatost souboru. Nalezněte maximální a minimální hodnotu v souboru.

- 15 -


Řešení: Pro výpočet pomocí analytického nástroje Popisná statistika je nutné připravit data do jednoho sloupce (nebo řádku), protože pro každý sloupec (případně řádek) se všechny hodnoty počítají zvlášť. Tato vlastnost je výhodná pro výpočet základních charakteristik dat pro několik souborů (sloupců nebo řádků dat) najednou. Po otevření nástroje Popisná statistika (Data  Analýza dat  Popisná statistika) lze zadat vstupní oblast dat, označit, zda jsou data ve sloupci nebo v řádku, zadat případné popisky a určit, že vyžadujeme celkový přehled (Obr. 1.9). Obrázek 1.9


Výsledná tabulka obsahuje všechny požadované informace s popisem (Obr. 1.10). Výsledky si můžete porovnat s řešením předchozího příkladu 1.2: Obrázek 1.10

Zdroj: Vlastní zpracování. ___________________________________________________________________________________

- 16 -


1.3 PŘÍKLADY K PROCVIČENÍ PŘÍKLAD 1.1 Následující tabulka z makroekonomie.

obsahuje

počty

bodů,

které

získali

12

13

11

18

4

11

1

14

19

20

12

2

4

12

20

0

14

16

18

19

studenti

z

testu

a) Vypočítejte optimální počet tříd pomocí Sturgersova pravidla. b) Zobrazte histogram četnosti pro počet tříd z příkladu a). ________________________________________________________________________ PŘÍKLAD 1.2 Zjistěte základní charakteristiky pro soubor dat z následující tabulky: 18

47

11

18

4

11

1

14

19

20

12

2

4

12

20

0

14

16

18

19

20

11

47

23

28

18

47

38

20

11

Vypočítejte průměrný počet bodů, nalezněte modus souboru, vypočítejte medián souboru, vypočítejte výběrový rozptyl a směrodatnou odchylku souboru. Vypočítejte šikmost a špičatost souboru. Nalezněte maximální a minimální hodnotu v souboru. Použijte analytický nástroj Popisná statistika. ___________________________________________________________________________ PŘÍKLAD 1.3 Pro data z následující tabulky určete výběrovou směrodatnou odchylku a populační směrodatnou odchylku a výsledky porovnejte. Která směrodatná odchylka je větší? 4

12

20

0

14

11

1

24

49

50

39

25

34

36

50

0

24

36

48

39

20

0

14

16

18

18

47

28

20

11

__________________________________________________________________________

1.4 ŘEŠENÍ PŘÍKLADŮ ŘEŠENÍ PŘÍKLADU 1.1 Optimální počet tříd: k  Round (3,3.log10 (20))  1  Round (3,3.1,301)  1  Round (4,293)  1  4  1  5 Histogram četnosti (Obr. 1.11):

- 17 -

1 Popisná statistika v programu MS Excel Obrázek 1.11


___________________________________________________________________________ ŘEŠENÍ PŘÍKLADU 1.2 Výsledná tabulka obsahuje všechny požadované informace s popisem (Obr. 1.12). Obrázek 1.12


___________________________________________________________________________ ŘEŠENÍ PŘÍKLADU 1.3 Výběrová směrodatná odchylka je 16,034 a populační směrodatná odchylka je 15,764. Větší je výběrová směrodatná odchylka. __________________________________________________________________________

- 18 -


1.5 PŘÍPADOVÉ STUDIE PŘÍPADOVÁ STUDIE 1.1 Při marketingové studii pro výrobce praček byli respondenti dotázáni, kolik let vlastní pračku, kterou mají doma. Odpovědi 100 respondentů jsou v následující tabulce: 2

3

2

5

5

6

7

2

12

1

1

2

3

4

3

5

6

8

8

8

15

17

2

3

2

2

1

1

2

3

5

6

7

7

8

19

6

7

0

1

2

2

4

6

7

12

12

13

10

9

20

12

11

9

9

8

6

5

2

2

3

4

2

2

5

6

7

9

8

9

1

2

3

4

2

2

3

4

4

5

2

2

3

9

9

19

13

12

12

5

0

10

12

10

7

6

5

4

3

2

Vypočítejte průměrný počet let vlastnictví pračky. Nalezněte modus souboru. Vypočítejte medián souboru. Vypočítejte výběrový rozptyl souboru. Vypočítejte výběrovou směrodatnou odchylku souboru. Vypočítejte populační rozptyl. Vypočítejte populační směrodatnou odchylku souboru. Vypočítejte šikmost a špičatost souboru. Pomocí Sturgersova pravidla určete optimální počet tříd a načrtněte histogram četnosti. j) Načrtněte histogram četnosti pro 10 tříd. a) b) c) d) e) f) g) h) i)

- 19 -


PŘÍPADOVÁ STUDIE 1.2 Při marketingové studii pro výrobce praček byli respondenti dále dotázáni, kolik let vlastnili pračku, kterou měli před nynější pračkou. Odpovědi 100 respondentů jsou v následující tabulce: 12

13

12

15

15

16

17

12

12

11

11

20

13

14

13

15

16

8

18

8

15

17

20

6

12

12

13

17

21

13

5

6

7

7

8

19

16

17

10

10

20

20

14

16

17

12

12

13

10

9

20

12

11

9

9

8

6

5

12

12

13

14

12

12

15

16

17

19

8

9

10

23

13

14

12

12

13

13

14

15

12

12

11

9

9

19

13

12

12

15

10

10

12

10

17

16

15

14

13

12

Vypočítejte průměrný počet let vlastnictví pračky. Nalezněte modus souboru. Vypočítejte medián souboru. Vypočítejte výběrový rozptyl souboru. Vypočítejte výběrovou směrodatnou odchylku souboru. Vypočítejte populační rozptyl. Vypočítejte populační směrodatnou odchylku souboru. Vypočítejte šikmost a špičatost souboru. Pomocí Sturgersova pravidla určete optimální počet tříd a načrtněte histogram četnosti. j) Načrtněte histogram četnosti pro 5 tříd. k) Porovnejte výsledky případové studie 1.1 a 1.2 a interpretujte rozdíly. a) b) c) d) e) f) g) h) i)

- 20 -

1 POPISNÁ STATISTIKA V PROGRAMU MS EXCEL

Recommend Documents