Linjär regression i Excel (innehållsförteckning)

  • Introduktion till linjär regression i Excel
  • Metoder för att använda linjär regression i Excel

Introduktion till linjär regression i Excel

Linjär regression är en statistisk teknik / metod som används för att studera förhållandet mellan två kontinuerliga kvantitativa variabler. I denna teknik används oberoende variabler för att förutsäga värdet på en beroende variabel. Om det bara finns en oberoende variabel, är det en enkel linjär regression, och om ett antal oberoende variabler är mer än en, är det multipel linjär regression. Linjära regressionsmodeller har en relation mellan beroende och oberoende variabler genom att anpassa en linjär ekvation till de observerade data. Linjär refererar till det faktum att vi använder en linje för att passa våra data. De beroende variablerna som används i regressionsanalys kallas också respons eller förutspådda variabler, och oberoende variabler kallas också förklarande variabler eller prediktorer.

En linjär regressionslinje har en ekvation av typen: Y = a + bX;

Var:

  • X är den förklarande variabeln,
  • Y är den beroende variabeln,
  • b är linjens lutning,
  • a är y-skärning (dvs. värdet på y när x = 0).

Minstkvadratmetoden används vanligtvis vid linjär regression som beräknar den bästa passningslinjen för observerade data genom att minimera summan av kvadraters avvikelse för datapunkter från linjen.

Metoder för att använda linjär regression i Excel

Detta exempel lär dig metoderna för att utföra linjär regressionsanalys i Excel. Låt oss titta på några metoder.

Du kan ladda ner den här linjära regression Excel-mallen här - linjär regression Excel-mall

Metod # 1 - Spridda diagram med en trendlinje

Låt oss säga att vi har en datasats med vissa individer med deras ålder, biomassindex (BMI) och det belopp som de spenderar på medicinska utgifter på en månad. Nu med en inblick i individens egenskaper som ålder och BMI, vill vi hitta hur dessa variabler påverkar de medicinska kostnaderna, och därmed använda dessa för att utföra regression och uppskatta / förutsäga de genomsnittliga medicinska kostnaderna för vissa specifika individer. Låt oss först se hur endast ålder påverkar medicinska utgifter. Låt oss se datasatsen:

Belopp på medicinska utgifter = b * ålder + a

  • Välj de två kolumnerna i datasatsen (x och y), inklusive rubriker.

  • Klicka på "Infoga" och utöka rullgardinsmenyn för "Scatter Chart" och välj "Scatter" -miniatyren (första)

  • Nu kommer en spridningsdiagram att visas och vi skulle dra regressionslinjen för detta. För att göra detta högerklickar du på vilken datapunkt som helst och väljer 'Lägg till trendlinje'

  • I rutan "Format trendlinje" till höger väljer du "Linjär trendlinje" och "Visa ekvation på kartan".

  • Välj 'Visa ekvation på diagram'.

Vi kan improvisera diagrammet enligt våra krav, som att lägga till axel-titlar, ändra skala, färg och linjetyp.

Efter att ha improviserat diagrammet är det resultatet vi får.

Obs: I denna typ av regressionsgraf bör den beroende variabeln alltid vara på y-axeln och oberoende på x-axeln. Om diagrammet plottas i omvänd ordning byter du antingen axlarna i ett diagram eller byter kolumnerna i datasatsen.

Metod # 2 - AnalysverktygPak-tilläggsmetod

Analysverktygspaket är ibland inte aktiverat som standard och vi måste göra det manuellt. Att göra så:

  • Klicka på "File" -menyn.

Klicka sedan på "Alternativ".

  • Välj "Excel-tillägg" i rutan "Hantera" och klicka på "Gå"

  • Välj 'Analysverktygspak' -> 'OK'

Detta kommer att lägga till "Data Analys" -verktyg till fliken "Data". Nu kör vi regressionsanalysen:

  • Klicka på "Dataanalys" på fliken "Data"

  • Välj "Regression" -> "OK"

  • En regressionsdialogruta visas. Välj In-Y-intervall och Input X-intervall (medicinska utgifter respektive ålder). Vid multipel linjär regression kan vi välja fler kolumner med oberoende variabler (som om vi vill se effekten av BMI också på medicinska utgifter).
  • Markera rutan "Etiketter" för att inkludera rubriker.
  • Välj önskat alternativ för "output".
  • Markera kryssrutan "rester" och klicka på "OK".

Nu kommer vår regressionsanalysutgång att skapas i ett nytt kalkylblad med angivande av regressionsstatistik, ANOVA, rester och koefficienter.

Utgångstolkning:

  • Regressionsstatistik berättar hur väl regressionsekvationen passar uppgifterna:

  • Multipel R är korrelationskoefficienten som mäter styrkan i linjärt samband mellan två variabler. Det ligger mellan -1 och 1, och dess absoluta värde visar relationens styrka med ett stort värde som indikerar starkare förhållande, lågt värde indikerar negativt och nollvärde som indikerar inget samband.
  • R Square är bestämningskoefficienten som används som en indikator på passformens godhet. Det ligger mellan 0 och 1, med ett värde nära 1 som indikerar att modellen passar bra. I detta fall förklaras 0, 57 = 57% av y-värdena av x-värdena.
  • Justerat R-kvadrat är R-kvadrat justerat för antalet prediktorer vid multipel linjär regression.
  • Standardfel visar precisionen för regressionsanalys.
  • Observationer visar antalet modellobservationer.
  • Anova berättar graden av variation i regressionsmodellen.

Detta används vanligtvis inte för enkel linjär regression. Emellertid indikerar "Betydelse F-värdena" hur tillförlitliga våra resultat är, med ett värde större än 0, 05 som tyder på att välja en annan prediktor.

  • Koefficienter är den viktigaste delen som används för att bygga regressionsekvationen.

Så vår regressionsekvation skulle vara: y = 16.891 x - 355.32. Detta är detsamma som med metod 1 (spriddiagram med en trendlinje).

Om vi ​​nu vill förutse genomsnittliga medicinska utgifter när åldern är 72:

Så y = 16.891 * 72 -355.32 = 860.832

Så på detta sätt kan vi förutsäga värden på y för alla andra värden på x.

  • Residualer indikerar skillnaden mellan faktiska och förutspådda värden.

Den sista metoden för regression används inte så vanligt och kräver statistiska funktioner som lutning (), avlyssning (), korrel (), etc. för att utföra regressionsanalys.

Saker att komma ihåg om linjär regression i Excel

  • Regressionsanalys används vanligtvis för att se om det finns ett statistiskt signifikant samband mellan två uppsättningar variabler.
  • Det används för att förutsäga värdet på den beroende variabeln baserat på värden för en eller flera oberoende variabler.
  • Varje gång vi vill anpassa en linjär regressionsmodell till en datagrupp, bör dataintervallet noga observeras som om vi använder en regressionsekvation för att förutsäga något värde utanför detta intervall (extrapolering), då kan det leda till fel resultat.

Rekommenderade artiklar

Detta är en guide till linjär regression i Excel. Här diskuterar vi hur man gör linjär regression i Excel tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -

  1. Hur förbereder du lönelistan i Excel?
  2. Användning av MAX-formler i Excel
  3. Handledning om cellreferenser i Excel
  4. Skapa regressionsanalys i Excel
  5. Linjär programmering i Excel

Kategori:

Excel-tips