Hvordan gjøre regresjonsanalyse i Excel

Innholdsfortegnelse:

Anonim

Regresjon er et analyseverktøy som vi bruker til å analysere store datamengder og lage prognoser og spådommer i Microsoft Excel.

Vil du forutsi fremtiden? Nei, vi skal ikke lære astrologi. Vi er inne i tall, og vi vil lære regresjonsanalyse i Excel i dag.

For å forutsi fremtidige estimater, vil vi studere:

  • REGRESSJONSANALYSE VED Å BRUKE EXCEL -FUNKSJONER (MANUELL REGRESSJONSFINDING)
  • REGRESJONSANALYSE VED Å BRUKE EXCEL’S ANALYSE TOOLPAK ADD-IN
  • REGRESJONSKART I EXCEL

La oss gjøre det…

Scenario:

La oss anta at du selger brus. Hvor kult blir det hvis du kan forutsi:

  • Hvor mange brus vil bli solgt neste år basert på data fra forrige år?
  • Hvilke felt må fokuseres?
  • Og hvordan kan du øke salget ved å endre strategien?

Det blir lønnsomt fantastisk. Ikke sant?… Jeg vet. Så la oss komme i gang.

Du har 11 poster over selgere og brus som er solgt.

Basert på disse dataene vil du forutsi antall selgere som kreves for å oppnå 2000 salg av brus.

Regresjonsligningen er et verktøy for å gjøre så tette estimater. For å gjøre det må vi kjenne regresjon først.

REGRESSJONSANALYSE VED Å BRUKE EXCEL -FUNKSJONER (MANUELL REGRESSJONSFINDING)

Denne delen vil få deg til å forstå regresjon bedre enn bare å fortelle excel regresjonsprosedyre.

Introduksjon:

Enkel lineær regresjon:

Studiet av forholdet mellom to variabler kalles enkel lineær regresjon. Hvor den ene variabelen er avhengig av den andre uavhengige variabelen. Den avhengige variabelen kalles ofte med navn som variabel Driven, Response og Target. Og den uavhengige variabelen uttales ofte som en Driving, Predictor eller ganske enkelt uavhengig variabel. Disse navnene beskriver dem tydelig.

La oss nå sammenligne dette med scenariet ditt. Du vil vite antall selgere som kreves for å oppnå 2000 salg. Så her er den avhengige variabelen antall selgere, og den uavhengige variabelen selges brus.

Den uavhengige variabelen er stort sett betegnet som x og avhengig variabel som y.

I vårt tilfelle selges brus x og antallet selgere er y.

Hvis vi vil vite hvor mange brus som blir solgt hvis vi avtaler 200 selgere, da vil scenariet være omvendt.

Går videre.

Den "enkle" matematikken for lineær regresjonsligning:

Vel, det er ikke enkelt. Men Excel gjorde det enkelt å gjøre.

Vi må forutsi det nødvendige antallet selgere for alle 11 tilfellene for å få den 12. nærmeste spådommen.

La oss si:

Brus solgt er x

Nummeret av selgere er y

Det spådde y (antall selgere) også ringt Regresjonsligning, ville vært

x*Skråning+avskjæring (slapp av, jeg har dekket det)

Nå lurer du sikkert på hvor stat vil du få skråningen og avskjære. Ikke bekymre deg, excel har funksjoner for dem. Du trenger ikke å lære å finne bakken og fange den opp manuelt.

Hvis du vil, vil jeg utarbeide en egen opplæring for det. Gi meg beskjed i kommentarfeltet. Dette er noen viktige dataanalyseverktøy.

La oss gå inn i beregningen vår:

Trinn 1: Forbered dette lille bordet

Steg 2: Finn skråningen på regresjonslinjen

Excel Funksjon for bakker er

= SLOPE (kjente_y’er, kjente_x’er)

Dine kjente_y er innenfor rekkevidde B2: B12 og kjente_x er innenfor rekkevidde C2: C12

I cellen B16, skriv formelen nedenfor

= SLOPE (B2: B12, C2: C12)

(Merk: Helling kalles også koeffisient for x i regresjonsligningen)

Du vil få 0.058409. Rund opp til 2 desimaler, så får du 0.06.

Trinn 3: Finn skjæringslinjen for regresjon

Excel -funksjon for skjæringspunktet er

=INTERCEPT (kjente_y’er, kjente_x’er)

Vi vet hva vår kjente x’er og y’er

I cellen B17, skriv ned denne formelen

= AVSNITT (B2: B12, C2: C12)

Du vil få en verdi på -1.1118969. Avrund til to desimaler. Du vil få -1.11.

Vår lineære regresjonsligning er = x*0,06 + (-1,11). Nå kan vi lett forutsi mulig y avhengig av målet x.

Trinn 4: Skriv formelen nedenfor i D2

=C2*$ B $ 16+$ B $ 17(Regresjonsligning)

Du får en verdi på 13.55.

Velg D2 til D13 og trykk CTRL+D for å fylle ut formelen i området D2: D13

I cellen D13 du har det nødvendige antallet selgere.

Derfor for å nå målet om 2000 Brus salg, du trenger et estimat på 115,71 selgere eller si 116 siden det er ulovlig å kutte mennesker i biter.

Når du bruker dette, kan du enkelt utføre What-If-analyse i excel. Bare endre antall salg, og det vil vise deg mange selgere om det skal til for å nå dette salgsmålet.

Spill rundt det for å finne ut:

Hvor mye arbeidskraft trenger du for å øke salget?

Hvor mange salg vil øke hvis du øker selgerne?

Gjør estimatet ditt mer pålitelig:

Nå vet du at du trenger 116 selgere for å få 2000 salg.

I analytics er ingenting bare sagt og trodd. Du må gi en prosentandel av pålitelighet på estimatet ditt. Det er som å gi et sertifikat for ligningen din.

Korrelasjonskoeffisientformel:

Det neste du vil bli spurt er hvor mye disse to variablene er relatert. I statiske termer må du fortelle korrelasjonskoeffisienten.

Excel -funksjon for korrelasjon er

= CORREL (array1, array2)

I ditt tilfelle er kjente_x’er og Know_y’er array1 og array2 uansett.

Skriv inn denne formelen i B18

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

Du vil ha 0.919090. Formater celle B2 til prosentandelen. Nå har 92% av korrelasjon.

Nå, hva dette 92% midler. Det betyr, der 92% sjansene for salg øker hvis du øker antall selgere og 92% av salget reduseres hvis du reduserer antall selgere. Det kalles Positiv korrelasjonskoeffisient.

R Squire (R^2):

R Squire -verdi forteller deg, med hvor stor prosentandel regresjonsligningen din ikke er en tilfeldighet. Hvor mye det er nøyaktig av dataene som er gitt.

Excel -funksjonen for R squire er RSQ.

RSQ (kjente_y’er, kjente_x’er)

I vårt tilfelle vil vi få R squire -verdi i celle B19.

Skriv inn denne formelen i B19

= RSQ (B2: B12, C2: C12)

Så vi har 84% av r Kvadratverdi. Noe som er en veldig god forklaring på vår regresjon. Det står at 84% av dataene våre bare ikke er tilfeldig. Y (antall selgere) er veldig avhengig av X (salg av brus).

Det er mange andre tester vi kan gjøre på disse dataene for å sikre vår regresjon. Men manuelt vil det være en kompleks og lang prosedyre. Det er derfor excel tilbyr Analysis Toolpak. Ved å bruke dette verktøyet kan vi gjøre denne regresjonsanalysen på sekunder.

REGRESJON I EXCEL BRUK AV EXCEL’S ANALYSE TOOLPAK ADD-IN

Hvis du allerede vet hva regresjonsligninger er, og du bare vil ha resultatene dine raskt, er denne delen noe for deg. Men hvis du enkelt vil forstå regresjonsligninger, rull deretter opp til REGRESSJONSANALYSE VED Å BRUKE EXCEL FUNKSJONER (MANUELL REGRESSJONSFINDING).

Excel tilbyr en hel haug med verktøy for analyse i Analysis Toolpak. Som standard er den ikke tilgjengelig i fanen Data. Du må legge den til. Så la oss legge det til først.

Legger til Analysis Toolpak til Excel 2016

Hvis du ikke vet hvor er dataanalyse i excel, følg disse trinnene

Trinn 1: Gå til Excel -alternativer: Fil? Alternativer? Tillegg

Trinn 2: Klikk på tillegg. Du vil se en liste over tilgjengelige tillegg.

Velg Analysis ToolPak, og finn administrer nederst i vinduet. Velg Administrer Excel-tillegg og klikk på GO.

Tilleggsvinduet åpnes. Velg Analysis ToolPak her. Klikk deretter på ok -knappen.

Nå kan du få tilgang til alle funksjonene til dataanalyse ToolPak fra Data Tab.

Bruke Analyse ToolPak for regresjon

Trinn 1: Gå til fanen Data, Finn dataanalyse. Klikk deretter på den.

En dialogboks vil dukke opp.

Trinn 2: Finn "Regresjon" i listen Analyseverktøy og trykk OK -knappen.

Regresjonen inndatavinduet vil dukke opp. Du vil se en rekke tilgjengelige inndatamuligheter. Men foreløpig vil vi bare konsentrere oss om Y Range og X Range, og la alt annet stå til standard.

Trinn 4: Oppgi innganger:

Antall selgere er Y

Salg av brus er X

Derfor

  • Y -område = B2: B11

Og

  • X Område = C2: C11

For utgangsområdet har jeg valgt E4 på samme ark. Du kan velge et nytt regneark for å få resultater på et nytt regneark i den samme arbeidsboken eller en komplett ny arbeidsbok. Når du er ferdig med innspillingen, trykker du på OK -knappen.

Resultater:

Du vil få servert en rekke opplysninger fra dataene dine. Ikke bli overveldet. Du trenger ikke å spise alle rettene.

Vi vil bare håndtere de resultatene som vil hjelpe oss å estimere det nødvendige antallet selgere

Trinn 5: Vi kjenner regresjonsligningen for estimering av y, det er

x*Skråning+avskjæring

Vi trenger bare å finne Skråningen og Avskjære i resultater.

Og her er de.

Avskjæringskoeffisienten er tydelig nevnt.

Skråningen er skrevet som 'X Variabel 1’, Noen ganger også nevnt som koeffisienten til X. Avrund dem, så får vi -1.11 som avskjæring og 0,06 som skråning.

Trinn 6: Fra resultater kan vi drive regresjonsligningen. Og det ville være

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

Forbered dette bordet i excel.

For nå, x er 2000, som er i celle E2.

Skriv inn denne formelen i celle F2

= E2*F21+F20

Du vil få et resultat av 115.7052757.

Å avrunde det vil gi oss 116 av obligatoriske selgere.

Så vi har lært hvordan man danner regresjonsligningen manuelt og bruker Analysis ToolPak. Hvordan kan du bruke denne ligningen til å estimere fremtidig statistikk?

La oss nå forstå regresjonsutgangen gitt av Analysis Toolpak.

Forstå regresjonsutgangen:

Det er ingen fordel hvis du gjør regresjonsanalyse ved hjelp av analyseverktøypakke i excel og ikke kan tolke betydningen.

Sammendragsseksjon:

Som navnet antyder, er det en oppsummering av dataene.

    1. Multiple R: Det forteller hvor tilpasset regresjonsligningen er til dataene. Det kalles også korrelasjonskoeffisienten.

I vårt tilfelle er det det 0.919090619 eller 0.92 (roundup). Dette betyr at det er en 92% sjanse for økning i salget hvis vi øker antallet selgere.

    1. R Square: Det forteller påliteligheten av funnet regresjon. Den forteller oss hvor mange observasjoner som er en del av regresjonslinjen vår. I vårt tilfelle er det 0,844727566 eller 0,85. Det betyr at regresjonen vår passer med 85%.
    2. Justert R -firkant: Den justerte ruten er bare en mer vitnet versjon av R square. Hovedsakelig nyttig i flere regresjonsanalyser.
    3. Standard feil: Mens R. Squire forteller deg hvor mange datapunkter som faller nær regresjonslinjen, forteller standardfeilen deg hvor langt et datapunkt kan gå fra regresjonslinjen.

I vårt tilfelle er det det 6.74.

  1. Observasjon: Dette er ganske enkelt antallet observasjoner, som er 11 i vårt eksempel.

Anova seksjon:

Denne delen brukes knapt i lineær regresjon.

  1. df. Det er en grad av frihet. Den brukes når man regner regresjon manuelt.
  2. SS. Summen av ruter. Det er bare en sum av kvadrater med avvik. Brukes til å finne R squire -verdier.
  3. MS. Dette betyr kvadratisk verdi.
  4. Og 5. F og Betydning av F. Hvis betydningen av F (p-verdien av skråningen) er mindre enn F-testen, kan du forkaste nullhypotesen og bevise din hypotese. På et enkelt språk kan du konkludere med at det er noen effekt av x på y når det endres.

I vårt tilfelle er F 48,96264 og betydningen av F er 0,000063. Det betyr at regresjonen vår passer til dataene.

Regresjonsseksjon:

I denne delen har vi de to viktigste verdiene for regresjonsligningen vår.

  1. Avskjæring: Vi har et skjæringspunkt her som forteller hvor x-avskjæringer på Y. Dette er en viktig del av regresjonsligningen. Det er -1,11 i vårt tilfelle.
  2. X -variabel 1 (Skråningen). Også kalt koeffisienten til x. Den definerer tangenten til regresjonslinjen.

REGRESJONSKART I EXCEL

I excel er det lett å plotte et regresjonsdiagram. Bare følg disse trinnene. Følg disse enkle trinnene for å legge til regresjonsdiagram i Excel 2016, 2013 og 2010.

Trinn 1. Ha dine kjente x -er i den første kolonnen og vet y -er i den andre.

I vårt tilfelle vet vi at Known_x er brus solgt. Og kjente_y’er er selgere.

Steg 2. Velg ditt kjente x og y -område.

Trinn 3: Gå til kategorien Sett inn og klikk på spredningsdiagrammet.

Du vil ha et diagram som ser slik ut.

Trinn 4. Legg til trendlinjen: Gå til layout og finn alternativet trendlinje i analysedelen.

Under alternativet Trendline klikker du på Lineær trendlinje.

Du vil få grafen til å se slik ut.

Dette er regresjonsgrafen din.

Hvis du legger til dataene nedenfor og utvider de valgte dataene. Du vil se en endring i grafen.

For vårt eksempel la vi til 2000 i bruset som ble solgt og la selgerne stå tomme. Og når vi utvider grafens område, er dette det vi vil ha.

Det vil gi det nødvendige antallet selgere for å gjøre 2000 salg av brus i grafisk form. Som er litt under 120 i grafen. Og fra regresjonsligningen vår, vet vi at den er 116.

I denne artikkelen prøvde jeg å dekke alt under Excel -regresjonsanalyse. Jeg forklarte regresjon i excel 2016. Regresjon i excel 2010 og excel 2013 er det samme som i excel 2016.

For ytterligere spørsmål om dette emnet, bruk kommentarseksjonen. Still et spørsmål, gi en mening eller bare nevne mine grammatiske feil. Alt er velkommen. Ikke nøl med å bruke kommentarfeltet.

Hvordan beregne MODE -funksjon i Excel

Hvordan beregne gjennomsnittlig funksjon i Excel

Hvordan lage en standardavviksgraf

Beskrivende statistikk i Microsoft Excel 2016

Slik bruker du Excel NORMDIST -funksjon

Hvordan bruke Pareto -diagrammet og analysen

Populære artikler:

50 Excel -snarvei for å øke produktiviteten

Slik bruker du VLOOKUP -funksjonen i Excel

Slik bruker du COUNTIF -funksjonen i Excel 2016

Slik bruker du SUMIF -funksjonen i Excel