Regresjonsberegning i excel. Ikke-lineær regresjon i Excel

MS Excel-pakken lar deg gjøre det meste av arbeidet veldig raskt når du konstruerer en lineær regresjonsligning. Det er viktig å forstå hvordan man tolker resultatene. For å bygge en regresjonsmodell, velg Verktøy\Dataanalyse\Regresjon (i Excel 2007 er denne modusen plassert i delen Data/Dataanalyse/Regresjon). Kopier deretter de oppnådde resultatene til en blokk for analyse.

MS Excel-pakken lar deg gjøre det meste av arbeidet veldig raskt når du konstruerer en lineær regresjonsligning. Det er viktig å forstå hvordan man tolker resultatene.

Krever tillegg for å fungere Analysepakke, som må være aktivert i menyelementet Tjeneste\Tillegg

I Excel 2007, for å aktivere analysepakken, klikk Gå til blokk Excel-alternativer, ved å klikke på knappen i øvre venstre hjørne, og deretter " Excel-alternativer» nederst i vinduet:

For å bygge en regresjonsmodell, velg elementet Tjeneste\Dataanalyse\Regresjon. (I Excel 2007 er denne modusen i Data/Data Analyse/Regresjon). Det vises en dialogboks som må fylles ut:

1) Inndataintervall Y¾ inneholder en lenke til celler som inneholder verdiene til det resulterende attributtet y. Verdier må være i en kolonne;

2) Inndataintervall X¾ inneholder en lenke til celler som inneholder verdiene til faktorene. Verdier må være i kolonner;

3) Signer Tagger angi om de første cellene inneholder forklarende tekst (dataetiketter);

4) Pålitelighetsnivå¾ er konfidensnivået, som antas å være 95 % som standard. Hvis denne verdien ikke passer deg, må du aktivere denne funksjonen og angi den nødvendige verdien;

5) Signer Null konstant er inkludert hvis det er nødvendig å konstruere en ligning der den frie variabelen ;

6) Utgangsalternativer bestemme hvor resultatene skal plasseres. Standard byggemodus Nytt arbeidsark;

7) Blokker Rester lar deg inkludere produksjonen av rester og konstruksjonen av grafene deres.

Som et resultat vises informasjon som inneholder all nødvendig informasjon og er gruppert i tre blokker: Regresjonsstatistikk, Analyse av varianter, Saldouttak. La oss vurdere dem mer detaljert.

1. Regresjonsstatistikk:

flere R er definert av formelen ( Pearson korrelasjonskoeffisient);

R (bestemmelseskoeffisient);

Normalisert R-kvadrat beregnes av formelen (brukes for multippel regresjon);

standard feil S beregnet med formelen ;

Observasjoner ¾ er mengden data n.

2. Analyse av varianter, linje Regresjon:

Parameter df er lik m(antall sett med faktorer x);

Parameter SS bestemmes av formelen;

Parameter MS bestemmes av formelen;

Statistikk F bestemmes av formelen;

Betydning F. Hvis det resulterende tallet overstiger , er hypotesen akseptert (ingen lineær sammenheng), ellers aksepteres hypotesen (det er en lineær sammenheng).

3. Analyse av varianter, linje Rest:

Parameter df er lik ;

Parameter SS bestemmes av formelen ;

Parameter MS bestemmes av formelen.

4. Analyse av varianter, linje Total inneholder summen av de to første kolonnene.

5. Analyse av varianter, linje Y-kryss inneholder verdien av koeffisient , standardfeil og t-statistikk.

P-verdi ¾ er verdien av signifikansnivåer som tilsvarer det beregnede t- statistikere. Bestemt av STUDIST( t-statistikk; ). Hvis en P-verdi overstiger , da er den tilsvarende variabelen statistisk insignifikant og kan ekskluderes fra modellen.

nederste 95 % og Topp 95 %¾ er de nedre og øvre grensene for 95 prosent konfidensintervaller for koeffisientene til den teoretiske lineære regresjonsligningen. Hvis verdien av konfidenssannsynligheten ble igjen som standard i dataregistreringsblokken, vil de to siste kolonnene duplisere de forrige. Hvis brukeren har angitt en egendefinert konfidensverdi, inneholder de to siste kolonnene de nedre og øvre grenseverdiene for det angitte konfidensnivået.

6. Analyse av varianter, linjene inneholder verdiene av koeffisienter, standardfeil, t- statistiker, P-verdier og konfidensintervaller for de tilsvarende .

7. Blokker Saldouttak inneholder verdiene til det spådde y(i vår notasjon er det ) og rester .

Regresjons- og korrelasjonsanalyse - statistiske forskningsmetoder. Dette er de vanligste måtene å vise avhengigheten til en parameter på en eller flere uavhengige variabler.

Nedenfor skal vi ved hjelp av konkrete praktiske eksempler vurdere disse to svært populære analysene blant økonomer. Vi vil også gi et eksempel på å oppnå resultater når de kombineres.

Regresjonsanalyse i Excel

Viser påvirkningen av noen verdier (uavhengig, uavhengig) på den avhengige variabelen. For eksempel hvordan antallet økonomisk aktive befolkning avhenger av antall bedrifter, lønn og andre parametere. Eller: hvordan påvirker utenlandske investeringer, energipriser osv. nivået på BNP.

Resultatet av analysen lar deg prioritere. Og basert på hovedfaktorene, for å forutsi, planlegge utviklingen av prioriterte områder, ta ledelsesbeslutninger.

Regresjon skjer:

• lineær (y = a + bx);
• parabolsk (y = a + bx + cx 2);
• eksponentiell (y = a * exp(bx));
• potens (y = a*x^b);
• hyperbolsk (y = b/x + a);
• logaritmisk (y = b * 1n(x) + a);
• eksponentiell (y = a * b^x).

Tenk på eksempelet med å bygge en regresjonsmodell i Excel og tolke resultatene. La oss ta en lineær type regresjon.

En oppgave. Ved 6 virksomheter ble gjennomsnittlig månedslønn og antall ansatte som sluttet analysert. Det er nødvendig å bestemme avhengigheten av antall pensjonerte ansatte på gjennomsnittslønnen.

Den lineære regresjonsmodellen har følgende form:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Der a er regresjonskoeffisientene, x er de påvirkende variablene, og k er antall faktorer.

I vårt eksempel er Y indikatoren for sluttarbeidere. Påvirkningsfaktoren er lønn (x).

Excel har innebygde funksjoner som kan brukes til å beregne parametrene til en lineær regresjonsmodell. Men Analysis ToolPak-tillegget vil gjøre det raskere.

Aktiver et kraftig analyseverktøy:

Når det er aktivert, vil tillegget være tilgjengelig under fanen Data.

Nå skal vi behandle regresjonsanalysen direkte.

Først av alt tar vi hensyn til R-kvadraten og koeffisientene.

R-kvadrat er bestemmelseskoeffisienten. I vårt eksempel er det 0,755, eller 75,5 %. Dette betyr at de beregnede parameterne til modellen forklarer sammenhengen mellom de studerte parameterne med 75,5 %. Jo høyere bestemmelseskoeffisient, jo bedre modell. Bra - over 0,8. Dårlig - mindre enn 0,5 (en slik analyse kan neppe anses som rimelig). I vårt eksempel - "ikke dårlig".

Koeffisienten 64.1428 viser hva Y vil være dersom alle variablene i modellen som vurderes er lik 0. Det vil si at andre faktorer som ikke er beskrevet i modellen også påvirker verdien av den analyserte parameteren.

Koeffisienten -0,16285 viser vekten av variabelen X på Y. Det vil si at gjennomsnittlig månedslønn innenfor denne modellen påvirker antall sluttere med en vekt på -0,16285 (dette er en liten grad av påvirkning). "-"-tegnet indikerer en negativ innvirkning: jo høyere lønn, jo mindre slutter. Som er rettferdig.

Korrelasjonsanalyse i Excel

Korrelasjonsanalyse bidrar til å fastslå om det er en sammenheng mellom indikatorer i ett eller to utvalg. For eksempel mellom driftstiden til maskinen og kostnadene for reparasjoner, prisen på utstyret og varigheten av driften, høyden og vekten til barn, etc.

Hvis det er en sammenheng, så om en økning i en parameter fører til en økning (positiv korrelasjon) eller en reduksjon (negativ) i den andre. Korrelasjonsanalyse hjelper analytikeren med å avgjøre om verdien av en indikator kan forutsi mulig verdi av en annen.

Korrelasjonskoeffisienten er betegnet r. Varierer fra +1 til -1. Klassifiseringen av korrelasjoner for ulike områder vil være forskjellig. Når koeffisientverdien er 0, er det ingen lineær sammenheng mellom prøvene.

Vurder hvordan du bruker Excel for å finne korrelasjonskoeffisienten.

CORREL-funksjonen brukes til å finne de parede koeffisientene.

Oppgave: Finn ut om det er en sammenheng mellom driftstiden til en dreiebenk og kostnadene for vedlikehold.

Plasser markøren i en hvilken som helst celle og trykk på fx-knappen.

1. I kategorien "Statistisk" velger du CORREL-funksjonen.
2. Argument "Array 1" - det første verdiområdet - tiden til maskinen: A2: A14.
3. Argument "Array 2" - det andre verdiområdet - kostnadene for reparasjoner: B2:B14. Klikk OK.

For å bestemme typen tilkobling, må du se på det absolutte tallet på koeffisienten (hvert aktivitetsfelt har sin egen skala).

For korrelasjonsanalyse av flere parametere (mer enn 2) er det mer praktisk å bruke "Data Analysis" ("Analysis Package"-tillegg). I listen må du velge en korrelasjon og angi en matrise. Alle.

De resulterende koeffisientene vil vises i korrelasjonsmatrisen. Som denne:

Korrelasjons-regresjonsanalyse

I praksis brukes disse to teknikkene ofte sammen.

Eksempel:

Nå er regresjonsanalysedataene synlige.

Emne: KRRELASJON OG REGRESJONSANALYSE IUTMERKE

LAB #1

1. BESTEMMELSE AV PARKORRELASJONSKOEFFISIENTEN I PROGRAMMETUTMERKE

sammenheng- dette er et ufullstendig, sannsynlig forhold mellom indikatorer, som bare manifesterer seg i massen av observasjoner.

Parkorrelasjon- dette er forholdet mellom to indikatorer, hvorav den ene er faktoriell, og den andre er effektiv.

Multippel korrelasjon oppstår fra samspillet mellom flere faktorer og ytelsesindikatoren.

Nødvendige betingelser for å bruke korrelasjonsanalyse:

1. Tilstedeværelsen av et tilstrekkelig stort antall observasjoner på størrelsen på den studerte faktoren og ytelsesindikatorer.

2. De studerte faktorene bør kvantifiseres og reflekteres i ulike informasjonskilder.

Bruken av korrelasjonsanalyse lar oss løse følgende problemer:

1. Bestem endringen i ytelsesindikatoren under påvirkning av en eller flere faktorer.

2. Etabler den relative graden av avhengighet av ytelsesindikatoren på hver faktor.

Øvelse 1.

Data er tilgjengelig for 20 jordbruksbedrifter. Finne korrelasjonskoeffisient mellom avlingene av kornavlinger og kvaliteten på landet og vurdere betydningen. Dataene er gitt i tabellen.

Bord. Avhengighet av kornavling av jordkvalitet

For å finne korrelasjonskoeffisienten bruk funksjonen CORREL.

Betydningen av korrelasjonskoeffisienten kontrolleres av kriteriet Student.

For eksemplet under vurdering, r=0,99, n=18.

For å finne kvantilen til studentens fordeling, bruk STUDISP-funksjonen med følgende argumenter: Sannsynlighet –0,05, grader frihet –18.

Sammenlign verdien av t-statistikk med kvantilen av studentens fordeling, trekk konklusjoner om betydningen av parkorrelasjonskoeffisienten. Hvis den beregnede verdien av t-statistikken er større enn kvantilen til studentens fordeling, er verdien av korrelasjonskoeffisienten signifikant.

KONSTRUKSJON AV EN REGRESJONSMODELL FOR KOMMUNIKASJON MELLOM TO VERDIER

Oppgave 2.

I henhold til oppgave 1:

1) bygge en regresjonsligning (lineær modell), som karakteriserer et rettlinjet forhold mellom landkvalitet og produktivitet;

2). kontrollere egnetheten til den resulterende modellen.

1 - vei.

1. På et Excel-ark velger du en rekke ledige celler med fem rader og to kolonner.

2. Kalle opp en funksjon LINJEST.

3. Angi følgende argumenter for funksjonen: Get_value_y Produktivitet, senter/ha;Get_value_x- kolonne med indikatorverdier Landkvalitet, poengsum; Konstant -1, Stat - 1(lar deg beregne indikatorene som brukes til å teste modellens tilstrekkelighet. Hvis stat - 0, da vil ikke slike indikatorer bli beregnet.

4. Trykk på tastekombinasjonen ctrl- Skifte- Tast inn.

De valgte cellene viser koeffisientene til modellen, samt indikatorer som lar deg sjekke modellen for tilstrekkelighet (tabell 2).

en 1 , en 0 er koeffisientene til modellen;

S e 1 S e 0 er standardfeilene til koeffisientene. Jo mer nøyaktig modellen er, desto mindre er disse verdiene.

R 2 – bestemmelseskoeffisient. Jo større den er, jo mer nøyaktig er modellen.

F– statistikk for å teste betydningen av modellen.

n- k-1 – antall frihetsgrader (n-utvalgsstørrelse, k-antall inngangsvariabler; i dette eksemplet n=20, k=1)

Q R er summen av kvadrater på grunn av regresjon;

Q e er summen av kvadrerte feil.

5. For å sjekke tilstrekkeligheten til modellen, finn kvantilen til Fisher-fordelingen F f . ved å bruke funksjonen FFORDELING. For å gjøre dette, skriv inn funksjonen i en hvilken som helst ledig celle FFORDELING med følgende argumenter: Sannsynlighet – 0,05, grader av frihet _1–1, grader av frihet _2–18. Hvis F> F f, så er modellen tilstrekkelig til de opprinnelige dataene

6. Kontroller tilstrekkeligheten til den konstruerte modellen ved å bruke det beregnede signifikansnivået (P). Gå inn på funksjon FRASP med følgende argumenter: X– verdien av statistikk F, Frihetsgrader_1–1, Frihetsgrader_2– 18. Hvis det beregnede signifikansnivået P<α =0,05, то модель адекватна исходным данным.

2. metode.

Bestemmelse av koeffisientene til modellen med innhenting av indikatorer for å kontrollere dens tilstrekkelighet og betydningen av koeffisientene.

Velg et lag Tjeneste/Dataanalyse/Regresjon. I installasjonsdialogboksen: inndataintervallY– indikatorverdier Produktivitet, senter/ha,inndataintervallX– indikatorverdier Landkvalitet, poengsum.

Avmerkingsboks Tagger. I området til Utgangsalternativer velg alternativknapp utgangsintervall og spesifiser cellen som utdataene av resultatene skal begynne fra. Klikk OK for å motta resultatene.

Tolking av resultater.

De ønskede koeffisientene til modellen er i kolonnen Odds:

For dette eksemplet er modellligningen:

Y=2,53+0,5X

I dette eksemplet, med en økning i jordkvaliteten med ett poeng, øker utbyttet av kornavlinger med gjennomsnittlig 0,5 c/ha.

Kontroller at modellen er tilstrekkelig utføres i henhold til det beregnede signifikansnivået P angitt i kolonnen BetydningF. Hvis det beregnede signifikansnivået er mindre enn det gitte signifikansnivået α =0,05, er modellen tilstrekkelig.

Testing for statistisk signifikans koeffisientene til modellen utføres i henhold til de beregnede signifikansnivåene P angitt i kolonnen P-betydning. Hvis det beregnede signifikansnivået er mindre enn det gitte signifikansnivået α =0,05, så er den tilsvarende koeffisienten til modellen statistisk signifikant.

FlereR – korrelasjonskoeffisient. Jo nærmere verdien er 1, jo nærmere forholdet mellom de studerte indikatorene. For dette eksemplet er R= 0,99. Dette lar oss konkludere med at kvaliteten på landet er en av hovedfaktorene som utbyttet av kornavlinger avhenger av.

R-torget – bestemmelseskoeffisient. Det oppnås ved å kvadrere korrelasjonskoeffisienten - R 2 \u003d 0,98. Den viser at utbyttet av kornavlinger er 98 % avhengig av jordkvaliteten, mens andre faktorer står for 0,02 %.

3. vei. GRAFISK METODE FOR Å BYGGE EN MODELL.

Selvstendig bygge en strøtomt som gjenspeiler forholdet mellom avling og arealkvalitet.

Skaff en lineær modell av avlingsavlingenes avhengighet av landkvaliteten.

28 okt

God ettermiddag, kjære blogglesere! I dag skal vi snakke om ikke-lineære regresjoner. Løsningen av lineære regresjoner kan sees på LINK.

Denne metoden brukes hovedsakelig i økonomisk modellering og prognoser. Formålet er å observere og identifisere forholdet mellom to indikatorer.

Hovedtypene for ikke-lineære regresjoner er:

• polynom (kvadratisk, kubisk);
• hyperbolsk;
• makt;
• demonstrasjon;
• logaritmisk.

Forskjellige kombinasjoner kan også brukes. For eksempel, for tidsserieanalyser innen bank, forsikring, demografiske studier, brukes Gompzer-kurven, som er en type logaritmisk regresjon.

Ved prognoser ved bruk av ikke-lineære regresjoner er det viktigste å finne ut korrelasjonskoeffisienten, som vil vise oss om det er en nær sammenheng mellom to parametere eller ikke. Som regel, hvis korrelasjonskoeffisienten er nær 1, er det en sammenheng, og prognosen vil være ganske nøyaktig. Et annet viktig element i ikke-lineære regresjoner er gjennomsnittlig relativ feil ( MEN ) hvis det er i intervallet<8…10%, значит модель достаточно точна.

På dette vil vi kanskje avslutte den teoretiske blokken og gå videre til praktiske beregninger.

Vi har en tabell over bilsalg for en periode på 15 år (la oss betegne det som X), antall måletrinn vil være argumentet n, vi har også inntekter for disse periodene (la oss betegne det Y), vi må forutsi hva inntektene vil være i fremtiden. La oss bygge følgende tabell:

For studien må vi løse ligningen (avhengigheten av Y på X): y=ax 2 +bx+c+e. Dette er en par-kvadratisk regresjon. I dette tilfellet bruker vi metoden med minste kvadrater for å finne ut de ukjente argumentene - a, b, c. Det vil føre til et system med algebraiske ligninger av formen:

For å løse dette systemet bruker vi for eksempel Cramer-metoden. Vi ser at summene som inngår i systemet er koeffisientene til de ukjente. For å beregne dem legger vi til flere kolonner i tabellen (D, E, F, G, H) og signerer dem i henhold til betydningen av beregningene - i kolonne D kvadrerer vi x, i E til en kube, i F til 4. potens, i G ganger vi indikatorene x og y, i H kvadrater vi x og ganger med y.

Det vil vise seg en tabell med skjemaet fylt med de som er nødvendige for å løse ligningen.

La oss lage en matrise EN et system som består av koeffisienter for ukjente på venstre side av ligningene. La oss legge den i celle A22 og kalle den " A=". Vi følger ligningssystemet som vi har valgt for å løse regresjonen.

Det vil si at i celle B21 må vi plassere summen av kolonnen der vi hevet indikatoren X til fjerde potens - F17. La oss bare referere til cellen - "=F17". Deretter trenger vi summen av kolonnen der X ble kubert - E17, så går vi strengt i henhold til systemet. Derfor må vi fylle ut hele matrisen.

I samsvar med Cramers algoritme vil vi samle en matrise A1, lik A, der i stedet for elementene i den første kolonnen, skal elementene i de høyre delene av systemets likninger plasseres. Det vil si, summen av kolonne X i kvadrat ganger Y, summen av kolonne XY og summen av kolonne Y.

Vi vil også trenge to matriser til - la oss kalle dem A2 og A3 der andre og tredje kolonne vil bestå av koeffisientene til høyresiden av ligningene. Bildet blir slik.

Etter den valgte algoritmen må vi beregne verdiene til determinantene (determinantene, D) til de oppnådde matrisene. La oss bruke MOPRED-formelen. Resultatene vil bli plassert i cellene J21:K24.

Vi vil beregne koeffisientene til ligningen i henhold til Cramer i cellene overfor de tilsvarende determinantene i henhold til formelen: en(i celle M22) - "=K22/K21"; b(i celle M23) — "=K23/K21"; Med(i celle M24) - "=K24 / K21".

Vi får vår ønskede kvadratiske regresjonsligning:

y=-0,074x2 +2,151x+6,523

La oss estimere stramheten til det lineære forholdet ved hjelp av korrelasjonsindeksen.

For å beregne, legg til en ekstra kolonne J i tabellen (la oss kalle den y*). Beregningen vil være som følger (i henhold til regresjonsligningen vi mottok) - "=$m$22*B2*B2+$M$23*B2+$M$24". La oss sette den i celle J2. Alt som gjenstår er å dra autofyll-markøren ned til celle J16.

For å beregne summene (Y-Y gjennomsnitt) 2, legg til kolonnene K og L i tabellen med de tilsvarende formlene. Vi beregner gjennomsnittet for Y-kolonnen ved å bruke AVERAGE-funksjonen.

I celle K25 plasserer vi formelen for beregning av korrelasjonsindeksen - "=ROOT(1-(K17/L17))".

Vi ser at verdien på 0,959 er veldig nær 1, noe som betyr at det er et nært ikke-lineært forhold mellom salg og år.

Det gjenstår å evaluere kvaliteten på tilpasningen av den oppnådde kvadratiske regresjonsligningen (bestemmelsesindeks). Det beregnes ved formelen til kvadratet av korrelasjonsindeksen. Det vil si at formelen i celle K26 vil være veldig enkel - "=K25*K25".

Koeffisienten på 0,920 er nær 1, noe som indikerer en god passform.

Det siste trinnet er å beregne den relative feilen. La oss legge til en kolonne og skrive inn formelen der: “=ABS((C2-J2)/C2), ABS — modul, absolutt verdi. La oss dra markøren ned og i celle M18 vil vi vise gjennomsnittsverdien (GJENNOMSNITT), tilordne prosentformatet til cellene. Resultatet oppnådd - 7,79 % er innenfor akseptable feilverdier<8…10%. Значит вычисления достаточно точны.

Om nødvendig kan vi bygge en graf basert på de oppnådde verdiene.

En eksempelfil er vedlagt - LINK!