Hur man gör regressionsanalys i Excel

Innehållsförteckning:

Anonim

Regression är ett analysverktyg som vi använder för att analysera stora mängder data och göra prognoser och förutsägelser i Microsoft Excel.

Vill du förutse framtiden? Nej, vi kommer inte att lära oss astrologi. Vi är in i siffror och vi kommer att lära oss regressionsanalys i Excel idag.

För att förutsäga framtida uppskattningar kommer vi att studera:

  • REGRESSIONSANALYS MED ANVÄNDNING AV EXCELFUNKTIONER (MANUELL REGRESSIONSFINDNING)
  • REGRESSION ANALYS MED ANVÄNDNING AV EXCEL’S ANALYS TOOLPAK ADD-IN
  • REGRESSIONSKORT I EXCEL

Vi gör det…

Scenario:

Anta att du säljer läsk. Hur coolt blir det om du kan förutsäga:

  • Hur många läsk kommer att säljas nästa år baserat på föregående års uppgifter?
  • Vilka fält behöver fokuseras?
  • Och hur kan du öka din försäljning genom att ändra din strategi?

Det kommer att bli lönsamt fantastiskt. Rätt?… Jag vet. Så låt oss börja.

Du har 11 register över sålda säljare och läskedrycker.

Baserat på dessa uppgifter vill du förutsäga antalet säljare som krävs för att uppnå 2000 försäljning av läsk.

Regressionsekvationen är ett verktyg för att göra så nära uppskattningar. För att göra det måste vi först känna till regression.

REGRESSIONSANALYS MED ANVÄNDNING AV EXCELFUNKTIONER (MANUELL REGRESSIONSFINDNING)

Denna del kommer att få dig att förstå regression bättre än att bara berätta för Excel -regressionsproceduren.

Introduktion:

Enkel linjär regression:

Studiet av sambandet mellan två variabler kallas Simple Linear Regression. Där en variabel beror på den andra oberoende variabeln. Den beroende variabeln kallas ofta med namn som Driven, Response och Target variabel. Och den oberoende variabeln uttalas ofta som en Driving, Predictor eller helt enkelt Oberoende variabel. Dessa namn beskriver dem tydligt.

Låt oss nu jämföra detta med ditt scenario. Du vill veta hur många säljare som krävs för att uppnå 2000 försäljning. Så här är den beroende variabeln antalet säljare och den oberoende variabeln säljs läsk.

Den oberoende variabeln betecknas mestadels som x och beroende variabel som y.

I vårt fall säljs läsk x och antalet säljare är y.

Om vi ​​vill veta hur många läsk som kommer att säljas om vi bestämmer 200 säljare, då blir scenariot tvärtom.

Gå vidare.

Den "enkla" matematiken för linjär regressionsekvation:

Tja, det är inte enkelt. Men Excel gjorde det enkelt att göra.

Vi måste förutsäga det antal säljare som krävs för alla 11 fallen för att få den tolfte närmaste förutsägelsen.

Låt oss säga:

Läsk som säljs är x

Numret av säljare är y

Det förutsagda y (antal säljare) ringde också Regressions ekvation, skulle vara

x*Lutning+avlyssning (koppla av, jag har täckt det)

Nu måste du undra var statistik kommer du att få lutningen och fånga upp. Oroa dig inte, Excel har funktioner för dem. Du behöver inte lära dig att hitta lutningen och fånga upp den manuellt.

Om du vill kommer jag att förbereda en separat handledning för det. Låt mig veta i kommentarsfältet. Det här är några viktiga dataanalysverktyg.

Låt oss nu gå in i vår beräkning:

Steg 1: Förbered det här lilla bordet

Steg 2: Hitta lutningen för regressionslinjen

Excel -funktion för backar är

= SLOPE (kända_y, kända_x)

Dina kända_y är inom räckvidd B2: B12 och kända_x är inom räckvidd C2: C12

I cellen B16, skriv formeln nedan

= LUTNING (B2: B12, C2: C12)

(Obs: Lutning kallas också koefficient för x i regressionsekvationen)

Du kommer att få 0.058409. Avrunda upp till 2 decimaler så får du 0.06.

Steg 3: Hitta skärningspunkten för regressionslinjen

Excel -funktion för avlyssningen är

=INTERCEPT (kända_y, kända_x)

Vi vet vad vår kända x och y

I cellen B17, skriv ner denna formel

= AVTAGNING (B2: B12, C2: C12)

Du får ett värde av -1.1118969. Avrunda till 2 decimaler. Du kommer att få -1.11.

Vår linjära regressionsekvation är = x*0,06 + (-1,11). Nu kan vi lätt förutsäga y beroende på målet x.

Steg 4: Skriv formeln nedan i D2

=C2*$ B $ 16+$ B $ 17(Regressions ekvation)

Du får ett värde av 13.55.

Välj D2 till D13 och tryck på CTRL+D att fylla i formeln i intervallet D2: D13

I cellen D13 du har ditt önskade antal säljare.

Därför för att uppnå målet för 2000 Läskedryssförsäljning, du behöver en uppskattning av 115,71 säljare eller säg 116 eftersom det är olagligt att skära människor i bitar.

Nu med detta kan du enkelt utföra What-If-analys i excel. Ändra bara antalet försäljningar och det kommer att visa dig många säljare kommer det att ta för att nå det försäljningsmålet.

Spela runt för att ta reda på:

Hur mycket personal behöver du för att öka försäljningen?

Hur många försäljningar kommer att öka om du ökar dina säljare?

Gör din uppskattning mer tillförlitlig:

Nu vet du att du behöver 116 säljare för att klara 2000 försäljningar.

Inom analytik sägs och tros ingenting. Du måste ange en procent av tillförlitligheten på din uppskattning. Det är som att ge ett intyg om din ekvation.

Korrelationskoefficientformel:

Det nästa du kommer att bli tillfrågad är hur mycket dessa två variabler är relaterade. I statiska termer måste du berätta korrelationskoefficienten.

Excel -funktion för korrelation är

= CORREL (array1, array2)

I ditt fall är known_x och Know_y's array1 och array2 oavsett.

Ange denna formel i B18

= CORREL ((B2: B12, C2: C12)

Du vill ha 0.919090. Formulera cell B2 i procent. Nu har 92% av korrelation.

Nu, vad det här 92% innebär att. Det betyder, där 92% av chanser till försäljning ökar om du ökar antalet säljare och 92% av försäljningen minskar om du minskar antalet säljare. Det kallas Positiv korrelationskoefficient.

R Squire (R^2):

R Squire -värdet talar om för dig hur stor andel din regressionsekvation inte är en lycka. Hur mycket det är korrekt av uppgifterna.

Excel -funktionen för R squire är RSQ.

RSQ (kända_y, kända_x)

I vårt fall får vi R squire -värde i cell B19.

Ange denna formel i B19

= RSQ (B2: B12, C2: C12)

Så vi har 84% av r Kvadratvärde. Vilket är en mycket bra förklaring till vår regression. Det står att 84% av vår data inte bara är av en slump. Y (antal säljare) är mycket beroende av X (försäljning av läsk).

Det finns många andra tester vi kan göra på denna data för att säkerställa vår regression. Men manuellt blir det en komplex och lång procedur. Det är därför som excel tillhandahåller Analysis Toolpak. Med hjälp av detta verktyg kan vi göra denna regressionsanalys på några sekunder.

REGRESSION I EXCEL ANVÄNDNING AV EXCEL’S ANALYS TOOLPAK ADD-IN

Om du redan vet vad regressionsekvationer är och du bara vill ha dina resultat snabbt så är den här delen för dig. Men om du enkelt vill förstå regressionsekvationer bläddrar du upp till REGRESSION ANALYS MED EXCEL FUNKTIONER (MANUELL REGRESSION FINDING).

Excel tillhandahåller en hel massa verktyg för analys i sitt Analysis Toolpak. Som standard är det inte tillgängligt på fliken Data. Du måste lägga till den. Så låt oss lägga till det först.

Lägga till Analysis Toolpak till Excel 2016

Om du inte vet var dataanalys i excel följer dessa steg

Steg 1: Gå till Excel -alternativ: Fil? Alternativ? Tillägg

Steg 2: Klicka på tillägg. Du kommer att se en lista över tillgängliga tillägg.

Välj Analysis ToolPak och hitta hantera längst ned i fönstret. I hantera väljer du Excel-tillägg och klickar på GO.

Tilläggsfönstret öppnas. Välj här Analysis ToolPak. Klicka sedan på ok -knappen.

Nu kan du komma åt alla funktioner för dataanalys ToolPak från fliken Data.

Använda Analysis ToolPak för regression

Steg 1: Gå till fliken Data, Leta upp dataanalys. Klicka sedan på den.

En dialogruta dyker upp.

Steg 2: Hitta "Regression" i listan med analysverktyg och tryck på OK -knappen.

Regressionen inmatningsfönstret dyker upp. Du kommer att se ett antal tillgängliga inmatningsalternativ. Men för tillfället kommer vi bara att koncentrera oss på Y Range och X Range, och lämna allt annat till standard.

Steg 4: Ge ingångar:

Antal säljare är Y

Försäljning av läsk är X

Därav

  • Y -intervall = B2: B11

Och

  • X Område = C2: C11

För utmatningsområdet har jag valt E4 på samma blad. Du kan välja ett nytt kalkylblad för att få resultat på ett nytt kalkylblad i samma arbetsbok eller en helt ny arbetsbok. När du är klar med dina inmatningar trycker du på OK -knappen.

Resultat:

Du kommer att få en mängd information från din data. Bli inte överväldigad. Du behöver inte konsumera alla rätter.

Vi kommer bara att hantera de resultat som hjälper oss att uppskatta det antal säljare som krävs

Steg 5: Vi känner till regressionsekvationen för uppskattning av y, det är

x*Lutning+Intercept

Vi behöver bara hitta Backe och Genskjuta i resultat.

Och här är de.

Avlyssningskoefficienten nämns tydligt.

Lutningen är skriven som 'X Variabel 1’, Som ibland också nämns som koefficienten för X. Avrunda dem så får vi -1.11 som Intercept och 0,06 som lutning.

Steg 6: Från resultat kan vi driva regressionsekvationen. Och det skulle vara

= x*(0,06) + (-1,11)

Förbered denna tabell i excel.

Tills vidare, x är 2000, som finns i cell E2.

I cell F2 anger du denna formel

= E2*F21+F20

Du kommer att få ett resultat av 115.7052757.

Att avrunda det ger oss 116 av obligatoriska säljare.

Så vi har lärt oss hur man bildar regressionsekvationen manuellt och använder Analysis ToolPak. Hur kan du använda denna ekvation för att uppskatta framtida statistik?

Låt oss nu förstå regressionsutmatningen från Analysis Toolpak.

Förstå regressionsutmatningen:

Det finns ingen fördel om du gör regressionsanalys med hjälp av analysverktygspaket i excel och inte kan tolka dess innebörd.

Sammanfattningsavsnitt:

Som namnet antyder är det en sammanfattning av data.

    1. Multipel R: Den berättar hur anpassad regressionsekvationen är till data. Det kallas också korrelationskoefficienten.

I vårt fall är det 0.919090619 eller 0.92 (runda upp). Det betyder att det finns en 92% chans att öka försäljningen om vi ökar antalet säljare.

    1. R Square: Det berättar tillförlitligheten för hittad regression. Den berättar hur många observationer som ingår i vår regressionslinje. I vårt fall är det 0,844727566 eller 0,85. Det betyder att vår regression är passande med 85%.
    2. Justerad R -kvadrat: Den justerade rutan är bara en mer vittnad version av R square. Främst användbar vid multipel regressionsanalys.
    3. Standard fel: Medan R. Squire berättar hur många datapunkter som faller nära regressionslinjen, berättar standardfelet hur långt en datapunkt kan gå från regressionslinjen.

I vårt fall är det 6.74.

  1. Observation: Detta är helt enkelt antalet observationer, vilket är 11 i vårt exempel.

Anova avsnitt:

Detta avsnitt används knappast vid linjär regression.

  1. df. Det är en grad av frihet. Den används när man beräknar regression manuellt.
  2. SS. Summa kvadrater. Det är bara en summa av kvadrater av varianser. Används för att hitta R squire -värden.
  3. FRÖKEN. Det betyder kvadratvärde.
  4. Och 5. F och Betydelsen av F. Om signifikansen av F (p-värdet på lutningen) är mindre än F-testet kan du kasta nollhypotesen och bevisa din hypotes. På ett enkelt språk kan du dra slutsatsen att det finns någon effekt av x på y när det ändras.

I vårt fall är F 48,96264 och betydelsen av F är 0,000063. Det betyder att vår regression passar data.

Regressionsavsnitt:

I detta avsnitt har vi de två viktigaste värdena för vår regressionsekvation.

  1. Avlyssning: Vi har en avlyssning här som berättar var x-avlyssningar på Y. Detta är en viktig del av regressionsekvationen. Det är -1,11 i vårt fall.
  2. X -variabel 1 (Backe). Kallas även koefficienten för x. Det definierar tangenten för regressionslinjen.

REGRESSIONSKORT I EXCEL

I excel är det enkelt att plotta ett regressionsdiagram. Följ bara dessa steg. Följ dessa enkla steg för att lägga till regressionsdiagram i Excel 2016, 2013 och 2010.

Steg 1. Ha dina kända x: er i den första kolumnen och vet y: erna i den andra.

I vårt fall vet vi att Known_x är läskedrycker som säljs. Och kända_y är säljare.

Steg 2. Välj dina kända x- och y -intervall.

Steg 3: Gå till fliken Infoga och klicka på spridningsdiagrammet.

Du kommer att ha ett diagram som ser ut så här.

Steg 4. Lägg till trendlinjen: Gå till layouten och hitta trendlinjen i analysavsnittet.

Under alternativet Trendline, klicka på Linjär trendlinje.

Du kommer att få din graf att se ut så här.

Detta är din regressionsgraf.

Om du nu lägger till data nedan och utökar den valda informationen. Du kommer att se en förändring i din graf.

För vårt exempel lade vi till 2000 i läskedrycken och sålde säljarna tomma. Och när vi utökar grafens intervall är detta vad vi kommer att ha.

Det kommer att ge det erforderliga antalet säljare för att göra 2000 försäljning av läsk i grafisk form. Vilket är något under 120 i grafen. Och från vår regressionsekvation vet vi att den är 116.

I den här artikeln försökte jag täcka allt under Excel -regressionsanalys. Jag förklarade regression i excel 2016. Regression i excel 2010 och excel 2013 är samma som i excel 2016.

För ytterligare frågor om detta ämne, använd kommentarsfältet. Ställ en fråga, ge en åsikt eller bara nämna mina grammatiska misstag. Allt är välkommet. Tveka bara inte med att använda kommentarsektionen.

Hur man beräknar MODE -funktionen i Excel

Hur man beräknar medelfunktionen i Excel

Hur man skapar standardavvikelsediagram

Beskrivande statistik i Microsoft Excel 2016

Hur man använder Excel NORMDIST -funktion

Hur man använder Pareto -diagrammet och analysen

Populära artiklar:

50 Excel -genväg för att öka din produktivitet

Så här använder du VLOOKUP -funktionen i Excel

Så här använder du funktionen RÄTTA i Excel 2016

Hur man använder SUMIF -funktionen i Excel