Une carte thermique dans Excel est une représentation visuelle qui vous montre rapidement une vue comparative d'un ensemble de données.
Par exemple, dans l'ensemble de données ci-dessous, je peux facilement repérer quels sont les mois où les ventes ont été faibles (surlignées en rouge) par rapport aux autres mois.
Dans l'ensemble de données ci-dessus, les couleurs sont attribuées en fonction de la valeur de la cellule. L'échelle de couleurs va du vert au jaune au rouge avec des valeurs élevées pour la couleur verte et des valeurs faibles pour la couleur rouge.
Création d'une carte thermique dans Excel
Alors que vous pouvez créer une carte thermique dans Excel en codant manuellement les cellules par couleur. Cependant, vous devrez le refaire lorsque les valeurs changeront.
Au lieu du travail manuel, vous pouvez utiliser une mise en forme conditionnelle pour mettre en évidence les cellules en fonction de la valeur. De cette façon, si vous modifiez les valeurs dans les cellules, la couleur/le format de la cellule mettrait automatiquement à jour la carte thermique en fonction des règles pré-spécifiées dans la mise en forme conditionnelle.
Dans ce didacticiel, vous apprendrez à :
- Créez rapidement une carte thermique dans Excel à l'aide de la mise en forme conditionnelle.
- Créez une carte thermique dynamique dans Excel.
- Créez une carte thermique dans les tableaux croisés dynamiques Excel.
Commençons!
Création d'une carte thermique dans Excel à l'aide de la mise en forme conditionnelle
Si vous avez un jeu de données dans Excel, vous pouvez mettre en évidence manuellement les points de données et créer une carte thermique.
Cependant, ce serait une carte thermique statique car la couleur ne changerait pas lorsque vous modifiez la valeur dans une cellule.
Par conséquent, la mise en forme conditionnelle est la bonne voie à suivre car elle modifie la couleur d'une cellule lorsque vous modifiez la valeur qu'elle contient.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Voici les étapes pour créer une carte thermique à l'aide de ces données :
- Sélectionnez le jeu de données. Dans cet exemple, ce serait B2:D13.
- Allez dans Accueil -> Mise en forme conditionnelle -> Échelles de couleurs. Il montre diverses combinaisons de couleurs qui peuvent être utilisées pour mettre en évidence les données. L'échelle de couleurs la plus courante est la première où les cellules avec des valeurs élevées sont surlignées en vert et faibles en rouge. Notez que lorsque vous passez la souris sur ces échelles de couleurs, vous pouvez voir l'aperçu en direct dans l'ensemble de données.
Cela vous donnera une carte thermique comme indiqué ci-dessous :
Par défaut, Excel attribue la couleur rouge à la valeur la plus basse et la couleur verte à la valeur la plus élevée, et toutes les valeurs restantes obtiennent une couleur basée sur la valeur. Il y a donc un dégradé avec différentes nuances des trois couleurs en fonction de la valeur.
Maintenant, que faire si vous ne voulez pas de dégradé et que vous voulez seulement afficher du rouge, du jaune et du vert. Par exemple, vous souhaitez mettre en surbrillance toutes les valeurs inférieures à 700 en rouge, quelle que soit la valeur. Donc 500 et 650 ont tous les deux la même couleur rouge car c'est moins de 700.
Pour faire ça:
- Allez dans Accueil -> Mise en forme conditionnelle -> Échelles de couleurs -> Plus d'options.
- Dans la boîte de dialogue Nouvelle règle de formatage, sélectionnez "Échelle à 3 couleurs" dans la liste déroulante Style de format.
- Vous pouvez maintenant spécifier la valeur minimale, médiane et maximale et lui attribuer la couleur. Puisque nous voulons mettre en surbrillance toutes les cellules avec une valeur inférieure à 700 en rouge, changez le type en Nombre et la valeur en 700.
- Cliquez sur OK.
Maintenant, vous obtiendrez le résultat comme indiqué ci-dessous. Notez que toutes les valeurs inférieures à 700 obtiennent la même nuance de couleur rouge.
Un mot d'avertissement: Bien que la mise en forme conditionnelle soit un outil formidable, malheureusement, elle est volatile. Cela signifie que chaque fois qu'il y a un changement dans la feuille de calcul, la mise en forme conditionnelle est recalculée. Bien que l'impact puisse être négligeable sur de petits ensembles de données, il peut entraîner un classeur Excel lent lorsque vous travaillez avec de grands ensembles de données.
Création d'une carte thermique dynamique dans Excel
Étant donné que la mise en forme conditionnelle dépend de la valeur d'une cellule, dès que vous modifiez la valeur, la mise en forme conditionnelle est recalculée et modifiée.
Cela permet de faire une carte thermique dynamique.
Examinons deux exemples de création de cartes thermiques à l'aide de commandes interactives dans Excel.
Exemple 1 : Carte de chaleur à l'aide de la barre de défilement
Voici un exemple où la carte thermique change dès que vous utilisez la barre de défilement pour changer l'année.
Ce type de cartes thermiques dynamiques peut être utilisé dans des tableaux de bord où vous avez des contraintes d'espace mais souhaitez toujours que l'utilisateur accède à l'ensemble des données.
Cliquez ici pour télécharger le modèle de carte thermique
Comment créer cette carte thermique dynamique ?
Voici l'ensemble de données complet qui est utilisé pour créer cette carte thermique dynamique.
Voici les étapes :
- Dans une nouvelle feuille (ou dans la même feuille), entrez les noms des mois (copiez-les simplement à partir des données d'origine).
- Allez dans Développeur -> Contrôles -> Insérer -> Barre de défilement. Maintenant, cliquez n'importe où dans la feuille de calcul et une barre de défilement sera insérée. (cliquez ici si vous ne trouvez pas l'onglet développeur).
- Faites un clic droit sur la barre de défilement et cliquez sur Contrôle du format.
- Dans la boîte de dialogue Contrôle du format, apportez les modifications suivantes :
- Valeur minimale : 1
- Valeur maximale 5
- Lien de cellule : Sheet1!$J$1 (Vous pouvez cliquer sur l'icône à droite puis sélectionner manuellement la cellule que vous souhaitez lier à la barre de défilement).
- Cliquez sur OK.
- Dans la cellule B1, entrez la formule : =INDEX(Sheet1!$B$1:$H$13,ROW(),Sheet1!$J$1+COLUMNS(Sheet2!$B$1:B1)-1)
- Redimensionnez et placez la barre de défilement au bas de l'ensemble de données.
Désormais, lorsque vous modifiez la barre de défilement, la valeur dans Sheet1!$J$1 change et, comme les formules sont liées à cette cellule, elle se met à jour pour afficher les valeurs correctes.
De plus, comme la mise en forme conditionnelle est volatile, dès que la valeur change, elle est également mise à jour.
Regarder la vidéo - Carte thermique dynamique dans Excel
Exemple 2 : Création d'une carte thermique dynamique dans Excel à l'aide de boutons radio
Voici un autre exemple où vous pouvez modifier la carte thermique en sélectionnant un bouton radio :
Dans cet exemple, vous pouvez mettre en surbrillance les 10 premières/dernières valeurs en fonction de la sélection du bouton radio/option.
Cliquez ici pour télécharger le modèle de carte thermique
Création d'une carte thermique dans un tableau croisé dynamique Excel
La mise en forme conditionnelle dans les tableaux croisés dynamiques fonctionne de la même manière qu'avec n'importe quelle donnée normale.
Mais il y a quelque chose d'important que vous devez savoir.
Laissez-moi prendre un exemple et vous montrer.
Supposons que vous ayez un tableau croisé dynamique comme indiqué ci-dessous :
Pour créer une carte thermique dans ce tableau croisé dynamique Excel :
- Sélectionnez les cellules (B5:D14).
- Allez dans Accueil -> Mise en forme conditionnelle -> Échelles de couleurs et sélectionnez l'échelle de couleurs que vous souhaitez appliquer.
Cela créerait instantanément la carte thermique dans le tableau croisé dynamique.
Le problème avec cette méthode est que si vous ajoutez de nouvelles données dans le backend et actualisez ce tableau croisé dynamique, la mise en forme conditionnelle ne sera pas appliquée aux nouvelles données.
Par exemple, lorsque j'ai ajouté de nouvelles données dans le back-end, ajusté les données source et actualisé le tableau croisé dynamique, vous pouvez voir que la mise en forme conditionnelle ne lui est pas appliquée.
Cela se produit lorsque nous avons appliqué la mise en forme conditionnelle aux cellules B5:D14 uniquement.
Si vous souhaitez que cette carte thermique soit dynamique de sorte qu'elle se mette à jour lorsque de nouvelles données sont ajoutées, voici les étapes :
- Sélectionnez les cellules (B5:D14).
- Allez dans Accueil -> Mise en forme conditionnelle -> Échelles de couleurs et sélectionnez l'échelle de couleurs que vous souhaitez appliquer.
- Allez à nouveau dans Accueil -> Mise en forme conditionnelle -> Gérer les règles.
- Dans le gestionnaire de règles de mise en forme conditionnelle, cliquez sur le bouton Modifier.
- Dans la boîte de dialogue Modifier la règle de mise en forme, sélectionnez la troisième option : toutes les cellules affichant les valeurs « Ventes » pour « Date » et « Client ».
Désormais, la mise en forme conditionnelle est mise à jour lorsque vous modifiez les données du backend.
Noter: La mise en forme conditionnelle disparaît si vous modifiez les champs de ligne/colonne. Par exemple, si vous supprimez le champ Date et l'appliquez à nouveau, la mise en forme conditionnelle sera perdue.
