opplæringen forklarer grunnleggende om regresjonsanalyse og viser noen forskjellige måter å gjøre lineær regresjon i Excel.

Tenk deg dette: du er utstyrt med en hel masse forskjellige data og blir bedt om å forutsi neste års salgstall for din bedrift. Du har oppdaget dusinvis, kanskje til og med hundrevis, av faktorer som muligens kan pavirke tallene. Men hvordan vet du hvilke som er veldig viktige? Kjør regresjonsanalyse I Excel. Det vil gi deg et svar på dette og mange flere spørsmål: Hvilke faktorer er viktige og hvilke kan ignoreres? Hvor nært er disse faktorene knyttet til hverandre? Og hvor sikker kan du være på forutsetningene?

• Regresjonsanalyse i Excel
• Lineær regresjon i Excel Med Analyseverktøy
• Tegn en lineær regresjonsgraf
• Regresjonsanalyse i Excel med formler

Regresjonsanalyse I Excel – grunnleggende

i statistisk modellering brukes regresjonsanalyse til å estimere forholdet mellom to eller flere variabler:

Avhengig variabel (aka criterion variable) er hovedfaktoren du prøver å forstå og forutsi.

Uavhengige variabler (også forklaringsvariabler eller prediktorer) er faktorer som kan påvirke den avhengige variabelen.Regresjonsanalyse hjelper deg å forstå hvordan den avhengige variabelen endres når en av de uavhengige variablene varierer og gjør det mulig å matematisk avgjøre hvilken av disse variablene som virkelig har innvirkning.Teknisk sett er en regresjonsanalysemodell basert på summen av kvadrater, som er en matematisk måte å finne spredning av datapunkter. Målet med en modell er å få den minste mulige summen av kvadrater og tegne en linje som kommer nærmest dataene.

i statistikk skiller de mellom en enkel og flere lineær regresjon. Enkel lineær regresjon modellerer forholdet mellom en avhengig variabel og en uavhengig variabel ved hjelp av en lineær funksjon. Hvis du bruker to eller flere forklaringsvariabler for å forutsi den avhengige variabelen, håndterer du flere lineære regresjon. Hvis den avhengige variabelen er modellert som en ikke-lineær funksjon fordi datarelasjonene ikke følger en rett linje, kan du bruke ikke-lineær regresjon i stedet. Fokuset på denne opplæringen vil være på en enkel lineær regresjon.Som et eksempel, la oss ta salgstall for paraplyer de siste 24 månedene og finne ut gjennomsnittlig månedlig nedbør i samme periode. Plott denne informasjonen på et diagram, og regresjonslinjen vil demonstrere forholdet mellom den uavhengige variabelen (nedbør) og avhengig variabel (paraplysalg):

Lineær regresjonsligning

Matematisk er en lineær regresjon definert av denne ligningen:

hvor:

• x er en uavhengig variabel.
• y er en avhengig variabel.
• a er Y-avskjæringen, som er den forventede middelverdien av y når alle x-variablene er lik 0. På en regresjonsgraf er det punktet hvor linjen krysser y-aksen.
• b er hellingen til en regresjonslinje, som er endringshastigheten for y som x endringer.
• ε er den tilfeldige feiltermen, som er forskjellen mellom den faktiske verdien av en avhengig variabel og dens forventede verdi.

den lineære regresjonsligningen har alltid en feilbetegnelse fordi prediktorer i virkeligheten aldri er helt presise. Noen programmer, inkludert Excel, gjør imidlertid feilberegningen bak kulissene. Så I Excel gjør du lineær regresjon ved hjelp av minste kvadraters metode og søker koeffisientene a og b slik at:

for vårt eksempel tar den lineære regresjonsligningen følgende form:

Umbrellas sold = b * rainfall + a

Det finnes en håndfull forskjellige måter å finne a og B På. :

• Regresjonsverktøy som følger Med Analyseverktøy
• Punktdiagram med en trendlinje
• Lineær regresjonsformel

Nedenfor finner du detaljerte instruksjoner om bruk av hver metode.

slik gjør du lineær regresjon i Excel med Analyseverktøy

dette eksemplet viser hvordan du kjører regresjon i Excel ved hjelp av et spesielt verktøy som følger Med Analyseverktøy-tillegget.

Aktiver Tillegget Analysis ToolPak

Analysis ToolPak er tilgjengelig i Alle Versjoner Av Excel 2019 til 2003, men er ikke aktivert som standard. Så, du må slå den på manuelt. Slik gjør du det:

1. I Excel klikker Du Fil > Alternativer.
2. i Dialogboksen Alternativer For Excel velger Du Tillegg i venstre sidepanel, kontrollerer At Excel-Tillegg er merket I Behandle-boksen,og klikker Gå Til.
   
3. i Dialogboksen Add-ins krysser Du Av Analyseverktøy og klikker OK:
   

dette vil legge Til Dataanalyseverktøyene i data-fanen på Excel-båndet.

Kjør regresjonsanalyse

I dette eksemplet skal vi gjøre en enkel lineær regresjon i Excel. Det vi har er en liste over gjennomsnittlig månedlig nedbør de siste 24 månedene i kolonne B, som er vår uavhengige variabel (prediktor), og antall paraplyer solgt i kolonne C, som er den avhengige variabelen. Selvfølgelig er det mange andre faktorer som kan påvirke salget, men for nå fokuserer vi bare på disse to variablene:

Med Analyseverktøy lagt til aktivert, utfør disse trinnene for å utføre regresjonsanalyse i Excel:

1. På data-fanen, i Analysegruppen, klikker Du På Dataanalyse-knappen.
   
2. Velg Regresjon og klikk OK.
   
3. i Regresjonsdialogboksen konfigurerer du følgende innstillinger:
   • Velg Input Y-Området, som er din avhengige variabel. I vårt tilfelle er det paraply salg (C1: C25).
   • Velg Input X-Området, dvs. din uavhengige variabel. I dette eksemplet er det gjennomsnittlig månedlig nedbør (B1: B25).

   hvis du bygger en multippel regresjonsmodell, velger du to eller flere tilstøtende kolonner med forskjellige uavhengige variabler.

   • Merk Av I Etiketter-boksen hvis det er overskrifter øverst I x-og Y-områdene.
   • Velg ditt foretrukne Utgangsalternativ, et nytt regneark i vårt tilfelle.
   • merk Eventuelt Av For Residuals for å få forskjellen mellom de forventede og faktiske verdiene.
     
4. klikk OK Og observere regresjonsanalyseutgangen opprettet Av Excel.

Tolk regresjonsanalyseutgang

som du nettopp har sett, er det enkelt å kjøre regresjon i Excel fordi alle beregninger utføres automatisk. Tolkningen av resultatene er litt vanskeligere fordi du trenger å vite hva som ligger bak hvert nummer. Nedenfor finner du en oversikt over 4 store deler av regresjonsanalyseutgangen.

Regresjonsanalyseutgang: Sammendragsutgang

denne delen forteller deg hvor godt den beregnede lineære regresjonsligningen passer til kildedataene dine.

Her er hva hvert stykke informasjon betyr:

Multiple R. Det er Korrelasjonskoeffisienten som måler styrken av et lineært forhold mellom to variabler. Korrelasjonskoeffisienten kan være en verdi mellom -1 og 1, og dens absolutte verdi indikerer forholdet styrke. Jo større absoluttverdien er, desto sterkere er forholdet:

• 1 betyr et sterkt positivt forhold
• -1 betyr et sterkt negativt forhold
• 0 betyr ikke noe forhold i det hele tatt

R Kvadrat. Det Er Bestemmelseskoeffisienten, som brukes som en indikator på godheten til passform. Det viser hvor mange poeng som faller på regresjonslinjen. R2-verdien beregnes ut fra summen av kvadrater, nærmere bestemt er det summen av de kvadrerte avvikene fra de opprinnelige dataene fra gjennomsnittet.

I vårt eksempel Er R2 0,91 (avrundet til 2 sifre), som er fe bra. Det betyr at 91% av våre verdier passer til regresjonsanalysemodellen. Med andre ord forklares 91% av de avhengige variablene (y-verdier) av de uavhengige variablene (x-verdier). Vanligvis Er R-Kvadratet på 95% eller mer ansett som en god passform.

Justert R Kvadrat. Det er r-kvadratet justert for antall uavhengige variable i modellen. Du vil ønske å bruke denne verdien i stedet For r square for flere regresjonsanalyser.

Standardfeil. Det er et annet godt mål som viser presisjonen av regresjonsanalysen din-jo mindre tall, jo mer sikker kan du være om regresjonsligningen din. Mens R2 representerer prosentandelen av den avhengige variabelens varians som forklares av modellen, Er Standardfeil et absolutt mål som viser gjennomsnittlig avstand som datapunktene faller fra regresjonslinjen.

Observasjoner. Det er bare antall observasjoner i modellen din.

Regresjonsanalyse utgang: ANOVA

den andre delen av utgangen er Analyse Av Varians (ANOVA):

I Utgangspunktet deler den summen av kvadrater i individuelle komponenter som gir informasjon om nivåene av variabilitet innenfor regresjonsmodellen:

• df er antallet frihetsgrader knyttet til varianskildene.
• SS ER summen av kvadrater. Jo mindre GJENVÆRENDE SS sammenlignet Med Total SS, desto bedre passer modellen til dataene.
• MS ER middel kvadrat.
• F er f-statistikken, Eller F-testen for nullhypotesen. Det brukes til å teste den generelle betydningen av modellen.
• Betydning F Er p-verdien Av F.

ANOVA-delen brukes sjelden til en enkel lineær regresjonsanalyse I Excel, men du bør definitivt se nærmere på den siste komponenten. Betydningen F-verdien gir en ide om hvor pålitelig (statistisk signifikant) resultatene dine er. Hvis Signifikans F er mindre enn 0,05 (5%), er modellen OK. Hvis den er større enn 0,05, vil du sannsynligvis bedre velge en annen uavhengig variabel.

Regresjonsanalyseutgang: koeffisienter

denne delen gir spesifikk informasjon om komponentene i analysen din:

Den mest nyttige komponenten i denne delen er Koeffisienter. Det gjør det mulig å bygge en lineær regresjonsligning I Excel:

For vårt datasett, hvor y er antall paraplyer solgt og x er en gjennomsnittlig månedlig nedbør, går vår lineære regresjonsformel som følger:

Y = Rainfall Coefficient * x + Intercept

Utstyrt Med a-og b-verdier avrundet til tre desimaler, blir Det til:

Y=0.45*x-19.074

for eksempel, med gjennomsnittlig månedlig nedbør lik 82 mm, vil paraplysalget være omtrent 17,8:

0.45*82-19.074=17.8

på samme måte kan du finne ut hvor mange paraplyer som skal selges med annen månedlig nedbør (x variabel) du angir.

Regresjonsanalyse utgang: residuals

hvis du sammenligner estimert og faktisk antall solgte paraplyer som svarer til månedlig nedbør på 82 mm, vil du se at disse tallene er litt forskjellige:

• Estimert: 17.8 (beregnet over)
• Faktisk: 15 (rad 2 av kildedataene)

Hvorfor er forskjellen? Fordi uavhengige variabler aldri er perfekte prediktorer for de avhengige variablene. Og residualene kan hjelpe deg å forstå hvor langt unna de faktiske verdiene er fra de forutsagte verdiene:

hvordan lage en lineær regresjonsgraf i Excel

hvis du raskt må visualisere forholdet mellom de to variablene, tegner du et lineært regresjonsdiagram. Det er veldig enkelt! Slik gjør du det:

1. Velg de to kolonnene med dataene dine, inkludert overskrifter.
2. på Innsatsfanen, i Chatter-gruppen, klikker Du På Scatter chart-ikonet og velger Scatter thumbnail (den første):
   

   Dette vil sette inn et scatter-plott i regnearket ditt, som vil ligne dette:
   

3. nå må Vi tegne de minste firkantene regresjonslinje. For å få det gjort, høyreklikk på et punkt og velg Legg Til Trendlinje… fra kontekstmenyen.
   
4. i den høyre ruten velger Du Den Lineære trendlinjeformen og, eventuelt, sjekk Visningsligningen På Diagrammet for å få regresjonsformelen:
   

   som du kanskje legger merke til, er regresjonsligningen Excel har opprettet for oss det samme som den lineære regresjonsformelen vi bygget basert på Koeffisientene utgang.

5. Bytt til Fyll& Linje-fanen og tilpass linjen til din smak. For eksempel kan du velge en annen linjefarge og bruke en heltrukket linje i stedet for en stiplet linje (velg Heltrukket linje i Dash-boksen):
   

på dette punktet ser diagrammet ditt allerede ut som en anstendig regresjonsgraf:

Likevel vil du kanskje gjøre noen flere forbedringer:

• dra ligningen dit du ønsker.
• Legg til aksetitler (Diagramelementer knapp> Aksetitler).
• hvis datapunktene dine starter midt på den horisontale og / eller vertikale aksen som i dette eksemplet, vil du kanskje bli kvitt den overdrevne hvite plassen. Følgende tips forklarer hvordan du gjør dette: Skalere diagramaksene for å redusere mellomrom.

  Og slik ser vår forbedrede regresjonsgraf ut:
  

  Viktig merknad! I regresjonsgrafen skal den uavhengige variabelen alltid være På X-aksen og den avhengige variabelen På Y-aksen. Hvis grafen tegnes i omvendt rekkefølge, bytter du kolonnene i regnearket, og deretter tegner du diagrammet på nytt. Hvis du ikke har lov til å omorganisere kildedataene, kan Du bytte X-og Y-aksene direkte i et diagram.

slik gjør du regresjon i Excel ved hjelp av formler

Microsoft Excel har noen statistiske funksjoner som kan hjelpe deg med å gjøre lineær regresjonsanalyse som RETTLINJE, HELLING, INTERCPET og CORREL.

FUNKSJONEN RETTLINJE bruker minste kvadraters regresjonsmetode til å beregne en rett linje som best forklarer forholdet mellom variablene og returnerer en matrise som beskriver den linjen. Du kan finne detaljert forklaring av funksjonens syntaks i denne opplæringen. For nå, la oss bare lage en formel for vårt utvalgsdatasett:

=LINEST(C2:C25, B2:B25)

FORDI RETTLINJE-funksjonen returnerer en rekke verdier, må du skrive den inn som en matriseformel. Merke to tilstøtende celler I samme rad, E2:F2 i vårt tilfelle skriver du inn formelen, og trykker Ctrl + Shift + Enter for å fullføre den.

formelen returnerer b-koeffisienten (E1) og a-konstanten (F1) for den allerede kjente lineære regresjonsligningen:

y = bx + a

hvis du unngår å bruke matriseformler i regnearkene dine, kan du beregne a og b individuelt med vanlige formler:

hent y-Avskjæringen (a):

=INTERCEPT(C2:C25, B2:B25)

hent skråningen (b):

=SLOPE(C2:C25, B2:B25)

I Tillegg kan du finne korrelasjonskoeffisienten (Multiple R i regresjonsanalysens oppsummeringsutgang) som angir hvor sterkt de to variablene er relatert til hverandre:

=CORREL(B2:B25,C2:C25)

følgende skjermbilde viser alle Disse Excel-regresjonsformlene i aksjon:

slik gjør du lineær regresjon i Excel. Når Det er sagt, vær oppmerksom På At Microsoft Excel ikke er et statistisk program. Hvis du trenger å utføre regresjonsanalyse på profesjonelt nivå, kan det være lurt å bruke målrettet programvare SOM XLSTAT, RegressIt, etc.

tilgjengelige nedlastinger:

for å se nærmere på våre lineære regresjonsformler og andre teknikker som diskuteres i denne opplæringen, er du velkommen til å laste ned vår prøveregresjonsanalyse i Excel-arbeidsbok.

• hvordan bruke Solver I Excel med eksempler
• hvordan beregne sammensatt interesse i Excel
• hvordan beregne CAGR (sammensatt årlig vekstrate) I Excel