(Last Updated On: )

Voici une approche générale de la conception de tableaux de bord sous Excel. Chaque tableau de bord est unique car il répond à une problématique spécifique, néanmoins je pense que la réalisation de tableaux de bord sous Excel peut suivre ces quelques étapes principales.

1. Déterminez l’objectif du tableau de bord

Déterminez les exigences du tableau de bord avec les utilisateurs. Ce processus appelé élicitation a pour but de cerner les besoins (à quelle(s) question(s) doit répondre le tableau de bord) et de délimiter le périmètre du tableau de bord (hypothèses, limites imposées, technologie utilisée, etc.).

Je vous conseille fortement d’écrire l’objectif et de vous assurer que les utilisateurs soient tous d’accord et ont bien compris la même chose.

Identifiez ensuite les interlocuteurs qui pourront vous aider dans la conception du tableau de bord (fournisseurs de données, experts sur la partie métier, etc.)

Il faut vraiment considérer quelles sont les questions ou problématiques auxquelles les utilisateurs cherchent à répondre ou à résoudre en se mettant à leur place et ne pas avoir peur de poser des “questions bêtes”. Cette étape est cruciale car si vous ne le faites pas au départ, il y a de fortes chances de devoir recommencer votre travail plus tard car il ne correspondra pas aux attentes.

Exemples de problématiques :

• Le tableau de bord doit-il présenter certains indicateurs de performance clés (KPI), tels que : a-t-on atteint nos objectifs de ventes mensuelles ?
• Le tableau de bord cherche-t-il à alerter en cas de problème : par exemple, l’inventaire de produits est inférieur au stock minimum fixé
• Le tableau de bord cherche-t-il à donner un aperçu général de l’activité, d’un département : par exemple, montant des ventes, montant des coûts, marge brute, etc.
• Le tableau de bord cherche-t-il à mettre en lumière les aspects à améliorer dans un processus : par exemple, améliorer les délais de facturation, de traitement des réclamations clients, etc.

Une fois les exigences connues, nous pouvons réaliser une esquisse de tableau de bord.

2. Créez une maquette de votre tableau de bord Excel

Prenez une feuille et un stylo et dessinez une esquisse du tableau de bord. Déterminez l’emplacement des valeurs clés à présenter, les graphiques, les tableaux de valeurs, les boutons de choix, etc.

Les tableaux de bord fournissent un aperçu et une réponse à une question ou à un problème spécifique. Votre objectif ici est de répondre à la question ou de résoudre le problème et de le faire d’une manière claire et facile à comprendre.

A ce stade, ne vous préoccupez pas d’implémenter des fonctionnalités trop complexes, mais concentrez-vous simplement sur la résolution du problème ou la réponse à la question.

Assurez-vous de faire le tour des personnes qui utiliseront le tableau de bord pour vous assurer que vous avez bien saisi tout ce qui sera nécessaire pour le tableau de bord final. Si les personnes semblent avoir des avis divergents, réunissez-les autour d’une table (ou d’un café..) pour les faire converger vers la solution à apporter.

3. Identifiez les sources de données, et créez une ou plusieurs feuilles de données Excel

En interrogeant vos interlocuteurs, vous savez normalement de quelles données vous avez besoin.

• Listez les différentes sources de données nécessaires :
  • S’il y a plusieurs sources, décidez si vous aurez besoin de plusieurs feuilles de données pour le tableau de bord
  • Créer une feuille ou plusieurs feuilles de données en fonction de leur type, de la fréquence de mise à jour, de leur interdépendance entre elles (regroupez les données liées dans un même classeur pour faciliter les opérations comme RECHERCHEV, INDEX-EQUIV, SOMME.SI, etc.)
• Les données sont-elles bien structurées ou faut-il d’abord les transformer pour pouvoir les utiliser dans Excel ?
  • Les données dans Excel doivent être structurées de manière à avoir des en-têtes de colonne explicites et uniques et une seule ligne pour chaque enregistrement de données. Cela facilite la synthèse des données avec des formules et des tableaux croisés dynamiques
  • Si les données ne sont pas structurées, utilisez soit la fonctionnalité Convertir (texte en colonnes) dans le menu Données, soit le nouveau menu puissant Transformer et Récupérer d’Excel 2016 pour transformer les données dans un format utilisable par Excel
• A quelles fréquences les données doivent-elles être mises à jour ?
  • Si les données doivent être souvent mises à jour, utiliser une connexion directe aux données peut être judicieuse, comme :
    • Connexion à une base de données ACCESS
    • Connexion à un service en ligne (Sharepoint)
    • Connexion à un fichier texte
    • etc.
      
  • Sinon un simple copier-coller en valeurs dans une nouvelle feuille fera parfaitement l’affaire
• Choisissez la méthode d’importation dans Excel (du plus simple au plus élaboré) :
  • Copier-coller des valeurs dans le fichier Excel du tableau de bord puis convertissez les données en Table
  • TCD connecté à une base dans un fichier Excel indépendant
  • Connexion directe à une base de données externe : via le menu Transformer et Récupérer d’Excel 2016
  • Création d’un modèle de données PowerPivot avec importation des données dedans
• Ayez un regard critique sur les données, et ceci à différents stades du projet :
  • Au niveau du choix des données, par exemple si vous avez le choix entre plusieurs sources, laquelle allez-vous choisir (quels sont les critères limitants ? taille des données, niveau de détail requis ? etc…)
  • Au niveau du traitement des données avant utilisation dans le tableau de bord : les données sont-elles propres (absence de “vide”, erreurs dans les données, formats homogènes, etc…
  • Au niveau de la conception du tableau de bord : les données sont-elles exhaustives (échantillon d’une population suffisant, périmètre bien couvert, périmètre d’exclusion…)
  • Au niveau de la validation des résultats : après analyse des premiers résultats, les données sont-elles bien employées, y a-t-il des valeurs aberrantes à exclure de l’analyse (voir cet article pour une méthode visuelle simple pour repérer les valeurs aberrantes) ? etc.

4. Créez une feuille de calcul dans Excel

Utilisez des formules, des tableaux croisés dynamiques et des filtres dans votre feuille de calcul pour créer le moteur du tableau de bord Excel. Votre tableau de bord sera littéralement alimenté par cette feuille de calcul donc elle joue un rôle essentiel dans le produit final.

• Créez et résumez les données à partir de vos feuilles de calcul pour fournir les réponses à la question posée. Voici une liste de formules essentielles pour construire une analyse et synthétiser des données :
  • SIERREUR
  • SOMME.SI et SOMME.SI.ENS
  • RECHERCHEV
  • INDEX + EQUIV
  • NB.SI
  • MIN/MAX et PETITE/GRANDE.VALEUR
  • LIREDONNEESTABCROISEDYNAMIQUE
  • Formules dates
  • Formules texte
  • Fonctions statistiques (MOYENNE, MEDIANE, ECARTTYPE, etc.)
• Structurez votre feuille de calcul de manière organisée :
  • Créez des blocs de données et formules séparées par des lignes et colonnes vides
  • Mettez des titres explicites en haut de ces blocs de calculs
  • Ajoutez des commentaires aux cellules pour expliquer une méthode de calcul par exemple
• Utilisez les plages nommées dans la feuille de calcul pour faciliter la référence aux cellules dans le tableau de bord Excel

La feuille de synthèse du tableau de bord ne comportera idéalement aucune formule de calcul et se référera simplement aux cellules nommées que vous avez créées dans la feuille de calcul

5. Créez une feuille de synthèse : votre tableau de bord Excel

Utilisez les informations calculées dans la feuille de calcul pour construire le tableau de bord que vous avez esquissé à l’étape 2.

• Est-ce que votre tableau de bord contient bien la réponse à la problématique posée à l’étape 1 ?
• Faites de votre mieux pour vous assurer que le tableau de bord est entièrement visible sur une page
  • Définir une zone d’impression pour cela
  • Sélectionnez les marges adéquates et l’option “Ajuster sur 1 page en largeur et sur 1 page en hauteur”

• Créez les contrôles interactifs de votre tableau de bord

Si vous avez utilisé des tableaux croisés dynamiques pour analyser et synthétiser les données dans votre feuille de calcul, vous pouvez créer des segments pour sélectionner les données du tcd et ensuite déplacer par simple couper-coller le segment dans la feuille de synthèse du tableau de bord

• Construisez le reste de votre tableau de bord
  • Ajoutez des graphiques et des blocs d’information récapitulatifs qui renvoient aux de données de la feuille de calcul et qui peuvent également interagir avec vos contrôles interactifs
  • Ajoutez des mises en forme conditionnelle sur les cellules pour souligner des résultats importants
  • Formatez le tableau de bord pour le rendre clair et professionnel (lisez mon e-book sur ce sujet)

• Faites un examen complet du tableau de bord pour vous assurer que vous avez bien intégré toutes les exigences requises à l’étape 1 par les utilisateurs
• Présentez-le à quelques interlocuteurs soigneusement sélectionnés (des interlocuteurs “amis” favorables et impliqués dans le projet 🙂 )
• Maintenant que le format est validé, testez le tableau de bord et examinez et vérifiez vos résultats en exécutant manuellement des calculs et des tests de vérification
• Corrigez les bug que vous pourriez rencontrer
• Masquez la feuille de calcul et toutes les feuilles de données pour empêcher les utilisateurs du tableau de bord de modifier accidentellement les calculs et données dans le fichier Excel

Enregistrez le tableau de bord et partagez-le avec vos utilisateurs enthousiastes !  🙌

Pour aller plus loin, téléchargez mon guide gratuit “6 ASTUCES FACILES POUR CONSTRUIRE DES RAPPORTS EXCEL CLAIRS ET PROFESSIONNELS”.

Enfin si vous souhaitez apprendre une méthode rapide et efficace pour collecter et analyser vos données dans Excel, et créer des tableaux de bord dynamiques calculant des Year-to-Date, variations N/N-1, etc. AUTOMATIQUEMENT, je vous propose un programme 100% en ligne spécialement conçu pour découvrir et maîtriser les Power Tools de Microsoft facilement.