Cum se creează un complot Stem-and-Leaf în Excel

Acest tutorial va arăta cum să creați un grafic de tulpini și frunze în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2022.

Un afișaj cu tulpină și frunză (cunoscut și ca stemplot) este o diagramă concepută pentru a vă permite să evaluați rapid distribuția unui set de date dat. Practic, complotul împarte numerele din două cifre în jumătate:

• Tulpini - Prima cifră
• Frunze - A doua cifră

De exemplu, uitați-vă la graficul de mai jos. Graficul afișează defalcarea în funcție de vârstă a unei populații mici. Tulpina (neagră, axa y) arată prima cifră a vârstei, în timp ce punctele roșii de date arată a doua cifră.

Puteți vedea rapid că există șase persoane în vârsta de douăzeci de ani, care este a doua grupă de vârstă cea mai populată.

Cu toate acestea, diagrama nu este acceptată în Excel, ceea ce înseamnă că va trebui să o construiți manual de la capăt. Consultați programul nostru de completare pentru Creator de diagrame, un instrument care vă permite să alăturați diagrame impresionante Excel avansate în doar câteva clicuri.

În acest tutorial pas cu pas, veți învăța cum să creați un grafic dinamic tulpină și frunză în Excel de la zero.

Noțiuni de bază

În scop ilustrativ, să presupunem că aveți 24 de puncte de date care conțin vârsta clienților dvs. Pentru a vizualiza ce grupe de vârstă se deosebesc de mulțime, v-ați propus să construiți un model:

Tehnica pe care urmează să o învățați este viabilă chiar dacă setul dvs. de date conține sute de valori. Dar pentru a-l scoate, trebuie mai întâi să puneți bazele.

Deci, să ne scufundăm chiar înăuntru.

Pasul 1: sortați valorile în ordine crescătoare.

Pentru început, sortați datele dvs. reale în ordine crescătoare.

1. Selectați orice celulă din intervalul setului de date (A2: A25).
2. Du-te la Date filă.
3. Apasă pe "Fel”Buton.
4. În fiecare meniu derulant, sortați după următoarele:
   1. Pentru "Coloană," Selectați "Vârsta clientului”(Coloana A).
   2. Pentru "Sortează pe," Selectați "Valori ”/„ Valori celulare.”
   3. Pentru "Ordin," Selectați "De la cel mai mic la cel mai mare.”

Pasul 2: configurați o masă de ajutor.

Odată ce coloana de date a fost sortată, configurați un tabel de ajutor separat pentru stocarea tuturor datelor grafice după cum urmează:

Câteva cuvinte pe fiecare element al tabelului:

• Stem (coloana C) - Aceasta va conține prima cifră a tuturor vârstelor.
• Frunza (coloana D) - Aceasta va conține a doua cifră a tuturor vârstelor.
• Poziția frunzei (coloana E) - Această coloană de ajutor va ajuta la poziționarea frunzelor pe diagramă.

Pasul 3: Găsiți valorile stem.

Mai întâi, calculați Tulpina valori (Coloana C) folosind funcțiile STÂNGA și VALOARE. Funcția STÂNGA - care returnează numărul specificat de caractere de la începutul unei celule - ne va ajuta să extragem prima cifră din fiecare valoare, în timp ce funcția VALUE formată rezultatul formulei ca număr (este crucial).

Introduceți această formulă în celulă C2:

1	= VALOARE (STÂNGA (A2,1))

După ce ați găsit prima valoare Stem, trageți mânerul de umplere în partea de jos a coloanei pentru a executa formula pentru celulele rămase (C3: C25).

Pasul 4: Găsiți valorile Leaf.

Următorul nostru pas este găsirea valorilor pentru Frunze coloana (Coloana D) trăgând ultima cifră din fiecare număr din coloana de date originală (coloana A). Din fericire, funcția DREAPTA poate face treaba murdară pentru dvs.

Tastați următoarea funcție în celulă D2:

1	= DREAPTA (A2,1)

Odată ce ai formula în celulă, trage-o pe restul celulelor (D3: D25).

Pasul 5: Găsiți valorile poziției frunzei.

Deoarece un grafic de împrăștiere va fi utilizat pentru construirea afișajului cu tulpină și frunză, pentru a face totul să cadă în locul său, trebuie să atribuiți fiecărei frunze un număr care să indice poziția sa pe diagramă cu ajutorul funcției COUNTIF.

Introduceți această formulă în celulă E2:

1	= COUNTIF ($ C $ 2: C2, C2)

În engleză simplă, formula compară fiecare valoare din coloană Tulpina (Coloana C) între ele pentru a detecta și marca aparițiile duplicate, atribuind în mod eficient identificatori unici frunzelor care au o tulpină comună.

Din nou, copiați formula în restul celulelor (E3: E25).

Pasul 6: Construiți un grafic XY scatter.

Acum ați adunat toate piesele de puzzle necesare pentru a crea un complot de împrăștiere. Să le punem împreună.

1. Evidențiați toate valorile din coloane Tulpina și Poziția frunzei selectând celulele de date din Coloana C, apoi menținând apăsat butonul Control în timp ce selectați celulele de date din coloana E, lăsând în afara celulele rândului antet (C2: C25 și E2: E25). NOTĂ: Nu selectați coloana D în acest moment.
2. Du-te la Introduce filă.
3. Apasă pe "Introduceți Scatter (X, Y) sau diagramă cu bule”Pictogramă.
4. Alegeți „Împrăștia.”

Pasul 7: Schimbați valorile X și Y.

Acum, poziționați axa orizontală responsabilă pentru afișarea tulpinilor pe verticală. Faceți clic dreapta pe graficul grafic și alegeți „Selectați Date”Din meniul care apare.

Apoi, faceți clic pe „Editați | ×”Buton.

Odată ajuns acolo, trebuie să modificați manual valorile X și Y:

1. Pentru "Valorile seriei X,”Selectați toate valorile din coloană Poziția frunzei (E2: E25).
2. Pentru "Valorile seriei Y,”Evidențiați toate valorile din coloană Tulpina (C2: C25).
3. Faceți clic pe „Bine”Pentru caseta de dialog Edit Series.
4. Faceți clic pe „Bine”Pentru caseta de dialog Select Data Source.

Pasul 8: Modificați axa verticală.

După ce ați rearanjat graficul, trebuie să îl întoarceți din nou pentru a sorta tulpinile în ordine crescătoare. Faceți clic dreapta pe axa verticală și alegeți „Formatează axa.”

Odată ajuns acolo, urmați acești pași simpli:

1. Navigați la Opțiuni Axis filă.
2. Schimba Limite maxime valoare la „6”Pentru că cel mai mare număr din setul de date este 60.
3. Seteaza Unități majore valoare la „1.”
4. Verifică "Valori în ordine inversă" cutie.

Pasul 9: Adăugați și modificați semnele de bifare a axei.

Marcajele plasate de-a lungul axei verticale vor fi folosite ca separator între tulpini și frunze. Fără a închide „Formatează axa”, Derulați în jos până la Bifează secțiunea și lângă „Tip major, ”Alege”Interior.”

Faceți marcajele să iasă în evidență schimbându-le culoarea și lățimea.

1. Navigați la Fill & Line filă.
2. Sub Linie, faceți clic pe „Culoare umplere”Pentru a deschide paleta de culori și a alege negru.
3. Seteaza Lăţime la "3 pct.”

Acum puteți elimina axa orizontală și liniile de grilă. Doar faceți clic dreapta pe fiecare element și alegeți „Șterge.”De asemenea, măriți fontul numerelor stem și faceți-le îndrăznețe, astfel încât să fie mai ușor de văzut (selectați axa verticală și navigați la Acasă > Font).

Pasul 10: adăugați etichete de date.

Pe măsură ce vă îndreptați spre linia de sosire, să adăugăm frunzele în diagramă. Pentru a face acest lucru, faceți clic dreapta pe orice punct reprezentând Seria „Seria 1” și alegeți „Adăugați etichete de date.”

Pasul 11: Personalizați etichetele de date.

Odată ajuns acolo, scăpați de etichetele implicite și adăugați valorile din coloană Frunze (Coloana D) in schimb. Faceți clic dreapta pe orice etichetă de date și selectați „Formatați etichete de date.”

Când apare panoul de activități, urmați câțiva pași simpli:

1. Treceți la Opțiuni pentru etichete filă.
2. Verifică "Valoare din celule" cutie.
3. Evidențiați toate valorile din coloană Frunze (D2: D25).
4. Faceți clic pe „Bine.”
5. Debifați „Valoarea Y" cutie.
6. Debifați „Afișați Liniile Leader" cutie.

Schimbați culoarea și dimensiunea fontului frunzelor pentru a le diferenția de tulpini și nu uitați să le faceți și îndrăznețe (Acasă > Font).

Pasul 12: ascundeți markerii de date.

Marcatorii de date (punctele) v-au servit bine, dar nu mai aveți nevoie de ei, cu excepția ca mijloc de a poziționa etichetele de date. Deci, să le facem transparente.

Faceți clic dreapta pe orice punct care ilustrează Seria „Seria 1” și selectați „Formatează seria de date.”

Odată ce panoul de activități apare, faceți următoarele:

1. Apasă pe "Fill & Line”Pictogramă.
2. Treceți la Marker filă.
3. Sub "Opțiuni de marcare,”Alege”Nici unul.”

Și nu uitați să schimbați titlul graficului.

Pasul 13: Adăugați titlurile axei.

Utilizați titlurile axelor pentru a eticheta ambele elemente ale diagramei.

1. Selectați graficul grafic.
2. Du-te la Proiecta filă.
3. Faceți clic pe „Adăugați un element grafic.”
4. Selectați "Titlurile axei.”
5. Alegeți „Primar orizontal" și "Vertical primar.”

După cum puteți vedea, titlurile axelor se suprapun graficului graficului. Pentru a remedia problema, selectați graficul graficului și reglați mânerele pentru a redimensiona suprafața graficului. Acum puteți schimba titlurile axelor.

Pasul 14: Adăugați o casetă text.

În cele din urmă, adăugați o casetă text cu o cheie pentru a ușura citirea stemplotului.

1. Selectați graficul grafic.
2. Du-te la Introduce filă.
3. În Text grup, alegeți „Casetă de text.”

În caseta text, dați un exemplu din datele dvs. explicând modul în care funcționează graficul și sunteți gata!