Comment créer un tableau de bord en Excel

La création d’un tableau de bord Microsoft Excel implique de tirer des informations de plusieurs sources afin de pouvoir visualiser ces données en un seul endroit. Excel offre différentes façons de le faire, ce qui en fait l’un des outils de reporting de tableau de bord les plus puissants que vous puissiez utiliser.

Qu’est-ce qu’un tableau de bord Excel ?

Un tableau de bord est un outil qui vous aide à contrôler et à analyser visuellement les mesures qui sont les plus importantes pour vous. Vous pouvez utiliser un tableau de bord pour suivre les performances de votre entreprise, l’efficacité d’un processus de fabrication ou les performances du personnel de votre service commercial.

Quelle que soit la façon dont vous utilisez un tableau de bord, le concept est toujours le même : les feuilles de calcul en arrière-plan tirent des données de fichiers, de services ou de connexions API à des bases de données et à d’autres sources. La feuille principale affiche les données de ces multiples feuilles à un seul endroit, où vous pouvez les consulter d’un seul coup d’œil.

Les éléments typiques d’un tableau de bord de données dans Excel sont les suivants

Vous pouvez créer deux types de tableaux de bord. Pour les rapports de tableaux de bord, vous pouvez créer un tableau de bord statique à partir de données figurant dans d’autres fiches que vous pouvez envoyer à quelqu’un dans un rapport Word ou PowerPoint. A tableau de bord dynamique est un document que l’on peut consulter dans Excel, et qui se met à jour chaque fois que les données des autres feuilles sont mises à jour.

Intégrer des données dans un tableau de bord Excel

La première phase de la création d’un tableau de bord Excel consiste à importer des données dans des feuilles de calcul à partir de diverses sources.

Parmi les sources potentielles d’importation de données dans Excel, on peut citer

• Autres fichiers de classeurs Excel
• Fichiers texte, CSV, XML ou JSON
• Base de données SQL
• Microsoft Access
• Azure Data Explorer
• Facebook et autres pages web
• Toute autre base de données qui supporte ODBC ou OLEDB
• Sources web (tout site web qui contient des tableaux de données)

Avec autant de sources de données potentielles, les possibilités de données que vous pouvez introduire dans Excel pour créer des tableaux de bord utiles sont illimitées.

Pour faire entrer une source de données :

• Ouvrez une feuille de calcul Excel vide. Sélectionnez le Données et dans le menu Obtenir des données sélectionnez le type de données que vous souhaitez, puis choisissez la source de données.

• Naviguez jusqu’au fichier ou à la source de données que vous souhaitez importer et sélectionnez-le. Sélectionnez Importer.

• Selon le type de source de données que vous choisissez, vous verrez différentes boîtes de dialogue pour transformer les données en format de feuille de calcul Excel.

• La feuille se remplira de toutes les données du fichier ou de la base de données externe.

• Pour rafraîchir les données afin qu’elles téléchargent régulièrement les modifications apportées à la source de données externe, sélectionnez le Rafraîchir sur le côté droit de l’icône Questions et connexions vitre.

• Sélectionnez les trois points à côté du EDIT au bas de la fenêtre de rafraîchissement, et sélectionnez Propriétés.

• Configurez les données à rafraîchir à partir de la source de données à intervalles réguliers en définissant le Rafraîchir toutes les xx minutes à l’intervalle que vous souhaitez mettre à jour les données.

  Le rafraîchissement des données provenant de sources externes est utile, mais il peut consommer du temps processeur si vous rendez le taux de rafraîchissement trop fréquent. Choisissez un taux de rafraîchissement qui permet de mettre à jour les données aussi souvent qu’elles sont modifiées à la source, mais pas au point de ne copier que les mêmes données à chaque fois.

• Répétez le processus ci-dessus dans de nouvelles feuilles de travail séparées jusqu’à ce que vous ayez importé toutes les données que vous souhaitez utiliser dans votre nouveau tableau de bord.
• Enfin, créez une nouvelle feuille de travail, placez-la comme première feuille de travail dans le classeur et renommez-la Tableau de bord.

Comment créer un tableau de bord Excel

Maintenant que vous avez toutes les données dont vous avez besoin dans votre classeur Excel, et que toutes ces données se rafraîchissent automatiquement, il est temps de créer votre tableau de bord Excel en temps réel.

L’exemple de tableau de bord ci-dessous utilisera des données météorologiques provenant de sites web de tout l’Internet.

• Créer un Graphique en barres pour afficher un point de données unique. Par exemple, pour afficher l’humidité relative actuelle (de 0 à 100 %), vous devez créer un graphique à barres avec 0 % comme point le plus bas et 100 % comme point le plus haut. Tout d’abord, sélectionnez l’option Insérer et sélectionnez ensuite le menu Colonne 2D en grappes diagramme à barres.

• Dans le Conception graphique dans le menu Données groupe, choisissez Sélectionnez les données.

• Dans le Sélectionnez la source de données qui s’ouvre, cliquez sur le bouton Plage de données des graphiques puis sélectionnez la cellule de la feuille de calcul que vous souhaitez afficher avec ce diagramme à barres.

• Modifiez le titre du graphique pour qu’il corresponde aux données que vous affichez. Mettez à jour les limites de l’axe pour qu’elles soient de 0 à 100 %. Ensuite, déplacez le graphique vers la zone du tiret où vous souhaitez l’afficher.

• Répétez les mêmes étapes ci-dessus pour créer des diagrammes en barres pour tous les autres points de données que vous souhaitez cartographier. Faites en sorte que la plage de l’axe soit au minimum et au maximum pour ces mesures. Par exemple, une bonne plage de pression barométrique serait de 28 à 32.

  Il est important de choisir la bonne fourchette de données car si vous n’utilisez que la valeur par défaut, l’échelle risque d’être trop grande pour les données, laissant la plupart des diagrammes à barres vides. Au lieu de cela, gardez les extrémités minimale et maximale de l’échelle de l’axe seulement légèrement plus basses et plus hautes que les valeurs extrêmes possibles de vos données.

• Créer un Graphique linéaire pour afficher une tendance des données. Par exemple, pour afficher un historique des températures locales pour votre région, vous devez créer un graphique linéaire couvrant le dernier nombre de jours de données que vous pouvez importer à partir du tableau du site web météo. Tout d’abord, sélectionnez le menu Insérer, puis le bouton Zone 2D de la carte.

• Dans le Conception graphique dans le menu Données groupe, sélectionnez Sélectionnez les données.

• Dans le Sélectionnez la source de données qui s’ouvre, cliquez sur le bouton Plage de données des graphiques puis sélectionnez les cellules de la feuille de calcul que vous souhaitez afficher avec ce graphique linéaire.

• Modifiez le titre du graphique pour qu’il corresponde aux données que vous affichez et déplacez le graphique vers la zone du tiret où vous souhaitez l’afficher.

  Les graphiques sont très flexibles lorsqu’on les place dans un tableau de bord. Vous pouvez modifier l’emplacement ainsi que la taille et la forme du widget de la carte. Utilisez cette flexibilité pour concevoir des tableaux de bord organisés qui fournissent autant d’informations à l’utilisateur dans un espace réduit.

• Créer un Textbox pour afficher les données de chaînes des feuilles que vous avez importées. Par exemple, pour voir les mises à jour des alertes météorologiques sur votre tableau de bord, reliez le contenu de la zone de texte à une cellule de la feuille de données importée. Pour ce faire, sélectionnez l’option Insérer dans le menu, sélectionnez Texteet sélectionnez ensuite le Textbox.

• Placez le curseur de la souris dans le champ de la formule, tapez = puis sélectionnez la cellule du tableau de données importées qui contient les données de chaîne que vous voulez afficher.

• Sélectionnez la zone de texte et utilisez le Format Forme sur la droite pour formater la zone d’affichage du texte dans votre tableau de bord.

• Vous pouvez également comparer deux points de données dans vos fiches de données importées en utilisant graphiques circulaires. Par exemple, vous pouvez afficher l’humidité relative sous la forme d’un diagramme circulaire. Sélectionnez d’abord les données que vous souhaitez afficher, puis dans le menu Insérer sélectionnez le menu Tarte 2D de la carte.

  Les diagrammes circulaires comparent deux ou plusieurs valeurs. Si vous affichez un pourcentage comme l’humidité relative, vous devrez peut-être créer manuellement une autre cellule qui soustrait cette valeur de 100 % pour la deuxième valeur à laquelle la comparer. Vous obtiendrez ainsi un graphique circulaire qui affiche la valeur en pourcentage comme une partie du total possible de 100 %.

• Modifiez le titre du graphique pour qu’il corresponde aux données que vous affichez, puis déplacez le graphique vers la zone du tiret où vous souhaitez l’afficher.

• En ajoutant différents types de graphiques de données, vous pouvez créer un tableau de bord utile qui affiche tous les types de données dans un tableau de bord pratique.

Ajoutez de la couleur à l’attrait visuel et au contexte

Une autre façon de rendre votre tableau de bord plus clair est de donner à vos diagrammes à barres un remplissage en dégradé qui affiche une couleur d’avertissement comme le rouge pour les régions des données qui peuvent ne pas être bonnes.

Par exemple, si vous voulez montrer qu’une humidité relative supérieure à 75 % est inconfortable, vous pouvez modifier le remplissage du gradient de l’histogramme simple en conséquence. Voici comment faire.

• Cliquez avec le bouton droit de la souris sur le bord extérieur d’un diagramme en barres et sélectionnez Zone du graphique de format.

• Sélectionnez le Remplir dans l’icône Zone du graphique de format et changez la sélection en Remplissage par gradient.

• Sélectionnez chaque icône de niveau le long de la ligne de remplissage du dégradé et modifiez la couleur et l’obscurité en fonction de la qualité du niveau. Dans cet exemple, l’humidité relative élevée s’estompe en rouge foncé.

• Répétez ce processus pour chaque carte où l’ajout d’un contexte de couleur à la carte a un sens pour ce point de données.

Comment les tableaux de bord Excel se mettent à jour automatiquement

Une fois que vous avez créé un tableau de bord, vous n’avez rien à faire pour mettre à jour les graphiques. Toutes les données de ces graphiques et widgets se mettent à jour comme suit :

• Les feuilles contenant des données importées sont actualisées à la date que vous avez fixée lors de la création de l’importation des données.
• Toute feuille supplémentaire que vous avez créée pour corriger ou reformater les données des feuilles importées sera mise à jour avec les nouvelles données de ces feuilles.
• Chaque widget de votre tableau de bord se met automatiquement à jour pour afficher les nouvelles données à l’intérieur de ces fiches mises à jour pour les plages que vous avez sélectionnées lors de la création de ces graphiques.

Comment utiliser les tableaux de bord Excel

La création de tableaux de bord dans Excel peut être utile pour de nombreuses raisons. Cependant, il est important de les créer en fonction d’un besoin spécifique, plutôt que d’essayer de construire un seul tableau de bord qui fait tout.

Par exemple, si vous êtes directeur commercial et que vous souhaitez suivre les performances de votre équipe de vente, le tableau de bord du directeur commercial doit se concentrer sur les indicateurs clés de performance (KPI) liés aux performances de vente.

Ce type de tableau de bord ne doit pas contenir d’informations qui ne sont pas liées aux performances de vente, sinon le tableau de bord pourrait être trop encombré. Un tableau de bord encombré rend plus difficile la visualisation des relations et des modèles de données qui comptent.

Autres considérations lors de la construction de tableaux de bord :

• Utilisez les bons graphiques pour obtenir les bonnes données.
• N’utilisez pas trop de couleurs dans le tableau de bord.
• Disposez le tableau de bord avec des données et des types de graphiques similaires dans des blocs communs.
• Veillez à ce que chaque graphique affiche des étiquettes simples et ne soit pas trop encombré.
• Organisez les widgets selon une hiérarchie d’importance, les informations les plus importantes se trouvant en haut à gauche du tableau de bord.
• Utilisez le formatage conditionnel pour vous assurer que lorsque les chiffres sont mauvais, ils sont rouges, et lorsqu’ils sont bons, ils sont verts.

Surtout, faites preuve de créativité pour concevoir des tableaux de bord informatifs et intéressants à utiliser.