Maak een pijlvariantiegrafiek in Excel

In Excel is de pijlvariantie- of verschilgrafiek een variant van een gegroepeerde kolom- of staafgrafiek, waarbij pijlen omhoog of omlaag aangeven of de procentuele verandering tussen twee gegevensreeksen toeneemt of afneemt.

Als u bijvoorbeeld het verkoopverschil ten opzichte van het vorige jaar wilt weergeven, kunt u groene en rode pijlen gebruiken om de procentuele verandering weer te geven, zoals in de onderstaande schermafbeelding. In dit artikel leg ik uit hoe u dit complexe type grafiek in Excel maakt.

• Maak een pijlvariantie- of verschilgrafiek in Excel
• Maak een pijlvariantie- of verschilgrafiek in Excel met een handige functie
• Download het voorbeeldbestand voor de pijlvariantie- of verschilgrafiek
• Video: Maak een pijlvariantie- of verschilgrafiek in Excel

Maak een pijlvariantie- of verschilgrafiek in Excel

Om een pijlvariantiegrafiek te maken ter vergelijking van de procentuele verandering in 2020 ten opzichte van jaar 2019, moet u eerst enkele hulpkolommen invoegen om de basisgegevens te berekenen, zoals in de onderstaande schermafbeelding:

Maak eerst de gegevens voor de hulpkolommen

1. Typ in cel D2, naast de oorspronkelijke gegevens, de volgende formule en sleep deze vervolgens naar cel D10. Zie schermafbeelding:

2. Voer in cel E2 de volgende formule in en kopieer deze daarna naar cel E10 (zie schermafbeelding):

3.Typ de onderstaande formule in cel F2 en kopieer deze naar cel F10, zie schermafbeelding:

4. Voer daarna in cel G2 de onderstaande formule in en sleep deze naar cel G10 om de formule door te voeren—zie de schermafbeelding:

5. Voer daarna de volgende formule in cel H2 in en sleep deze naar cel H10. Zie schermafbeelding:

6. Voer daarna de onderstaande formule in cel I2 in en sleep de vulgreep naar cel I10, zie schermafbeelding:

Maak vervolgens de pijlvariantiegrafiek op basis van de gegevens uit de hulpkolommen

7. Nadat u de hulpgegevens hebt gemaakt, selecteert u de gegevens in kolom A, B, C, D en E. Klik vervolgens op Invoegen > Kolom- of staafgrafiek invoegen > Gegroepeerde kolommen. Zie de schermafbeelding:

8. Er wordt nu een gegroepeerde kolomgrafiek ingevoegd, zoals te zien is in de onderstaande schermafbeelding:

9. Klik op de grafiek en kies vervolgens in het contextmenu Gegevens selecteren, zie de schermafbeelding:

10. In het venster Gegevens selecteren-bron selecteert u in het vak Bereik van de reeksnaam de reeks Max Links en klikt u op de pijl-omhoogknop om deze gegevensreeks bovenaan te plaatsen, zie onderstaande demo:

11. Klik vervolgens op OK om het dialoogvenster te sluiten. Klik met de rechtermuisknop op de gegevensreeks Max Links en klik op de knop Grafiekelementen om het keuzemenu uit te klappen. Vink in dit menu Foutbalken aan, zie schermafbeelding:

12. Klik met de rechtermuisknop op de gegevensreeks Max Rechts en klik vervolgens op de knop Grafiekelementen om het keuzemenu uit te klappen. Vink in dit menu Foutbalken aan, zie schermafbeelding:

13. Klik met de rechtermuisknop op de Foutbalken Y Max Links en kies in het contextmenu Foutbalken opmaken, zie schermafbeelding:

14. In het geopende deelvenster Foutbalken opmaken configureert u onder het tabblad Opties foutbalk, in het gedeelte Verticale foutbalk, de volgende instellingen:

• Selecteer Minusonder het gedeelte Richting;
• Selecteer vervolgens Geen kapin het gedeelte Eindstijl;
• Selecteer daarna Aangepast in het gedeelte Foutmarge en klik vervolgens op de knop Waarde opgeven. In het venster Aangepaste foutbalken laat u het tekstvak Positieve foutwaarde leeg, selecteert u de gegevens uit de kolom Pijl omhoog in het tekstvak Negatieve foutwaarde en klikt u daarna op OK om het dialoogvenster te sluiten.

15. Laat het deelvenster Foutbalken opmaken open en selecteer vervolgens in de grafiek de Foutbalken Y Max Rechts. Configureer onder het tabblad Opties foutbalk, in het gedeelte Verticale foutbalk, de volgende instellingen:

• Selecteer Minusonder het gedeelte Richting;
• Selecteer vervolgens Geen kapin het gedeelte Eindstijl;
• Selecteer daarna Aangepast in het gedeelte Foutmarge en klik vervolgens op de knop Waarde opgeven. In het venster Aangepaste foutbalken laat u het tekstvak Positieve foutwaarde leeg, selecteert u de gegevens uit de kolom Pijl omlaag in het tekstvak Negatieve foutwaarde en klikt u daarna op OK om het dialoogvenster te sluiten.

16. Maak de balken van de gegevensreeksen Max Links en Max Rechts onzichtbaar. Klik met de rechtermuisknop op de balk Max Links en kies Gegevensreeks opmaken. In het geopende deelvenster Gegevensreeks opmaken selecteert u onder het tabblad Opvulling en lijn in het gedeelte Opvulling de optie Geen opvulling , zie schermafbeelding:

17. Voer dezelfde bewerking uit om de balken Max Rechts te verbergen, zie schermafbeelding:

18. Nadat u de gegevensreeksen Max Links Y-foutmarges en Max Rechts hebt verborgen, klikt u met de rechtermuisknop op Max Links Y-foutmarges en kiest u Foutmarges opmaken in het contextmenu. Voer in het deelvenster Foutmarges opmaken, onder het tabblad Opvulling & lijn, de volgende handelingen uit:

• Selecteer Doorgetrokken lijnen geef vervolgens een gewenste kleur op voor de pijlen omhoog via de keuzelijst Kleur;
• Pas daarna de lijndikte aan naar 1,25 ptzoals gewenst via het gedeelte Breedte;
• Selecteer vervolgens het pictogram Pijl uit de keuzelijst Pijl begin.

19. Houd het deelvenster Foutmarges opmaken nog geopend en klik vervolgens om de Max Rechts Y-foutmarges te selecteren. Voer in het deelvenster Foutmarges opmaken, onder het tabblad Opvulling & lijn, de volgende handelingen uit:

• Selecteer Doorgetrokken lijnen geef vervolgens een andere gewenste kleur op voor de pijlen omlaag via de keuzelijst Kleur;
• Pas daarna de lijndikte aan naar 1,25 ptzoals gewenst via het gedeelte Breedte;
• Selecteer vervolgens het pictogram Pijl uit de keuzelijst Pijl einde.

20. U krijgt nu de grafiek zoals weergegeven in de schermafbeelding hieronder:

21. Klik daarna met de rechtermuisknop op de gegevensbalk Max Links en kies Gegevensreeks opmaken in het contextmenu. In het geopende deelvenster Gegevensreeks opmaken, onder het tabblad Reeksopties, selecteert u de optie Secundaire as in de sectie Reeks plotten op. Klik vervolgens op de gegevensbalk Max Rechts en voer dezelfde handeling uit als in de onderstaande demo:

22. Klik daarna met de rechtermuisknop op een willekeurige balk in de grafiek en kies Gegevensreeks opmaken. In het deelvenster Gegevensreeks opmaken, onder het tabblad Reeksopties, voert u 0 % en 100 % in bij de velden Reeksoverlap en Ruimte tussen balken, respectievelijk.

Ga vervolgens in het deelvenster Gegevensreeks opmaken verder met het selecteren van Reeks „Jaar 2019” of Reeks „Jaar 2020” in de vervolgkeuzelijst Reeksopties, en voer 0 % en 100 % in bij de velden Reeksoverlap en Ruimte tussen balken, respectievelijk. Zie de onderstaande demo:

23. In deze stap voegt u gegevenslabels toe aan de grafiek. Selecteer de gegevensreeks Max Links door erop te klikken en klik vervolgens op Grafiekelementen. Schakel in het uitgevouwen keuzelijstvak Grafiekelementen de optie Gegevenslabels in. Zie schermafbeelding:

24. Klik daarna met de rechtermuisknop op de gegevenslabels en kies Opmaak Gegevenslabels. Zie schermafbeelding:

25. In het geopende deelvenster Opmaak Gegevenslabels, schakelt u onder het tabblad Labelopties de optie Waarde uit cellen in. Selecteer in het verschijnende dialoogvenster Gegevenslabels-bereik de gegevenscellen uit de kolom Bovenste gegevenslabel en klik op de knop OK. Zie schermafbeelding:

26. Blijf in het deelvenster Opmaak Gegevenslabels en schakel de opties Waarde en Leidingslijnen weergeven uit. U krijgt dan de onderstaande grafiek. Zie schermafbeelding:

27. Voeg vervolgens de onderste gegevenslabels toe: klik met de rechtermuisknop op de gegevensbalk Max Rechts en klik op Grafiekelementen om het keuzelijstvak uit te vouwen. Schakel in het lijstvak Grafiekelementen de optie Gegevenslabels in. Zie schermafbeelding:

28. Klik daarna met de rechtermuisknop op het nieuw toegevoegde gegevenslabel en kies Opmaak Gegevenslabels. In het geopende deelvenster Opmaak Gegevenslabels, onder het tabblad Labelopties, schakelt u de optie Waarde uit cellen in. Selecteer in het verschijnende dialoogvenster Gegevenslabels-bereik de gegevenscellen uit de kolom Onderste gegevenslabel en klik op de knop OK. Zie schermafbeelding:

29. Blijf in het deelvenster Opmaak gegevenslabels en schakel de opties Waarde en Leidingslijnen weergeven uit. De procentuele verandering is nu als gegevenslabels aan de grafiek toegevoegd. Zie schermafbeelding:

30. Verwijder ten slotte naar wens de onnodige grafiekelementen, zoals rasterlijnen, grafiektitels en de secundaire as. Zo krijgt u de volledige grafiek zoals hieronder in de schermafbeelding wordt weergegeven:

Maak een pijlvariantie- of verschilgrafiek in Excel met een handige functie

Download het voorbeeldbestand voor de pijlvariantie- of verschilgrafiek

Video: Maak een pijlvariantie- of verschilgrafiek in Excel