Correlatiematrix in Excel: een complete gids voor maken en interpreteren

Dankzij de ingebouwde functies en invoegtoepassingen van Excel is zelfs de meest geavanceerde analyse nu mogelijk in Excel. Als beginnend data science‑professional is het cruciaal om je vertrouwd te maken met Excel voor uiteenlopende analysetechnieken.

In deze tutorial introduceren we het statistische concept correlatie, de verschillende typen en de toepassingen ervan. Nadat we relevante ingebouwde functies en add-ins hebben besproken, nemen we voorbeeldgegevens om de Excel-correlatiematrix te maken, te visualiseren en te interpreteren.

Wat is een correlatiematrix?

Correlatie is een statistische maat die beschrijft in welke mate twee of meer variabelen met elkaar samenhangen. Het geeft de sterkte en richting van een relatie tussen variabelen aan.

Wanneer variabelen gecorreleerd zijn, betekent dit dat veranderingen in de ene variabele samenhangen met veranderingen in een andere — positief of negatief:

• Positieve correlatie: Wanneer twee variabelen samen toenemen of afnemen, zijn ze positief gecorreleerd. Zo zijn lengte en gewicht doorgaans positief gecorreleerd; als de lengte toeneemt, neemt het gewicht meestal ook toe.
• Negatieve correlatie: Wanneer de ene variabele toeneemt terwijl de andere afneemt. Bijvoorbeeld: de snelheid waarmee een voertuig rijdt en de tijd die nodig is om de bestemming te bereiken; als de snelheid toeneemt, neemt de benodigde tijd af.

Correlatiecoëfficiënten zijn numerieke maten die de sterkte en richting van deze relatie kwantificeren. Deze mate van correlatie kan met verschillende statistische hulpmiddelen worden gemeten, waarvan de Pearson-correlatiecoëfficiënt de meest gebruikelijke is.

De Pearson-coëfficiënt begrijpen

De Pearson-correlatiecoëfficiënt, vaak eenvoudigweg “Pearson’s r” genoemd, is een maat voor de lineaire correlatie tussen twee variabelen 𝑋 en 𝑌. Hij kwantificeert in welke mate de relatie tussen deze variabelen met een rechte lijn kan worden beschreven.

De Pearson-correlatiecoëfficiënt wordt berekend als de covariantie van de twee variabelen gedeeld door het product van hun standaardafwijkingen. Wiskundig wordt dit uitgedrukt als:

Merk op dat 𝑋‾ en 𝑌‾ respectievelijk de gemiddelden zijn van de variabelen 𝑋 en 𝑌.

De berekende coëfficiënt heeft een waarde tussen -1 en +1, waarbij:

• +1 een perfecte positieve lineaire relatie aangeeft: Naarmate de ene variabele toeneemt, neemt de andere variabele in perfect lineair verband toe.
• -1 een perfecte negatieve lineaire relatie aangeeft: Naarmate de ene variabele toeneemt, neemt de andere variabele in perfect lineair verband af.
• 0 geen lineaire correlatie aangeeft: Er is geen lineair verband tussen de variabelen.

Visueel ziet dat er zo uit voor de verschillende soorten correlaties:

Visuele weergave van correlaties (Bron)

Nu we termen als correlatie en correlatiecoëfficiënten begrijpen, kijken we hoe dit samenkomt in een correlatiematrix.

Een correlatiematrix is een tabel die de correlatiecoëfficiënten tussen meerdere variabelen weergeeft. Elke cel in de matrix vertegenwoordigt de correlatie tussen twee variabelen.

Deze matrix is een handig hulpmiddel om in statistische data-analyse de sterkte en richting van relaties tussen variabelen te bestuderen. Naast dit voor de hand liggende gebruik wordt de correlatiematrix toegepast in data science, financiën, marktonderzoek en meer.

Enkele van deze toepassingen zijn:

• In data science gebruiken we het om features (variabelen) te selecteren of uit te sluiten uit modellen op basis van hun onderlinge relaties. Sterk gecorreleerde features kunnen multicollineariteit veroorzaken in regressiemodellen, wat de resultaten kan vertekenen. Door deze correlaties te identificeren, verminderen we redundantie en verbeteren we de modelprestaties.
• In de financiële sector worden correlatiematrices gebruikt om te begrijpen hoe verschillende activa zich ten opzichte van elkaar bewegen. Dit is cruciaal voor portefeuillediversificatie en risicobeheer, omdat het helpt activa te selecteren die niet sterk gecorreleerd zijn, waardoor het risico mogelijk afneemt.
• Bedrijven gebruiken correlatiematrices om relaties te vinden tussen verschillend consumentengedrag, productbeoordelingen en demografische variabelen. Dit helpt bij het verbeteren van de targeting en positionering van producten. Een vergelijkbaar voorbeeld zien we later in deze tutorial.

Nu we het concept, de berekeningen en de toepassingen van correlatie begrijpen, duiken we in de implementatie in Excel.

Correlatiematrix: relevante functies en add-ins in Excel

Twee hulpmiddelen die we gebruiken om correlatiematrices in Excel te berekenen zijn de functie CORREL en de add-in Analysis ToolPak.

CORREL-functie

De CORREL-functie in Excel biedt een eenvoudige methode om de Pearson-correlatiecoëfficiënt tussen twee datasets te berekenen.

De syntaxis van de CORREL-functie is:

CORREL(array1, array2)

waarbij:

• array1: Dit is het celbereik dat de eerste dataset/kolom bevat.
• array2: Dit is het celbereik dat de tweede dataset/kolom bevat.

Beide arrays moeten evenveel elementen bevatten. Excel geeft de correlatiecoëfficiënt voor deze arrays terug, met waarden tussen -1 en +1.

Analysis ToolPak

Voor uitgebreidere statistische analyses, waaronder het genereren van een volledige correlatiematrix over meerdere variabelen, kan de Analysis ToolPak in Excel handig zijn.

De Analysis ToolPak is mogelijk niet standaard ingeschakeld in je Excel-bestand. Controleer of het pictogram “Data Analysis” zichtbaar is in de rechterhoek onder het tabblad “Data”, zoals hieronder weergegeven.

Analysis Toolpak in Excel.

Zie je het niet? Geen zorgen; volg de onderstaande stappen om de Analysis ToolPak in te schakelen.

• Klik in het menu op File en selecteer vervolgens Options.

Options selecteren vanuit het tabblad File.

• Selecteer in het dialoogvenster Excel Options de optie Add-ins.

Selecteer Add-ins in het dialoogvenster Excel Options.

• Selecteer in het vak Manage onderaan de weergave Excel Add-ins en klik op Go.

Excel-add-ins beheren.

• Vink in het vak Add-Ins Analysis ToolPak aan en klik vervolgens op OK.

Analysis Toolpak inschakelen.

Als je de instructies hebt gevolgd en alles correct hebt ingeschakeld, zou je het pictogram “Data Analysis” moeten zien onder het tabblad Data, in de groep Analysis.

Zoals je later zult zien, maakt Analysis ToolPak het berekenen van de correlatiematrix veel eenvoudiger en overzichtelijker.

Hoe maak je een correlatiematrix in Excel: een voorbeeld

Stel: je bent data-analist bij een toonaangevend consumentengoederenbedrijf met meerdere winkels door het hele land. Het bedrijf heeft gegevens verzameld over verkoop en klantdemografie.

Je doel is om de relaties tussen klantkenmerken en hun aankoopgedrag te begrijpen. Deze analyse is bedoeld om producten te vinden die vaak samen worden gekocht en te onderzoeken hoe verschillende demografische factoren de verkooptrends beïnvloeden.

Je hebt besloten een correlatiematrix te gebruiken voor de analyse. De volgende voorbeeldgegevens van 10 klanten zijn aan je aangeleverd in een Excel-bestand:

Voorbeeldgegevens van een toonaangevend consumentengoederenbedrijf.

De kolommen zijn:

• Customer ID: Unieke identificatie voor elke klant.
• Age: Leeftijd van de klant in jaren.
• Annual Income (K$): Het jaarlijkse inkomen van de klant in duizend dollar.
• Frequency of Visits (per month): Hoe vaak de klant de winkel elke maand bezoekt.
• Spend per Visit ($): Gemiddeld bedrag dat de klant per winkelbezoek uitgeeft.
• Electronics ($) / Clothing ($) / Groceries ($): Bedrag dat per bezoek aan verschillende productcategorieën wordt uitgegeven.

Correlaties handmatig met de CORREL-functie

Volg deze stappen om de Pearson-correlatiecoëfficiënt in Excel te berekenen:

• Stap 1: Selecteer de juiste cel die onderdeel uitmaakt van de correlatiematrix. Hier selecteren we B16.

Een cel selecteren om de correlatie te berekenen.

• Stap 2: Typ de volgende formule die de CORREL-functie gebruikt. We berekenen eerst de correlatie tussen Age en Annual Income.

=CORREL(B2:B11,C2:C11)

De formule intypen.

• Stap 3: Bevestig de cel en herhaal het proces voor alle andere combinaties van kolommen.

De correlatie berekenen.

Zoals je ziet, kan het handmatig berekenen van de correlatiewaarden voor elke kolomcombinatie tijdrovend worden, zeker wanneer het aantal kolommen toeneemt.

We kunnen ook de OFFSET-functie gebruiken om de celbereiken automatisch aan te passen en de coëfficiëntwaarden in de tabel te vullen.

Hoewel het belangrijk is te weten dat de CORREL-functie bestaat en nuttig is voor individuele correlaties, is voor een volledige correlatiematrix de Analysis ToolPak veel eenvoudiger en sneller, en dus aan te raden.

Correlatiematrix met Analysis ToolPak

Volg deze stappen om de correlatiematrix te maken:

• Stap 1: Klik op het pictogram Data Analysis onder het tabblad Data.

Klikken op het pictogram Data Analysis.

• Stap 2: Selecteer de optie “Correlation” en klik op “OK”.

De optie Correlation selecteren.

• Stap 3: Geef het invoerbereik op, inclusief de kolomnamen. Vink de optie “Labels in first row” aan. Kies als uitvoerbereik een cel waar je de resultaten wilt tonen, hier bijvoorbeeld $A$14. Klik ten slotte op OK.

Details invullen voor correlaties met de add-in Analysis Toolpak.

Je zou de correlatiematrix moeten zien zoals hieronder:

Gegevens met de bijbehorende correlatiematrix.

De correlatiewaarde van een kolom met zichzelf is 1, wat een perfecte correlatie met zichzelf betekent. De matrix bevat alle kolommen ook in de rijen, zodat alle combinaties van kolommen onderling worden gevormd. De diagonaal rechtsboven is leeg, omdat die precies overeenkomt met de combinaties linksonder.

We hebben de correlatiematrix in Excel succesvol gemaakt. Nu is het tijd om te begrijpen wat die waarden betekenen en deze getallen om te zetten in inzichten.

Hoe interpreteer je een correlatiematrix in Excel

Hoewel het interpreteren van de bovenstaande correlatiematrix niet heel moeilijk is, wordt het onoverzichtelijk wanneer het aantal kolommen toeneemt. Het is onmogelijk om elke combinatie na te lopen als er veel kolommen zijn.

We hebben een mechanisme nodig om snel de meest significante correlaties te identificeren tussen alle mogelijke combinaties in de correlatiematrix.

Voorwaardelijke opmaak van deze cellen in een correlatiematrix helpt bij de interpretatie door de focus te richten op de meest relevante cellen (donkerdere tinten) in de matrix.

Zo kleurcodeer je de correlatiematrix in Microsoft Excel:

• Stap 1: Markeer de correlatiematrix (alleen de cijfers) en klik op Conditional formatting onder het tabblad “Home”. Klik op “New Rule”, zoals hieronder te zien is.

Klikken op het pictogram “Conditional Formatting”.

• Stap 2: Na stap 1 verschijnt een dialoogvenster zoals hieronder. Selecteer eerst “Format all cells based on their values” en stel de opmaakstijl in op “3-Color Scale”. Drie kleuren zijn ideaal omdat ze positieve, negatieve en neutrale correlaties kunnen aanduiden. Stel ten slotte Type, Value en Color in zoals in het diagram.

Een nieuwe regel voor voorwaardelijke opmaak maken.

Je krijgt een resultaat zoals hieronder:

Voorbeeldgegevens met kleurgecodeerde correlatiematrix.

Dat is alles! We hebben kleuren toegevoegd om de correlatiematrix beter te visualiseren.

Zodra we de correlatiematrix voor de visuele interpretatie hebben ingekleurd, kunnen we het volgende afleiden uit de resultaten:

• Leeftijd is sterk positief gecorreleerd met het jaarinkomen; hoe ouder de klanten, hoe groter hun koopkracht.
• Het bedrag dat aan boodschappen wordt uitgegeven, is negatief gecorreleerd met alle variabelen, behalve met de bezoekfrequentie van klanten, die juist positief gecorreleerd is. Dit betekent dat klanten waarschijnlijk meer boodschappen kopen als ze de winkel vaker bezoeken.
• De categorieën kleding en elektronica hebben een sterke positieve correlatie, dus het kan een goed idee zijn om deze twee categorieën dicht bij elkaar te plaatsen om de verkoop te verhogen.
• De categorieën kleding en boodschappen hebben een negatieve correlatie, wat betekent dat je deze categorieën in fysieke winkels beter niet dicht bij elkaar kunt plaatsen.

De sleutel tot het interpreteren van de correlatiematrix is het observeren van de richting en de sterkte van de waarde in de matrix en terug te koppelen naar de bijbehorende kolommen.

Merk op dat we in onze uitleg nooit beweerden dat oudere klanten een hoger jaarinkomen krijgen vanwege hun hogere leeftijd, of dat hogere kledingverkoop lagere boodschappenverkoop veroorzaakt. Dat komt omdat correlatie geen causaliteit is, een veelvoorkomend misverstand.

We kunnen met een correlatieanalyse alleen het bestaan (of ontbreken) van een relatie tussen twee variabelen interpreteren, maar nooit dat de ene variabele veranderingen in de andere veroorzaakt. Om causaliteit vast te stellen, moeten we specifieke experimenten uitvoeren. Meer daarover lees je in onze tutorial over Correlatie vs. Causaliteit.

Tot slot

In deze tutorial hebben we het concept correlatie, de Pearson-coëfficiënt, de wiskundige formulering, de verschillende typen correlaties en hoe je ze herkent op basis van richting en sterkte van de correlatiecoëfficiënten behandeld.

Vervolgens hebben we de uitvoering behandeld: we namen voorbeeldgegevens, maakten een correlatiematrix, visualiseerden die met voorwaardelijke opmaak en interpreteerden de resultaten. Ook bespraken we misvattingen bij het interpreteren van de correlatiematrix.

Je hoeft hier niet te stoppen met leren! Als je geïnteresseerd bent in het implementeren van correlaties met andere programmeertalen, bekijk dan de volgende gidsen:

• R Correlation Tutorial
• Python Correlation Tutorial
• SQL Correlation Course

Wil je je technische basis verder versterken? Bekijk dan onze beginnersvriendelijke cursussen Kansrekening en Statistiek. Voel je je al zeker over de technische concepten maar wil je je praktische vaardigheden in Excel verbeteren, bekijk dan de Excel Fundamentals-track.