Excel : Créer un tableau de bord dynamique

Tutoriel Excel : Créer un tableau de bord dynamique et animé pour les données politiques américaines

 

Qui n’aime pas un tableau de bord Excel dynamique et animé ?

En voici une sur laquelle j’ai travaillé récemment, une visualisation des données politiques historiques des États-Unis, montrant les tendances des partis, les populations des États et les présidents en exercice au fil du temps :

Tableau de bord dynamique Excel - Données politiques américaines

Dans l’article suivant, j’entre dans les détails de la façon dont j’ai créé ce tableau de bord. Il ne s’agit pas d’un compte rendu complet cellule par cellule de la façon dont je l’ai fait, car cela nécessiterait un article au moins deux fois plus long, mais plutôt d’un aperçu des différentes étapes et processus de réflexion en cours de route.

S’il semble un peu en lambeaux, c’est parce que c’est probablement le cas ! Probablement parce que j’écris ces yeux troubles à 1h du matin, entre les tétées et les changements de couches de mon fils de 6 semaines. 😉

 

Introduction

C’est un projet que j’avais en tête depuis longtemps.

Le timing et la motivation se sont finalement réunis lorsque j’ai récemment commencé à enseigner le cours d’analyse de données pour l’Assemblée générale , qui implique Excel et le tableau de bord, donc il s’est bien lié et a été l’occasion de revoir certaines facettes intéressantes d’Excel. J’ai donc réservé quelques jours pour exécuter cette idée, du concept à la publication de cet article de blog (cela a fini par prendre plus d’une semaine au total – le temps vient par petits blocs ces jours-ci avec un nouveau-né 🙂 ).

J’ai pensé que les données politiques historiques des États-Unis fourniraient un ensemble de données riche et intéressant à visualiser et à animer. Je suis donc parti à la recherche de données sur les votes, les données sur la population de l’État et les données présidentielles du siècle dernier.

Étape 1 : Concept initial et esquisse des idées

Ce projet a commencé avec un stylo et du papier, esquissant simplement quelques idées sur les informations et les graphiques que je voulais inclure. Je savais qu’il serait intéressant d’examiner un siècle de données sur les votes ainsi que des informations sur les présidents en poste pendant cette période. Par la suite, j’ai pensé qu’il serait également intéressant d’ajouter des estimations de la population des États américains.

Je suis venu avec les notes et croquis suivants comme prototype initial :

Croquis du tableau de bord Excel

Oui, c’est assez approximatif, mais cela m’a aidé à façonner mes idées initiales. Vous pouvez voir plusieurs éléments du croquis intégrés au tableau de bord final en haut de cette page, à savoir le tableau des États, les données des présidents et les deux graphiques en bas à droite (représentant le Sénat et la Chambre des représentants).

Ensuite, j’ai sorti le mug « I <3 Spreadsheets » ! Il est garanti de donner 10 % de plus à ses compétences en Excel lorsqu’il est rempli de thé anglais fort.Tasse à thé feuille de calcul

Étape 2 : Sourcer les données

D’où ai-je obtenu les données ?

Données du président

Wikipédia – liste des présidents américains

J’ai récupéré la liste des présidents américains sur Wikipédia et utilisé Google Sheets pour effectuer une recherche Web rapide des données à l’aide de la fonction suivante :

=importhtml("https://en.wikipedia.org/wiki/List_of_Presidents_of_the_United_States","table",1)

Données de vote de l’État

Wikipédia – Résultats des élections par État

De même, j’ai obtenu des données sur l’historique des enregistrements de vote des États américains sur Wikipedia et j’ai de nouveau utilisé la importhtml()formule pour extraire ces données dans une feuille Google dans un premier temps.

Composition du Congrès

InfoPlease – Composition du Congrès

Données démographiques de l’État

Portail de la population du site Web du recensement américain

En fouillant ici, j’ai pu obtenir des estimations de la population de l’État de 1900 à 2012, par exemple :

  - Lien dataset 1900 – Lien dataset   1980 – Lien
  dataset 2010 (format csv)

Enfin, j’ai remarqué que certains États, l’Alaska et Hawaï, manquaient d’estimations démographiques pour le début du siècle, ce qui semblait étrange au premier abord. Après quelques lectures supplémentaires , j’ai découvert que ces états ne se sont réellement formés que dans les années 50, d’où les données manquantes.

Mon classeur comporte donc quatre feuilles contenant des données brutes : données présidentielles, données sur le vote des États, données sur le Congrès et données sur la population de l’État.

Étape 3 : Mise en scène des données

Quand je dis « Staging the data », je veux dire préparer les données et les mettre en place dans des tableaux agrégés prêts à piloter le tableau de bord.

Le pilote global de tous les visuels du tableau de bord est l’ ANNÉE sélectionnée par l’utilisateur (ou automatiquement par l’animation). Étant donné que mes formules de recherche s’exécutent toutes sur cette année variable, les données renvoyées par les recherches changent lorsqu’une année différente est sélectionnée.

La cellule G5contient cette variable d’année et pilote ainsi le tableau de bord. Je l’utilise dans toutes les recherches dans la feuille de mise en scène et dans la feuille de tableau de bord pour obtenir les données nécessaires.

J’ai ensuite ajouté une nouvelle feuille appelée « Staging » et l’ai configurée comme suit :

Données de mise en scène du tableau de bord Excel

Il y a quatre tableaux ici :

Le tableau dynamique Population de l’état contient les données utilisées pour la colonne et le graphique de la population de l’état dans le tableau de bord. Ce tableau est configuré de manière à ce que l’année en haut de la colonne (1900 dans cette capture d’écran) soit liée à l’année sélectionnée dans le tableau de bord, de sorte qu’elle changera pour refléter le choix des utilisateurs (ou l’animation). Les données de population de cette colonne sont ensuite extraites de la feuille de données brutes de la population de l’État à l’aide d’une formule d’index/correspondance, en utilisant la variable de date et l’état comme valeurs de recherche (ceci est uniquement à titre d’illustration, en pratique, il est rempli avec une cellule spécifique et références gamme) :

=INDEX(state_population_data, MATCH(lookup_date,state_population_data,0), MATCH(lookup_state,state_population_data,0))

La dynamique du Sénat et la dynamique des représentants sont deux tables similaires, utilisant à nouveau exactement le même principe d’une valeur d’année variable liée au tableau de bord, puis une recherche dynamique des données brutes. Ces deux tableaux pilotent les deux graphiques du tableau de bord, montrant la composition du Sénat et de la Chambre des représentants.

La liste déroulante Liste de dates pour la validation des données est simplement une liste des années que je souhaite afficher dans le menu déroulant que l’utilisateur peut utiliser pour interagir avec le tableau de bord (voir la section VBA ci-dessous).

Étape 4 : Création des visualisations du tableau de bord

Carte quadrillée des registres de vote

La première partie du tableau de bord que j’ai implémenté était la carte quadrillée des états et des votes présidentiels, sur le côté gauche du tableau de bord. En haut de ce tableau, j’ai cinq en-têtes de colonne contenant des valeurs d’année, qui changent lorsque l’utilisateur active la section des commandes du tableau de bord.

Comme je l’ai mentionné, la cellule G5 affiche l’année sélectionnée par l’utilisateur (ou l’animation), et les quatre autres années sont simplement basées sur celle-ci, comme suit :

Variables années Excel

À ce stade, j’ai simplement tapé 1900 dans la cellule G5 – j’ai ajouté l’interactivité à l’étape suivante (voir la section VBA ci-dessous). Pour les quatre autres en-têtes de colonne, basés sur la cellule G5, les formules étaient les suivantes :

=G5 - 4
=F5 - 4
=E5 - 4
=D5 - 4

Ensuite, j’ai utilisé une formule d’index/match pour récupérer dynamiquement les données de vote pour chaque État, à partir de la feuille de données de vote de l’État, en fonction de ces valeurs d’année changeantes (formule à titre d’illustration uniquement, en pratique, j’ai ajouté les références de cellule et de plage spécifiques) :

=INDEX(state_voting_data, MATCH(state_lookup_value,state_voting_data,0), MATCH(date_lookup_value,state_voting_data,0))

J’ai utilisé la mise en forme conditionnelle pour colorer les cellules en bleu si elles valaient « D », en rouge si elles valaient « R », en gris pour les résultats « N/k » (certains états n’existaient pas au début de la période, par exemple Hawaï, voir section source de données ci-dessus) et or sinon (pour les occasions où un État a voté pour quelqu’un d’autre que démocrate ou républicain).

Enfin, j’ai appliqué une bordure blanche épaisse sur toute la grille pour donner une certaine séparation et obtenir un aspect plus propre.

Ajout des informations du président et de la photo de profil

L’ajout d’informations sur le président était relativement simple. J’ai utilisé une VLOOKUP()formule pour rechercher la valeur de l’année dans la table de données des présidents et renvoyer le nom, le parti et l’état pour cette année. J’ai dû définir le quatrième argument de VLOOKUP (appelé range_lookup) sur TRUE, car les années n’étaient pas des correspondances exactes.

Changer l’affichage de l’image de profil était un peu plus délicat. L’idée ici est de mettre à jour la photo de profil sur le tableau de bord pour afficher une photo du président en fonction du nom affiché.

Je suis redevable au gourou d’Excel Sumit Bansal pour son article informatif sur la façon de rechercher une image à l’aide de plages nommées et d’une image liée – une astuce vraiment cool que je n’avais jamais vue auparavant. Merci! Consultez certainement son article de blog pour une explication complète de la façon de faire ce peu de magie.

Ce qui suit est une brève description de la façon dont j’ai appliqué cette méthode à mon tableau de bord.

Dans une feuille séparée, j’ai mis en place un tableau avec une colonne pour tous les noms des présidents et dans la colonne adjacente, leur photo de profil. J’ai ensuite défini une plage nommée appelée presidents_profileset au lieu d’une plage statique, mettez cette formule dans la zone de saisie de la plage :

=INDEX(images!$B$4:$B$23,MATCH(dashboard!$T$7,images!$A$4:$A$23,0))

Ce que cela fait, c’est prendre le nom du président sur le tableau de bord (dans la cellule T7) et le trouver dans la colonne des noms du président dans mon tableau d’images, puis renvoyer l’image de profil correspondante et la charger dans la plage nommée presidents_profiles. Phew!

Ensuite, j’ajoute l’une des images de profil au tableau de bord et je lie cette image à la presidents_profilesplage nommée. Lorsque le nom du président sur le tableau de bord change, l’image liée et la formule de plage nommée opèrent leur magie et hop ! la photo de profil du nouveau président apparaît.

Ajout du diagramme à barres de la population de l’État

Diagramme à barres de la population des États américains

Il s’agissait d’un graphique à barres Excel simple exécuté à partir de la population de l’État – tableau dynamique dans la feuille de calcul de mise en scène.

J’ai formaté l’axe des abscisses pour afficher la population en millions, avec seulement les principaux quadrillages montrant chaque incrément de 10 millions. J’ai mis les barres au noir pour rester non partisan.

Ajout des histogrammes du Sénat et de la Chambre des représentants

Graphique à colonnes du Congrès américain

Il s’agissait de deux graphiques à colonnes assez simples, basés sur les tableaux Dynamique du Sénat et Dynamique des représentants dans la feuille de calcul de mise en scène.

J’ai défini les axes x et y pour qu’aucune ligne ne s’affiche, j’ai supprimé le quadrillage, ajouté un jeu de couleurs rouge/bleu/or et ajouté des étiquettes de données, pour obtenir l’aspect propre et minimal que je recherchais.

Étape 5 : Ajout de l’interactivité avec VBA

Maintenant que tous mes visuels étaient configurés dans le tableau de bord, il était temps d’ajouter un peu d’interactivité. C’était la dernière pièce majeure du puzzle et j’ai utilisé un peu de VBA de base pour implémenter une option d’animation et une sélection manuelle de l’année.

Animation

Le gourou d’Excel Chandoo a publié l’année dernière un article sur la façon de créer une minuterie de base à l’aide de VBA , ce qui m’a été utile lors de la création de mon propre code. Je vous recommande de vérifier cet exemple comme introduction si vous débutez avec VBA.

J’ai une cellule sur le côté du tableau de bord (AB4) que l’utilisateur ne peut pas voir et qui contient le mot START. Je l’étiquette ensuite comme start.button.labeldans la zone de nom (à gauche de la barre de la zone de formule dans le ruban):

Étiquette du bouton de démarrage VBA

Sur le tableau de bord maintenant, près du centre de la section d’en-tête, j’ai ajouté une forme de rectangle arrondi ( Insertion > Formes > Rectangle arrondi ) et dans la barre de formule, je tape =start.button.label. Cela lie la forme à la cellule contenant le mot START, de sorte que START s’affiche sur la forme rectangulaire.

J’ai formaté ce bouton pour que le texte soit centralisé et d’une taille appropriée et ai changé la couleur en blanc, donc il ressemble à l’exemple suivant :

Boutons Excel VBA

Dans mon éditeur VBA, j’ai configuré une sous-procédure pour gérer l’animation, en commençant par le code suivant :

1
2
3
4
5
Dim Stopped As Boolean
Sub startStopTimer()
End Sub

Ici, j’ai déclaré la variable Stoppedet lui ai donné un type de données booléen, ce qui signifie qu’elle ne peut avoir que la valeur VRAI ou FAUX. Ensuite, j’ai déclaré une sous-procédure appelée startStopTimer().

J’ai ensuite lié le bouton rectangulaire à ma startStopTimer()sous-procédure (clic droit sur le rectangle, puis Assign Macro… > startStopTimer() > OK ).

Désormais, lorsqu’un utilisateur clique sur le bouton vert START, il déclenche la startStopTimer()sous-procédure.

J’ai ajouté le code suivant :

01
02
03
04
05
06
07
08
09
dix
11
12
13
14
15
16
17
18
Dim Stopped As Boolean
Sub startStopTimer()
    If Range("start.button.label") = "START" Then
        Range("start.button.label") = "STOP"
        CurrentYear = 1900
        Stopped = False
        Do Until Stopped Or CurrentYear = 2012
            Application.Wait (Now + TimeValue("0:00:01"))
            CurrentYear = CurrentYear + 4
            Worksheets("dashboard").Range("G5").Value = CurrentYear
            DoEvents
        Loop
    Else
        Stopped = True
        Range("start.button.label") = "START"
    End If
End Sub

Qu’est-ce qui se passe ici alors?

Le code VBA vérifie d’abord la start.button.labelcellule avec l’instruction Range("start.button.label")pour voir si elle est égale à la chaîne « START » ou non.

Lorsque cela est vrai, nous entrons dans l’instruction conditionnelle IF et exécutons ce qui suit :

  1. Changez le start.button.labelen STOP
  2. Définissez la variable CurrentYearsur 1900
  3. Définissez la variable booléenne Stoppedsur FALSE
  4. Entrez dans a Do Until Loop, qui continue de boucler jusqu’à ce que la variable Stopped soit VRAI (parce que le bouton STOP a été enfoncé) ou que la CurrentYearvariable soit égale à 2012. Sinon, la boucle continue de boucler, en exécutant à chaque fois ce qui suit :
    • Faire attendre l’application 1 seconde pour que l’animation n’arrive pas trop vite (ligne 10)
    • Ajouter 4 ans à la CurrentYearvariable (ligne 11)
    • Mettre à jour la valeur YEAR dans la cellule G5 de ma feuille de tableau de bord (ligne 12)
    • DoEvents(ligne 13) est une commande pour passer le contrôle de l’application au système d’exploitation, afin que l’expérience du tableau de bord ne soit pas gelée et que l’utilisateur puisse faire des choses comme appuyer sur le bouton STOP

Si la start.button.labelcellule n’est pas égale à START (parce qu’elle est égale à STOP), alors nous entrons dans la partie ELSE de la sous-procédure. Ici, nous définissons la Stoppedvariable sur True et changeons le start.button.labelen START, prêt pour que l’animation recommence.

J’ai ajouté un bouton RESET de la même manière, pour gérer la réinitialisation du tableau de bord à 1900 avec le bouton START à nouveau affiché. Le code pour y parvenir était relativement simple :

1
2
3
4
Sub ResetYear()
    Worksheets("dashboard").Range("G5").Value = 1900
    Range("start.button.label") = "START"
End Sub

Comme pour le bouton START/STOP, j’ai dû lier le bouton RESET au code (clic droit sur le rectangle du bouton Reset, puis Assign Macro… > startStopTimer() > OK ), pour que le code s’exécute lorsque le bouton était pressé.

Menu déroulant manuel

J’ai également ajouté un menu déroulant manuel (Développeur > Insérer > Combo Box), permettant à un utilisateur de choisir manuellement une année spécifique.

Le code suivant gère ensuite la logique en définissant simplement la variable year sur ce qui est choisi dans le menu déroulant.

1
2
3
4
Sub ChooseYear()
    ChosenYear = Range("manual.year.choice")
    Worksheets("dashboard").Range("G5").Value = ChosenYear
End Sub

L’étape suivante consiste à affecter cette ChooseYear()macro à la zone de liste déroulante, afin qu’elle se déclenche lorsque la zone de liste déroulante est utilisée.

Le dernier problème à résoudre concerne la zone de liste déroulante renvoyant une valeur représentant l’indice de l’élément sélectionné, plutôt que l’année. Par exemple, ma liste contenait les années suivantes [1900, 1904, 1908,…, 2008, 2012] donc si un utilisateur sélectionnait 1904, la sortie était 2, s’il sélectionnait 2008, la sortie était 28, et pour 2012 la sortie était 29. J’avais donc besoin de reconvertir ce numéro d’index en une valeur d’année pour les recherches.

J’ai fait cela en ajoutant la index()formule suivante à une cellule sur le côté droit du tableau de bord (AB8):

=INDEX(years_data_array,index_number_from_combo_box,0)

puis en nommant cette cellule manual.year.choicepour que le code VBA puisse la trouver.

Le numéro d’index et manual.year.choicela cellule sont désactivés sur le côté droit du tableau de bord :

Variables d'année VBA

Le résultat final de ce code manuel est le suivant :

Tableau de bord dynamique Excel - Données politiques américaines - Sélecteur de date

Le code VBA final se trouve sur mon compte GitHub dans ce dépôt VBA .

Étape 6 : Touches finales

J’ai formaté la ligne d’en-tête avec un remplissage gris clair et une bordure inférieure gris foncé. J’ai gelé les trois premières lignes pour verrouiller le titre et les commandes du tableau de bord en haut de l’écran.

J’ai évidemment utilisé le bleu et le rouge pour représenter les deux principaux partis politiques. Cependant, plutôt que d’utiliser du bleu pur et du rouge pur (très intenses et peu agréables à l’œil), j’ai utilisé des variantes plus douces, plus douces et plus faciles à regarder, sans rien perdre du sens. Si j’étais un vrai pointilleux sur les détails et que j’avais le temps, je verrais si les parties ont des directives officielles sur les couleurs et fonderais mon jeu de couleurs sur cela.

J’ai supprimé le quadrillage du tableau de bord, une fois que j’ai terminé la mise en page de toutes les différentes pièces. Les quadrillages n’ajoutent rien au produit final, alors débarrassez-vous ! (Trouvez cette option dans le menu Affichage.)

J’ai caché la colonne de variables qui se cache sur le côté du tableau de bord, pour m’assurer que les utilisateurs ne les verraient pas.

J’ai également caché toutes les autres feuilles contenant les données et les données de mise en scène. Il n’est pas nécessaire que ceux-ci soient montrés à l’utilisateur final, et en fait, les laissant montrer aux utilisateurs les risques de modifier vos données.

Tous ces changements étaient relativement mineurs et n’ont pas pris longtemps à mettre en œuvre, mais ils ont un impact disproportionné sur la qualité esthétique et, en fin de compte, sur l’impact de votre tableau de bord, il vaut donc vraiment la peine de franchir cette dernière étape.

Et c’est tout!

Capture d'écran du tableau de bord dynamique Excel

Bien sûr, notre travail n’est jamais terminé et pas un instant ne s’est écoulé après que l’encre métaphorique ait séché, avant que je commence à réfléchir aux améliorations et aux prochaines étapes.

Mais avant cela, permettez-moi de partager quelques idées que j’ai essayées et qui n’ont pas été intégrées au tableau de bord final…

Faux départ

Au départ, j’ai utilisé un graphique en radar pour montrer la croissance de la population au fil du temps. J’étais assez excité et satisfait du visuel car il ajoutait quelque chose de différent au tableau de bord :

Graphique en radar Excel

J’ai dû supprimer de nombreuses étiquettes d’État car elles étaient si encombrées, ne laissant que les noms d’État les plus importants.

Quand je l’ai montré à ma femme, ce tableau n’avait aucun sens sans que j’aie à l’expliquer (et elle est un cookie intelligent ). Les cartes radar sont difficiles à comprendre et encore plus difficiles à lire des informations réelles, d’autant plus que j’ai supprimé de nombreuses étiquettes d’État.

Donc, cela a échoué en tant que visualisation et a donc dû disparaître.

Le remplacer par un simple graphique à barres a été une énorme amélioration. Morale de l’histoire – méfiez-vous de tous les tableaux fantaisistes, car ils sont rarement une amélioration par rapport à leurs frères plus simples et plus humbles.

J’avais également espéré inclure une carte choroplèthe des États montrant les registres des votes, mais c’est un projet complexe et stimulant, et un pour un autre jour.

Donc, à la place, j’ai pensé à ajouter une carte d’état pour l’état d’origine du président, qui se mettrait à jour dynamiquement à côté de la photo de profil, mais j’ai décidé que ce n’était pas un bon retour sur investissement pour le temps que cela aurait pris. J’ai également pensé que c’était un peu fantaisiste et que cela n’ajouterait rien de substantiel au tableau de bord (qui est le critère d’inclusion numéro 1).

Pour mémoire, je prévoyais d’ajouter de petites cartes d’état comme celle-ci, avec l’état pertinent en surbrillance (par exemple Texas pour GW Bush) :

Carte du Texas pour tableau de bord dynamique Excel

Zones d’amélioration

Le graphique de la population montre les États, mais ils ne correspondent pas aux données d’enregistrement des votes pour chaque État. De plus, avoir la colonne de la population ici est peut-être redondant, compte tenu des informations contenues dans le graphique.

Si j’avais plus de temps et que cela était construit pour un client plutôt que pour le plaisir, alors les variables sur le côté du tableau de bord seraient rangées et placées quelque part sur la feuille de mise en scène, donc il n’y a rien qui se cache sur la feuille de tableau de bord qui ne soit pas t dédié à l’affichage d’informations. Dans la même veine de rangement, j’aurais utilisé plus de plages nommées pour rendre mes formules plus nettes et moins sujettes aux erreurs si je le faisais à nouveau.

Avoir des données sous le pli (donc elles ne sont pas visibles sur un seul écran) tombe dans l’un des 13 pièges courants de la conception de tableaux de bord, adopté par Stephen Few dans ce livre blanc digne de ce nom , c’est donc quelque chose qu’un puriste voudrait résoudre…

Les graphiques peuvent être condensés et des données ou informations supplémentaires peuvent être affichées. Oui, c’est tout à fait possible, mais comme il s’agissait d’un projet très visuel, je pense que l’impact visuel serait perdu par dilution en affichant encore plus d’informations.

Les photos de profil des présidents américains étaient un mélange d’images carrées et circulaires, qui auraient pu être améliorées. Cependant, avec un bébé de 6 semaines dont il fallait s’occuper à la maison, cela ne semblait pas être la meilleure utilisation de mon temps ! 🙂

Également sur les images de profil, elles ne se mettent pas à jour assez rapidement pour suivre le rythme de l’évolution des données lors de l’utilisation de la fonction Auto « START/STOP ». Comme vous pouvez le voir dans le GIF supérieur, il y a un léger décalage dans la mise à jour de l’image, de sorte que le mauvais visage apparaît parfois contre le nom du président.

Retour en haut