Opprette en lineær regresjonsmodell i Excel

Lineær regresjon er et dataplan som grafer det lineære forholdet mellom en uavhengig og en avhengig variabel. Det brukes vanligvis til visuelt å vise styrken i forholdet og spredningen av resultater - alt for å forklare atferden til den avhengige variabelen.

Si at vi ønsket å teste styrken i forholdet mellom mengden iskrem spist og overvekt. Vi ville ta den uavhengige variabelen, mengden is, og relatert den til den avhengige variabelen, overvekt, for å se om det var et forhold. Gitt en regresjon er en grafisk visning av dette forholdet, jo lavere variasjon i dataene, jo sterkere er forholdet og desto strammere passform til regresjonslinjen.

Viktige takeaways

• Lineær regresjon modellerer forholdet mellom en avhengig og uavhengig variabel (er).
• Regresjonsanalyse kan oppnås hvis variablene er uavhengige, det ikke er noen heteroscedasticitet, og feilbetingelsene for variabler ikke er korrelert.
• Det er enklere å modellere lineær regresjon i Excel med Data Analysis ToolPak.

Viktige hensyn

Det er noen få kritiske forutsetninger om datasettet ditt som må være sant for å kunne fortsette med en regresjonsanalyse:

1. Variablene må være virkelig uavhengige (ved hjelp av en Chi-kvadrat-test).
2. Dataene må ikke ha forskjellige feilavvik (dette kalles heteroskedasticitet (også stavet heteroscedasticity)).
3. Feilvilkårene for hver variabel må være ukorrelerte. Hvis ikke, betyr det at variablene er serielt korrelert.

Hvis de tre tingene høres kompliserte ut, er de det. Men effekten av at et av disse hensynene ikke stemmer, er et partisk estimat. I hovedsak ville du feilaktig forholdet du måler.

Avgir en regresjon i Excel

Det første trinnet i å kjøre regresjonsanalyse i Excel er å dobbeltsjekke at gratis Excel-plugin-dataanalyseverktøyPak er installert. Denne pluginen gjør det veldig enkelt å beregne en rekke statistikker. Det er ikke påkrevd å kartlegge en lineær regresjonslinje, men det gjør det enklere å lage statistiktabeller. For å bekrefte om det er installert, velg "Data" fra verktøylinjen. Hvis "Dataanalyse" er et alternativ, er funksjonen installert og klar til bruk. Hvis ikke installert, kan du be om dette alternativet ved å klikke på Office-knappen og velge "Excel-alternativer".

Ved hjelp av Data Analysis ToolPak er det bare noen få klikk å lage en regresjonsutgang.

Den uavhengige variabelen går i X-området.

Gitt S&P 500-avkastningen, si at vi vil vite om vi kan estimere styrken og forholdet til Visa (V) aksjeavkastning. Visa (V) aksjen returnerer data som fyller kolonne 1 som den avhengige variabelen. S&P 500 returnerer datafolket kolonne 2 som den uavhengige variabelen.

1. Velg "Data" fra verktøylinjen. "Data" -menyen vises.
2. Velg "Dataanalyse". Dialogboksen Dataanalyse - analyseverktøy vises.
3. Velg "Regresjon" fra menyen og klikk "OK".
4. I dialogboksen Regresjon, klikk på "Input Y Range" -boksen og velg de avhengige variabeldataene (Visa (V) stock return).
5. Klikk på "Input X Range" -boksen og velg de uavhengige variabeldataene (S&P 500 returnerer).
6. Klikk på "OK" for å kjøre resultatene.

[Merk: Hvis tabellen virker liten, høyreklikker du på bildet og åpner i en ny fane for høyere oppløsning.]

Tolke resultatene

Ved å bruke disse dataene (de samme fra vår R-firkantede artikkel), får vi følgende tabell:

R2-verdien, også kjent som bestemmelseskoeffisienten, måler andelen av variasjonen i den avhengige variabelen som er forklart av den uavhengige variabelen eller hvor godt regresjonsmodellen passer til dataene. R2-verdien varierer fra 0 til 1, og en høyere verdi indikerer bedre passform. P-verdien, eller sannsynlighetsverdien, varierer også fra 0 til 1 og indikerer om testen er signifikant. I motsetning til R2-verdien, er en mindre p-verdi gunstig ettersom den indikerer en korrelasjon mellom de avhengige og uavhengige variablene.

Kartlegge en regresjon i Excel

Vi kan kartlegge en regresjon i Excel ved å utheve dataene og kartlegge dem som et scatter-plot. For å legge til en regresjonslinje, velg "Layout" fra "Chart Tools" -menyen. I dialogboksen velger du "Trendline" og deretter "Linear Trendline". For å legge til R ^2- verdien, velg "Flere trendlinjealternativer" fra "Trendlinje-menyen. Til slutt velger du" Vis R-kvadratverdi på diagrammet. "Det visuelle resultatet oppsummerer styrken i forholdet, om enn på bekostning av ikke å gi like mye detalj som tabellen over.