Correlatie en Regressie
         
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 en 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.
Je 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 matrix 2 de cellen van de y-variabele (in ons geval C2:C11)
         
 

         
  Druk op OK en je vindt de correlatiecoëfficiënt  r = 0,578...
         
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 (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 ·  gx

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 dat g = 10a  en  B = 10b

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 = 100,47634 = 2,99
F14 =  index(lijnsch(F2:F11;E2:E11);2) geeft b = 0,415021 dus  B = 100,415021  = 2,6
De beste exponentiële formule is dus  y = 2,6 · 2,99x