Regresijas Excel: vienādojumu piemēriem. lineārā regresija

Regresijas analīze - rezultātu statistisko pētījumu metode, lai parādītu atkarību no parametra viena vai vairāku neatkarīgo mainīgo. Jo pirms datoru ēras, tās izmantošana ir diezgan grūti, jo īpaši, kad tas nonāca pie lieliem datu apjomiem. Šodien, mācīties, kā veidot regresiju Excel, jūs varat atrisināt sarežģītas statistikas problēmu tikai dažas minūtes. Zemāk ir konkrēti piemēri ekonomikā.

regresijas veidi

Šī koncepcija tika ieviesta matemātika Francis Galton 1886. Regresijas ir:

• lineāra;
• parabolisks;
• jauda;
• eksponenciāli;
• hiperbolisks;
• eksponenciāli;
• logaritmiskā.

Example 1

Apsveriet problēmu noteikšanai atkarību skaita atkāpšanās darbinieku vidējās algas, kas 6 rūpniecības uzņēmumiem.

Uzdevums. Seši uzņēmumi ir analizēti vidējo mēnešalgu un darbinieku skaitu, kas atmest brīvprātīgi. Tabulas veidā mums ir:

		B	C
1	X	Skaits atkāpšanās	alga
2		y	30000 rubļu
3	1	60	35000 rubļu
4	2	35	40000 rubļu
5	3	20	45000 rubļu
6	4	20	50000 rubļu
7	5	15	55000 rubļu
8	6	15	60000 rubļu

Lai problēmu noteiktu atkarību no summas šķirtības ņēmējiem no vidējās algas, par 6 uzņēmumiem regresijas modelis ir forma vienādojuma Y = a ₀ + a, ₁ x ₁ + ... + a, _k x _k, kur x _i - ietekmējošie mainīgie, a _i - regresijas koeficienti, ak - vairāki faktori.

Y konkrētam uzdevumam - tas ir rādītājs, lai ugunsgrēka darbinieks, faktors - darba alga, kas tiek apzīmēts ar X.

Mobilizācija Power no "Excel" izklājlapu

Regresijas analīze Excel būtu pirms pieteikumu esošajiem tabulas datiem iebūvētās funkcijas. Tomēr šiem mērķiem ir labāk izmantot ļoti noderīgs add-in "pakešu analīzi". Lai nodrošinātu to, jums ir nepieciešams, lai:

• ar tab "File" iet uz "Settings";
• logā, kas atveras, izvēlieties "Add-ons";
• noklikšķiniet uz pogas "go", kas atrodas apakšējā labajā līnijas "vadība";
• likts atzīme "analīze ToolPak" un apstipriniet savu darbību, nospiežot "OK".

Ja izdarīts pareizi, tad labajā pusē "Data" tab, kas atrodas virs darba lapas "Excel", norāda vajadzīgo pogu.

Linear Regression Excel

Tagad, ka jums ir uz rokas visas nepieciešamās virtuālo rīkus ekonometrijas aprēķiniem, mēs varam sākt risināt savu problēmu. Lai to izdarītu:

• poga ir noklikšķinājuši uz "datu analīze";
• noklikšķiniet uz pogas "pasliktināšanai" atvērtu logu;
• cilne, kas parādās ieviest vērtību diapazonu Y (skaits šķirtības darbinieku) un X (viņu algas);
• atkārtoti apstiprina savas darbības, nospiežot «Labi» pogu.

Tā rezultātā, programma automātiski aizpildīt jaunās lapas izklājlapas datu regresijas analīzi. Pievērsiet uzmanību! Excel, ir iespēja noteikt vietu kas jums patīk labāk šim nolūkam. Piemēram, tas var būt tā pati lapa, kur vērtības Y un X, vai pat jauna grāmata, kas īpaši paredzēti glabāšanai šādu datu.

Regresijas analīzes rezultāti R-kvadrātā

Excel iegūtie dati uzskatāmi piemēram, dati ir formā:

Pirmkārt, mums vajadzētu pievērst uzmanību vērtību R kvadrātā. Tā ir koeficientu noteikšanas. Šajā piemērā, R-kvadrāts = 0,755 (75,5%), m. E. Aprēķinātie parametri modeli, lai izskaidrotu attiecības starp parametriem par 75.5% uzskata. Jo augstāka vērtība koeficienta noteikšanu, izvēlētais modelis tiek uzskatīts, ka ir vairāk noderīga konkrētiem uzdevumiem. Tiek uzskatīts, ka, lai pareizi aprakstītu reālo situāciju pie R-kvadrāta vērtību virs 0,8. Ja R-kvadrāts <0.5, tad regresijas analīze Excel nevar uzskatīt par pieņemamu.

attiecība analīze

Numurs 64,1428 parāda, kāda būs vērtību Y, ja visi mainīgie xi mūsu modelis tiks atiestatīti. Citiem vārdiem sakot, to var apgalvot, ka vērtība analizēto parametru ietekmē citi faktori, kas nav aprakstītas konkrētu modeli.

Nākamais faktors -,16285 atrodas šūnā B18, parāda svarīga ietekme mainīgā X Y. Tas nozīmē, ka vidējā darba samaksa ietvaros modeļa ietekmē skaits atkāpšanās no no -0.16285 svaru, t. E. tā ietekmes pakāpe vispār maza. Zīme "-" norāda, ka koeficients ir negatīvs. Tas ir skaidrs, jo mēs visi zinām, ka vairāk alga uzņēmumā, jo mazāk cilvēki ir izteikuši vēlmi izbeigt darba līgumu vai noraidīt.

daudzfaktoru regresijas

Saskaņā ar šo terminu attiecas uz komunikāciju vienādojumu ar vairākiem neatkarīgiem mainīgajiem formā:

y = f (x ₁ + x ₂ + ... x _m) + ε, kur y - ir iezīme rezultāts (atkarīgais mainīgais), un x _1, x _2, ... x _m - ir pazīmes faktori (neatkarīgiem mainīgajiem).

parametru novērtēšana

Multiplās regresijas (MR) tā tiek veikta, izmantojot mazāko kvadrātu metodi (LSM). Lineāro vienādojumu formā: Y = a + b ₁ x _{1 + ... + b m x m +} ε veidojot sistēmu parastos vienādojumu (cm. Zemāk)

Lai izprastu metodes principu, mēs uzskatām, ka divu faktoru lietu. Tad mēs esam aprakstītā situācija ar formulu

Līdz ar to, mēs iegūstam:

kur σ - ir dispersija attiecīgo funkciju, atspoguļo indeksā.

MNC ir piemērojams vienādojums MR uz standartiziruemom skalu. Šajā gadījumā, mēs iegūstam vienādojumu:

kur t _y, t _{x 1, ...} t _xm - standartiziruemye mainīgie, par kuru vidējās vērtības ir 0; beta _i - standartizētu regresijas koeficienti un standartnovirzi - 1.

Lūdzu, ņemiet vērā, ka visas beta _i šajā gadījumā definēts kā normēti un tsentraliziruemye, tāpēc salīdzinājums starp: a uzskatīti par derīgiem un pieņemamiem. Turklāt, tas ir pieņemts, lai veiktu pārbaudi faktoru, atmetot tos, kas ir zemākās vērtības βi.

Problēma ar izmantojot lineārās regresijas vienādojumu

Pieņemsim, ka jums ir tabula ar dinamikas cenu konkrētā produkta N pēdējo 8 mēnešu laikā. Tas ir nepieciešams, lai izlemtu, vai viņa partijas iegādi par cenu 1850 rubļu. / T.

		B	C
1	mēnesis	nosaukums mēneša	Cena N
2	1	janvāris	1750 rubļu par tonnu
3	2	februāris	1755 rubļu par tonnu
4	3	marts	1767 rubļu par tonnu
5	4	aprīlis	1760 rubļu par tonnu
6	5	maijs	1770 rubļu par tonnu
7	6	jūnijs	1790 rubļu par tonnu
8	7	jūlijs	1810 rubļu par tonnu
9	8	augusts	1840 rubļu par tonnu

Lai atrisinātu šo problēmu tabulas procesoru "Excel", kas nepieciešams, lai izmantotu jau zināms, piemēram, rīku "Datu analīze", kā iepriekš aprakstītais. Tālāk, izvēlieties "regresijas" sadaļu un iestatīt parametrus. Mums ir jāatceras, ka "Ievades diapazons Y» jāievieš virkni vērtībām atkarīgā mainīgā (šajā gadījumā cenu preču konkrētos gada mēnešos) un "Ievades intervāls X» - par neatkarīgu (mēnesis). Mēs apstiprinātu darbību, noklikšķinot uz «Ok». Jaunā darblapā (ja tā norādīts), mēs iegūstam datus par regresiju.

Mēs balstoties uz tiem lineāru vienādojumu formā: y = ax + b, kur kā parametriem a un b ir koeficienti no līnijas numuru mēnesi un nosaukumu koeficientiem un «Y-krustošanās" rindā loksnes ar regresijas analīzes rezultātu. Tādējādi, problēmu lineārā regresijas vienādojums (EQ) 3 var rakstīt šādi:

Preču cena N = 11714 * 1727,54 skaits mēnesī +.

vai algebrisko notācija

y = 11714 x + 1727,54

rezultātu analīze

Lai izlemtu, vai saņemtā adekvāti lineārās regresijas vienādojuma, izmantojot vairākus korelācijas koeficientus (CMC) un noteikšanu, kā arī testu un Fišera t-tests. Šajā tabulā "Excel" regresiju ar rezultātiem, viņi darbojas ar nosaukumu vairākas R, R-Square, F-t-statistika un, attiecīgi.

KMC R ļauj novērtēt tuvību varbūtējs attiecības starp neatkarīgajiem un atkarīgo mainīgo. Tās augsto vērtību norāda pietiekami spēcīga saikne starp mainīgo "skaits mēneša" un "N produktu cenas rubļu 1 tonnu." Tomēr šo attiecību būtība nav zināma.

Par koeficienta noteikšana ^R2 laukums (RI) ir tāda daļa no kopējās izkliedes skaitliskais raksturīgs un parāda izkliedi eksperimentālā datu daļu, t.i., vērtības atkarīgā mainīgā atbilst lineāro regresijas vienādojumu. Ar šo problēmu, šī vērtība ir 84,8%, mp. E. statistika ar augstu iegūto precizitātes ir aprakstīti SD.

F-statistika, kas pazīstams arī kā Fišera kritēriju izmanto, lai novērtētu nozīmi lineārās atkarības vai atspēkot hipotēzi apstiprina tās pastāvēšanu.

No t-statistikas (Stjūdenta t testu) vērtība palīdz novērtēt nozīmi koeficienta jebkurā brīvajā nezināmā lineārās atkarības locekli. Ja vērtības t-testu> t _CR, hipotēze lineāras vienādojuma niecību brīvā termiņu tiek noraidīts.

Ar šo problēmu par brīvu terminu izmantojot instrumentus "Excel", tika konstatēts, ka t = 169,20903, un p = 2,89E-12, t. E. Vai nulles varbūtību, ka ticīgajiem tiks noraidīts hipotēzi par niecību bezmaksas termiņā. Par nezināmu koeficientu pie t = 5,79405, un p = 0,001158. Citiem vārdiem sakot, varbūtība, ka noraida pareizs hipotēze tiks nenozīmīgums koeficientu nezināmā, ir 0,12%.

Līdz ar to var apgalvot, ka pienācīgi iegūta lineārās regresijas vienādojumu.

Problēma ar lietderību pērk akcijas

Vairāki regresijas tika veikta Excel, izmantojot vienu un to pašu "Datu analīze" rīku. Apsveriet konkrēto pieteikumu.

Ceļvedis kompānija «NNN» ir jāizlemj, vai iegādāties 20% akciju AS «MMM». Komplekta cena (SP) ir 70 miljoni ASV dolāru. Speciālisti «NNN» apkopoti dati par līdzīgiem darījumiem. Tika nolemts, lai novērtētu vērtību akciju uz šādiem parametriem, izteikts miljonos ASV dolāru, piemēram:

• parādi (VK);
• gada apgrozījums apjoms (VO);
• parādi (VD);
• pamatlīdzekļu vērtība (SOF).

Turklāt, izmantojiet algu parādus uzņēmumu (V3 u) tūkstošiem ASV dolāru.

Par lēmumu galda procesoru Excel līdzekļi

Vispirms jums ir nepieciešams, lai izveidotu tabulu ievades datiem. Tas ir šāds:

Nākamais:

• zvans box "datu analīze";
• atlasīts sekcija "regresijas";
• logs "Ievades intervāls Y» ievada klāsts atkarīgo mainīgo vērtības no G kolonnā;
• noklikšķiniet uz ikonas ar sarkanu bultiņu pa labi no loga "Input intervāls X» un izolēti uz lapas diapazonu visām vērtībām B, C, D, F kolonnu

Atzīmējiet punkts "Jaunā darblapā" un noklikšķiniet uz "OK".

Saņemiet regresijas analīzi šo uzdevumu.

Pētījuma rezultāti un secinājumi

"Collect" noapaļots no datiem, kas minēti iepriekš uz lapas galda Excel procesors regresijas vienādojumu:

SD = 0,103 * SOF + 0541 * VO - 0,031 * VK + 0,405 * VD + 0691 * VZP - 265.844.

Jo vairāk parastā matemātiskā formā to var uzrakstīt kā:

y = 0103 * x1 + 0541 * x2 - 0,031 * x3 + 0,405 * x4 + 0691 * x5 - 265844

Dati par «MMM» AS sniegti tabulā:

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

Ievietojot tos regresijas vienādojumu, iegūst skaitli 64.72 miljonus ASV dolāru. Tas nozīmē, ka akcijas AS «MMM» nevajadzētu pirkt, jo to izmaksas ir diezgan pārspīlēta par 70 miljoniem ASV dolāru.

Kā jūs varat redzēt, izklājlapas izmantošana "Excel" un regresijas vienādojums drīkst pieņemt pārdomātu lēmumu par iespēju diezgan konkrētu darījumu.

Tagad jūs zināt, ko regresijas. Piemēri Excel, iepriekš minētās, kas palīdzēs jums praktiskas problēmas ekonometrijas risināšanā.