Heb je het Excel geclusterde gestapelde kolomdiagramtype kunnen vinden?
Nou, het is er helaas niet. Maar Excel is een krachtig hulpmiddel en we kunnen andere opties gebruiken om de grafiek te maken die we zoeken met deze eenvoudige tip om uw eigen Excel-geclusterde gestapelde kolomgrafiek te maken. Deze tutorial is gericht op Microsoft Excel 2013, Excel 2016 en Excel 2019. Maar het zal waarschijnlijk werken voor versies na 2019. Als je de stappen wilt zien die nodig zijn voor Excel 20010 en Excel 2007, bekijk dan deze tutorial: how-to- easy-create-a-stacked-clustered-column-chart-in-Excel
The Breakdown
Hier zijn de basisstappen die we in deze tutorial zullen doorlopen. Het voorbeelddownloadbestand en de videodemonstratie staan aan het einde van het artikel.
1) Configuratiegegevens
2) Maak een gestapeld kolomdiagram
3) Schakel rijen / kolommen van diagrammen (indien nodig)
4) Verklein de breedte van de tussenruimte in de serie (zoals gewenst)
Optionele stappen voor de uitgebreide kaartfuncties:
5) Filler-serie toevoegen voor scheiding
5a) Voeg indien nodig rijen toe aan een eerder gemaakte kaart
6) Toevoegen Verticale 2e-aslabelserie
6a) Serie toevoegen
6b) Herhaal stap 2-3
6c) Verplaats nieuwe authentieke 2e-aslabelserie naar 2e as
6d) Nieuwe verticale 2e aslabelserie vullen in Geen opvulling
6e) Serie-opening verkleinen Breedte (zoals gewenst)
6f) Invoer labellegenda 2e as verwijderen
Stap-voor-stap
1) Configuratiegegevens
Om een gesimuleerd geclusterd gestapeld kolomdiagramtype te maken, hoeven we slechts 4 stappen uit te voeren. Deze tip gebruikt nu de Excel-diagramoptie “Categorie-labels met meerdere niveaus” voor kolomdiagrammen. Om deze optie te gebruiken, moeten we onze gegevens op een bepaalde manier instellen. Dat is dus onze eerste stap.
Ons scenario is dat we een grafiek maken voor het marketingteam over het bestede bedrag en het gebudgetteerde bedrag voor 2 producten op 4 mediatypen (print, internet, tv, radio). De gegevens die we hebben ontvangen waren in deze indeling.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Verkoopbudget versus werkelijk | ||||||||
| 2 | Radio | Radio | Afdrukken | Afdrukken | TV | TV | Internet | Internet | |
| 3 | Budget | Werkelijk | Budget | Werkelijk | Budget | Werkelijk | Budget | Werkelijk | |
| 4 | Product 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Product 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Oorspronkelijke gegevens
Om de optie “Categorie-labels met meerdere niveaus” in een kolomdiagram te gebruiken, moeten we onze gegevens instellen met gelaagde categorieën. Dat betekent dat we een hoofdcategorie in één kolom hebben en veel categorieën in de rechterkolom. Daarom moeten we onze gegevens wijzigen in het volgende formaat.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Verkoopbudget vs. werkelijk | |||||
| 2 | Radio | Afdrukken | TV | Internet | ||
| 3 | Product 1 | Budget | 123 | 155 | 91 | 181 |
| 4 | Werkelijk | 166 | 128 | 163 | 171 | |
| 5 | Product 2 | Budget | 63 | 157 | 53 | 198 |
| 6 | Werkelijk | 149 | 101 | 140 | 153 |
Basisinstelling van geclusterde gestapelde grafieken
Zodat u kunt zien dat onze gegevens is herschikt zodat Product 1 heeft 2 bijbehorende regels. Een voor de budgetwaarden en een voor de werkelijke waarden voor alle 4 soorten media-advertenties. We zijn nu klaar om het Excel geclusterde gestapelde kolomdiagram te maken.
2) Maak een gestapeld kolomdiagram
Om ons diagram te maken, markeert u het gegevensbereik in de bovenstaande dataset vanaf A2: F6. Ga dan naar het lint invoegen en klik op het gestapelde kolomdiagram in de diagramknop zoals je hier ziet.
Uw diagram zou er nu als volgt uit moeten zien:
3 ) Rijen / kolommen van diagram wisselen (indien nodig)
Ons diagram ziet er niet helemaal goed uit. In de meeste gevallen moet u rijen / kolommen in uw diagram wisselen. Waarom moet ik dit doen? Bekijk deze video hier
Om uw diagram te corrigeren, klikt u eenvoudig op het diagram en gaat u naar het Design Ribbon en klikt u op de knop Rijen / kolommen wisselen.
Je diagram is nu compleet en zou er als volgt uit moeten zien:
Als u de kolommen dichter bij elkaar wilt plaatsen, volgt u de volgende stap, maar deze is optioneel.
4) Verklein de breedte van de tussenruimte in de reeks (zoals gewenst)
Als u dat wilt, en dat is het ook mijn favoriete look, je kunt de tussenruimte van de kolommen sluiten. Selecteer hiervoor het diagram en selecteer vervolgens een gegevensreeks en druk op CTRL + F1 (of klik met de rechtermuisknop en selecteer Gegevensreeks opmaken). Wijzig vervolgens in het dialoogvenster Gegevensreeks opmaken de tussenruimte in 15% of wat u ook denkt dat er het beste uitziet.
Je uiteindelijke diagram zou er nu uit moeten zien zoals je hierboven ziet. Maar we zijn nog niet klaar als u nog wat extra wijzigingen wilt aanbrengen. Hieronder is volledig optioneel en zoals u wenst, maar ik denk dat het wat duidelijkheid en extra details toevoegt die de lezer zullen helpen.
Optionele geclusterde gestapelde kolomdiagramstappen
5) Voeg Filler-serie toe voor scheiding
Als u de groeperingen wilt scheiden, volgt u deze stap.
5a) Voeg zo nodig rijen in in een eerder gemaakte grafiek
Doe dit naar eigen inzicht , voeg gewoon een rij in uw gegevens in en de groepen worden gescheiden.
Nadat u uw rij heeft ingevoegd, moet u de stappen 1-3 hierboven volgen om uw grafiek vanaf het begin te maken.Wanneer u stap 1-3 voltooit, ziet u dat uw diagram er niet helemaal goed uitziet, aangezien uw productgroep 1 deze nieuwe ruimte bevat, zoals u hier ziet:
Om dit op te lossen, moet u Excel voor de gek houden door een ander niveau 1-groep te maken dat wordt niet weergegeven op de kaart. Dit is heel eenvoudig door gewoon een spatie in de gegevensset in de cel recht boven het item in Product 2 te plaatsen.
Uw uiteindelijke diagram met de toegevoegde ruimte moet zien er zo uit met de groepering hersteld:
6) Verticale 2e-aslabelserie toevoegen
Optionele stap 5 hierboven ziet er goed uit voor mij, maar de gegevens van product 2 staan verder weg van de cijfers, dus soms voeg ik graag een aanvullende diagramreeksen op de secundaire as om de verticale asnummers dichter bij die reeks te laten zien. Dit is een optionele stap, dus gebruik het alleen als je het uiterlijk ook leuk vindt.
6a) Serie toevoegen
Om de maximale limietwaarde van de secundaire as overeen te laten komen met de maximale limiet van de primaire as, we moeten een formule maken voor onze extra reeks secundaire assen. Om dit te doen, maakt u een formule op de eerste rij met gegevens met een formule die de maximale gestapelde kolom berekent van alle gestapelde kolommen die in het diagram zullen staan. Daarom maken we in cel G3 een MAX () -formule met een som van elke rij. Zie de Max en Som-formule hieronder voor meer details.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Verkoopbudget vs. werkelijk | ||||||
| 2 | Radio | Afdrukken | TV | Internet | 2e as-labels | ||
| 3 | Product 1 | Budget | 123 | 155 | 91 | 181 | 628 |
| 4 | Werkelijk | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Product 2 | Budget | 63 | 157 | 53 | 198 | |
| 7 | Werkelijk | 149 | 101 | 140 | 153 |
Optioneel 2e asdiagram
Werkbladformules
|
6b) Herhaal stap 2-3
Nu u uw gegevensset heeft gemaakt, volgt u gewoon alle oorspronkelijke basisstappen 1-3 hierboven om uw geclusterde gestapelde kolomdiagram te maken maar verander je kaartbereik naar A2: G6 of A2: G7 als je een extra rij hebt toegevoegd voor een opening tussen groeperingen. In mijn geval heb ik de gap-rij toegevoegd, dus mijn kaartbereik is A2: G7.Ga je gang en maak het eerste diagram zoals je hier ziet:
De initiaal van uw diagram zou er nu zo uit moeten zien als u de gap-rij in de gegevensreeks na stap 2 heeft.
Nadat u van rij / kolom bent gewisseld, ziet uw diagram er na stap 3 als volgt uit:
6c) Nieuwe Veritical 2e-aslabelserie verplaatsen naar 2e as
Onze grafiek lijkt erg op onze basisgrafiek, maar er is een extra reeks die we zullen gebruiken voor onze secundaire as. Selecteer dus het diagram en selecteer vervolgens de reeks “Labels van de tweede as” en druk op CTRL + F1 of klik er met de rechtermuisknop op en selecteer de optie Gegevensreeks opmaken…. Dit zal de optie oproepen om het naar de secundaire as te verplaatsen. Doe dat nu .
6d) Nieuwe verticale vulling van labelserie 2e as wijzigen in Geen vulling
Wanneer we de reeks naar de secundaire as hebben verplaatst, bedekt deze een van onze gestapelde kolommen. Om dit te omzeilen, moeten we de vulling van de reeks naar Geen vulling. Als u zich nog steeds in het dialoogvenster Gegevensreeks opmaken bevindt, klikt u op de vullingopties (ziet eruit als een verfblik) en wijzigt u de vulling naar Geen vulling.
6d) Serie tussenruimte verkleinen (als Gewenst)
Dit is hetzelfde als optionele stap 4. Ik heb de breedte van de opening van de gestapelde kolommen op de primaire as gewijzigd in 15%, zoals je hier ziet:
6f) Verwijderen 2e as label legenda item
Het laatste wat we moeten doen is de” 2e as labels “legenda item op de legenda verwijderen. Om dit te doen, selecteert u het diagram en vervolgens de legenda en vervolgens de legenda en vervolgens het legenda-item “2e aslabels” en druk op de delete-toets.
Je uiteindelijke diagram ziet er nu als volgt uit:
Dit is een geweldige techniek om een combinatiediagram dat vaak nodig is voor complexe datasets. Het werkt zelfs voor meer productdatasets. Hier is een voorbeeld van hoe een andere serie eruit zou zien.
Videodemonstratie
Bekijk deze video-tutorial over de technieken hierboven weergegeven.
Voorbeeldbestand downloaden
Klik hier om het gratis voorbeeldbestand van Excel-sjabloon te downloaden:
Clustered-Stacked -Kolom-grafieken
