Come creare una mappa di calore in Excel-Una guida passo passo

Una mappa di calore in Excel è una rappresentazione visiva che mostra rapidamente una vista comparativa di un set di dati.

Ad esempio, nel set di dati qui sotto, posso facilmente individuare quali sono i mesi in cui le vendite erano basse (evidenziate in rosso) rispetto ad altri mesi.

Nel dataset di cui sopra, i colori vengono assegnati in base al valore nella cella. La scala dei colori è da verde a giallo a 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 è possibile creare una mappa di calore in Excel codificando manualmente le celle a colori. Tuttavia, dovrai rifarlo quando i valori cambiano.

Invece del lavoro manuale, è possibile utilizzare la formattazione condizionale per evidenziare le celle in base al valore. In questo modo, nel caso in cui si modifichino i valori nelle celle, il colore/formato della cella aggiornerebbe automaticamente la mappa di calore in base alle regole pre-specificate nella formattazione condizionale.

In questo tutorial, imparerai come:

• Creare rapidamente una mappa di calore in Excel utilizzando la formattazione condizionale.
• Crea una mappa di calore dinamica in Excel.
• Crea una mappa di calore nelle tabelle Pivot di Excel.

Cominciamo!

Creazione di una mappa di calore in Excel Utilizzando la formattazione condizionale

Se si dispone di un set di dati in Excel, è possibile evidenziare manualmente i punti dati e creare una mappa di calore.

Tuttavia, quella sarebbe una mappa di calore statica in quanto il colore non cambierebbe quando si modifica il valore in una cella.

Quindi, la formattazione condizionale è la strada giusta da percorrere in quanto rende il colore in una cella cambia quando si modifica il valore in esso.

Supponiamo di avere un set di dati come mostrato di seguito:

Ecco i passaggi per creare una mappa di calore utilizzando questi dati:

• Selezionare il set di dati. In questo esempio, sarebbe B2: D13.
• Vai alla 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 elevati sono evidenziate in verde e basse in rosso. Si noti che, come si passa il mouse su queste scale di colore, è possibile vedere l’anteprima dal vivo nel set di dati.
  

Questo vi darà una mappa di calore, come illustrato di seguito:

per impostazione predefinita, Excel assegna il colore rosso per il valore più basso e il colore verde per il valore più alto, e tutti i restanti valori di ottenere 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 tutti i valori inferiori a 700 in rosso, indipendentemente dal valore. Quindi 500 e 650 hanno entrambi lo stesso colore rosso poiché sono meno di 700.

Per fare ciò:

• Vai a Home –>Formattazione condizionale –>Scale di colore –> Altre opzioni.
• Nella finestra di dialogo Nuova regola di formattazione, selezionare ‘Scala a 3 colori’ dal menu a discesa Stile formato.
• Ora è possibile specificare il valore minimo, il punto medio e il valore massimo e assegnare il colore ad esso. Poiché vogliamo evidenziare tutte le celle con un valore inferiore a 700 in rosso, modificare il tipo in Numero e il valore in 700.
• Fare clic su OK.

Ora otterrai il risultato come mostrato di seguito. Si noti che tutti i valori inferiori a 700 ottengono la stessa tonalità di colore rosso.

Una parola di cautela: mentre la formattazione condizionale è uno strumento meraviglioso, sfortunatamente, è volatile. Ciò significa che ogni volta che c’è qualche modifica nel foglio di lavoro, la formattazione condizionale viene ricalcolata. Mentre l’impatto può essere trascurabile su set di dati di piccole dimensioni, può portare a una cartella di lavoro Excel lenta quando si lavora con set di dati di grandi dimensioni.

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 ricalcola e cambia.

Ciò consente di creare una mappa di calore dinamica.

Diamo un’occhiata a due esempi di creazione di mappe di calore utilizzando controlli interattivi in Excel.

Esempio 1: Mappa di calore usando la barra di scorrimento

Ecco un esempio in cui la mappa di calore cambia non appena si utilizza la barra di scorrimento per cambiare l’anno.

Questo tipo di mappe di calore dinamiche può essere utilizzato in dashboard in cui si hanno vincoli di spazio ma si desidera comunque che l’utente acceda all’intero set di dati.

Clicca qui per scaricare il modello di mappa di calore

Come creare questa mappa di calore dinamica?

Ecco il set di dati completo che viene utilizzato per creare questa mappa di calore dinamica.

Ecco i passaggi:

• In un nuovo foglio (o nello stesso foglio), inserisci i nomi dei mesi (basta copiarlo e incollarlo dai dati originali).
• Vai allo 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 riesci a trovare la scheda sviluppatore).
• Fare clic destro 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
  • Cell Link: Sheet1!J J 1 1 (È possibile fare clic sull’icona a destra e quindi selezionare manualmente la cella che si desidera collegare alla barra di scorrimento).
• Fare clic su OK.
• Nella cella B1, inserire la formula: = INDEX (Sheet1!B B 1 1: H H H 13, RIGA (), Foglio1!COLUMNS J COLUMNS 1+COLONNE (Foglio2!$B$1:B1)-1)
• Ridimensiona e posiziona la barra di scorrimento nella parte inferiore del set di dati.

Ora quando si modifica la barra di scorrimento, il valore in Sheet1!change J 1 1 cambierebbe, e poiché le formule sono collegate a questa cella, si aggiornerebbe per mostrare i valori corretti.

Inoltre, poiché la formattazione condizionale è volatile, non appena il valore cambia, viene aggiornato.

Guarda il video-Mappa dinamica del calore in Excel

Esempio 2: Creazione di un Dinamico Mappa di Calore in Excel utilizzando i Pulsanti

Ecco un altro esempio in cui è possibile modificare la mappa di calore rendendo il pulsante di selezione:

In questo esempio, è possibile evidenziare superiore/inferiore di 10 valori basati sulla radio/pulsante di opzione di selezione.

Clicca qui per scaricare il modello Heat Map

Creare una Heat Map 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.

Fammi fare un esempio e mostrarti.

Supponiamo di avere una tabella pivot come mostrato di seguito:

Per creare una mappa di calore in questa tabella Pivot di Excel:

• Selezionare le celle (B5:D14).
• Vai a Home –> Formattazione condizionale – > Scale di colore e seleziona la scala di colori che vuoi applicare.

Questo creerebbe istantaneamente la mappa di calore nella tabella pivot.

Il problema con questo metodo è che se si aggiungono nuovi dati nel backend 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, è possibile vedere che la formattazione condizionale non viene applicata ad essa.

Questo accade quando abbiamo applicato la formattazione condizionale solo alle celle B5:D14.

Se vuoi che questa mappa di calore sia dinamica in modo tale che si aggiorni quando vengono aggiunti nuovi dati, ecco i passaggi:

• Seleziona le celle (B5:D14).
• Vai a Home –> Formattazione condizionale – > Scale di colore e seleziona la scala di colori che vuoi applicare.
• Di nuovo vai a Home –> Formattazione condizionale –> Gestisci regole.
• Nel Gestore regole di formattazione condizionale, fare clic sul pulsante Modifica.
• Nella finestra di dialogo Modifica regola formattazione, selezionare la terza opzione: Tutte le celle che mostrano i valori ‘Vendite’ per ‘Data’ e ‘Cliente’.

Ora la formattazione condizionale si aggiorna quando si modificano i dati del 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.

Potrebbero piacerti anche i seguenti tutorial di Excel:

• Come evidenziare ogni altra riga in Excel.
• Crea istogramma stack al 100% usando la formattazione condizionale.
• Come contare le celle colorate in Excel.
• Come evidenziare le celle vuote in Excel.
• Excel Sparklines – Una guida completa