Hoe het correlatiecoëfficiënt tussen twee variabelen in Excel te berekenen?
We gebruiken meestal de correlatiecoëfficiënt (een waarde die varieert van -1 tot 1) om de sterkte en richting van de lineaire relatie tussen twee variabelen aan te geven. Een correlatiecoëfficiënt is een veelgebruikte statistiek die u helpt relaties te begrijpen, zoals de connectie tussen verkoop en advertentie-uitgaven, temperatuur en ijsverkoop, of andere gepaarde gegevens. In Excel zijn er meerdere eenvoudige methoden om de correlatiecoëfficiënt te berekenen, inclusief ingebouwde functies en analysehulpmiddelen.
Methode A: Gebruik direct de CORREL-functie
Methode B: Pas Data-analyse toe en output de analyse
Methode C: Gebruik 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
Beschouw twee lijsten met gegevens, elk vertegenwoordigend een variabele. Als je de correlatiecoëfficiënt tussen deze twee variabelen in Excel wilt berekenen, is deze methode zowel snel als efficiënt.
Voor praktisch gebruik, zorg ervoor dat beide gegevensbereiken numeriek zijn en hetzelfde aantal observaties bevatten. Bijvoorbeeld, als je de volgende gepaarde gegevens hebt:
Selecteer een lege cel waar je het berekeningsresultaat wilt weergeven. Voer de volgende formule in en druk vervolgens op de "Enter"-toets om de correlatiecoëfficiënt te berekenen:
=CORREL(A2:A7,B2:B7)
In deze formule vertegenwoordigen A2:A7 en B2:B7 de twee lijsten met variabelen die je wilt analyseren. De bereiken moeten van gelijke lengte zijn en elk paar moet overeenkomen met dezelfde observatie.
Praktische tip: CORREL negeert automatisch lege cellen en tekst, maar als er geen geldige numerieke paren in de twee kolommen zijn, zal het een #DIV/0! fout retourneren. Zorg ervoor dat uw gegevens goed zijn uitgelijnd en numerieke paren bevat voor een nauwkeurige correlatieberekening.
Zodra je de correlatiecoëfficiënt hebt berekend, kun je een lijndiagram invoegen om de relaties visueel te observeren en de correlatie verder te interpreteren, zoals hieronder weergegeven:
Deze methode is het beste geschikt voor snelle, handmatige controles tussen twee kleine datasets of wanneer interactief wordt gewerkt binnen uw spreadsheet. Het is geschikt voor gebruikers die een onmiddellijk resultaat zoeken zonder behoefte aan geavanceerde statistische output.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Methode B: Pas Data-analyse toe en output de analyse
Als u de correlatie tussen meerdere variabelen tegelijk wilt analyseren of een meer uitgebreide uitvoertabel wilt hebben, biedt Excel’s “Analysis Toolpak” een nuttige oplossing. Deze add-in genereert een correlatiematrix en stelt u in staat om verschillende variabelen in één stap te vergelijken, wat waardevol is voor grotere datasets of het opbouwen van statistische rapporten.
1. Als u de Data Analysis-add-in al hebt toegevoegd aan uw Data-tabblad, kunt u doorgaan naar stap 3. Anders klikt u op Bestand > Opties. Kies in het dialoogvenster “Excel-opties” Invoegtoepassingen uit het linkerpaneel en klik vervolgens op de Ga knop naast het vak “Excel-invoegtoepassingen”.
2. Schakel in het dialoogvenster “Invoegtoepassingen” het selectievakje in dat gelabeld is met Analyse ToolPak, en klik dan op OK. Dit voegt de groep "Data-analyse" toe aan de Gegevens tab.
3. Klik vervolgens op Gegevens > Data-analyse. Kies in het pop-upvenster “Data-analyse” Correlatie uit de lijst en klik vervolgens op OK.
4. Configureer het volgende in het Correlatie-dialoogvenster:
1) Selecteer het bereik dat uw gegevens bevat.
2) Kies ofwel de optie "Kolommen" of "Rijen", afhankelijk van hoe uw gegevens zijn georganiseerd.
3) Als uw gegevens koppen bevat, schakel dan de optie “Labels in eerste rij” in.
4) Specificeer een uitvoerlocatie in “Uitvoeropties” om de resultaten weer te geven.
5. Klik op OK om de correlatieanalysistabel te genereren. De correlatiecoëfficiënten worden weergegeven in het gespecificeerde bereik.
Deze methode is geschikt wanneer u de relaties tussen meer dan twee variabelen wilt evalueren of een samenvattende tabel nodig heeft voor rapportagedoeleinden. De uitvoer van Data-analyse is beknopt, maar biedt geen extra significantiestatistieken. Als u onverwachte resultaten krijgt, controleer dan uw gegevens op consistentie, lege cellen en juiste bereikselectie.
Methode C: Gebruik PEARSON-functie als alternatief
Naast CORREL biedt Excel de PEARSON-functie, die ook de Pearson-correlatiecoëfficiënt tussen twee variabelen berekent. Functioneel retourneren PEARSON en CORREL hetzelfde resultaat. Echter, PEARSON houdt zich strikt aan de originele wiskundige formule, terwijl CORREL geoptimaliseerd is voor Excel’s omgeving. Als u gewend bent aan statistische theorie of werkt met statistische tools buiten Excel, kan PEARSON vertrouwder voelen.
Bijvoorbeeld, met twee numerieke lijsten in A2:A7 en B2:B7, kunt u de correlatie als volgt berekenen:
1. Selecteer een cel waar u het resultaat wilt weergeven, en voer deze formule in:
=PEARSON(A2:A7,B2:B7)2. Druk op Enter om de berekening te voltooien. Als u extra paren gegevens wilt analyseren, past u de celbereiken dienovereenkomstig aan, of sleept u de formule naar andere cellen.
Tips: PEARSON negeert tekst of logische waarden, dus zorg ervoor dat beide bereiken alleen numerieke waarden bevatten en van gelijke lengte zijn. Als er ontbrekende gegevens in één kolom zijn, pas dan uw bereiken dienovereenkomstig aan om fouten te voorkomen.
Het gebruik van PEARSON is vooral praktisch voor gebruikers die overstappen van andere statistische software, of voor academische instellingen waar strikte naleving van terminologie wordt verwacht. Beide CORREL en PEARSON geven hetzelfde resultaat voor typische gebruiksscenario's in Excel.
Als u een #DIV/0! fout tegenkomt, controleer dan of beide bereiken even lang zijn en geen niet-gekoppelde lege of niet-numerieke cellen bevatten.
Voordelen: Gemakkelijk te gebruiken, consistent met statistische software; Nadelen: Biedt geen significante verschil van CORREL voor de meeste gebruikers.
Methode D: Gebruik VBA-code om correlatiecoëfficiënten voor meerdere paren te berekenen
Als u de berekening van correlatiecoëfficiënten voor verschillende paren gegevens moet automatiseren (bijvoorbeeld bij het werken met veel combinaties van variabelen), is het schrijven van een eenvoudige VBA-macro een efficiënte keuze. Deze methode is geschikt voor gevorderde gebruikers die grote datasets willen verwerken of repetitieve analysistaken willen automatiseren.
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. Na het invoegen van de code sluit u de VBA-editor. Druk in Excel op Alt + F8, selecteer BatchCalculateCorrelations, en klik op Uitvoeren. U wordt gevraagd om het volgende te selecteren:
- Het eerste variabelenbereik (één kolom, bijv. A2:A7)
- Het tweede variabelenbereik (één of meer kolommen, bijv. B2:D7)
- De cel waar u de resultaten wilt laten beginnen (bijv. F2)
De macro berekent vervolgens de correlatiecoëfficiënt tussen de eerste variabele en elke kolom in het tweede bereik, waarbij de resultaten horizontaal worden weergegeven vanaf de gekozen cel.
Voordelen: Automatiseert herhalende berekeningen, bespaart aanzienlijke tijd met grote datasets en zorgt voor consistentie.
Als u problemen tegenkomt zoals “De twee gegevensbereiken moeten hetzelfde aantal rijen hebben”, zorg er dan voor dat alle geselecteerde kolommen exact hetzelfde aantal rijen delen en geen lege rijen hebben. Voor foutopsporing, controleer of macros zijn ingeschakeld en bereiken correct zijn geselecteerd.
Bij het werken met correlatiecoëfficiënten in Excel hangt de keuze van 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 gemakkelijk te gebruiken. Voor meerdere variabelen of de behoefte aan samenvattende tabellen is de Analysetoolpak zeer praktisch. Als u herhaalanalyses nodig hebt op grote datasets of aangepaste workflows wilt, overweeg dan automatisering met VBA om tijd te besparen en menselijke fouten te verminderen.
Zorg er altijd voor dat uw gegevensbereiken zijn uitgelijnd, schoon zijn en geen lege of niet-numerieke cellen bevatten om formulefouten te voorkomen. Als u onverwachte resultaten tegenkomt, controleer dan selecties en gegevenstypen.
Gerelateerde artikelen
- Percentageverandering of verschil tussen twee getallen in Excel berekenen
Dit artikel gaat over het berekenen van percentageverandering of verschil tussen twee getallen in Excel.
- Bereken of wijs lettercijfer toe in Excel
Om voor elke student een lettercijfer toe te wijzen op basis van hun scores, kan een gebruikelijke taak voor een leraar zijn. Bijvoorbeeld, ik heb een beoordelingsschaal gedefinieerd waarbij score 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B, en 90-100 = A, zie meer.
- Kortingpercentage of prijs berekenen in Excel
Wanneer Kerstmis nadert, zijn er vast veel promoties in winkelcentra. Maar als de verschillende soorten items verschillende kortingen hebben, hoe bereken je dan de kortingspercentages of prijzen van de verschillende items?
- Tel het aantal dagen / werkdagen / weekenden tussen twee data in Excel
Misschien wil je soms alleen de werkdagen tussen twee data berekenen, en soms moet je alleen de weekenddagen tellen tussen de twee data.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen