Comment créer des feux de signalisation tricolores dynamiques dans Excel sans VBA ?

Dans ce tutoriel, je vais vous montrer comment à créer des feux tricolores dynamiques dans Excel pour visualiser facilement les performances de nos restaurants.

Cette technique va transformer nos tableaux de bord en outils visuels percutants.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le petit tableau suivant qui permet de suivre les performances des établissements d’un petit groupe de restaurants.

Voici le tableau en question :

Maintenant que notre tableau est prêt, nous devons définir les seuils qui détermineront la couleur de nos feux tricolores.

Ces seuils sont cruciaux car ils reflètent nos objectifs et nos standards de performance.

Pour la satisfaction client :

• rouge : moins de 60%
• jaune : entre 60% et 75%
• vert : 75% et plus

Pour le temps d'attente :

• rouge : plus de 35 minutes
• jaune : entre 25 et 35 minutes
• vert : 25 minutes ou moins

Et enfin, pour le chiffre d'affaires :

• rouge : moins de 2000€
• jaune : entre 2000€ et 2500€
• vert : 2500€ et plus

Nous pouvons synthétiser ces informations dans un petit tableau annexe récapitulatif, cela nous aidera à nous y référer facilement et à les ajuster si nécessaire :

2. Création des formules conditionnelles

Maintenant que nos données et nos seuils sont en place, passons à la partie la plus technique : la création des formules conditionnelles qui détermineront la couleur de nos feux tricolores.

   2.1. Formules pour les valeurs

Tout d'abord, pour simplifier la détermination des couleurs, nous faire apparaître celle-ci distinctement en utilisant la fonction SOMME.SI().

Cette fonction nous permettra de sommer les valeurs correspondant à un restaurant spécifique, que nous sélectionnons dans la cellule M6 à partir de la liste déroulante.

Dans la cellule G9, pour la satisfaction client, nous saisissons :

 =SOMME.SI($A$9:$A$13;$M$6 ;B$9:B$13) 

Cette formule cherche le nom du restaurant dans la cellule M6 dans la plage A9:A13, et somme les valeurs correspondantes dans la plage B9:B13.

Nous répétons ensuite ce processus pour le temps d'attente (cellule H9) et le chiffre d'affaires (cellule I9) en ajustant les plages de données :

pour le temps d'attente : =SOMME.SI($A$9:$A$13;$M$6;C$9:C$13)

pour le chiffre d'affaires : =SOMME.SI($A$9:$A$13;$M$6;D$9:D$13)

   2.2. Formules pour déterminer les couleurs

Ensuite, nous allons créer des formules pour déterminer la couleur appropriée pour chaque critère en fonction de la valeur calculée.

Dans la cellule G10, pour la satisfaction client, nous saisissons :

 =SI(G9>75;"Vert";SI(G9>60;"Orange";"Rouge"))  

Cette formule utilise des SI() imbriqués pour attribuer la couleur appropriée en fonction de la valeur dans G9.

Nous commençons par vérifier si la valeur de la cellule G9 est supérieure à 75, et dans ce cas, nous pouvons afficher le mot « Vert ».

Dans le cas contraire, nous vérifions si cette valeur est supérieure à 60 pour afficher « Orange », ou encore « Rouge » si ce n’est pas le cas.

Ensuite, pour le temps d'attente (cellule H10) :

 =SI(H9<25;"Vert";SI(H9<35;"Orange";"Rouge"))  

Et enfin pour le chiffre d'affaires (cellule I10) :

 =SI(I9>2500;"Vert";SI(I9>2000;"Orange";"Rouge"))  

   2.3. Formules pour les feux tricolores

Enfin, nous allons créer les formules qui généreront une lettre « l » pour chaque couleur de feu tricolore (que nous utiliserons plus tard pour créer notre point lumineux).

Pour cela, nous saisissons la formule suivante dans la cellule G11 :

 =SI(G$10=$F11;"l";"") 

Cette formule vérifie si la couleur déterminée dans la ligne 10 correspond à « Rouge ».

Si c’est bien le cas, nous pouvons afficher la lettre « l », et dans le cas contraire, nous n’affichons rien.

Attention de bien utiliser des références relatives en bloquant la ligne de la cellule G10 et la lettre de la cellule F11.

Pour cela, il suffit d’appuyer sur la touche F4 du clavier.

Cela va nous permettre d’étendre la formule sur toutes les cellules du tableau en utilisant la poignée de recopie, en permettant à Excel d’ajuster automatiquement les références de cellules.

Ces formules fonctionnent de la même manière que celles pour le feu rouge, mais vérifient si la couleur correspond à « Orange » (F12) ou « Vert » (F13).

Avec ces formules en place, notre système de feux tricolores est maintenant dynamique et s'ajustera automatiquement en fonction des données du restaurant sélectionné dans la cellule M6.

3. Création des feux tricolores

Maintenant que nos formules sont en place, passons à la partie la plus amusante : la création visuelle de nos feux tricolores !

   3.1. Dessin du cadre du feu tricolore avec des formes

Commençons par créer le cadre de notre feu tricolore.

Pour cela, nous allons utiliser les outils de dessin d'Excel.

Nous nous rendons dans l'onglet « insertion » du ruban Excel, puis nous cliquons sur « formes ».

Dans le menu déroulant, nous choisissons le rectangle arrondi.

Nous dessinons ensuite un rectangle vertical arrondi à côté de notre tableau de données.

Pour obtenir des proportions parfaites, nous maintenons la touche [Maj] enfoncée tout en dessinant.

Une fois le rectangle dessiné, nous cliquons dessus avec le bouton droit et sélectionnons « format de la forme ».

Dans le panneau qui s'ouvre, nous réglons la hauteur à 7 cm et la largeur à 2,5 cm.

Pour donner du relief à notre feu tricolore, nous allons ajouter un effet d'ombre.

Toujours dans le panneau « format de la forme », nous allons dans la section « effets », puis « ombre ».

Nous choisissons l'option « décalage diagonal bas droit » :

Enfin, nous changeons la couleur de remplissage en noir et nous supprimons la bordure pour un aspect plus réaliste.

   3.2. Création des « ampoules » du feu tricolore

Maintenant que nous avons notre cadre, ajoutons les « ampoules » de notre feu tricolore.

Nous retournons dans le menu « formes » et cette fois-ci, nous choisissons l'outil « ovale ».

Nous dessinons trois cercles parfaits (en maintenant la touche [Maj] enfoncée) à l'intérieur de notre rectangle arrondi.

Astuce : pour aligner parfaitement nos cercles, nous pouvons les sélectionner tous les trois (en maintenant la touche [Ctrl] enfoncée), puis utiliser les outils d'alignement dans l'onglet « format » du ruban.

Pour chaque cercle, nous allons dans le « format de la forme » et nous réglons la hauteur et la largeur à 2 cm.

Nous supprimons également la bordure.

Ensuite, nous colorons chaque cercle : rouge pour le cercle du haut, jaune pour celui du milieu et vert pour celui du bas.

Pour un effet plus réaliste, nous réglons la transparence de chaque cercle à 60% dans les options de remplissage.

   3.3. Liaison des feux aux formules conditionnelles

Nous voici arrivés à l'étape cruciale : lier nos feux tricolores à nos formules conditionnelles.

Tout d'abord, nous sélectionnons toutes les cellules contenant nos formules (de E2 à G4), puis nous allons dans l'onglet « Accueil » du ruban, et dans la section « Police », nous choisissons la police « Wingdings ».

Cette police va transformer notre « l » en un petit point noir.

Ensuite, nous créons une zone de texte pour chaque « ampoule » de notre feu tricolore.

Pour cela, nous allons dans l'onglet « insertion », puis nous cliquons sur « zone de texte ».

Nous dessinons une petite zone de texte sur chaque cercle de notre feu tricolore et dans chacune de ces zones de texte, nous entrons une formule qui fait référence à la cellule correspondante de nos formules conditionnelles, en tapant le symbole « = » dans la barre des formules, puis en cliquant sur le point correspondant.

Par exemple, pour le feu rouge de la satisfaction client, nous entrons =E2 dans la zone de texte.

Nous répétons cette opération pour chaque « ampoule » de chaque feu tricolore, en veillant à bien lier chaque zone de texte à la bonne cellule.

Enfin, nous formatons nos zones de texte : nous supprimons la bordure, nous réglons la couleur du texte pour qu'elle corresponde à la couleur de l'« ampoule » (rouge, jaune ou vert), et nous augmentons la taille de la police pour que le point remplisse bien le cercle.

Astuce : pour copier rapidement le format d'une zone de texte à une autre, nous pouvons utiliser l'outil « reproduire la mise en forme » (le petit pinceau dans l'onglet « accueil »).

4. Mise en forme finale et astuces

Nous y sommes presque ! il ne nous reste plus qu'à peaufiner notre tableau de bord pour le rendre vraiment impactant.

   4.1. Alignement et distribution des feux tricolores

Pour que notre tableau de bord soit visuellement agréable, nous devons aligner correctement nos feux tricolores.

Nous sélectionnons tous nos feux tricolores (en maintenant la touche [Ctrl] enfoncée), puis nous utilisons les outils d'alignement dans l'onglet « format » du ruban.

Nous choisissons « aligner au milieu » pour les aligner verticalement, puis « distribuer horizontalement » pour les espacer de manière égale.

   4.2. Ajout des étiquettes pour chaque critère

Pour plus de clarté, ajoutons des étiquettes à nos feux tricolores.

Nous créons une zone de texte au-dessus de chaque feu et y inscrivons le critère correspondant : « satisfaction client », « temps d'attente » et « chiffre d'affaires ».

Nous formatons ces étiquettes pour qu'elles soient bien lisibles : police Arial, taille 10, gras.

Nous les alignons ensuite avec nos feux tricolores.

   4.3. Ajout des lueurs sur les feux

Pour accentue l’impression de lueur sur les feux, nous pouvons également ajouter une lueur sur ces derniers.

Pour cela, nous sélectionnons le premier (feu rouge), puis nous effectuons un clic droit sur celui-ci afin de sélectionner « Format de la forme ».

Dans la liste des effets, nous pouvons sélectionner l’éclat rouge :

Nous répétons ensuite cette opération pour les autres couleurs de feu.