Wat is de correlatiecoëfficiënt?
De correlatiecoëfficiënt van een dataset is een statistisch getal dat aangeeft hoe sterk twee variabelen aan elkaar gerelateerd zijn. Men kan zeggen dat het het percentage is van de relatie tussen twee variabelen (x en y). Het kan niet groter zijn dan 100% en niet kleiner dan -100%.
De correlatiecoëfficiënt ligt tussen -1,0 en +1,0.
Een negatieve correlatiecoëfficiënt vertelt ons dat als een variabele toeneemt, de andere variabele afneemt. Een correlatie van -1,0 is een perfecte negatieve correlatie. Dit betekent dat als x met 1 eenheid toeneemt, y met 1 eenheid afneemt.
Een positieve correlatiecoëfficiënt vertelt ons dat als de waarde van de ene variabele toeneemt, de waarde van de andere variabele ook toeneemt. Dit betekent dat als x met 1 eenheid toeneemt, y ook met 1 eenheid toeneemt.
De correlatie van 0 zegt dat er geen enkele relatie is tussen twee variabelen.
De wiskundige formule van de correlatiecoëfficiënt is:
=Dekkingxy/(Soax*Soaja)
dekkingxy is de covariantie (steekproef of populatie) van de dataset.
Soax= Het is de standaarddeviatie (steekproef of populatie) van Xs.
Soay=Het is de standaarddeviatie (steekproef of populatie) van Ys.
Hoe de correlatiecoëfficiënt in Excel berekenen?
Als u de correlatie in Excel moet berekenen, hoeft u de wiskundige formule niet te gebruiken. U kunt deze methoden gebruiken:
- Correlatiecoëfficiënt berekenen met behulp van de COREL-functie.
- Correlatiecoëfficiënt berekenen met behulp van Analysis Toolpak.
Laten we een voorbeeld bekijken om te weten hoe u de correlatiecoëfficiënt in Excel kunt berekenen.
Voorbeeld van berekening van correlatiecoëfficiënt in Excel
Hier heb ik een voorbeeldgegevensset. We hebben xs in het bereik A2:A7 en ys in B2:B7.
We moeten de correlatiecoëfficiënt van xs en ys berekenen.
Excel CORREL-functie gebruiken
Syntaxis van de CORREL-functie:
| =CORREL(matrix1,matrix2) |
matrix1:Dit is de eerste reeks waarden (xs)
array2:Het is de tweede reeks waarden (ys).
Opmerking: de array 1 en array 2 moeten dezelfde grootte hebben.
Laten we de CORREL-functie gebruiken om de correlatiecoëfficiënt te krijgen. Schrijf deze formule in A10.
| =CORREL(A2:A7,B2:B7) |
We krijgen een correlatie van 0,356448487 of 36% tussen x en y.
Excel Analysis Toolpak gebruiken
Volg deze stappen om de correlatie te berekenen met behulp van analysetoolpak:
- Ga naar het tabblad Gegevens op het lint. In de meest linkse hoek vindt u de optie voor gegevensanalyse. Klik erop. Als u het niet kunt zien, moet u eerst het analysetoolpak installeren.
- Selecteer Correlatie uit de beschikbare opties.
- Selecteer het invoerbereik als A2:B7. Selecteer het uitvoerbereik waar u uw uitvoer wilt zien.
- Druk op de OK-knop. U heeft uw correlatiecoëfficiënt in het gewenste bereik. Het is exact dezelfde waarde als geretourneerd door de CORREL-functie.
Hoe wordt de correlatie berekend?
Om te begrijpen hoe we deze waarde krijgen, moeten we deze handmatig vinden. Dit zal onze twijfels wegnemen.
Zoals we weten is de correlatiecoëfficiënt:
=Dekkingxy/(Soax*Soaja)
Eerst moeten we de covariantie berekenen. We kunnen de COVERIACE.S-functie van Excel gebruiken om het te berekenen.
| =COVARIANTIE.S(A2:A7,B2:B7) |
Laten we vervolgens de standaarddeviatie van x en y berekenen met behulp van de STDEV.S-functie.
| =STDEV.S(A2:A7) |
| =STDEV.S(A2:A7) |
Schrijf nu in cel D10 deze formule.
| =D3/(D5*D7) |
Dit is gelijk aan =Covariantiexy/(Soax*Soaja). U kunt zien dat we exact dezelfde waarde krijgen als gegeven door de CORREL-functie. Nu weet je hoe we de correlatiecoëfficiënt in Excel hebben afgeleid.
Opmerking: In het bovenstaande voorbeeld hebben we COVARIANCE.S (covariantie van de steekproef) en STDEV.S (standaarddeviatie van de steekproef) gebruikt. De correlatiecoëfficiënt is hetzelfde als u COVARIANCE.P en STDEV.P gebruikt. Zolang ze allebei van dezelfde categorie zijn, is er geen verschil. Als u COVARIANCE.S (covariantie van de steekproef) en STDEV.P (standaarddeviatie van de populatie) gebruikt, zal het resultaat anders en onjuist zijn.
Dus ja jongens, dit is hoe we de correlatiecoëfficiënt in Excel kunnen berekenen. Ik hoop dat dit voldoende verklarend was om de correlatiecoëfficiënt te verklaren. U kunt nu uw eigen correlatiecoëfficiëntcalculator maken in Excel.
Bereken INTERCEPT in Excel
HELLING berekenen in Excel
Regressies in Excel
Een standaarddeviatiegrafiek maken
Beschrijvende statistieken in Microsoft Excel 2016
Hoe Excel NORM.VERD-functie te gebruiken
Pareto-diagram en analyse
Populaire artikels:
50 Excel-snelkoppeling om uw productiviteit te verhogen
De VERT.ZOEKEN-functie in Excel
AANTAL.ALS in Excel 2016
Hoe de SUMIF-functie in Excel te gebruiken?