Een spreidingsplot maken in Excel (XY-diagram)

Excel heeft een aantal handige grafiektypen die kunnen worden gebruikt om gegevens te plotten en analyses weer te geven.

Een veelvoorkomend scenario is dat u X- en Y-waarden in een grafiek in Excel wilt plotten en wilt laten zien hoe de twee waarden aan elkaar gerelateerd zijn.

Dit kan worden gedaan met behulp van een Spreidingsdiagram in Excel.

Als u bijvoorbeeld de gegevens voor Lengte (X-waarde) en Gewicht (Y-waarde) voor 20 studenten hebt, kunt u deze in een spreidingsdiagram plotten en ziet u hoe de gegevens met elkaar in verband staan.

Hieronder ziet u een voorbeeld van een Scatter Plot in Excel (ook wel de XY-grafiek genoemd):

In deze zelfstudie laat ik u zien hoe u een spreidingsplot in Excel maakt, de verschillende soorten spreidingsdiagrammen en hoe u deze grafieken kunt aanpassen.

Wat is een spreidingsdiagram en wanneer moet je het gebruiken?

Spreidingsdiagrammen worden gebruikt om de correlatie (gerelateerdheid) tussen twee gegevensvariabelen te begrijpen.

Een spreidingsplot heeft stippen waarbij elke stip twee waarden vertegenwoordigt (X-aswaarde en Y-aswaarde) en op basis van deze waarden worden deze stippen in de grafiek gepositioneerd.

Een realistisch voorbeeld hiervan zijn de marketingkosten en de inkomsten van een groep bedrijven in een specifieke branche.

Wanneer we deze gegevens (marketingkosten versus inkomsten) in een spreidingsdiagram plotten, kunnen we analyseren hoe sterk of losjes deze twee variabelen met elkaar zijn verbonden.

Een spreidingsplot maken in Excel

Stel dat u een dataset heeft zoals hieronder weergegeven en dat u een spreidingsplot wilt maken met deze gegevens.

Het doel van deze grafiek is om te zien of er een verband is tussen het marketingbudget en de omzet.

Voor het maken van een spreidingsdiagram is het belangrijk om beide waarden (van de twee variabelen die u in het spreidingsdiagram wilt plotten) in twee afzonderlijke kolommen te hebben.

De kolom aan de linkerkant (kolom Marketinguitgaven in ons voorbeeld) zou worden uitgezet op de X-as en de inkomsten zouden worden uitgezet op de Y-as.

Hieronder vindt u de stappen om een ​​spreidingsplot in Excel in te voegen:

1. Selecteer de kolommen met de gegevens (exclusief kolom A)

2. Klik op de optie Invoegen

3. Klik in de groep Grafiek op het pictogram Spreidingsdiagram invoegen

4. Klik op de optie 'Spreidingsdiagram' in de grafieken die worden weergegeven

De bovenstaande stappen zouden een spreidingsplot invoegen zoals hieronder in het werkblad wordt weergegeven.

De kolom aan de linkerkant (kolom Marketinguitgaven in ons voorbeeld) zou worden uitgezet op de X-as en de inkomsten zouden worden uitgezet op de Y-as. Het is het beste om de onafhankelijke statistiek in de linkerkolom te hebben en degene waarvoor u de correlatie moet vinden in de kolom aan de rechterkant.

Een trendlijn toevoegen aan het spreidingsdiagram

Hoewel ik later in deze zelfstudie meer manieren zal bespreken om de spreidingsgrafiek in Excel aan te passen, is een ding dat u direct na het bouwen van de spreidingsplot kunt doen, het toevoegen van een trendlijn.

Het helpt u snel een idee te krijgen of de gegevens positief of negatief gecorreleerd zijn, en hoe sterk/losjes gecorreleerd.

Hieronder vindt u de stappen om een ​​trendlijn toe te voegen aan een spreidingsdiagram in Excel:

1. Selecteer de Scatter plot (waar u de trendlijn wilt toevoegen)
2. Klik op het tabblad Grafiekontwerp. Dit is een contextueel tabblad dat alleen verschijnt als u het diagram selecteert

3. Klik in de groep Grafieklay-outs op de optie 'Grafiekelement toevoegen'

4. Ga naar de optie ‘Trendline’ en klik vervolgens op ‘Lineair’

De bovenstaande stappen zouden een lineaire trendlijn aan uw spreidingsdiagram toevoegen.

Alleen al door naar de trendlijn en de gegevenspunten te kijken die in het spreidingsdiagram zijn uitgezet, kunt u een idee krijgen of de gegevens positief gecorreleerd, negatief gecorreleerd of niet gecorreleerd zijn.

In ons voorbeeld zien we een positieve helling in de trendlijn die aangeeft dat de gegevens positief gecorreleerd zijn. Dit betekent dat wanneer de marketingkosten stijgen, de inkomsten stijgen en als de marketingkosten dalen, de inkomsten dalen.

In het geval dat de gegevens negatief gecorreleerd zijn, zou er een inverse relatie zijn. In dat geval, als de marketingkosten stijgen, dalen de inkomsten en vice versa.

En dan is er een geval waarin er geen correlatie is. In dit geval, wanneer de marketingkosten stijgen, kunnen hun inkomsten al dan niet toenemen.

Merk op dat de helling ons alleen vertelt of de gegevens positief of negatief gecorreleerd zijn. het vertelt ons niet hoe nauw het verwant is.

In ons voorbeeld kunnen we bijvoorbeeld door naar de trendlijn te kijken niet zeggen hoeveel de omzet zal stijgen wanneer de marketingkosten met 100% stijgen. Dit is iets dat kan worden berekend met behulp van de correlatiecoëfficiënt.

U kunt dat vinden met behulp van de onderstaande formule:

=CORREL(B2:B11,C2:C11)

De correlatiecoëfficiënt varieert tussen -1 en 1, waarbij 1 een perfect positieve correlatie aangeeft en -1 een perfect negatieve correlatie

In ons voorbeeld retourneert het 0,945, wat aangeeft dat deze twee variabelen een hoge positieve correlatie hebben.

Clusters identificeren met behulp van een spreidingsdiagram (praktische voorbeelden)

Een van de manieren waarop ik spreidingsdiagrammen gebruikte in mijn werk als financieel analist, was om clusters van gegevenspunten te identificeren die een soortgelijk gedrag vertonen.

Dit werkt meestal goed als je een diverse dataset hebt met minder algemene correlatie.

Stel dat je een dataset hebt zoals hieronder weergegeven, waar ik 20 bedrijven heb met hun omzet- en winstmargecijfers.

Wanneer ik een scatterplot voor deze gegevens maak, krijg ik iets zoals hieronder wordt weergegeven:

In deze grafiek kun je zien dat de gegevenspunten overal zijn en dat er een zeer lage correlatie is.

Hoewel dit diagram ons niet veel vertelt, kun je het op één manier gebruiken om clusters in de vier kwadranten in het diagram te identificeren.

Het gegevenspunt in het kwadrant linksonder zijn bijvoorbeeld die bedrijven waar de omzet laag is en de nettowinstmarge laag, en de bedrijven in het kwadrant rechtsonder zijn die bedrijven waar de omzet hoog is maar de nettowinstmarge laag.

Dit was vroeger een van de veelbesproken grafieken in de managementvergadering toen we potentiële klanten identificeerden op basis van hun financiële gegevens.

Verschillende soorten spreidingsplots in Excel

Afgezien van het normale spreidingsdiagram dat ik hierboven heb behandeld, kunt u ook de volgende typen spreidingsdiagrammen in Excel maken:

• Verstrooien met vloeiende lijnen
• Verstrooien met vloeiende lijnen en markeringen
• Verstrooiing met rechte lijnen
• Verstrooien met rechte lijnen en markeringen

Al deze vier bovenstaande spreidingsgrafieken zijn geschikt wanneer u minder gegevenspunten heeft en wanneer u twee reeksen in de grafiek plot.

Stel dat u de gegevens over marketingkosten versus inkomsten hebt, zoals hieronder weergegeven, en dat u een grafiek met spreiding met vloeiende lijnen wilt plotten.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de dataset (exclusief de kolom bedrijfsnaam)
2. Klik op het tabblad Invoegen
3. Klik in de groep Grafieken op de optie Spreidingsdiagram invoegen
4. Klik op Scatter met vloeiende lijnen en markeringen opties

Je zult iets zien zoals hieronder weergegeven.

Deze grafiek kan snel onleesbaar worden als u meer gegevenspunten heeft. Daarom wordt aanbevolen om dit alleen met minder datapunten te gebruiken.

Ik heb deze grafiek nooit in mijn werk gebruikt omdat ik denk dat deze geen zinvol inzicht geeft (omdat we er niet meer gegevenspunten op kunnen plotten).

Spreidingsdiagram aanpassen in Excel

Net als elke andere grafiek in Excel, kunt u de spreidingsplot eenvoudig aanpassen.

In dit gedeelte bespreek ik enkele van de aanpassingen die u kunt doen met een spreidingsdiagram in Excel:

Grafiekelementen toevoegen/verwijderen

Wanneer u op het spreidingsdiagram klikt, ziet u het pluspictogram rechtsboven in het diagram.

Wanneer u op dit pluspictogram klikt, worden opties weergegeven die u eenvoudig kunt toevoegen aan of verwijderen uit uw spreidingsdiagram.

Dit zijn de opties die je krijgt:

• assen
• As titel
• Grafiektitel
• Gegevenslabels
• Foutbalken
• Rasterlijnen
• Legende
• Trendlijnen

Sommige van deze opties zijn al aanwezig in uw grafiek en u kunt deze elementen verwijderen door op het selectievakje naast de optie te klikken (of deze toevoegen door op het selectievakje te klikken als dit nog niet is aangevinkt).

Als ik bijvoorbeeld de grafiektitel wil verwijderen, kan ik de optie eenvoudigweg uitschakelen en zou het weg zijn,

Als u meer controle nodig heeft, kunt u op de kleine zwarte pijl klikken die verschijnt wanneer u de cursor over een van de opties beweegt.

Als u erop klikt, krijgt u meer opties voor dat specifieke grafiekelement (deze openen als een venster aan de rechterkant).

Opmerking: alle schermafbeeldingen die ik u heb laten zien, zijn afkomstig uit een recente versie van Excel (Microsoft 365). Als u een oudere versie gebruikt, kunt u dezelfde opties krijgen als u met de rechtermuisknop op een van de grafiekelementen klikt en op de optie Opmaak klikt.

Laten we snel deze elementen doornemen en enkele van de geweldige aanpassingen die u kunt doen om diagrammen te spreiden die het gebruiken.

assen

Assen zijn de verticale en horizontale waarden die u direct naast de grafiek ziet.

Een van de handigste aanpassingen die u met assen kunt doen, is het aanpassen van de maximale en minimale waarde die kan worden weergegeven.

Om dit te wijzigen, klikt u met de rechtermuisknop op de assen in de grafiek en klikt u vervolgens op Assen opmaken. Hierdoor wordt het deelvenster As opmaken geopend.

In de optie As kunt u de minimum- en maximumgrenzen instellen, evenals de grote en kleine eenheden.

In de meeste gevallen kunt u dit op automatisch zetten, en Excel regelt dit op basis van de dataset. Maar als u specifieke waarden wilt, kunt u deze hier wijzigen.

Een voorbeeld kan zijn wanneer u niet wilt dat de minimumwaarde in de Y-as 0 is, maar iets anders (zeg 1000). Als u de ondergrens wijzigt in 1000, wordt de grafiek zo aangepast dat de minimumwaarde op de verticale as dan 1000 is.

As titel

De titel van de as is iets dat u kunt gebruiken om aan te geven wat de X- en Y-as vertegenwoordigen in het spreidingsdiagram in Excel.

In ons voorbeeld zou dit het netto-inkomen zijn voor de X-as en de marketingkosten voor de Y-as.

U kunt ervoor kiezen om geen astitels weer te geven, en u kunt deze verwijderen door het diagram te selecteren, op het pluspictogram te klikken en vervolgens het selectievakje voor Astitel uit te schakelen.

Om de tekst in de astitel te wijzigen, dubbelklikt u erop en typt u wat u maar wilt als de astitel.

U kunt de titelwaarde van de as ook aan een cel koppelen.

Als u bijvoorbeeld wilt dat de waarde in cel B1 wordt weergegeven in de titel van de verticale as, klikt u op het vak voor de titel van de as en voert u vervolgens =B1 in de formulebalk in. Dit toont de waarde in cel B1 in de astitel.

U kunt hetzelfde doen voor de titel van de horizontale as en deze koppelen aan een specifieke cel. Dit maakt deze titels dynamisch en als de celwaarde verandert, veranderen ook de astitels.

Als u meer controle nodig hebt over het opmaken van de astitels, klikt u op een as, klikt u met de rechtermuisknop en klikt u vervolgens op Astitel opmaken.

Met deze opties kunt u de vulling en rand van de titel wijzigen, de tekstkleur, uitlijning en rotatie wijzigen.

Grafiektitel

Net als bij Axis-titels kunt u de grafiektitel ook opmaken in een spreidingsplot in Excel.

Een grafiektitel wordt meestal gebruikt om te beschrijven waar de grafiek over gaat. Ik kan bijvoorbeeld 'Marketinguitgaven versus inkomsten' gebruiken als de titel van de grafiek.

Als u de grafiektitel niet wilt, kunt u erop klikken en deze verwijderen. En als u deze niet hebt, selecteert u de grafiek, klikt u op het pluspictogram en vinkt u vervolgens de optie Grafiektitel aan.

Om de tekst in de grafiektitel te bewerken, dubbelklikt u op het vak en typt u handmatig de gewenste tekst. En als u de grafiektitel Dynamisch wilt maken, kunt u op het titelvak klikken en vervolgens de celverwijzing of de formule in de formulebalk typen.

Om de grafiektitel op te maken, klikt u met de rechtermuisknop op de grafiektitel en klikt u vervolgens op de optie 'Grafiektitel opmaken'. Dit toont het deelvenster Grafiektitel opmaken aan de rechterkant.

Met deze opties kunt u de vulling en rand van de titel wijzigen, de tekstkleur, uitlijning en rotatie wijzigen.

Gegevenslabels

Gegevenslabels zijn standaard niet zichtbaar wanneer u een spreidingsplot maakt in Excel.

Maar deze kunt u eenvoudig toevoegen en opmaken.

Voeg de gegevenslabels toe aan het spreidingsdiagram, selecteer het diagram, klik op het pluspictogram aan de rechterkant en vink vervolgens de optie gegevenslabels aan.

Hiermee worden de gegevenslabels toegevoegd die de Y-aswaarde voor elk gegevenspunt in de spreidingsgrafiek weergeven.

Om de gegevenslabels op te maken, klikt u met de rechtermuisknop op een van de gegevenslabels en klikt u vervolgens op de optie 'Gegevenslabels opmaken'.

Hierdoor wordt het voormalige paneel met gegevenslabels aan de rechterkant geopend en kunt u deze aanpassen met behulp van verschillende opties die in het paneel worden vermeld.

Afgezien van de reguliere opmaak zoals vulling, rand, tekstkleur en uitlijning, krijgt u ook enkele extra labelopties die u kunt gebruiken.

In de opties 'Label bevat' kunt u ervoor kiezen om zowel de X-as als de Y-as waarde te tonen, in plaats van alleen de Y-as.

U kunt ook de optie 'Waarde uit cellen' kiezen. waarmee u gegevenslabels kunt hebben die zich in een kolom in het werkblad bevinden (het opent een dialoogvenster wanneer u deze optie selecteert en u kunt een celbereik kiezen waarvan de waarden in de gegevenslabels zouden worden weergegeven. In ons voorbeeld, Ik kan dit gebruiken om bedrijfsnamen in de gegevenslabels weer te geven

U kunt ook de positie van het label en het formaat waarin het wordt weergegeven aanpassen.

Foutbalken

Hoewel ik geen foutbalken heb gezien die worden gebruikt in spreidingsdiagrammen, heeft Excel wel een optie waarmee u deze foutbalken voor elk gegevenspunt in de spreidingsgrafiek in Excel kunt toevoegen.

Om de foutbalken toe te voegen, selecteert u de grafiek, klikt u op het pluspictogram en vinkt u de optie Foutbalken aan.

En als u deze foutbalken verder wilt aanpassen, klik dan met de rechtermuisknop op een van deze foutbalken en klik vervolgens op de optie 'Foutbalken opmaken'.

Hierdoor wordt het deelvenster 'Foutbalken opmaken' aan de rechterkant geopend, waar u dingen zoals kleur, richting en stijl van de foutbalken kunt aanpassen.

Rasterlijnen

Rasterlijnen zijn handig wanneer u veel gegevenspunten op uw kaart heeft, omdat de lezer hierdoor snel de positie van het gegevenspunt kan begrijpen.

Wanneer u een spreidingsdiagram maakt in Excel, zijn rasterlijnen standaard ingeschakeld.

U kunt deze rasterlijnen opmaken door met de rechtermuisknop op een van de rasterlijnen te klikken en op de optie Rasterlijnen opmaken te klikken.

Dit opent het deelvenster Rasterlijnen opmaken op de juiste manier. U kunt de opmaak wijzigen, zoals de kleur, dikte van de rasterlijn.

Naast de hoofdrasterlijnen die al zichtbaar zijn wanneer u het spreidingsdiagram maakt, kunt u ook kleine rasterlijnen toevoegen.

Tussen twee grote rasterlijnen kunt u een paar kleine rasterlijnen hebben die de leesbaarheid van de grafiek verder verbeteren voor het geval u veel gegevenspunten heeft.

Om kleine horizontale of verticale rasterlijnen toe te voegen, selecteert u de grafiek, klikt u op het pluspictogram en plaatst u de cursor op de optie Rasterlijnen.

Klik op de dikke zwarte pijl die daar verschijnt en vink vervolgens de optie 'Primair Minor Horizontaal' of 'Primair Minor Verticaal' aan om de kleine rasterlijnen toe te voegen

Legende

Als u meerdere reeksen hebt uitgezet in het spreidingsdiagram in Excel, kunt u een legenda gebruiken die aangeeft welk gegevenspunt naar welke reeks verwijst.

Standaard is er geen legenda wanneer u een spreidingsdiagram maakt in Excel.

Om een ​​legenda aan het spreidingsdiagram toe te voegen, selecteert u het diagram, klikt u op het pluspictogram en vinkt u de legenda-optie aan.

Om de legenda op te maken, klikt u met de rechtermuisknop op de legenda die verschijnt en klikt u vervolgens op de optie 'Legende opmaken'.

In het paneel Legenda opmaken dat wordt geopend, kunt u de vulkleur, rand en positie van de legenda in het diagram aanpassen.

Trendlijn

U kunt ook een trendlijn in het spreidingsdiagram toevoegen die aangeeft of er een positieve of negatieve correlatie is in de gegevensset.

Ik heb al besproken hoe u een trendlijn toevoegt aan een spreidingsdiagram in Excel in een van de bovenstaande secties.

3D Scatter Plot in Excel (worden best vermeden)

In tegenstelling tot een lijndiagram, kolomdiagram of vlakdiagram, is er geen ingebouwd 3D-spreidingsdiagram in Excel.

Hoewel u hiervoor invoegtoepassingen en hulpmiddelen van derden kunt gebruiken, kan ik geen extra voordeel bedenken dat u krijgt met een 3D-spreidingsdiagram in vergelijking met een gewoon 2D-spreidingsdiagram.

Ik raad zelfs aan om weg te blijven van elke vorm van 3D-grafiek, omdat deze de gegevens en delen in de grafiek verkeerd kan weergeven.

Dus dit is hoe u een spreidingsplot in Excel kunt maken en deze kunt aanpassen aan uw merk en vereisten.

Ik hoop dat je deze tutorial nuttig vond.