O hartă de căldură în Excel este o reprezentare vizuală care vă arată rapid o vedere comparativă a unui set de date.
De exemplu, în setul de date de mai jos, pot identifica 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ă. Scara de culoare 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, codând manual celulele. Cu toate acestea, va trebui să o refaceți atunci când valorile se schimbă.
În loc de lucru 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 ar actualiza automat harta de căldură pe baza regulilor pre-specificate în formatarea condiționată.
În acest tutorial, veți afla cum să:
- Creați rapid o hartă de căldură în Excel utilizând 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 de căldură în Excel folosind 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ă de căldură.
Cu toate acestea, aceasta ar fi o hartă de căldură statică, deoarece culoarea nu se va schimba atunci când modificați valoarea într-o celulă.
Prin urmare, formatarea condiționată este calea corectă, 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.
- Accesați Acasă -> Formatare condiționată -> Scale de culori. Afișează diferite combinații de culori care pot fi utilizate pentru a evidenția datele. Cea mai obișnuită scară de culoare este prima în care celulele cu valori ridicate sunt evidențiate în verde și reduse în roșu. Rețineți că, în timp ce plasați mouse-ul peste aceste scale de culoare, puteți vedea previzualizarea live în setul de date.
Acest lucru 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 obțin o culoare pe baza valorii. Deci, există un gradient cu diferite nuanțe ale celor trei culori pe baza valorii.
Acum, 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 700 în roșu, indiferent de valoare. Deci 500 și 650 au ambele aceeași culoare roșie, deoarece au mai puțin de 700.
Pentru a face acest lucru:
- Accesați Acasă -> Formatare condiționată -> Scale de culori -> Mai multe opțiuni.
- În caseta de dialog Nouă regulă de formatare, selectați „Scală cu 3 culori” din meniul derulant Format stil.
- Acum puteți specifica valoarea minimă, mijlocul și valoarea maximă și îi puteți atribui culoarea. Deoarece vrem să evidențiem toate celulele cu o valoare sub 700 în roșu, schimbați tipul la Număr și valoarea 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 obțin aceeași nuanță de culoare roșie.
Un Cuvânt de Atenție: Deși formatarea condiționată este un instrument minunat, din păcate, este volatilă. Aceasta înseamnă că, de fiecare dată când există vreo modificare în foaia de lucru, formatarea condiționată este recalculată. Deși 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 dinamice de căldură în Excel
Deoarece formatarea condițională este dependentă de valoarea dintr-o celulă, de îndată ce modificați valoarea, formatarea condițională se recalculează și se modifică.
Acest lucru face posibilă realizarea unei hărți dinamice a căldurii.
Să vedem două exemple de creare a hărților de căldură folosind controale interactive în Excel.
Exemplul 1: Heat Map folosind bara de derulare
Iată un exemplu în care harta căldurii se schimbă de îndată ce utilizați bara de derulare pentru a schimba anul.
Acest tip de hărți dinamice de căldură poate fi utilizat în tablouri de bord în care aveți constrângeri de spațiu, dar doriți totuși ca utilizatorul să acceseze întregul set de date.
Faceți clic aici pentru a descărca șablonul Heat Map
Cum să creați această hartă dinamică a căldurii?
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 lipiți-le din datele originale).
- Accesați Dezvoltator -> Comenzi -> Inserare -> Bara de derulare. Acum faceți clic oriunde în foaia de lucru și va insera o bară de derulare. (faceți clic aici dacă nu găsiți fila dezvoltator).
- Faceți clic dreapta pe bara de defilare și faceți clic pe Format Control.
- În caseta de dialog Format Control, 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 derulare).
- Faceți clic pe OK.
- În celula B1, introduceți formula: = INDEX (Sheet1! $ B $ 1: $ H $ 13, ROW (), Sheet1! $ J $ 1 + COLUMNS (Sheet2! $ B $ 1: B1) -1)
- Redimensionați și plasați bara de derulare în partea de jos a setului de date.
Acum, când schimbați bara de defilare, valoarea din Sheet1! $ 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ă, imediat ce valoarea se schimbă, se actualizează și ea.
Vizionați video - Harta dinamică a căldurii în Excel
Exemplul 2: Crearea unei hărți dinamice de căldură în Excel folosind butoanele radio
Iată un alt exemplu în care puteți schimba harta de căldură prin selectarea unui buton radio:
În acest exemplu, puteți evidenția 10 valori de sus / de jos pe baza selecției butonului radio / opțional.
Faceți clic aici pentru a descărca șablonul Heat Map
Crearea unei hărți de căldură în tabelul pivot Excel
Formatarea condiționată în tabelele pivot funcționează la fel ca în cazul oricăror date normale.
Dar trebuie să știți ceva important.
Lasă-mă să iau un exemplu și să-ți 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 din pivot Excel:
- Selectați celulele (B5: D14).
- Accesați Acasă -> Formatare condiționată -> Scale de culori și selectați scala de culori pe care doriți să o aplicați.
Acest lucru 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 ar 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 actualizat tabelul pivot, puteți vedea că formatarea condiționată nu i se aplică.
Acest lucru se întâmplă pe măsură ce aplicăm formatarea condițională numai pentru celulele B5: D14.
Dacă doriți ca această hartă de căldură să fie dinamică astfel încât să se actualizeze atunci când sunt adăugate date noi, iată pașii:
- Selectați celulele (B5: D14).
- Accesați Acasă -> Formatare condiționată -> Scale de culori și selectați scala de culori pe care doriți să o aplicați.
- Din nou, accesați Acasă -> Formatare condiționată -> Gestionați regulile.
- În Managerul de reguli de formatare condiționată, faceți clic pe butonul Editați.
- În caseta de dialog Editați regula de formatare, selectați a treia opțiune: Toate celulele care afișează valorile „Vânzări” pentru „Data” și „Client”.
Acum, formatarea condițională se va actualiza atunci când modificați datele de backend.
Notă: Formatarea condiționată dispare dacă modificați câmpurile rând / coloană. De exemplu, dacă eliminați câmpul Data și îl aplicați din nou, formatarea condiționată s-ar pierde.
