Regression yn Excel: fergeliking foarbylden. lineêre regression

Regresje analyse - in statistyske stúdzje metoade om de ôfhinklikheid fan in parameter fan ien of mear selsstannige fariabelen. Yn de pre-kompjûter tiidrek, syn gebrûk is nochal dreech, foaral as it kaam ta grutte dielen fan de gegevens. Hjoed, leare hoe't it bouwen fan in regression yn Excel, kinne jo oplosse komplekse statistyske problemen mei mar in pear minuten. Hjirûnder binne spesifike foarbylden fan ekonomy.

regression types

Dit begryp waard yntrodusearre oan de wiskunde troch Francis Galton yn 1886. Regression is:

• lineêr;
• parabolic;
• macht;
• Joast;
• hyperboalyske;
• Joast;
• logaritmyske.

foarbyld 1

Tink oan it probleem fan it fêststellen fan 'e ôfhinklikheid fan' e oantal resignations fan leden fan meiwurkers fan de gemiddelde lean yn 'e 6 ûndernimmings.

Taak. Seis bedriuwen hawwe analysearre de trochsneed moannetemperatuer salaris en it tal meiwurkers dy't ophâlde frijwillich. Yn tabular foarm wy hawwe:

	A	B	C
1	X	Oantal resignations	salaris
2		y	30000 roebels
3	1	60	35000 roebels
4	2	35	40000 roebels
5	3	20	45000 roebels
6	4	20	50,000 roebel
7	5	15	55000 roebels
8	6	15	60000 roebels

Foar it probleem fan it fêststellen fan 'e ôfhinklikheid fan it bedrach Skiedingen arbeiders út de gemiddelde salaris foar 6 bedriuwen regressy model hat de foarm fan fergeliking Y = in ₀ + a ₁ x ₁ + ... + a _k x _k, dêr't x _i - beynfloedzjende fariabelen, in _i - regresje coefficients, ak - oantal faktoaren.

Y foar in opjûne taak - it is in indicator by brân in wurknimmer, in bydroegen faktor - it salaris, dat wurdt oantsjutten mei X.

Harnessing de krêft fan "Excel" rekkenblêd

Regresje analyse yn Excel moatte wurde foarôfgien troch in applikaasje foar de besteande tabel gegevens ynboude funksjes. Mar foar dizze doelen is it better om te brûken op in hiel brûkbere tafoegings-yn "pakket analyze". Om ynskeakelje it, jim moatte:

• mei de ljepper "Triem" gean nei "Ynstellings";
• yn it finster dat iepenet, selektearje 'Add-ons';
• klik op de knop "Gean", leit op de boaiem rjochts fan 'e line "behear";
• sette in markearring neist "Analyze ToolPak" en befêstigje jo aksje troch te drukken op "OK".

As dien korrekt, rjuchts fen 'e "Data" ljepper, leit boppe it wurk sheet "Excel", toant de winske knop.

Linear Regression in Excel

No dat jo hawwe oan kant alle nedige firtuele ynstruminten foar econometric berekkenings, kinne wy begjinne te pakken ús probleem. Dat giet sa:

• knop wurdt klikt op de "Data Analysis";
• klik op de knop "regresje" yn 'e iepen finster;
• in ljepper dy't ferskynt foar ynfiering fan in oanbod fan wearden Y (it oantal Skiedingen arbeiders) en X (harren salaris);
• en nij befêstigje wolle harren aksjes troch te drukken op de «Ok» knop.

As gefolch, it programma sil automatysk folje de nije wurkblêd wurkblêd data regressy analyze. Pay oandacht! Yn Excel, is der gelegenheid om it plak dat jo leaver foar dit doel. Bygelyks, it kin wêze deselde blêd, dêr't de wearden Y en X, of sels in nij boek, spesifyk ûntwurpen foar de opslach fan dy gegevens.

Regresje analyse results for R-plein

De Excel gegevens helle yn de beskôge foarbyld gegevens hawwe de foarm:

Earst fan alles, wy moatte betelje omtinken oan de wearde fan R-Squared. It stiet foar de koëffisjint fan berettens. Yn dit foarbyld, R-plein = 0.755 (75.5%), m. E. De berekkene parameters fan it model te ferklearjen de relaasje tusken de parameters beskôge troch 75.5%. De hegere de wearde fan 'e koëffisjint fan bepaling, de selektearre model wurdt beskôge as mear brûkber foar bepaalde taken. Der wurdt fan útgien om korrekt beskriuwe it echte situaasje by de R-plein wearde boppe 0.8. As de R-plein <0.5, dan in regressy analyze yn Excel kin net beskôge ridlik.

ratio analysis

Nûmer 64,1428 lit sjen wat sil wêze de wearde fan de Y, as alle fariabelen xi yn ús model sil reset wurde. Mei oare wurden, it kin seit dat de wearde fan 'e analysearre parameter wurdt beynfloede troch oare faktoaren as dy beskreaun yn de spesifike model.

De folgjende faktor -0,16285 leit yn sel B18, toant de wichtige ynfloed fan fariabele X te Y. Dit betsjut dat de gemiddelde salaris fan de meiwurkers binnen it model beynfloedet it oantal resignations út it gewicht fan -0,16285, t. E. de graad fan syn ynfloed op alle lyts. It teken "-" jout oan dat de Koëffisjint negatyf is. It leit foar de hân, om't wy allegearre witte, dat it mear salaris yn de ûndernimming, it minder minsken hawwe útsprutsen in winsk om stop it kontrakt fan wurkgelegenheid of ûntslein.

meardere regression

Under dizze term ferwiist nei de kommunikaasje fergeliking mei ferskate ûnôfhinklike fariabelen fan de foarm:

y = f (x ₁ + x ₂ + ... x _m) + ε, dêr't y - is in funksje skoare (de ôfhinklike fariabele), en x _1, x _2, ... x _m - binne buorden faktoaren (ûnôfhinklike fariabelen).

parameter schatting

Foar meardere regresje (MR) It wurdt dien mei help fan in minste kwadraten metoade (LSM). Foar strekkende fergelikingen fan de foarm Y = a + b ₁ x _{1 + ... + b m x m +} ε it bouwen fan in systeem fan normale fergelikingen (sm. Hjirûnder)

Om begripe it prinsipe fan 'e metoade, beskôget men de twa-factor gefal. Dan ha wy de situaasje beskreaun troch de formule

Dêrfandinne, wy krije:

dêr't σ - is de fariaasje werom fan 'e oanbelangjende funksje, ta utering yn de yndeks.

MNC is fan tapassing foar de fergeliking MR te standartiziruemom skaal. Yn dit gefal, wy krije de fergeliking:

dêr't t _y, t _{x 1, ...} t _Xm - standartiziruemye fariabelen dêr't gemiddelde wearden 0 binne; β _i - standerdisearre regresje coefficients en standertdeviaasje - 1.

Tink derom dat alle β _i yn dit gefal definiearre as de normalisearre en tsentraliziruemye, dêrom in ferliking tusken in beskôge jildich en akseptabel. Boppedat, it is akseptearre te fieren trochljochtsjen fan faktoaren, ûnacht dyjingen dy't hawwe de leechste wearden fan βi.

It probleem mei help fan lineêre regressy fergeliking

Stel jo ha in tafel fan de dynamyk fan de priis fan in bepaald produkt N foar de lêste 8 moannen. It is needsaaklik om te besluten oft it oernimmen fan syn partij by de priis fan 1850 roebel. / T.

	A	B	C
1	de moanne	Namme fan it moanne	Priis N
2	1	Jannewaris	1750 roebels per ton
3	2	Febrewaris	1755 roebels per ton
4	3	march	1767 roebels per ton
5	4	April	1760 roebels per ton
6	5	may	1770 roebels per ton
7	6	june	1790 roebels per ton
8	7	Juli	1810 roebels per ton
9	8	August	1840 roebels per ton

Oplosse dit probleem yn de tabular prosessor "Excel" nedich te brûken al bekend bygelyks ark "Data Analysis" presintearre hjirboppe. Folgjende, kies "Regresje" diel en set parameters. Wy moatte betinke dat yn it "Input berik Y» moatte wurde ynfierd mei in oanbod fan wearden fan de ôfhinklike fariabele (yn dit gefal de priis fan it guod yn spesifike moannen fan it jier) en yn de "Input tuskenskoft X» - foar in selsstannich (de moanne). Wy befêstigje de aksje troch te klikken «Ok». Yn in nij wurkblêd (as sa oanjûn), wy krije de gegevens foar it regresje.

Wy bouwe oan harren liniearre meiinoar fan de foarm y = bijl + b, wêr as de parameters a en b binne de coefficients út 'e line tal fan' e moanne en de namme fan 'e coefficients en «Y-intersection" line fan it blêd mei de resultaten fan it regresje analyze. Sa, de lineêre regresje fergeliking (EQ) 3 foar it probleem kin skreaun wurde as:

De priis fan guod N = 11,714 * 1727.54 moanne nûmer +.

of yn algebraic notaasje

y = 11.714 x + 1727,54

analyze fan de resultaten

Beslute oft de ûntfongen adekwaat lineêre regressy fergeliking mei help fan it meardere gearhing coefficients (CMC) en fêststelling likegoed as test en Fisher syn t-test. Yn 'e tabel "Excel" regresje mei de resultaten se hannelje ûnder de nammen meardere R, R-Square, F-t-statistiken en statistiken, resp.

KMC R stelt om skatte de closeness probabilistic relaasje tusken ûnôfhinklike en ôfhinklike fariabelen. Syn hege wearde wiist op in sterk genôch ferbining tusken de fariabele 'Oantal de moanne "en" N Product priis yn roebels per 1 ton. " Lykwols, de aard fan dizze relaasje is ûnbekend.

It plein fan it koëffisjint fan fêststellen R ² (RI) is in nûmerike karakteristyk fan it oandiel fan it totale scatter en lit in scatter fan eksperimintele gegevens diel, i.e., wearden fan de ôfhinklike fariabele oerienkomt mei in liniearre regressy fergeliking. Yn dit probleem, dizze wearde is 84,8%, mp. E. Statistics mei in hege mjitte fan accuracy krigen wurde beskreaun SD.

F-statistics, ek bekend as Fisher kritearium brûkt om te beoardieljen fan de betsjutting fan de lineêre ôfhinklikheid of disproving hypoteze befêstiget syn bestean.

De wearde fan T-statistic (Student syn t test) helpt evaluearje 'e betsjutting fan' e Koëffisjint op elk frije ûnbekende lineêre ôfhinklikheid lid. As de wearde fan 't-test> t _cr, de hypoteze fan in liniearre fergeliking insignificance fan frije term wurdt ôfwiisd.

Yn dit probleem foar in frije term fia ynstruminten "Excel" It waard fûn dat t = 169,20903, en p = 2,89E-12, t. E. Have a nul kâns dat de leauwigen sil wurde wegere de hypoteze fan de insignificance fan de frije termyn. Foar ûnbekende Koëffisjint by 't = 5,79405, en p = 0,001158. Mei oare wurden, de kâns dat in ôfwiisd juste hypoteze sil insignificance fan de Koëffisjint foar it ûnbekende, is 0,12%.

Sa, dan kin seit dat it krigen lineêre regresje fergeliking genôch.

It probleem fan 'e advisability fan it keapjen fan oandielen

Meardere regresje waard útfierd yn Excel mei help fan itselde "Data Analysis" ark. Tink oan de spesifike applikaasje.

Gids bedriuw «NNN» moat beslisse oft te keapjen 20% fan de oandielen fan JSC «MMM». Arranzjemint Priis (SP) is 70 miljoen Amerikaanske dollars. Spesjalisten fan «NNN» sammele gegevens op ferlykbere transaksjes. Der waard besletten om te beoardieljen de wearde fan de oandielen op sokke parameters, ferwurde yn miljoenen Amerikaanske dollars, sa as:

• payables (VK);
• jierlikse omset folume (VO);
• receivables (VD);
• wearde fan de fêste aktiva (SOF).

Dêrneist brûk it lean skulden fan bedriuwen (V3 U) yn tûzenen Amerikaanske dollars.

It beslút tafel prosessor Excel middels

Earst moatte jo om in tafel fan ynfier gegevens. It is as folget:

Folgjende:

• call box "data analyze";
• selektearre "Regression" section;
• it finster "Input ynterfal Y» administered berik dependent fariabele wearden út kolom G;
• klik op it ikoantsje mei in reade pylk oan de rjochterkant fan it finster "Input tuskenskoft X» en isolearre op in lekken berik fan alle wearden fan kolom B, C, D, F.

Mark de punt "Nij wurkblêd" en klik op "Ok".

Skriuw dy yn foar in regressy analyze foar dizze taak.

De stúdzje resultaten en konklúzjes

"Collect" ôfrûne út de gegevens presintearre hjirboppe op it wurkblêd tafel Excel prosessor regression fergeliking:

SD = 0,103 * SOF + 0,541 * VO - 0.031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

Yn de mear gebrûklike wiskundige foarm kin skreaun wurde as:

y = 0.103 * x1 + 0,541 * x2 - 0.031 * x3 + 0,405 * x4 + 0,691 * X5 - 265.844

Gegevens foar «MMM» JSC presintearre yn de tabel hjirûnder:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41.5	21.55	64,72

Substituting se yn it regresje fergeliking, krige in figuer fan 64,72 miljoen Amerikaanske dollars. Dat betsjut dat de oandielen fan JSC «MMM» moatte net keapje, omdat harren kosten is frij Overpriced om 70 miljoen Amerikaanske dollars.

Sa't jo sjen kinne, it brûken fan wurkblêd "Excel" en it regresje fergeliking tastien om in op 'e hichte beslút oangeande it advisability hiel spesifike transaksje.

No do witst wat in regression. Foarbylden to Excel, besprutsen hjirboppe, sil helpe by it oplossen praktyske problemen fan econometrics.