Excel-regressionsanalys (innehållsförteckning)
- Regressionsanalys i Excel
- Förklaring av regression matematiskt
- Hur man utför linjär regression i Excel?
- # 1 - Regressionsverktyg med hjälp av analysverktygPak i Excel
- # 2 - Regressionsanalys med Scatterplot med Trendline i Excel
Regressionsanalys i Excel
Linjär regression är en statistisk teknik som undersöker det linjära förhållandet mellan en beroende variabel och en eller flera oberoende variabler.
- Beroende Variabel (aka svar / utfallsvariabel): Är variabeln för ditt intresse och som du ville förutsäga baserat på informationen tillgänglig för Independent variabel (er).
- Oberoende variabel (alias förklarande / förutsägbar variabel): Är / är den eller de variabler som svarsvariabeln är beroende på. Vilket innebär att det är dessa variabler som använder vilken svarsvariabel som kan förutsägas.
Linjärt samband innebär att förändringen i en oberoende variabel orsakar en förändring i den beroende variabeln.
Det finns i princip två typer av linjära förhållanden också.
- Positivt linjärt förhållande: När oberoende variabel ökar ökar den beroende variabeln också.
- Negativ linjär relation: När oberoende variabel ökar minskar den beroende variabeln.
Detta var några av förutsättningarna innan du faktiskt fortsatte med regressionsanalys i Excel.
Det finns två grundläggande sätt att utföra linjär regression i Excel med:
- Regressionsverktyg genom AnalysverktygPak
- Spriddiagram med trendlinje
Det finns faktiskt ytterligare en metod som använder manuella formler för att beräkna linjär regression. Men varför ska du gå efter det när excel gör beräkningar för dig?
Därför kommer vi bara att prata om de två metoderna som diskuterats ovan.
Anta att du har en data om höjd och vikt på 10 individer. Om du plottar denna information genom ett diagram, låt oss se vad den ger.
Som ovanstående skärmbild visar kan den linjära relationen hittas i höjd och vikt genom diagrammet. Bli inte mycket involverad i grafen nu, vi kommer hur som helst att gräva det djupt i den andra delen av denna artikel.
Förklaring av regression matematiskt
Vi har ett matematiskt uttryck för linjär regression enligt nedan:
Y = aX + b + ε
Var,
- Y är en beroende variabel eller svarsvariabel.
- X är en oberoende variabel eller prediktor.
- a är lutningen för regressionslinjen. Som representerar att när X förändras, sker det en förändring i Y med "a" -enheter.
- b fångar upp. Det är värdet Y tar när värdet på X är noll.
- ε är den slumpmässiga feltermen. Förekommer eftersom det förutsagda värdet på Y aldrig kommer att vara exakt detsamma som det verkliga värdet för en given X. Den här feltermen behöver vi inte oroa oss för. Eftersom det finns vissa programvara som gör beräkningen av denna feltermin i backend för dig. Excel är en av den programvaran.
I så fall blir ekvationen,
Y = aX + b
Som kan representeras som:
Vikt = a * Höjd + b
Vi försöker ta reda på värdena på dessa a och b med hjälp av metoder vi har diskuterat ovan.
Hur man utför linjär regression i Excel?
Den ytterligare artikeln förklarar grunderna för regressionsanalys i Excel och visar några olika sätt att göra linjär regression i Excel.
Du kan ladda ner denna Excel-mall för regressionsanalys här - Excel-mall för regressionsanalys# 1 - Regressionsverktyg med hjälp av analysverktygPak i Excel
För vårt exempel försöker vi anpassa regression för viktvärden (som är beroende variabel) med hjälp av höjdvärden (som är en oberoende variabel).
- I Excel-kalkylbladet klickar du på Data Analys (närvarande under Analysegruppen ) under Data.
- Sök efter regression . Välj det och tryck på ok.
- Använd följande ingångar under regressionsfönstret som öppnas.
- Inmatningsområde Y : Välj celler som innehåller din beroende variabel (i detta exempel B1: B11)
- Input X Range : Välj celler som innehåller din oberoende variabel (i detta exempel A1: A11).
- Markera rutan med namnet Etiketter om dina data har kolumnnamn (i det här exemplet har vi kolumnnamn).
- Konfidensnivån är inställd på 95% som standard, vilket kan ändras enligt användarnas krav.
- Under Output-alternativ kan du anpassa var du vill se utgången från regressionsanalys i Excel. I det här fallet vill vi se utgången på samma ark. Därför givet intervall i enlighet därmed.
- Under alternativet Residuals har du valfria ingångar som Residuals, Restplots, Standardized Residuals, Line Fit Plots som du kan välja efter ditt behov. I det här fallet ska du markera kryssrutan Residuals så att vi kan se spridningen mellan förutsagda och faktiska värden.
- Under alternativet Normal sannolikhet kan du välja Normala sannolikhetsplaner som kan hjälpa dig att kontrollera normaliteten hos prediktorer. Klicka på OK .
- Excel kommer att beräkna regressionsanalys för dig på en bråkdel av sekunder.
Tills här var det enkelt och inte så logiskt. Men att tolka denna utgång och göra värdefulla insikter från den är en svår uppgift.
En viktig del av hela denna utgång är R Square / Justerad R Square under SAMMANFATTAD UTGÅNG-tabell. Som ger information, hur bra vår modell passar. I detta fall är R- kvadratvärdet 0, 9547. Vilket tolkar att modellen har 95, 47% noggrannhet (bra passform). Eller på ett annat språk förklaras information om Y-variabel 95, 47% av X-variabel.
Den andra viktiga delen av hela utgången är en tabell över koefficienter. Den ger värden på koefficienter som kan användas för att bygga modellen för framtida förutsägelser.
Nu blir vår regressionsekvation för förutsägelse:
Vikt = 0, 6746 * Höjd - 38, 45508 ( lutningsvärde för höjd är 0, 6746 … och skärning är -38, 45508 …)
Fick du vad du har definierat? Du har definierat en funktion där du nu bara måste sätta värdet på höjden så får du viktvärdet.
# 2 - Regressionsanalys med Scatterplot med Trendline i Excel
Nu ska vi se hur vi i Excel kan passa en regressionsekvation på en spridningsplott själv.
- Välj hela dina två kolumnuppgifter (inklusive rubriker).
- Klicka på Infoga och välj Scatter Plot under diagramavsnittet som visas på bilden nedan.
- Se utgångsgrafen.
- Nu måste vi ha en minst kvadratisk regressionslinje på denna graf. För att lägga till den här raden högerklickar du på någon av datapunkterna i diagrammet och väljer Lägg till trendlinje .
- Det gör det möjligt för dig att ha en trendlinje med minst kvadrat av regression som nedan.
- Under alternativet Format trendlinje markerar du rutan för Displayekvation i diagrammet.
- Det gör att du kan se ekvationen för den minst kvadratiska regressionslinjen på diagrammet.
Detta är ekvationen med vilken vi kan förutsäga viktvärdena för varje given uppsättning höjdvärden.
Saker att komma ihåg om regressionsanalys i Excel
- Du kan ändra layouten för trendlinjen under alternativet Format trendlinje i scatter-plot.
- Det rekommenderas alltid att titta på återstående tomter medan du gör regressionsanalys med Data Analysis ToolPak i Excel. Det ger dig en bättre förståelse för spridningen av de verkliga Y-värdena och uppskattade X-värden.
- Enkel linjär regression i Excel behöver inte ANOVA och Justerat R Square för att kontrollera. Dessa funktioner kan beaktas för multipel linjär regression. Vilket ligger utanför denna artikel.
Rekommenderade artiklar
Detta har varit en guide till regressionsanalys i Excel. Här diskuterar vi hur man gör regressionsanalys i Excel tillsammans med Excel-exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -
- Excel-verktyg för dataanalys
- Beräkna ANOVA i Excel
- Hur man hittar rörliga medel i Excel
- Z TEST Exempel i Excel