Lineære regresjoner modellerer en sammenheng mellom avhengige og uavhengige statistiske datavariabler. I enklere termer fremhever de en trend mellom to tabellkolonner i et regneark. For eksempel, hvis du setter opp en Excel-regnearktabell med en måned x-kolonne og registrerte et sett med data for hver av månedene i den tilstøtende y-kolonnen, vil lineær regresjon fremheve trenden mellom x- og y-variablene ved å legge til trendlinjer i tabellen grafer. Slik kan du legge til lineær regresjon til Excel-grafer.
Legge til en lineær regresjonstrendlinje til grafen
- Først åpner du et tomt Excel-regneark, velg celle D3 og skriv inn 'Måned' som kolonneoverskrift, som vil være x-variabelen.
- Klikk deretter celle E3 og skriv inn "Y-verdi" som kolonneoverskriften for y-variabelen. Dette er i utgangspunktet en tabell med en registrert serie med dataverdier for månedene jan-mai.
- Skriv inn månedene i cellene D4 til D8 og dataverdier for dem i cellene E4 til E8 som vist i øyeblikksbildet rett nedenfor.
Nå kan du sette opp en spredningsgraf for den tabellen.
- Velg alle cellene i tabellen med markøren.
- Klikk på Sett inn-fanen og velg Spre > Spred med kun markører for å legge til grafen i regnearket som nedenfor. Alternativt kan du trykke hurtigtasten Alt + F1 for å sette inn et søylediagram.
- Deretter bør du høyreklikke på diagrammet og velge Endre diagramtype >X Y (spredning) >Spred med kun markører.
Deretter kan du legge til trendlinjen til spredningsplottet
- Velg et av datapunktene på spredningsplottet og høyreklikk for å åpne kontekstmenyen, som inkluderer en Legg til Trendline alternativ.
- Plukke ut Legg til Trendline for å åpne vinduet vist i øyeblikksbildet rett under. Dette vinduet har fem faner som inkluderer forskjellige formateringsalternativer for lineære regresjonstrendlinjer.
3. Klikk Trendlinjealternativer og velg en regresjonstype derfra. Du kan velge Eksponentiell, Lineær, Logaritmisk, Glidende gjennomsnitt, Makt og Polynom alternativer for regresjonstype derfra.
4. Velg Lineær og klikk Lukk for å legge til den trendlinjen i grafen som vist rett nedenfor.
Linerregresjonstrendlinjen i grafen ovenfor fremhever at det er et generelt oppadgående forhold mellom x- og y-variablene til tross for noen få dråper på diagrammet. Merk at den lineære regresjonstrendlinjen ikke overlapper noen av datapunktene på diagrammet, så det er ikke det samme som den gjennomsnittlige linjegrafen som forbinder hvert punkt.
Formatering av trendlinjen for lineær regresjon
Formatering av trendlinjen for lineær regresjon er et viktig verktøy for å lage leselige, klare grafer i excel.
- For å begynne å formatere trendlinjen, bør du høyreklikke på den og velge Formater trendlinje.
- Det vil åpne Format Trendline-vinduet igjen hvorfra du kan klikke Linjefarge.
- Plukke ut Solid linje og klikk på Farge boksen for å åpne en palett der du kan velge en alternativ farge for trendlinjen.
- For å tilpasse linjestilen, klikk på Linjestil-fanen. Deretter kan du justere pilbredden og konfigurere pilinnstillingene.
- trykk Pilinnstillinger knapper for å legge til piler på linjen.
Du kan også legge til effekter i trendlinjen din for estetiske formål
- Legg til en glødeffekt til trendlinjen ved å klikke Gløde og Myke kanter. Det åpner fanen nedenfor der du kan legge til glød ved å klikke på Forhåndsinnstillinger knapp.
- Velg deretter en glødvariant for å velge en effekt. Klikk Farge for å velge alternative farger for effekten, og du kan dra Størrelse og Åpenhet søyler for å konfigurere trendlinjegløden ytterligere.
Prognoseverdier med lineær regresjon
Når du har formatert trendlinjen, kan du også forutsi fremtidige verdier med den. La oss for eksempel anta at du må anslå en dataverdi tre måneder etter mai for august, som ikke er inkludert i tabellen vår.
- klikk på Trendlinjealternativer og skriv inn "3" i Forward-tekstboksen.
- Den lineære regresjonstrendlinjen fremhever at Augusts verdi sannsynligvis vil være like over 3500 som vist nedenfor.
Hver lineær regresjonstrendlinje har sin egen ligning og r-kvadratverdi som du kan legge til i diagrammet.
- Klikk på Vis ligning på diagrammet avmerkingsboksen for å legge til ligningen til grafen. Den ligningen inkluderer en helnings- og avskjæringsverdi.
- For å legge til r-kvadratverdien til grafen, klikk på Vis R-kvadratverdi på diagrammet avkrysningsboksen. Det legger til r i kvadrat til grafen rett under ligningen som i øyeblikksbildet nedenfor.
- Dra liknings- og korrelasjonsboksen for å endre posisjonen på spredningsplottet.
De lineære regresjonsfunksjonene
Excel inkluderer også lineære regresjonsfunksjoner som du kan finne helnings-, avskjærings- og r-kvadratverdiene med for y- og x-datamatriser.
- Velg en regnearkcelle for å legge til en av disse funksjonene, og trykk deretter på Sett inn funksjon knapp. De lineære regresjonsfunksjonene er statistiske, så velg Statistisk fra kategori-rullegardinmenyen.
- Deretter kan du velge RSQ, SKRÅNINGEN eller AVSKJÆRE for å åpne funksjonsvinduene som nedenfor.
Vinduene RSQ, SLOPE og INTERCEPT er ganske like. De inkluderer Known_y's og Known_x's bokser du kan velge å legge til y- og x-variabelverdiene til fra tabellen din. Vær oppmerksom på at cellene kun må inneholde tall, så bytt ut måneder i tabellen med tilsvarende tall som 1 for jan, 2 for feb, osv. Klikk OK for å lukke vinduet og legge til funksjonen i regnearket.
Så nå kan du piffe opp Excel-regnearkgrafene dine med lineære regresjonstrendlinjer. De vil fremheve de generelle trendene for grafers datapunkter, og med regresjonsligningene er de også nyttige prognoseverktøy.
Har du noen tips, triks eller spørsmål knyttet til trendlinjer for lineær regresjon i Excel? Gi oss beskjed i kommentarfeltet nedenfor.