Excel is een krachtig hulpmiddel dat een aantal geweldige functies en functionaliteiten heeft bij het werken met statistieken.
Het vinden van een correlatie tussen twee datareeksen is een van de meest voorkomende statistische berekeningen bij het werken met grote datasets,
Ik werkte een paar jaar geleden als financieel analist, en hoewel we niet veel met statistische gegevens bezig waren, moesten we nog vrij vaak correlaties vinden.
In deze tutorial laat ik je twee heel eenvoudige manieren zien om correlatiecoëfficiënt berekenen in Excel. Er is al een ingebouwde functie om dit te doen, en je kunt ook de Data Analysis Toolpak gebruiken.
Dus laten we beginnen!
Wat is de correlatiecoëfficiënt?
Aangezien dit geen statistiekles is, zal ik kort uitleggen wat de correlatiecoëfficiënt is, en dan gaan we verder met het gedeelte waar we de correlatiecoëfficiënt in Excel berekenen.
Een correlatiecoëfficiënt is een waarde die aangeeft hoe nauw twee gegevensreeksen verwant zijn.
Een veelgebruikt voorbeeld is het gewicht en de lengte van 10 personen in een groep. Als we de correlatiecoëfficiënt voor de lengte- en gewichtsgegevens voor deze mensen berekenen, krijgen we een waarde tussen -1 en 1.
Een waarde kleiner dan nul duidt op een negatieve correlatie, wat betekent dat als de lengte toeneemt, het gewicht afneemt, of als het gewicht toeneemt, de lengte afneemt.
En een waarde groter dan nul geeft een positieve correlatie aan, wat betekent dat als de lengte toeneemt, het gewicht toeneemt, en als de lengte afneemt, het gewicht afneemt.
Hoe dichter de waarde bij 1 ligt, hoe sterker de positieve correlatie. Dus een waarde van .8 zou erop wijzen dat de lengte- en gewichtsgegevens sterk gecorreleerd zijn.
Opmerking: er zijn verschillende soorten correlatiecoëfficiënten en statistieken, maar in deze tutorial zullen we kijken naar de meest voorkomende, namelijk de Pearson-correlatiecoëfficiëntLaten we nu eens kijken hoe we deze correlatiecoëfficiënt in Excel kunnen berekenen.
Correlatiecoëfficiënt berekenen in Excel
Zoals ik al zei, zijn er een aantal manieren waarop u de correlatiecoëfficiënt in Excel kunt berekenen.
De CORREL-formule gebruiken
CORREL is een statistische functie die werd geïntroduceerd in Excel 2007.
Stel je hebt een dataset zoals hieronder weergegeven waar je de correlatiecoëfficiënt tussen de lengte en het gewicht van 10 personen wilt berekenen.
Hieronder is de formule die dit zou doen:
=CORREL(B2:B12,C2:C12)
De bovenstaande CORREL-functie heeft twee argumenten - de reeks met de hoogtegegevenspunten en de reeks met de gewichtsgegevenspunten.
En dat is het!
Zodra u op enter drukt, voert Excel alle berekeningen in de back-end uit en krijgt u één enkel Pearson-correlatiecoëfficiëntnummer.
In ons voorbeeld is die waarde iets meer dan 0,5, wat aangeeft dat er een vrij sterke positieve correlatie is.
Deze methode kan het beste worden gebruikt als u twee reeksen hebt en u alleen de correlatiecoëfficiënt wilt.
Maar als u meerdere series heeft en u wilt de correlatiecoëfficiënt van al deze series weten, dan kunt u ook overwegen om het toolpakket voor gegevensanalyse in Excel te gebruiken (hierna beschreven)
Het toolpak voor gegevensanalyse gebruiken
Excel heeft een Data Analysis Toolpak dat kan worden gebruikt om snel verschillende statistische waarden te berekenen (inclusief het verkrijgen van de correlatiecoëfficiënt).
Maar de Data Analysis Toolpak is standaard uitgeschakeld in Excel. Dus de eerste stap zou zijn om de tool voor gegevensanalyse weer in te schakelen en die vervolgens te gebruiken om de Pearson-correlatiecoëfficiënt in Excel te berekenen.
Het Data Analysis Toolpak inschakelen
Hieronder staan de stappen om de Data Analysis Toolpak in Excel in te schakelen:
- Klik op het tabblad Bestand
- Klik op Opties
- Klik in het dialoogvenster Excel-opties dat wordt geopend op de optie Add-ins in het zijbalkvenster
- Selecteer in de vervolgkeuzelijst Beheren Excel-invoegtoepassingen
- Klik op Ga. Dit opent het dialoogvenster voor invoegtoepassingen
- Vink de optie Analyse Toolpak aan
- Klik op Ok
De bovenstaande stappen zouden een nieuwe groep toevoegen aan het tabblad Gegevens in het Excel-lint met de naam Analyse. Binnen deze groep zou u de optie Gegevensanalyse hebben
De correlatiecoëfficiënt berekenen met behulp van Data Analysis Toolpak
Nu u de analysetool weer beschikbaar hebt in het lint, laten we eens kijken hoe u de correlatiecoëfficiënt ermee kunt berekenen.
Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt weten wat de correlatie is tussen de drie reeksen (lengte en gewicht, lengte en inkomen, en gewicht en inkomen)
Hieronder staan de stappen om dit te doen:
- Klik op het tabblad Gegevens
- Klik in de groep Analyse op de optie Gegevensanalyse
- Klik in het dialoogvenster Gegevensanalyse dat wordt geopend op 'Correlatie'
- Klik OK. Dit opent het dialoogvenster Correlatie
- Selecteer voor het invoerbereik de drie series - inclusief de headers
- Zorg ervoor dat bij 'Gegroepeerd op' 'Kolommen' is geselecteerd
- Selecteer de optie - 'Label in eerste rij'. Dit zorgt ervoor dat de resulterende gegevens dezelfde kopteksten hebben en het zou een stuk gemakkelijker zijn om de resultaten te begrijpen
- Kies in de uitvoeropties waar u de resulterende tabel wilt hebben. Ik ga met cel G1 op hetzelfde werkblad aan de slag. U kunt er ook voor kiezen om uw resultaten in een nieuw werkblad of een nieuwe werkmap te krijgen
- Klik OK
Zodra u dit doet, zou Excel de correlatiecoëfficiënt voor alle reeksen berekenen en u een tabel geven zoals hieronder weergegeven:
Houd er rekening mee dat de resulterende tabel statisch is en niet wordt bijgewerkt als een van de gegevenspunten in uw tabel verandert. Bij een wijziging moet u de bovenstaande stappen opnieuw herhalen om een nieuwe tabel met correlatiecoëfficiënten te genereren.
Dit zijn dus twee snelle en gemakkelijke methoden om de correlatiecoëfficiënt in Excel te berekenen.
Ik hoop dat je deze tutorial nuttig vond!
