Lineaire regressie modelleert een relatie tussen afhankelijke y en onafhankelijke x statistische gegevensvariabelen. Met andere woorden, ze markeren een trend tussen twee tabelkolommen in een spreadsheet. Als u bijvoorbeeld een Excel-werkbladtabel met een maand x-kolom instelt en een gegevensset voor elk van de maanden in de aangrenzende kolom y registreert, markeert lineaire regressie de trend tussen de x- en y-variabelen door trendlijnen toe te voegen aan tabelgrafieken. . Op deze manier kunt u lineaire regressie toevoegen aan Excel-grafieken.
Lineaire regressie-trendlijn toevoegen aan grafiek
Open eerst een leeg Excel-werkblad, selecteer cel D3 en voer 'Maand' in als kolomkop, die de x-variabele is. Klik vervolgens op cel E3 en voer 'Y-waarde' in als de y-variabele kolomkop. Dit is in feite een tabel met een opgenomen reeks gegevenswaarden voor de maanden januari-mei. Dus vul de maanden in cellen D4 tot D8 en gegevenswaarden voor hen in cellen E4 tot E8 in zoals getoond in de momentopname direct hieronder.
Nu kunt u een spreidingsgrafiek voor die tabel instellen. Selecteer alle cellen in de tabel met de cursor. Klik op het tabblad Invoegen en selecteer Scatter > Scatter met alleen markeringen om de grafiek aan de spreadsheet toe te voegen, zoals hieronder. U kunt ook op de Alt + F1-sneltoets drukken om een staafdiagram in te voegen. Klik vervolgens met de rechtermuisknop op het diagram en selecteer Diagramtype wijzigen > XY (spreiding) > Scatter met alleen markeringen .
Selecteer vervolgens een van de gegevenspunten in de spreidingsplot en klik met de rechtermuisknop om het contextmenu te openen, dat een optie Trendlijn toevoegen bevat. Selecteer Trendlijn toevoegen om het venster te openen dat direct in de snapshot wordt getoond. Dat venster heeft vijf tabbladen met verschillende opmaakopties voor lineaire regressie-trendlijnen.
Klik eerst op Trendlijnopties en selecteer daar een regressietype. U kunt daar opties Exponent, Lineair, Logaritmisch, Bewegingsgemiddeld, Vermogen en Polynoom regressietype selecteren. Selecteer Lineair en klik op Sluiten om die trendlijn aan de grafiek toe te voegen, zoals hieronder weergegeven.
De lijnlijnregressie-trendlijn in de bovenstaande grafiek benadrukt dat er een algemene opwaartse relatie is tussen de x- en y-variabelen, ondanks enkele druppels op de grafiek. Merk op dat de lineaire regressie-trendlijn geen van de gegevenspunten in de grafiek overlapt, dus het is niet hetzelfde als uw gemiddelde lijngrafiek die elk punt verbindt.
De trendlijn voor lineaire regressie opmaken
Als u de trendlijn wilt opmaken, klikt u er met de rechtermuisknop op en selecteert u Trendlijn opmaken. Dat opent het venster Formatline-trend opnieuw, van waaruit u op Lijnkleur kunt klikken. Selecteer Effen lijn en klik op het vak Kleur om een palet te openen waaruit u een alternatieve kleur voor de trendlijn kunt kiezen.
Als u de lijnstijl wilt aanpassen, klikt u op het tabblad Lijnstijl. Vervolgens kunt u de breedte van de pijl aanpassen en de pijlinstellingen configureren. Druk op de pijl-instelknoppen om pijlen aan de lijn toe te voegen.
Voeg een gloedeffect toe aan de trendlijn door op Glow and Soft Edges te klikken. Dat opent het tabblad hieronder van waaruit u gloed kunt toevoegen door op de knop Voorinstellingen te klikken. Selecteer vervolgens een glimmingsvariatie om een effect te kiezen. Klik op Kleur om alternatieve kleuren voor het effect te selecteren en sleep de formaat- en transparantiebalken om de trendlijngloed verder te configureren.
Waarden voorspellen met lineaire regressie
Nadat u de trendlijn hebt opgemaakt, kunt u er ook toekomstige waarden mee voorspellen. Laten we bijvoorbeeld eens aannemen dat u drie maanden na mei een gegevenswaarde moet voorspellen voor augustus, die niet is opgenomen in onze tabel. Vervolgens kunt u op trendlijnopties klikken en '3' invoeren in het tekstvak Doorsturen. De lineaire regressie-trendlijn benadrukt dat de waarde van augustus waarschijnlijk net boven de 3500 zal zijn, zoals hieronder te zien is.
Elke lineaire regressie-trendlijn heeft een eigen vergelijking en een r -waarde die u aan het diagram kunt toevoegen. Klik op het selectievakje Vergelijking weergeven op diagram om de vergelijking toe te voegen aan de grafiek. Die vergelijking bevat een helling- en onderscheppingswaarde.
Als u de vierkante r-waarde aan de grafiek wilt toevoegen, klikt u op het selectievakje R-kwadraatwaarde weergeven in diagram . Dat voegt r kwadraat toe aan de grafiek net onder de vergelijking zoals in de onderstaande afbeelding. U kunt de vergelijking en het correlatievak slepen om de positie in de scatterplot te wijzigen.
De lineaire regressiefuncties
Excel bevat ook lineaire regressiefuncties waarmee u de slope, intercept en r square-waarden kunt vinden voor y- en x-gegevensarrays. Selecteer een spreadsheetcel om een van die functies aan toe te voegen en druk vervolgens op de knop Functie invoegen . De lineaire regressiefuncties zijn statistisch, dus selecteer Statistiek in het vervolgkeuzemenu categorie. Vervolgens kunt u RSQ, SLOPE of INTERCEPT selecteren om hun functievensters te openen, zoals hieronder.
De RSQ-, SLOPE- en INTERCEPT-vensters zijn vrijwel hetzelfde. Ze omvatten de vakken Known_y's en Known_x's die u kunt selecteren om de variabelen y en x toe te voegen aan uw tabel. Merk op dat de cellen alleen cijfers moeten bevatten, dus vervang maanden in de tabel door overeenkomstige cijfers zoals 1 voor Jan, 2 voor Feb, enz. Klik vervolgens op OK om het venster te sluiten en de functie aan de spreadsheet toe te voegen.
U kunt nu uw Excel-spreadsheetgrafieken opschroeven met lineaire regressie-trendlijnen. Ze zullen de algemene trends voor de gegevenspunten van grafieken benadrukken en met de regressievergelijkingen zijn ze ook handige prognosetools.
