Kehl-Sipos: Excel parancsfájlok

Excelparancsfájlok.docx

Excel parancsfájlok.zip

Programozott idősorkutatás.zip

SABL.zip

OSZK-MEK. Kehl Dániel-Sipos Béla. Excel parancsfájlok felhasználása a statisztikai elemzésekben : Oktatási segédlet. Gyakorlati alkalmazások.

Kehl Dániel – Dr. Sipos Béla: Excel parancsfájlok felhasználása a statisztikai elemzésekben. (Oktatási segédlet)

A valós méretű statisztikai modellek megoldása kézi számításokkal általában nem, vagy csak nehezen végezhető el, a számítógépes feldolgozás lehetősége azonban új utakat nyitott meg a statisztika tudományában is. Napjainkban a számolási igény – a személyi számítógépek megjelenése és elterjedése miatt – már nem jelent különösebb akadályt, a számítások megkönnyítésére több matematikai-statisztikai és ökonometriai szoftvert is megalkottak. Ezeknek a programoknak az oktatás és a gyakorlati felhasználás szempontjából azonban több hiányossága is van. Az eladásra szánt programcsomagok[1] általában „fekete dobozként” működnek, azaz a felhasználó nem látja, azt, hogy mi történik a háttérben, a bevitt input és az értelmezendő output jelenik meg csupán. A hivatkozott, legtöbbször az Amerikai Egyesült Államokban kiadott szakkönyvek a hallgatók számára nehezen beszerezhetőek és drágák. Az ilyen szoftverekkel kapcsolatos további gond az is, hogy folyamatosan újabb verzióik jelennek meg, ami széleskörű alkalmazásuk lehetőségét megnehezíti. Drágítja a felhasználásukat továbbá, hogy az éves licencdíj kifizetésén túl a gépszám függvényében gyakorta külön díjat kell fizetni. A felsőoktatásban sok esetben a szoftverek csak az egyetemi/főiskolai számítógépeken érhetőek el, a hallgatók otthoni számítógépükre legálisan nem telepíthetik azokat. A különböző szoftverek emellett különböző felhasználói felülettel rendelkeznek. A „preferált” csomag kiválasztása így meglehetősen önkényes. A különböző formátumok miatt a programcsomagok közötti váltás némely esetben gondokat okoz. Az interneten található, ingyenesen letölthető ökonometriai programcsomagok, mint például az egyik legismertebb és legelterjedtebb gretl (Gnu Regression, Econometrics and Time-series Library, http://gretl.sourceforge.net/), igen sokoldalú szolgáltatást nyújtanak, de az elméleti háttér feldolgozásához a megadott angol nyelvű szakirodalmat1 is be kell szerezni és el kell sajátítani. A jelenleg legnépszerűbb irodai programcsomag a Microsoft Office Windows változata 1990-ben jelent meg. A Microsoft Office és ezen belül az MS Excel[2] világviszonylatban és Magyarországon is széleskörűen alkalmazott szoftver. Egyrészt ez a tény indokolja az MS Excel (továbbiakban Excel) alkalmazását, továbbá az is, hogy az előzőekben ismertetett problémákat részben ki lehet küszöbölni. Az Excel sok statisztikai műveletet képes elvégezni, de az alapfunkciók segítségével felépíthetők a bonyolultabb statisztikai és ökonometriai módszerek is a függvények segítségével. További előny, hogy a módszerek, a felhasznált képletek megjelennek, azok alakíthatók, az adott feladat megoldásához testre szabhatók, láthatóvá, és megérthetővé válnak a részeredmények és a mellékszámítások. Az Excel – a speciális statisztikai szoftverekhez hasonlóan, de messze nem olyan részletességgel – a statisztika módszertanának nagy részét felöleli beépített modulja (Analysis ToolPak) segítségével. Az Excel kétségtelen és messze legfontosabb előnye, hogy az Office csomag elterjedése miatt szinte mindenhol megtalálható. Általános elérhetősége egyben azt is jelenti, hogy akár mikro- és kisvállalatok – amelyek a drága, és folyamatosan friss verziókkal jelentkező szoftvereket nem képesek megvásárolni – elemzési eszköztárát is erősítheti. Ismereteink szerint csak az Excel alapszolgáltatásainak használata terjedt el az oktatásban és az üzleti életben Magyarországon, pedig – mint arról már szó esett – az Excel ennél többre képes, lehet batch file-okat, kötegelt parancsállományokat (a továbbiakban parancsfájlokat, illetve programokat) készíteni. Internetes keresés, és a rendelkezésünkre álló szakkönyvek feldolgozása alapján megállapítottuk, hogy az USA-ban igen elterjedtek a parancsfájlok, bár legtöbbször csak korlátozott szolgáltatásokat nyújtanak. A további szolgáltatásokat külön meg kell fizetni, azokat a könyvekhez mellékelt CD-k nem tartalmazzák. Rátérve az alkalmazási lehetőségekre, véleményünk szerint az adatelemzés öt szintje oldható meg az Excellel: Az első szint az, amikor a Függvény beszúrása varázslót (ikont) használjuk, tehát beépített statisztikai, matematikai és trigonometriai, mátrix, adatbázis, stb. függvényeket alkalmazunk. A második szint, amikor az Eszközök - Adatelemzés menüpont szolgáltatásait (pl. korrelációanalízis, regresszió) használjuk. A harmadik szint, amikor magunk írunk konkrét adatsorhoz vagy adatsorokhoz képleteket, mivel nem minden feladathoz áll rendelkezésre megírt függvény. A negyedik szint az, amikor parancsfájlokat készítünk – vagyis a harmadik szintet általánosítjuk – aminek felhasználásával az általunk megadott adatbázis terjedelméig (ez az adatbázisok sajátosságainak függvényében 25 - 10000 megfigyelés) új adatbázisok felhasználásával korlátlan számban számításokat végezhetünk a programozott képletek, illetve függvények alkalmazásával. Gyakran igen sok számítást kell elvégezni. Eben az esetben az idővel való takarékos gazdálkodás a cél, mert gyakran a harmadik szintnél egy feladatsor számításainak elvégzése több óra, vagy több nap, amit a parancsfájlok felhasználásával egy perc alatt el lehet végezni. Az ötödik szint az, amikor a feladat a hagyományos módon nem oldható meg. Erre példa a CES termelési függvény, ahol a változók száma több mint a rendelkezésre álló egyenletek száma. A feladat a legjobban illeszkedő függvény paramétereinek a megkeresése. A logisztikus és egyéb speciális trendfüggvények esetében a függvényeket nem lehet lineárisra transzformálni, a cél megkeresni azokat a paramétereket, amelyek mellett az illesztés a legpontosabb. A logisztikus regressziós függvények sem linearizálhatók, de iterációs eljárással, a paraméterek változtatásával a paraméterek becsülhetők, meghatározható egy olyan függvény, ahol a többszörös determinációs együttható a legnagyobb. Az Excel a Visual Basic for Applications (VBA) felhasználásával programozható, így ezek a feladatok egy iterációs eljárással megoldhatók. A negyedik és ötödik szint további előnye az, hogy szakértői értékelésre is felhasználhatók, vagyis javaslatot lehet tenni a különböző modellek elfogadására vagy elutasítására.

A munkalapokat egységes szerkezetben építettük fel. A változtatható, illetve megadható vagy megadandó adatokat sárga mezők jelölik, az eredményeket pedig egységes struktúrában, illetve szóhasználattal kívántuk megjeleníteni. A megértéshez szükséges végeredmények, és az egyes cellák számításához használt képletek valamennyi cella esetén láthatóak. Természetesen a képletek, függvények olvasásához alapvető táblázatkezelési ismeretek elengedhetetlenek, ezzel a számítás menete követhetővé válik. Szintén nagyon fontos, hogy egyetlen cella, vagy vezérlőelem (Checkbox, legördülő menü stb.) megváltoztatása az eredmények azonnali változását vonja maga után, és mindezt – hála a gyors számítási sebességnek – azonnal elérhetjük. Az általunk írt oktatási segédlet „felhasználóbarát” stílusban íródott, csak annyi matematikai képletet tartalmaz, ami az Excel parancsfájlok megértéshez és a feldolgozáshoz, az eredmények értelmezéséhez feltétlenül szükséges és széleskörű hazai és nemzetközi adatbázist dolgoz fel, ami a szakmai megértést elősegíti. Az oktatási segédlet függelékében felhívjuk a figyelmet arra és bemutatjuk, hogy hogyan lehet a feldolgozott adatsorokat az interneten megkeresni és letölteni. Oktatási segédletünkben azokat az Excel parancsfájlokat mutatjuk be, melyek elkészítését feladatul tűztük ki, és amelyek felölelik a statisztika illetve ökonometria három fontos területét; 1. egyszerű elemzések: viszonyszámok számítása, grafikonok készítése, empirikus eloszlások elemzése és elemi statisztikai műveletek; 2. dekompoziciós és sztochasztikus idősorelemzés fontosabb statisztikai módszerei; 3; korreláció- és regressziószámítás, sztochasztikus kapcsolatok elemzése és egyes speciális alkalmazások: pl. késleltetett regressziós modellek, CES-függvények, logisztikus regressziós függvények. Az Excel alkalmazásának témájával bővebben foglalkozó kézikönyv (Kehl Dániel – Dr. Sipos Béla: Excel parancsfájlok felhasználása a statisztikai elemzésekben) és az Excel parancsfájlok két internetes helyről letölthetőek:

https://ktk.pte.hu/hu/gyakorlati-alkalmazasok

Ez a Pécsi Tudományegyetem Közgazdaságtudományi Kar honlapja. Gyakorlati alkalmazások.

Felhőszolgáltatás. 50 GB. Sipos Béla. Cloud Drive.

Excelparancsfájlok.zip

Tartalmazza a kézikönyvet és az összes parancsfájlt.

SABL.zip

A SABL nem Excel parancsfájl.

A tartalomjegyzék.

ELŐSZÓ 4

BEVEZETÉS, AZ EXCEL BEÁLLÍTÁSAI, AZ EXCEL PARANCSFÁJLOK HASZNÁLATA SORÁN 7

1 EGYSZERŰ ADAT-ELEMZÉSEK: VISZONYSZÁMOK SZÁMÍTÁSA ÉS GRAFIKUS ÁBRÁZOLÁS 14

1.1 A DINAMIKUS VISZONYSZÁMOK PARANCSFÁJL MŰKÖDÉSE 17

GYAKORLÓ FELADATOK. (DINAMIKUS VISZONYSZÁMOK.XLS) 18

1.2 ÁBRÁK KÉSZÍTÉSE EXCEL PARANCSFÁJL MŰKÖDÉSE 19

GYAKORLÓ FELADATOK. (ÁBRÁK KÉSZITÉSE.XLS)27F 19

1.3 AZ ORSZÁGONKÉNTI KORFA PROGNÓZIS KÉSZÍTÉSE 2050-IG EXCEL PARANCSFÁJL MŰKÖDÉSE 21

GYAKORLÓ FELADATOK. (ORSZÁGONKÉNTI KORFA PROGNÓZIS KÉSZÍTÉSE 2050-IG.XLS) 22

1.4 NEMZETKÖZI ÖSSZEHASONLÍTÁSOK EXCEL PARANCSFÁJLOK FELHASZNÁLÁSÁVAL 22

GYAKORLÓ FELADATOK. (NEMZETKÖZI ÖSSZEHASONLÍTÁSOK EXCEL PARANCSFÁJLOK) 24

2 ELEMI MŰVELETEK A VÁLTOZÓKKAL ÉS EMPIRIKUS ELOSZLÁSOK ELEMZÉSE 24

2.1 SZÁMLÁLÁS, RANGSOROLÁS, ÖSSZEGZÉS 25

2.2 KÖZÉPÉRTÉKEK ÉS KVANTILISEK 25

2.3 SZÓRÓDÁSI MÉRŐSZÁMOK 27

2.4 AZ ELEMI MŰVELETEK PARANCSFÁJL MŰKÖDÉSE 27

2.5 EMPIRIKUS ELOSZLÁSOK ELEMZÉSE EXCEL PARANCSFÁJL MŰKÖDÉSE 29

GYAKORLÓ FELADATOK. (ELEMIMŰVELETEK.XLS ÉS EMPIRIKUSELOSZLÁSOKELEMZÉSE.XLS) 37

3 AZ IDŐSOROK ELEMZÉSI MÓDSZEREI 38

3.1 A DEKOMPOZÍCIÓS IDŐSORMODELLEK 39

3.1.1 AZ IDŐSOROK ÖSSZETEVŐI ÉS KAPCSOLÓDÁSI MÓDJAI 39

3.1.2 A TREND VAGY A HOSSZÚ TÁVÚ ALAPIRÁNYZAT BECSLÉSI MÓDSZEREI 41

3.1.3 A SZABÁLYOS RÖVID TÁVÚ (SZEZONÁLIS) INGADOZÁS 51

3.1.4 A CIKLIKUS (PERIODIKUS) MOZGÁS MODELLEZÉSE 52

3.2 AZ ELŐREJELZÉSEK HIBÁINAK A MÉRÉSE85F (A HIBAKÉPLETEK EXCEL PARANCSFÁJL MŰKÖDÉSE) 60

3.3 TRENDSZEZON-HIBASZÁMÍTÁS PARANCSFÁJL MŰKÖDÉSE 61

GYAKORLÓ FELADATOK. KONJUNKTÚRA CIKLUSOK MODELLEZÉSE, A TRENDSZEZON - HIBASZÁMÍTÁS EXCEL PARANCSFÁJL MŰKÖDÉSE) 63

3.4 A TELÍTŐDÉSI, A LOGISZTIKUS (S-ALAKÚ)- ÉS ÉLETGÖRBE TRENDFÜGGVÉNYEK BECSLÉSE EXCEL PARANCSFÁJLLAL 64

3.4.1 INFLEXIÓS PONTTAL NEM RENDELKEZŐ TELÍTŐDÉSI GÖRBÉK 65

3.4.2 EGY INFLEXIÓS PONTTAL RENDELKEZŐ TRENDFÜGGVÉNYEK 66

3.4.3 KÉT INFLEXIÓS PONTTAL RENDELKEZŐ TRENDFÜGGVÉNYEK 75

3.4.4 A LOGISZTIKUS TRENDEK BECSLÉSE EXCEL PARANCSFÁJL MŰKÖDÉSE 78

GYAKORLÓ FELADATOK. DEKOMPOZÍCIÓS IDŐSORMODELLEK 79

3.5 NAIV ELŐREJELZÉSI TECHNIKÁK. (A NAIVMÓDSZER-PARANCSFÁJL MŰKÖDÉSE.) 81

GYAKORLÓ FELADATOK. (NAIVMODSZER.XLS) 85

3.6 AZ EXPONENCIÁLIS KIEGYENLÍTÉS MÓDSZERE 13 (EXPONENCIÁLISSIMITÁS.XLS ÉS EXPS FOR WINDOWS) 85

3.6.1 AZ EXPONENCIÁLISSIMITÁS.XLS PARANCSFÁJL MŰKÖDÉSE. 85

GYAKORLÓ FELADATOK. (EXPONENCIÁLISSIMITÁS.XLS) 86

3.6.2 AZ EXPS FOR WINDOWS SZOFTVER MŰKÖDÉSE 87

3.7 A SABL-MÓDSZER (SZOFTVER) FELHASZNÁLÁSA ADATELŐKÉSZÍTÉSRE, A TREND ÉS A PERIODIKUS HULLÁMZÁS SZÉTVÁLASZTÁSÁRA 99

GYAKORLÓ FELADATOK A SABL-SZOFTVER ALKALMAZÁSÁRA 106

3.8 AZ ARIMA MODELLEZÉS MENETE 107

3.8.1 AZ ARIMA MODELLEZÉS LÉPÉSEI. 110

3.8.2 AZ ARIMA MODELL AZONOSÍTÁSA 123

3.8.3 AZ ARIMA MODELLEK BECSLÉSE 125

3.8.4 EXCEL-PARANCSFÁJLOK AZ ARIMA MODELLEZÉS TÉMAKÖRÉBŐL 128

3.8.5 SPEKTRÁLANALIZIS.XLS PARANCSFÁJL MŰKÖDÉSE. 142

3.8.5 25 IDŐSORKUTATÁSI MODELL BECSLÉSE.XLS EXCEL PARANCSFÁJL MŰKÖDÉSE. 144

3.8.6 R+ INTERNETEN ELÉRHETŐ: FREE STATISTICS SOFTWARE (CALCULATOR) 150

A KORRELÁCIÓ- ÉS REGRESSZIÓSZÁMÍTÁS 154

4.1 A REGRESSZIÓ.XLS PARANCSFÁJL MŰKÖDÉSE154F 156

4.1.1 AZ ADAT MUNKALAP 157

4.1.2 A MÁTRIX MUNKALAP 161

4.1.3 A MARADÉK MUNKALAP 163

4.1.4 A MULTIKOLLINEARITÁS MUNKALAP 164

4.1.5 AZ AUTOKORRELÁCIÓ MUNKALAP 171

4.1.6 A HOMOSZKEDASZTICITÁS MUNKALAP 173

4.2 GYAKORLATI ALKALMAZÁSOK BEMUTATÁSA IDŐSOROS ÉS KERESZTMETSZETI ADATOK ALAPJÁN 175

4.3 COCHRANE-ORCUTT ITERÁCIÓS ELJÁRÁS, A COTRANSZFORMÁCIÓ.XLS PARANCSFÁJL MŰKÖDÉSE 183

4.4 A SZROETER-HARRISON-KING-FÉLE PRÓBA. (SZROETERTESZ.XLS PARANCSFÁJ MŰKÖDÉSE) ÉS A GOLDFELD-QUANDT-PRÓBA (GOLDFELD-QUANDT-PRÓBA.XLS PARANCSFÁJ MŰKÖDÉSE) 184

4.4.1 A SZROETER-HARRISON-KING-FÉLE PRÓBA 184

4.4.2 A GOLDFELD-QUANDT-PRÓBA 188

4.5 A REGRESSZIÓS EGYÜTTHATÓK ÖSSZEFÜGGÉSEI (AZ ÚTELEMZÉS) 191

4.6 KÉSLELTETETT REGRESSZIÓS MODELLEK. (KÉSLELTETETTMÁTRIX.XLS PARANCSFÁJL MŰKÖDÉSE) 192

4.6.1 A KÉSLELTETÉS MODELLJEINEK RÖVID TÖRTÉNETE 192

4.6.2 A FORDÍTOTT V-KÉSLELTETÉSŰ MODELLEK. 194

4.6.3 KOYCK MÓDSZEREI 196

4.6.4 ALMON-FÉLE POLINOM ELOSZLÁSÚ OSZTOTT KÉSLELTETÉSŰ MODELLEK 200

4.7 A HATVÁNYKITEVŐS, COBB-DOUGLAS TERMELÉSI FÜGGVÉNY (A TERMELÉSI FÜGGVÉNY ÁTLAG ÉS HATÁRMUTATÓI.XLS PARANCSFÁJL MŰKÖDÉSE) 201

GYAKORLÓ FELADATOK: C-D-TERMELÉSI FÜGGVÉNY ÁTLAG ÉS HATÁRMUTATÓI EXCEL PARANCSFÁJL.XLS ALKALMAZÁSA. NEM LINEÁRIS, DE LINEARIZÁLHATÓ REGRESSZIÓS FÜGGVÉNYEK BECSLÉSE REGRESSZIO.XLS EXCEL PARANCSFÁJLLAL 212

4.8 A CES-FÜGGVÉNY BECSLÉSE. (CES1.XLS, CES2.XLS CES3.XLS) 213

GYAKORLÓ FELADATOK CES1.XLS, CES2.XLS ÉS CES3.XLS 217

4.9 LOGISZTIKUS REGRESSZIÓS FÜGGVÉNYEK 217

4.10 A SZTOCHASZTIKUS KAPCSOLAT ELEMZÉSE, AZ ASSZOCIÁCIÓS EGYÜTTHATÓK EXCEL PARANCSFÁJL MŰKÖDÉSE 219

4.11 KENDALL-FÉLE RANGKONKORDANCIA-MUTATÓ 227

FÜGGELÉK 229

F.1 INTERNETES INGYENES SZOFTVEREK ÉS ADATBÁZISOK 229

F.2 A MATRIX.XLS PARANCSFÁJL MŰKÖDÉSE 232

F3 TUDOMÁNYTÖRTÉNETI ÖSSZEFOGLALÓ 234

F.4. TÁBLÁZATOK 239

F.4. A GÖRÖG BETŰK 249

FELHASZNÁLT IRODALOM 250

[1] Pl.: BMDP, SPSS, SAS, STATISTICA, MINISTAT, MINITAB, EViews, stb.

[2] Az Excel Windows változata 1987-ben jelent meg.