Maak een kolomdiagram met procentuele verandering in Excel

In Excel kunt u een eenvoudig kolomdiagram maken om de datatrends normaal te bekijken. Om de gegevens intuïtiever te laten lijken en de verschillen tussen de jaren weer te geven, kunt u een kolomdiagram met procentuele verandering tussen elke kolom maken zoals in onderstaande schermafbeelding te zien is. In dit type grafiek duiden de pijlen omhoog de toegenomen percentages aan van het latere jaar ten opzichte van het vorige jaar, terwijl de pijlen omlaag de afgenomen percentages aangeven.

In dit artikel zullen we uitleggen hoe u een kolomdiagram maakt dat de procentuele verandering tussen de kolommen in Excel weergeeft.

• Maak een kolomdiagram met procentuele verandering door gebruik te maken van foutbalken
• Maak een kolomdiagram met procentuele verandering door gebruik te maken van pijlen omhoog en omlaag
• Maak een kolomdiagram met procentuele verandering door gebruik te maken van een krachtige functie
• Download Voorbeeldbestand Kolomdiagram Met Procentuele Verandering

Maak een kolomdiagram met procentuele verandering door gebruik te maken van foutbalken

Om met behulp van foutbalken een kolomdiagram met procentuele verandering te maken, moet u enkele hulpcolumnen invoegen zoals hieronder weergegeven, en vervolgens het diagram maken op basis van de hulpgegevens. Volg deze stappen:

Maak eerst de hulpcolumnen

1. Typ in cel C2 naast de originele gegevens de volgende formule en sleep de formule naar cel C10, zie screenshot:

2. Voer vervolgens onderstaande formule in cel D2 in en sleep en kopieer de formule naar cel D10, zie screenshot:

3. Voer vervolgens in cel E2 de volgende formule in en sleep de vulgreep naar beneden naar cel E9, zie screenshot:

4. Voer vervolgens de onderstaande formule in cel F2 in en sleep de vulgreep naar beneden naar cel F9, zie screenshot:

5. Voer vervolgens de volgende formule in cel G2 in en sleep deze naar cel G9, zie screenshot:

6. Typ vervolgens de volgende formule in cel H2 in en kopieer deze formule naar cel H9, zie screenshot:

7. Voeg nu de laatste hulpcelkolom in, pas de onderstaande formule toe in cel I2 en sleep deze naar cel I9, en formatteer de decimale resultaten naar Percentage Stijl. Zie screenshot:

Tweede, Maak het diagram op basis van de hulpcolumnen

8. Nadat u de hulpgegevens hebt gemaakt, selecteert u de gegevens in kolom C, kolom D en kolom E, en klik vervolgens op Invoegen > Invoegen Kolom of Staafdiagram > Geclusterd Kolomdiagram, zie screenshot:

9. En dan is er een kolomdiagram ingevoegd, waarbij u de niet-gebruikte elementen van het diagram kunt verwijderen, zoals de titel van het diagram, legende of rasterlijnen, zie screenshots:

10. Klik vervolgens op de kolombalk die de onzichtbare gegevens weergeeft, en klik vervolgens op de knop Diagram Elementen om de lijst met diagram-elementen uit te vouwen, en kies Foutbalken > Meer Opties, zie screenshot:

11. In het geopende venster Formaat Foutbalken, onder het tabblad Foutbalk Opties:

• Selecteer Beide in de sectie Richting;
• Kies Dop in de sectie Einde Stijl;
• Selecteer Aangepast in de sectie Fout Hoeveelheid, en klik vervolgens op Waarde Specificeren, in het volgende dialoogvenster Aangepaste Foutbalken, selecteer de gegevens uit cel G2:G10 in het vak Positieve Foutwaarde, selecteer vervolgens cellen H2:H10 in het vak Negatieve Foutwaarde.

12. Klik vervolgens op de knop OK, en u krijgt het diagram zoals in onderstaande schermafbeelding te zien is:

13. Klik nu met de rechtermuisknop op de kolombalk die de Order 1-gegevens weergeeft, en kies Formaat Gegevensreeks uit het contextmenu, zie screenshot:

14. In het geopende venster Formaat Gegevensreeks, onder het tabblad Reeks Opties, wijzig de waarden in de secties Reeksoverlap en Tussenruimte Breedte naar 0%, zie screenshot:

15. Verberg nu de onzichtbare databalk, klik met de rechtermuisknop op een ervan, in het pop-up contextmenu, kies Geen Opvulling in de Opvulling sectie, zie screenshot:

Met de onzichtbare databalk nog steeds geselecteerd, klik op de knop Diagram Elementen, kies Gegevenslabels > Meer Opties, zie screenshot:

17. In het venster Formaat Gegevenslabels, onder het tabblad Label Opties, vink Waarde Uit Cellen aan, en in het pop-up venster Gegevenslabel Bereik, selecteer het variantiegegevensbereik I2:I9, zie screenshots:

18. Klik vervolgens op OK, blijf in het venster Formaat Gegevenslabels:

• Vink de opties Waarde en Toon Leiderslijnen uit onder de sectie Label Opties;
• Specificeer vervolgens de labelpositie als Buiten Einde in de sectie Label Positie.

19. Nu kunt u zien dat de gegevenslabels aan het diagram zijn toegevoegd, kunt u de negatieve percentage labels instellen op Binnen Einde, en de gegevenslabels naar wens formatteren, zie screenshot:

Maak een kolomdiagram met procentuele verandering door gebruik te maken van pijlen omhoog en omlaag

Soms wilt u misschien wat pijlen gebruiken om de foutbalken te vervangen, als de gegevens in het volgende jaar toenemen, wordt een pijl omhoog weergegeven, als de gegevens in het volgende jaar afnemen, wordt een pijl omlaag weergegeven. Tegelijkertijd zullen de gegevenslabels en de pijlen dynamisch veranderen zoals de gegevens veranderen zoals in onderstaande demo te zien is.

Om dit type diagram te maken, moet u twee delen hulpgegevens invoegen zoals in onderstaande schermafbeelding te zien is. Het eerste deel berekent de variantie en het procentuele verschil zoals het blauwe deel weergeeft, het tweede deel wordt gebruikt voor aangepaste foutbalken voor zowel toename als afname zoals het rode deel weergeeft.

Eerst, maak de hulpcolumnen

1. Om het eerste deel van de hulpgegevens in te voegen, past u de onderstaande formules toe:

2. Pas vervolgens de volgende formules toe om het tweede deel van de hulpgegevens te maken:

Tweede, Maak het diagram op basis van de hulpcolumnen

3. Selecteer de gegevens in kolom C en kolom D, klik vervolgens op Invoegen > Invoegen Kolom of Staafdiagram > Geclusterd Kolomdiagram om een kolomdiagram in te voegen zoals in onderstaande schermafbeelding te zien is:

4. Druk vervolgens op Ctrl + C om de gegevens in kolom G, kolom H, kolom I te kopiëren, en klik vervolgens op het diagram, zie screenshot:

5. Nadat u het diagram heeft geselecteerd, klikt u op Start > Plakken > Plakken Speciaal, in het dialoogvenster Plakken Speciaal, selecteer Nieuwe reeks, Kolomopties, en vink vervolgens Reeksnaam in Eerste Rij en Categorieën (X Labels) in Eerste Kolom opties aan, zie screenshots:

6. En dan krijgt u een diagram zoals in onderstaande schermafbeelding te zien is:

7. Klik met de rechtermuisknop op een kolombalk in het diagram, en kies Vervang Reeks Diagramtype uit het contextmenu, zie screenshot:

8. In het dialoogvenster Diagramtype Wijzigen, verander beide Toename en Afname naar Spreidingsgrafiek, vink vervolgens het vak Secundaire As uit voor elk in de lijst Kies het diagramtype en as voor uw reeks. Zie screenshot:

9. Klik vervolgens op de knop OK, u krijgt een combinatiegrafiek waarbij de markeringen zich tussen de respectieve kolommen bevinden. Zie screenshot:

10. Klik vervolgens op de toename reeks (de oranje stip), en klik vervolgens op de knop Diagram Elementen, vink de Foutbalken aan in de lijst, en foutbalken zijn aan het diagram toegevoegd, zie screenshot:

11. Selecteer de horizontale foutbalken en druk op Delete-toets om ze te verwijderen, zie screenshot:

12. Selecteer vervolgens de verticale foutbalken, klik met de rechtermuisknop, en kies Formaat Foutbalken, in het venster Formaat Foutbalken, onder het tabblad Foutbalk Opties, voer de volgende handelingen uit:

• Selecteer Beide optie in de sectie Richting;
• Selecteer Geen Dop in de sectie Einde Stijl;
• Selecteer in de sectie Fout Hoeveelheid Aangepast, en klik vervolgens op de knop Waarde Specificeren, in het pop-up venster Aangepaste Foutbalken, voer in het vak Positieve Foutwaarde ={0} in, en in het vak Negatieve Foutwaarde, selecteer de variantiewaarde E2:E9.
• Klik vervolgens op de knop OK.

13. Nu, blijf in het venster Formaat Foutbalken, klik op het tabblad Opvulling & Lijn, voer de volgende handelingen uit:

• Selecteer Vaste lijn in de sectie Lijn en kies een kleur die u nodig hebt, specificeer vervolgens de lijnbreedte naar wens;
• Selecteer een Pijltype uit de vervolgkeuzelijst Begin Pijltype.

14. In deze stap moet u de markeringen (de oranje stippen) verbergen, selecteer de oranje stippen, klik met de rechtermuisknop, kies Formaat Gegevensreeks uit het contextmenu, in het geopende venster Formaat Gegevensreeks, onder het tabblad Opvulling & Lijn, klik op sectie Markering, selecteer vervolgens Geen in de Markering Opties, zie screenshot:

15. Herhaal de bovenstaande stap 10-14 om de pijl omlaag in te voegen voor de afnamedatareeks en de grijze markeringen te verbergen, en u krijgt het diagram zoals in onderstaande schermafbeelding te zien is:

16. Nadat u de pijlen hebt ingevoegd, moet u nu de gegevenslabels toevoegen, klik op de verborgen toename reeks, en klik vervolgens op Diagram Elementen > Gegevenslabels > Boven, zie screenshot:

17. Klik vervolgens met de rechtermuisknop op een gegevenslabel, en kies Formaat Gegevenslabels uit het contextmenu, in het uitgebreide venster Formaat Gegevenslabels, onder het tabblad Label Opties, vink Waarde Uit Cellen optie aan, en in het pop-up venster Gegevenslabel Bereik, selecteer de variantiepercentagecellen (F2:F9), zie screenshot:

18. Klik op OK om het dialoogvenster te sluiten, blijf in het venster Formaat Gegevenslabels, vink de Y-waarde en Toon Leiderslijnen opties uit, zie screenshot:

19. Herhaal vervolgens de bovenstaande stap 16-18 om de negatieve percentagelabels toe te voegen (deze keer moet u labels onder de afnamedatapunten toevoegen, kies Onder in het submenu van Gegevenslabels in Diagram Elementen), en het kolomdiagram met procentuele verandering is succesvol gemaakt, zie screenshot:

Maak een kolomdiagram met procentuele verandering door gebruik te maken van een krachtige functie

Download Voorbeeldbestand Kolomdiagram Met Procentuele Verandering