Sei stato in grado di trovare il tipo di grafico a colonne impilato in cluster di Excel?
Ebbene non c’è, purtroppo. Ma Excel è uno strumento potente e possiamo utilizzare altre opzioni per creare il grafico che stiamo cercando con questo semplice suggerimento per creare il tuo grafico a colonne impilate in cluster Excel. Questo tutorial è incentrato su Microsoft Excel 2013, Excel 2016 ed Excel 2019. Ma molto probabilmente funzionerà per le versioni successive al 2019. Se vuoi vedere i passaggi richiesti per Excel 20010 ed Excel 2007, dai un’occhiata a questo tutorial: how-to- facilmente-crea-un-grafico-a-colonne-raggruppato-in-pila-in-Excel
La ripartizione
Ecco i passaggi di base che illustreremo in questo tutorial. Il file di download di esempio e la dimostrazione video si trovano alla fine dell’articolo.
1) Dati di configurazione
2) Crea grafico a colonne in pila
3) Cambia righe / colonne del grafico (se necessario)
4) Diminuisci la larghezza dello spazio tra le serie (come desiderato)
Passaggi opzionali per le funzionalità del grafico estese:
5) Aggiungi serie di riempitivi per la separazione
5a) Inserisci le righe necessarie al grafico creato in precedenza
6) Aggiungi Serie di etichette secondo asse verticale
6a) Aggiungi serie
6b) Ripeti i passaggi 2-3
6c) Sposta la nuova serie di etichette secondo asse verticale sul secondo asse
6d) Cambia il riempimento della nuova serie di etichette del secondo asse verticale in Nessun riempimento
6e) Diminuisci lo spazio tra le serie Larghezza (come desiderato)
6f) Elimina l’immissione della legenda dell’etichetta del 2 ° asse
Passo dopo passo
1) Dati di configurazione
Per creare un tipo di istogramma in pila in pila simulato, dobbiamo solo eseguire 4 passaggi. Ora questo suggerimento utilizza l’opzione del grafico “Etichette categoria multilivello” di Excel per i grafici a colonne. Per utilizzare questa opzione, dobbiamo impostare i nostri dati in un certo modo. Questo è il nostro primo passaggio.
Il nostro scenario è che stiamo costruendo un grafico per il team di marketing sull’importo speso e l’importo preventivato per 2 prodotti su 4 tipi di media (stampa, Internet, TV, radio). I dati che abbiamo ricevuto erano in questo formato.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Confronto tra budget di vendita e effettivo | ||||||||
| 2 | Radio | Radio | Stampa | Stampa | TV | TV | Internet | Internet | |
| 3 | Budget | Effettivo | Budget | Effettivo | Budget | Effettivo | Budget | Effettivo | |
| 4 | Prodotto 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Prodotto 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Dati originali
Per utilizzare l’opzione “Etichette di categoria multilivello” in un grafico a colonne, dobbiamo impostare i nostri dati con categorie a più livelli. Ciò significa che abbiamo una categoria principale in una colonna e molte categorie nella colonna a destra. Pertanto, dobbiamo modificare i nostri dati nel seguente formato.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Confronto tra budget di vendita e effettivo | |||||
| 2 | Radio | Stampa | TV | Internet | ||
| 3 | Prodotto 1 | Budget | 123 | 155 | 91 | 181 |
| 4 | Effettivo | 166 | 128 | 163 | 171 | |
| 5 | Prodotto 2 | Budget | 63 | 157 | 53 | 198 |
| 6 | Effettivo | 149 | 101 | 140 | 153 |
Configurazione di base di grafici in pila raggruppati
Così puoi vedere che i nostri dati è stato riorganizzato in modo tale Il prodotto 1 ha 2 linee ad esso associate. Uno per i valori del budget e uno per i valori effettivi per tutti e 4 i tipi di pubblicità sui media. Siamo ora pronti per creare l’istogramma in pila in pila di Excel.
2) Crea grafico a colonne in pila
Per creare il nostro grafico, evidenzia l’intervallo di dati nel set di dati sopra da A2: F6. Quindi vai alla barra multifunzione Inserisci e fai clic sul grafico a colonne in pila nel pulsante del grafico come vedi qui.
Il tuo grafico dovrebbe ora assomigliare a questo:
3 ) Cambia righe / colonne del grafico (se necessario)
Il nostro grafico non sembra corretto. Nella maggior parte dei casi è necessario cambiare righe / colonne nel grafico. Perché ho bisogno di farlo? Guarda questo video qui
Per correggere il grafico, fai semplicemente clic sul grafico, quindi vai alla barra multifunzione di progettazione e fai clic sul pulsante Cambia righe / colonne.
Il tuo grafico ora è completo e dovrebbe avere questo aspetto:
Se desideri avvicinare le colonne, segui il passaggio successivo, ma è facoltativo.
4) Diminuisci l’ampiezza dello spazio tra le serie (come desiderato)
Se lo desideri, ed è il mio aspetto preferito, puoi chiudere il Gap Width delle colonne. Per fare ciò, seleziona il grafico, quindi seleziona una serie di dati e premi CTRL + F1 (o fai clic con il pulsante destro del mouse e seleziona Formato serie di dati). Quindi, dalla finestra di dialogo Formato serie di dati, modifica la larghezza dello spazio al 15% o qualsiasi altra cosa ritieni sia migliore.
Il tuo grafico finale dovrebbe ora assomigliare a quello che vedi sopra. Ma non abbiamo finito se vuoi aggiungere ulteriori modifiche. Di seguito è completamente facoltativo e come desideri, ma penso che aggiunga un po ‘di chiarezza e ulteriori dettagli che aiuteranno il lettore.
Passaggi per grafici a colonne in pila raggruppati opzionali
5) Aggiungi serie di riempitivi per la separazione
Se vuoi separare i raggruppamenti segui questo passaggio.
5a) Inserisci le righe necessarie nel grafico creato in precedenza
Fallo come meglio credi , inserisci semplicemente una riga nei tuoi dati e separerà i gruppi.
Dopo aver inserito la riga, devi seguire i passaggi 1-3 sopra per creare il grafico dall’inizio.Quando completi i passaggi 1-3, vedrai che il tuo grafico non sembra proprio corretto poiché il tuo gruppo di prodotti 1 include questo nuovo spazio come vedi qui:
Per risolvere questo problema, devi ingannare Excel creando un altro raggruppamento di livello uno che non viene visualizzato nel grafico. È molto semplice inserendo uno spazio nel set di dati nella cella proprio sopra la voce Prodotto 2.
Il tuo grafico finale con lo spazio vuoto aggiunto dovrebbe avere questo aspetto con il raggruppamento corretto:
6) Aggiungi serie di etichette del secondo asse verticale
Opzionale Il passaggio 5 sopra mi sembra buono, ma i dati del prodotto 2 sono più lontani dai numeri, quindi a volte mi piace aggiungere un serie di grafici aggiuntive sull’asse secondario per visualizzare i numeri dell’asse verticale più vicini a quella serie. Questo è un passaggio facoltativo, quindi usalo solo se ti piace anche l’aspetto.
6a) Aggiungi serie
Per fare in modo che il valore del limite massimo dell’asse secondario corrisponda al limite massimo dell’asse principale, dobbiamo creare una formula per la nostra serie di assi secondari aggiuntivi. Per fare ciò, crea una formula sulla prima riga di dati con una formula che calcola la colonna impilata massima di tutte le colonne impilate che saranno nel grafico. Pertanto, nella cella G3, creiamo una formula MAX () con una somma di ogni riga. Per ulteriori dettagli, consulta la formula di Max e Sum di seguito.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Confronto tra budget di vendita e effettivo | ||||||
| 2 | Radio | Stampa | TV | Internet | Etichette secondo asse | ||
| 3 | Prodotto 1 | Budget | 123 | 155 | 91 | 181 | 628 |
| 4 | Effettivo | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Prodotto 2 | Budget | 63 | 157 | 53 | 198 | |
| 7 | Effettivo | 149 | 101 | 140 | 153 |
Grafico 2 ° asse facoltativo
Formule del foglio di lavoro
|
6b) Ripeti i passaggi 2-3
Ora che hai creato il tuo set di dati, seguirai semplicemente tutti i passaggi di base 1-3 precedenti per creare il tuo grafico a colonne in pila raggruppato ma cambia l’intervallo del grafico in A2: G6 o A2: G7 se hai aggiunto una riga in più per uno spazio tra i raggruppamenti. Nel mio caso ho aggiunto la riga del gap, quindi il mio intervallo del grafico è A2: G7.Vai avanti e crea il grafico iniziale come vedi qui:
Il tuo grafico iniziale dovrebbe ora assomigliare a questo se hai la riga di spazio nella serie di dati dopo il passaggio 2.
Dopo aver cambiato riga / colonna, il grafico avrà questo aspetto dopo il passaggio 3:
6c) Spostare la nuova serie di etichette Veritical 2nd Axis sul 2nd Axis
Il nostro grafico è molto simile al nostro grafico di base, ma c’è una serie extra che useremo per il nostro asse secondario. Quindi seleziona il grafico, quindi seleziona la serie “2nd Axis Labels” e premi CTRL + F1 o fai clic con il pulsante destro del mouse su di esso e seleziona l’opzione Formato serie di dati…. Verrà visualizzata l’opzione per spostarlo sull’asse secondario. Fallo ora .
6d) Cambia il Nuovo riempimento della serie di etichette del secondo asse verticale in Nessun riempimento
Quando abbiamo spostato la serie sull’asse secondario, copre una delle nostre colonne impilate. Quindi, per aggirare questo problema, dobbiamo cambiare il riempimento della serie su Nessun riempimento. Se ci si trova ancora nella finestra di dialogo Formato serie di dati, fare clic su Opzioni di riempimento (sembra un barattolo di vernice che versa) e modificare il Riempimento su Nessun riempimento.
6d) Riduci la larghezza dello spazio tra le serie (come Desiderato)
Questo è lo stesso del passaggio opzionale 4 sopra. Ho cambiato la larghezza dello spazio delle colonne in pila sull’asse principale al 15% come vedi qui:
6f) Elimina la voce della legenda dell’etichetta del 2 ° asse
L’ultima cosa che dobbiamo fare è eliminare la voce della legenda” Etichette del 2 ° asse “nella legenda. Per fare ciò, seleziona il grafico, quindi seleziona la legenda, quindi seleziona la legenda, quindi seleziona la voce della legenda “2nd Axis Labels” e premi il tasto Canc.
Il tuo grafico finale ora avrà questo aspetto:
Questa è un’ottima tecnica per creare un grafico combinato che è spesso necessario per set di dati complessi. Funzionerà anche per più set di dati di prodotto. Ecco un esempio di come sarebbe un’altra serie.
Dimostrazione video
Guarda questo tutorial video sulle tecniche presentato sopra.
Download del file di esempio
Fare clic qui per scaricare il file modello Excel di esempio gratuito:
Clustered-Stacked -Column-Grafici
