Avoir de bons tableaux de bord est une nécessité pour toutes les entreprises et tous les services. Excel est un outil privilégié pour y arriver : inclus dans le pack Office, souple et disposant de nombreux outils et fonctionnalités dédiés aux tableaux de bord. Néanmoins, quid de la ou des sources de données, des indicateurs à présenter, des fonctionnalités Excel à utiliser, de la présentation graphique, de l’ergonomie du tableau de bord ?

Dans cet article, nous allons voir les fonctionnalités de base mais il faut garder à l’esprit qu’Excel est un outil très puissant et évolutif. Pour  aller plus loin, il sera peut-être nécessaire d’envisager la mise à jour de votre tableau de bord en un seul clic, qui sera rendu possible via l’utilisation de macros. Dans ce cas, il pourra être nécessaire de faire appel à un développeur VBA.

Sources des données

Souvent des extractions provenant d’autres logiciels ou d’application Web, les sources de données ne sont pas toujours exploitables telles quelles : incomplètes, en csv, à retraiter, comprenant des doublons, etc. L’objectif étant d’obtenir une source de données si possible unique, propre, comprenant les axes d’analyse et leur valeur associée, en lignes (éviter les données croisées en lignes et colonnes).

Retraitement des données

Plusieurs fonctionnalités sur Excel permettent de retraiter les données provenant d’autres sources et de les rendre exploitables sur Excel :

  • Fonctionnalités de conversion des données (onglet Données, Convertir), les formules texte (STXT, GAUCHE, DROITE, CONCATENER, SUBSTITUE, etc.), les formules date (ANNE, MOIS, JOUR, DATE, FIN .MOIS, NO.SEMAINE, etc.), les formules logiques (SI, ET, OU, SIERREUR, etc.).
  • Fonctionnalités de traitement des doublons (Onglet Données, Suppression des doublons), mise en forme conditionnelle avec la mise en surbrillance des doublons, la fonction SI accompagné d’un tri, etc.
  • Fonctionnalités de transformation des sources en une seule source : les fonctions de recherche (RECHERCHEV, RECHERCHEH, INDEX, EQUIV, DECALER, TRANSPOSE, etc.), utilisation du modèle de données sur Excel qui permet de lier plusieurs sources entre elles.

Optimisation du stockage des données pour mise à jour rapide

La fonction Excel qui existe depuis Excel 2007 « Mettre sous forme de tableau » a de nombreux avantages pour la construction de tableaux de bord :

  • Mise à jour facilitée : il suffit de supprimer les dernières lignes du tableau (sauf la première ligne afin de conserver les formules éventuelles), puis de coller les données mises à jour.
  • Source fiable de vos tableaux croisés dynamiques après mis à jour de ces derniers (onglet Données, Actualiser Tout)
  • Les formules de retraitement des données peuvent être stockées dans les dernières colonnes et se mettront à jour automatiquement.

Les indicateurs, les axes d’analyse

Pour choisir de bons indicateurs, commencez par ceux spécifiques à votre branche. Idéal pour faire du benchmarking. Vous pourrez y ajouter les indicateurs propres à vos spécificités si nécessaire.

Les indicateurs doivent permettre de donner une vision claire et analytique d’une situation, être pertinents et valables sur la durée. Ce dernier point est particulièrement important : en effet un tableau de bord gagne en valeur d’analyse au fil des mois, des années qui s’écoulent. Les indicateurs choisis doivent donc être stables, et peut-être devriez-vous déterminer à quelle période du mois vous devez l’arrêter et vous y tenir (par exemple le 10 de chaque mois). L’indicateur est bien souvent un calcul (par exemple un ratio) qui peut être analysé de différentes manières en fonction des axes d’analyse (ex. : ventes réalisées par rapport aux ventes prévues en nombre et en montant par commerciaux, par direction régionale, etc.).

Les axes d’analyse peuvent être des zones géographiques, des noms de commerciaux, des types de clients, etc. Dans la source de données, nous vous conseillons qu’ils soient en-têtes de colonnes et détaillés par lignes.

Alors quelles fonctionnalités Excel ?

Nous avons déjà parlé de la mise sous forme de tableau : graphique, ergonomique, source solide des tableaux croisés dynamiques.

Bien sûr les tableaux croisés dynamiques sont à user et abuser. Equivalents des fonctions SOMME.SI, SOMME.SI.ENS, NB.SI, NB.SI.ENS, les tableaux croisés dynamiques sont à privilégier car plus complets, avec des possibilités de cumul des valeurs ou d’analyse des valeurs en pourcentages des colonnes, des lignes, etc.

Les graphiques classiques ou croisés dynamiques offrent la présentation visuelle et synthétiques indispensable à tout tableau de bord. Citons notamment le graphique combiné disponible à partir de la version Excel 2010.

Autres présentations graphiques utiles en fonction des cas : les graphiques Sparkline et la mise en forme conditionnelle.

Quant à l’ergonomie, citons les segments sur les axes d’analyses (depuis 2010) et sur les dates (chronologie uniquement depuis 2013). Ils permettent de filtrer d’un seul coup tous les tableaux croisés dynamiques liés à la même source.

En conclusion, Excel offre de nombreux outils afin de créer le tableau de bord qui répondra à toutes vos attentes ! Néanmoins, malgré toutes les astuces disponibles, la mise en place de ces tableaux de bord demande à la fois beaucoup de manipulations qui peuvent être sources d’erreur, et des connaissances solides des fonctionnalités du logiciel. Les Big DATA demandent également une réflexion supplémentaire quant à leur stockage surtout sur des tableaux de bord demandant une historisation des indicateurs sur la durée (film).