Cum să creați o diagramă în trepte în Excel – Automate ExcelAutomate Excel

Acest tutorial va demonstra cum să creați o diagramă în trepte în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2019.

Graficele în trepte – care sunt utilizate în mod obișnuit pentru a analiza fluctuațiile de preț și sunt croite din aceeași stofă ca și graficele cu linii – vin în ajutor atunci când aveți nevoie să afișați în mod clar salturi cantitative regulate sau sporadice în valori, prețuri, rate ale dobânzii și așa mai departe.

Cu toate acestea, deoarece graficul nu este suportat în Excel, va trebui să depuneți un efort pentru a-l trasa singur. Pentru a vă economisi timp, am dezvoltat Add-in-ul Chart Creator, un instrument ușor de utilizat pentru a crea diagrame avansate în Excel în câteva secunde.

În acest tutorial pas cu pas, veți învăța cum să construiți un grafic în trepte simplu de la zero în Excel:

Începem

În scop ilustrativ, să presupunem că sunteți pe cale să lansați o campanie masivă de marketing pentru brandul dvs. și trebuie să vă dați seama unde să vă plasați banii de publicitate în 2020.

În timp ce treceți prin diferite canale de marketing în căutarea celei mai performante opțiuni, v-ați propus să trasați un grafic în trepte care să afișeze costul de achiziție a clienților (CAC) de e-mail marketing din 2019, metrica pivot care măsoară costul eforturilor de marketing necesare pentru a câștiga un nou client.

Acestea fiind spuse, iată un exemplu de tabel care rezumă performanța canalului de marketing în 2019:

În afaceri, timpul înseamnă bani, așa că haideți să începem.

Pasul nr. 1: Clonarea tabelului de date original.

Din moment ce construirea unui grafic în trepte presupune o mare cantitate de manipulare a datelor, creați o copie a tabelului care conține datele originale pentru a păstra lucrurile mai organizate.

Etapa 2: Duplică toate valorile din tabelul clonat.

Ca o soluție de lucru pentru transformarea unei diagrame liniare într-o diagramă în trepte, în tabelul nou creat, configurați două seturi identice de date care conțin aceleași valori.

Pentru a face acest lucru, evidențiați toate valorile din cel de-al doilea tabel și apăsați Ctrl + C pentru a copia celulele selectate. Apoi, selectați prima celulă goală de sub tabel (în cazul nostru, D14) și apăsați Ctrl + V pentru a lipi datele.

Iată cum arată în practică:

Pasul 3: Sortați tabelul după coloana de date, de la cea mai veche la cea mai nouă.

Este timpul să puneți bazele pentru etichetele de date personalizate pe care le veți crea mai târziu.

1. Subliniați întregul tabel clonat (D1:E25).
2. Navigați la fila Data.
3. În grupul Sort & Filter, selectați butonul „Sort”.
4. În fiecare meniu derulant, sortați după următoarele:
   1. Pentru „Column” (Coloană), selectați „Month” (Coloana D).
   2. Pentru „Sort On” (Sortare pe), selectați „Values” / „Cell Values” (Valori).”
   3. Pentru „Order” (Ordine), selectați „Oldest to Newest” (De la cel mai vechi la cel mai nou).”
5. Click pe OK pentru a închide caseta de dialog.

Pasul 4: Deplasați valorile de cost cu o celulă în jos.

Această tehnică simplă vă va scuti de deranjul de a tasta manual valorile pentru fiecare dată.

Subliniați toate valorile din coloana CAC (coloana E) din tabelul duplicat. Apoi deplasați totul cu o celulă în jos, apăsând Ctrl + X pentru a tăia datele, apoi Ctrl + V în următoarea celulă de jos (E3) pentru a le lipi în noua poziție.

Etapa #5: Eliminați primul și ultimul rând din tabelul duplicat.

După aceasta, eliminați primul și ultimul rând al celui de-al doilea tabel și mutați datele rămase sub titlurile coloanelor, astfel încât coloanele să fie din nou egale.

Subliniați celulele D2 și E2, faceți clic dreapta și selectați Delete. În caseta de dialog, alegeți Mutarea celulelor în sus. Faceți același lucru pentru celula E25.

Pasul 6: Proiectați etichetele de date personalizate.

Pasul următor este proiectarea etichetelor de date personalizate doar pentru valorile din grafic care reflectă datele originale, lăsând în afară punctele de date ajutătoare, care țin totul împreună.

În primul rând, configurați o nouă categorie de date numită „Etichetă” în coloana adiacentă celui de-al doilea tabel (în cazul nostru, coloana F).

După aceasta, pentru a crea etichetele care conțin datele împerecheate cu valorile corespunzătoare, trebuie să dezlănțuiți puterea funcțiilor IF, CHAR și TEXT. Acest cocktail special de funcții Excel ne va ajuta să ducem la bun sfârșit această sarcină:

Pentru cei neinițiați, iată varianta decodificată a formulei:

În limbaj simplu, formula compară valorile E1 și E2 între ele, iar dacă nu se potrivesc, celula F2 se umple cu eticheta personalizată care afișează atât luna, cât și costul corespunzător. Funcția CHAR(10) rămâne o constantă și reprezintă o pauză de linie. În caz contrar, formula returnează o valoare goală.

Iată cum arată atunci când cauciucul atinge drumul:

Acum, trageți mânerul de umplere din colțul din dreapta jos al celulei selectate până în partea de jos a coloanei F pentru a executa formula pentru celulele rămase (F3:F24).

Pasul nr. 7: Creați un grafic liniar.

Toate obstacolele au fost acum eliminate. Este timpul să creăm o diagramă liniară simplă și să vedem ce se întâmplă.

1. Subliniați toate celulele care conțin valorile datei și ale costurilor (D2:E24).
2. Direcționați-vă la fila Insert.
3. Click pe pictograma „Insert Line or Area Chart”.
4. Alegeți „Line.”

Miraculos, apare o diagramă în trepte! Cu toate acestea, merită menționat un avertisment. Să presupunem că v-ați propus să analizați performanța marketingului prin e-mail doar pe parcursul a trei luni, în schimb:

După cum probabil ați observat, axa orizontală arată destul de dezordonat. Din fericire, remedierea problemei este la fel de ușoară ca și decojirea mazării. În primul rând, faceți clic dreapta pe axa orizontală (etichetele din partea de jos) și selectați „Format Axis.”

În panoul de sarcini care apare, faceți următoarele:

1. Treceți la fila Axis Options.
2. La rubrica Axis Type, selectați butonul radio „Date axis”.
3. La rubrica Units, setați valoarea Base la „Days/Month/Year”, în funcție de datele dvs. reale.

Acum, revenim la graficul nostru în trepte.

Etapa nr. 8: Adăugați etichetele de date.

În continuare, trebuie să adăugăm la graficul în trepte etichetele de date personalizate pe care le-ați creat anterior.

Click dreapta pe linia care ilustrează fluctuațiile de costuri și selectați „Add Data Labels”.”

Etapa #9: Adăugați etichetele de date personalizate și scăpați de valorile implicite ale etichetelor.

Click dreapta pe oricare dintre etichetele din diagramă și alegeți „Format Data Labels.”

După ce apare panoul de sarcini, faceți următoarele:

1. Accesați fila Label Options.
2. Decifrați caseta „Value from Cells” (Valoare din celule).
3. Subliniați toate valorile din coloana Label (F2:F24).
4. Click pe „OK” pentru a închide fereastra de dialog.
5. Decifrați caseta „Value” (Valoare) pentru a elimina etichetele implicite.

Etapa #10: Împingeți etichetele deasupra liniei graficului.

Am terminat aproape! Ca o ultimă atingere, trebuie să aliniați etichetele în locurile potrivite. Etichetele care reprezintă creșterile de costuri vor fi plasate deasupra liniei. În schimb, etichetele care afișează scăderi de costuri vor fi mutate în jos, sub linie.

În panoul de activități Format Data Labels, sub fila Label Options, derulați în jos până la Label Position și selectați opțiunea „Above.”

Etapa #11: Mutați etichetele care afișează scăderile de costuri sub linia graficului.

În cele din urmă, selectați una câte una etichetele care afișează scăderile de CAC și setați valoarea Label Position pentru fiecare dintre ele la „Below.”

Pentru o ultimă ajustare, faceți etichetele bold (fila Home > Font) și modificați titlul graficului. Graficul dvs. în trepte este gata.

Atunci iată-l: mai multe informații care duc la decizii mai bune, totul datorită graficului dvs. în trepte!

.