Regressiooni Excelis võrrandi näited. lineaarse regressiooni

Regressioonanalüüs - statistilise uurimise meetod näidata sõltuvus parameetri ühe või mitme sõltumatu muutuja. Pre-arvuti ajastu, selle kasutamine on olnud üsna raske, eriti kui ta tuli suuri andmemahtusid. Täna õppida, kuidas ehitada regressiooni Excel, saate lahendada keerulisi statistilisi probleeme vaid mõne minuti. Allpool on konkreetsed näited majandus.

regressiooni liiki

See kontseptsioon võeti kasutusele matemaatika Francis Galton 1886. Regressiooni on:

• lineaarne;
• parabool;
• võimsus;
• eksponentsiaalse;
• hüperboolse;
• eksponentsiaalse;
• logaritmiline.

NÄIDE 1

Vaatleme määramise probleemi sõltuvust arv tagasiastumist töötajate keskmisest palgast 6 tööstusettevõtted.

Task. Kuus ettevõtted on analüüsitud keskmine kuupalk ja töötajate arv, kes loobuda vabatahtlikult. Tabelina meil:

		B	C
1	X	Number tagasiastumist	palk
2		y	30000 rubla
3	1	60	35000 rubla
4	2	35	40000 rubla
5	3	20	45000 rubla
6	4	20	50000 rubla
7	5	15	55000 rubla
8	6	15	60000 rubla

Et kindlustada määramise probleemi sõltuvuse summa lahutamine töötajate keskmine palk 6 ettevõtete regressioonimudeli on kujul võrrandiga y = ₀ + ₁ x ₁ + ... + _k x _k, kus x _i - mõjutamisel muutujad, a _i - regressioonikoefitsendid, ak - mitmed tegurid.

Y antud ülesanne - see on näitaja, et tulekahju töötaja, tegur - palk, mis on tähistatud X

Võimaluste kasutamine "Excel" tabelisse

Regressioonanalüüs Excel peaks eelnema taotluse olemasoleva tabeli andmed sisseehitatud funktsioone. Kuid neil eesmärkidel on parem kasutada väga kasulik add-in "paketi analüüs". Selle võimaldamiseks peate:

• sakiga "File" minna "Seaded";
• aknas, mis avaneb, valige "Add-ons";
• vajutage nupule "Mine", allosas paremal line "juhtimine"
• pane linnuke "Analüüs Tööriistad" ja kinnitage tegevus vajutades "OK".

Kui teha õigesti, paremal pool "Data" tab, mis asub eespool töölehelt "Excel", näitab soovitud nuppu.

Lineaarne regressioon Excelis

Nüüd, kui on käsi kõik vajalikud virtuaalsed vahendid ökonomeetrilise arvutused, saame hakata tegelema meie probleem. Selleks:

• hiireklahvi "Andmete analüüs";
• vajutage nupule "halvendamiseks" avatud akna
• sakk näib aga erinevaid väärtusi Y (arv lahutamine töötajate) ja X (nende palk);
• kinnitavad oma tegevuse vajutades nuppu «Ok».

Selle tulemusena programm automaatselt täita uus leht tabelisse andmeid regressioonanalüüs. Pöörake tähelepanu! Excelis on olemas võimalus määrata koht, et sa eelistad selleks. Näiteks võib ta olla samasugused lehed, kus väärtused Y ja X, või isegi uut raamatut, spetsiaalselt säilitatakse kõnealuseid andmeid.

Regressioonianalüüsiga tekstile R-ruut

Exceli saadud andmeid loetakse näiteks andmete põhjal kujul:

Esiteks, me peame pöörama tähelepanu väärtus R-ruudu. See esindab määratlustegur. Selles näites R-ruut = 0,755 (75,5%), m. E. Arvestatud mudeli parameetreid selgitamiseks parameetrite seos loeb 75,5%. Mida kõrgem väärtus määratlustegur, valitud mudeli peetakse kasulikumaks eriti ülesandeid. Usutakse õigesti kirjeldavat tegelikku olukorda hetkel R-ruudu väärtus üle 0,8. Kui R-ruut <0,5, seejärel regressioonianalüs Excel ei saa pidada mõistlikuks.

suhteanalüüs

Number 64,1428 näitab, milline saab olema väärtus Y, kui kõik muutujad xi meie mudeli lähtestatakse. Teisisõnu, võib väita, et väärtus analüüsitud parameeter mõjutab muid tegureid peale nende, mida on kirjeldatud mudeliga.

Järgmine tegur -0,16285 asub raku B18, näitab olulist mõju muutuja X Y. See tähendab, et keskmine palk töötajat mudeli number mõjutab tagasiastumist alates kaal -0,16285, t. E. aste mõju üldse väike. Märk "-" näitab, et koefitsient on negatiivne. On ilmselge, sest me kõik teame, et rohkem palka ettevõttes, seda vähem inimesed on avaldanud soovi lõpetada tööleping või tagasi lükata.

multiregressiooni

Selle mõiste viitab kommunikatsioon võrrandi mitme sõltumatud muutujad kujul:

y = f (x ₁ + x ₂ ... + x _m) + ε, kus y - on funktsioon skoori (sõltuv muutuja) ja x _1, x _2, ... x _m - on märke tegurid (sõltumatud muutujad).

parameetrite hindamine

Suhe multiregressiooni (MR) on läbi, kasutades vähimruutude meetodit (LMS). Suhe lineaarvõrrandid kujul Y = a + b ₁ x _{1 + ... + b m x m +} ε hoone süsteemi normaalse võrrandid (cm. Allpool)

Selleks, et mõista meetodi põhimõtet, vaatleme kahe teguri puhul. Siis oleme kirjeldatud olukorra valemiga

Seega saame:

kus σ - on vastuolus vastava funktsiooni kajastub indeks.

MNC kehtib võrrand MR standartiziruemom skaalal. Sel juhul saame võrrandi:

kus t _y, t _{x 1, ...} t _xm - standartiziruemye muutujaid, mille keskmised väärtused on 0; P _i - standardiseeritud regressioonikoefitsendid ja standardhälve - 1.

Pange tähele, et kõik p _i sel juhul määratletakse normaliseeritud ja tsentraliziruemye, seega võrdlus vahel loetakse kehtivaks ja vastuvõetav. Lisaks on selge, et teostada sõelumine tegurid, visates need, mis on madalaim väärtused βi.

Probleem kasutades lineaarse regressiooni võrrandi

Me eeldame, et olete tabeli dünaamika hind konkreetse toote N viimase 8 kuu jooksul. On vaja otsustada, kas omandamine tema partei hinnaga 1850 rubla. / T.

		B	C
1	kuu	kuu nimetus	Hind N
2	1	jaanuar	1750 rubla tonni
3	2	veebruar	1755 rubla tonn
4	3	märts	1767 rubla tonn
5	4	aprill	1760 rubla tonn
6	5	mai	1770 rubla tonn
7	6	juuni	1790 rubla tonn
8	7	juuli	1810 rubla tonn
9	8	august	1840 rubla tonni

Selle probleemi lahendamiseks on tabelina protsessor "Excel" peab kasutama juba tuntud näiteks tööriista "Data Analysis" esitatud eespool. Järgmisena valige "regressioon" ja parameetrite määramiseks. Tuleb meeles pidada, et valdkonnas "sisend intervalli Y» tuleks kasutusele erinevaid väärtusi sõltuv muutuja (antud juhul hind kirje konkreetse kuu jooksul) ja "sisend intervalli X» - sõltumatu (kuu). Me toimingu kinnitamiseks klõpsates «Ok». Uues tööleht (kui jah täpsustatud), saame andmeid regressiooni.

Metuginedes neist lineaarvõrrandi kujul y = ax + b, kus nagu parameetrite a ja b on koefitsiendid liinilt kuu number ja nimi koefitsientide ja «Y-ristmik" joon poogna tulemustega regressioonianalüüsita. Seega lineaarse regressiooni võrrandi (EQ) 3 probleemile saab kirjutada kui:

Kauba hind N = 11714 * 1727,54 kuu number +.

või algebraline märke

y = 11714 x + 1727,54

tulemuste analüüs

Otsustama, kas vastuvõetud adekvaatselt lineaarse regressiooni võrrandist mitu korrelatsioonikoefitsente (CMC) ja määramine, samuti testi ja Fisheri t-testiga. Tabelis "Excel" regressiooni tulemused nad tegutsevad nime all mitu R, R-Square, F-t-statistika ja statistika vastavalt.

KMC R võimaldab hinnata lähedust tõenäosuslik suhet sõltumatutest ja sõltuvatest muutujad. Selle kõrge väärtus näitab piisavalt tugev seos muutuja "number kuus" ja "N Toote hind rubla 1 tonni." Kuid milline see seos on teadmata.

Ruuduga määratlustegur ^R2 (RI) on numbriline iseloomulik osa kogu hajumine ning näitab hajumine eksperimentaalsete andmevälja, st väärtused sõltuv muutuja väärtus vastab lineaarse regressiooni võrrandi. Selles ülesandes see väärtus on 84,8%, st. E. Statistika suure täpsusastmega saadud kirjeldatakse SD.

F-statistika, mida tuntakse ka Fisher kriteeriumi kasutatakse olulisuse hindamiseks lineaarse sõltuvuse või lükkab hüpoteesi kinnitavad oma olemasolu.

Väärtus t-statistiku (Studenti t-test) aitab hinnata tähtsust koefitsient igal vaba teadmata lineaarne sõltuvus liige. Kui väärtus t-test> t _ov, hüpoteesi Lineaarvõrrandi tähtsusetus vaba terminit lükata.

Selles probleem tasuta perspektiivis vahendite kaudu "Excel" leiti, et t = 169,20903 ja p = 2,89E-12, t. E. Kas null tõenäosus, et ustavad tagasi lükata hüpoteesi väikesest tasuta perspektiivis. Tundmatute koefitsient t = 5,79405 ja p = 0,001158. Teisisõnu on tõenäosus, et lükata õiget hüpotees väikesest koefitsient teadmata, on 0,12%.

Seega võib väita, et saadud lineaarse regressiooni võrrandi adekvaatselt.

Probleem on otstarbekas osta aktsiaid

Mitmese regressiooni viidi läbi Exceli kasutades sama "Data Analysis" tööriista. Mõtle üksikakt.

Guide firma «NNN» peavad otsustama, kas osta 20% aktsiatest JSC "MMM». Paketi hind (SP) on 70 miljonit USA dollarit. Spetsialistid «NNN» kogutud andmeid sarnaste tehingute. Otsustati hinnata aktsiate väärtust sellistest parameetritest, väljendatakse miljonites USA dollarites, näiteks:

• võlgnevus (VK)
• aastakäive maht (VO);
• nõuete (VD);
• põhivara väärtus (SOF).

Lisaks kasutavad palk võlgu ettevõtete (V3 U) tuhandetes USA dollarit.

Otsus tabelis protsessor Excel abil

Esmalt on vaja luua tabel sisendandmed. See on järgmine:

Järgmine:

• kõne kasti "andmete analüüs"
• valitud "regressioon" osa;
• akna "sisend intervalli Y» manustada illustreeriv sõltuv muutuja väärtused kolonn G;
• klõpsa ikooni punase noolega paremal "sisend intervalli X» ja isoleeritakse lehed valikut kõik väärtused veerus B, C, D, F

Mark punkti "New tööleht" ja vajuta "OK".

Saa regressioonanalüüsi selle ülesande täitmiseks.

Uuringu tulemused ja järeldused

"Kogu" ümardatud toodud andmetest eespool lehel tabeli Excel protsessor regressioonivalemi:

SD = 0,103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844.

In veel tavaline matemaatiline vorm võib kirjutada:

y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265844

Andmed »MMM» JSC esitatud alljärgnevas tabelis:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV USD
102,5	535,5	45,2	41,5	21.55	64,72

Asendades need regressioonvõrrandi saadud näitaja 64720000 USA dollarit. See tähendab, et aktsiate JSC «MMM» tohiks osta, sest nende hind on üsna ülehinnatud 70 miljonit USA dollarit.

Nagu näete, kasutamise tabelisse "Excel" ja regressioonivalemi lubatud teha teadlikke otsuseid seoses otstarbekuse üsna konkreetse tehingu.

Nüüd sa tead, mida regressiooni. Näited Excel, eespool mainitud, aitab lahendada praktilisi probleeme ökonomeetria.