Regresinė Excel: Lygtis pavyzdžių. tiesinė regresija

Regresijos analizė - statistinis Tyrimo metodas parodyti vieno ar daugiau nepriklausomų kintamųjų parametro priklausomybę. Pasirengimo kompiuterių eros, jo naudojimas buvo gana sunku, ypač, kai ji atėjo į didelės apimties duomenis. Šiandien, mokymosi, kaip sukurti regresiją Excel, galite išspręsti sudėtingas statistines problemas vos per keletą minučių. Žemiau yra konkretūs pavyzdžiai ekonomika.

regresijos atmainos

Ši koncepcija buvo pristatyta į matematikos Francis Galtona 1886. Regresinė yra:

• tiesinis procesas;
• parabolinis;
• galia;
• eksponentinis;
• hiperbolinės;
• eksponentinis;
• logaritminė.

1 PAVYZDYS

Apsvarstykite nustatant atsistatydinus iš darbuotojų vidutinio darbo užmokesčio per 6 pramonės įmonių skaičiaus priklausomybę problemą.

Uždavinys. Šešios bendrovės analizuojami vidutinis mėnesinis darbo užmokestis ir darbuotojų, kurie mesti savo noru skaičių. Lentelių forma mes turime:

		B	C
1	X	Taškų atsistatydinimo	atlyginimas
2		Y	30000 rublių
3	1	60	35000 rublių
4	2	35	40000 rublių
5	3	20	45000 rublių
6	4	20	50000 rublių
7	5	15	55000 rublių
8	6	15	60000 rublių

Siekiant nustatyti Tos sumos atsiskyrimo, darbuotojų priklausomybę nuo vidutinio algos už 6 įmonių regresijos modelio problemos turi formą iš lygties Y = A ₀ + per ₁ x ₁ + ... + per _k x _k, kur x _i - įtakos kintamieji, _i a - regresijos koeficientai, ak - Taškų veiksnių.

Y tam tikrą užduotį - tai rodiklis, į ugnį darbuotoją, veiksnys - darbo užmokestis, kuris yra žymimas X

Pajungti "Excel" skaičiuoklę galią

turėtų būti prieš regresinė analizė Excel paduotame į esamą lentelę duomenų built-in funkcijos. Tačiau šiems tikslams tai geriau naudoti labai naudingą papildinys "paketų analizė". Norėdami įjungti jį, jums reikia:

• su skirtukas "File" eiti į "Nustatymai";
• į Atsidariusiame lange pasirinkite "Add-ons";
• spustelėkite mygtuką "GO", esančio apačioje dešinėje linijos "valdymo";
• įdėti varnelę šalia "analizė ToolPak" ir patvirtinkite savo veiksmų spausdami "Gerai."

Jei padaryta teisingai, dešinėje pusėje "duomenys" skirtuke virš darbo lape "Excel", rodo norimą mygtuką.

Tiesinės regresijos Excel

Dabar, kad jūs turite po ranka visus reikalingus virtualius įrankius ekonometrinius skaičiavimus, mes galime pradėti spręsti mūsų problemų. Norėdami tai padaryti:

• mygtuką paspaudžia ant "Duomenų analizė";
• spustelėkite mygtuką "regresija" atviro lango;
• skirtukas, kuris pasirodo įvesti vertybių spektrą y (iš skyrium darbuotojų skaičius) ir X (jų darbo užmokestis);
• dar kartą patvirtina savo veiksmus spausdami «Ok» mygtuką.

Kaip rezultatas, programa bus automatiškai užpildyti naują lapą skaičiuoklės duomenų regresinę analizę. Atkreipkite dėmesį! Excel, yra galimybė nustatyti vietą, kad jums patogiau šiam tikslui. Pavyzdžiui, ji gali būti vienodi lapas, kur Y reikšmės ir X, arba netgi nauja knyga, specialiai suprojektuoti tokių duomenų saugojimui.

Regresinė analizė rezultatai R-kvadratu

Excel duomenys, gauti iš nagrinėjamo pavyzdžio duomenys formą:

Visų pirma, mes turėtume atkreipti dėmesį į R-kvadratu reikšmė. Ji atstovauja determinacijos koeficientas. Šiame pavyzdyje, R-kvadratinių = 0,755 (75,5%), m. E. apskaičiuotos modelio parametrai paaiškinti tarp parametrų apsvarstytomis 75,5% santykius. Kuo didesnė už nustatymo koeficiento vertė, pasirinktas modelis yra laikomas labiau naudinga konkrečių užduočių. Manoma, kad teisingai apibūdinti realią situaciją R-kvadrato vertė viršija 0,8. Jei R-kvadrato <0,5, tada regresinė analizė Excel negali būti laikomas pagrįstu.

rodiklių analizė

Taškų 64,1428 parodo, kas bus, kad Y reikšmė, jei visi kintamieji xi mūsų modelio bus iš naujo. Kitaip tariant, galima teigti, kad analizuojamu parametro reikšmė turi įtakos ir kiti veiksniai, negu nurodyta konkretaus modelio.

Kitas veiksnys -,16285 įsikūręs ląstelių B18, rodo svarbi įtaka kintamojo X Y. Tai reiškia, kad vidutinis atlyginimas darbuotojams per modelio įtakos atsistatydinimo skaičių nuo -0.16285 svoris, t. E. jos poveikio laipsnis ne visi mažas. Ženklas "-" reiškia, kad koeficientas yra neigiamas. Akivaizdu, nes visi žinome, kad kuo daugiau darbo užmokestis įmonėje, tuo mažiau žmonės išreiškė norą nutraukti darbo ar atmetė sutartį.

regresinė

Pagal šį terminą nurodo Ryšių lygtis su keletu nepriklausomų kintamųjų formos:

Y = f (x ₁ + x ₂ + ... x _m) + ε, kur y - tai funkcija, rezultatas (priklausomas kintamasis), ir x _1, x _2, ... x _m - yra ženklai veiksniai (nepriklausomi kintamieji).

parametras įvertinimas

Daugkartinio regresijos (MR) jis yra vykdomas, naudojant mažiausių kvadratų metodą (LSM). Linijinių lygtis rūšies Y = a + b _{+ 1} x _{1 ... + b m x M +} ε pastato įprastų lygtis sistemą (cm. Žemiau)

Norėdami suprasti šio metodo principą, manome dviejų veiksnių bylą. Tada mes turime situacija aprašyta formule

Taigi, gauname:

kur σ - tai atitinkamo funkcija dispersija, atsispindi indeksą.

MNC yra taikomos lygtis M. į standartiziruemom skalę. Šiuo atveju mes gauname lygtį:

kur t _y, t _{x 1, ...} t _XM - standartiziruemye kintamuosius, dėl kurių vidutinės vertės yra 0; beta _i - standartizuotus regresijos koeficientus ir standartinis nuokrypis - 1.

Atkreipkite dėmesį, kad visi beta _i šiuo atveju apibrėžiamas kaip normalizuotas ir tsentraliziruemye, todėl palyginimas tarp apgalvotą galioja ir priimtina. Be to, jis sutiko atlikti patikrą veiksnių, išpilant tiems, kurie turi mažiausias vertes βi.

Problema su naudojant tiesinės regresijos lygtį

Tarkime, jūs turite iš tam tikro produkto N kainų dinamiką lentelę pastaruosius 8 mėnesius. Būtina nuspręsti, ar jo šalis įsigyti prie 1850 rublių kainą. / T.

		B	C
1	mėnesį	Pavadinimas mėnesį	Kaina N
2	1	sausis	1750 rublių už toną
3	2	vasaris	1755 rublių už toną
4	3	kovas	1767 rublių už toną
5	4	balandis	1760 rublių už toną
6	5	gegužės	1770 rublių už toną
7	6	birželis	1790 rublių už toną
8	7	liepa	1810 rublių už toną
9	8	rugpjūtis	1840 rublių už toną

Norėdami išspręsti šią problemą lentelės procesorius "Excel" reikia vartoti jau žinomas pavyzdys priemonė "Duomenų analizė" virš pateikta. Toliau pasirinkite skyrių "regresija" ir nustatytus parametrus. Turime prisiminti, kad "Įvesties sritis Y» turėtų būti supažindinti su vertybėmis priklausomo kintamojo nuotolio (šiuo atveju prekių kaina konkrečių metų mėnesius) ir "Įvesties intervalas X» - už nepriklausomą (mėnesį). Mes patvirtiname, spustelėdami «Ok» Veiksmas. Į naują darbalapį (jei taip nurodyta), gauname dėl regresijos duomenis.

Mes statome ant jų tiesinę lygtį formos y = ax + b, kur, kaip parametrų a ir b koeficientai nuo linijos numeris mėnesį ir pavadinimas koeficientų ir "Y sankirtos" linijos lapo su regresinės analizės rezultatus. Taigi, regresijos lygties (EQ) 3 dėl problemos gali būti parašytas kaip:

Prekių kaina N = 11.714 * 1727,54 mėnesį numeris +.

arba Algebrinė notacija

Y = 11714 x + 1727,54

rezultatų analizė

Nuspręsti, ar gavo pakankamai tiesinės regresijos lygtį, naudojant kelis koreliacijos koeficientus (CMC), ir ryžtą, taip pat išbandyti ir Fisher anketa t-testą. Lentelėje "Excel" regresija su rezultatais jie veikia pagal pavadinimus kelių R, R-Square, F-t-statistika ir statistikos, atitinkamai.

KMC R leidžia įvertinti artumo tikimybinį ryšį tarp nepriklausomų ir priklausomų kintamųjų. Jos didelės vertės rodo pakankamai stiprus ryšys tarp kintamojo "numeris mėnesio" ir "N produkto kainos rubliais už 1 toną." Tačiau šių santykių prigimtis yra nežinoma.

Iš už nustatymo R ² koeficiento kvadratas (RI) yra skaitinė charakteristika viso sklaidos proporcingai ir rodo eksperimentinio duomenų dalies sklaidą, t.y., vertės priklausomas kintamasis, atitinkančios tiesinės regresijos lygtį. Į šią problemą, tai yra, vertės nėra 84,8%, MP. E. Statistika su dideliu tikslumu gauta aprašyti SD.

F-statistika, taip pat žinomas kaip Fisher kriterijus naudojamas įvertinti tiesinės priklausomybės ar paneigti hipotezę, patvirtinantis jos egzistavimą reikšmę.

Iš t-statistika (Stjudento t testas) vertė padeda įvertinti koeficiento reikšmę bet laisvos nežinoma tiesinės priklausomybės narys. Jei T-testo> t _{kr verte,} yra tiesinė lygtis nereikšmingumo laisvo laikotarpiu hipotezė atmetama.

Į šią problemą nemokamai laikotarpiu per priemonėmis "Excel" "buvo nustatyta, kad t = 169,20903, ir p = 2,89E-12, T. E. Turėti nulio tikimybė, kad tikintieji bus atmetė laisvo laikotarpiu nereikšmingumo hipotezę. Dėl nežinomų koeficiento esant t = 5,79405, ir p = 0,001158. Kitaip tariant, yra tikimybė, kad atmetė informacija hipotezė bus nereikšmingumo iš Unknown koeficiento, yra 0,12%.

Taigi, galima teigti, kad gauti tiesinės regresijos lygtį tinkamai.

Iš pirkti akcijas tikslingumo problema

Regresinė buvo atlikta Excel naudojant tą pačią "Duomenų analizė" įrankį. Apsvarstykite konkretų prašymą.

Vadovas įmonės «NNN» turi nuspręsti, ar įsigyti 20% akcijų UAB «MMM». Pakuotės kaina (SP) 70 milijonų JAV dolerių. Specialistai «NNN» surinkti duomenis apie panašius sandorius. Nuspręsta įvertinti akcijomis tokių parametrų vertės, išreikštos milijonais JAV dolerių, kaip antai:

• mokėtinos sumos (VK);
• metinė apyvarta tūris (VO);
• gautinos (VD);
• vertė ilgalaikio turto (SOF).

Be to, naudotis darbo užmokesčio skolas įmonių (V3 u) tūkstančių JAV dolerių.

Sprendimas stalo procesorius Excel priemonės

Pirmiausia jūs turite sukurti duomenų įvedimo lentelę. Tai taip:

Kitas:

• kvietimas dėžutė "duomenų analizė";
• pasirinktas "regresija" skyrių;
• langas "įvesties intervalas Yģ vartojamas grupę priklausomas kintamasis vertės iš G skiltyje;
• spustelėkite piktogramą su raudonu rodyklės į lango "Įvesties intervalas X» dešinę ir izoliuoti ant lapo asortimentą visų B, C, D, F stulpelyje vertybių

Pažymėti taškas "Naujasis Užduotis" ir spauskite "OK".

Gauti regresinę analizę atlikti šią užduotį.

Tyrimo rezultatai ir išvados

"Surinkti" suapvalintos anksčiau pateiktais ant lapo stalo Excel procesorius regresijos lygties duomenys:

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

Per daugiau įprastą matematinę formą jis gali būti parašytas kaip:

Y = 0,103 * x1 + 0541 * X2 - 0031 * x3 + 0,405 * X4 + 0691 * X5 - 265844

Duomenys «MMM» UAB pateikiami žemiau esančioje lentelėje:

SOP, USD	VO, USD	V., USD	VD, USD	VZP, USD	JV dolerių
102.5	535,5	45.2	41,5	21.55	64.72

Pakeisti juos į regresijos lygtį, gaunama iš 64.72 milijonų JAV dolerių paveikslą. Tai reiškia, kad UAB «MMM» akcijos neturėtų pirkti, nes jų kaina yra gana brangių bent 70 milijonų JAV dolerių.

Kaip matote, iš skaičiuoklės naudojimas "Excel" "ir regresijos lygtis leido padaryti pagrįstą sprendimą dėl tikslingumo gana konkretų sandorį.

Dabar jūs žinote, kas yra regresija. Pavyzdžiai tobulėti, buvo aptarta anksčiau, padės jums sprendžiant praktines problemas ekonometrijos.