Două diagrame într-una. Diagrame mixte în Excel. Cum să construiți o diagramă combinată în Excel

În Excel, puteți afișa rezultatele calculelor sub forma unei diagrame sau a unui grafic, oferindu-le o mai mare claritate, iar pentru comparație, uneori trebuie să construiți două grafice una lângă alta. Vom analiza mai departe cum să construim două grafice în Excel pe un câmp.

Să începem cu faptul că nu orice tip de diagramă din Excel poate afișa exact rezultatul pe care îl așteptăm. De exemplu, există rezultate de calcul pentru mai multe funcții bazate pe aceleași date inițiale. Dacă construiți o histogramă sau un grafic obișnuit din aceste date, atunci datele originale nu vor fi luate în considerare în timpul construcției, ci doar cantitatea lor, între care se vor specifica intervale egale.

Selectăm două coloane de rezultate de calcul și construim o histogramă obișnuită.

Acum să încercăm să adăugăm o altă histogramă la cele existente cu același număr de rezultate de calcul. Pentru a adăuga un grafic în Excel, activați graficul existent selectându-l și în fila care apare "Constructor" alege „Selectați datele”. În fereastra care apare în secțiune „Elemente de legendă” faceți clic pe adăugare și specificați celule „Numele rândului:”Şi „Valori:” pe foaie, care vor fi valorile de calcul al funcției "j".

Acum să vedem cum va arăta diagrama noastră dacă adăugăm alta la histogramele existente, care are aproape de două ori mai multe valori. Să adăugăm valorile funcției la grafic "k".

După cum puteți vedea, există valori adăugate mult mai recent și sunt atât de mici încât sunt practic invizibile pe histogramă.

Dacă schimbăm tipul diagramei de la o histogramă la un grafic obișnuit, rezultatul în cazul nostru va fi mai clar.

Dacă utilizați un grafic de dispersie pentru a construi grafice în Excel, atunci graficele rezultate vor lua în considerare nu numai rezultatele calculului, ci și datele sursă, de exemplu. va exista o relaţie clară între cantităţi.

Pentru a crea un grafic punctat, să selectăm o coloană de valori inițiale și o pereche de coloane de rezultate pentru două funcții diferite. Pe fila "Introduce" selectați un grafic de dispersie cu curbe netede.

Pentru a adăuga un alt grafic, selectați-le pe cele existente și pe filă "Constructor" presa „Selectați datele”.

Într-o fereastră nouă în coloană „Elemente de legendă” presa "Adăuga", și indicați celulele pentru „Numele rândului:”, „Valori X:”Şi „Valori Y:”. Să adăugăm funcția în acest fel "j" la diagramă.

Să combinăm mai multe tipuri diferite de diagrame într-una singură.

Uneori trebuie să combinați diferite tipuri de diagrame pe o singură diagramă, de exemplu o histogramă și o curbă obișnuită:

Datele sursă pentru diagramă sunt prezentate în tabel (vezi fișierul exemplu):

Pentru claritate, culoarea rândului din tabel se potrivește cu culoarea diagramei corespunzătoare. De exemplu, rândul 5 „Venituri din vânzări” (evidențiat cu visiniu) corespunde curbei visinii din diagramă (a se vedea figura de sus).

Algoritmul pentru construirea diagramelor combinate este următorul:

• selectați toate datele care ar trebui incluse în diagramă (celule O 3: E 6 );
• în meniu Inserare / Diagrame / Histograme alege, de exemplu, Histograma cu grupare; V Creator de diagrame schimbați coloanele și rândurile ( Lucrul cu Diagrame/ Designer/ Date/ Rând-Coloană);
• în diagramă evidenţiem coloanele care corespund Venituri din vânzări(aceste coloane vor avea un tip de grafic diferit - diferit de o histogramă);
• selectați tipul dorit din meniu.

Un alt exemplu. Să fie 2 grafice pe diagramă:

Trebuie să faceți o histogramă din graficul verde. Pentru a face acest lucru, selectați graficul verde.

Prin meniu Lucrul cu diagrame/ Designer/ Tip/ Schimbați tipul diagramei selectați tipul Histogramă cu grupare, faceți clic pe OK.

Este posibil ca unele tipuri de diagrame să nu fie compatibile - EXCEL vă va avertiza despre acest lucru.

O opțiune mai complexă pentru combinarea diferitelor tipuri de diagrame este conținută în fișierul exemplu și este prezentată în figura de mai jos.

Luați în considerare construcția înDOMNIȘOARĂEXCELADiagrame 2010 cu mai multe serii de date, precum și utilizarea axelor auxiliare și combinarea diferitelor tipuri de diagrame pe o singură diagramă.

Construcția diagramelor cu o singură serie de date este discutată în articol. De asemenea, este recomandabil ca utilizatorii începători să citească articolul înainte de a citi.

HISTOGRAMA

Să construim Histogramă cu grupare bazat pe un tabel cu două coloane numerice care sunt apropiate ca valoare.

Selectați orice celulă de tabel (vezi exemplu de fișier) din filă Introduce, în grup Diagrame faceți clic pe butonul Histogramă, selectați din meniul derulant Histogramă cu grupare .

MS EXCEL va trasa ambele serii folosind numai axele principale (pentru a vă asigura acest lucru, faceți dublu clic mai întâi pe coloana unei serii, apoi pe una dintre coloanele de pe cealaltă. În fereastră Format de serie de date pe filă Parametrii seriei se va indica pe ce axă sunt trasate valorile seriei). Deoarece valorile ambelor serii sunt apropiate, atunci această soluție ni se potrivește.

Pentru seriile ale căror valori diferă semnificativ (cu un ordin de mărime sau mai mult), una dintre serii trebuie construită folosind .

Pentru a face acest lucru, trebuie doar să selectați construcția pe axa auxiliară pentru unul dintre rânduri (făcând dublu clic pe una dintre coloane), apoi reglați lățimea coloanelor (decalaj lateral) astfel încât să fie afișate ambele rânduri.

Dacă nu ajustați lățimea coloanelor, această diagramă poate fi confundată cu o diagramă Histogramă cu completare(coloanele aparținând aceleiași categorii sunt „stivuite” una peste alta).

O altă opțiune pentru afișarea a două serii cu valori semnificativ diferite pe o diagramă este să scalați valorile în tabelul sursă.

Axa auxiliară orizontală poate fi poziționată chiar și în partea de sus. În acest caz, coloanele diferitelor rânduri se vor intersecta într-un mod original.

Acum să schimbăm etichetele de-a lungul axei orizontale (categorie).

În fereastră Selectarea unei surse de date Se poate observa că pentru ambele rânduri etichetele axei orizontale sunt aceleași, deoarece categoriile sunt aceleași pentru ambele rânduri (coloana Lună).

Faceți clic mai întâi pe rândul Vânzări, apoi pe Profit - etichetele de pe axa orizontală vor fi aceleași.

Acest lucru poate fi schimbat și dacă se dorește. În fereastră Selectarea unei surse de date evidențiați rândul Profit, faceți clic pe butonul Schimba situat în partea dreaptă, eliminați referința celulei. Acum seria Profit, în loc de nume de categorii, va avea pur și simplu numere de serie 1, 2, 3,... Cu toate acestea, acestea nu vor fi afișate pe grafic, deoarece momentan doar afișat Axa principală orizontală .

Acum, în meniul Axe (fila Aspect, grup Axe) selectați și instalați-l De la stânga la dreapta. În fereastra de format Axa verticală auxiliară modificați punctul de intersecție a axei (set Selectare automată). Obținem această diagramă.

Deși există posibilitatea tehnică de afișare a 2 categorii diferite, desigur, astfel de diagrame trebuie evitate, deoarece sunt greu de perceput. Categoriile trebuie să fie aceleași pentru toate seriile din diagramă. Desigur, acest truc va funcționa doar pentru două grupuri de rânduri, deoarece Există doar 2 tipuri de axe: principală și auxiliară.

PROGRAMA

O diagramă grafică este similară în multe privințe cu o histogramă în cluster: folosește aceleași idei pentru afișarea a două serii cu valori semnificativ diferite ca o histogramă.

După așezarea rândurilor pe diferite axe, obținem o diagramă ca aceasta (liniile axelor verticale sunt evidențiate în culori corespunzătoare culorilor rândurilor).

Nota. Dacă desenați linii orizontale ale grilei pe axa auxiliară, atunci acestea, în mod natural, pot să nu coincidă cu liniile către axa principală, deoarece scările axelor (valorile principalelor diviziuni ale axelor verticale) pot să nu coincidă. Acest lucru poate supraîncărca diagrama.

În general, utilizarea axelor verticale auxiliare și, cu atât mai mult, a axelor orizontale auxiliare pentru histogramă și grafic trebuie abordată cu atenție: la urma urmei, diagrama trebuie să fie „lizibilă” - de înțeles fără comentarii suplimentare.

LOC

Din punct de vedere vizual, o diagramă de dispersie este similară cu o diagramă de tip grafic (cu excepția cazului în care, desigur, punctele dintr-o diagramă de dispersie sunt conectate prin linii).

Nota. Dacă nu este specificată o referință la valorile X pentru a construi un diagramă de dispersie (sau punctele de referință la valorile text), atunci aceeași secvență 1, 2, 3, ... va fi folosită ca coordonate X ca și pentru grafic .

Pe baza celor de mai sus, atunci când construiți o diagramă Scatter, este recomandabil să indicați valorile numerice prin X. În caz contrar, trebuie doar să utilizați Graficul, deoarece pentru aceasta puteți seta orice etichete de-a lungul X (inclusiv text), ceea ce nu se poate face pentru o diagramă Scatter (doar numere).

Acum despre combinarea diferitelor tipuri de diagrame cu Scatter. Dacă un diagramă de dispersie nu utilizează o coordonată X, acesta apare ca un grafic pe diagramă.

Etichetele de-a lungul axei orizontale sunt preluate din grafic. În fereastră Selectarea unei surse de date Se poate observa că pentru o serie afișată printr-un grafic de dispersie, etichetele axei orizontale nu pot fi modificate/șterse.

În plus, graficul poate fi doar pe axa principală și nu poate fi schimbat.

Dacă coordonatele X este folosită pentru un diagramă de dispersie și ambele serii sunt reprezentate pe aceeași axă (principală), atunci rezultatul nu este o diagramă foarte frumoasă.

Acest lucru se datorează faptului că atunci când este combinată cu o diagramă Scatter (pe aceeași axă și când Scatter-ul are valori X), diagrama tip Graph devine, așa cum spunea, principala:

• Etichetele numai pentru Grafic sunt afișate pe axa orizontală;
• grila verticală nu este afișată pentru valorile X negative (deoarece graficul este construit doar pentru X=1, 2, 3, ...);
• Este imposibil să schimbați axa graficului de la principal la auxiliar (pentru axa spot este posibil).

Dacă un spot este construit pe o axă auxiliară, diagrama se va schimba.

Etichetele pentru Spot (valori X) sunt acum afișate în partea de sus.

Sfaturi. Tipul de diagramă de dispersie este folosit pentru și altele.

Acum să ne uităm la construirea a 2 serii de date care utilizează o diagramă Scatter.

Mai întâi, să construim 2 elipse cu coordonate centrale diferite și dimensiuni ale semi-axelor fără a folosi axe auxiliare.

Nota. Există de fapt 4 serii de date în diagramă: punctul central reprezintă o serie separată.

Faceți dublu clic pe elipsa visiniu și selectați construirea unui rând pe axa auxiliară (vom face același lucru pentru centrul elipsei).

Acum, coordonatele Y pentru elipsa visiniu sunt trasate de-a lungul axei verticale din dreapta (puteți evidenția și în culoarea visiniu pentru claritate).

Să adăugăm o axă orizontală auxiliară (în meniul Axe (fila Aspect, grup Axe) selectați Axa orizontală secundarăși instalați-l Implicit).

Probabil pentru o diagramă ca Loc utilizarea axelor auxiliare este cea mai justificată - acest lucru vă permite să afișați curbe de diferite scări pe o diagramă.

CIRCULAR

De asemenea, se pot lăuda cu originalitatea combinației HistogramăŞi Bară stivuită normalizată .

Probabil că singurele tipuri de diagrame care merită combinate sunt ProgramaŞi Histogramă(categorii trebuie să fie aceleași pentru ambele rânduri).

Pentru o astfel de diagramă, mai întâi construiți Histogramă cu grupare cu două rânduri (vezi secțiunea Histogramă de la începutul acestui articol). Apoi selectați rândul dorit și apăsați butonul Schimbați tipul diagramei(fila Constructor). Prin selectare Programa sau Grafic cu markeri faceți clic pe OK.

Dacă scalele valorilor seriei diferă semnificativ, atunci puteți configura o axă verticală auxiliară (consultați secțiunea Histogramă).

O histogramă în Excel este o modalitate de a construi o diagramă vizuală care reflectă modificările mai multor tipuri de date într-o perioadă de timp.

O histogramă este utilă pentru ilustrarea și compararea diferiților parametri. Să ne uităm la cele mai populare tipuri de histograme și să învățăm cum să le construim.

Cum se construiește o histogramă actualizată?

Avem date despre vânzările de diferite tipuri de produse lactate pentru fiecare lună pentru 2015.

Să construim o histogramă actualizată care va răspunde la modificările aduse tabelului. Selectați întreaga matrice, inclusiv antetul și faceți clic pe fila INSERT. Să găsim DIAGRAME - HISTOGRAMĂ și să selectăm primul tip. Se numeste HISTOGRAMA CU GRUPARE.

Am primit o histogramă a cărei dimensiune a câmpului poate fi modificată. Această diagramă arată clar, de exemplu, că cele mai mari vânzări au fost pentru lapte în noiembrie, iar cele mai mici pentru smântână în iunie.

Dacă facem modificări în tabel, se va schimba și aspectul histogramei. De exemplu, în loc de 1400 în ianuarie pentru chefir, să punem 4000. Vedem cum a zburat coloana verde.

Histogramă stivuită

Acum să vedem cum să construim o histogramă stivuită în Excel. Un alt tip de histogramă care vă permite să afișați datele ca procent. Este construit exact în același mod, dar se alege un alt tip.

Obținem o diagramă în care puteți vedea că, de exemplu, în ianuarie s-a vândut mai mult lapte decât chefir sau smântână. Și în august, față de alte produse lactate, s-a vândut puțin lapte. etc.

Histogramele din Excel pot fi modificate. Deci, dacă facem clic dreapta într-o zonă goală a diagramei și selectăm MODIFICARE TIP, o putem modifica oarecum. Să schimbăm tipul histogramei noastre de la stivuit la normalizat. Rezultatul va fi același grafic, dar axa Y va afișa rapoartele în termeni procentuali.

În mod similar, puteți face alte modificări histogramei, ceea ce am făcut:

• a schimbat fontul în Arial și și-a schimbat culoarea în violet;
• a făcut o subliniere cu o linie punctată;
• a mutat legenda puțin mai sus;
• a adăugat subtitrări la coloane.

Cum să combinați o histogramă și un grafic în Excel?

Unele seturi de date necesită construirea de diagrame mai complexe care combină mai multe tipuri de date. De exemplu, o histogramă și un grafic.

Să ne uităm la un exemplu. Mai întâi, să adăugăm un alt rând la tabel cu datele, unde sunt înregistrate veniturile pentru fiecare lună. Este indicat în ruble.

Acum să modificăm diagrama existentă. Faceți clic dreapta într-un spațiu gol și selectați SELECTARE DATE. Va apărea un câmp care vă va cere să selectați un interval diferit. Selectăm din nou întregul tabel, dar de data aceasta inclusiv rândul cu venituri.

Excel a extins automat intervalul de valori de-a lungul axei Y, astfel încât datele privind numărul de vânzări au rămas în partea de jos, sub formă de coloane invizibile.

Dar o astfel de histogramă este incorectă, deoarece pe o diagramă avem numere în ruble și echivalente cantitative (ruble și litri). Prin urmare, trebuie făcute modificări. Să mutăm datele privind veniturile în partea dreaptă. Să facem clic dreapta pe coloanele violet, să selectăm DATA SERIES FORMAT și să marchem AUXILIARY AXIS.

Vedem că programul s-a schimbat imediat. Acum, bara de venit violet are propria sa zonă de valoare (în dreapta).

Dar acest lucru nu este încă foarte convenabil, deoarece coloanele aproape se îmbină. Prin urmare, vom efectua încă o acțiune suplimentară: faceți clic dreapta pe coloanele violet și selectați MODIFICARE TIP DE HABĂ PENTRU SERIE. Va apărea o fereastră în care selectăm o diagramă, chiar primul tip.

Obținem o diagramă foarte clară, care este o combinație între o histogramă și un grafic. Vedem că venitul maxim a fost în ianuarie și noiembrie, iar cel minim în august.

Construire diagrame combinate

În cazurile în care trebuie să afișați date de diferite scale pe o singură diagramă, este util să utilizați diagrame combinate. Un caz tipic este indicatorii absoluti (ruble) și relativi (procentaj). De exemplu, dimensiunea conturilor de încasat și cota sa din vânzări.

Combinate (sau amestecat ) este o diagramă care constă din mai multe serii de date și utilizează diferite tipuri de diagrame în același timp (de exemplu, o histogramă și un grafic). Pentru a crea diagrame combinate, aveți nevoie de cel puțin două serii de date. Exemple de diagrame mixte sunt postate în carte Combinate

Există câteva restricții privind construirea diagramelor combinate:

· este imposibil să amesteci orice tip de diagramă cu tipuri volumetrice;

· unele combinații de tipuri de diagrame arată extrem de nesatisfăcător (de exemplu, o combinație între un grafic și o diagramă radar);

· o diagramă mixtă utilizează o singură zonă de trasare, prin urmare este imposibil să se creeze, de exemplu, o diagramă combinată constând din trei diagrame circulare;

· Combinarea unui grafic și a unei diagrame cu bare nu este acceptată: axa categoriei unei diagrame cu bare este întotdeauna direcționată vertical, iar axa unui grafic este întotdeauna direcționată orizontal.

Figura prezintă o diagramă construită folosind trei serii de date. Mai mult, temperaturile aerului și apei sunt prezentate sub forma unei histograme, iar cantitatea de precipitații este prezentată într-un grafic.

Figura prezintă o histogramă construită folosind două serii de date:

Să schimbăm tipul de diagramă pentru a doua serie de date ( Precipitare) și utilizați o axă valorică separată pentru această serie.

1. Selectați o serie de date pe diagramă Precipitareși apelați meniul contextual al rândului.

2. Deschide fereastra Format de serie de date (Format seria de date) iar pe filă Opțiuni rând (Opțiuni de serie) instalați comutatorul De auxiliar topoare(Axa secundară).

3. Fără a elimina selecția din rând, executați comanda Lucrul cu diagrame– Constructor – Tip – Schimba tip diagrame(Diagramă Instrumente – Proiecta – Tip – Schimba Diagramă Tip) .

4. În caseta de dialog Modificare tip diagramă, selectați tipul Programa (Linia) și faceți clic pe butonul Bine.

Datele despre precipitații sunt acum vizualizate ca segmente de linie dreaptă, cu unul nou în dreapta Axa valorii (ValueOxis).

Este important să înțelegeți că echipa Lucrul cu diagrame– Constructor – Tip – Schimba tip diagrame (Diagramă Instrumente – Proiecta – Tip – Schimba Diagramă Tip) funcționează diferit în funcție de ceea ce este selectat. Dacă este selectată o serie de diagrame, comanda schimbă doar tipul acelei serii. Dacă este selectat orice alt element de diagramă, comanda schimbă tipul întregii diagrame.

Suprapunere diagrame (OverlayCharts)

Suprapunerea diagramelor se referă la plasarea diagramelor ca obiecte grafice: una peste alta. O diagramă de sus are de obicei o zonă de diagramă transparentă și o zonă de trasare. Îndepărtează toate elementele, cu excepția, de exemplu, a marcajelor și a liniilor. Suprapunerea diagramei este o lucrare manuală care necesită poziționarea precisă a obiectelor, configurarea axelor valorice și ale categoriilor.

Pentru a aranja diagramele „stivuite” în ordinea corespunzătoare, acestea trebuie selectate ca obiecte. Pentru a selecta un obiect mai degrabă decât o diagramă, apăsați Ctrlși faceți clic pe diagramă.

Să ne uităm la un exemplu simplu (foaia Suprapunere_1 cărți Combinate). Se știe că una dintre limitările construcției de diagrame combinate este incapacitatea de a amesteca orice tip de diagramă cu tipuri volumetrice. Metoda de suprapunere a diagramei vă permite să combinați un grafic și o histogramă volumetrică pe o foaie. Să facem următorii pași:

1. Selectați un interval din tabelul de date A2:B7și construiți o histogramă volumetrică cu grupare. Să eliminăm legenda diagramei.

2. Selectați intervale neadiacente A2:A7Şi C2:C7și construiți un grafic cu markeri. Să eliminăm legenda diagramei.

3. Pe a doua diagramă, setați o umplere transparentă pentru zona diagramei și zona de trasare a diagramei și ștergeți axele. Să formatăm liniile și marcajele graficului.

4. Combinați manual graficul și histograma volumetrică. Schimbând dimensiunea obiectului „Graph”, ne vom asigura că marcatorii graficului coincid cu mijlocul coloanelor histogramei.

5. După formatarea finală vom obține o diagramă suprapusă.

6. Dacă, după selectarea a două diagrame ca obiecte grafice, le grupați, diagrama suprapusă rezultată va fi copiată și mutată ca un singur obiect. Rețineți că încercarea de a plasa diagrama pe o foaie separată va distruge suprapunerea.

Următoarea ilustrație arată o suprapunere a unei diagrame 3-D și a unei histograme 3-D. Cadrul general dă impresia că este o singură diagramă. Cu toate acestea, acestea sunt două diagrame separate. Cadrul nu le aparține, ci celulelor foii de lucru. Titlul este o inscripție care se poate deplasa liber. Datele pentru o astfel de suprapunere sunt date pe foaie Circular _volumetric cărți Combinate.

Histogramele volumetrice Excel nu vă permit să afișați rânduri suplimentare în profunzime. Suprapunerea graficelor depășește această limitare. Este suficient să construiți trei histograme volumetrice cu acumulare (pentru fiecare an separat) și să le combinați astfel încât să „creați” o a treia axă în profunzime. Graficul vă permite să comparați vizual rezultatele după trei parametri - regiuni, luni și ani. Tabelul de date original este în foaia de lucru Trei_volume cărți Combinate.