Regresija v Excel: enačbami primerov. linearne regresije

Regresijska analiza - metoda statistična študija za prikaz odvisnost parametra enega ali več neodvisnimi spremenljivkami. V pre-računalniške dobe, je njegova uporaba je precej težko, še posebej, ko je šlo za velike količine podatkov. Danes, učenje, kako zgraditi nazadovanje v Excelu, lahko reši zapletene statistične težav v samo nekaj minutah. Spodaj so konkretni primeri iz ekonomije.

vrste regresijskih

Ta koncept je bil uveden za matematiko Francis Galton leta 1886. Regresija je:

• linearen;
• parabolična;
• moč;
• eksponentno;
• hiperbolični;
• eksponentno;
• logaritemsko.

PRIMER 1

Razmislite problem določitve odvisnosti števila odstopov članov osebja povprečne plače v industrijskih podjetjih 6.

Naloga. Šest podjetij so analizirali povprečne mesečne plače in število zaposlenih, ki nehal prostovoljno. V obliki tabele imamo:

		B	C
1	X	Število odstopov	plače
2		y	30.000 rubljev
3	1	60	35000 rubljev
4	2	35	40000 rubljev
5	3	20	45000 rubljev
6	4	20	50.000 rubljev
7	5	15	55000 rubljev
8.	6	15	60000 rubljev

Za problema določanje odvisnosti delavcev zneska ločitev od povprečne plače za 6 podjetij regresijski model ima obliko enačbe y = a = ₀ + A ₁ x ₁ + ... + A _k x _k, kjer je x _i - vplivne spremenljivke, a _i - regresijski koeficienti, ak - številni dejavniki.

Y za dano nalogo - je kazalnik na ogenj delavca, ki prispeva dejavnik - plača, ki je označena z X.

Izkoriščanje moči "Excel" preglednico

Regresijska analiza v Excelu je treba opraviti z uporabo obstoječih podatkov tabele vgrajenih funkcij. Vendar pa je za te namene je bolje uporabiti zelo koristen dodatek "analize paketov". Če jo želite omogočiti, morate:

• z zavihku "File" iti na "Nastavitve";
• V oknu, ki se odpre, izberite "Add-ons";
• kliknite na gumb "Go", ki se nahaja v spodnjem desnem kotu vrstice "upravljanja";
• dal kljukico na "Analiza ToolPak" in potrdite dejanje s pritiskom na tipko "OK".

Če je to narejeno pravilno, na desni strani v zavihku "Data", ki se nahaja nad delovnega lista "Excel", kaže na želeni gumb.

Linearna regresija v Excelu

Zdaj, da imate pri roki vse potrebne virtualnih orodij za ekonometrične izračune se lahko začnemo obravnavati naš problem. V ta namen:

• gumb kliknili na "analizi podatkov";
• kliknite na gumb "regresije" v odprtem oknu;
• zavihek, ki se pojavi uvesti razpon vrednosti Y (število separacije delavcev) in X (njihova plača);
• ponovno potrjujejo svoje ukrepe, s pritiskom na gumb «OK».

Kot rezultat, bo program samodejno izpolnite nov list preglednice podatkov regresijska analiza. Bodite pozorni! V Excelu, obstaja možnost, da določi mesto, ki vam je ljubše za ta namen. Na primer, lahko enaki stanja, kjer se vrednosti Y in X ali celo nova knjiga, posebej za shranjevanje teh podatkov.

Rezultati Regresijska analiza za R-kvadrat

Podatki, Excel, pridobljeni v obravnavanih primer podatkih ima obliko:

Najprej moramo biti pozorni na vrednost R-kvadrat. Predstavlja koeficient določanja. V tem primeru je R-kvadrat = 0,755 (75,5%), m. E. izračunani parametri modela pojasniti odnos med parametri, ki jih 75,5% šteje. Čim višja je vrednost koeficienta določanja se bodo izbrani vzorec šteje za bolj koristno za določene naloge. Domneva se, da pravilno opisujejo realno stanje na R-kvadrat vrednost nad 0,8. Če je R-kvadrat <0,5, nato pa regresijska analiza v Excelu ni mogoče šteti za razumno.

analiza razmerja

Število 64,1428 kaže, kaj se bo vrednost Y, če bo vse spremenljivke xi v našem modelu ponastaviti. Z drugimi besedami, lahko trdimo, da je vrednost analiziranega parametra vplivajo tudi drugi dejavniki, kot so opisani v posebnem modelu.

Naslednji dejavnik -,16285 nahaja v celici B18, prikazuje pomemben vpliv spremenljivke X Y. To pomeni, da je povprečna plača zaposlenih v modelu vpliva na število odstopov od teže -0.16285, t. E. Stopnja njenega vpliva na vse majhna. Znak "-" pomeni, da je koeficient negativen. Očitno je, saj vsi vemo, da več plača v podjetju, so manj ljudje izrazili željo, da prekine pogodbo o zaposlitvi ali zavrne.

multipla regresija

Pod tem pojmom se nanaša na komunikacijo enačbo z več neodvisnimi spremenljivkami v obliki:

y = f (x ₁ + x ₂ + ... x _m) + ε, kjer je Y - je funkcija ocena (odvisne spremenljivke) in _{X1, X2,} ... x _m - znaki faktorji (neodvisne spremenljivke).

ocenjevanje parametrov

Za multiple regresije (MR) pa izvedemo z uporabo metode najmanjših kvadratov (LSM). Za linearnih enačb v obliki Y = A + B ₁ x _{1 + ... + b m x m +} ε izgradnji sistema normalnih enačb (cm. Spodaj)

Da bi razumeli načela metode, menimo, da je primer dva faktorja. Potem smo se razmere po formuli opisal

Tako dobimo:

kjer je σ - je varianca ustrezne funkcije, ki se kaže v indeksu.

MNC se uporablja za enačba MR da standartiziruemom lestvice. V tem primeru smo dobili enačbo:

kjer je t _y, t _{x 1, ...} t _XM - standartiziruemye spremenljivk, za katere so povprečne vrednosti 0; ß _i - standardiziranih regresijskih koeficientov in standardni odklon - 1.

Upoštevajte, da vse ß _i v tem primeru definirana kot normalizirani in tsentraliziruemye, zato primerjava med položajema a kot veljaven in sprejemljiv. Poleg tega je bilo sprejeto, da se opravi pregled dejavnikov, zavržejo tiste, ki imajo najnižje vrednosti βi.

Problem s pomočjo linearne regresijske enačbe

Recimo, da imate tabelo dinamike cene posameznega izdelka N v zadnjih 8 mesecih. Zato je treba odločiti, ali pridobitev njegove stranke po ceni 1850 rubljev. / T.

		B	C
1	mesec	ime meseca	Cena N
2	1	januar	1750 rubljev na tono
3	2	februar	1755 rubljev na tono
4	3	marec	1767 rubljev na tono
5	4	april	1760 rubljev na tono
6	5	maj	1770 rubljev na tono
7	6	junij	1790 rubljev na tono
8.	7	julij	1810 rubljev na tono
9.	8.	avgust	1840 rubljev na tono

Da bi rešili ta problem, v tabelarni procesor "Excel", potrebne za uporabo že znane na primer orodje "Data Analysis" zgoraj predstavljena. Nato izberite "regresije" poglavje in določene parametre. Ne smemo pozabiti, da je v "mora območju Vhodni Y" se je predstavil na območju vrednosti odvisne spremenljivke (v tem primeru je cena blaga v določenih mesecih leta) in v "Input interval X» - za neodvisno (mesec). Potrjujemo dejanje s klikom na "V redu". V novem delovnem listu (če je to navedeno), dobimo podatke za regresijo.

Gradimo na njih linearno enačbo obliki y = ax + b, kjer so kot parametrov a in b koeficienti iz številko vrstice v mesecu in ime koeficientov in linije «Y-križišču" lista z rezultati regresijske analize. Tako lahko linearne regresijske enačbe (EQ) 3 za problem zapišemo kot:

Cena blaga N = 11.714 * 1727,54 mesec številka +.

ali algebrske zapisu

y = 11.714 x + 1727,54

Analiza rezultatov

Za odločitev, ali je prejel ustrezno linearno regresijsko enačbo z uporabo več korelacijske koeficiente (CMC) in odločnost, pa tudi preizkus in Fisherjevega t-testa. V tabeli "Excel" regresije z rezultati so delovali pod imenom multipla R, R-kvadrat, F-t-statistike in statistike, oz.

KMC R omogoča oceno bližino verjetnostno razmerje med neodvisnimi in odvisnimi spremenljivkami. Visoka vrednost nakazuje močne povezave med spremenljivke "Število meseca" in "N izdelku v rubljih na 1 tono." Vendar pa je narava tega odnosa ni znan.

Kvadratni koeficienta določanja ^R2 (RI) je številčno značilnost delež celotnega razprševanja in kaže odbojev eksperimentalnih podatkov odseka, t.j. vrednosti odvisne spremenljivke, ki ustreza linearne regresijske enačbe. V ta problem, je ta vrednost 84,8%, tal. E. Statistika z visoko stopnjo natančnosti so opisane SD.

F-statistika, znan tudi kot Fisher merila uporabijo za oceno pomembnosti linearne odvisnosti ali ovrgla hipotezo potrjuje njen obstoj.

Vrednost t-statistike (Studentov t test) pomaga oceniti pomen koeficienta v vsakem prostem neznani linearni člana odvisnosti. Če je vrednost t-testa> T _{cr se} domneva linearno enačbo marginalizaciji prosti čas zavrne.

Na ta problem za brezplačno izraza prek instrumentov "Excel" je bilo ugotovljeno, da je t = 169,20903 in p = 2,89E-12, t. E. imeti ničelno verjetnost, da se bo zvesti zavrnil hipotezo o nepomembnosti prostega izraza. Za neznanega koeficientom pri t = 5,79405 in p = 0,001158. Z drugimi besedami, je verjetnost, da bo zavrnjen pravilna hipoteza nepomembnosti koeficienta za neznano, je 0,12%.

Tako je mogoče trditi, da se pridobljeni linearno regresijsko enačbo ustrezno.

Problem smotrnosti nakupa delnic

Multipla regresija je bila izvedena v Excelu z uporabo iste "Analiza podatkov" orodje. Razmislite posebno vlogo.

Vodnik podjetje «NNN» se mora odločiti, ali za nakup 20% delnic JSC "MMM". Cena paketa (SP) je 70 milijonov ameriških dolarjev. Strokovnjaki «NNN» zbrali podatke o podobnih poslov. Odločeno je bilo, da ocenijo vrednost delnic na teh parametrih, izražena v milijonih ameriških dolarjev, kot so:

• obveznosti (VK);
• Obseg letni promet (VO);
• terjatve (VD);
• vrednost osnovnih sredstev (SOF).

Poleg tega se uporabljajo dolgove plač podjetij (V3 u) tisoč ameriških dolarjev.

Odločitev namizni procesor Excel sredstva

Najprej morate ustvariti tabelo vhodnih podatkov. To je, kot sledi:

Naprej:

• škatla klic "analiza podatkov";
• Izbrani del "Regresijska";
• okno "vhodne interval Y» dajemo razpon odvisno spremenljivko vrednosti iz stolpca G;
• kliknite na ikono z rdečo puščico na desni strani okna "Vhodni interval X» in izoliramo na območju stanja vse vrednosti stolpca B, C, D, F.

Mark točka "Nova lista« in kliknite »V redu«.

Get regresijska analiza za to nalogo.

Rezultati študije in zaključki

"Zbiranje" zaokroženo iz podatkov zgoraj predstavljenih na list tabele Excel procesor regresijsko enačbo:

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

V bolj običajni matematični obliki, ga lahko zapišemo kot:

y = 0103 * x1 + 0541 * X2 - 0031 * x3 + 0405 * X4 + 0691 * X5 - 265.844

Podatki za "MMM» JSC predstavljeni v spodnji tabeli:

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

jih ob zamenjavi v regresijsko enačbo, dobimo številko od 64.72 milijonov ameriških dolarjev. To pomeni, da so delnice JSC "MMM» ne bi kupil, ker je njihova cena precej precenjene na 70 milijonov ameriških dolarjev.

Kot lahko vidite, je uporaba preglednice »Excel« in regresijska enačba dovoliti, da informirano odločitev o smotrnosti precej specifično transakcijo.

Zdaj veste, kaj je regresija. Primeri za Excel, opisani zgoraj, vam bo pomagal pri reševanju praktičnih problemov ekonometrije.