Una mappa termica in Excel è una rappresentazione visiva che mostra rapidamente una vista comparativa di un set di dati.
Ad esempio, nel set di dati di seguito, posso facilmente individuare quali sono i mesi in cui le vendite sono state basse (evidenziate in rosso) rispetto ad altri mesi.
Nel set di dati sopra, i colori vengono assegnati in base al valore nella cella. La scala dei colori va dal verde al giallo al rosso con valori alti che ottengono il colore verde e valori bassi che ottengono il colore rosso.
Creazione di una mappa di calore in Excel
Mentre puoi creare una mappa termica in Excel codificando manualmente i colori delle celle. Tuttavia, dovrai rifarlo quando i valori cambiano.
Invece del lavoro manuale, puoi utilizzare la formattazione condizionale per evidenziare le celle in base al valore. In questo modo, nel caso in cui si modificassero i valori nelle celle, il colore/formato della cella aggiornerebbe automaticamente la mappa termica in base alle regole prestabilite nella formattazione condizionale.
In questo tutorial imparerai come:
- Crea rapidamente una mappa termica in Excel utilizzando la formattazione condizionale.
- Crea una mappa termica dinamica in Excel.
- Crea una mappa termica nelle tabelle pivot di Excel.
Iniziamo!
Creazione di una mappa di calore in Excel utilizzando la formattazione condizionale
Se disponi di un set di dati in Excel, puoi evidenziare manualmente i punti dati e creare una mappa termica.
Tuttavia, questa sarebbe una mappa termica statica poiché il colore non cambierebbe quando si modifica il valore in una cella.
Quindi, la formattazione condizionale è la strada giusta da percorrere poiché fa cambiare il colore in una cella quando si modifica il valore in essa.
Supponiamo di avere un set di dati come mostrato di seguito:
Ecco i passaggi per creare una mappa di calore utilizzando questi dati:
- Seleziona il set di dati. In questo esempio, sarebbe B2:D13.
- Vai a Home -> Formattazione condizionale -> Scale di colore. Mostra varie combinazioni di colori che possono essere utilizzate per evidenziare i dati. La scala di colori più comune è la prima in cui le celle con valori alti sono evidenziate in verde e quelle basse in rosso. Nota che quando passi il mouse su queste scale di colori, puoi vedere l'anteprima dal vivo nel set di dati.
Questo ti darà una mappa di calore come mostrato di seguito:
Per impostazione predefinita, Excel assegna il colore rosso al valore più basso e il colore verde al valore più alto e tutti i valori rimanenti ottengono un colore in base al valore. Quindi c'è un gradiente con diverse sfumature dei tre colori in base al valore.
Ora, cosa succede se non vuoi un gradiente e vuoi solo mostrare rosso, giallo e verde. Ad esempio, si desidera evidenziare in rosso tutti i valori inferiori a 700, indipendentemente dal valore. Quindi 500 e 650 ottengono entrambi lo stesso colore rosso poiché è inferiore a 700.
Per fare questo:
- Vai a Home -> Formattazione condizionale -> Scale di colore -> Altre opzioni.
- Nella finestra di dialogo Nuova regola di formattazione, seleziona "Scala a 3 colori" dal menu a discesa Stile formato.
- Ora puoi specificare il valore minimo, medio e massimo e assegnargli il colore. Poiché vogliamo evidenziare tutte le celle con un valore inferiore a 700 in rosso, cambia il tipo in Numero e il valore in 700.
- Fare clic su OK.
Ora otterrai il risultato come mostrato di seguito. Nota che tutti i valori inferiori a 700 ottengono la stessa tonalità di colore rosso.
Una parola di cautela: Sebbene la formattazione condizionale sia uno strumento meraviglioso, sfortunatamente è volatile. Ciò significa che ogni volta che si verifica una modifica nel foglio di lavoro, la formattazione condizionale viene ricalcolata. Sebbene l'impatto possa essere trascurabile su piccoli set di dati, può portare a una cartella di lavoro di Excel lenta quando si lavora con grandi set di dati.
Creazione di una mappa termica dinamica in Excel
Poiché la formattazione condizionale dipende dal valore in una cella, non appena si modifica il valore, la formattazione condizionale viene ricalcolata e modificata.
Ciò consente di creare una mappa termica dinamica.
Diamo un'occhiata a due esempi di creazione di mappe termiche utilizzando i controlli interattivi in Excel.
Esempio 1: mappa termica utilizzando la barra di scorrimento
Ecco un esempio in cui la mappa termica cambia non appena si utilizza la barra di scorrimento per modificare l'anno.
Questo tipo di mappe termiche dinamiche può essere utilizzato nei dashboard in cui si hanno vincoli di spazio ma si desidera comunque che l'utente acceda all'intero set di dati.
Fare clic qui per scaricare il modello di mappa di calore
Come creare questa mappa termica dinamica?
Ecco il set di dati completo utilizzato per creare questa mappa termica dinamica.
Ecco i passaggi:
- In un nuovo foglio (o nello stesso foglio), inserisci i nomi dei mesi (basta copiarlo incollandolo dai dati originali).
- Vai su Sviluppatore -> Controlli -> Inserisci -> Barra di scorrimento. Ora fai clic in qualsiasi punto del foglio di lavoro e inserirà una barra di scorrimento. (clicca qui se non trovi la scheda sviluppatore).
- Fare clic con il pulsante destro del mouse sulla barra di scorrimento e fare clic su Controllo formato.
- Nella finestra di dialogo Controllo formato, apportare le seguenti modifiche:
- Valore minimo: 1
- Valore massimo 5
- Collegamento cella: Foglio1!$J$1 (puoi fare clic sull'icona a destra e quindi selezionare manualmente la cella che desideri collegare alla barra di scorrimento).
- Fare clic su OK.
- Nella cella B1, inserisci la formula: =INDEX(Foglio1!$B$1:$H$13,RIGA(),Foglio1!$J$1+COLUMNS(Foglio2!$B$1:B1)-1)
- Ridimensiona e posiziona la barra di scorrimento nella parte inferiore del set di dati.
Ora, quando cambi la barra di scorrimento, il valore in Foglio1! $ J $ 1 cambierà e poiché le formule sono collegate a questa cella, si aggiornerà per mostrare i valori corretti.
Inoltre, poiché la formattazione condizionale è volatile, non appena il valore cambia, anche questo viene aggiornato.
Guarda il video - Mappa termica dinamica in Excel
Esempio 2: creazione di una mappa termica dinamica in Excel utilizzando i pulsanti di opzione
Ecco un altro esempio in cui è possibile modificare la mappa termica effettuando una selezione del pulsante di opzione:
In questo esempio, puoi evidenziare i primi 10 valori in base alla selezione del pulsante di opzione/opzione.
Fare clic qui per scaricare il modello di mappa di calore
Creazione di una mappa di calore nella tabella pivot di Excel
La formattazione condizionale nelle tabelle pivot funziona allo stesso modo di qualsiasi dato normale.
Ma c'è qualcosa di importante che devi sapere.
Faccio un esempio e te lo mostro.
Supponiamo di avere una tabella pivot come mostrato di seguito:
Per creare una mappa termica in questa tabella pivot di Excel:
- Seleziona le celle (B5: D14).
- Vai su Home -> Formattazione condizionale -> Scale di colore e seleziona la scala di colori che desideri applicare.
Ciò creerebbe istantaneamente la mappa di calore nella tabella pivot.
Il problema con questo metodo è che se si aggiungono nuovi dati nel back-end e si aggiorna questa tabella pivot, la formattazione condizionale non verrà applicata ai nuovi dati.
Ad esempio, quando ho aggiunto nuovi dati nel back-end, regolato i dati di origine e aggiornato la tabella pivot, puoi vedere che la formattazione condizionale non viene applicata.
Questo accade quando abbiamo applicato la formattazione condizionale solo alle celle B5: D14.
Se vuoi che questa mappa termica sia dinamica in modo tale da aggiornarsi quando vengono aggiunti nuovi dati, ecco i passaggi:
- Seleziona le celle (B5: D14).
- Vai su Home -> Formattazione condizionale -> Scale di colore e seleziona la scala di colori che desideri applicare.
- Di nuovo vai su Home -> Formattazione condizionale -> Gestisci regole.
- In Gestione regole di formattazione condizionale, fare clic sul pulsante Modifica.
- Nella finestra di dialogo Modifica regola di formattazione, seleziona la terza opzione: Tutte le celle che mostrano i valori "Vendite" per "Data" e "Cliente".
Ora la formattazione condizionale si aggiornerebbe quando si modificano i dati di backend.
Nota: La formattazione condizionale scompare se si modificano i campi riga/colonna. Ad esempio, se si rimuove il campo Data e lo si applica di nuovo, la formattazione condizionale andrebbe persa.
