Excel-regressionsanalyse (indholdsfortegnelse)

  • Regressionsanalyse i Excel
  • Forklaring af regression matematisk
  • Sådan udføres lineær regression i Excel?
    • # 1 - Regressionsværktøj ved hjælp af analyseværktøjPak i Excel
    • # 2 - Regressionsanalyse ved hjælp af Scatterplot med Trendline i Excel

Regressionsanalyse i Excel

Lineær regression er en statistisk teknik, der undersøger det lineære forhold mellem en afhængig variabel og en eller flere uafhængige variabler.

  • Afhængig variabel (aka svar / udgangsvariabel): Er variablen for din interesse, og som du ønskede at forudsige baseret på de tilgængelige oplysninger om uafhængige variabler.
  • Uafhængig variabel (alias forklarende / forudsigelsesvariabel): Er / er den eller de variabler, som responsvariablen er afhængig af. Hvilket betyder, at dette er de variabler, der bruger hvilken responsvariabel, der kan forudsiges.

Lineært forhold betyder, at ændringen i en eller flere uafhængige variabler forårsager en ændring i den afhængige variabel.

Der er stort set to typer lineære forhold også.

  1. Positivt lineært forhold: Når den uafhængige variabel stiger, stiger den afhængige variabel også.
  2. Negativt lineært forhold: Når den uafhængige variabel stiger, falder den afhængige variabel.

Dette var nogle af forudsætningerne, før du faktisk gik videre til regressionsanalyse i Excel.

Der er to grundlæggende måder at udføre lineær regression i Excel ved hjælp af:

  • Regressionsværktøj gennem Analyse ToolPak
  • Spredekort med trendlinie

Der er faktisk endnu en metode, der bruger manuelle formler til at beregne lineær regression. Men hvorfor skal du gå efter det, når excel gør beregninger for dig?

Derfor vil vi kun tale om de to metoder, der er diskuteret ovenfor.

Antag, at du har en data om højde og vægt på 10 personer. Hvis du plot disse oplysninger gennem et diagram, så lad os se, hvad det giver.

Som ovenstående skærmbillede viser, kan det lineære forhold findes i Højde og vægt gennem grafen. Bliv ikke meget involveret i graf nu, vi vil under alle omstændigheder grave det dybt i den anden del af denne artikel.

Forklaring af regression matematisk

Vi har et matematisk udtryk for lineær regression som nedenfor:

Y = aX + b + ε

Hvor,

  • Y er en afhængig variabel eller responsvariabel.
  • X er en uafhængig variabel eller prediktor.
  • a er hældningen for regressionslinjen. Hvilket repræsenterer, at når X ændres, er der en ændring i Y med "a" enheder.
  • b opfanger. Det er værdien Y tager, når værdien af ​​X er nul.
  • ε er den tilfældige fejlbetegnelse. Forekommer, fordi den forudsagte værdi af Y aldrig vil være nøjagtigt den samme som den faktiske værdi for givet X. Denne fejlbetegnelse behøver vi ikke bekymre os om. Da der er nogle software, der beregner denne fejlbetegnelse i backend for dig. Excel er en af ​​den software.

I dette tilfælde bliver ligningen,

Y = aX + b

Som kan repræsenteres som:

Vægt = a * Højde + b

Vi prøver at finde ud af værdierne for disse a og b ved hjælp af metoder, vi har diskuteret ovenfor.

Sådan udføres lineær regression i Excel?

Den yderligere artikel forklarer det grundlæggende i regressionsanalyse i Excel og viser et par forskellige måder at udføre lineær regression i Excel.

Du kan downloade denne regressionsanalyse Excel-skabelon her - regressionsanalyse Excel-skabelon

# 1 - Regressionsværktøj ved hjælp af analyseværktøjPak i Excel

For vores eksempel prøver vi at passe regression til vægtværdier (som er afhængig variabel) ved hjælp af højdeværdier (som er en uafhængig variabel).

  • I excel-regnearket skal du klikke på Dataanalyse (findes under Analysegruppe ) under Data.

  • Søg efter regression . Vælg det, og tryk på ok.

  • Brug følgende input under regressionsruden, der åbnes.

  • Input Y Range : Vælg cellerne, der indeholder din afhængige variabel (i dette eksempel B1: B11)

  • Input X-område : Vælg cellerne, der indeholder din uafhængige variabel (i dette eksempel A1: A11).

  • Marker afkrydsningsfeltet Labels, hvis dine data har kolonnenavne (i dette eksempel har vi kolonnenavne).

  • Tillidsniveauet er som standard indstillet til 95%, hvilket kan ændres i henhold til brugernes krav.

  • Under Output-indstillinger kan du tilpasse, hvor du vil se output fra regressionsanalyse i Excel. I dette tilfælde ønsker vi at se output på det samme ark. Derfor givet område i overensstemmelse hermed.

  • Under indstillingen Residuals har du valgfri input som Residuals, Restplots, Standardised Residuals, Line Fit Plots, som du kan vælge efter dit behov. I dette tilfælde skal du markere afkrydsningsfeltet Residuals, så vi kan se spredningen mellem forudsagte og faktiske værdier.

  • Under indstillingen Normal sandsynlighed kan du vælge Normale sandsynlighedsplaner, som kan hjælpe dig med at kontrollere normaliteten af ​​forudsigere. Klik på OK .

  • Excel beregner regressionsanalyse for dig på en brøkdel af sekunder.

Indtil her var det let og ikke så logisk. Det er en vanskelig opgave at fortolke dette output og give værdifuld indsigt herfra.

En vigtig del af hele dette output er R Square / Justeret R Square under SAMMENFATTET UDGANG. Hvilket giver information, hvor god vores model er egnet. I dette tilfælde er R- værdien 0, 9547. Hvilket fortolker, at modellen har en nøjagtighed på 95, 47% (god pasform). Eller på et andet sprog forklares information om Y-variabel 95, 47% med X-variabel.

Den anden vigtige del af hele output er en tabel over koefficienter. Det giver værdier for koefficienter, der kan bruges til at opbygge modellen til fremtidige forudsigelser.

Nu bliver vores regressionsligning til forudsigelse:

Vægt = 0, 6746 * Højde - 38, 45508 (Hældningsværdi for Højde er 0, 6746 … og Aflytning er -38, 45508…)

Fik du det, du har defineret? Du har defineret en funktion, hvor du nu bare skal lægge værdien på Højde, og du får vægtværdien.

# 2 - Regressionsanalyse ved hjælp af Scatterplot med Trendline i Excel

Nu skal vi se, hvordan vi i Excel kan passe til en regressionsligning på en scatterplot i sig selv.

  • Vælg hele dine to kolonnedata (inklusive overskrifter).
  • Klik på Indsæt og vælg Spredningsdiagram under sektionen af ​​grafer som vist på billedet herunder.

  • Se outputgrafen.

  • Nu skal vi have en mindst kvadratisk regressionslinje på denne graf. For at tilføje denne linje skal du højreklikke på et af datapunkterne på grafen og vælge Tilføj trendlinjeindstilling .

  • Det giver dig mulighed for at have en trendlinje med mindst regressionsfelt som nedenfor.

  • Marker afkrydsningsfeltet for Displayligning på kort under indstillingen Format Trendline .

  • Det giver dig mulighed for at se ligningen på den mindst kvadratiske regressionslinje på grafen.

Dette er ligningen, som vi kan forudsige vægtværdierne for et givet sæt højdeværdier på.

Ting at huske på regressionsanalyse i Excel

  • Du kan ændre layout af trendlinje under indstillingen Format Trendline i scatter plot.
  • Det anbefales altid at kigge på restdiagrammer, mens du laver regressionsanalyse ved hjælp af Data Analysis ToolPak i Excel. Det giver dig en bedre forståelse af spredningen af ​​de faktiske Y-værdier og estimerede X-værdier.
  • Enkel lineær regression i excel behøver ikke ANOVA og justeret R-firkant til at kontrollere. Disse funktioner kan tages i betragtning ved Multiple Lineær Regression. Hvilket er uden for denne artikels rækkevidde.

Anbefalede artikler

Dette har været en guide til regressionsanalyse i Excel. Her diskuterer vi, hvordan man udfører regressionsanalyse i Excel sammen med exceleksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -

  1. Excel-værktøj til dataanalyse
  2. Beregn ANOVA i Excel
  3. Sådan finder du bevægelige gennemsnit i Excel
  4. Z TEST Eksempler i Excel

Kategori: