Maak een pijlvariantiegrafiek in Excel

In Excel is de pijlvariantie of verschilgrafiek een type gegroepeerde kolom- of staafgrafiek met omhoog- of omlaagwijzende pijlen om de procentuele toename of afname van twee datasets aan te geven.

Bijvoorbeeld, als u de verkoopvariantie ten opzichte van het vorige jaar wilt weergeven, kunt u groene en rode pijlen gebruiken om de procentuele verandering weer te geven zoals in onderstaande schermafbeelding. In dit artikel zal ik uitleggen 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 pijlvariantie- of verschilgrafiek
• Video: Maak een pijlvariantie- of verschilgrafiek in Excel

Maak een pijlvariantie- of verschilgrafiek in Excel

Om een pijlvariantiegrafiek te maken om de procentuele verandering van 2020 te vergelijken met die van 2019, moet u eerst enkele hulpkolommen invoegen om de basisgegevens te berekenen, zoals in onderstaande schermafbeelding te zien is:

Maak eerst de gegevens voor de hulpkolommen

1. Typ in cel D2 naast de originele gegevens de volgende formule en sleep de formule naar cel D10, zie onderstaande schermafbeelding:

2. Typ in cel E2 de volgende formule en kopieer de formule naar cel E10, zie onderstaande schermafbeelding:

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

4. Typ vervolgens in cel G2 de volgende formule en sleep deze naar cel G10 om de formule in te vullen, zie onderstaande schermafbeelding:

5. Voer vervolgens de volgende formule in cel H2 in en sleep de formule naar cel H10, zie onderstaande schermafbeelding:

6. Typ vervolgens de volgende formule in cel I2 en sleep de vulgreep naar beneden naar cel I10, zie onderstaande schermafbeelding:

Tweede, maak een pijlvariantiegrafiek op basis van de gegevens uit de hulpkolommen

7. Nadat u de hulpgedata heeft gemaakt, selecteert u de gegevens in kolom A, B, C, D, E en klikt u op Invoegen > Invoegen Kolom- of Staafdiagram > Gegroepeerde Kolom, zie onderstaande schermafbeelding:

8. Er wordt een gegroepeerd kolomdiagram ingevoegd zoals in onderstaande schermafbeelding te zien is:

9. Klik vervolgens op de grafiek om deze te selecteren en kies Gegevens selecteren uit het contextmenu, zie onderstaande schermafbeelding:

10. In het pop-upvenster Selecteer Gegevensbron, in het vak Legenda-items (Reeksen), selecteer de reeks Max Links en klik vervolgens op de pijl omhoog om deze gegevensreeks naar boven te verplaatsen, zie onderstaande demo:

11. Klik vervolgens op de knop OK om het dialoogvenster te sluiten en klik met de rechtermuisknop op de gegevensreeks Max Links, klik op de knop Grafiekelementen om de lijst uit te vouwen, vink in de lijst Foutbalken aan, zie onderstaande schermafbeelding:

12. Klik met de rechtermuisknop op de gegevensreeks Max Rechts en klik op de knop Grafiekelementen om de lijst uit te vouwen, vink in de lijst Foutbalken aan, zie onderstaande schermafbeelding:

13. Klik vervolgens met de rechtermuisknop op de Y-foutbalken Max Links en kies Formaat Foutbalken uit het contextmenu, zie onderstaande schermafbeelding:

14. Open vervolgens het venster Formaat Foutbalken, onder het tabblad Foutbalkopties, in de sectie Verticale Foutbalk, configureer de volgende acties:

• Selecteer de optie Min onder de sectie Richting;
• Selecteer vervolgens Geen Kapje uit de sectie Einde Stijl;
• Selecteer vervolgens Aangepast uit de sectie Foutwaarde, klik op de knop Waarde Opgeven, in het pop-upvenster Aangepaste Foutbalken, laat de tekstvak Positieve Foutwaarde leeg en selecteer de gegevens uit de kolom Omhoog Pijl in het tekstvak Negatieve Foutwaarde en klik op OK om het dialoogvenster te sluiten.

15. Laat het venster Formaat Foutbalken open en klik vervolgens op de Y-foutbalken Max Rechts in de grafiek, onder het tabblad Foutbalkopties, in de sectie Verticale Foutbalk, configureer de volgende acties:

• Selecteer de optie Min onder de sectie Richting;
• Selecteer vervolgens Geen Kapje uit de sectie Einde Stijl;
• Selecteer vervolgens Aangepast uit de sectie Foutwaarde, klik op de knop Waarde Opgeven, in het pop-upvenster Aangepaste Foutbalken, laat de tekstvak Positieve Foutwaarde leeg en selecteer de gegevens uit de kolom Omlaag Pijl in het tekstvak Negatieve Foutwaarde en klik op OK om het dialoogvenster te sluiten.

16. Vervolgens moet u de balken van de gegevensreeksen Max Links en Max Rechts onzichtbaar maken, klik met de rechtermuisknop op de balk Max Links en kies de optie Formaat Gegevensreeks, in het venster Formaat Gegevensreeks, onder het tabblad Opvulling & Lijn, selecteer Geen opvulling uit de sectie Opvulling, zie onderstaande schermafbeelding:

Voer dezelfde actie uit om de balken van de gegevensreeks Max Rechts te verbergen, zie onderstaande schermafbeelding:

18. Nadat u de balken van de gegevensreeksen Max Links en Max Rechts hebt verborgen, klik nu met de rechtermuisknop op de Y-foutbalken Max Links en kies Formaat Foutbalken uit het contextmenu, in het venster Formaat Foutbalken, onder het tabblad Opvulling & Lijn, voer de volgende acties uit:

• Selecteer de optie Vaste lijn en specificeer vervolgens een kleur naar keuze voor de omhoogpijlen uit de vervolgkeuzelijst Kleur;
• Pas vervolgens de lijnbreedte aan naar 1,25 pt zoals u nodig hebt uit de sectie Breedte;
• Selecteer vervolgens het pictogram Pijl uit de vervolgkeuzelijst Begin Pijltype.

19. Laat het venster Formaat Foutbalken open en klik vervolgens op de Y-foutbalken Max Rechts, in het venster Formaat Foutbalken, onder het tabblad Opvulling & Lijn, voer de volgende acties uit:

• Selecteer de optie Vaste lijn en specificeer vervolgens een andere kleur naar keuze voor de omlaagpijlen uit de vervolgkeuzelijst Kleur;
• Pas vervolgens de lijnbreedte aan naar 1,25 pt zoals u nodig hebt uit de sectie Breedte;
• Selecteer vervolgens het pictogram Pijl uit de vervolgkeuzelijst Einde Pijltype.

20. U krijgt nu de grafiek zoals in onderstaande schermafbeelding te zien is:

21. Klik vervolgens met de rechtermuisknop op de balk Max Links en kies Formaat Gegevensreeks uit het contextmenu, in het venster Formaat Gegevensreeks, onder het tabblad Reeks Opties, selecteer de optie Secundaire As uit de sectie Reeks Weergeven Op, klik vervolgens op de balk Max Rechts en voer dezelfde actie uit zoals in onderstaande demo te zien is:

22. Klik vervolgens met de rechtermuisknop op een willekeurige balk in de grafiek en kies Formaat Gegevensreeks, in het venster Formaat Gegevensreeks, onder het tabblad Reeks Opties, voer 0% en 100% in de vakken Reeksoverlap en Tussenruimte Breedte in.

Ga vervolgens in het venster Formaat Gegevensreeks door met het selecteren van Reeks “Jaar 2019” of Reeks “Jaar 2020” uit de vervolgkeuzelijst Reeks Opties en voer 0% en 100% in de vakken Reeksoverlap en Tussenruimte Breedte in. Zie onderstaande demo:

23. In deze stap kunt u de gegevenslabels voor de grafiek toevoegen, klik om de gegevensreeks Max Links te selecteren en klik vervolgens op Grafiekelementen, in de uitgebreide lijst Grafiekelementen, vink de optie Gegevenslabels aan, zie onderstaande schermafbeelding:

24. Klik vervolgens met de rechtermuisknop op de gegevenslabels en kies Formaat Gegevenslabels, zie onderstaande schermafbeelding:

25. In het venster Formaat Gegevenslabels, onder het tabblad Labelopties, vink de optie Waarde uit Cellen aan en in het pop-upvenster Gegevenslabels Bereik, selecteer de gegevenscellen uit de kolom Omhoog Gegevenslabel en klik op OK, zie onderstaande schermafbeelding:

26. Blijf in het venster Formaat Gegevenslabels en vink de opties Waarde en Toon Leiderslijnen uit en u krijgt de onderstaande grafiek, zie onderstaande schermafbeelding:

27. Voeg vervolgens de omlaag gegevenslabels toe, klik met de rechtermuisknop op de balk Max Rechts en klik op Grafiekelementen om de lijst uit te vouwen, in de lijst Grafiekelementen, vink de optie Gegevenslabels aan, zie onderstaande schermafbeelding:

28. Klik vervolgens met de rechtermuisknop op de nieuw toegevoegde gegevenslabels en kies Formaat Gegevenslabels, in het venster Formaat Gegevenslabels, onder het tabblad Labelopties, vink de optie Waarde uit Cellen aan en in het pop-upvenster Gegevenslabels Bereik, selecteer de gegevenscellen uit de kolom Omlaag Gegevenslabel en klik op OK, zie onderstaande schermafbeelding:

29. Blijf in het venster Formaat Gegevenslabels en vink de opties Waarde en Toon Leiderslijnen uit en de procentuele veranderingsgegevenslabels zijn toegevoegd aan de grafiek, zie onderstaande schermafbeelding:

30. Verwijder tot slot de onnodige grafikelementen naar behoefte, zoals rasterlijnen, grafiektitels, secundaire assen en u krijgt de voltooide grafiek zoals in onderstaande schermafbeelding te zien is:

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

Download het voorbeeldbestand voor pijlvariantie- of verschilgrafiek

Video: Maak een pijlvariantie- of verschilgrafiek in Excel