Excel-regressieanalyse (inhoudsopgave)
- Regressieanalyse in Excel
- Wiskundige verklaring van regressie
- Hoe lineaire regressie uit te voeren in Excel?
- # 1 - Regressietool met behulp van Analysis ToolPak in Excel
- # 2 - Regressieanalyse met behulp van Scatterplot met Trendline in Excel
Regressieanalyse in Excel
Lineaire regressie is een statistische techniek die de lineaire relatie onderzoekt tussen een afhankelijke variabele en een of meer onafhankelijke variabelen.
- Afhankelijke variabele (ook bekend als respons / uitkomstvariabele): is de variabele van uw interesse en die u wilde voorspellen op basis van de beschikbare informatie over onafhankelijke variabele (n).
- Onafhankelijke variabele (ook wel verklarende / voorspellende variabele genoemd): Is / zijn de variabele (n) waarvan de responsvariabele afhankelijk is. Dit betekent dat dit de variabelen zijn waarmee de responsvariabele kan worden voorspeld.
Lineaire relatie betekent dat de verandering in een onafhankelijke variabele (n) een verandering in de afhankelijke variabele veroorzaakt.
Er zijn in principe ook twee soorten lineaire relaties.
- Positieve lineaire relatie: wanneer de onafhankelijke variabele toeneemt, neemt ook de afhankelijke variabele toe.
- Negatieve lineaire relatie: Wanneer de onafhankelijke variabele toeneemt, neemt de afhankelijke variabele af.
Dit waren enkele van de vereisten voordat u daadwerkelijk verder ging naar regressieanalyse in excel.
Er zijn twee basismanieren om lineaire regressie in Excel uit te voeren met:
- Regressietool via Analysis ToolPak
- Spreidingsdiagram met trendlijn
Er is eigenlijk nog een methode die handmatige formules gebruikt om lineaire regressie te berekenen. Maar waarom zou je ervoor gaan als Excel berekeningen voor je doet?
Daarom gaan we het alleen hebben over de twee hierboven besproken methoden.
Stel dat u gegevens over lengte en gewicht van 10 personen hebt. Als u deze informatie in een grafiek plot, laten we kijken wat het oplevert.
Zoals de bovenstaande schermafbeelding laat zien, is de lineaire relatie te vinden in lengte en gewicht via de grafiek. Raak nu niet veel betrokken bij de grafiek, we gaan het hoe dan ook diepgraven in het tweede deel van dit artikel.
Wiskundige verklaring van regressie
We hebben een wiskundige uitdrukking voor lineaire regressie zoals hieronder:
Y = aX + b + ε
Waar,
- Y is een afhankelijke variabele of responsvariabele.
- X is een onafhankelijke variabele of voorspeller.
- a is de helling van de regressielijn. Dat betekent dat wanneer X verandert, er een verandering is in Y door "a" -eenheden.
- b onderschept. Het is de waarde die Y aanneemt wanneer de waarde van X nul is.
- ε is de willekeurige foutterm. Komt voor omdat de voorspelde waarde van Y nooit exact hetzelfde zal zijn als de werkelijke waarde voor gegeven X. Deze foutterm hoeft ons geen zorgen te maken. Omdat er enkele software's zijn die de berekening van deze foutterm in de backend voor u doen. Excel is een van die software.
In dat geval wordt de vergelijking
Y = aX + b
Die kan worden weergegeven als:
Gewicht = a * Hoogte + b
We zullen proberen de waarden van deze a en b te achterhalen met behulp van de hierboven besproken methoden.
Hoe lineaire regressie uit te voeren in Excel?
In het verdere artikel worden de basisprincipes van regressieanalyse in Excel uitgelegd en worden een paar verschillende manieren getoond om lineaire regressie in Excel uit te voeren.
U kunt deze Excel-sjabloon voor regressieanalyse hier downloaden - Excel-sjabloon voor regressieanalyse# 1 - Regressietool met behulp van Analysis ToolPak in Excel
In ons voorbeeld proberen we regressie voor Gewichtswaarden (die afhankelijke variabele is) aan te passen met behulp van Hoogtewaarden (die een onafhankelijke variabele is).
- Klik in de Excel-spreadsheet op Gegevensanalyse (aanwezig onder Analysegroep ) onder Gegevens.
- Zoek naar regressie . Selecteer het en druk op OK.
- Gebruik de volgende ingangen onder het regressiedeelvenster dat wordt geopend.
- Input Y Range : Selecteer de cellen die uw afhankelijke variabele bevatten (in dit voorbeeld B1: B11)
- Input X Range : Selecteer de cellen die uw onafhankelijke variabele bevatten (in dit voorbeeld A1: A11).
- Vink het vakje Labels aan als uw gegevens kolomnamen hebben (in dit voorbeeld hebben we kolomnamen).
- Het betrouwbaarheidsniveau is standaard ingesteld op 95%, wat kan worden gewijzigd volgens de gebruikersvereisten.
- Onder Uitvoeropties kunt u aanpassen waar u de uitvoer van regressieanalyse in Excel wilt zien. In dit geval willen we de uitvoer op hetzelfde blad zien. Daarom gegeven bereik dienovereenkomstig.
- Onder de optie Residuals hebt u optionele ingangen zoals Residuals, Residual Plots, gestandaardiseerde Residuals, Line Fit Plots die u naar wens kunt selecteren. Vink in dit geval het selectievakje Residuen aan zodat we de spreiding tussen voorspelde en werkelijke waarden kunnen zien.
- Onder de optie Normale waarschijnlijkheid kunt u Plots van normale waarschijnlijkheid selecteren waarmee u de normaliteit van voorspellers kunt controleren. Klik op OK .
- Excel berekent regressieanalyse voor u in een fractie van seconden.
Tot hier was het gemakkelijk en niet zo logisch. Het is echter een lastige taak om deze output te interpreteren en er waardevolle inzichten uit te halen.
Een belangrijk onderdeel van deze gehele uitvoer is R Vierkant / Aangepast R Vierkant onder de SAMENVATTING UITVOERTabel. Die informatie geeft, hoe goed ons model past. In dit geval is de waarde R Square 0, 9547. Dat interpreteert dat het model een nauwkeurigheid heeft van 95, 47% (goede pasvorm). Of in een andere taal, wordt informatie over Y-variabele voor 95, 47% verklaard door X-variabele.
Het andere belangrijke deel van de gehele output is een tabel met coëfficiënten. Het geeft waarden van coëfficiënten die kunnen worden gebruikt om het model voor toekomstige voorspellingen te bouwen.
Nu wordt onze regressievergelijking voor voorspelling:
Gewicht = 0, 6746 * Hoogte - 38, 45508 ( hellingwaarde voor hoogte is 0, 6746 … en onderschepping is -38, 45508 …)
Heb je gekregen wat je hebt gedefinieerd? U hebt een functie gedefinieerd waarin u nu alleen de waarde Hoogte moet invoeren en u krijgt de waarde Gewicht.
# 2 - Regressieanalyse met behulp van Scatterplot met Trendline in Excel
Nu zullen we zien hoe we in Excel een regressievergelijking op een spreidingsdiagram zelf kunnen passen.
- Selecteer uw volledige twee kolomgegevens (inclusief kopteksten).
- Klik op Invoegen en selecteer Scatterplot onder het gedeelte met grafieken zoals weergegeven in de onderstaande afbeelding.
- Zie de outputgrafiek.
- Nu moeten we een minst vierkante regressielijn in deze grafiek hebben. Als u deze lijn wilt toevoegen, klikt u met de rechtermuisknop op een van de gegevenspunten in de grafiek en selecteert u de optie Trendlijn toevoegen .
- Hiermee kunt u een trendlijn hebben van het minste kwadraat van regressie zoals hieronder.
- Schakel onder de optie Trendlijn opmaken het selectievakje Weergavevergelijking in grafiek in.
- Hiermee kunt u de vergelijking van de minst vierkante regressielijn in de grafiek zien.
Dit is de vergelijking waarmee we de gewichtswaarden kunnen voorspellen voor een bepaalde set hoogtewaarden.
Dingen om te onthouden over regressieanalyse in Excel
- U kunt de lay-out van de trendlijn wijzigen onder de optie Trendlijn opmaken in spreidingsplot.
- Het wordt altijd aanbevolen om de resterende plots te bekijken terwijl u regressieanalyse uitvoert met Data Analysis ToolPak in Excel. Het geeft u een beter inzicht in de spreiding van de werkelijke Y-waarden en geschatte X-waarden.
- Eenvoudige lineaire regressie in Excel vereist geen ANOVA en aangepast R-vierkant om te controleren. Met deze functies kan rekening worden gehouden voor meervoudige lineaire regressie. Dat valt buiten het bestek van dit artikel.
Aanbevolen artikelen
Dit is een handleiding voor regressieanalyse in Excel geweest. Hier bespreken we hoe u regressieanalyse in Excel kunt doen, samen met Excel-voorbeelden en een downloadbare Excel-sjabloon. U kunt ook onze andere voorgestelde artikelen doornemen -
- Excel-tool voor gegevensanalyse
- Bereken ANOVA in Excel
- Hoe Excel Moving Averages te vinden
- Z TEST Voorbeelden in Excel