Regression i Excel: ekvationsexempel. linjär regression

Regressionsanalys - en statistisk studie metod för att visa beroendet av en parameter hos en eller flera oberoende variabler. I pre-dataåldern, har dess användning varit ganska svårt, särskilt när det kom till stora mängder data. Idag, lära sig hur man bygger en regression i Excel, kan du lösa komplexa statistiska problem på bara några minuter. Nedan är specifika exempel på ekonomi.

regressionstyper

Detta koncept infördes till matematik av Francis Galton 1886. Regression är:

• linjär;
• parabolisk;
• effekt;
• exponentiell;
• hyperbolisk;
• exponentiell;
• logaritmisk.

EXEMPEL 1

Betrakta problemet att bestämma beroendet av antalet avgångar för anställda av den genomsnittliga lönen i 6 industriföretag.

Uppgift. Sex företag har analyserat den genomsnittliga månadslönen och antalet anställda som slutar frivilligt. I tabellform har vi:

	EN	B	C
1	X	Antal avgångar	lön
2		y	30000 rubel
3	1	60	35000 rubel
4	2	35	40000 rubel
5	3	20	45000 rubel
6	4	20	50.000 rubel
7	5	15	55000 rubel
8	6	15	60000 rubel

För problemet att bestämma beroendet av de belopp separationer arbetarna från den genomsnittliga lönen för 6 företag regressionsmodell har formen av ekvationen Y = en ₀ + en ₁ x ₁ + ... + a _k x _k, där x _i - påverkande variabler, en _i - regressionskoefficienter, ak - antal faktorer.

Y för en given uppgift - det är en indikator för att skjuta en anställd, en bidragande faktor - lönen, som betecknas med X.

Utnyttja kraften i "Excel" kalkyl

Regressionsanalys i Excel bör föregås av en ansökan till den befintliga tabelldata inbyggda funktioner. Men det är för dessa ändamål bättre att använda en mycket användbar add-in "paket analys". För att aktivera den, måste du:

• med fliken "File" gå till "Inställningar";
• i fönstret som öppnas väljer du 'Add-ons';
• klicka på knappen "Go", som ligger längst ner till höger på raden "management";
• sätta en bock bredvid "Analysis ToolPak" och bekräfta åtgärden genom att trycka på "OK".

Om det görs på rätt sätt, den högra sidan av "Data" fliken, som ligger ovanför arbetsblad "Excel", visar på önskad knapp.

Linear Regression i Excel

Nu när du har på handen alla nödvändiga virtuella verktyg för ekonometriska beräkningar kan vi börja ta itu med våra problem. Gör så här:

• knappen klickas på "Data Analysis";
• klicka på knappen "regression" i det öppna fönstret;
• en flik som verkar för att införa ett område av värden Y (antalet separations arbetare) och X (lönen);
• bekräfta sina handlingar genom att trycka på «OK» -knappen.

Som ett resultat av detta kommer programmet automatiskt fylla det nya arket kalkyluppgifter regressionsanalys. Var uppmärksam! I Excel, finns det en möjlighet att ställa in den plats som du föredrar för detta ändamål. Till exempel, kan det vara samma ark, där värdena Y och X, eller ens en ny bok, särskilt utformad för lagring av sådana data.

Regressionsanalysresultat för R-kvadrat

Excel-data som erhållits i de betraktade exempeldata har formen:

Först av allt bör vi vara uppmärksamma på att värdet på R-kvadrat. Den representerar determinationskoefficienten. I detta exempel, R-kvadrat = 0,755 (75,5%), m. E. De beräknade parametrarna för modellen för att förklara sambandet mellan parametrarna behandlas av 75,5%. Ju högre värde för koefficienten för bestämning, är den valda modellen anses vara mer användbart för särskilda uppgifter. Man tror att korrekt beskriva den verkliga situationen vid R-kvadrat värde över 0,8. Om R-kvadrat <0,5, då kan inte betraktas som en regressionsanalys i Excel rimligt.

analysera förhållandet mellan

Nummer 64,1428 visar vad som kommer att värdet av Y, om alla variabler xi i vår modell kommer att återställas. Med andra ord, kan det hävdas att värdet av den analyserade parametern påverkas av andra faktorer än de som beskrivs i den specifika modellen.

Nästa faktor -0,16285 belägen i cell B18, visar det viktiga inflytandet av variabel X till Y. Detta innebär att den genomsnittliga lönen för anställda inom modellen påverkar antalet utträden från vikten av -0,16285, t. E. den graden av dess inverkan alls liten. Tecknet "-" indikerar att koefficienten är negativ. Det är uppenbart, eftersom vi alla vet att ju mer lön i företaget, har färre människor uttryckt en önskan om att säga upp anställningsavtalet eller ogillas.

multipel regression

Inom ramen för denna term hänvisar till kommunikations ekvation med flera oberoende variabler av formen:

y = f (x ₁ + x ₂ + ... x _m) + ε, där y - är en funktion score (den beroende variabeln), och x _1, x _2, ... x _m - är tecken faktorer (oberoende variabler).

parameterskattning

För multipel regression (MR) det utförs med användning av en minstakvadratmetoden (LSM). För linjära ekvationer på formen Y = a + b ₁ x _{1 + ... + b m x m +} ε bygga ett system av normala ekvationerna (cm. Nedanför)

För att förstå principen för metoden, anser vi två-faktor fallet. Sedan har vi den situation som beskrivs av formeln

Därför får vi:

där σ - är variansen för respektive funktion återspeglas i indexet.

MNC är tillämplig på ekvationen MR att standartiziruemom skala. I det här fallet får vi ekvationen:

vari t _y, t _{x 1, ...} t _xm - standartiziruemye variabler för vilka medelvärdena är 0; p _i - standardiserade regressionskoefficienter och standardavvikelse - 1.

Observera att alla p _jag i detta fall definieras som den normaliserade och tsentraliziruemye, därför en jämförelse mellan en anses giltig och godtagbar. Dessutom är det accepterat att utföra screening av faktorer, kasta de som har de lägsta värdena av βi.

Problemet med att använda linjära regressionsekvationen

Anta att du har en tabell över dynamiken i priset på en viss produkt N under de senaste 8 månaderna. Det är nödvändigt att besluta om förvärv av hans parti till priset av 1850 rubel. / T.

	EN	B	C
1	månaden	namn i månaden	pris N
2	1	januari	1750 rubel per ton
3	2	februari	1755 rubel per ton
4	3	mars	1767 rubel per ton
5	4	april	1760 rubel per ton
6	5	maj	1770 rubel per ton
7	6	juni	1790 rubel per ton
8	7	juli	1810 rubel per ton
9	8	augusti	1840 rubel per ton

För att lösa detta problem i tabell processor "Excel" krävs för att använda redan kända exempelvis funktionen "Data Analysis" som presenteras ovan. Nästa, välj "Regression" sektionen och ställa in parametrar. Vi måste komma ihåg att i "Y» Inmatningsområde bör införas för att ett intervall av värden av den beroende variabeln (i detta fall priset på varorna i specifika månader) och i "Input intervallet X» - för en självständig (månad). Vi bekräftar åtgärden genom att klicka på «OK». I ett nytt kalkylblad (om så anges), vi får data för regression.

Vi bygger på dem linjär ekvation av formen y = ax + b, där som den parametrarna a och b är koefficienterna från linjenumret för den månaden och namnet på de koefficienter och «Y-korsning" linje av arket med resultaten av regressionsanalys. Sålunda kan den linjära regressionsekvationen (EQ) 3 för problemet skrivas som:

Priset på varor N = 11.714 * 1727,54 månader nummer +.

eller i algebraisk notation

y = 11.714 x + 1727,54

analys av resultaten

Att besluta huruvida den mottagna adekvat linjära regressionsekvationen med användning av de multipla korrelationskoefficienterna (CMC) och bestämning samt testet och Fishers t-test. I tabellen "Excel" regression med de resultat de verkar under namnen multipel R, R-Square, F-t-statistik och statistik, respektive.

KMC R gör det möjligt att uppskatta hur nära probabilistisk förhållandet mellan oberoende och beroende variabler. Dess höga värde indikerar en tillräckligt stark förbindning mellan variabeln "Antal månaden" och "N Produkt priset i rubel per 1 ton." Det är dock okänd natur detta förhållande.

Kvadraten på determinationskoefficienten R ² (RI) är en numerisk egenskap hos den andel av totala scatter och visar ett punkt av experimentell datadelen, d.v.s. värden av den beroende variabeln som motsvarar en linjär regressionsekvation. I detta problem, är detta värde 84,8%, smp. E. Statistik med en hög grad av noggrannhet erhålls beskrivs SD.

F-statistik, även kända som Fisher kriterium som används för att bedöma betydelsen av det linjära beroendet eller disproving hypotes som bekräftar dess existens.

Värdet på t-statistik (Students t-test) hjälper utvärdera betydelsen av koefficienten vid någon fri okänt linjärt beroende medlem. Om värdet på t-test> t _cr, är hypotesen om en linjär ekvation obetydlighet av fri sikt avvisas.

I detta problem för en fri sikt genom instrument "Excel" det konstaterades att t = 169,20903, och p = 2,89E-12, t. E. Ha en noll sannolikhet att de troende kommer att avvisas hypotesen om obetydlighet av den fria sikt. För okända koefficient vid t = 5,79405, och p = 0,001158. Med andra ord, sannolikheten att en avvisade riktig hypotes kommer obetydlighet koefficienten för det okända, är 0,12%.

Sålunda, kan det hävdas att det erhållna linjära regressionsekvationen adekvat sätt.

Problemet med det är lämpligt att köpa aktier

Multipel regression utfördes i Excel använder samma "Data Analysis" verktyg. Betrakta den specifika applikationen.

Guide företag «NNN» måste besluta om att köpa 20% av aktierna i JSC «MMM». Paketpris (SP) är 70 miljoner dollar. Specialister på «NNN» insamlade data om liknande transaktioner. Det beslutades att bedöma värdet av aktier på sådana parametrar, uttryckt i miljoner US-dollar, till exempel:

• skulder (VK);
• årlig omsättning volym (VO);
• fordringar (VD);
• värdet av anläggningstillgångar (SOF).

Dessutom använder löne skulder företag (V3 U) i tusentals dollar.

Beslutet bord processor Excel medel

Först måste du skapa en tabell med indata. Det är som följer:

Nästa:

• call box "dataanalys";
• valda avsnittet "Regression";
• fönstret "Ingångsintervall Y» administreras avståndsberoende variabelvärden från kolumn G;
• klicka på ikonen med en röd pil till höger i fönstret "Input intervallet X» och isoleras på ett ark intervall av alla värden på kolumn B, C, D, F.

Markera punkten "Ny kalkylblad" och klicka på "Ok".

Få en regressionsanalys för denna uppgift.

Studieresultaten och slutsatser

"Collect" avrundade från de data som presenteras ovan på arket Schema Excel processor regressionsekvation:

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

I de mer vanliga matematiska formen kan det skrivas som:

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

Data för «MMM» JSC presenteras i tabellen nedan:

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

Ersätta dem i regressionsekvationen, erhålls en siffra på 64,72 miljoner dollar. Detta innebär att aktier i JSC «MMM» inte ska köpa, eftersom deras kostnad är ganska dyr på 70 miljoner dollar.

Som ni kan se, användning av kalkylprogram "Excel" och regressionsekvationen får fatta ett välgrundat beslut om huruvida det är lämpligt ganska specifik transaktion.

Nu vet du vad en regression. Exempel på Excel, som diskuterats ovan, kommer att hjälpa dig att lösa praktiska problem ekonometri.