La création d'un tableau de bord Microsoft Excel implique d'extraire des informations de plusieurs sources afin que vous puissiez afficher ces données au même endroit. Excel offre une variété de façons de le faire, ce qui en fait l'un des outils de création de rapports de tableau de bord les plus puissants que vous puissiez utiliser.
Les informations contenues dans ce didacticiel s'appliquent à Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 et Excel pour Mac.
Qu'est-ce qu'un tableau de bord Excel ?
Un tableau de bord de données est un outil qui vous aide à surveiller et à analyser visuellement les métriques les plus importantes pour vous. Vous pouvez utiliser un tableau de bord pour surveiller 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 extraient 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 en un seul endroit, où vous pouvez tout revoir en un coup d'œil.
Les éléments typiques d'un tableau de bord de données dans Excel incluent:
- Graphiques
- Graphiques
- Jauges
- Cartes
Vous pouvez créer deux types de tableaux de bord. Pour les rapports de tableau de bord, vous pouvez créer un tableau de bord statique à partir des données d'autres feuilles que vous pouvez envoyer à quelqu'un dans un rapport Word ou PowerPoint. Un tableau de bord dynamique est une personne que les gens peuvent voir dans Excel, et il se met à jour chaque fois que les données des autres feuilles sont mises à jour.
Introduire 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.
Les sources potentielles pour importer des données dans Excel incluent:
- Autres fichiers de classeur Excel
- Fichiers texte, CSV, XML ou JSON
- Base de données SQL
- Microsoft Access
- Explorateur de données Azure
- Facebook et autres pages Web
- Toute autre base de données prenant en charge ODBC ou OLEDB
- Sources Web (tout site Web contenant des tableaux de données)
Avec autant de sources de données potentielles, la possibilité d'importer des données dans Excel pour créer des tableaux de bord utiles est illimitée.
Pour importer une source de données:
-
Ouvrir une feuille de calcul Excel vide. Sélectionnez le menu Data et dans le menu déroulant Get Data, sélectionnez le type de données souhaité, puis choisissez la source de données.
Image -
Parcourez le fichier ou une autre source de données que vous souhaitez importer et sélectionnez-le. Sélectionnez Importer.
Image -
Selon le type de source de données que vous choisissez, vous verrez différentes boîtes de dialogue pour transformer les données au format de feuille de calcul Excel.
Image -
La feuille se remplira avec toutes les données du fichier externe ou de la base de données.
Image -
Pour actualiser les données afin qu'elles téléchargent régulièrement toutes les modifications apportées à la source de données externe, sélectionnez l'icône Actualiser à droite de l' Requêtes & Connexions volet.
Image -
Sélectionnez les trois points à côté du lien EDIT en bas de la fenêtre d'actualisation, puis sélectionnez Properties.
Image -
Configurez les données à actualiser à partir de la source de données à intervalles réguliers en définissant Actualiser toutes les xx minutes à l'intervalle auquel vous souhaitez mettre à jour les données.
L'actualisation des données à partir de sources externes est utile, mais elle peut consommer du temps CPU si vous rendez le taux d'actualisation trop fréquent. Choisissez un taux de rafraîchissement qui maintient les données mises à jour à peu près aussi souvent qu'elles sont modifiées à la source, mais pas si fréquemment que vous ne copiez que les mêmes données à chaque fois.
Image - Répétez le processus ci-dessus dans de nouvelles feuilles de calcul 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 calcul, placez-la comme première feuille de calcul dans le classeur et renommez-la Dashboard.
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 sont actualisées 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 les données météorologiques de sites Web sur Internet.
Parfois, lorsque vous importez des données à partir de sources externes, vous ne pouvez pas représenter graphiquement les données importées. La solution consiste à créer une nouvelle feuille de calcul et dans chaque cellule, tapez =convert(et sélectionnez les données de la feuille de calcul importée. Pour les paramètres d'unité, sélectionnez simplement les mêmes paramètres pour avant Remplissez toute la feuille avec la même fonction afin que toutes les données soient copiées sur la nouvelle feuille et converties en nombres que vous pourrez utiliser dans les différents graphiques que vous créerez pour votre tableau de bord.
-
Créez un Bar Chart pour afficher un seul point de données. 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 élevé. Tout d'abord, sélectionnez le menu Insert, puis sélectionnez le graphique à barres 2D Clustered Column.
Image -
Dans le menu Chart Design, dans le groupe Data, choisissez Select Data.
Image -
Dans la fenêtre Select Data Source qui apparaît, cliquez sur le champ Chart data range, puis sélectionnez la cellule dans les données feuille de calcul que vous souhaitez afficher avec ce graphique à barres.
Image -
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 comprises entre 0 et 100 %. Déplacez ensuite le graphique vers la zone du tiret où vous souhaitez l'afficher.
Image -
Répétez les mêmes étapes ci-dessus pour créer des graphiques à barres pour tous les autres points de données que vous souhaitez représenter. Faites en sorte que la plage d'axes soit le minimum et le maximum pour ces mesures. Par exemple, une bonne plage de pression barométrique serait de 28 à 32.
Choisir la bonne plage de données est important car si vous n'utilisez que la valeur par défaut, l'échelle peut être trop grande pour les données, laissant des graphiques à barres principalement vides. Au lieu de cela, gardez les extrémités minimale et maximale de l'échelle de l'axe légèrement inférieures et supérieures aux valeurs extrêmes possibles de vos données.
Image -
Créez un Line Chart 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 les derniers jours de données que vous pouvez importer à partir de la table du site Web météo. Tout d'abord, sélectionnez le menu Insertion, sélectionnez le tableau 2D Area.
Image -
Dans le menu Chart Design, dans le groupe Data, sélectionnez Select Data.
Image -
Dans la fenêtre Select Data Source qui apparaît, cliquez sur le champ Chart data range, puis sélectionnez les cellules dans les données feuille de calcul que vous souhaitez afficher avec ce graphique linéaire.
Image -
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'ils sont placés dans un tableau de bord. Vous pouvez modifier l'emplacement ainsi que la taille et la forme du widget graphique. Utilisez cette flexibilité pour concevoir des tableaux de bord organisés qui fournissent autant d'informations à l'utilisateur dans le plus petit espace.
Image -
Créez un Textbox pour afficher les données de chaîne des feuilles que vous avez importées. Par exemple, pour voir les mises à jour des avertissements météorologiques sur votre tableau de bord, liez le contenu de la zone de texte à une cellule de la feuille de données importée. Pour ce faire, sélectionnez le menu Insert, sélectionnez Text, puis sélectionnez le Textbox
Image -
Placez le curseur de la souris dans le champ de formule, tapez =puis sélectionnez la cellule dans la table de données importée qui contient les données de chaîne que vous souhaitez afficher.
Image -
Sélectionnez la zone de texte et utilisez la fenêtre Format Shape à droite pour formater la zone d'affichage du texte dans votre tableau de bord.
Image -
Vous pouvez également comparer deux points de données dans vos feuilles de données importées à l'aide de graphiques circulaires Par exemple, vous pouvez afficher l'humidité relative sous la forme d'un graphique circulaire. Tout d'abord, sélectionnez les données que vous souhaitez afficher, puis dans le menu Insert, sélectionnez le graphique 2D Pie.
Les graphiques à secteurs comparent deux valeurs ou plus. 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. Cela se traduira par un graphique à secteurs 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.
Image - En ajoutant divers 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.
Ajouter un attrait visuel et un contexte avec la couleur
Une autre façon d'ajouter de la clarté à votre tableau de bord consiste à donner à vos graphiques à barres un remplissage dégradé qui représente une couleur d'avertissement comme le rouge pour les régions des données qui peuvent ne pas être bonnes.
Par exemple, si vous souhaitez montrer qu'une humidité relative supérieure à 75 % est inconfortable, vous pouvez modifier le remplissage en dégradé du graphique à barres unique en conséquence. Voici comment procéder.
-
Cliquez avec le bouton droit de la souris sur la bordure extérieure d'un graphique à barres et sélectionnez Format de la zone de graphique.
Image -
Sélectionnez l'icône Fill dans le volet Format Chart Area et changez la sélection en Gradient fill.
Image -
Sélectionnez chaque icône de niveau le long de la ligne de remplissage en dégradé et modifiez la couleur et l'obscurité en fonction de la "bonne" ou de la "mauvaise" qualité de ce niveau. Dans cet exemple, une humidité relative élevée passe au rouge foncé.
Image - Répétez ce processus pour chaque graphique où l'ajout d'un contexte de couleur au graphique a du 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 sont mises à jour comme suit:
- Les feuilles avec les données importées sont actualisées à la date que vous avez définie lors de la première création de l'importation de données.
- Toutes les feuilles supplémentaires que vous avez créées pour corriger ou reformater les données des feuilles importées seront mises à 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 dans ces feuilles mises à jour pour les plages que vous avez sélectionnées lors de la création de ces graphiques.
Ces mises à jour se produisent automatiquement tant qu'Excel est ouvert.
Comment utiliser les tableaux de bord Excel
La création de tableaux de bord dans Excel peut être utile pour de nombreuses raisons différentes. Cependant, il est important de les créer en fonction d'un besoin spécifique, plutôt que d'essayer de créer un tableau de bord qui fait tout.
Par exemple, si vous êtes un directeur des ventes et que vous souhaitez surveiller les performances de votre équipe de vente, le tableau de bord du directeur des ventes doit se concentrer sur les indicateurs de performance clés (KPI) liés aux performances des ventes.
Ce type de tableau de bord ne doit pas inclure d'informations qui ne sont pas liées aux performances des ventes, sinon le tableau de bord pourrait devenir trop encombré. Un tableau de bord encombré rend plus difficile la visualisation des relations et des modèles de données importants.
Autres considérations lors de la création de tableaux de bord:
- Utilisez les bons graphiques pour les bonnes données.
- N'utilisez pas trop de couleurs dans le tableau de bord.
- Organisez le tableau de bord avec des types de données et de graphiques similaires dans des blocs communs.
- Assurez-vous que chaque graphique affiche des étiquettes simples et qu'il ne soit pas trop encombré.
- Organisez les widgets par ordre d'importance, avec les informations les plus importantes en haut à gauche du tableau de bord.
- Utilisez la mise en forme conditionnelle pour vous assurer que lorsque les nombres sont mauvais, ils sont rouges, et lorsqu'ils sont bons, ils sont verts.
Plus important encore, faites preuve de créativité pour concevoir des tableaux de bord informatifs et intéressants à utiliser.
