Vous pouvez utiliser une visualisation de type carte thermique pour comparer des valeurs ventilées par jour de la semaine et par créneau horaire.

Ce type de visualisation est très utile pour connaître à quel moment de la semaine et à quelle heure du jour ont lieu des événements enregistrées dans une base de données, par exemple des commandes clients et ainsi mieux connaître :

• Les habitudes d’achat de vos clients
• Les heures d’affluence de votre site e-commerce
• Les moments propices pour lancer une promotion
• etc.

Dans ce type de graphique, plus les couleurs sont foncées, plus les valeurs sont élevées, à l’inverse plus les couleurs sont claires, plus les valeurs sont faibles. Cela permet de visualiser rapidement les créneaux horaires les plus représentés.

Voici un exemple très simple sur Excel pour construire ce type de visualisation et analyser ce type de données. Nous avons besoin pour cela d’une table contenant 3 colonnes :

• La première colonne contient la date, par exemple : 27/12/2019
• La deuxième colonne contient l’heure, par exemple 23:00:00
• La troisième colonne est la valeur quantifiable, par exemple : montant des ventes, ou l’occurrence d’un achat

Nous allons enrichir cette table avec d’autres colonnes calculées pour créer ce type de visuel :

Retrouvez également en fin d’article le téléchargement du fichier d’exemple.

Table de données

Voici un extrait de la table de données, constituée de 3 colonnes : date, heure et valeur, ici il s’agit des réponses à mon sondage en ligne 😁

Création des colonnes supplémentaires

1. Jour de la semaine : Nous avons besoin de récupérer tout d’abord le jour de la semaine. Pour cela, on créé une nouvelle colonne “JourNum” avec la formule : =JOURSEM([@Date];2). Ici le deuxième argument de la fonction JOURSEM est 2, cela signifie que la semaine commence le lundi (sinon si on ne spécifie rien, par défaut on commence le dimanche).
2. Nom du jour : A l’aide d’une table de mapping comme celle ci-dessous, on relie le numéro du jour de la semaine au nom du jour. Si la table de mapping s’appelle “TabJourSem”, on insère une nouvelle colonne “Jour” à la table de données avec la formule : =RECHERCHEV([@JourNum];TabJourSem;2;0)

   JourNum	Jour
   1	Lundi
   2	Mardi
   3	Mercredi
   4	Jeudi
   5	Vendredi
   6	Samedi
   7	Dimanche

La colonne Jour ainsi créée constituera un des 2 axes de la carte thermique.

Il nous reste à créer le second axe constitué des créneaux horaires.

Pour cela on créé 2 autres colonnes :

1. Numéro du créneau : Les numéros serviront à trier dans l’ordre chronologique les créneaux horaires. On doit définir pour chaque heure dans notre table de données à quel créneau horaire elle appartient. On s’aide d’une table de mapping comme celle ci-dessous pour relier l’heure au créneau horaire. Si la table de mapping s’appelle “TabCreneau”, on insère une nouvelle colonne “CréneauNum” à notre table de données avec la formule : =RECHERCHEV([@Heure];TabCreneau;2;0)
2. Nom du créneau : Pour récupérer le nom du créneau, on procède de la même manière en insérant une nouvelle colonne “Créneau” en allant récupérer la 3ème colonne de la table “TabCreneau” à l’aide de cette formule :=RECHERCHEV([@Heure];TabCreneau;3;0)

Enfin, la table de données devrait ressembler à cela :

Création du visuel “carte thermique hebdomadaire”

Pour créer le visuel, nous allons simplement créer dans une nouvelle feuille un Tableau croisé dynamique, avec les champs suivants :

• Jour : à mettre en colonne
• CréneauNum : à mettre en ligne
• Créneau : à mettre en ligne
• Valeur (ici RépondantId ) : à mettre en valeur => Selon vos données, choisissez de résumer les valeurs en Somme ou en Nombre. Ici, j’agrège les participants à mon sondage en Nombre car il s’agit de dénombrer le nombre de participants par jour et par créneau. S’il s’agissait de montants des ventes, alors il serait intéressant d’agréger avec une Somme pour avoir le montant total des ventes ventilé par jour et par créneau.

Après avoir inséré les champs dans le TCD (Tableau Croisé Dynamique), vous devriez avoir ce résultat :

Il ne reste maintenant plus qu’à mettre en forme le TCD pour obtenir le visuel !

C’est ce que nous allons voir ci-après.

Mise en forme conditionnelle du visuel “carte thermique hebdomadaire”

1. Sélectionnez tout d’abord les cellules du TCD contenant les valeurs, sans prendre les lignes et colonnes de totaux, puis allez dans le Menu Accueil -> Mise en forme conditionnelle
2. Sélectionnez Nuances de couleurs
3. Puis choisissez une échelle à 2 couleurs

Voilà, on y est presque.

Vous pouvez bien sûr changer l’échelle des couleurs à l’étape 3 en sélectionnant Autres règles, puis en définissant la couleur pour les valeurs minimales et la couleur pour les valeurs maximales.

Maintenant, sélectionnez la ligne Total des valeurs et appliquez la même mise en forme conditionnelle.

Enfin, sélectionnez la colonne Total des valeurs et appliquez la même mise en forme conditionnelle.

Dernières astuces 😉 :

• Vous pouvez masquer les entêtes et les colonnes du TCD en mettant le texte en blanc si votre fond est blanc par exemple
• Vous pouvez masquer les boutons “Réduire” du TCD dans les options du TCD pour une meilleure lisibilité

Le résultat final devrait ressembler à ceci :

ALTERNATIVE : Utiliser Power Query pour construire la table de données source du visuel

Pour aller plus vite et traiter plus efficacement les données sources, nous allons voir une autre méthode en utilisant l’outil ETL d’Excel Power Query pour créer les colonnes supplémentaires requises pour le visuel de carte thermique hebdomadaire, à savoir les colonnes Créneau et Jour de la semaine.

Si vous êtes nouveau dans Power Query, n’ayez pas d’inquiétude, je vous guide pas à pas 👍

Je vous invite tout d’abord à télécharger le fichier Excel à la fin de cet article, puis revenir dans l’article et suivre les étapes décrites ci-dessous.

A noter : La version présentée ici de Power Query est celle intégrée à Excel 2016 ou Office 365.

Etape 1 : charger les tables dans PowerQuery

1. Cliquez sur n’importe quelle cellule de la table des données source. Dans le fichier Excel téléchargeable, c’est l’onglet “Données PowerQuery”
2. Allez dans Données => Récupérer et Transformer des données. Repérez l’icône “A partir d’un tableau ou d’une plage”
3. Cliquez sur cette icone
4. La table se charge dans PowerQuery
5. Allez dans le menu Fichier de PowerQuery => “Fermer et Charger dans”
6. Dans la boîte de dialogue qui s’affiche, cochez “Rapport de tableau croisé dynamique” et “Nouvelle feuille de calcul” puis cliquez sur Ok
7. Un TCD est créé dans une nouvelle feuille. Pour l’instant n’y touchez pas
8. Allez dans l’onglet Mapping et cliquez sur n’importe quelle cellule de la table des créneaux puis répétez les étapes 1 à 5
9. Maintenant dans la boîte de dialogue “Fermer et charger dans”, au lieu de cocher “Rapport de tableau croisé dynamique”, cochez “Ne créer que la connexion” et puis valider par Ok
10. Les tables sont désormais chargées dans PowerQuery.

Etape 2 : Importer la colonne Créneau de la table Mapping dans la table de données source

Importer la colonne Créneau de la table de mapping à la table principale, cela revient à effectuer une RechercheV dans Excel. Sauf que l’on travaille avec des tables dans PowerQuery, et on appelle ce processus “Joindre des tables” ou “Fusionner des requêtes”.

1. Dans le panneau de gauche “Requêtes”, dépliez-le s’il ne s’affiche pas entièrement, cliquez sur la table principale que vous avez chargée précédemment à l’étape 1. A noter qu’elle porte souvent le nom de la Table dans Excel par défaut.
2. Allez ensuite dans le menu Accueil => Combiner => Fusionner des requêtes et cliquez sur le bouton
3. Dans la boîte de dialogue Fusionner qui s’affiche, sélectionnez la colonne Heure de la table principale
4. Puis cherchez la table TabCreneau dans la liste déroulante, et sélectionnez la colonne Heure également => Nous allons joindre les 2 tables à l’aide de la colonne en commun Heures.
5. Type de jointure : laissez la valeur par défaut (externe gauche)
6. Cliquez sur Ok
7. PowerQuery a créé une nouvelle colonne dans la table principale, repérez le bouton sur le nom de la nouvelle colonne en forme de 2 flèches opposées et cliquez dessus
8. Dans la boîte de dialogue, cochez les colonnes de la table de mapping à récupérer, ici CréneauNum et Créneau. Décochez l’option “utiliser le nom de la colonne d’origine comme préfixe”
9. Cliquez sur Ok
10. PowerQuery a maintenant créé 2 colonnes dans la table principale : Créneau et CréneauNum. Les valeurs de créneaux doivent normalement correspondre aux heures

Etape 3 : Créer une colonne personnalisée des jours de la semaine dans la table de données source

1. Dans le menu de PowerQuery, allez dans “Ajouter une colonne”
2. Cliquez sur Colonne personnalisée
3. Dans la boîte de dialogue Colonne personnalisée, donner un nom à la nouvelle colonne, par exemple “Jour Semaine”
4. Tapez la formule suivante pour calculer le jour de la semaine à partir de la colonne des dates : =Date.DayOfWeekName([Date])
5. Validez par Ok
6. PowerQuery a créé la colonne des jours de la semaine. Il reste à modifier le type de données en texte
7. Pour cela, faites un clic droit sur l’entête de la nouvelle colonne, puis cliquez sur modifier le type => choisir Texte

Etape 4 : Actualiser le Rapport de tableau croisé dynamique créé à l’étape 1

Après le chargement des données dans PowerQuery, pour la table principale nous avions choisi l’option charger dans un rapport de tableau croisé dynamique.

Maintenant si vous retournez dans ce TCD, et faites un clic droit sur le TCD => Actualiser , vous allez voir les nouvelles colonnes créées dans PowerQuery.

Glissez les champs comme dans l’image ci-dessous :

• Jour Semaine : en colonne
• Créneau Num et Créneau : en ligne
• Valeur (ici RépondantId) : en valeurs

Voilà le résultat : on obtient la même chose qu’avec la méthode sur Excel, mais de manière plus rapide et robuste car il n’y a pas de colonnes avec des formules dans la table des données, qui risqueraient de sauter un jour ou l’autre à force de manipuler le fichier, la liaison entre les tables se faisant dans PowerQuery de manière automatique.

Avec un peu de mise en forme conditionnelle, on retrouve la carte thermique :

📥TÉLÉCHARGER LE FICHIER D’EXEMPLE

Télécharger le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.

Comment créer une carte thermique Hebdomadaire avec Excel et Power Query

Ce fichier Excel montre comment créer une carte thermique avec des données ventilées selon les jours de la semaine et les heures du jour. En effet, dans une analyse il…

Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

Si vous aimez cet article, dites-le moi en commentaire et n’hésitez-pas à le partager à vos contacts ! 😉