Linjära regressioner modellerar ett samband mellan beroende och oberoende statistiska datavariabler. I enklare termer lyfter de fram en trend mellan två tabellkolumner i ett kalkylblad. Om du till exempel ställer in en Excel-tabell med en månad x-kolumn och registrerade en uppsättning data för var och en av månaderna i den intilliggande y-kolumnen, kommer linjär regression att framhäva trenden mellan x- och y-variablerna genom att lägga till trendlinjer i tabellen grafer. Så här kan du lägga till linjär regression till Excel-grafer.
Lägga till en linjär regressionstrendlinje till grafen
- Öppna först ett tomt Excel-kalkylblad, välj cell D3 och ange "Månad" som kolumnrubrik, vilket kommer att vara x-variabeln.
- Klicka sedan på cell E3 och skriv in "Y-värde" som kolumnrubriken för y-variabeln. Detta är i princip en tabell med en registrerad serie datavärden för månaderna jan-maj.
- Ange månaderna i cellerna D4 till D8 och datavärden för dem i cellerna E4 till E8 som visas i ögonblicksbilden direkt nedan.
Nu kan du skapa en spridningsgraf för den tabellen.
- Markera alla celler i tabellen med markören.
- Klicka på fliken Infoga och välj Sprida ut > Sprid med endast markörer för att lägga till grafen i kalkylarket enligt nedan. Alternativt kan du trycka på snabbtangenten Alt + F1 för att infoga ett stapeldiagram.
- Sedan ska du högerklicka på diagrammet och välja Ändra diagramtyp >X Y (spridning) >Sprid med endast markörer.
Därefter kan du lägga till trendlinjen i spridningsdiagrammet
- Välj en av datapunkterna på spridningsdiagrammet och högerklicka för att öppna snabbmenyn, som innehåller en Lägg till Trendline alternativ.
- Välj Lägg till Trendline för att öppna fönstret som visas i ögonblicksbilden direkt nedan. Det fönstret har fem flikar som inkluderar olika formateringsalternativ för linjära regressionstrendlinjer.
3. Klicka Trendlinjealternativ och välj en regressionstyp därifrån. Du kan välja Exponentiell, Linjär, Logaritmisk, Glidande medelvärde, Kraft och Polynom alternativ för regressionstyp därifrån.
4. Välj Linjär och klicka Stänga för att lägga till den trendlinjen i grafen som visas direkt nedan.
Linerregressionstrendlinjen i grafen ovan framhäver att det finns ett allmänt uppåtgående förhållande mellan x- och y-variablerna trots några droppar på diagrammet. Observera att den linjära regressionstrendlinjen inte överlappar någon av datapunkterna i diagrammet, så det är inte samma sak som ditt genomsnittliga linjediagram som förbinder varje punkt.
Formatera trendlinjen för linjär regression
Formatering av den linjära regressionstrendlinjen är ett viktigt verktyg för att skapa läsbara, tydliga grafer i excel.
- För att börja formatera trendlinjen bör du högerklicka på den och välja Formatera trendlinje.
- Det öppnar Format Trendline-fönstret igen från vilket du kan klicka Linjefärg.
- Välj Solid linje och klicka på Färg ruta för att öppna en palett där du kan välja en alternativ färg för trendlinjen.
- För att anpassa linjestilen, klicka på fliken Linjestil. Sedan kan du justera pilens bredd och konfigurera pilinställningarna.
- tryck på Pilinställningar knappar för att lägga till pilar på linjen.
Du kan också lägga till effekter till din trendlinje för estetiska ändamål
- Lägg till en glödeffekt till trendlinjen genom att klicka Glöd och Mjuka kanter. Det öppnar fliken nedan där du kan lägga till glöd genom att klicka på Förinställningar knapp.
- Välj sedan en glödvariant för att välja en effekt. Klick Färg för att välja alternativa färger för effekten, och du kan dra Storlek och Genomskinlighet staplar för att ytterligare konfigurera trendlinjens glöd.
Prognosvärden med linjär regression
När du har formaterat trendlinjen kan du också förutsäga framtida värden med den. Låt oss till exempel anta att du behöver prognostisera ett datavärde tre månader efter maj för augusti, vilket inte finns med i vår tabell.
- klicka på Trendlinjealternativ och ange "3" i textrutan Framåt.
- Den linjära regressionstrendlinjen belyser att Augusts värde förmodligen kommer att vara strax över 3 500 som visas nedan.
Varje linjär regressionstrendlinje har sin egen ekvation och r-kvadratvärde som du kan lägga till i diagrammet.
- Klicka på Visa ekvation på diagrammet kryssrutan för att lägga till ekvationen till grafen. Den ekvationen inkluderar ett lutnings- och skärningsvärde.
- För att lägga till r-kvadratvärdet till grafen, klicka på Visa R-kvadratvärde på diagrammet kryssruta. Det lägger till r i kvadrat till grafen precis under ekvationen som i ögonblicksbilden nedan.
- Dra ekvationen och korrelationsrutan för att ändra dess position på spridningsdiagrammet.
De linjära regressionsfunktionerna
Excel innehåller också linjära regressionsfunktioner som du kan hitta lutnings-, skärnings- och r-kvadratvärden med för y- och x-datamatriser.
- Välj en kalkylbladscell att lägga till en av dessa funktioner till och tryck sedan på Infoga funktion knapp. De linjära regressionsfunktionerna är statistiska, så välj Statistisk från rullgardinsmenyn för kategori.
- Sedan kan du välja RSQ, BACKE eller GENSKJUTA för att öppna deras funktionsfönster enligt nedan.
Fönstren RSQ, SLOPE och INTERCEPT är ganska lika. De inkluderar Known_y's och Known_x's rutor som du kan välja att lägga till y- och x-variabelvärdena till från din tabell. Observera att cellerna endast måste innehålla siffror, så ersätt månader i tabellen med motsvarande siffror som 1 för jan, 2 för feb, etc. Klicka på OK för att stänga fönstret och lägga till funktionen i kalkylarket.
Så nu kan du piffa upp dina Excel-kalkylbladsdiagram med linjära regressionstrendlinjer. De kommer att belysa de allmänna trenderna för grafernas datapunkter, och med regressionsekvationerna är de också praktiska prognosverktyg.
Har du några tips, tricks eller frågor relaterade till linjär regression i Excel? Låt oss veta i kommentarsfältet nedan.