Andmeanalüüs MS Exceli abil


Regressioonanalüüs


 


  
Chart Wizard

Kahe tunnuse vahelisest sõltuvusest visuaalse ülevaate saamiseks on analüüsi esimese sammuna kasulik teha nende tunnuste vaheline hajuvusdiagramm (scatter plot).

Näiteks tudengite pikkuste ja kaalude vahelise seose iseloomustamiseks saame alljärgneva toodud graafiku.

Lihtsaim viis kahe tunnuse vahelise regressioonanalüüsi tegemiseks on:

  • aktiveerida joonis;
  • valida menüüst Chart käsk Add Trendline…;
  • valida avanenud menüüst punktiparve kuju paremini jälgiv regressioonijoon (peale tavalise lineaarse regressiooni - Linear - on punktiparvele sobitatavad ka mitmed keerulisemad kõverad);
  • valida samast Add Trendline…-aknast (sama aken avaneb ka peale hiire parempoolse nupu topeltklõpsu trendijoonel) lipik Options ja märkida seal ära käsud Display equation on chart ja Display R-squared value on chart.

Tulemuseks on joonis, kus lisaks punkti parvele on kujutatud ka regressiooni joon, regressioonivõrrand ja determinatsioonikordaja R2.

Antud näite korral on meil tudengi kaal prognoositav tema pikkusest valemiga
Kaal = -107,5 + 0,9967*Pikkus,
kusjuures selline mudel võimaldab ära kirjeldada 82% tunnuse 'Kaal' hajuvusest.

NB! Joonise tegemisel paigutage vertikaalsele teljele (y-teljele) uuritav (prognoositav) tunnus ja horisontaalteljele (x-teljele) argumenttunnus.
  


Üles


  
Protseduur Regression

Täieliku lineaarse regressioonanalüüsi tegemiseks on MS Exceli keskkonnas protseduur Regression (Tools -> Data Analysis).
Erinevalt Chart Wizard'st või funktsioonidest võimaldab see teostada ka mitmest regressioonanalüüsi, argumenttunnuste blokid peavad siis vaid paiknema üksteise kõrval (et neid saaks ette anda ühe pideva andmeblokina)

Protseduuri sisestusaknas tuleb määrata:

  Input Y Range - funktsioontunnuse andmete blokk;
  Input X Range - argumenttunnus(t)e andmete blokk;
  Labels - märgitakse nimede või tähiste olemasolu korral tunnuste bloki esimeses reas;
  Constant is Zero - märgitakse, kui tahetakse kontrollida tunnuste vahelist võrdelist sõltuvust (vabaliige a = 0);
  Confidence Level - usaldusnivoo parameetrite 1-a usalduspiiride arvutamiseks;
  Output options - määratakse tulemuste väljastamise asukoht: samale töölehele (Output Range), uuele töölehele (New Worksheet Ply) või uude faili (New Workbook).

Nende sisestuste põhjal moodustatakse kolm tabelit lineaarse regressioonanalüüsi parameetrite hinnangute, nende veahinnangute ja dispersioonanalüüsiga.

Soovi korral võib sisestusaknas täiendavalt tellida:

  Residuals - kõigi vaatluste prognoosijäägid;
  Standardizised Residuals - kõigi vaatluste standardiseeritud prognoosijäägid;
  Residuals Plot - prognoosijääkide graafik argumenttunnuse suhtes;
  Line Fit Plot - funktsioontunnuse ja prognooside graafik argumenttunnuse suhtes;
  Normal Probability Plot - funktsioontunnuse empiiriliste kvantiilide graafik (tõenäosuspaber).

Protseduuri Regression väljund

SUMMARY OUTPUT  

Regression Statistics

Multiple R  

0.9065

R Square   0.8218
Adjusted R Square 0.8158
Standard Error   5.2218
Observations 32
  

Regressiooni statistikud

Mitmene korrelastsioonikordaja  
Determinatsioonikordaja
Determinatsioonikordaja nihketa hinnang
Jääkstandardhälve
Vaatluste arv
  
ANOVA           Regressioonanalüüsi tulemuste dispersioonanalüüs
  

 

df
Vabadusastmete arv

SS
Hälvete ruutude summa

MS
Keskruut

F
F-statistik

Significance F
Mudeli olulisuse tõenäosus (p)

Regression Regressioonisirge

1

3771.8629

3771.8629

138.3303

2.7085E-08

Residual Prognoosijäägid

30

818.0121

27.2671

Total
Kokku

31

4589.8750

  

Regressioonivõrrandi kordajate analüüs

 

Coefficients
Parameetri hinnang

Standard Error
Hinnangu statndardviga

t Stat
t-statistik

P-value
Parameetri
olulisuse tõenäosus

Lower 95%
Alumine 95%-line usalduspiir

Upper 95%
Ülemine
 95%-line usalduspiir

Intercept
Vabaliige a

-107.5023

14.6057

-7.3603

3.37E-08

-137.3311

-77.6735

Pikkus
Regr. kordaja b

0.9967

0.0847

11.7614

9.2E-13

0.8236

1.1697

  
Protseduur Regression võimaldab väljastada ka kolm joonist:

Neist esimene, valikuga Residuals Plot tellitav, iseloomustab prognoosijääkide paiknemist argumenttunnuse suhtes. Kui regressioonanalüüsi eeldused on täidetud, peavad punktid sellel graafikul paiknema juhuslikult, ühtlaselt hajutatud punktiparvena.

Teine, valikuga Line Fit Plot tellitav graafik, näitab funktsioontunnuse ja prognooside paiknemist argumenttunnuse suhtes. See graafik on analoogne Chart Wizard'i abil saaduduga, kus hajuvusdiagrammile lisati lineaarne regressioonijoon (ka sellel graafikul võib prognoosidele vastavad punktid omavahel ühendada, saades nii regressioonisirge). Selline pilt on põhiline kahe tunnuse vahelise seose illustreerimiseks kasutatav joonis.

Kolmas, valikuga Normal Probability Plot tellitav graafik, on jällegi kasutatav regressioonanalüüsi eelduste täidetuse kontrollimiseks. Ideaalvariandis paiknevad selle graafiku punktid ühel diagonaalsel sirgel. Kui see nii ei ole, ei jaotu uuritav tunnus vastavalt normaaljaotusele.


 

   

Üles


   
Funktsioonid

Lineaarse regressioonivõrrandi parameetrite a ja b arvutamiseks võib kasutada ka funktsioone:

SLOPE(Known_y's,Known_x's) - väljastatakse regressioonisirge tõus b ette
antud funktsioontunnuse y ja argumenttunnuse x blokkide korral;

INTERCEPT(Known_y's,Known_x's) - väljastatakse regressioonisirge vabaliige
a ette antud funktsioontunnuse y ja argumenttunnuse x blokkide korral.

Determinatsioonikordaja R2 on leitav funktsioonist RSQ(Known_y's,Known_x's).
     


Üles


 

ktanel@eau.ee
http://ph.eau.ee/~ktanel/kool_ja_too/
märts, 2000