Slezská univerzita v Opavě Obchodně podnikatelská fakulta v Karviné
STATISTICKÉ PROGRAMY VYUŽITÍ EXCELU A SPSS PRO VĚDECKO - VÝZKUMNOU ČINNOST
Elena Mielcová, Radmila Stoklasová a Jaroslav Ramík
Karviná 2012 Projekt OP VK 2.2 (CZ.1.07/2.2.00/15.0176) „Rozvoj kompetencí managementu a pracovníků VŠ MSK (s podporou ICT)“
Obor:
Vzdělávání pedagogických pracovníků: Excel a SPSS pro vědecko-výzkumnou činnost.
Anotace:
Text „Statistické programy – využití Excelu a SPSS pro výzkumnou činnost“ a s ním související prezenční kurz slouží ke zkvalitnění výstupů vědecko-výzkumné a publikační činnosti akademických pracovníků SU OPF, kde se ve stále větší míře využívá kvantitativních metod a to i v oblasti obchodu a podnikání, v marketingu, managementu a financích. S využitím kvantitativních metod – tedy metod zpracovávajících informace v číselné i nečíselné podobě souvisí využití informačních technologií zahrnujících jak sběr dat, tak také jejich zpracování pomocí počítačů a příslušného software (SW). Nejčastěji se přitom využívá statistického SW a to zejména tabulkových kalkulátorů, z nichž daleko nejpopulárnější je Excel od firmy Microsoft. Významnou součástí funkcí Excelu tvoří jeho statistické funkce, kde základní funkce lze nalézt přímo v seznamu statistických funkcí, standardně je však dodáván i dodatek Excelu – Analýza dat, která soubor standardních funkcí významně rozšiřuje. Pro profesionální zpracování dat, kterým je i vědecko-výzkumná činnost na fakultě, však funkce Excelu často nestačí, je zapotřebí sofistikovanějších metod. Pro oblast sociálních věd, v tom i ekonomických věd, je velmi vhodný program SPSS (Statistical Package for Social Sciences) dodávaný firmou IBM. Tento text se v 1. části zabývá statistickými funkcemi Excelu, které ve 2. části rozšiřuje o možnosti použití statistického programu SPSS.
Klíčová slova:
testy hypotéz, regresní analýza, ANOVA, Excel, SPSS.
©
Slezská univerzita v Opavě Obchodně podnikatelská fakulta v Karviné
Autor:
Ing. Elena Mielcová, Ph.D., Mgr. Radmila Stoklasová, Ph.D., prof. RNDr. Jaroslav Ramík, CSc.
Editor a autor e-kurzu:
Ing. Robert Kempný
Recenzenti:
?doc. Ing. Jana Hančlová, CSc., ?doc. Ing. Pavlína Pellešová, Ph.D.
ISBN:
Doplní oddělení vědy a výzkumu.
OBSAH ÚVOD ........................................................................................................................................ 7 POPISNÁ STATISTIKA V PROGRAMU MS EXCEL ............................................. 9
1 1.1
HISTOGRAM ČETNOSTI ........................................................................................................... 10 ŘEŠENÝ PŘÍKLAD 1.1 ............................................................................................................... 10
1.2
ZÁKLADNÍ CHARAKTERISTIKY DAT ....................................................................................... 12 ŘEŠENÝ PŘÍKLAD 1.2 ............................................................................................................... 14 ŘEŠENÝ PŘÍKLAD 1.3 ............................................................................................................... 15
1.3
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 17 PŘÍKLAD 1.1 ............................................................................................................................ 17 PŘÍKLAD 1.2 ............................................................................................................................ 17 PŘÍKLAD 1.3 ............................................................................................................................ 17
1.4
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 17 ŘEŠENÍ PŘÍKLADU 1.1 .............................................................................................................. 17 ŘEŠENÍ PŘÍKLADU 1.2 .............................................................................................................. 18 ŘEŠENÍ PŘÍKLADU 1.3 .............................................................................................................. 18
1.5
PŘÍPADOVÉ STUDIE ................................................................................................................ 19 PŘÍPADOVÁ STUDIE 1.1 ........................................................................................................... 19 PŘÍPADOVÁ STUDIE 1.2 ........................................................................................................... 20
TESTOVÁNÍ HYPOTÉZ ............................................................................................ 21
2 2.1
PŘÍKLADY PARAMETRICKÝCH TESTŮ..................................................................................... 22 2.1.1 TEST STŘEDNÍ HODNOTY, KDYŽ JE ZNÁMÉ .......................................................... 22 ŘEŠENÝ PŘÍKLAD 2.1 ............................................................................................................... 22 2.1.2 TEST STŘEDNÍ HODNOTY, KDYŽ 2 NENÍ ZNÁMÉ ....................................................... 24 ŘEŠENÝ PŘÍKLAD 2.2 ............................................................................................................... 24 2.1.1 TEST PRO ROZPTYL NÁHODNÉ VELIČINY.................................................................... 25 ŘEŠENÝ PŘÍKLAD 2.3 ............................................................................................................... 25 2
2.2
NEPARAMETRICKÉ TESTY ...................................................................................................... 25 2.2.1 TEST DOBRÉ SHODY.................................................................................................. 26 ŘEŠENÝ PŘÍKLAD 2.4 ............................................................................................................... 26 2.2.2 TEST NEZÁVISLOSTI KVALITATIVNÍCH ZNAKŮ............................................................. 27 ŘEŠENÝ PŘÍKLAD 2.5 ............................................................................................................... 29 2.2.3 KONTINGENČNÍ TABULKY ......................................................................................... 30 ŘEŠENÝ PŘÍKLAD 2.6 ............................................................................................................... 31
2.3
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 35 PŘÍKLAD 2.1 ............................................................................................................................ 35 PŘÍKLAD 2.2 ............................................................................................................................ 35 PŘÍKLAD 2.3 ............................................................................................................................ 36
2.4
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 36 ŘEŠENÍ PŘÍKLADU 2.1 .............................................................................................................. 36 ŘEŠENÍ PŘÍKLADU 2.2 .............................................................................................................. 36 ŘEŠENÍ PŘÍKLADU 2.3 .............................................................................................................. 37
2.5
PŘÍPADOVÉ STUDIE ................................................................................................................ 37
-3-
PŘÍPADOVÁ STUDIE 2.1 ........................................................................................................... 37 PŘÍPADOVÁ STUDIE 2.1 ........................................................................................................... 38
ANALÝZA ROZPTYLU – ANOVA ........................................................................... 40
3 3.1
ANALÝZA ROZPTYLU S JEDNÍM FAKTOREM ........................................................................... 40 ŘEŠENÝ PŘÍKLAD 3.1 ............................................................................................................... 42
3.2
DVOUFAKTOROVÁ ANOVA .................................................................................................. 44 ŘEŠENÝ PŘÍKLAD 3.2 ............................................................................................................... 45
3.3
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 46 PŘÍKLAD 3.1 ............................................................................................................................ 46 PŘÍKLAD 3.2 ............................................................................................................................ 47 PŘÍKLAD 3.3 ............................................................................................................................ 47
3.4
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 47 ŘEŠENÍ PŘÍKLADU 3.1 .............................................................................................................. 47 ŘEŠENÍ PŘÍKLADU 3.2 .............................................................................................................. 48 ŘEŠENÍ PŘÍKLADU 3.3 .............................................................................................................. 48
3.5
PŘÍPADOVÉ STUDIE ................................................................................................................ 48 PŘÍPADOVÁ STUDIE 3.1 ........................................................................................................... 48 PŘÍPADOVÁ STUDIE 3.2 ........................................................................................................... 49
LINEÁRNÍ REGRESE ................................................................................................ 50
4 4.1
JEDNODUCHÁ LINEÁRNÍ REGRESE .......................................................................................... 50 ŘEŠENÝ PŘÍKLAD 4.1 ............................................................................................................... 51 ŘEŠENÝ PŘÍKLAD 4.2 ............................................................................................................... 53
4.2
VÍCENÁSOBNÁ LINEÁRNÍ REGRESE ........................................................................................ 55 ŘEŠENÝ PŘÍKLAD 4.3 ............................................................................................................... 56
4.3
VYROVNÁVÁNÍ ČASOVÝCH ŘAD ............................................................................................ 58 ŘEŠENÝ PŘÍKLAD 4.4 ............................................................................................................... 60
4.4
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 60 PŘÍKLAD 4.1 ............................................................................................................................ 60 PŘÍKLAD 4.2 ............................................................................................................................ 61 PŘÍKLAD 4.3 ............................................................................................................................ 61
4.5
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 61 ŘEŠENÍ PŘÍKLADU 4.1 .............................................................................................................. 61 ŘEŠENÍ PŘÍKLADU 4.2 .............................................................................................................. 62 ŘEŠENÍ PŘÍKLADU 4.3 .............................................................................................................. 62
4.6
PŘÍPADOVÉ STUDIE ................................................................................................................ 62 PŘÍPADOVÁ STUDIE 4.1 ........................................................................................................... 62 PŘÍPADOVÁ STUDIE 4.2 ........................................................................................................... 63
ÚVOD DO SPSS............................................................................................................ 64
5 5.1
PRÁCE S DATOVÝMI SOUBORY ............................................................................................... 65 5.1.1 5.1.2
NABÍDKA DATA .................................................................................................... 66 NABÍDKA TRANSFORM........................................................................................ 68
-4-
5.2
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 70 PŘÍKLAD 5.1 ............................................................................................................................ 70 PŘÍKLAD 5.2 ............................................................................................................................ 70 PŘÍKLAD 5.3 ............................................................................................................................ 71
5.3
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 71 ŘEŠENÍ PŘÍKLADU 5.1 ............................................................................................................ 71 ŘEŠENÍ PŘÍKLADU 5.2 ............................................................................................................ 71 ŘEŠENÍ PŘÍKLADU 5.3 ............................................................................................................ 72
TESTY HYPOTÉZ – NEPARAMETRICKÉ TESTY .............................................. 73
6 6.1 6.2 6.3
TESTY ZALOŽENÉ NA BINOMICKÉM ROZDĚLENÍ .................................................................... 74 TESTY PRO DVOUROZMĚRNOU KONTINGENČNÍ TABULKU ..................................................... 74 TESTY ZALOŽENÉ NA POŘADÍ................................................................................................. 75 6.3.1 6.3.2 6.3.3 6.3.4 6.3.5 6.3.6
6.4
TESTY ZALOŽENÉ NA STATISTIKÁCH KOLMOGOROVOVA-SMIRNOVOVA TYPU .................... 78 6.4.1 6.4.2
6.5
MANNŮV-WHITNEYŮV-WILCOXONŮV TEST PRO DVA NEZÁVISLÉ VÝBĚRY ................... 75 WALDŮV-WOLFOWITZŮV TEST ................................................................................. 76 KRUSKALŮV-WALLISŮV TEST PRO „K“ NEZÁVISLÝCH VÝBĚRŮ................................... 76 WILCOXONŮV TEST PRO DVA ZÁVISLÉ VÝBĚRY .......................................................... 77 FRIEDMANŮV TEST ................................................................................................... 77 KENDALLŮV TEST KONKORDANCE ............................................................................ 78 KOLMOGORŮV TEST DOBRÉ SHODY PRO JEDEN VÝBĚR.............................................. 78 KOLMOGORŮV-SMIRNOVŮV TEST PRO DVA NEZÁVISLÉ VÝBĚRY ................................. 79
ŘEŠENÉ PŘÍKLADY ................................................................................................................. 79 6.5.1 KOLMOGOROVŮV TEST PRO JEDEN VÝBĚR ................................................................ 79 ŘEŠENÝ PŘÍKLAD 6.1 ............................................................................................................... 80 6.5.2 SROVNÁNÍ DVOU NEZÁVISLÝCH VÝBĚRŮ .................................................................... 82 ŘEŠENÝ PŘÍKLAD 6.2 ............................................................................................................... 82 6.5.3 SROVNÁNÍ „K“ NEZÁVISLÝCH VÝBĚRŮ ..................................................................... 84 ŘEŠENÝ PŘÍKLAD 6.3 ............................................................................................................... 84 6.5.4 SROVNÁNÍ DVOU ZÁVISLÝCH VÝBĚRŮ ........................................................................ 86 ŘEŠENÝ PŘÍKLAD 6.4 ............................................................................................................... 86 6.5.5 SROVNÁNÍ „K“ ZÁVISLÝCH VÝBĚRŮ.......................................................................... 88 ŘEŠENÝ PŘÍKLAD 6.5 ............................................................................................................... 88
6.6
PŘÍKLADY K PROCVIČENÍ ....................................................................................................... 89 PŘÍKLAD 6.1 ............................................................................................................................ 89 PŘÍKLAD 6.2 ............................................................................................................................ 90 PŘÍKLAD 6.3 ............................................................................................................................ 90 PŘÍKLAD 6.4 ............................................................................................................................ 90
6.7
ŘEŠENÍ PŘÍKLADŮ .................................................................................................................. 91 ŘEŠENÍ ŘEŠENÍ ŘEŠENÍ ŘEŠENÍ
6.8
PŘÍKLADU PŘÍKLADU PŘÍKLADU PŘÍKLADU
6.1 ............................................................................................................ 91 6.2 ............................................................................................................ 91 6.3 ............................................................................................................ 91 6.4 ............................................................................................................ 93
PŘÍPADOVÉ STUDIE ................................................................................................................ 94 PŘÍPADOVÁ STUDIE 6.1 ........................................................................................................... 94 PŘÍPADOVÁ STUDIE 6.2 ........................................................................................................... 94
-5-
ANALÝZA ČASOVÝCH ŘAD – TRENDOVÁ SLOŽKA....................................... 95
7 7.1 7.2
DEKOMPOZICE ČASOVÉ ŘADY ................................................................................................ 95 LINEÁRNÍ TREND .................................................................................................................... 96 ŘEŠENÝ PŘÍKLAD 7.1 ............................................................................................................... 98 ŘEŠENÝ PŘÍKLAD 7.2 ............................................................................................................. 101
7.3
KVADRATICKÝ TREND ......................................................................................................... 103 ŘEŠENÝ PŘÍKLAD 7.3 ............................................................................................................. 103
7.4
EXPONENCIÁLNÍ TREND ....................................................................................................... 105 ŘEŠENÝ PŘÍKLAD 7.4 ............................................................................................................. 106
7.5
LOGISTICKÝ TREND .............................................................................................................. 108 ŘEŠENÝ PŘÍKLAD 7.5 ............................................................................................................. 109
7.6
PŘÍKLADY K PROCVIČENÍ ..................................................................................................... 111 PŘÍKLAD 7.1 .......................................................................................................................... 111 PŘÍKLAD 7.2 .......................................................................................................................... 112 PŘÍKLAD 7.3 .......................................................................................................................... 112 PŘÍKLAD 7.4 .......................................................................................................................... 112
7.7
ŘEŠENÍ PŘÍKLADŮ ................................................................................................................ 112 ŘEŠENÍ PŘÍKLADU 7.1 ........................................................................................................... 112 ŘEŠENÍ PŘÍKLADU 7.2 ........................................................................................................... 112 ŘEŠENÍ PŘÍKLADU 7.3 ........................................................................................................... 113 ŘEŠENÍ PŘÍKLADU 7.4 ............................................................................................................ 113
7.8
PŘÍPADOVÉ STUDIE .............................................................................................................. 113 PŘÍPADOVÁ STUDIE 7.1 ......................................................................................................... 113 PŘÍPADOVÁ STUDIE 7.2 ......................................................................................................... 113
ANALÝZA ČASOVÝCH ŘAD – SEZÓNNÍ SLOŽKA ......................................... 114
8
ŘEŠENÝ PŘÍKLAD 8.1 ............................................................................................................. 116 ŘEŠENÝ PŘÍKLAD 8.2 ............................................................................................................. 119 8.1
PŘÍKLADY K PROCVIČENÍ ..................................................................................................... 121 PŘÍKLAD 8.1 .......................................................................................................................... 121 PŘÍKLAD 8.2 .......................................................................................................................... 121 PŘÍKLAD 8.3 .......................................................................................................................... 122 PŘÍKLAD 8.4 .......................................................................................................................... 122 PŘÍKLAD 8.5 .......................................................................................................................... 122
8.2
ŘEŠENÍ PŘÍKLADŮ ................................................................................................................ 123 ŘEŠENÍ PŘÍKLADU 8.1 ........................................................................................................... 123 ŘEŠENÍ PŘÍKLADU 8.2 ........................................................................................................... 123 ŘEŠENÍ PŘÍKLADU 8.3 ........................................................................................................... 123 ŘEŠENÍ PŘÍKLADU 8.4 ............................................................................................................ 123 ŘEŠENÍ PŘÍKLADU 8.5 ............................................................................................................ 123
8.3
PŘÍPADOVÉ STUDIE .............................................................................................................. 124 PŘÍPADOVÁ STUDIE 8.1 ......................................................................................................... 124
ZÁVĚR .................................................................................................................................. 125 SEZNAM POUŽITÉ LITERATURY ................................................................................ 126
-6-
ÚVOD Tento text nazvaný „Statistické programy – využití Excelu a SPSS pro výzkumnou činnost“ je určen především vědecko-pedagogickým pracovníkům Slezské univerzity v Opavě, Obchodně podnikatelské fakulty v Karviné. Text a s ním související prezenční kurz by jim měl být nápomocen ke zkvalitnění výstupů jejich vedecko-výzkumné a publikační činnosti, kde se ve stále větší míře využívá kvantitativních metod a to i v oblasti obchodu a podnikání, v marketingu, managementu, nemluvě o oblasti financí, kde je to již dlouhodobý trend. S využitím kvantitativních metod – tedy metod zpracovávajících informace v číselné i nečíselné podobě - souvisí využití informačních technologií zahrnujících jak sběr dat, tak také jejich zpracování pomocí počítačů a příslušného software (SW). Nejčastěji se přitom využívá statistického SW a to zejména tabulkových kalkulátorů, z nichž daleko nejpopulárnější je Excel od firmy Microsoft. Ten je dnes při nákupu standardně dodáván s osobním počítačem PC spolu s operačním systémem Windows. Významnou součástí funkcí Excelu tvoří jeho statistické funkce, kde základní funkce lze nalézt přímo v seznamu statistických funkcí, standardně je však dodáván i dodatek Excelu – Analýza dat, která soubor standardních funkcí významně rozšiřuje. Pro profesionální zpracování dat, kterým je i vědecko-výzkumná činnost na fakultě, však funkce Excelu často nestačí, je zapotřebí sofistikovanějších metod. Pro oblast sociálních věd, v tom i ekonomických věd, je velmi vhodný program SPSS (Statistical Package for Social Sciences) dodávaný firmou IBM. V tomto textu se proto v 1. části zabýváme statistickými funkcemi Excelu, které ve 2. části rozšiřujeme o možnosti použití statistického programu SPSS. Celý text má 8 kapitol, je rozdělen do dvou částí, každá část obsahuje 4 kapitoly. První část je věnována statistickým funkcím v Excelu: 1. kapitola nazvaná Popisná statistika v programu Excel přináší základní statistické funkce a jejich aplikace. Ve 2. kapitole Testování hypotéz je nejprve stručně vysvětlena problematika testování statistických hypotéz a poté popsány statistické testy – parametrické i neparametrické, které Excel nabízí a to na konkrétních příkladech. 3. kapitola je věnována metodě analýzy rozptylu (ANOVA) a možnostem aplikace této metody (jednofaktorové i dvoufaktorové) pomocí Excelu. Lineární regresi – nejčastěji uplatňované metodě identifikace závislosti statistických proměnných – je věnována 4. kapitola. Nejprve se vysvětluje jednoduchý lineární regresní model s jednou vysvětlující a vysvětlovanou proměnnou, poté se problematika rozšiřuje na vícerozměrný regresní model s několika vysvětlujícími proměnnými. Vše je prezentováno s ohledem na funkce Excelu, konkrétně na excelovskou funkci „Vložit spojnici trendu“ a doplněk Analýza dat, Lineární regrese. Druhá část této kapitoly se zabývá metodami analýzy časových řad jmenovitě jejich vyrovnáním pomocí klouzavých průměru a exponenciálního vyrovnání. Druhá část textu je zaměřena na využití vybraných statistických metod z programu SPSS. Nejprve se v 5. kapitole věnujeme základům práce s tímto programem zejména práci s datovými soubory. 6. kapitola je zaměřena na neparametrické testy statistických hypotéz, které jsou sice stručně pojednány již v kapitole 2, zde se však podrobněji seznámíte s celou řadou neparametrických testů, které jsou pak demonstrovány na konkrétních příkladových typech, z oblastí, jaké se mohou vyskytovat ve výzkumné práci. Poslední dvě kapitoly jsou zaměřeny na problematiku analýzy časových řad, která je v ekonomické oblasti jednou z nejvíce frekventovaných. 7. kapitola s názvem „Analýza časových řad – trendová složka“ vychází ze základní dekompozice časových řad na trendovou, periodickou a náhodnou složku. V kapitole jsou demonstrovány různé případy analýzy trendové složky časové řady, které se vyskytují v časových řadách ekonomického zaměření: lineární trend, kvadratický trend, exponenciální a logistický trend. Poslední – 8. kapitola - s názvem „Analýza časových řad –
-7-
sezónní složka“ se zabývá problematikou pravidelné kolísavosti časových řad, která je modelována sezónní složkou. Jsou zde uvedeny metody sezónní dekompozice které slouží k identifikaci sezónní složky a prognózování hodnot časové řady do budoucna. Každá kapitola je pojata jako tematický blok zahrnující jedinou jednotku prezenčního kurzu. Nejprve jsou v textu prezentována teoretická východiska tématu včetně základních vzorců podle nichž jsou prováděny výpočty, poté následuje aplikační část kapitoly. Ta je tvořena několika řešenými číselnými příklady s využitím počítače a SW (tj. Excelu nebo SPSS), kdy je krok po kroku uveden postup řešení využívající systém menu daného programu. Pak následuje zadání několika příkladů, které nejsou bezprostředně následovány jejich řešením, slouží k samostatné práci a zvládnutí probírané látky při domácím studiu účastníka kurzu. Pro kontrolu pak slouží zkrácené postupy řešení s výsledky řešení uváděné v následující části. Kapitola je zakončena aktivitou nazvanou „případová studie“, která představuje komplexní úkol zahrnující několik dílčích příkladů. Tato aktivita simulující dílčí výzkumný problém slouží k dalšímu prohloubení porozumění probrané látky a získání výpočetních dovedností při využití SW. Postup ani výsledky případové studie nejsou v textu uvedeny, účastníci mají na vyřešení případové studie časový prostor mezi dvěma prezenčními soustředěními kurzu. Předpokládá se, že případové studii bude věnována část následujícího prezenčního soustředění. Jak jsme uvedli již na začátku, text by měl sloužit vědecko-pedagogickým pracovníkům při zkvalitnění jejich vědecko-výzkumné práce, která tvoří imanentní součást činnosti každého akademického pracovníka na vysoké škole. Může však být přínosem i pro studenty zejména doktorského studia, kteří začínají svoji vědecko-výzkumnou kariéru zpracováním svojí doktorské práce, která je rovněž považována za jejich první vědecko-výzkumnou práci.
-8-
