Meervoudige Lineaire Regressie Analyse
In dit artikel leg ik in simpele bewoordingen uit wat we verstaan onder Meervoudige Lineaire Regressie Analyse (MLRA) en hoe u dit in Excel kunt implementeren.
Wanneer te gebruiken?
U gebruikt MLRA wanneer er sprake is van meerdere invoer-data-kanalen die leiden naar 1 uitvoer-data-kanaal.
Hierbij kunt u denken aan een enquête waarbij een aantal vragen leiden naar een politieke partij of aan een laboratorium opstelling waarbij een aantal invoer sensoren een meter doen uitslaan, etc, etc.
Vanuit een aantal metingen met desbetreffende uitkomsten wilt u kunnen voorspellen wat de uitkomst is bij nieuwe invoer parameters zonder daadwerkelijk opnieuw de meting(en) te moeten uitvoeren.
Eén van de methodes is MLRA. Het voordeel van deze methode is dat ze een lineaire formule oplevert en dit is altijd relatief makkelijk te implementeren.
Wat was ook al weer een lineaire formule?
Een lineaire formule heeft de vorm y=a1x1+a2x2+…an-1xn-1+anxn+c, waarbij at de coëfficiënten en xt de (invoer) variabelen zijn.
De constante c is de waarde die overblijft wanneer alle xt nul zijn.
Als de formule in een grafiek wordt getekend dan levert dit een rechte lijn op, vandaar de term lineair.
Voorbeeldje:
De formule y=3x-1 geeft de volgende grafiek:
De constante c is hier -1 en is het snijpunt met de y-as.
De richting (s-coëfficiënt), tegenwoordig ook wel helling (s-hoek) genoemd, is 3. Dit betekent dat wanneer x met 1 naar rechts opschuift y met 3 naar boven opschuift.
Andersom gaat het als volgt: We hebben twee punten (x,y): A(-1,-4) en B(2,5). Kunnen we nu de lineaire formule bepalen?
Dat gaat als volgt: De richting krijgen we door het verschil van de y-en te delen door het verschil van de x-en, waarbij we rekening moeten houden met de volgorde. Dus als we voor het verschil van de y-en B-A nemen dan ook voor het verschil van de x-en.
De richting wordt nu: (5 – -4) / (2 – -1) = 9 / 3 = 3.
Het snijpunt met de y-as (de constante) krijgen we door A of B in te vullen in de formule y=3x+c. Laten we B nemen dan krijgen we 5=3*2+c=6+c, c=5-6=-1.
De formule luidt dus: y=3x-1.
In Excel kunt u de twee punten invullen en vervolgens de functies RICHTING en SNIJPUNT gebruiken:
Hoe gebruiken we MLRA?
Om dit met de hand te doen komt er een hoop wiskunde bij kijken. Maar Excel kan u helpen!
Laten we kijken naar een voorbeeld:
Er zijn 9 waarnemingen met 4 invoer-variabelen (X1 t/m X4) en 1 uitvoer-variabele (Y).
Wat we willen is een lineaire formule Y=aX1+bX2+cX3+dX4+e waarmee we Y-waarde vanuit de X-en kunnen berekenen.
We willen ook weten hoe betrouwbaar deze formule is, vandaar de A in MLRA, de Analyse.
Ga in Excel naar de tab GEGEVENS in het lint en klik daar met de muis op de optie Gegevensanalyse in het vak Analyse.
Kies uit de keuzelijst de optie Regressie en klik op de knop OK.
U krijgt het volgende scherm:
Vul dit scherm als volgt in:
Klik op de knop OK.
U krijgt nu het volgende rapport:
In dit voorbeeld zijn de volgende waardes van belang:
De Coëfficiënten: Dit geeft de volgende formule: Y = -2,33 + -0,71 * X1 + 0,06 * X2 + 0,07 * X3 + 1,65 * X4. Een keurig lineair verband.
Eén van de belangrijke graadmeters voor de betrouwbaarheid van deze formule is R2 of R-kwadraat, dit is een getal tussen de -1 en 1 en geeft aan hoe de variabelen met de uitkomst in verband staan. Hoe dichter bij 1 (of -1) hoe beter.
In dit voorbeeld is R2 0,93. Dit mogen we als volgt vertalen: De uitkomsten (Y) worden voor 93% “verklaart” uit de coëfficiënten van de variabelen. Dit is goed betrouwbaar en bruikbaar.
Om nog wat nader te kijken waarom R2 niet 1 is kunnen we naar de storingen kijken. We zien dan dat met name de 5e waarneming een behoorlijke afwijking vertoont met de voorspelde waarde. Het kan zijn dat dit een meetfout betreft. Wanneer je dit zou willen corrigeren dan moet de Y-waarde van de 5e waarneming worden aangepast in (bv.) 11 (de R2 wordt dan 0,96).
Stel nu dat er nieuwe waardes binnen komen voor de X-en, laten we zeggen: 5, 5, 7, en 8 (voor resp. X1 t/m X4). De waarde van Y is nu met de formule uit te rekenen: Y = Y = -2,33 + -0,71 * 5 + 0,06 * 5 + 0,07 * 7 + 1,65 * 8 = 8,19.
Nadelen MLRA in Excel
Er zijn mijns inziens twee grote nadelen aan de MLRA van Excel zoals hier boven beschreven.
- Het rapport is statisch. Dit betekent dus dat wanneer ik de 5e waarneming zou aanpassen ik het rapport opnieuw moet laten uitvoeren.
- Vanuit de analyse wil ik een functie hebben met de berekende formule, zodat ik deze niet zelf hoef in te voeren.
Deze twee nadelen worden opgelost door de MLRA-optie in de Excel-Handigheidjes-Add-In van HJGSoft.
Vanuit de tab HJGSoft in het lint klikt u op de knop MLRA in het vak Statistiek.
U krijgt het volgende scherm:
Vul dit scherm als volgt in:
Klik op de knop OK.
U ziet nu, min of meer, dezelfde resultaten als bij de statische rapportage van Excel:
Verander nu eens de Y-waarde van de 5e waarneming van 10 naar 11.
U ziet meteen de MLRA van HJGSoft mee veranderen, het is dus een dynamische analyse!
Verander de waarde weer terug om het voorbeeld zuiver te houden.
Ga met de celwijzer ergens in de analyse staan en klik weer op de knop MLRA in de tab HJGSoft.
Het dialoogscherm komt weer in beeld.
Klik nu het vakje voor UDF aanmaken aan:
Klik op de knop OK.
U heeft nu de Excel-functie HJG_Y toegevoegd en kunt deze gebruiken. Hij is te vinden in de functie-categorie “Door gebruiker gedefinieerd”:
En deze functie kunt u nu gebruiken voor de getallen 5, 5, 7 en 8:
MLRA is onderdeel van de Excel-Handigheidjes van HJGSoft. Meer informatie vindt u op de pagina Excel. U kunt de tool downloaden op de Download-pagina en kunt deze daarna gratis 10 dagen uitproberen.