correlatie Excel

© h.hofstede (h.hofstede@hogeland.nl)

Correlatie & Regressie met Excel.

Soms heb je te maken met twee variabelen en ben je benieuwd of er misschien een verband tussen die twee bestaat. Bijvoorbeeld of, als de ene groter wordt, de andere dat ook doet.

Dat kun je met Excel onderzoeken.

We doen dat met een klein voorbeeld: De cijfers die een groep van 10 leerlingen hebben gehaald op hun proefwerk Wiskunde en op hun proefwerk Natuurkunde.
Dat zijn deze cijfers:

leerlingnummer.	1	2	3	4	5	6	7	8	9	10
cijfer wiskunde.	8,2	4,9	5,4	6,9	3,3	7,9	8,8	4,5	5,8	7,9
cijfer natuurkunde.	8,4	3,8	7,9	8,0	5,0	6,9	7,2	5,8	4,6	5,8

Laten we eerst in Excel een spreidingsdiagram maken, waarbij de wiskundecijfers op de x-as staan en de natuurkundecijfers op de y-as:

Je ziet in het spreidingsdiagram een beetje een soort van stijgende lijn. Dat zou betekenen dat een hoger wiskundecijfer "een beetje" hoort bij ook een hoger natuurkundecijfer. Er is een "soort van" verband tussen de cijfers.

Correlatiecoëfficiënt.

Gelukkig kan Excel voor ons bepalen hoe goed het verband tussen beide variabelen is, door een getal r te berekenen dat de correlatiecoëfficiënt heet.
Dat getal r varieert van -1 tot 1, en het betekent:

r = -1	de punten liggen perfect op een dalende lijn.
r = 0	de punten liggen willekeurig door elkaar, er is geen verband.
r = 1	de punten liggen perfect op een stijgende lijn.

Hoe dichter r bij 1 of -1 ligt, des te sterker is het verband (de "correlatie") tussen de twee variabelen.
Dus r = 0,9 betekent een sterkere correlatie (stijgend) dan r = 0,8.
En r = -0,5 betekent een zwakkere correlatie (dalend) dan r = -0,7

Je (of eigenlijk Excel natuurlijk) berekent r als volgt:

•

Kies achtereenvolgens: Formules → Meer functies → Statistisch → Correlatie

•

Voer nu bij matrix1 de cellen van de x-variabele in (in ons geval B2:B11) en bij matrix2 de cellen van de y-variabele (in ons geval C2:C11)

•

Druk op OK en je vindt de correlatiecoëfficiënt r = 0,578...

Dat betekent dat er een beetje een stijgende lijn is tussen x en y (want r is positief), maar niet super goed, want r is niet dicht bij 1. Een beetje een licht stijgend effect tussen beide variabelen dus.

Het kan overigens ook in één keer door in de cel de formule =correlatie(B2 : B11 ; C2 : C11) in te voeren (dubbele-punt tussen de cellen, punt-komma tussen de twee matrices)

Regressielijn.

Excel kan zelfs voor ons berekenen welke rechte lijn het best past bij onze meetpunten. Die lijn heet de regressielijn, en de a en b daarvan (jeweetwel, die van y = ax + b) bereken je als volgt (merk op dat je de cellen op dezelfde manier invult als hierboven, eerst de y, dan de x):

a = index(lijnsch(C2:C11; B2:B11);1)
b = index(lijnsch(C2:C11; B2:B11);2)

Probeer het maar: dat geeft a = 0,4942... en b = 3,1966...

De beste lijn bij deze meetpunten is dus de lijn y = 0,4842x + 3,1966

Kijk maar, lijkt aardig te kloppen toch?

Exponentiële Groei.

Als je vermoedt (of wilt onderzoeken) of exponentiële groei misschien beter bij de meetwaarden past, dan ga je dus op zoek naar een formule van de vorm y = B · g^x

Als je dan alle y-waarden vervangt door log(y) en alle x-waarden door log(x) dan kun je op die nieuwe tabel weer gewoon correlatie en regressie als hierboven toepassen.
Je vindt dan een r en een a en een b

Bedenk alleen wel dat voor je formule geldt: g = 10^a en B = 10^b

Met bovenstaand voorbeeld geeft dat:

Als volgt ingevoerd:
	E2 = log(B2) en dan omlaag kopiëren. F2 = log(C2) en dan omlaag kopiëren. F12 = correlatie(F2:F11;E2:E11) geeft r = 0,5699 en dat is iets minder goede correlatie dan bij de rechte lijn. F13 = index(lijnsch(F2:F11;E2:E11);1) geeft a = 0,47634 dus g = 10^0,47634 = 2,99 F14 = index(lijnsch(F2:F11;E2:E11);2) geeft b = 0,415021 dus B = 10^0,415021 = 2,6
De beste exponentiële formule is dus y = 2,6 · 2,99^x