Regression i Excel: ekvation, exempel. Linjär regression

Regressionsanalys är en statistisk teknikforskning som låter dig visa beroendet av en parameter på en eller flera oberoende variabler. Under tiden före datoren var dess applikation ganska svår, särskilt när det gäller stora mängder data. Idag, efter att ha lärt dig hur man bygger en regression i Excel, kan du lösa komplexa statistiska problem på bara några minuter. Nedan följer specifika exempel från det ekonomiska området.

Regressionstyper

Själva konceptet introducerades i matematik av Francis Galton 1886. Regression händer:

• linjär;
• parabolisk;
• makt-lag;
• exponentiell;
• hyperbolisk;
• indikativ;
• logaritmisk.

Exempel 1

Låt oss överväga problemet med att avgöra beroendet av antalet anställda som slutar med sin genomsnittliga lön vid 6 industriföretag.

En uppgift. Sex företag analyserade den genomsnittliga månadslönen och antalet anställda som slutade frivilligt. I tabellform har vi:

För problemet med att bestämma beroendet av antalet anställda som slutar med genomsnittslönen vid 6 företag har regressionsmodellen formen av ekvationen Y = a₀ + a₁med₁ + ... + a_tillmed_tilldär x_och - påverkar variabler, a_och Är regressionskoefficienterna, och k är antalet faktorer.

För denna uppgift är Y en indikator på anställda som slutar, och den påverkande faktorn är lönen som vi betecknar med X.

Med hjälp av Excel-bordsprocessorn

Regressionsanalys i Excel måste föregås avTillämpa inbyggda funktioner på befintlig tabelldata. För dessa ändamål är det dock bättre att använda det mycket användbara tillägget "Analyspaket". För att aktivera det behöver du:

• från fliken "Arkiv" gå till avsnittet "Parametrar";
• i fönstret som öppnas väljer du raden "Tillägg";
• klicka på "Gå" -knappen nedan till höger om "Kontroll" -raden;
• markera bredvid namnet "Analyspaket" och bekräfta dina åtgärder genom att klicka på "OK".

Om allt är gjort korrekt visas den önskade knappen till höger på fliken "Data", ovanför "Excel" -kalkylbladet.

Linjär regression i Excel

Nu när vi har alla nödvändiga virtuella verktyg för att genomföra ekonometriska beräkningar kan vi börja lösa vårt problem. För detta:

• klicka på knappen "Dataanalys";
• i fönstret som öppnas klickar du på "Regression" -knappen;
• i fliken som visas anger du värdet för Y (antalet anställda som slutar) och för X (deras löner);
• vi bekräftar våra handlingar genom att trycka på "Ok" -knappen.

Som ett resultat kommer programmet automatiskt att fyllas iett nytt kalkylark med regressionsanalysdata. Notera! Excel har förmågan att självständigt definiera den plats du föredrar för detta ändamål. Det kan till exempel vara samma ark som innehåller Y- och X-värdena, eller till och med en ny arbetsbok som är särskilt utformad för att lagra sådan data.

Analysera regressionsresultat för R-torget

I Excel är de uppgifter som erhållits under behandlingen av uppgifterna i exemplet i fråga:

Först och främst bör du vara uppmärksam påR-kvadratvärde. Det representerar bestämningskoefficienten. I detta exempel förklarar R-kvadrat = 0,755 (75,5%), det vill säga modellens beräknade parametrar förhållandet mellan de betraktade parametrarna med 75,5%. Ju högre värde på bestämningskoefficienten, desto mer anses den valda modellen vara mer användbar för en specifik uppgift. Man tror att det korrekt beskriver den verkliga situationen när R-kvadratvärdet är över 0,8. Om R-kvadraten är <0,5 kan en sådan regressionsanalys i Excel inte anses rimlig.

Oddsanalys

Siffran 64,1428 visar vad värdet på Y kommer att vara,om alla variabler xi i modellen vi överväger är noll. Med andra ord kan det hävdas att värdet på den analyserade parametern påverkas av andra faktorer som inte beskrivs i en viss modell.

Nästa koefficient är -0,16285, belägen icell B18, visar betydelsen av påverkan av variabeln X på Y. Detta innebär att den genomsnittliga månadslönen för anställda inom den aktuella modellen påverkar antalet kvittrar med vikten -0,16285, det vill säga graden av dess inflytande är ganska liten. Ett “-” -tecken indikerar att koefficienten är negativ. Detta är uppenbart, eftersom alla vet att ju högre lön på företaget desto färre människor uttrycker en önskan att säga upp anställningsavtalet eller sluta.

Multipel regression

Denna term förstås som en tvångsekvation med flera oberoende variabler i formen:

y = f (x₁+ x₂+ ... X_m) + ε, där y är den resulterande funktionen (beroende variabel) och x₁, x₂, ... X_m - detta är teckenfaktorer (oberoende variabler).

Parameteruppskattning

För multipel regression (MR) utförs den med metoden för minsta kvadrater (OLS). För linjära ekvationer av formen Y = a + b₁med₁ + ... + b_mmed_m+ ε konstruerar vi ett system med normala ekvationer (se nedan)

För att förstå principen för metoden, överväg tvåfaktorsfallet. Sedan har vi en situation som beskrivs av formeln

Härifrån får vi:

där σ är variansen för motsvarande funktion som återspeglas i indexet.

OLS tillämpas på MR-ekvationen i en standardiserad skala. I det här fallet får vi ekvationen:

där t_y, t_{medett, …}t_xm - standardiserade variabler för vilka medelvärdet är 0; β_och Är de standardiserade regressionskoefficienterna och standardavvikelsen är 1.

Observera att alla β_och i detta fall anges som normaliserade ochcentraliserad, så deras jämförelse anses vara korrekt och giltig. Dessutom är det vanligt att filtrera bort faktorer och kasta dem med de minsta värdena på βi.

Problem med att använda en linjär regressionsekvation

Antag att du har en tabell med prisdynamik för en specifik produkt N under de senaste 8 månaderna. Det är nödvändigt att fatta ett beslut om lämpligheten att köpa hans parti till ett pris av 1850 rubel / ton.

För att lösa detta problem i en bordsprocessorExcel måste använda det dataanalysverktyg som redan är känt från exemplet ovan. Välj sedan avsnittet "Regression" och ställ in parametrarna. Man bör komma ihåg att i fältet "Inmatningsintervall Y" måste du ange ett värdeintervall för den beroende variabeln (i det här fallet priset på produkten under specifika månader av året) och i "Input intervall X "- för den oberoende variabeln (månadens antal). Vi bekräftar åtgärderna genom att klicka på "Ok". På ett nytt ark (om det angavs så) får vi data för regressionen.

Med hjälp av dem konstruerar vi en linjär ekvation av formen y = ax + b, därsom parametrarna a och b är koefficienterna för linjen med namnet på månadsnumret och koefficienterna och raderna "Y-skärning" från arket med resultaten av regressionsanalys. Således skrivs den linjära regressionsekvationen (RB) för problem 3 som:

Produktpris N = 11,714 * månadsnummer + 1727,54.

eller i algebraisk notation

y = 11,714 x + 1727,54

Analys av resultaten

Att avgöra om den erhållna ekvationen är tillräckliglinjär regression, multipel korrelation och bestämningskoefficienter används, liksom Fishers test och Studenttest. I Excel-tabellen med regressionsresultat kallas de för multipel R, R-kvadrat, F-statistik respektive t-statistik.

KMK R gör det möjligt att bedöma täthetendet sannolika förhållandet mellan de oberoende och beroende variablerna. Dess höga värde indikerar ett ganska starkt samband mellan variablerna "Månadsnummer" och "Produktpris N i rubel per 1 ton". Emellertid förblir arten av denna anslutning okänd.

Kvadrat för bestämningskoefficienten R²(RI) är en numerisk egenskapandel av den totala spridningen och visar spridningen av vilken del av experimentdata, dvs. värdena för den beroende variabeln motsvarar den linjära regressionsekvationen. I det aktuella problemet är detta värde 84,8%, dvs statistiska data beskrivs med hög grad av noggrannhet av den erhållna SD.

F-statistiken, även kallad Fisher-testet, används för att bedöma betydelsen av ett linjärt förhållande, motbevisa eller bekräfta hypotesen om dess existens.

Värdet av t-statistiken (Studentens test) hjälper till att bedöma betydelsen av koefficienten med en okänd eller en skärning av ett linjärt förhållande. Om t-testvärdet> t_cr, då förkastas hypotesen om obetydlighet för den fria termen för den linjära ekvationen.

I det aktuella problemet för den fria periodenmed hjälp av Excel-verktygen erhölls att t = 169.20903 och p = 2.89E-12, det vill säga att vi har en noll sannolikhet att den korrekta hypotesen om obetydligheten för den fria termen kommer att avvisas. För koefficienten vid okänd t = 5,79405 och p = 0,001158. Med andra ord är sannolikheten att den korrekta hypotesen om koefficientens obetydlighet med det okända kommer att avvisas är 0,12%.

Således kan det hävdas att den resulterande linjära regressionsekvationen är adekvat.

Problemet med lämpligheten att köpa ett aktieblock

Multipel regression i Excel utförs med samma dataanalysverktyg. Låt oss överväga ett specifikt tillämpat problem.

Ledningen för företaget "NNN" måste beslutaom möjligheten att köpa en andel på 20% i JSC MMM. Kostnaden för paketet (JV) är 70 miljoner US-dollar. NNN-specialister har samlat in data om liknande transaktioner. Det beslutades att utvärdera värdet på aktieblocket med sådana parametrar, uttryckta i miljoner US-dollar, som:

• leverantörsskulder (VK);
• volymen på den årliga omsättningen (VO);
• kundfordringar (VD);
• kostnaden för anläggningstillgångar (SOF).

Dessutom är parametern företagets löneefterskott (V3 P) i tusentals US-dollar.

Excel kalkylbladslösning

Först och främst måste du skapa en tabell med initialdata. Det ser ut så här:

Ytterligare:

• ring fönstret "Dataanalys";
• välj avsnittet "Regression";
• i rutan "Inmatningsintervall Y" matar du in värdena för beroende variabler från kolumnen G;
• klicka på ikonen med en röd pil till höger om fönstret "Inmatningsintervall X" och välj på arket intervallet för alla värden från kolumner B, C, D, F.

Markera objektet "Nytt arbetsblad" och klicka på "Ok".

Få en regressionsanalys för en viss uppgift.

Studie av resultat och slutsatser

Vi "samlar" från de rundade data som presenteras ovan på kalkylarket Excel, regressionsekvationen:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

I en mer bekant matematisk form kan den skrivas som:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Data för JSC "MMM" presenteras i tabellen:

Att ersätta dem med regressionsekvationen får visiffran 64,72 miljoner US-dollar. Detta innebär att aktierna i JSC "MMM" inte ska köpas, eftersom deras värde på 70 miljoner US dollar är ganska överdrivet.

Som du kan se gjorde användningen av Excel-kalkylprocessorn och regressionsekvationen det möjligt att fatta ett välgrundat beslut om lämpligheten för en mycket specifik transaktion.

Nu vet du vad regression är. Exemplen i Excel som diskuterats ovan hjälper dig att lösa praktiska problem inom ekonometri.