22 Le tableau de bord

Vos données sont prêtes à être exploitées, il vous faut créer la maquette qui va accueillir l’analyse de ces données.

La maquette, c’est la forme de votre futur tableau de bord. Je vous conseille de la préparer avant d’y ajouter les données.

Ah bon, pourquoi ?

Pour vous focaliser sur la question posée, sans être influencé par les données. À ce stade, vous allez donc utiliser de fausses données, que vous inventerez !

Définissez les éléments pertinents pour votre analyse

Qu’il soit sous forme de tableau, de graphique ou autre, votre tableau de bord doit avoir comme unique objectif de répondre à la question posée.

Petit rappel des deux questions posées :

  • Quel est l’état des ventes de notre société en Europe de l’Ouest, par catégorie de vêtements et par trimestre depuis 2019 ?
  • Est-ce que les pantalons de la collection 2018 se vendent aussi bien que ceux de la collection 2020 ?

Le “mauvais exemple”

Si vous répondiez à ces questions de manière superficielle, vous pourriez obtenir le résultat suivant :

Partie d’un classeur Excel dans lequel on retrouve des données relatives à l’état de santé des ventes de Zarigual en Europe de l’Est. Un premier tableau démontre l’évolution des ventes par trimestre. Les trimestres se retrouvent dans la ligne 4 et les catégories de vêtements se retrouvent dans la colonne A. Un second tableau représente les ventes de pantalons si créés >= 2 ans ou si créés < 2 ans.

Ici, le tableau répond bien aux questions posées, cependant :

  • le lecteur est obligé de “lire” chacune des données pour le comprendre ;
  • ce tableau de bord n’est pas présentable : aucune couleur, aucun graphique, il n’invite pas le lecteur à l’analyser ;
  • l’analyse de ce tableau est très chronophage.

La bonne pratique

Avant de créer la maquette, posez-vous les questions suivantes :

  • Quel type de tableau/graphique est le plus adapté à la demande ?
  • Une “évolution” implique un graphique en courbes ou un histogramme.
  • Une “comparaison” peut être mise en valeur par une mise en forme conditionnelle, ou encore un graphique.
  • Est-ce que je peux anticiper les questions qui viendront après l’analyse ci-dessus ?
  • Prévoir une analyse plus détaillée par pays permettrait d’affiner la réponse à la première question.
  • Je peux anticiper de présenter le détail des vêtements à l’intérieur des catégories.
  • Est-ce que les couleurs de mon tableau de bord vont aider le lecteur ?
  • Si chaque catégorie a une couleur unique tout le long du tableau de bord, cela en facilitera la lecture.

Excel regorge de fonctionnalités de mise en forme que vous connaissez déjà (format de cellule, couleurs, bordures, graphiques simples) ! Utilisez-les en fonction des réponses aux questions ci-dessus.

Ainsi, on peut tout aussi bien répondre à la question avec un tableau de bord de ce type :

Tableau de bord Excel de l’état de santé des ventes de Zarigual en Europe de l’Est. Un tableau démontre l’évolution des ventes par trimestre. Les trimestres se retrouvent dans la ligne 5 et les catégories de vêtements se retrouvent dans la colonne B. Les données du tableau sont en euros et la cellule des données supérieure ou égale à 3 est mauve. En dessous de ce tableau, on retrouve un graphique à ligne brisée (courbe). Les trimestres sont à l’axe des X et les ventes en euros sont à l’axe des Y. Les 3 lignes sur le graphique représentent les 3 catégories de vêtement. On retrouve ensuite 3 graphiques à barres représentant respectivement les top 5 vêtements de la catégorie 1, 2 et 3.

Exemple de tableau de bord mis en forme

Beaucoup plus lisible, n’est-ce pas ?

Vous voulez appliquer un format de cellule qui n’existe pas dans la liste d’Excel ? N’hésitez pas à utiliser un format personnalisé ! Regardez comment cela fonctionne, grâce à l’aide d’Excel sur le sujet.

Et voilà, vous avez créé votre maquette ! Vous pouvez la faire valider par votre responsable et gagner un temps précieux ! Je me rappelle avoir dû remettre en forme tout un rapport car les couleurs ne plaisaient pas au client…

Anticipez la mise en forme conditionnelle de vos données

Avez-vous déjà entendu parler de la mise en forme conditionnelle d’Excel ? Si ce n’est pas le cas, je vous invite à lire l’encadré suivant.

Utilisez la mise en forme conditionnelle

La mise en forme conditionnelle vous permet de définir la mise en forme d’une cellule si une condition est remplie dans le contenu de cette cellule.

Par exemple, vous voulez que dans le tableau de chiffre d’affaires les valeurs inférieures à 100 000 euros soient mises en rouge.

Un tableau dans Excel (qui occupe la plage B3:E9) possède les noms dans la colonne B, les quantités dans la colonne C, les tarifs dans la colonne D et les chiffres d’affaires dans la colonne E. Les chiffres d’affaires sont 144000, 87500, 125700, 91200, 110100 et 112900. Les chiffres d’affaires 87500 et 91200 sont écrits en rouge avec un arrière-plan rouge pâle alors que le reste des chiffres d’affaires sont écrits en noir avec un arrière-plan blanc.

Les chiffres d’affaires inférieurs à 100 000 euros apparaissent en rouge

Bien entendu, vous pourriez colorer les cellules qui remplissent la condition (inférieures à 100 000 euros), mais dans ce cas, si le chiffre change et passe au-dessus de 100 000 euros, la cellule conservera la mise en forme et restera en rouge.

Pour éviter cela, il vous faut utiliser la mise en forme conditionnelle.

Pour appliquer cette mise en forme conditionnelle :

  • Sélectionnez les cellules concernées par cette mise en forme.
  • Cliquez sur l’onglet Accueil.
  • Dans le groupe Styles, cliquez sur l’icône Mise en forme conditionnelle
  • Sélectionnez Règles de mise en surbrillance des cellules.
  • Puis cliquez sur Inférieur à…

La boîte de dialogue ci-dessous apparaît :

Boîte de dialogue intitulée Inférieur à. Il est écrit Mettre en forme les cellules dont le contenu est inférieur à : 100000 avec remplissage rouge clair avec texte rouge foncé.

Saisissez 100000 dans la boîte de dialogue

  • Cliquez sur OK pour valider votre mise en forme conditionnelle.

En cliquant sur OK, la mise en forme que vous avez définie s’applique aux cellules qui remplissent la condition, c’est-à-dire celles qui sont inférieures à 100 000 euros.

Si vous voulez supprimer la mise en forme automatique, procédez de la façon suivante :

  • Sélectionnez les cellules concernées par la mise en forme.
  • Cliquez sur l’onglet Accueil.
  • Dans le groupe Styles, cliquez sur l’icône Mise en forme conditionnelle.
  • Sélectionnez Effacer les règles.
  • Puis cliquez sur Effacer les règles des cellules sélectionnées.

Dans l’exemple qui suit, vous allez faire apparaître trois indicateurs de couleurs en fonction du chiffre d’affaires.

Un tableau dans Excel (qui occupe la plage B3:E9) possède les noms dans la colonne B, les quantités dans la colonne C, les tarifs dans la colonne D et les chiffres d’affaires dans la colonne E. Les chiffres d’affaires sont 144000, 87500, 125700, 91200, 110100 et 112900. Dans les cellules pour les chiffres d’affaires 144000 et 125700, il y a un cercle vert. Dans les cellules pour les chiffres d’affaires 110100 et 112900, il y a un cercle jaune. Dans les cellules pour les chiffres d’affaires 87500 et 91200, il y a un cercle rouge.

La mise en forme conditionnelle avec 3 indicateurs

Pour appliquer cette mise en forme conditionnelle :

  • Sélectionnez les cellules concernées par cette mise en forme.
  • Cliquez sur l’onglet Accueil.
  • Dans le groupe Styles, cliquez sur l’icône Mise en forme conditionnelle.
  • Sélectionnez Jeux d’icônes.
  • Dans Formes, sélectionnez 3 indicateurs (sans bordures).

La mise en forme conditionnelle est très utile pour mettre en évidence des éléments quand une condition est remplie. Son utilisation est vaste, et de nombreuses possibilités existent. Je vous laisse découvrir tout son potentiel et faire preuve de créativité !

En résumé

  • La mise en forme conditionnelle met en évidence des valeurs lorsqu’une condition est vérifiée.
  • Vous pouvez créer vos mises en forme personnalisées en spécifiant vos conditions.
  • Vous pouvez utiliser des mises en forme “automatiques”, il en existe de nombreuses dans Excel.

Dans notre cas, nous souhaitons utiliser la mise en forme conditionnelle (ou MEF), en combinant plusieurs conditions :

  • les valeurs qui sont dans le top 5 % des ventes sont en fond vert foncé ;
  • les autres valeurs qui sont dans le top 15 % des ventes sont en fond vert moyen ;
  • les autres valeurs qui sont dans le top 25 % des ventes sont en fond vert clair.

Pour cela, vous allez créer 3 mises en forme conditionnelles, et leur donner une priorité.

Première mise en forme conditionnelle

  • Sélectionnez les cellules de votre tableau principal.
  • Cliquez sur l’icône “Mise en forme conditionnelle”, puis sur la commande « Nouvelle règle…”.
  • Dans la boîte de dialogue qui apparaît, sélectionnez la règle “Appliquer une mise en forme uniquement aux valeurs rangées parmi les premières ou les dernières valeurs”.
  • Sélectionnez la valeur 25 %, et choisissez une couleur de remplissage vert clair.

Fenêtre intitulée Modifier la règle de mise en forme. Le type de règle sélectionné est « Appliquer une mise en forme uniquement aux valeurs rangées parmi les premières ou les dernières valeurs ». La description de la règle est « Appliquer une mise en forme aux valeurs situées dans la plage : premiers 25 % de la plage sélectionnée ». L’aperçu démontre une cellule vert pâle avec du texte noir.

Sélectionnez le premier format conditionnel

  • Cliquez sur OK.

Deuxième mise en forme conditionnelle

Effectuez les mêmes manipulations pour créer la deuxième règle de MEF conditionnelle, avec un critère à 15 %, et une couleur de remplissage vert moyen.

Troisième mise en forme conditionnelle

Effectuez les mêmes manipulations pour créer la troisième règle de MEF conditionnelle, avec un critère à 5 %, et une couleur de remplissage vert foncé.

Le résultat vous donne ceci :

Tableau Excel avec les catégories de vêtements 1, 2 et 3 à la verticale ainsi que les trimestres 1, 2, 3, 4, 5, 6, 7 et 8 à l’horizontale. Les données du tableau sont en euros. Pour la catégorie de vêtement 1, la cellule du trimestre 2 (3,5 euros), 6 (3,7 euros) et 7 (3,9 euros) est en vert pâle. Encore pour la catégorie de vêtement 1, la cellule du trimestre 3 (4,8 euros) et 5 (4,3 euros) est en vert et la cellule du trimestre 8 (5,2 euros) est en vert foncé. Le reste des cellules sont blanches.

Résultat avec 3 critères (Top 5 %, Top 15 % et Top 25 % des valeurs)

La lecture est maintenant orientée vers les meilleurs résultats de vente !

À vous de jouer !

Vous venez de créer les 2 premiers tableaux qui vous permettront de répondre aux questions de votre responsable.

Passez maintenant à la mise en forme ! Téléchargez ce fichier et réalisez les opérations suivantes :

  • Dans le premier tableau, créez une mise en forme conditionnelle qui mettra en valeur les 2 évolutions les plus fortes :
  • la plus forte évolution en fond vert foncé et en police blanche ;
  • la 2e plus forte évolution en fond vert clair.
  • Dans le 2e tableau, créez une mise en forme conditionnelle qui définit la couleur de remplissage en jaune, pour toute évolution inférieure à la cellule G13.

Corrigé

Vous pouvez consulter le corrigé.

Éléments clés à retenir

  • Définissez les éléments les plus pertinents qui composeront votre tableau de bord.
  • Concevez une mise en forme conditionnelle à plusieurs critères.
  • Ajoutez le logo de votre entreprise dans l’en-tête ou le pied de page de votre tableau de bord.
  • Optimisez la forme de votre maquette pour l’impression, et la lecture sur tout appareil, avec le format rectangle.

Références

Coulbault, D., Lejewski, C., Nagy, Y. (2021). Maîtrisez les fondamentaux d’Excel. OpenClassrooms.  https://openclassrooms.com/fr/courses/7168336-maitrisez-les-fondamentaux-dexcel

Coulbault, D. (2021). Perfectionnez-vous sur Excel. OpenClassrooms. https://openclassrooms.com/fr/courses/7139456-perfectionnez-vous-sur-excel