Har du kunnat hitta Excel Clustered Stacked Column Chart Type?
Det är tyvärr inte där. Men Excel är ett kraftfullt verktyg och vi kan använda andra alternativ för att skapa diagrammet vi letar efter med detta enkla tips för att skapa din egen Excel-grupperade staplade kolumngraf. Denna handledning fokuserar på Microsoft Excel 2013, Excel 2016 och Excel 2019. Men det kommer troligen att fungera för versioner efter 2019. Om du vill se stegen som krävs för Excel 20010 och Excel 2007, kolla in den här självstudien: hur-till- enkelt skapa-en-staplade-kluster-kolumn-diagram-i-excel
Fördelningen
Här är de grundläggande stegen som vi kommer att gå igenom i denna handledning. Exempel på nedladdningsfil och videodemonstration finns i slutet av artikeln.
1) Inställningsdata
2) Skapa staplade kolumndiagram
3) Byt rader / kolumner i diagram (om det behövs)
4) Minska seriebredden (som önskat)
Valfria steg för de utökade sjökortfunktionerna:
5) Lägg till fyllnadsserier för separering
5a) Infoga rader efter behov för tidigare skapade diagram
6) Lägg till Vertikal 2: a axel etikett serie
6a) Lägg till serie
6b) Upprepa steg 2-3
6c) Flytta ny veritisk etikett serie 2: a axel till andra axel
6d) Ändra ny vertikal 2: a axel etikettserie fyllning till ingen fyllning
6e) Minska seriegapet Bredd (som önskat)
6f) Ta bort 2: a axelns etikettförteckning
Steg för steg
1) Inställningsdata
För att skapa en simulerad typ av grupperade staplade kolonndiagram behöver vi bara utföra fyra steg. Nu använder detta tips Excel-alternativet ”Multi-level Category Labels” för kolumndiagram. För att kunna använda detta alternativ måste vi ställa in våra data på ett visst sätt. Så det är vårt första steg.
Vårt scenario är att vi bygger ett diagram för marknadsföringsteamet över använt belopp och budgeterat belopp för 2 produkter på 4 mediatyper (Print, Internet, TV, Radio). Data vi fick var i detta format.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Försäljningsbudget kontra faktiskt | ||||||||
| 2 | Radio | Radio | Skriv ut | Skriv ut | TV | TV | Internet | Internet | |
| 3 | Budget | Faktisk | Budget | Faktisk | Budget | Faktisk | Budget | Faktisk | |
| 4 | Produkt 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Produkt 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Ursprungliga data
För att använda alternativet ”Kategorin Etiketter på flera nivåer” i ett kolumndiagram måste vi ställa in våra data med nivåindelade kategorier. Det betyder att vi har en huvudkategori i en kolumn och många kategorier i kolumnen till höger. Därför måste vi ändra våra data till följande format.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Försäljningsbudget kontra faktiskt | |||||
| 2 | Radio | Skriv ut | TV | Internet | ||
| 3 | Produkt 1 | Budget | 123 | 155 | 91 | 181 |
| 4 | Faktiskt | 166 | 128 | 163 | 171 | |
| 5 | Produkt 2 | Budget | 63 | 157 | 53 | 198 |
| 6 | Faktiskt | 149 | 101 | 140 | 153 |
Grundläggande grupperade staplade diagraminställningar
Så att du kan se att våra data har omarrangerats så att Produkt 1 har två rader associerade med sig. En för budgetvärdena och en för de faktiska värdena för alla fyra typer av mediareklam. Vi är nu redo att skapa Excel-grupperade staplade kolumndiagram.
2) Skapa staplade kolumndiagram
För att skapa vårt diagram, markera dataområdet i datauppsättningen ovan från A2: F6. Gå sedan till Infoga menyfliksområdet och klicka på det staplade kolonndiagrammet i diagramknappen som du ser här.
Ditt diagram ska nu se ut så här:
3 ) Byt rader / kolumner i diagrammet (om det behövs)
Vårt diagram ser inte riktigt ut. I de flesta fall måste du byta rad / kolumn i diagrammet. Varför behöver jag göra det här? Kolla in den här videon här
För att fixa ditt diagram, klicka bara på diagrammet och gå sedan till Design Ribbon och klicka på knappen Byt rader / kolumner.
Ditt diagram är nu komplett och ska se ut så här:
Om du vill flytta kolumnerna närmare varandra följer du nästa steg, men det är valfritt.
4) Minska seriebredden (som önskat)
Om du vill, och det är mitt föredragna utseende, du kan stänga kolumnernas gapbredd. För att göra detta, välj diagrammet och välj sedan valfri dataserie och tryck på CTRL + F1 (eller högerklicka och välj Formatera dataserie). Ändra sedan Gap-bredden till 15% eller vad du tycker ser bäst ut från dialogrutan Format Data Series.
Ditt slutgiltiga diagram ska nu se ut som du ser ovan. Men vi är inte klara om du vill lägga till några ytterligare ändringar. Nedan är helt frivilligt och som du vill, men jag tror att det ger lite tydlighet och ytterligare detaljer som kommer att hjälpa läsaren.
Valfria grupperade staplade kolumndiagramsteg
5) Add Filler Series för separering
Om du vill separera grupperingarna, följ detta steg.
5a) Infoga rader efter behov till tidigare skapade diagram
Gör detta som du tycker passar , sätt bara in en rad i dina data så skiljer de grupperna.
När du har infogat din rad måste du följa steg 1-3 ovan för att skapa ditt diagram från början.När du har slutfört steg 1-3 ser du att ditt diagram inte ser riktigt bra ut eftersom din produkt 1-grupp innehåller det nya utrymmet som du ser här:
För att åtgärda detta måste du lura Excel genom att skapa en annan nivå en gruppering som visas inte på diagrammet. Detta är väldigt enkelt genom att bara placera ett mellanslag i datamängden i cellen precis ovanför produkt 2-posten.
Ditt slutgiltiga diagram med den tillagda luckan ska se ut så här med grupperingen fast:
6) Lägg till vertikal 2: a axel-etikett-serien
Valfritt steg 5 ovan ser bra ut för mig, men produkt 2-data är längre bort från siffrorna så ibland vill jag lägga till en ytterligare diagramserier på sekundäraxeln för att de vertikala axelnumren ska visas närmare den serien. Det här är ett valfritt steg, så använd det bara om du också gillar utseendet.
6a) Lägg till serie
För att få det sekundära axelns maxgränsvärde att matcha den maximala axelns maxgräns vi måste skapa en formel för vår ytterligare sekundära axel serie. För att göra detta skapar du en formel på den första raden med data med en formel som beräknar den maximala staplade kolumnen för alla staplade kolumner som kommer att finnas i diagrammet. Därför, i cell G3, skapar vi en MAX () formel med en summa av varje rad. Se Max och Sum-formeln nedan för mer information.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Försäljningsbudget kontra faktiskt | ||||||
| 2 | Radio | Skriv ut | TV | Internet | Etiketter för andra axeln | ||
| 3 | Produkt 1 | Budget | 123 | 155 | 91 | 181 | 628 |
| 4 | Faktiskt | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Produkt 2 | Budget | 63 | 157 | 53 | 198 | |
| 7 | Faktiskt | 149 | 101 | 140 | 153 |
Tillval 2: a axeldiagram
Formulär för arbetsblad
|
6b) Upprepa steg 2-3
Nu när du har skapat din datamängd följer du helt enkelt alla ursprungliga grundläggande steg 1-3 ovan för att skapa ditt klustrade staplade kolumndiagram men ändra ditt diagramintervall till A2: G6 eller A2: G7 om du har lagt till en extra rad för ett mellanrum mellan grupperingar. I mitt fall har jag lagt till gap-raden, så mitt diagramintervall är A2: G7.Fortsätt och skapa det första diagrammet som du ser här:
Din initial bör nu se ut så här om du har gap-raden i dataserien efter steg 2.
När du byter rad / kolumner ser ditt diagram ut så här efter steg 3:
6c) Flytta ny Veritical 2: a axel etikett serie till 2: a axel
Vårt diagram ser väldigt ut som vårt grundläggande diagram, men det finns en extra serie som vi kommer att använda för vår sekundära axel. Så välj diagrammet och välj sedan ”2nd Axis Labels” -serien och tryck på CTRL + F1 eller högerklicka på den och välj alternativet Format Data Series…. Detta ger dig möjlighet att flytta den till den sekundära axeln. Gör det nu .
6d) Ändra ny vertikal 2-axel etikett-serie fyllning till ingen fyllning
När vi flyttade serien till sekundäraxeln täcker den en av våra staplade kolumner. Så för att komma runt detta måste vi ändra fyllningen i serien till Ingen fyllning. Om du fortfarande är i dialogrutan Formatera dataserie, klicka på Fyllningsalternativ (ser ut som en färg kan hälla) och ändra Fyll till Ingen fyllning.
6d) Minska seriebredden (som Önskad)
Detta är detsamma som valfritt steg 4. Jag ändrade springbredden för de staplade kolumnerna på den primära axeln till 15% som du ser här:
6f) Ta bort teckenförteckning för 2: a axeln
Det sista vi behöver göra är att ta bort legendposten” 2nd Axis Labels ”på legenden. För att göra detta, välj diagrammet och välj sedan förklaringen och välj sedan förklaringen och välj sedan ”2: a Axis Etiketter” -förklaringen och tryck på raderingsknappen. / div>
Ditt slutgiltiga diagram kommer nu att se ut så här:
Detta är en utmärkt teknik att göra ett kombinationsdiagram som ofta behövs för komplexa datamängder. Det fungerar till och med för fler produktdatamängder. Här är ett exempel på hur en annan serie skulle se ut.
Videodemonstration
Kolla in denna videohandledning om teknikerna presenteras ovan.
Nedladdning av provfil
Klicka här för att ladda ner gratis Excel-mallfilen:
Clustered-Stacked -Kolonndiagram
