Excel-regresjonsanalyse (innholdsfortegnelse)
- Regresjonsanalyse i Excel
- Forklaring av regresjon matematisk
- Hvordan utføre lineær regresjon i Excel?
- # 1 - Regresjonsverktøy ved hjelp av analyseverktøyPak i Excel
- # 2 - Regresjonsanalyse ved bruk av Scatterplot med Trendline i Excel
Regresjonsanalyse i Excel
Lineær regresjon er en statistisk teknikk som undersøker det lineære forholdet mellom en avhengig variabel og en eller flere uavhengige variabler.
- Avhengig variabel (aka respons / utfallsvariabel): Er variabelen for interessen din og som du ønsket å forutsi basert på informasjonen som er tilgjengelig om uavhengige variabler.
- Uavhengig variabel (aka forklarende / prediktor variabel): Er / er variabelen (e) som responsvariabelen er avhengig av. Hvilket betyr at dette er variablene som bruker hvilken responsvariabel som kan spås.
Lineær sammenheng betyr at endringen i en uavhengig variabel (r) forårsaker en endring i den avhengige variabelen.
Det er i utgangspunktet to typer lineære forhold også.
- Positive Lineære forhold: Når uavhengig variabel øker, øker den avhengige variabelen også.
- Negativt lineært forhold: Når den uavhengige variabelen øker, reduseres den avhengige variabelen.
Dette var noen av forutsetningene før du faktisk gikk videre til regresjonsanalyse i Excel.
Det er to grunnleggende måter å utføre lineær regresjon i Excel ved å bruke:
- Regresjonsverktøy gjennom AnalyseverktøyPak
- Spredekart med trendlinje
Det er faktisk en metode til som bruker manuelle formler for å beregne lineær regresjon. Men hvorfor skal du gå for det når Excel gjør beregninger for deg?
Derfor skal vi snakke om de to metodene som er omtalt ovenfor.
Anta at du har en data om høyde og vekt på 10 individer. Hvis du planlegger denne informasjonen gjennom et diagram, la oss se hva den gir.
Som skjermbildet over viser, kan det lineære forholdet finnes i høyde og vekt gjennom grafen. Ikke bli veldig involvert i graf nå, vi kommer uansett til å grave det dypt i den andre delen av denne artikkelen.
Forklaring av regresjon matematisk
Vi har et matematisk uttrykk for lineær regresjon som nedenfor:
Y = aX + b + ε
Hvor,
- Y er en avhengig variabel eller responsvariabel.
- X er en uavhengig variabel eller prediktor.
- a er skråningen på regresjonslinjen. Som representerer at når X endres, er det en endring i Y med "a" enheter.
- b fanger opp. Det er verdien Y tar når verdien til X er null.
- ε er den tilfeldige feilbegrep. Forekommer fordi den forutsagte verdien av Y aldri vil være nøyaktig den samme som den faktiske verdien for gitt X. Dette feiluttrykket trenger vi ikke å bekymre oss for. Siden det er noen programvare som gjør beregningen av denne feilbegrep i backend for deg. Excel er en av den programvaren.
I så fall blir ligningen,
Y = aX + b
Som kan bli representert som:
Vekt = a * Høyde + b
Vi prøver å finne ut verdiene til disse a og b ved å bruke metoder vi har diskutert ovenfor.
Hvordan utføre lineær regresjon i Excel?
Den videre artikkelen forklarer det grunnleggende om regresjonsanalyse i Excel og viser noen få forskjellige måter å gjøre lineær regresjon i Excel.
Du kan laste ned denne Excel-malen for regresjonsanalyse her - Excel-mal for regresjonsanalyse# 1 - Regresjonsverktøy ved hjelp av analyseverktøyPak i Excel
For vårt eksempel vil vi prøve å passe regresjon for vektverdier (som er avhengig variabel) ved hjelp av høydeverdier (som er en uavhengig variabel).
- I excel-regnearket, klikk på Data Analyse (presentert under Analysegruppe ) under Data.
- Søk etter regresjon . Velg det og trykk på ok.
- Bruk følgende innganger under Regresjonsrute som åpnes.
- Input Y Range : Velg cellene som inneholder den avhengige variabelen din (i dette eksemplet B1: B11)
- Input X Range : Velg cellene som inneholder den uavhengige variabelen din (i dette eksempelet A1: A11).
- Merk av i boksen Labels hvis dataene dine har kolonnenavn (i dette eksemplet har vi kolonnenavn).
- Tillitsnivået er som standard satt til 95%, som kan endres i henhold til brukernes krav.
- Under Output-alternativer kan du tilpasse hvor du vil se utdataene fra regresjonsanalyse i Excel. I dette tilfellet ønsker vi å se utdataene på samme ark. Derfor gitt rekkevidden tilsvarende.
- Under alternativet Residuals har du valgfrie innganger som Residuals, Restplot, Standardised Residuals, Line Fit Plots som du kan velge etter ditt behov. I dette tilfellet, merk av for Residuals- avkrysningsruten slik at vi kan se spredningen mellom forutsagte og faktiske verdier.
- Under alternativet Normal sannsynlighet kan du velge Normale sannsynlighetsplott som kan hjelpe deg med å sjekke normaliteten til prediktorer. Klikk på OK .
- Excel vil beregne regresjonsanalyse for deg på en brøkdel av sekunder.
Til her var det enkelt og ikke så logisk. Det er imidlertid en vanskelig oppgave å tolke dette resultatet og gi verdifull innsikt fra det.
En viktig del av hele utdataet er R Square / Adjusted R Square under SAMMENDRAGT UTGANG. Som gir informasjon, hvor god modellen vår passer. I dette tilfellet er R- kvadratverdien 0, 9547. Noe som tolker at modellen har en nøyaktighet på 95, 47% (god passform). Eller på et annet språk forklares informasjon om Y-variabel 95, 47% av X-variabel.
Den andre viktige delen av hele produksjonen er en tabell over koeffisienter. Den gir verdier av koeffisienter som kan brukes til å bygge modellen for fremtidige prediksjoner.
Nå blir vår, regresjonsligning for prediksjon:
Vekt = 0, 6746 * Høyde - 38, 45508 (helningsverdi for høyde er 0, 6746 … og avskjæring er -38, 45508 …)
Fikk du det du har definert? Du har definert en funksjon der du bare må sette verdien på høyden, og du får vektverdien.
# 2 - Regresjonsanalyse ved bruk av Scatterplot med Trendline i Excel
Nå skal vi se hvordan vi i Excel kan passe en regresjonsligning på en scatterplot i seg selv.
- Velg hele de to kolonnedataene (inkludert overskrifter).
- Klikk på Sett inn og velg Scatter Plot under grafseksjonen som vist på bildet nedenfor.
- Se utgangsgrafen.
- Nå må vi ha en minst kvadratisk regresjonslinje på denne grafen. Hvis du vil legge til denne linjen, høyreklikker du på et av datapunktene på grafen og velger Legg til trendlinje .
- Det vil gjøre det mulig for deg å ha en trendlinje med minst kvadrat av regresjon som nedenfor.
- Under alternativet Format Trendline, merk av i ruten for Display Equation on Chart.
- Den lar deg se ligningen på minst kvadratiske regresjonslinje på grafen.
Dette er ligningen som vi kan forutsi vektverdiene for et gitt sett med høydeverdier.
Ting å huske på om regresjonsanalyse i Excel
- Du kan endre utformingen av trendlinjen under Format Trendline-alternativet i scatter-plott.
- Det anbefales alltid å se på gjenværende tomter mens du gjør regresjonsanalyse ved å bruke Data Analysis ToolPak i Excel. Det gir deg en bedre forståelse av spredningen av de faktiske Y-verdiene og estimerte X-verdiene.
- Enkel lineær regresjon i excel trenger ikke ANOVA og justert R-firkant for å sjekke. Disse funksjonene kan tas i betraktning for Multiple Lineær Regresjon. Noe som er utenfor omfanget av denne artikkelen.
Anbefalte artikler
Dette har vært en guide til regresjonsanalyse i Excel. Her diskuterer vi hvordan du gjør regresjonsanalyse i Excel sammen med Excel-eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -
- Excel-verktøy for dataanalyse
- Beregn ANOVA i Excel
- Hvordan finne Excel bevegelige gjennomsnitt
- Z TEST Eksempler i Excel