Hoe de correlatiecoëfficiënt met Excel te vinden

Een van de eenvoudigste en meest voorkomende statistische Excel-berekeningen Basisstatistieken berekenen in Excel: een beginnershandleiding Berekening van basisstatistieken in Excel: een beginnershandleiding Microsoft Excel kan statistieken maken! U kunt percentages, gemiddelden, standaarddeviatie, standaardfout en T-tests van studenten berekenen. Meer lezen wat je zou kunnen maken is correlatie. Het is een eenvoudige statistiek, maar het kan zeer informatief zijn als u wilt zien of twee variabelen gerelateerd zijn. Als u de juiste opdrachten kent, vindt u de correlatiecoëfficiënt in Excel uiterst eenvoudig.

We zullen eens kijken naar wat correlatie is om u een idee te geven van de informatie die het u geeft. Daarna gaan we verder met het vinden van de correlatiecoëfficiënt in Excel met behulp van twee methoden en een goede grafiek om naar correlaties te kijken. Ten slotte geef ik je een zeer snelle inleiding tot lineaire regressie, een andere statistische functie die handig zou kunnen zijn als je naar correlaties kijkt.

Wat is correlatie?

Laten we, voordat we beginnen, de definitie van correlatie bespreken. Het is een eenvoudige maatstaf van hoe dingen gerelateerd zijn. Laten we eens kijken naar twee variabelen die geen enkele correlatie hebben.

Deze twee variabelen (één geplot op de X-as, één op de Y) zijn volledig willekeurig en zijn niet nauw verwant.

De twee onderstaande variabelen zijn echter gecorreleerd:

Over het algemeen neemt de ene variabele toe, de andere ook. Dat is correlatie. (Merk op dat dit ook andersom kan zijn: als de ene omhoog gaat en de andere naar beneden gaat, is dat een negatieve correlatie.)

Correlatiecoëfficiënt begrijpen

De correlatiecoëfficiënt vertelt u hoe gerelateerd twee variabelen zijn. De coëfficiënt ligt tussen -1 en 1. Een correlatiecoëfficiënt van 0 betekent dat er absoluut geen correlatie is tussen twee variabelen. Dit is wat je zou moeten krijgen als je twee sets willekeurige getallen hebt.

Een coëfficiënt van -1 betekent dat u een perfecte negatieve correlatie hebt: als een variabele toeneemt, neemt de andere proportioneel af. Een coëfficiënt van 1 is een perfecte positieve correlatie: als een variabele toeneemt, neemt de andere ook evenredig toe.

Elk getal ertussen staat voor een schaal. Een correlatie van .5 is bijvoorbeeld een matige positieve correlatie.

Zoals u in de onderstaande afbeelding kunt zien, zoekt correlatie alleen naar een lineaire relatie. Twee variabelen kunnen op een andere manier sterk gerelateerd zijn en hebben nog steeds een correlatiecoëfficiënt van nul:

Correlatiecoëfficiënt in Excel vinden met CORREL

Er is een ingebouwde functie voor correlatie in Excel. De CORREL-functie heeft een zeer eenvoudige syntaxis:

= CORREL (array1, array2)

array1 is uw eerste groep getallen en array2 is de tweede groep. Excel spuwt een getal uit, en dat is uw correlatiecoëfficiënt. Laten we een voorbeeld bekijken.

In deze spreadsheet hebben we een lijst met auto's, met model en jaar, en hun waarden. Ik heb de functie CORREL gebruikt om te zien of het modeljaar en de waarde gerelateerd waren:

Er is een zeer zwakke positieve correlatie; dus naarmate het jaar stijgt, neemt ook de waarde van het voertuig toe. Maar niet erg veel.

Grafische correlaties

Wanneer u correlaties uitvoert, is het een goed idee om een ​​scatterplot te gebruiken om een ​​visueel inzicht te krijgen in hoe uw datasets gerelateerd zijn. Ga naar Kaarten> Scatter om te zien hoe uw gegevens eruitzien:

U kunt zien dat in deze gegevens het jaar van de auto de waarde niet erg beïnvloedt. Er is een gering positieve trend, maar het is zwak. Dat is wat we vonden met onze CORREL-functie.

Een ander handig element in een scatterplot is een trendlijn, die er als volgt uitziet:

De trendlijn kan handig zijn als u een correlatie duidelijk wilt maken in uw scatterplot. Klik in Windows op Hulpmiddelen voor grafieken> Ontwerp> Grafiekelement toevoegen en selecteer trendlijn. Op een Mac moet je naar Grafiek lay-out of Grafiekontwerp, afhankelijk van de editie van Excel.

En vergeet niet onze gids voor het maken van geweldige grafieken in Excel te raadplegen. Krachtige grafieken en grafieken maken in Microsoft Excel Krachtige grafieken en grafieken maken in Microsoft Excel Een goede grafiek kan het verschil maken tussen uw punt laten zien of verlaten iedereen dommelt in. We laten u zien hoe u krachtige grafieken in Microsoft Excel maakt die uw publiek zullen aanspreken en informeren. Lees meer voordat u bevindingen presenteert!

Meerdere variabelen correleren met het Data Analysis Toolpak

Als u veel verschillende sets getallen hebt en u wilt correlaties tussen deze getallen vinden, moet u de CORREL-functie uitvoeren voor elke combinatie. Met de Data Analysis Toolpak kunt u echter een aantal datasets selecteren en zien waar de correlaties liggen.

Weet u niet zeker of u de Data Analysis Toolpak hebt? Bekijk onze uitleg van de basisbegrippen Basisinformatieanalyse uitvoeren in Excel Basisgegevensanalyse uitvoeren in Excel Excel is niet bedoeld voor gegevensanalyse, maar kan nog steeds met statistieken overweg. We laten u zien hoe u de Data Analysis Toolpak-invoegtoepassing gebruikt om Excel-statistieken uit te voeren. Lees meer om te downloaden en onder de knie te krijgen.

Ga naar om de Toolpak te activeren Gegevens> Gegevensanalyse. Je ziet een lijst met keuzes:

kiezen Correlatie en druk op OK.

Selecteer in het resulterende venster al uw gegevenssets in de Invoerbereik en vertel Excel waar u uw resultaten wilt plaatsen:

Dit is wat je krijgt als je raakt OK:

In de bovenstaande afbeelding hebben we correlaties uitgevoerd op vier verschillende gegevenssets: het jaar, de wereldpopulatie en twee sets willekeurige getallen.

De correlatie van elke dataset met zichzelf is 1. Het jaar en de wereldbevolking hebben een extreem sterke correlatie, terwijl er elders zeer zwakke correlaties zijn, zoals we zouden verwachten met willekeurige getallen.

Correlatie versus lineaire regressie in Excel

Correlatie is een eenvoudige maatregel: hoe nauw gerelateerd zijn twee variabelen? Deze maat heeft echter geen voorspellende of oorzakelijke waarde. Alleen omdat twee variabelen gecorreleerd zijn, wil dat nog niet zeggen dat de ene veranderingen in de andere veroorzaakt. Dat is een cruciaal iets om te begrijpen over correlatie.

Als u geïnteresseerd bent in het claimen van causaliteit, moet u lineaire regressie gebruiken. U kunt hier ook toegang toe krijgen via het Data Analysis Toolpak. (Dit artikel gaat niet in op de details van hoe lineaire regressie werkt, maar er zijn veel gratis statistische bronnen. Leer gratis statistieken met deze 6 bronnen Leer gratis statistieken met deze 6 bronnen Statistieken hebben de reputatie van een onderwerp dat moeilijk te begrijpen is Maar door te leren van de juiste bron kunt u de enquêteresultaten, verkiezingsrapporten en uw klassestoewijzingen in een mum van tijd begrijpen. Meer informatie over de basisprincipes.)

Open de Data Analysis Toolpak, selecteer regressie, en klik OK.

Vul de X- en Y-bereiken in (de X-waarde is de verklarende variabele en de Y-waarde is de waarde die u probeert te voorspellen). Selecteer vervolgens waar u uw uitvoer naartoe wilt sturen en klik op OK nog een keer.

Het nummer waarop u hier wilt focussen, is de p-waarde voor uw verklarende variabele:

Als het minder dan 0,05 is, hebt u een sterk argument dat wijzigingen in uw X-variabele wijzigingen in uw Y-variabele veroorzaken. In de bovenstaande afbeelding hebben we laten zien dat het jaar een significante voorspeller is van de wereldbevolking.

Lineaire regressie is ook nuttig omdat het naar meerdere waarden kan kijken. Hier hebben we regressie gebruikt om te zien of jaar en populatie significante voorspellers zijn van de prijs van ruwe olie:

Beide p-waarden zijn minder dan 0,05, dus we kunnen concluderen dat zowel het jaar als de wereldbevolking significante voorspellers zijn van de prijs van ruwe olie. (Hoewel sterke correlaties tussen X-variabelen hun eigen problemen kunnen veroorzaken.)

Nogmaals, dit is een zeer simplistische uitleg van lineaire regressie, en als je geïnteresseerd bent in causaliteit, zou je wat stats tutorials moeten lezen.

Maar nu hebt u een idee van hoe verder te gaan dan eenvoudige correlatie als u op zoek bent naar meer statistische informatie!

Haal betere gegevens uit Excel

Als u basisstatistische functies in Excel begrijpt, kunt u veel nuttigere informatie uit uw gegevens halen. Correlatie is een eenvoudige maatregel, maar het kan een grote hulp zijn wanneer u claims probeert te maken over de getallen in uw spreadsheet.

Natuurlijk kunt u nog tal van andere meer gecompliceerde maatregelen uitvoeren. Maar tenzij u vertrouwd bent met statistieken, wilt u de basis leren 8 Tips voor snel leren 8 Tips voor het snel leren van Excel Niet zo comfortabel met Excel als u zou willen? Begin met eenvoudige tips voor het toevoegen van formules en het beheren van gegevens. Volg deze gids en u bent zo snel mogelijk op de hoogte. Lees verder .

Gebruik je de correlatiefunctie van Excel regelmatig? Over welke andere statistische functies zou u willen leren?

Ontdek meer over: Microsoft Excel.