Ga naar hoofdinhoud

Maak een kolomdiagram met procentuele verandering in Excel

In Excel kunt u een eenvoudig kolomdiagram maken om de gegevenstrends normaal te bekijken. Om de gegevens er intuïtiever uit te laten zien om de verschillen tussen de jaren weer te geven, kunt u een kolomdiagram maken met een procentuele verandering tussen elke kolom, zoals onderstaand screenshot. In dit type diagram geven de pijlen omhoog het verhoogde percentage aan dat het latere jaar dan het voorgaande jaar, terwijl de pijlen omlaag het verlaagde percentage aangeven.

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


Maak een kolomdiagram met procentuele verandering door foutbalken te gebruiken

Als u de foutbalken gebruikt om een ​​kolomdiagram met procentuele wijziging te maken, moet u enkele hulpkolommen invoegen zoals hieronder weergegeven, en vervolgens de grafiek maken op basis van de hulpgegevens. Ga als volgt te werk:

Maak eerst de gegevens van de hulpkolommen

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

=A2&REPT(" ",10)
Tips: Deze formule wordt gebruikt om het label langer te maken en de tekst naar links te verplaatsen. Anders bevindt het label zich onder het midden van de zichtbare en onzichtbare kolombalken.

2. Ga verder, voer de onderstaande formule in cel D2 in en sleep en kopieer de formule naar cel D10, zie screenshot:

=B2
Tips: Deze formule verwijst naar de bestelnummers in kolom B.

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

=B3
Tips: Deze formule wordt gebruikt om naar de bestellingen van het volgende jaar te verwijzen. Het wordt gebruikt voor de onzichtbare balken, die de bovenkant of onderkant van de foutbalken zijn.

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

=B3-B2
Tips: Deze formule berekent de variantie of het verschil tussen het huidige jaar en volgend jaar.

5. Ga door met het invoeren van de volgende formule in cel G2 en sleep deze naar cel G9, zie screenshot:

=IF(F2>0,-F2,"")
Tips: Deze formule wordt gebruikt om de variantie-waarde te retourneren als deze positief is, en er worden enkele negatieve getallen weergegeven die worden gebruikt om de foutbalken te laten beginnen aan de bovenkant van de onzichtbare balk en naar beneden te gaan.

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

=IF(F2<0,F2,"")
Tips: Deze formule wordt gebruikt om de variantiewaarde te retourneren als deze negatief is, en er worden enkele negatieve getallen weergegeven die worden gebruikt om de foutbalken onder aan de foutbalk te laten beginnen en omhoog te gaan.

7. Voeg nu de laatste helperkolom 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:

=F2/B2
Tips: Deze formule berekent het verschilpercentage tussen vorig jaar en het volgende jaar. Het wordt gebruikt als de labels op de onzichtbare kolom onder de foutbalken.

Ten tweede: maak het diagram op basis van de gegevens van de hulpkolommen

8. Nadat u de hulpgegevens hebt gemaakt, selecteert u de gegevens in kolom C, kolom D en kolom E en klikt u vervolgens op Invoegen > Kolom- of staafdiagram invoegen > Geclusterde kolom, zie screenshot:

9. En dan is er een kolomdiagram ingevoegd, u kunt de onnodige elementen van het diagram verwijderen, zoals diagramtitel, legenda of rasterlijnen, zie screenshots:

10. Klik vervolgens op de kolombalk die de onzichtbare gegevens weergeeft, en klik vervolgens op het Grafiekelementen knop om het Grafiekelementen keuzelijst en kies Foutbalken > meer opties, zie screenshot:

11. In het geopende Formatteer foutbalken deelvenster, onder het Foutbalkopties tab:

  • kies Te gebruiken zowel van het Aanwijzingen sectie;
  • Kies Pet van het Eindstijl;
  • kies Eigen van het FoutbedragEn klik op Geef waarde op, in de volgende Aangepaste foutbalken dialoogvenster, selecteert u de gegevens uit cel G2: G10 in het Positieve foutwaarde vak en selecteer vervolgens H2: H10-cellen in het Negatieve foutwaarde doos.

12. Dan klikken OK knop, en je krijgt de grafiek zoals hieronder getoond screenshot:

13. En klik nu met de rechtermuisknop op de balkkolom die de Order 1-gegevens weergeeft, en kies Formatteer gegevensreeksen zie screenshot vanuit het contextmenu:

14. In het geopende Formatteer gegevensreeksen deelvenster, onder het Serie-opties tab, verander de waarden in Series overlappen en Tussenruimte secties naar 0%, zie screenshot:

15. Nu moet u de onzichtbare gegevensbalk verbergen, met de rechtermuisknop op een ervan klikken, in het uitgeklapte contextmenu kiezen Geen vulling van het Vulling sectie, zie screenshot:

16. Klik met de onzichtbare gegevensbalk nog steeds geselecteerd op Grafiekelementen knop, kies Gegevenslabels > meer opties, zie screenshot:

17. In de Gegevenslabels opmaken deelvenster, onder het Labelopties tab, vink aan Waarde uit cellen, en in de pop eruit Bereik gegevenslabel prompt, selecteer het variantie-gegevensbereik I2: I9, zie screenshots:

18. Dan klikken OK, nog steeds in de Gegevenslabels opmaken paneel:

  • Schakel het vinkje uit Waarde en Toon leiderslijnen opties onder de Labelopties;
  • Geef vervolgens de labelpositie op als Buiten einde van het Positie labelen.

19. Nu kunt u zien dat de gegevenslabels aan de grafiek zijn toegevoegd, u kunt de labels voor negatieve percentages instellen op Binnen End, en formatteer de gegevenslabels naar uw behoefte, zie screenshot:


Maak een kolomdiagram met procentuele wijziging door de pijlen omhoog te gebruiken

Soms wilt u misschien een paar pijlen gebruiken om de foutbalken te vervangen. Als de gegevens volgend jaar toenemen, wordt een pijl omhoog weergegeven, als de gegevens in het volgende jaar wegvallen, wordt een pijl omlaag weergegeven. Tegelijkertijd worden de datalabels en de pijlen dynamisch gewijzigd als de data verandert, zoals hieronder wordt getoond.

Om dit type diagram te maken, moet u hulpgegevens uit twee delen invoegen, zoals onderstaand screenshot. Het eerste deel berekent de variantie en de procentuele variantie zoals het blauwe deel wordt weergegeven, het tweede deel wordt gebruikt voor aangepaste foutbalken voor zowel toename als afname als het rode deel wordt weergegeven.

Maak eerst de gegevens van de hulpkolommen

1. Pas de onderstaande formules toe om de gegevens van de eerste deelhulp in te voegen:

C2: =REPT(A2,1)        (drag the formula to cell C10)
D2
: = B2 (sleep de formule naar cel D10)
E2: = B3-B2 (sleep de formule naar cel E9)
F2: = E2 / B2 (sleep de formule naar cel F9)

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

G2: Enter 1.5, 2.5, 3.5, 4.5…plus one to the previous cell.          (fill the data to from G2 to G9)
H2
: = ALS (B3> = B2, B3, NA ()) (sleep de formule naar cel H9)
I2: = ALS (B3

Ten tweede: maak het diagram op basis van de gegevens van de hulpkolommen

3. Selecteer de gegevens in kolom C en kolom D en klik op Invoegen > Kolom- of staafdiagram invoegen > Geclusterde kolom om een ​​kolomdiagram in te voegen zoals onderstaand screenshot:

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

5. Klik na het selecteren van de grafiek op Home > pasta > plakken speciaal, in de plakken speciaal dialoogvenster, selecteer Nieuwe series, Kolom opties, en controleer dan Serie naam in eerste rij en Categorieën (X-labels) in eerste kolom opties, zie screenshots:

6. En dan krijg je een grafiek zoals hieronder getoond screenshot:

7. Klik met de rechtermuisknop op een kolombalk in het diagram en kies Wijzig serie grafiektypee vanuit het contextmenu, zie screenshot:

8. In de Wijzig het diagramtype dialoogvenster, verander beide Laat uw omzet en Kleiner worden naar Spreidingsdiagramen schakel het selectievakje uit Secundaire as doos voor elk van Kies het diagramtype en de as voor uw serie keuzelijst. Zie screenshot:

9. En klik dan OK knop, krijg je een combinatiegrafiek waarin de markeringen tussen de respectievelijke kolommen staan. Zie screenshot:

10. Klik vervolgens om de reeks verhogen (de oranje stip) te selecteren en klik vervolgens op Grafiekelementen knop, controleer het Foutbalken uit de keuzelijst en foutbalken zijn aan de grafiek toegevoegd, zie screenshot:

11. Selecteer de horizontale foutbalken en druk op Verwijder sleutel om ze te verwijderen, zie screenshot:

12. En selecteer vervolgens de verticale foutbalken, klik er met de rechtermuisknop op en kies Formatteer foutbalken, in de Formatteer foutbalken deelvenster, onder het Foutbalkopties tabblad, voer de volgende bewerkingen uit:

  • kies Te gebruiken zowel optie van de Aanwijzingen;
  • kies Geen pet van het Eindstijl;
  • Van de Foutbedrag sectie, selecteer EigenEn klik op Geef waarde op knop, in de pop-out Aangepaste foutbalken dialoogvenster, in de Positieve foutwaarde vakje, enter = {0}, en in de Negatieve foutwaarde Selecteer de variantie waarde E2: E9.
  • Dan klikken OK knop.

13. Nu, nog steeds in de Formatteer foutbalken venster, klik op Vullen en lijn tabblad, voer de volgende bewerkingen uit:

  • kies Ononderbroken lijn in de Lijn sectie en kies een kleur die je nodig hebt, specificeer vervolgens de lijndikte zoals je nodig hebt;
  • Van de Begin pijltype vervolgkeuzelijst, kies er een pijl type.

14. In deze stap moet u de markeringen (de oranje stippen) verbergen, de oranje stippen selecteren en met de rechtermuisknop klikken en kiezen Formatteer gegevensreeksen vanuit het contextmenu, in het geopende Formatteer gegevensreeksen deelvenster, onder het Vullen en lijn tab, klik Fiche sectie en selecteer Geen van het Markeringsopties, zie screenshot:

15. Herhaal de bovenstaande stap 10-14 om de pijl naar beneden in te voegen voor de gegevensreeks met afname en de grijze markeringen te verbergen, en u krijgt de grafiek zoals hieronder afgebeeld:

16. Nadat u de pijlen heeft ingevoegd, moet u nu de gegevenslabels toevoegen, klik om de verborgen toename-reeks te selecteren en klik vervolgens op Grafiekelementen > Gegevenslabels > Boven, zie screenshot:

17. Klik vervolgens met de rechtermuisknop op een gegevenslabel en kies Gegevenslabels opmaken vanuit het contextmenu, in het uitgevouwen Gegevenslabels opmaken deelvenster, onder het Labelopties tab, vink aan Waarde uit cellen optie, dan, in de pop-out Bereik gegevenslabel dialoogvenster, selecteer de cellen van het variantiepercentage (F2: F9), zie screenshot:

18. Klikken OK om het dialoogvenster te sluiten, nog steeds in het Gegevenslabels opmaken vink het Y-waarde en Toon leiderslijnen opties, zie screenshot:

19. Vervolgens hoeft u alleen maar de bovenstaande stap 16-18 te herhalen om de labels voor het negatieve percentage toe te voegen (deze keer moet u labels toevoegen onder de verlagingsgegevenspunten, kies Onder in het submenu van Gegevenslabels in Grafieken elementen), en het kolomdiagram met procentuele verandering is met succes gemaakt, zie screenshot:


Maak een kolomdiagram met procentuele verandering met behulp van een krachtige functie

Voor de meesten van ons zijn de bovenstaande methoden te moeilijk om te gebruiken, maar als je dat hebt gedaan Kutools for Excel, biedt het verschillende speciale soorten grafieken die Excel niet heeft, zoals Bullet-diagram, Doel- en werkelijke grafiek, Slop-grafiek enzovoorts. Met zijn gemakkelijke tool- Kolomdiagram met gewijzigd percentagekunt u in Excel snel en eenvoudig een kolomdiagram met procentuele wijziging maken door de pijlen omhoog en omlaag te gebruiken. Klik om Kutools voor Excel gratis uit te proberen!


Kolomdiagram met voorbeeldbestand met percentage wijzigen downloaden


De beste tools voor kantoorproductiviteit

Kutools for Excel - Helpt u zich te onderscheiden van de menigte

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...

Omschrijving


Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)

  • Een seconde om te schakelen tussen tientallen geopende documenten!
  • Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
  • Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
  • Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Love this tutorial - I wonder though is it possible to create a similar cart but grouped by months across the year so 3 bars next to each other for April, then May, then June etc. I can't work this out!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations