Diagrama termometrului Excel - Descărcare gratuită și cum se creează

Acest tutorial va arăta cum să creați o diagramă a termometrului în toate versiunile de Excel: 2007, 2010, 2013, 2016 și 2022.

O diagramă cu termometru este o modificare specială a unei diagrame cu coloane stivuite. Coloanele sunt așezate una peste cealaltă și seamănă vizual cu un termometru, care se umple pe măsură ce avansați către obiectivul dvs.

De fiecare dată când trebuie să urmăriți progresul către un obiectiv - fie că este vorba de finanțare, vânzări, venituri sau alte valori relevante - acest grafic oferă un mod simplu și eficient de a vizualiza aceste date.

Dar o diagramă simplă a termometrului nu mai taie muștarul (haide, acesta este 2022!).

În acest tutorial detaliat, pas cu pas, veți învăța cum să trasați o diagramă a obiectivelor termometrului remarcabil de versatilă, cu o etichetă fantezie, care funcționează chiar și atunci când rezultatele dvs. depășesc obiectivul stabilit.

Doar verificați-l:

După cum veți vedea în scurt timp, construirea unui grafic impresionant de termometru necesită eforturi. De aceea, am proiectat programul de completare Chart Creator, un instrument pentru crearea diagramelor Excel avansate în doar câteva clicuri.

Așadar, haideți să trecem la nitty-gritty.

Noțiuni de bază

În scop ilustrativ, imaginați-vă că ați lansat un magazin de comerț electronic ca o agitație laterală și doriți să construiți un grafic cu termometru pentru a urmări performanța magazinului în raport cu obiectivul dvs. de venituri. În 2022, ai avut o cursă destul de bună.

Magazinul a generat venituri de 21.734 USD, depășind ușor obiectivul dvs. inițial (puteți scoate o sticlă de șampanie mai târziu).

Acestea fiind spuse, aruncați o privire asupra setului de date pentru grafic:

Înainte de a trece la construirea diagramei, trebuie să descompunem fiecare punct de date, astfel încât să puteți urmări cu ușurință pașii folosind datele dvs.

Setul de date are două coloane:

• Date originale: Această coloană reprezintă datele dvs. reale, oricare ar fi acestea. Lasă-l așa cum este.
• Intrări grafice: Aici se întâmplă toată magia. Tabelul conține toate valorile speciale pe care le veți utiliza pentru construirea diagramei. De asemenea, o notă importantă: categoriile trebuie comandate exact în același mod ca în imaginea de mai sus!

S-ar putea să vă întrebați de unde provin toate valorile din al doilea tabel. Ei bine, de fapt va trebui să le calculați singur folosind formulele speciale Excel enumerate mai jos (surpriză, surpriză).

Din păcate, nu există nici o altă cale de rezolvare - dar instrucțiunile de mai jos ar trebui să fie ușor de urmat.

Așa că începe călătoria!

Pasul 1: configurați masa de ajutor.

Întrucât știftul ține totul împreună, fiecare dintre elementele din al doilea tabel își are scopul. Să-i acoperim mai detaliat pe acești băieți răi.

Venituri vizate: Acest punct de date trebuie să se potrivească cu valoarea obiectivului (B7). Simplu ca buna ziua.

Formula veniturilor totale: Această funcție IF împiedică marcatorii de date să se stivuiască unul peste celălalt într-un mod greșit în cazul în care rezultatul depășește obiectivul:

1	= IF (B6 <= B7, B6, B7)

Iată versiunea decodificată bazată pe datele originale:

1	= IF ({Total} <= {Goal}, {Total}, {Goal})

Traducerea în limbajul uman de bază: dacă rezultatul (B6) este mai mic sau egal cu obiectivul (B7), formula returnează valoarea rezultatului (B6) -în cazul nostru, orice se încadrează sub marca de 20.000 USD. În schimb, dacă rezultatul (B6) depășește obiectivul (B7), formula returnează valoarea obiectivului (B7).

Sfat rapid: faceți clic pe „Format număr contabil ” buton (Fila Acasă > Grup de numere) pentru a afișa funcția de ieșire ca monedă.

Suprasolicitat: Acest Funcția IF calculează dacă ținta dvs. este depășită sau nu. Ori de câte ori are loc, formula scade obiectivul (B7) din rezultat (B6) și returnează ceea ce a rămas. În caz contrar, formula arată pur și simplu zero:

1	= IF (B7 <= B6, B6-B7, 0)

Versiunea decodificată:

1	= IF ({Goal} <= {Total}, {Total} - {Goal}, 0)

Progres: Acesta este destul de auto-explicativ. Trebuie să măsurăm progresul ca procent din obiectivul etichetei noastre ultra-fanteziste. Pur și simplu împărțiți rezultatul (B6) după obiectiv (B7) și formatați rezultatul ca procent, faceți clic pe „Stil procentual ” buton (Fila Acasă > Grup de numere).

1

= B6 / B7

Versiune decodificată:

1	= {Total} / {Obiectiv}

Eticheta: Cireasa de pe tort. Acesta combină valorile numerice și procentuale care reprezintă progresul către obiectivul dvs. și le formează cu atenție. Pentru a-l scoate, vom trece la funcția TEXT (începători Excel, aparat pentru impact):

1	= TEXT (B6, "$ # 0.00") & "(" & TEXT (E5, "## 0%") & ")"

Și versiunea decodificată:

1	= TEXT ({Total}, "{Formatează valoarea ca monedă}") & "(" & TEXT ({Progres}, "{Formatează valoarea ca procente}") & ")"

Dacă acest lucru pare descurajant, consultați articolul legat mai sus care acoperă funcția.

Iată cum arată în practică:

Vai, cel mai rău este în spatele nostru. Pentru a rezuma totul, aruncați o a doua privire asupra formulelor:

Setarea graficului	Formulă
Formula veniturilor totale	= IF (B6 <= B7, B6, B7)
Suprasolicitat	= IF (B7 <= B6, B6-B7, 0)
Progres	= B6 / B7
Eticheta	= TEXT (B6, „$ # 0,00 ″) &” (”& TEXT (E5,„ ## 0% ”) și„) ”

Odată ajuns acolo, treceți la pasul următor.

Pasul 2: creați o diagramă de coloane stivuite.

1. Evidențiați gama de celule E2: E4 în Valoare
2. Selectează Introduce
3. Apasă pe "Introduceți o coloană sau o diagramă cu bare”Pictogramă.
4. Alegeți „Coloană stivuită.”

Pasul 3: Stivați seria de date una peste alta.

După ce apare graficul, rearanjați seria de date:

1. Faceți clic pe diagramă.
2. Treceți la Proiecta
3. Alegeți „Comutați rând / coloană.”

Pasul 4: schimbați culorile markerului de date.

Pentru a transforma graficul în ceva asemănător unui termometru, începeți prin schimbarea culorii markerelor de date. Mai întâi, creați marcatorul de date pentru seria 1 „Venituri țintă” (E2) transparent și puneți linii de frontieră în jurul său.

Faceți clic dreapta pe marcatorul de date care reprezintă „Venit țintă” (E2-secțiunea de jos a graficului) și selectați „Formatează seria de date.”

În panoul de activități care apare, efectuați următoarele:

1. Navigați la Fill & Line
2. Sub "Completati," Selectați "Fără umplere.”
3. Sub "Frontieră, ”Alege”Linie solida.”
4. Sub "Frontieră," apasă pe Culoare umplere pictogramă pentru a deschide paleta de culori și a selecta roșu.
5. Seteaza Lăţime valoare pentru 2 pct.

Colorează celelalte markere de date după cum urmează:

1. Pentru seria 2 „Formula veniturilor totale” (E3):
   1. Selectați markerul de date (E3) cu Formatează seria de date panoul de activități este încă deschis.
   2. Sub "Completati, ”Alege”Umplere solidă.”
   3. Apasă pe Culoare umplere pictogramă și selectați roșu.
2. Pentru seria 3 „Suprasolicitat” (E4):
   1. Selectați markerul de date (E4) cu Formatează seria de date panoul de activități este încă deschis.
   2. Sub "Completati, ”Alege”Umplere solidă.”
   3. Apasă pe Culoare umplere pictogramă și selectați verde deschis.

Pasul 5: adăugați eticheta de date în grafic.

Acum este timpul să introduceți eticheta de date minunată (E6) care ne-a luat atât de mult sânge, sudoare și lacrimi pentru a le pune împreună.

Faceți clic dreapta pe "Formula veniturilor totale" din seria 2 (cel cu umplutură roșie) și alegeți „Adăugați etichete de date.”

Pasul 6: Modificați valoarea etichetei de date.

Conectați eticheta la valoarea din al doilea tabel.

1. Faceți dublu clic pe etichetă.
2. Tip “=” în Bara de formule.
3. Selectați valoarea etichetei (E6).

Acum trebuie să-l sporim puțin. Faceți eticheta îndrăzneață, colorați-o în alb și modificați dimensiunea fontului pentru a îmbunătăți elementul din punct de vedere estetic (Fila Acasă > Grup de fonturi).

Pasul 7: Repoziționați eticheta de date.

După ce ați configurat eticheta, împingeți-o până la capătul superior al markerului de date aferent. Faceți clic dreapta pe eticheta de date și alegeți „Formatați etichete de date.”

Apoi, în Opțiuni pentru etichete filă, sub Poziția etichetei, alege „Inside End.”

Pasul 8: Mutați seria 1 „Venit țintă” pe axa secundară.

Pentru a modela graficul într-o formă de termometru, trebuie să obțineți Seria 1 „Venituri țintă” (E2) în poziția corectă.

1. Faceți clic dreapta pe Seria 1 „Venituri țintă” (cel transparent în partea de jos).
2. Alegeți „Formatează seria de date.”

În panoul de activități care apare, sub Plot Series On, alege „Axa secundară.”

Pasul 9: Eliminați axa secundară.

Axa secundară și-a făcut treaba, așa că nu mai avem nevoie de ea.

Faceți clic dreapta pe axa secundară (coloana numerelor din dreapta) și selectați „Șterge.”

Pasul 10: Modificați intervalele scalei axei primare.

După cum probabil ați observat, scala axei primare arată puțin dezactivată. Să schimbăm asta.

Faceți clic dreapta pe scala axei principale și alegeți „Formatează axa.”

Seteaza Limite minime valoare pentru -10000. De ce? Acest lucru va elibera spațiu de mai jos pentru becul termometrului.

Această valoare diferă în funcție de datele grafice. O regulă bună este să folosiți numărul obiectivului dvs. și împărțiți-l la doi.

De exemplu, să presupunem că obiectivul dvs. de venituri este de 100.000 USD. Apoi Limite minime valoarea ar fi -50000.

Pasul 11: Schimbați formatul numeric al scalei axei primare și adăugați marcaje.

Aceste numere negative nu ne ajută aici. Din fericire, putem scăpa cu ușurință de ele folosind puterea formatelor de numere personalizate.

Cu Formatează axa panoul de activități este încă deschis, sub Opțiuni Axis, navigați la Numere secțiunea și efectuați următoarele:

1. Pentru "Categorie," Selectați ""
2. Tip "$#,##0.00;;$0" în Cod format câmp și faceți clic Adăuga.

De asemenea, sub Bifează, alege „Interior" de la "Tip major”Meniul derulant.

Acum, treceți la Fill & Line filă. Schimbați culoarea marcajelor în negru și setați Lăţime valoare pentru 1,5 pct.

Pasul 12: Eliminați titlul graficului, liniile grilei și axa orizontală.

Eliminați elementele diagramei care nu au valoare practică: titlul diagramei, liniile grilei și axa orizontală. Faceți clic dreapta pe fiecare element și selectați „Șterge.”

Pasul 13: Modificați lățimea spațiului seriei de date pe axa secundară.

Pe măsură ce puneți ultimele atingeri pe graficul dvs., slăbiți tubul termometrului. Începeți cu seria plasată pe axa secundară (seria 1 „Venituri vizate”).

Faceți clic dreapta pe seria „Venituri țintă” din seria 1 (E2), deschide Formatează seria de date panoul de activități și setați fișierul Lățimea golului valoare pentru 250%. Măriți procentajul dacă doriți să-l reduceți mai mult.

Pasul 14: Modificați lățimea spațiului seriei de date pe axa primară.

După aceea, faceți clic din nou pe marginea tubului roșu pentru a selecta Seria 2 „Formula veniturilor totale” (E3), plasat pe axa primară. Setează la fel Lățimea golului valoare (250%).

Pasul 15: Adăugați becul termometrului în diagramă.

Ca o ajustare finală, creați și adăugați becul termometrului în diagramă.

1. Treceți la Introduce
2. Alege "Forme”Buton.
3. Alegeți „Oval.”

Țineți apăsat Schimb în timp ce desenezi pentru a crea un cerc perfect. Apoi faceți clic dreapta pe cerc și, în meniul care apare, modificați Schiță de formă și Forma umplere culori la roșu.

Doar un ultim lucru. Copiați cercul selectându-l și apăsând Ctrl + C. Apoi selectați graficul și apăsați Ctrl + V pentru a lipi cercul în diagrama termometrului. În cele din urmă, redimensionați și reglați poziția becului pentru a-l încadra în imagine.

Deci, iată-l. Acum sunteți gata să creați diagrame uimitoare cu termometru în Excel!