Lineaire regressies modelleren een relatie tussen afhankelijke en onafhankelijke statistische gegevensvariabelen. In eenvoudiger bewoordingen markeren ze een trend tussen twee tabelkolommen op een spreadsheet. Als u bijvoorbeeld een Excel-spreadsheettabel instelt met een maand x-kolom en een set gegevens voor elk van de maanden in de aangrenzende y-kolom registreert, zal lineaire regressie de trend tussen de x- en y-variabelen markeren door trendlijnen toe te voegen aan de tabel grafieken. Zo kunt u lineaire regressie toevoegen aan Excel-grafieken.
Een lineaire regressie-trendlijn aan de grafiek toevoegen
- Open eerst een leeg Excel-spreadsheet, selecteer cel D3 en voer 'Maand' in als de kolomkop, wat de x-variabele zal zijn.
- Klik vervolgens op cel E3 en voer 'Y-waarde' in als de y-variabele kolomkop. Dit is in feite een tabel met een geregistreerde reeks gegevenswaarden voor de maanden jan-mei.
- Voer de maanden in de cellen D4 tot D8 in en de gegevenswaarden daarvoor in de cellen E4 tot E8, zoals weergegeven in de snapshot 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 Verstrooien > Verstrooien met alleen markeringen om de grafiek aan de spreadsheet toe te voegen, zoals hieronder. U kunt ook op de sneltoets Alt + F1 drukken om een staafdiagram in te voegen.
- Klik vervolgens met de rechtermuisknop op de grafiek en selecteer Grafiektype wijzigen >X Y (verstrooiing) >Verstrooien met alleen markeringen.
Vervolgens kunt u de trendlijn toevoegen aan de spreidingsplot
- Selecteer een van de gegevenspunten op de spreidingsplot en klik met de rechtermuisknop om het contextmenu te openen, dat een Trendlijn toevoegen optie.
- Selecteer Trendlijn toevoegen om het venster direct hieronder in de snapshot te openen. Dat venster heeft vijf tabbladen met verschillende opmaakopties voor lineaire regressie-trendlijnen.
3. Klik op Trendlijn opties en selecteer daar een regressietype. U kunt selecteren: exponentieel, Lineair, logaritmisch, voortschrijdend gemiddelde, Stroom en veelterm regressietype-opties vanaf daar.
4. Selecteer Lineair en klik Dichtbij om die trendlijn toe te voegen aan de grafiek zoals hieronder weergegeven.
De lijnregressie-trendlijn in de bovenstaande grafiek laat zien dat er een algemene opwaartse relatie is tussen de x- en y-variabelen, ondanks een paar druppels op de grafiek. Merk op dat de lineaire regressietrendlijn geen van de gegevenspunten op de grafiek overlapt, dus het is niet hetzelfde als uw gemiddelde lijngrafiek die elk punt verbindt.
De lineaire regressietrendlijn opmaken
Het opmaken van de lineaire regressie-trendlijn is een belangrijk hulpmiddel bij het maken van leesbare, duidelijke grafieken in Excel.
- Om te beginnen met het opmaken van de trendlijn, moet u er met de rechtermuisknop op klikken en Trendlijn opmaken.
- Dat zal het venster Trendlijn formatteren opnieuw openen van waaruit u kunt klikken Lijnkleur.
- Selecteer Ononderbroken lijn en klik op de Kleur om een palet te openen waaruit u een alternatieve kleur voor de trendlijn kunt kiezen.
- Klik op het tabblad Lijnstijl om de lijnstijl aan te passen. Vervolgens kunt u de pijlbreedte aanpassen en de pijlinstellingen configureren.
- druk de Pijlinstellingen knoppen om pijlen aan de lijn toe te voegen.
U kunt ook effecten aan uw trendlijn toevoegen voor esthetische doeleinden
- Voeg een gloedeffect toe aan de trendlijn door te klikken op Gloed en Zachte randen. Dat opent het onderstaande tabblad van waaruit u gloed kunt toevoegen door op de te klikken Voorinstellingen knop.
- Selecteer vervolgens een gloedvariatie om een effect te kiezen. Klik Kleur om alternatieve kleuren voor het effect te selecteren, en u kunt de Maat en Transparantie balken 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 aannemen dat u een gegevenswaarde drie maanden na mei voor augustus moet voorspellen, die niet in onze tabel is opgenomen.
- klik op Trendline-opties en typ '3' in het tekstvak Doorsturen.
- De lineaire regressie-trendlijn benadrukt dat de waarde van augustus waarschijnlijk net boven de 3.500 zal liggen, zoals hieronder weergegeven.
Elke lineaire regressietrendlijn heeft zijn eigen vergelijking en r-kwadraatwaarde die u aan de grafiek kunt toevoegen.
- Klik op de Vergelijking op grafiek weergeven selectievakje om de vergelijking aan de grafiek toe te voegen. Die vergelijking bevat een hellings- en snijpuntwaarde.
- Om de r-kwadraatwaarde aan de grafiek toe te voegen, klikt u op de Toon R-kwadraat waarde op grafiek selectievakje. Dat voegt r kwadraat toe aan de grafiek net onder de vergelijking zoals in de onderstaande snapshot.
- Sleep het vergelijkings- en correlatievak om de positie op de spreidingsplot te wijzigen.
De lineaire regressiefuncties
Excel bevat ook lineaire regressiefuncties waarmee u de hellings-, snijpunt- en r-kwadraatwaarden kunt vinden voor y- en x-gegevensarrays.
- Selecteer een spreadsheetcel om een van die functies aan toe te voegen en druk vervolgens op de Functie invoegen knop. De lineaire regressiefuncties zijn statistisch, dus selecteer Statistisch uit het vervolgkeuzemenu categorie.
- Dan kunt u selecteren: RSQ, HELLING of ONDERSCHEPPEN om hun functievensters te openen zoals hieronder.
De vensters RSQ, SLOPE en INTERCEPT zijn vrijwel hetzelfde. Ze bevatten de vakken Known_y en Known_x die u kunt selecteren om de y- en x-variabelewaarden uit uw tabel toe te voegen. Houd er rekening mee dat de cellen alleen getallen mogen bevatten, dus vervang maanden in de tabel door overeenkomstige cijfers zoals 1 voor januari, 2 voor februari, enz. Klik op oke om het venster te sluiten en de functie aan het werkblad toe te voegen.
Dus nu kunt u uw Excel-spreadsheetgrafieken verfraaien met lineaire regressietrendlijnen. Ze zullen de algemene trends voor de gegevenspunten van grafieken benadrukken, en met de regressievergelijkingen zijn ze ook handige prognosetools.
Heeft u tips, trucs of vragen met betrekking tot lineaire regressie-trendlijnen in Excel? Laat het ons weten in de commentaarsectie hieronder.