Regression i Excel: ligningen eksempler. lineær regression

Regressionsanalyse - en statistisk undersøgelse metode til at vise afhængighed af en parameter af en eller flere uafhængige variabler. I den præ-computer æra, har været temmelig vanskeligt dens brug, især når det kom til store mængder af data. I dag, at lære at bygge en regression i Excel, kan du løse komplekse statistiske problemer på blot et par minutter. Nedenfor er konkrete eksempler på økonomi.

regression typer

Dette koncept blev introduceret til matematik af Francis Galton i 1886. Regression er:

• lineær;
• parabolsk;
• magt;
• eksponentiel;
• hyperbolske;
• eksponentiel;
• logaritmisk.

EKSEMPEL 1

Betragt problemet med at bestemme afhængigheden af antallet af opsigelser af medarbejdere ved den gennemsnitlige løn i de 6 industrivirksomheder.

Opgave. Seks virksomheder har analyseret den gennemsnitlige månedsløn og antallet af medarbejdere, der holder op frivilligt. I tabelform har vi:

	En	B	C
1	X	Antal fratrædelser	løn
2		y	30000 rubler
3	1	60	35000 rubler
4	2	35	40000 rubler
5	3	20	45000 rubler
6	4	20	50.000 rubler
7	5	15	55000 rubler
8	6	15	60000 rubler

På problemet med bestemmelse af afhængigheden af det beløb separationer arbejdere fra den gennemsnitlige løn for 6 virksomheder regressionsmodel har form som ligning Y = en ₀ + en ₁ x ₁ + ... + et _k x _k, hvor x _i - påvirker variabler, en _i - regressionskoefficienter, ak - række faktorer.

Y til en given opgave - det er en indikator til at fyre en medarbejder, en medvirkende faktor - løn, der er betegnet med X.

Udnyttelse magt "Excel" regneark

Regressionsanalyse i Excel bør indledes med en ansøgning til de eksisterende tabeldata indbyggede funktioner. , Til disse formål er det bedre at bruge en meget nyttig add-in "packet analyse". For at aktivere det, skal du:

• med fanen "Filer" gå til "Indstillinger";
• i vinduet, der åbnes, skal du vælge 'Add-ons';
• klik på knappen "Go", placeret nederst til højre på linjen "forvaltning";
• sætte et flueben ud for "Analysis ToolPak", og bekræft din handling ved at trykke på "OK".

Hvis det gøres korrekt, i højre side af fanen "Data", placeret oven på arbejde ark "Excel", viser den ønskede knap.

Lineær regression i Excel

Nu hvor du har på hånden alle de nødvendige virtuelle værktøjer til økonometriske beregninger, kan vi begynde at løse vores problem. For at gøre dette:

• knappen er klikket på "Data Analysis";
• klik på knappen "regression" i det åbne vindue;
• en fane, der ser ud til at indføre en række værdier Y (antallet af separationer arbejdere) og X (deres løn);
• bekræfter deres handlinger ved at trykke på «OK» knap.

Som et resultat, vil programmet automatisk udfylde det nye ark regneark data regressionsanalyse. Vær opmærksom! I Excel, er der en mulighed for at sætte det sted, du foretrækker til dette formål. For eksempel kan det være det samme ark, hvor værdierne Y- og X, eller endda en ny bog, specielt designet til opbevaring af sådanne data.

Regression Analyseresultaterne for R-square

Excel data opnået i de betragtede eksempel data have formen:

Først og fremmest skal vi være opmærksomme på værdien af R-kvadreret. Den repræsenterer determinationskoefficienten. I dette eksempel R-kvadrat = 0,755 (75,5%), m. E. De beregnede modellens parametre til at forklare forholdet mellem de betragtede parametre med 75,5%. Jo højere værdien af determinationskoefficienten, er den valgte model for at være mere egnede til særlige opgaver. Det menes at beskrive den reelle situation korrekt i R-square værdi over 0,8. Hvis F-kvadrat <0,5, så kan ikke anses for rimelig regressionsanalyse i Excel.

nøgletalsanalyse

Nummer 64,1428 viser, hvad der vil være værdien af Y, hvis alle variablerne xi i vores model vil blive nulstillet. Med andre ord kan det hævdes, at værdien af den analyserede parameter påvirkes af andre faktorer end dem, der er beskrevet i den specifikke model.

Den næste faktor -0,16285 placeret i celle B18, viser den vigtige indflydelse variabel X til Y. Dette betyder, at den gennemsnitlige løn af medarbejdere inden for modellen påvirker antallet af fratrædelser fra vægten af -0,16285, t. E. Graden af dens indvirkning på alle lille. Tegnet "-" angiver, at koefficienten er negativ. Det er indlysende, da vi alle ved, at jo mere løn i virksomheden, har de færre mennesker udtrykt ønske om at opsige ansættelseskontrakten eller afvist.

multipel regression

Under dette udtryk henviser til meddelelse ligning med flere uafhængige variable af formen:

y = f (x ₁ + x ₂ + ... x _m) + ε, hvor y - er en funktion score (den afhængige variabel), og x _1, x _2, ... x _m - er signs faktorer (uafhængige variabler).

parameterestimering

For multipel regression (MR) det udføres under anvendelse af en mindste kvadraters metode (LSM). For lineære ligninger af formen Y = a + b ₁ x _{1 + ... + b m x m +} ε opbygge et system af normale ligninger (cm. Nedenfor)

For at forstå princippet om metoden, vi betragter to-faktor sagen. Så har vi den situation, der beskrives ved formlen

Derfor får vi:

hvor σ - er variansen af den pågældende funktion, afspejles i indekset.

MNC er anvendelig til ligningen MR til standartiziruemom skala. I dette tilfælde får vi ligningen:

hvor t _y, t _{x 1, ...} t _xm - standartiziruemye variabler, for hvilke middelværdier er 0; p _i - standardiserede regressionskoefficienter og standardafvigelse - 1.

Bemærk, at alle P _jeg i dette tilfælde defineret som den normaliserede og tsentraliziruemye derfor en sammenligning mellem en anses for gyldige og acceptable. Desuden er det accepteret at udføre screening af faktorer, kassere dem, der har de laveste værdier af βi.

Problemet med anvendelse af lineær regressionsligning

Antag, at du har en tabel over dynamikken i prisen på et bestemt produkt N i de sidste 8 måneder. Det er nødvendigt at afgøre, om købet af hans parti til en pris af 1850 rubler. / T.

	En	B	C
1	måneden	navnet på måneden	pris N
2	1	januar	1750 rubler per ton
3	2	februar	1755 rubler per ton
4	3	marts	1767 rubler per ton
5	4	april	1760 rubler per ton
6	5	maj	1770 rubler per ton
7	6	juni	1790 rubler per ton
8	7	juli	1810 rubler per ton
9	8	august	1840 rubler per ton

For at løse dette problem i tabelform processor "Excel" forpligtet til at bruge allerede kendt for eksempel værktøj "Dataanalyse" præsenteret ovenfor. Dernæst skal du vælge "Regression" sektionen og indstille parametre. Vi må huske på, at i "Y» Indlæseområde bør indføres for en række værdier af den afhængige variabel (i dette tilfælde prisen på varerne i bestemte måneder af året), og i "Input interval X» - for en uafhængig (måneden). Vi bekræfte handlingen ved at klikke på «OK». I et nyt regneark (hvis det fremgår), får vi data for regressionen.

Vi bygger på dem lineær ligning af formen y = ax + b, hvor som parametre a og b er koefficienter fra den linje antallet af måneden og navnet på koefficienterne og «Y-kryds" linje af arket med resultaterne af regressionsanalyse. Således kan den lineære regressionsligning (EQ) 3 for problemet skrives som:

Prisen på varer N = 11.714 * 1727,54 måned nummer +.

eller i algebraisk notation

y = 11.714 x + 1727,54

analyse af resultater

At afgøre, om den modtagne tilstrækkeligt lineære regressionsligning under anvendelse af de multiple korrelationskoefficienter (CMC) og bestemmelse samt test og Fishers t-test. I tabellen "Excel" regression med de resultater, de virker under navnene multiple R, R-Square, F-t-statistik og statistik, henholdsvis.

KMC R gør det muligt at estimere nærhed probabilistisk Forholdet mellem uafhængige og afhængige variable. Dens høje værdi indikerer en stærk nok sammenhæng mellem variablen "antal måneden" og "N Produkt pris i rubler per 1 ton." Men karakteren af dette forhold er ukendt.

Kvadratet på determinationskoefficienten ^R2 (RI) er en numerisk karakteristisk for andelen af det totale scatter og viser en scatter af eksperimentelle del data, dvs. værdier af den afhængige variabel svarer til en lineær regressionsligning. I dette problem er denne værdi 84,8%, smp. E. Statistik med en høj grad af nøjagtighed, der opnås, er beskrevet SD.

F-statistik, også kendt som Fisher kriterium der anvendes til at vurdere betydningen af den lineære afhængighed eller modbevise hypotesen bekræftede sin eksistens.

Værdien af t-statistik (Students t-test) hjælper vurdere betydningen af koefficienten på enhver fri ukendt lineær afhængighed medlem. Hvis værdien af t-test> t _cr, er hypotesen om en lineær ligning ubetydelighed af fri sigt afvist.

I denne opgave for en gratis sigt gennem instrumenter "Excel" blev det konstateret, at t = 169,20903, og p = 2,89E-12, t. E. Har en nul sandsynlighed for, at de troende vil blive afvist hypotesen om ubetydelighed af den frie sigt. For ukendt koefficient ved t = 5,79405, og p = 0,001158. Med andre ord, sandsynligheden for, at en afvist korrekt hypotese vil ubetydelighed af koefficienten for det ukendte, er 0,12%.

Således kan det hævdes, at det opnåede lineære regressionsligning tilstrækkeligt.

Problemet med det hensigtsmæssige i at købe aktier

Multipel regression blev udført i Excel ved hjælp af den samme "Data Analysis" værktøj. Overvej den specifikke anvendelse.

Guide selskab «NNN» skal beslutte, om at købe 20% af aktierne i JSC «MMM». Pakke pris (SP) er 70 millioner dollars. Specialister af «NNN» indsamlede data om lignende transaktioner. Det blev besluttet at vurdere værdien af aktier på sådanne parametre, udtrykt i millioner US-dollars, såsom:

• gæld (VK);
• årlig omsætning volumen (VO);
• tilgodehavender (VD);
• værdi af anlægsaktiver (SOF).

Desuden bruger løn gæld virksomheder (V3 U) i tusindvis af dollars.

Beslutningen tabel processor Excel midler

Først skal du oprette en tabel med input-data. Det er som følger:

Næste:

• telefonboks "dataanalyse";
• valgte sektion "Regression";
• vinduet "Input interval Y» administreret range afhængige variable værdier fra kolonne G;
• klikke på ikonet med en rød pil til højre for vinduet "Input interval X» og isoleret på et ark område af alle værdier af kolonne B, C, D, F.

Markér punktet "Ny regneark" og klik "Ok".

Få en regressionsanalyse til denne opgave.

resultater og konklusioner Undersøgelsen

"Collect" rundet af dataene vist ovenfor på arkbordet Excel processor regressionsligningen:

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

I den mere almindelige matematiske form, kan det skrives som:

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

Data for «MMM» JSC præsenteret i tabellen nedenfor:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102,5	535,5	45.2	41,5	21,55	64.72

Substitution dem i regressionsligningen, opnåede et tal på 64.72 millioner dollars. Det betyder, at aktierne i JSC «MMM» ikke bør købe, fordi deres omkostninger er helt overpris på 70 millioner dollars.

Som du kan se, brug af regneark "Excel" og regressionsligningen lov til at træffe en informeret beslutning om det hensigtsmæssige ganske specifikke transaktion.

Nu ved du, hvad en regression. Eksempler til Excel, diskuteret ovenfor, vil hjælpe dig med at løse praktiske problemer med økonometri.