Ați reușit să găsiți tipul de diagramă a coloanei stivuite în cluster Excel?
Ei bine, nu este acolo, din păcate. Dar Excel este un instrument puternic și putem folosi alte opțiuni pentru a crea graficul pe care îl căutăm cu acest sfat simplu pentru a vă crea propriul grafic Excel Coloană Stacked. Acest tutorial se concentrează pe Microsoft Excel 2013, Excel 2016 și Excel 2019. Dar cel mai probabil va funcționa pentru versiuni după 2019. Dacă doriți să vedeți pașii necesari pentru Excel 20010 și Excel 2007, consultați acest tutorial: instrucțiuni creați cu ușurință o coloană-cluster-grafică-în-excel
Defalcare
Iată pașii de bază pe care îi vom parcurge în acest tutorial. Eșantionul de fișier de descărcare și demonstrația video sunt la sfârșitul articolului.
1) Date de configurare
2) Creați o diagramă de coloane stivuite
3) Comutați rândurile / coloanele diagramei (dacă este necesar)
4) Reduceți lățimea decalajului în serie (după cum doriți)
Pași opționali pentru caracteristicile grafice extinse:
5) Adăugați o serie de umpluturi pentru separare
5a) Inserați rândurile după cum este necesar în diagrama creată anterior
6) Adăugați Seria de etichete a 2-a axă verticală
6a) Adăugați serie
6b) Repetați pașii 2-3
6c) Mutați noua serie de etichete a 2-a axă Veritical pe a 2-a axă
6d) Schimbați noua completare verticală a seriei de etichete a 2-a axă la Fără umplere
6e) Reduceți diferența de serie Lățime (după dorință)
6f) Ștergeți intrarea legendei etichetei a doua axă
Pas cu pas
1) Date de configurare
Pentru a crea un tip de diagramă a coloanelor stivuite simulate, trebuie doar să parcurgem 4 pași. Acum acest sfat folosește opțiunea grafică „Etichete de categorii pe mai multe niveluri” pentru diagramele pe coloane. Pentru a utiliza această opțiune, trebuie să ne configurăm datele într-un anumit mod. Deci acesta este primul nostru pas.
Scenariul nostru este că construim un grafic pentru echipa de marketing cu privire la suma cheltuită și suma bugetată pentru 2 produse pe 4 tipuri media (tipărit, Internet, TV, radio). Datele pe care le-am primit au fost în acest format.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Buget de vânzări vs. Actual | ||||||||
| 2 | Radio | Radio | TV | TV | Internet | Internet | |||
| 3 | Buget | Actual | Buget | Actual | Buget | Actual | Buget | Actual | |
| 4 | Produsul 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Produs 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Date originale
Pentru a utiliza opțiunea „Etichete de categorie multi-nivel” într-o diagramă coloană, trebuie să ne configurăm datele cu categorii de niveluri. Asta înseamnă că avem o categorie principală într-o singură coloană și multe categorii în coloana din dreapta. Prin urmare, trebuie să ne schimbăm datele în formatul următor.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Buget de vânzări vs. Actual | |||||
| 2 | Radio | TV | Internet | |||
| 3 | Produsul 1 | Buget | 123 | 155 | 91 | 181 |
| 4 | Actual | 166 | 128 | 163 | 171 | |
| 5 | Produsul 2 | Buget | 63 | 157 | 53 | 198 |
| 6 | Actual | 149 | 101 | 140 | 153 |
Configurarea de bază a graficelor stivuite în clustere
Deci, puteți vedea că datele noastre a fost rearanjat astfel încât Produsul 1 are asociate 2 linii. Una pentru valorile bugetare și una pentru valorile reale pentru toate cele 4 tipuri de publicitate media. Acum suntem gata să creăm diagrama Excel cu coloane stivuite în cluster.
2) Creați o diagramă cu coloane stivuite
Pentru a crea diagrama noastră, evidențiați intervalul de date din setul de date de mai sus din A2: F6. Apoi accesați panglica Insert și faceți clic pe Diagrama coloanelor stivuite din butonul diagramă așa cum vedeți aici.
Diagrama dvs. ar trebui să arate acum:
3 ) Comutați rândurile / coloanele diagramei (dacă este necesar)
Diagrama noastră nu arată foarte bine. În majoritatea cazurilor, trebuie să comutați rânduri / coloane pe diagramă. De ce trebuie să fac asta? Vedeți acest videoclip aici
Pentru a remedia diagrama, pur și simplu faceți clic pe diagramă și apoi accesați panglica de design și faceți clic pe butonul Comutare rânduri / coloane.
Diagrama dvs. este completă și ar trebui să arate astfel:
Dacă doriți să mutați coloanele mai aproape, urmați pasul următor, dar este opțional.
4) Reduceți lățimea decalajului în serie (după cum doriți)
Dacă doriți, și este aspectul meu preferat, puteți închide Gap Width a coloanelor. Pentru a face acest lucru, selectați graficul, apoi selectați orice serie de date și apăsați CTRL + F1 (sau faceți clic dreapta și selectați Formatare serie de date). Apoi, din caseta de dialog Formatare serie de date, schimbați Lățimea decalajului la 15% sau orice credeți că arată cel mai bine.
Diagrama finală ar trebui să arate acum așa cum vedeți mai sus. Dar nu am terminat dacă doriți să adăugați câteva modificări suplimentare. Mai jos este complet opțional și după cum doriți, dar cred că adaugă o anumită claritate și detalii suplimentare care vor ajuta cititorul.
Etape opționale ale graficului coloanei stivuite în grup
5) Adăugați o serie pentru separare
Dacă doriți să separați grupările, urmați acest pas.
5a) Introduceți rândurile după cum este necesar în diagrama creată anterior
Faceți acest lucru după cum doriți , pur și simplu introduceți un rând în datele dvs. și va separa grupurile.
După ce introduceți rândul, trebuie să urmați pașii 1-3 de mai sus pentru a vă crea diagrama de la început.Când parcurgeți pașii 1-3, veți vedea că graficul dvs. nu arată foarte bine, deoarece grupul dvs. de produse 1 include acest spațiu nou așa cum vedeți aici:
Pentru a remedia acest lucru, trebuie să păcăliți Excel creând un alt nivel de grupare care nu apare pe grafic. Acest lucru este foarte simplu, doar plasând un spațiu în setul de date din celula chiar deasupra intrării Produsului 2.
Diagrama finală cu spațiul adăugat ar trebui arată așa cu gruparea fixată:
6) Adăugați seria de etichete a doua axă verticală
Opțional pasul 5 de mai sus mi se pare bine, dar datele despre produsul 2 sunt mai departe de numere, așa că uneori îmi place să adaug un seriile de diagrame suplimentare pe axa secundară pentru ca numerele axei verticale să fie mai apropiate de acea serie. Acesta este un pas opțional, deci folosiți-l numai dacă vă place și aspectul.
6a) Adăugați serie
Pentru ca valoarea maximă a axei secundare să se potrivească cu limita maximă a axei primare, trebuie să creăm o formulă pentru seria noastră suplimentară de axe secundare. Pentru a face acest lucru, creați o formulă pe primul rând de date cu o formulă care calculează coloana stivuită maximă a tuturor coloanelor stivuite care vor fi în diagramă. Prin urmare, în celula G3, creăm o formulă MAX () cu o sumă a fiecărui rând. Consultați formula Max și Suma de mai jos pentru mai multe detalii.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Bugetul vânzărilor vs. Actual | ||||||
| 2 | Radio | TV | Internet | Etichete a 2-a axă | |||
| 3 | Produsul 1 | Buget | 123 | 155 | 91 | 181 | 628 |
| 4 | Actual | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Produsul 2 | Buget | 63 | 157 | 53 | 198 | |
| 7 | Actual | 149 | 101 | 140 | 153 |
Diagrama opțională pe axa a doua
Formule foaie de lucru
|
6b) Repetați pașii 2-3
Acum că v-ați creat setul de date, veți urma pur și simplu toți pașii de bază 1-3 de mai sus pentru a crea diagrama de coloane stivuite în grup dar schimbați intervalul graficului în A2: G6 sau A2: G7 dacă ați adăugat un rând suplimentar pentru un decalaj între grupări. În cazul meu, am adăugat rândul decalaj, așa că gama mea de diagrame este A2: G7.Continuați și creați diagrama inițială așa cum vedeți aici:
Inițialul graficului dvs. ar trebui să arate acum dacă aveți rândul de spațiu din seria de date după pasul 2.
După ce schimbați rândurile / coloanele, diagrama dvs. va arăta astfel după pasul 3:
6c) Mutați noua serie de etichete Veritical 2nd Axis pe 2nd Axis
Diagrama noastră arată foarte asemănătoare cu diagrama noastră de bază, dar există o serie suplimentară pe care o vom folosi pentru axa secundară. Așadar, selectați graficul, apoi selectați seria „Etichete a 2-a axă” și apăsați CTRL + F1 sau faceți clic dreapta pe el și selectați opțiunea Formatare serie de date …. Aceasta va afișa opțiunea de a o muta pe axa secundară. Faceți asta acum .
6d) Schimbați noua serie verticală de etichete a 2-axa de umplere la No Fill
Când am mutat seria pe axa secundară, aceasta acoperă una dintre coloanele noastre stivuite. Deci, pentru a ocoli acest lucru, trebuie să schimbăm umplerea din serie la Fără umplere. Dacă vă aflați încă în caseta de dialog Formatare serie de date, faceți clic pe Opțiunile de umplere (se pare că se poate revărsa o vopsea) și schimbați umplerea la Fără umplere.
6d) Reduceți lățimea decalajului în serie (ca Dorit)
Acesta este același lucru cu pasul opțional 4 de mai sus. Am schimbat lățimea spațiului coloanelor stivuite pe axa primară la 15% așa cum vedeți aici:
6f) Ștergeți intrarea legendei etichetei a 2-a axă
Ultimul lucru pe care trebuie să-l facem este să ștergeți intrarea legendei„ Etichete a 2-a axă ”din Legendă. Pentru a face acest lucru, selectați diagrama, apoi selectați legenda, apoi selectați legenda, apoi selectați intrarea legendei „Etichete a 2-a axă” și apăsați tasta de ștergere.
Diagrama finală va arăta astfel:
Aceasta este o tehnică excelentă de realizat o diagramă combinată care este frecvent necesară pentru seturi de date complexe. Va funcționa chiar și pentru mai multe seturi de date despre produse. Iată un exemplu de cum ar arăta o altă serie.
Demonstrație video
Consultați acest tutorial video despre tehnici prezentat mai sus.
Descărcare fișier eșantion
Faceți clic aici pentru a descărca fișierul șablon Excel gratuit:
Clustered-Stacked -Column-Charts
