Hoe berekent u de correlatiecoëfficiënt tussen twee variabelen in Excel?
We gebruiken meestal de correlatiecoëfficiënt – een waarde tussen -1 en 1 – om de sterkte en richting van het lineaire verband tussen twee variabelen weer te geven. Deze veelgebruikte statistiek helpt bij het inzichtelijk maken van relaties, zoals die tussen verkoop en advertentie-uitgaven, temperatuur en ijsverkoop, of andere onderling verbonden gegevens. In Excel zijn diverse eenvoudige methoden beschikbaar om de correlatiecoëfficiënt te berekenen, waaronder ingebouwde functies en analysehulpmiddelen.
Methode A: Gebruik direct de CORREL-functie
Methode B: Pas Data-analyse toe en genereer de analyse
Methode C: Gebruik de PEARSON-functie als alternatief
Methode D: Gebruik VBA-code om correlatiecoëfficiënten voor meerdere paren te berekenen
Methode A: Gebruik direct de CORREL-functie
Neem twee lijsten met gegevens, elk vertegenwoordigend een variabele. Deze methode is de snelste en meest efficiënte manier om de correlatiecoëfficiënt tussen deze twee variabelen in Excel te berekenen.
Zorg in de praktijk dat beide bereiken numeriek zijn en evenveel waarnemingen bevatten. Bijvoorbeeld, bij de volgende gekoppelde gegevens:
Selecteer een lege cel waarin u het berekeningsresultaat wilt weergeven, voer de volgende formule in en druk op Enter om de correlatiecoëfficiënt te berekenen:
=CORREL(A2:A7,B2:B7)
In deze formule staan A2:A7 en B2:B7 voor de twee lijsten met variabelen die u wilt analyseren. De bereiken moeten even lang zijn, en elk paar waarden moet corresponderen met dezelfde waarneming.
Praktische tip: CORREL negeert automatisch lege cellen en tekst, maar als er geen geldige numerieke paren in de twee kolommen aanwezig zijn, retourneert de functie een #DIV/0!-fout. Zorg ervoor dat uw gegevens correct uitgelijnd zijn en numerieke paren bevatten voor een nauwkeurige correlatieberekening.
Nadat u de correlatiecoëfficiënt hebt berekend, kunt u een lijndiagram invoegen om de relaties visueel te observeren en de correlatie verder te interpreteren, zoals hieronder wordt weergegeven:
Deze methode is het meest geschikt voor snelle, handmatige controles tussen twee kleine datasets of wanneer u interactief in uw werkblad werkt. Het is ideaal voor gebruikers die onmiddellijk een resultaat willen zonder geavanceerde statistische uitvoer.
Ontgrendel de magie van Excel met KUTOOLS AI
- Slimme uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak grafieken — allemaal met eenvoudige opdrachten.
- aangepaste formules: Genereer op maat gemaakte formules om uw workflows te stroomlijnen.
- VBA-programmeren: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules moeiteloos.
- Tekstvertaling: Doorbreek taalbarrières in uw spreadsheets.
Methode B: Pas Data-analyse toe en genereer de analyse
Als u de correlatie tussen meerdere variabelen tegelijk wilt analyseren of een uitgebreidere uitvoertabel nodig heeft, biedt de Analysis ToolPak van Excel een krachtige oplossing. Deze invoegtoepassing genereert een correlatiematrix en stelt u in staat om meerdere variabelen in één keer te vergelijken — ideaal voor grotere datasets of het samenstellen van statistische rapporten.
1. Als u de invoegtoepassing Data-analyse al aan uw tabblad Gegevens heeft toegevoegd, kunt u doorgaan naar stap 3. Klik anders op Bestand > Opties. Kies in het dialoogvenster „Excel-opties” Invoegtoepassingen in het linkerdeelvenster en klik vervolgens op de knop Ga naar naast het vak „Excel-invoegtoepassingen”.
2. Schakel in het dialoogvenster „Invoegtoepassingen” het selectievakje met het label Analysis ToolPak in en klik vervolgens op OK. Hierdoor wordt de groep „Data-analyse” toegevoegd aan het tabblad Gegevens.
3. Klik vervolgens op Gegevens > Data-analyse. Kies in het pop-updialoogvenster „Data-analyse” Correlatie in de lijst en klik daarna op OK.
4. Configureer in het dialoogvenster Correlatie het volgende:
1) Selecteer het bereik met uw gegevens.
2) Kies „Kolommen” of „Rijen”, afhankelijk van hoe uw gegevens zijn georganiseerd.
3) Als uw gegevens koppen bevatten, schakelt u de optie „Labels in eerste rij” in.
4) Geef onder „Uitvoeropties” een uitvoerlocatie op om de resultaten weer te geven.
5. Klik op OK om de correlatie-analysetabel te genereren. De correlatiecoëfficiënten worden weergegeven in het beperkte bereik.
Deze methode is ideaal wanneer u de relaties tussen meer dan twee variabelen wilt analyseren of een overzichtelijke samenvattende tabel nodig heeft voor rapportagedoeleinden. De uitvoer van de Data-analyse is beknopt, maar bevat geen aanvullende significantiestatistieken. Controleer bij onverwachte resultaten uw gegevens op consistentie, lege cellen en de juiste bereikselectie.
Methode C: Gebruik de PEARSON-functie als alternatief
Naast CORREL biedt Excel ook de PEARSON-functie, die eveneens de Pearson-correlatiecoëfficiënt tussen twee variabelen berekent. Functioneel gezien leveren PEARSON en CORREL hetzelfde resultaat. PEARSON volgt echter strikt de oorspronkelijke wiskundige formule, terwijl CORREL is geoptimaliseerd voor de Excel-omgeving. Als u vertrouwd bent met statistische theorie of werkt met statistische tools buiten Excel, voelt PEARSON mogelijk vertrouwder aan.
Bijvoorbeeld, met twee numerieke lijsten in A2:A7 en B2:B7 kunt u de correlatie als volgt berekenen:
1. Selecteer een cel waarin u het resultaat wilt weergeven en voer de volgende formule in:
=PEARSON(A2:A7,B2:B7)2. Druk op Enter om de berekening te voltooien. Als u extra gegevensparen wilt analyseren, past u de celbereiken dienovereenkomstig aan of sleept u de formule naar andere cellen.
Tips: PEARSON negeert tekst en logische waarden, dus zorg ervoor dat beide bereiken uitsluitend numerieke waarden bevatten en even lang zijn. Als er ontbrekende gegevens zijn in één kolom, past u uw bereiken dienovereenkomstig aan om fouten te voorkomen.
PEARSON gebruiken is vooral praktisch voor gebruikers die overstappen vanuit andere statistische software of voor academische contexten waar strikte naleving van terminologie wordt verwacht. Zowel CORREL als PEARSON leveren voor typische use cases in Excel hetzelfde resultaat.
Als u een #DIV/0!-fout krijgt, controleer dan of beide bereiken even lang zijn en geen lege of niet-numerieke cellen bevatten die niet met elkaar overeenkomen.
Voordelen: Eenvoudig te gebruiken en consistent met statistische software;Nadelen: Biedt voor de meeste gebruikers geen significant verschil ten opzichte van CORREL.
Methode D: Gebruik VBA-code om correlatiecoëfficiënten voor meerdere paren te berekenen
Als u de berekening van correlatiecoëfficiënten voor meerdere gegevensparen wilt automatiseren—bijvoorbeeld bij het werken met talloze variabelencombinaties—is het schrijven van een eenvoudige VBA-macro een efficiënte oplossing. Deze aanpak is ideaal voor gevorderde gebruikers die grote datasets willen verwerken of herhaalde analyses geautomatiseerd uit willen voeren.
1. Om deze methode te gebruiken, opent u eerst de VBA-editor door te klikken op Ontwikkelaar > Visual Basic. Ga in het venster Visual Basic for Applications naar Invoegen > Module en plak vervolgens de volgende code in de module:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub2. Sluit de VBA-editor nadat u de code hebt ingevoegd. Druk in Excel op Alt + F8, selecteer BatchCalculateCorrelations en klik op Uitvoeren. U wordt gevraagd het volgende te selecteren:
- Het eerste variabelenbereik (één enkele kolom, bijvoorbeeld A2:A7)
- Het tweede variabelenbereik (één of meer kolommen, bijvoorbeeld B2:D7)
- De cel waarin u wilt dat de resultaten beginnen (bijvoorbeeld F2)
De macro berekent vervolgens de correlatiecoëfficiënt tussen de eerste variabele en elke kolom in het tweede bereik, en toont de resultaten horizontaal vanaf de geselecteerde cel.
Voordelen: automatiseert herhalende berekeningen, bespaart aanzienlijk tijd bij grote datasets en garandeert consistentie.
Als u foutmeldingen zoals „De twee bereiken moeten hetzelfde aantal rijen hebben” tegenkomt, controleer dan of alle geselecteerde kolommen exact evenveel rijen bevatten en geen lege rijen omvatten. Voor een effectieve oplossing dient u te verifiëren of macro’s zijn ingeschakeld en of de bereiken correct zijn geselecteerd.
Bij het werken met correlatiecoëfficiënten in Excel hangt de keuze voor de juiste methode af van uw gegevensstructuur en analysebehoeften. Voor eenmalige, snelle berekeningen tussen twee reeksen zijn formules zoals CORREL of PEARSON efficiënt en eenvoudig te gebruiken. Voor meerdere variabelen of wanneer u overzichtstabellen nodig heeft, is de Analysis ToolPak zeer praktisch. Als u herhaalde analyses op grote datasets uitvoert of aangepaste workflows wilt, kunt u dit automatiseren met VBA om tijd te besparen en menselijke fouten te verminderen.
Zorg er altijd voor dat uw bereik uitgelijnd en schoon is en geen lege of niet-numerieke cellen bevat om formulefouten te voorkomen. Controleer bij onverwachte resultaten uw selecties en gegevenstypen nogmaals.
Gerelateerde artikelen
- Bereken het percentageverschil of de procentuele verandering tussen twee getallen in Excel
Dit artikel legt uit hoe u het percentageverschil of de procentuele verandering tussen twee getallen in Excel berekent.
- Bereken of wijs een cijferletter toe in Excel
Het toewijzen van een cijferletter aan elke student op basis van hun score is waarschijnlijk een veelvoorkomende taak voor docenten. Bijvoorbeeld: ik heb een beoordelingsschaal gedefinieerd waarbij scores van 0–59 = F, 60–69 = D, 70–79 = C, 80–89 = B en 90–100 = A opleveren; zie meer.
- Bereken de korting of de kortingsprijs in Excel
Met Kerstmis in aantocht zijn winkelcentra vol met acties. Maar als elk artikel een andere korting heeft, hoe berekent u dan snel de korting in procenten of de uiteindelijke prijs per artikel?
- Tel het aantal dagen, werkdagen of weekenddagen tussen twee datums in Excel
Soms wilt u misschien alleen de werkdagen tussen twee datums berekenen, en soms juist alleen de weekenddagen tellen.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins