Cartes d’indicateurs KPI et mises en évidence conditionnelles

Une carte KPI est un chiffre clé unique qui indique au lecteur la principale information à retenir : le revenu total, le nombre de commandes du jour ou la valeur moyenne d’une commande. Les tableaux de bord commencent généralement par une rangée de ces cartes.

Une bonne carte réunit trois éléments : un libellé clair, le grand nombre issu d’une formule et souvent une petite comparaison, par exemple l’évolution par rapport à la période précédente.

Dans cette leçon, vous allez créer des valeurs KPI à l’aide de formules d’agrégation, ajouter des comparaisons entre périodes et utiliser une logique conditionnelle pour mettre en évidence les valeurs qui nécessitent votre attention.

Commencez par l’indicateur principal. Le revenu total d’une feuille Ventes n’est qu’un SUM appliqué à la colonne des montants.

Placez cette formule dans la cellule de valeur de la carte :

Pour compter les commandes, utilisez COUNTA sur une colonne qui contient toujours une valeur, comme un ID de commande. Le libellé de la carte, dans une simple cellule de texte placée au-dessus, indique ce que représente le nombre. Limitez chaque carte à un seul nombre pour qu’elle soit comprise instantanément.

De nombreux KPI sont des ratios. La valeur moyenne d’une commande divise le revenu total par le nombre de commandes. Construisez-la à partir de deux agrégats.

Une méthode compacte utilise directement AVERAGE :

Si vous préférez la calculer vous-même, divisez la valeur de votre carte du total par celle de votre carte du nombre. Dans les deux cas, cette carte affiche désormais le montant habituel d’une vente et se met à jour chaque fois que les données changent.

Un KPI est plus parlant à côté d’un objectif. Supposons que votre objectif mensuel de revenus se trouve en B1. Calculez dans quelle mesure vous êtes au-dessus ou au-dessous de cet objectif.

L’écart correspond à la valeur actuelle moins l’objectif :

Vous pouvez aussi l’exprimer en pourcentage de l’objectif avec =SUM(Sales!C2:C500)/B1. Afficher 1,12 indique au lecteur que vous avez atteint 112 % de l’objectif. La comparaison donne du sens à un nombre brut.

Les lecteurs aiment voir la dynamique. Calculez la variation en pourcentage par rapport à une période précédente. Supposons que le total de cette période se trouve en D2 et celui de la période précédente en D3.

La formule de croissance est :

Si le total de cette période est 120 et celui de la précédente 100, vous obtenez 0,2, soit une croissance de 20 %. Mettez la cellule au format pourcentage. Un petit indicateur de hausse ou de baisse comme celui-ci transforme une carte statique en tendance visible en un coup d’œil.

Vous pouvez indiquer la direction par des mots ou des symboles à l’aide de IF. Lisez la cellule de variation et choisissez un libellé.

Cela renvoie un indicateur de hausse ou de baisse accompagné du pourcentage :

Lorsque la variation en E1 est positive, un message de hausse s’affiche ; sinon, un message de baisse. La concaténation du texte et d’un nombre mis en forme garde la carte compacte et lisible, sans nécessiter de graphique.

La mise en forme conditionnelle modifie la couleur d’une cellule en fonction d’une règle, afin que les valeurs importantes ressortent. Sélectionnez vos cellules KPI, ouvrez Mise en forme conditionnelle et ajoutez une règle pilotée par une formule.

Pour signaler en rouge toute carte inférieure à l’objectif, utilisez une règle de formule telle que :

Les cellules pour lesquelles la règle renvoie TRUE reçoivent la mise en forme de votre choix. C’est ainsi qu’un tableau de bord devient rouge lorsque les ventes n’atteignent pas l’objectif et vert lorsqu’elles le dépassent, sans que personne ait besoin d’examiner attentivement les chiffres.

Parfois, vous voulez que le signalement apparaisse sous forme de texte dans la cellule elle-même, et pas seulement en couleur. IF associé à des opérateurs de comparaison crée un libellé d’état.

Pour étiqueter les performances par rapport à l’objectif :

Cela affiche Dans les temps lorsque l’objectif est atteint et En retard lorsque le résultat est inférieur. Une colonne d’état de ce type s’accorde bien avec une mise en forme conditionnelle qui colore le libellé, fournissant ainsi un signal visuel et textuel.

Les vrais KPI comportent souvent plus de deux états : bon, attention, critique. La fonction IFS évalue les conditions dans l’ordre et renvoie la première correspondance, ce qui est plus lisible que des IF imbriqués.

Pour classer les performances en trois niveaux :

IFS évalue les conditions de haut en bas : placez donc la condition la plus stricte en premier. Associez chaque niveau à une règle de couleur et vos cartes communiqueront l’état en un seul coup d’œil.

Au-delà des règles individuelles, les échelles de couleurs appliquent un dégradé à une plage de nombres afin que les valeurs les plus élevées et les plus faibles ressortent sans aucune formule. Elles sont idéales pour une colonne de totaux régionaux ou de chiffres quotidiens.

Sélectionnez la plage, ouvrez Mise en forme conditionnelle et choisissez une échelle de couleurs, par exemple du vert au rouge.

• Les valeurs les plus élevées apparaissent en vert, les plus faibles en rouge et les valeurs intermédiaires se fondent entre les deux.
• L’échelle se recalcule automatiquement lorsque les données changent.

Les échelles de couleurs s’associent parfaitement aux cartes KPI : les cartes affichent le chiffre clé, tandis que la colonne mise en couleur montre la position de chaque valeur dans la distribution.

Disposez vos cartes en une rangée claire en haut du tableau de bord. Chaque carte est un petit bloc composé d’une cellule de libellé, d’une formule de valeur et d’une comparaison ou d’un état en dessous.

• Carte 1 : Revenu total, =SUM(Sales!C2:C500), avec un écart par rapport à l’objectif.
• Carte 2 : Commandes, =COUNTA(Sales!A2:A500).
• Carte 3 : Valeur moyenne d’une commande, =AVERAGE(Sales!C2:C500), avec un pourcentage de croissance.

La mise en forme conditionnelle colore les cellules de comparaison. La rangée fournit un résumé instantané avant que le lecteur ne fasse défiler la page pour consulter les détails.

Vous avez créé la couche des indicateurs principaux d’un tableau de bord :

• Des formules d’agrégation comme SUM, COUNTA et AVERAGE ont produit les grands nombres KPI.
• Les formules d’écart et de croissance ont ajouté un contexte de comparaison par rapport à un objectif et à une période antérieure.
• IF et IFS ont transformé les nombres en libellés d’état comme Dans les temps, À surveiller et Critique.
• La mise en forme conditionnelle, avec des règles fondées sur des formules, a automatiquement coloré les cellules pour signaler ce qui compte.

Associé aux tableaux récapitulatifs, aux tableaux croisés créés avec des formules et aux listes déroulantes interactives des leçons précédentes, cet ensemble vous permet maintenant de créer un tableau de bord complet, actualisé et piloté par des formules.