cum se creează o hartă de căldură în Excel – un ghid pas cu pas

o hartă de căldură în Excel este o reprezentare vizuală care vă arată rapid o vizualizare comparativă a unui set de date.

de exemplu, în setul de date de mai jos, pot observa cu ușurință care sunt lunile în care vânzările au fost scăzute (evidențiate cu roșu) în comparație cu alte luni.

în setul de date de mai sus, culorile sunt atribuite pe baza valorii din celulă. Scala de culori este de la verde la galben la roșu, cu valori ridicate obținând culoarea verde și valori scăzute obținând culoarea roșie.

crearea unei hărți de căldură în Excel

în timp ce puteți crea o hartă de căldură în Excel prin codarea manuală a culorilor celulelor. Cu toate acestea, va trebui să o refaceți atunci când valorile se schimbă.

în loc de lucrul manual, puteți utiliza formatarea condiționată pentru a evidenția celulele pe baza valorii. În acest fel, în cazul în care modificați valorile din celule, culoarea/formatul celulei va actualiza automat harta de căldură pe baza regulilor pre-specificate în formatarea condiționată.

în acest tutorial, veți învăța cum să:

• creați rapid o hartă de căldură în Excel folosind formatarea condiționată.
• creați o hartă dinamică de căldură în Excel.
• creați o hartă de căldură în tabelele Pivot Excel.

Să începem!

crearea unei hărți termice în Excel utilizând formatarea condiționată

Dacă aveți un set de date în Excel, puteți evidenția manual punctele de date și puteți crea o hartă termică.

cu toate acestea, aceasta ar fi o hartă de căldură statică, deoarece culoarea nu s-ar schimba atunci când modificați valoarea dintr-o celulă.

prin urmare, formatarea condiționată este modalitatea corectă de a merge, deoarece face ca culoarea dintr-o celulă să se schimbe atunci când modificați valoarea din ea.

Să presupunem că aveți un set de date așa cum se arată mai jos:

iată pașii pentru a crea o hartă de căldură folosind aceste date:

• selectați setul de date. În acest exemplu, ar fi B2:D13.
• mergeți acasă –> Formatare condiționată –> scale de culoare. Acesta arată diferite combinații de culori care pot fi utilizate pentru a evidenția datele. Cea mai obișnuită scală de culori este prima în care celulele cu valori ridicate sunt evidențiate în verde și scăzute în roșu. Rețineți că, pe măsură ce plasați mouse-ul peste aceste scale de culori, puteți vedea previzualizarea live în setul de date.
  

aceasta vă va oferi o hartă de căldură așa cum se arată mai jos:

în mod implicit, Excel atribuie culoarea roșie la cea mai mică valoare și culoarea verde la cea mai mare valoare, iar toate valorile rămase pe baza valorii. Deci, există un gradient cu nuanțe diferite ale celor trei culori pe baza valorii.

acum, ce se întâmplă dacă nu doriți un gradient și doriți doar să afișați roșu, galben și verde. De exemplu, doriți să evidențiați toate valorile mai mici decât să spunem 700 în roșu, indiferent de valoare. Deci, 500 și 650 au aceeași culoare roșie, deoarece este mai mică de 700.

pentru a face acest lucru:

• du –te la Acasă –> Formatare condiționată –> scale de culoare – > mai multe opțiuni.
• în noua casetă de dialog regulă de formatare, selectați „scară de 3 culori” din meniul derulant stil Format.
• acum Puteți specifica valoarea minimă, mijlocie și maximă și să îi atribuiți culoarea. Deoarece dorim să evidențiem toate celulele cu o valoare sub 700 în roșu, schimbați tipul La Număr și valoare la 700.
• Faceți clic pe OK.

acum veți obține rezultatul așa cum se arată mai jos. Rețineți că toate valorile sub 700 au aceeași nuanță de culoare roșie.

Un cuvânt de precauție: în timp ce formatarea condiționată este un instrument minunat, din păcate, este Volatil. Aceasta înseamnă că ori de câte ori există vreo modificare în foaia de lucru, formatarea condiționată este recalculată. În timp ce impactul poate fi neglijabil asupra seturilor de date mici, poate duce la un registru de lucru Excel lent atunci când lucrați cu seturi de date mari.

crearea unei hărți termice dinamice în Excel

deoarece formatarea condiționată depinde de valoarea dintr-o celulă, de îndată ce modificați valoarea, formatarea condiționată se recalculează și se modifică.

Acest lucru face posibilă realizarea unei hărți dinamice a căldurii.

să ne uităm la două exemple de creare a hărților de căldură folosind controale interactive în Excel.

Exemplul 1: Harta căldurii folosind bara de defilare

Iată un exemplu în care harta căldurii se schimbă imediat ce utilizați bara de defilare pentru a schimba anul.

Acest tip de hărți de căldură dinamice pot fi utilizate în tablouri de bord unde aveți constrângeri de spațiu, dar totuși doriți ca utilizatorul să acceseze întregul set de date.

Click aici pentru a descărca șablonul hartă căldură

Cum de a crea această hartă căldură dinamică?

iată setul complet de date care este utilizat pentru a crea această hartă dinamică a căldurii.

iată pașii:

• într-o foaie nouă (sau în aceeași foaie), introduceți numele lunii (pur și simplu copiați-l lipiți din datele originale).
• du –te la dezvoltator –> controale –> Insert – > bara de defilare. Acum faceți clic oriunde în foaia de lucru și va introduce o bară de defilare. (faceți clic aici dacă nu găsiți fila dezvoltator).
• faceți clic dreapta pe bara de defilare și faceți clic pe Control Format.
• în caseta de dialog Control Format, efectuați următoarele modificări:
  • valoare minimă: 1
  • valoare maximă 5
  • Link celulă: Sheet1!$J$1 (puteți face clic pe pictograma din dreapta și apoi selectați manual celula pe care doriți să o conectați la bara de defilare).
• Faceți clic pe OK.
• în celula B1, introduceți formula: = INDEX (Foaie1!$B$1:$H$13,rând (), Sheet1!$J $ 1+coloane (Foaie2!$B $ 1:B1) -1)
• redimensionați și plasați bara de defilare în partea de jos a setului de date.

acum, când schimbați bara de defilare, valoarea în Foaie1!$J$1 s-ar schimba și, deoarece formulele sunt legate de această celulă, s-ar actualiza pentru a afișa valorile corecte.

De asemenea, deoarece formatarea condiționată este volatilă, de îndată ce valoarea se schimbă, se actualizează și ea.

urmăriți Video – harta dinamică a căldurii în Excel

Exemplul 2: Crearea unei hărți dinamice a căldurii în Excel utilizând butoanele Radio

iată un alt exemplu în care puteți schimba harta căldurii făcând o selecție de butoane radio:

În acest exemplu, puteți evidenția 10 valori de sus/jos pe baza selecției butonului radio/opțiune.

Faceți clic aici pentru a descărca șablonul hărții termice

crearea unei hărți termice în tabelul pivot Excel

formatarea condiționată în tabelele Pivot funcționează la fel ca în cazul oricăror date normale.

dar este ceva important ce trebuie să știți.

permiteți-mi să iau un exemplu și să vă arăt.

Să presupunem că aveți un tabel pivot așa cum se arată mai jos:

pentru a crea o hartă de căldură în acest tabel pivot Excel:

• Selectați celulele (B5:D14).
• du –te la Acasă –> Formatare condiționată – > scale de culori și selectați scala de culori pe care doriți să o aplicați.

aceasta ar crea instantaneu harta de căldură în tabelul pivot.

problema cu această metodă este că dacă adăugați date noi în backend și reîmprospătați acest tabel Pivot, formatarea condiționată nu va fi aplicată noilor date.

de exemplu, pe măsură ce am adăugat date noi în partea din spate, am ajustat datele sursă și am reîmprospătat tabelul Pivot, puteți vedea că formatarea condiționată nu i se aplică.

Acest lucru se întâmplă pe măsură ce am aplicat formatarea condiționată numai celulelor B5:D14.

Dacă doriți ca această hartă de căldură să fie dinamică, astfel încât să se actualizeze atunci când se adaugă date noi, iată pașii:

• Selectați celulele (B5:D14).
• du –te la Acasă –> Formatare condiționată – > scale de culori și selectați scala de culori pe care doriți să o aplicați.
• mergeți din nou acasă –> Formatare condiționată –> Gestionați regulile.
• în Managerul de reguli de formatare condiționată, faceți clic pe butonul Editare.
• în caseta de dialog Editare regulă de formatare, selectați a treia opțiune: toate celulele care afișează valorile „vânzări” pentru „data” și „client”.

acum formatarea condiționată se va actualiza atunci când modificați datele backend.

notă: formatarea condiționată dispare dacă modificați câmpurile rând / coloană. De exemplu, dacă eliminați câmpul Dată și îl aplicați din nou, formatarea condiționată s-ar pierde.

s-ar putea să vă placă și următoarele tutoriale Excel:

• cum să evidențiați fiecare alt rând din Excel.
• creați 100% stivă diagramă coloană folosind formatarea condiționată.
• cum se numără celulele colorate în Excel.
• cum se evidențiază celulele goale în Excel.
• Excel Sparklines – un ghid complet