Regression er et analyseværktøj, som vi bruger til at analysere store datamængder og lave prognoser og forudsigelser i Microsoft Excel.
Vil du forudsige fremtiden? Nej, vi skal ikke lære astrologi. Vi er til tal, og vi vil lære regressionsanalyse i Excel i dag.
For at forudsige fremtidige skøn vil vi undersøge:
- REGRESSION ANALYSE VED BRUG AF EXCEL FUNKTIONER (MANUEL REGRESSION FINDING)
- REGRESSIONSANALYSE VED AT BRUGE EXCEL’S ANALYSE TOOLPAK ADD-IN
- REGRESSIONSKORT I EXCEL
Lad os gøre det…
Scenarie:
Lad os antage, at du sælger læskedrikke. Hvor fedt bliver det, hvis du kan forudsige:
- Hvor mange læskedrikke vil blive solgt næste år baseret på tidligere års data?
- Hvilke felter skal fokuseres?
- Og hvordan kan du øge dit salg ved at ændre din strategi?
Det bliver rentabelt fantastisk. Ikke?… Jeg ved det. Så lad os komme i gang.
Du har 11 optegnelser over sælger og læskedrikke.
Nu baseret på disse data vil du forudsige antallet af sælgere, der kræves for at opnå 2000 salg af læskedrikke.
Regressionsligningen er et værktøj til at lave så tætte skøn. For at gøre det skal vi først kende regression.
REGRESSION ANALYSE VED BRUG AF EXCEL FUNKTIONER (MANUEL REGRESSION FINDING)
Denne del får dig til at forstå regression bedre end bare at fortælle excel regression procedure.
Introduktion:
Enkel lineær regression:
Undersøgelsen af forholdet mellem to variabler kaldes Simple Linear Regression. Hvor den ene variabel afhænger af den anden uafhængige variabel. Den afhængige variabel kaldes ofte ved navne som f.eks. Driven, Response og Target variabel. Og den uafhængige variabel udtales ofte som en Driving, Predictor eller simpelthen uafhængig variabel. Disse navne beskriver dem klart.
Lad os nu sammenligne dette med dit scenario. Du vil gerne vide, hvor mange sælgere der kræves for at opnå 2000 salg. Så her er den afhængige variabel antallet af sælgere, og den uafhængige variabel sælges læskedrikke.
Den uafhængige variabel betegnes for det meste som x og afhængig variabel som y.
I vores tilfælde sælges læskedrikke x og antallet af sælgere er y.
Hvis vi vil vide, hvor mange læskedrikke, der vil blive solgt, hvis vi udpeger det 200 sælgere, så vil scenariet være omvendt.
Komme videre.
Den "simple" matematik for lineær regressionsligning:
Det er ikke enkelt. Men Excel gjorde det let at gøre.
Vi er nødt til at forudsige det nødvendige antal sælgere for alle 11 sager for at få den 12. tætteste forudsigelse.
Lad os sige:
Sodavand solgt er x
Nummeret af Sælgere er y
Det forudsagte y (antal sælgere) også kaldet Regressionsligning, ville være
| x*Hældning+aflytning (slap af, jeg har dækket det) |
Nu må du undre dig over, hvor stat vil du få hældningen og aflytte. Bare rolig, excel har funktioner til dem. Du behøver ikke at lære at finde skråningen og opfange den manuelt.
Hvis du vil, udarbejder jeg en separat vejledning til det. Lad mig vide i kommentarfeltet. Disse er nogle vigtige dataanalyseværktøjer.
Lad os nu gå ind i vores beregning:
Trin 1: Forbered dette lille bord
Trin 2: Find hældningen af regressionslinjen
Excel Funktion til skråninger er
| = SLOPE (kendt_y’er, kendte_x’er) |
Dine kendte_y er inden for rækkevidde B2: B12 og kendt_x er inden for rækkevidde C2: C12
I cellen B16, skriv formlen herunder
| = Hældning (B2: B12, C2: C12) |
(Bemærk: Hældning kaldes også koefficient for x i regressionsligningen)
Du vil få 0.058409. Rund op til 2 decimaler, og du får 0.06.
Trin 3: Find skæringspunktet for regressionslinje
Excel -funktion til aflytningen er
| =INTERCEPT (kendt_y’er, kendt_x’er) |
Vi ved, hvad vores kendte x’er og y’er
I cellen B17, skriv denne formel ned
| = INTERCEPT (B2: B12, C2: C12) |
Du får en værdi på -1.1118969. Afrund til 2 decimaler. Du vil få -1.11.
Vores lineære regressionsligning er = x*0,06 + (-1,11). Nu kan vi let forudsige mulig y afhængigt af målet x.
Trin 4: Skriv formlen herunder i D2
| =C2*$ B $ 16+$ B $ 17(Regressionsligning) |
Du får en værdi på 13.55.
Vælg D2 til D13, og tryk på CTRL+D for at udfylde formlen i området D2: D13
I cellen D13 du har dit nødvendige antal sælgere.
| Derfor for at nå målet om 2000 Sodavands salg, du har brug for et skøn på 115,71 sælgere eller sig 116, da det er ulovligt at skære mennesker i stykker. |
Nu ved hjælp af dette kan du let foretage What-If analyse i excel. Bare ændre antallet af salg, og det vil vise dig mange sælgere, vil det tage for at nå dette salgsmål.
Spil rundt for at finde ud af:
Hvor meget arbejdsstyrke har du brug for for at øge salget?
Hvor mange salg vil stige, hvis du øger dine sælgere?
Gør dit estimat mere pålideligt:
Nu ved du, at du har brug for 116 sælgere for at få 2000 salg gennemført.
I analytics siges og troes intet bare. Du skal angive en procentdel af pålidelighed på dit skøn. Det er som at give et certifikat for din ligning.
Korrelationskoefficientformel:
Den næste ting, du bliver spurgt om, er, hvor meget disse to variabler hænger sammen. I statiske termer skal du fortælle korrelationskoefficienten.
Excel -funktion til korrelation er
| = CORREL (array1, array2) |
I dit tilfælde er kendt_x’er og Know_y’er array1 og array2 uanset.
Indtast denne formel i B18
| = CORREL ((B2: B12, C2: C12) |
Du vil have 0.919090. Formuler celle B2 i procentdelen. Nu har 92% af korrelation.
Hvad nu 92% midler. Det betyder, der 92% af chancer for salg stiger, hvis du øger antallet af sælgere og 92% af salget falder, hvis du reducerer antallet af sælgere. Det kaldes Positiv korrelationskoefficient.
R Squire (R^2):
R Squire -værdi fortæller dig, hvor stor en procentdel din regressionsligning ikke er en tilfældighed. Hvor meget det er korrekt af de leverede data.
Excel -funktionen for R squire er RSQ.
| RSQ (kendt_y’er, kendte_x’er) |
I vores tilfælde får vi R squire -værdi i celle B19.
Indtast denne formel i B19
| = RSQ (B2: B12, C2: C12) |
Så vi har 84% af r Kvadratværdi. Hvilket er en meget god forklaring på vores regression. Det siger, at 84% af vores data bare ikke er tilfældigt. Y (antal sælgere) er meget afhængig af X (salg af læskedrikke).
Der er mange andre tests, vi kan udføre på disse data for at sikre vores regression. Men manuelt vil det være en kompleks og lang procedure. Derfor leverer excel Analysis Toolpak. Ved hjælp af dette værktøj kan vi lave denne regressionsanalyse på få sekunder.
REGRESSION I EXCEL VED AT BRUGE EXCEL’S ANALYSE TOOLPAK ADD-IN
Hvis du allerede ved, hvad regressionsligninger er, og du bare vil have dine resultater hurtigt, så er denne del noget for dig. Men hvis du let vil forstå regressionsligninger, skal du rulle op til REGRESSION ANALYSE VED BRUG AF EXCELFUNKTIONER (MANUEL REGRESSION FINDING).
Excel giver en hel masse værktøjer til analyse i sit analyseværktøjspakke. Som standard er den ikke tilgængelig på fanen Data. Du skal tilføje det. Så lad os tilføje det først.
Tilføjelse af Analysis Toolpak til Excel 2016
Hvis du ikke ved, hvor er dataanalyse i excel, skal du følge disse trin
Trin 1: Gå til Excel -indstillinger: Fil? Muligheder? Tilføjelser
Trin 2: Klik på tilføjelsesprogrammer. Du får vist en liste over tilgængelige tilføjelsesprogrammer.
Vælg Analysis ToolPak, og find administrer nederst i vinduet. Vælg Administrer Excel-tilføjelsesprogrammer, og klik på GO.
Tilføjelsesvinduet åbnes. Vælg her Analysis ToolPak. Klik derefter på knappen ok.
Nu kan du få adgang til alle funktioner i dataanalyse ToolPak fra fanen Data.
Brug af Analyse ToolPak til regression
Trin 1: Gå til fanen Data, Find dataanalyse. Klik derefter på den.
En dialogboks vil dukke op.
Trin 2: Find 'Regression' på listen Analyseværktøjer, og tryk på knappen OK.
Regressionen inputvindue vil dukke op. Du vil se en række tilgængelige inputmuligheder. Men lige nu vil vi bare koncentrere os om Y Range og X Range, og lade alt andet stå til standard.
Trin 4: Angiv input:
Antal sælgere er Y
Salg af sodavand er x
Derfor
- Y -område = B2: B11
Og
- X Område = C2: C11
For outputområdet har jeg valgt E4 på det samme ark. Du kan vælge et nyt regneark for at få resultater på et nyt regneark i den samme projektmappe eller en komplet ny projektmappe. Når du er færdig med dine input, skal du trykke på knappen OK.
Resultater:
Du vil blive serveret med en række oplysninger fra dine data. Bliv ikke overvældet. Du behøver ikke at indtage alle retterne.
Vi behandler kun de resultater, der hjælper os med at estimere det nødvendige antal sælgere
Trin 5: Vi kender regressionsligningen til estimering af y, det er
x*Hældning+aflytning
Vi mangler bare at finde Hældning og Aflytning i resultater.
Og her er de.
Aflytningskoefficienten er tydeligt nævnt.
Hældningen er skrevet som 'X Variabel 1’, Nogle gange også nævnt som koefficienten for X. Afrund dem, og vi får -1.11 som aflytning og 0,06 som hældning.
Trin 6: Ud fra resultater kan vi drive regressionsligningen. Og det ville være
= x*(0,06) + (-1,11)
Forbered denne tabel i excel.
For nu, x er 2000, som er i celle E2.
Indtast denne formel i celle F2
= E2*F21+F20
Du får et resultat af 115.7052757.
At afrunde det vil give os 116 af påkrævede sælgere.
Så vi har lært, hvordan man danner regressionsligningen manuelt og ved hjælp af Analysis ToolPak. Hvordan kan du bruge denne ligning til at estimere fremtidig statistik?
Lad os nu forstå regressionsoutput givet af Analysis Toolpak.
Forståelse af regressionsoutput:
Der er ingen fordel, hvis du laver regressionsanalyse ved hjælp af analyseværktøjspakke i excel og ikke kan fortolke dens betydning.
Resuméafsnit:
Som navnet antyder, er det et resumé af dataene.
-
- Multiple R: Det fortæller, hvor passende regressionsligningen er til dataene. Det kaldes også korrelationskoefficienten.
I vores tilfælde er det 0.919090619 eller 0.92 (runde op). Det betyder, at der er en 92% chance for en stigning i salget, hvis vi øger vores sælgere.
-
- R Square: Det fortæller pålideligheden af fundet regression. Det fortæller os, hvor mange observationer der er en del af vores regressionslinje. I vores tilfælde er det 0,844727566 eller 0,85. Det betyder, at vores regression er passende med 85%.
- Justeret R -firkant: Den justerede firkant er bare en mere vidnet version af R square. Hovedsagelig nyttig i multipel regressionsanalyse.
- Standard fejl: Mens R. Squire fortæller dig, hvor mange datapunkter der falder nær regressionslinjen, fortæller standardfejlen dig, hvor langt et datapunkt kan gå fra regressionslinjen.
I vores tilfælde er det 6.74.
- Observation: Dette er simpelthen antallet af observationer, hvilket er 11 i vores eksempel.
Anova sektion:
Dette afsnit bruges næppe i lineær regression.
- df. Det er en grad af frihed. Det bruges til at beregne regression manuelt.
- SS. Summen af firkanter. Det er bare en sum af kvadrater af variationer. Bruges til at finde R squire -værdier.
- FRK. Det betyder kvadratværdi.
- Og 5. F og Betydning af F. Hvis betydningen af F (p-værdi af hældningen) er mindre end F-testen, kan du kassere nulhypotesen og bevise din hypotese. I et enkelt sprog kan du konkludere, at der er en vis effekt af x på y, når det ændres.
I vores tilfælde er F 48,96264 og Betydningen af F er 0,000063. Det betyder, at vores regression passer til dataene.
Regressionsafdeling:
I dette afsnit har vi de to vigtigste værdier for vores regressionsligning.
- Aflytning: Vi har et aflytning her, der fortæller, hvor x-aflytter på Y. Dette er en vigtig del af regressionsligningen. Det er -1,11 i vores tilfælde.
- X -variabel 1 (Hældning). Også kaldet koefficienten for x. Det definerer tangenten af regressionslinjen.
REGRESSIONSKORT I EXCEL
I excel er det let at plotte et regressionsdiagram. Bare følg disse trin. Følg disse enkle trin for at tilføje regressionsdiagram i Excel 2016, 2013 og 2010.
Trin 1. Har dine kendte x’er i den første kolonne og kender y’erne i den anden.
I vores tilfælde ved vi, at Known_x er læskedrikke solgt. Og kendte_y’er er sælgere.
Trin 2. Vælg dine kendte x’er og y’er.
Trin 3: Gå til fanen Indsæt, og klik på spredningsdiagrammet.
Du får et diagram, der ser sådan ud.
Trin 4. Tilføj trendlinjen: Gå til layout, og find trendlinjemuligheden i analysesektionen.
Under indstillingen Trendline skal du klikke på Lineær Trendline.
Du får din graf til at se sådan ud.
Dette er din regressionsgraf.
Hvis du nu tilføjer dataene herunder og udvider de valgte data. Du vil se en ændring i din graf.
For vores eksempel tilføjede vi 2000 til den solgte sodavand og efterlod sælgerne tomme. Og når vi udvider grafens område, er det dette, vi får.
Det vil give det nødvendige antal sælgere til at foretage 2000 salg af læskedrikke i grafisk form. Hvilket er lidt under 120 i grafen. Og fra vores regressionsligning ved vi, at den er 116.
I denne artikel forsøgte jeg at dække alt under Excel Regressionsanalyse. Jeg forklarede regression i excel 2016. Regression i excel 2010 og excel 2013 er det samme som i excel 2016.
For yderligere forespørgsler om dette emne, brug kommentarfeltet. Stil et spørgsmål, giv en mening eller bare nævn mine grammatiske fejl. Alt er velkomment. Tøv bare ikke med at bruge kommentarfeltet.
Sådan beregnes MODE -funktion i Excel
Sådan beregnes gennemsnitlig funktion i Excel
Sådan oprettes en standardafvigelsesgraf
Beskrivende statistik i Microsoft Excel 2016
Sådan bruges Excel NORMDIST -funktion
Sådan bruges Pareto -diagrammet og -analysen
Populære artikler:
50 Excel -genvej til at øge din produktivitet
Sådan bruges VLOOKUP -funktionen i Excel
Sådan bruges COUNTIF -funktionen i Excel 2016
Sådan bruges SUMIF -funktionen i Excel