Regresjon i Excel: ligningen eksempler. lineær regresjon

Regresjonsanalyse - en statistisk undersøkelsesmetode for å vise avhengigheten av en parameter av en eller flere uavhengige variabler. I pre-dataalderen, har bruken vært ganske vanskelig, spesielt når det kom til store mengder data. I dag, lære å bygge en regresjon i Excel, kan du løse komplekse statistiske problemene i løpet av få minutter. Nedenfor er konkrete eksempler på økonomi.

regresjon typer

Dette konseptet ble introdusert til matematikk av Francis Galton i 1886. Regresjon er:

• lineær;
• parabolske;
• strøm;
• eksponentiell;
• hyperbolsk;
• eksponentiell;
• logaritmisk.

EKSEMPEL 1

Vurdere problemet med å bestemme avhengighet av antall utmeldinger av medlemmer av staben av gjennomsnittlig lønn i de 6 industrielle foretak.

Oppgave. Seks selskaper har analysert gjennomsnittlig månedslønn og antall ansatte som sluttet frivillig. I tabellform har vi:

	En	B	C
1	X	Antall utmeldinger	lønn
2		y	30000 rubler
3	1	60	35000 rubler
4	2	35	40000 rubler
5	3	20	45000 rubler
6	4	20	50000 rubler
7	5	15	55000 rubler
8	6	15	60000 rubler

For problemet med å bestemme avhengigheten av mengden separasjons arbeiderne fra gjennomsnittlig lønn for 6 bedrifter regresjonsmodell har form av ligningen y = a ₀ + en ₁ x ₁ + ... + a _^ x _k, hvor x _i - påvirkende variable a _i - regresjonskoeffisientane, ak - rekke faktorer.

Y for en gitt oppgave - det er en indikator for å avfyre en ansatt, en medvirkende faktor - lønn, som er betegnet med X.

Utnytte kraften av "Excel" regneark

Regresjonsanalyse i Excel bør innledes med en applikasjon til eksisterende tabelldataene innebygde funksjoner. For disse formål er det bedre å bruke en meget nyttig tilleggs "pakke analyse". For å aktivere den, må du:

• med kategorien "File" gå til "Innstillinger";
• i vinduet som åpner seg, velg 'Add-ons';
• klikk på knappen "Go", som ligger nederst til høyre på linjen "management";
• sette en hake ved siden av "Analyseverktøy" og bekreft handlingen din ved å trykke på "OK".

Hvis det gjøres riktig, riktig side av "Data" -fanen, som ligger over arbeidsarket "Excel", viser ønsket knapp.

Lineær regresjon i Excel

Nå som du har på hånden alle nødvendige virtuelle verktøy for økonometriske beregninger, kan vi begynne å ta vårt problem. For å gjøre dette:

• knappen er klikket på "Data Analysis";
• klikk på knappen "regresjon" i det åpne vinduet;
• en fane som ser ut til å innføre en rekke verdier Y (antall separasjoner arbeidere) og X (lønn);
• bekrefter sine handlinger ved å trykke på «OK» -knappen.

Som et resultat, vil programmet automatisk fylle den nye ark i regnearket regresjonsanalyse. Vær oppmerksom! I Excel, er det en mulighet for å sette stedet som du foretrekker for dette formålet. For eksempel kan det være det samme ark, hvor verdiene Y og X, eller til og med en ny bok, er spesielt beregnet på lagring av slike data.

Regresjon analyseresultater for R-kvadrat

Excel-data som oppnås i de vurderte eksempeldata ha formen:

Først av alt, bør vi ta hensyn til verdien av R-squared. Det representerer koeffisienten. I dette eksemplet, R-kvadrat = 0,755 (75,5%), m. E. De beregnede parametere i modellen for å forklare forholdet mellom parametrene vurderes av 75,5%. Jo høyere verdien av koeffisienten, blir den valgte modellen anses å være mer nyttige for spesielle oppgaver. Det antas å riktig beskrive den virkelige situasjon i R-kvadrat-verdi over 0,8. Hvis R-kvadrat <0,5, og deretter en regresjonsanalyse i Excel kan ikke anses som rimelig.

ratio analyse

Antall 64,1428 viser hva vil være verdien av Y, hvis alle variablene xi i vår modell vil bli nullstilt. Med andre ord, kan det hevdes at den verdi av den analyserte parameteren blir påvirket av andre faktorer enn de som er beskrevet i den aktuelle modellen.

Den neste faktor -0,16285 lokalisert i cellen B18, viser betydelig innvirkning av variabel X til Y. Dette betyr at gjennomsnittlig lønn ansatte innenfor modellen påvirker antallet av avgang fra vekten av -0,16285, t. E. Graden av dets innvirkning i det hele tatt liten. Skiltet "-" indikerer at koeffisienten er negativ. Det er åpenbart, siden vi alle vet at jo mer lønn i bedriften, har de mindre folk uttrykt et ønske om å avslutte arbeidsavtalen eller avvist.

multippel regresjon

Under dette begrepet refererer til kommunikasjons ligning med flere uavhengige variable av formen:

y = f (x + ₁ x ₂ + ... x _m) + ε, hvor y - er en funksjon stillingen (den avhengige variable), og _{1 x, 2 x,} ... x _m - er tegn faktorer (uavhengige variabler).

parameterestimering

For multippel regresjon (MR) blir det utført ved bruk av en minste kvadraters metode (LSM). For lineære ligninger av formen Y = a + b ₁ x _{1 + ... + b m x m +} ε bygge opp et system av normale ligninger (cm. Nedenfor)

For å forstå prinsippet for metoden, anser vi to-faktor tilfelle. Da har vi situasjonen beskrevet av formelen

Derfor får vi:

der σ - er variansen av den respektive funksjonen, reflekteres i indeksen.

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

hvori t _y, t _{x 1, ...} t _xm - standartiziruemye variable for hvilken gjennomsnittsverdier er 0; p _i - standardiserte stigningstall og standardavvik - 1.

Vær oppmerksom på at alle P _jeg i dette tilfellet er definert som den normaliserte og tsentraliziruemye derfor en sammenligning mellom en betraktet som gyldig og akseptabelt. I tillegg er det akseptert å gjennomføre screening av faktorer, forkaster de som har de laveste verdiene av βi.

Problemet med å bruke lineær regresjonsligning

Anta at du har en tabell over dynamikken i prisen på et bestemt produkt N for de siste 8 månedene. Det er nødvendig for å avgjøre om oppkjøpet av hans parti på prisen på 1850 rubler. / T.

	En	B	C
1	måned	navnet på måneden	Pris N
2	1	januar	1750 rubler per tonn
3	2	februar	1755 rubler per tonn
4	3	mars	1767 rubler per tonn
5	4	april	1760 rubler per tonn
6	5	mai	1770 rubler per tonn
7	6	juni	1790 rubler per tonn
8	7	juli	1810 rubler per tonn
9	8	august	1840 rubler per tonn

For å løse dette problemet i tabell prosessor "Excel" kreves for å bruke allerede kjent for eksempel funksjonen "Data Analysis" er presentert ovenfor. Deretter velger du "Regression" -delen og sette parametre. Vi må huske på at i "Input range Y» skal bli introdusert til en rekke verdier av den avhengige variabelen (i dette tilfellet prisen på varene i bestemte måneder) og i "Input intervallet X» - for en uavhengig (i måneden). Vi bekrefte handlingen ved å klikke på «Ok». I et nytt regneark (hvis angitt), får vi data for regresjon.

Vi bygger på dem lineær ligning av formen y = ax + b, hvor som parametrene a og b er koeffisientene fra det linjenummer i måneden og navn av koeffisientene og «Y-skjæringspunkt" linje på arket med resultatene av regresjonsanalyse. Således kan den lineære regresjonsligning (EQ) 3 for problemet skrives som:

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

eller i algebraisk notasjon

y = 11714 x + 1727,54

analysen av resultatene

For å avgjøre hvorvidt det mottatte tilstrekkelig lineær regresjon ligning ved hjelp av de multiple korrelasjonskoeffisientene (CMC), og bestemmelse, samt test og Fisher t-test. I tabellen er "Excel" regresjon med de resultater som de opptrer under navnene multiplum R, R-Square, F-t-statistikk og statistikk, henholdsvis.

KMC R gjør det mulig å anslå nærhet probabilistiske forholdet mellom uavhengige og avhengige variabler. Den høye verdien indikerer en sterk nok sammenheng mellom variabelen "Antall måneden" og "N Product pris i rubler per 1 tonn." Imidlertid er innholdet i dette forholdet ukjent.

Kvadratet av koeffisienten ^R2 (RI) er en numerisk karakteristisk for den andel av den totale spredning og viser en spredning av eksperimentelle data del, dvs. verdier av den avhengige variable som svarer til en lineær regresjonsligning. I dette problemet, er denne verdien 84,8%, smp. E. Statistisk med en høy grad av nøyaktighet oppnås, er beskrevet SD.

F-statistikk, også kjent som Fisher kriterium brukt for å vurdere betydningen av den lineære avhengighet eller disproving hypotese som bekrefter dens eksistens.

Verdien av t-statistikken (Student t-test) bidrar til å vurdere betydningen av koeffisienten ved en hvilken som helst ledig ukjent lineær avhengighet medlem. Hvis verdien av t-test> t _cr, er hypotesen om en lineær ligning ubetydelighet av fri sikt forkastet.

I denne oppgaven for en gratis sikt gjennom instrumenter "Excel" ble det funnet at t = 169,20903, og p = 2,89E-12, t. E. Ha en null sannsynlighet for at de troende vil bli avvist hypotesen om ubetydelighet av gratis sikt. For ukjent koeffisient ved t = 5,79405, og p = 0,001158. Med andre ord, sannsynligheten for at en avvist korrekt hypotese vil ubetydelighet av koeffisienten for det ukjente, er 0,12%.

Således kan det hevdes at det oppnås lineær regresjonsligning tilstrekkelig.

Problemet med det er tilrådelig å kjøpe aksjer

Multippel regresjon ble utført i Excel ved hjelp av samme "Data Analysis" verktøyet. Vurdere den spesifikke applikasjonen.

Guide selskapet «NNN» må bestemme om du vil kjøpe 20% av aksjene i JSC «MMM». Pakkepris (SP) er 70 millioner amerikanske dollar. Spesialister på «NNN» samlet inn data for lignende transaksjoner. Det ble besluttet å vurdere verdien av aksjene på slike parametre, uttrykt i millioner av amerikanske dollar, for eksempel:

• gjeld (VK);
• omsetning volum (VO);
• fordringer (VD);
• verdien av anleggsmidler (SOF).

I tillegg bruker lønns gjelden til bedrifter (V3 U) i tusenvis av dollar.

Beslutningen tabellen prosessor Excel hjelp

Først må du opprette en tabell med inndata. Det er som følger:

Neste:

• telefonkiosk "dataanalyse";
• valgt "Regression" -delen;
• vinduet "Inngangsintervall Y» administreres avstandsavhengige variable verdier fra kolonne G;
• klikker på ikonet med en rød pil til høyre for vinduet "Input intervall X» og isolert på et ark rekke alle verdier av kolonne B, C, D, F.

Marker punktet "Nytt regneark" og klikk "Ok".

Få en regresjonsanalyse for denne oppgaven.

Studien resultater og konklusjoner

"Collect" avrundet fra de data som er presentert ovenfor på arket bordet Excel prosessor regresjonsligning:

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

I den mer vanlige matematiske form kan det skrives som:

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

Data for «MMM» JSC presentert i tabellen under:

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

Å erstatte dem inn i regresjonsligningen, fikk en figur av 64,72 millioner amerikanske dollar. Dette betyr at aksjene i JSC «MMM» ikke bør kjøpe, fordi kostnadene er ganske overpriset på 70 millioner amerikanske dollar.

Som du kan se, bruk av regneark "Excel" og regresjonsligningen lov til å ta en avgjørelse om det er tilrådelig ganske bestemt transaksjon.

Nå vet du hva en regresjon. Eksempler til Excel, omtalt ovenfor, vil hjelpe deg med å løse praktiske problemer i økonometri.