Dans cet article, je vais vous montrer comment créer un graphique en relief dynamique comme celui ci-dessus, en utilisant des données historiques pour la Premier League anglaise remontant à 1992-93 lors de sa création.
Le graphique vous montre le succès de Manchester United au cours des années de Premier League :
En parcourant toutes les différentes équipes, vous ressentez un sentiment de nostalgie lorsque vous vous souvenez de certaines des équipes qui ont quitté la Premier League il y a longtemps (je m’en souviens depuis mon enfance):
On se croirait il y a très longtemps, une époque révolue, quand Nottingham Forest traînait avec les grands clubs près du sommet de la Premier League.
Et voici ma propre équipe, Liverpool, l’un des clubs d’élite du monde qui lutte pour retrouver le succès de ses jours de gloire, mais qui n’a pas encore remporté de titre de Premier League. Nous sommes passés si près en 2013-14 , mais pour une malheureuse erreur à un moment crucial, lorsque nous avons perdu 0-2 contre Chelsea à domicile.
J’ai empilé toutes les tables pour chaque saison dans une table géante de 486 lignes, qui contient les données des 24 saisons. J’ai ajouté une colonne pour identifier chaque saison, exprimée sous forme de valeur textuelle, par exemple « 1992-93 ».
Ensuite, j’ai dû faire un petit nettoyage des données pour uniformiser tous les noms, par exemple en changeant Arsenal !Arsenal *(C)* en Arsenal .
J’ai d’abord fait une copie de la colonne du nom de l’équipe (que j’ai colorée pour la distinguer), afin de pouvoir conserver un enregistrement des données d’origine pour vérification.
Ensuite, j’ai sélectionné ma nouvelle colonne de noms et ouvert Rechercher et remplacer ( Ctrl + F). Chaque équipe a été corrigée facilement en utilisant Replace Alldes caractères génériques, comme le montre cette image :
Trouvez *Arsenal* et remplacez par Arsenal .
En ajoutant le * devant et après la chaîne « Arsenal », j’ai pu corriger toutes les variantes en une seule recherche.
Création du graphique
1. Sélectionnez l’ensemble du tableau des données Premier League ( Ctrl + A) et Insert > PivotTable. Ajoutez Cleaned Teamà Rows, YEARà Columnset Positionà Values, comme indiqué dans le schéma suivant.
2. Faites une copie des données du tableau croisé dynamique dans un nouvel onglet. Sélectionnez l’ensemble du tableau ( Ctrl + A) puis remplissez toutes les cellules vides avec la valeur 23, c’est-à-dire pour toutes les équipes qui ne sont pas en Premier League cette saison, classez-les en dehors du top 22 (le nombre maximum d’équipes de Premier League au cours de la premières saisons). Utilisez Rechercher et remplacer à nouveau, cette fois laissez la case de recherche vide (vide) et mettez 23 dans la case de remplacement. Appuyez ensuite sur Replace All.
3. Sous ce tableau, nous allons ajouter une ligne pour les valeurs dynamiques qui changent lorsqu’un utilisateur choisit une équipe différente.
Pour l’instant, répétez le nom de la dernière équipe (Wolverhampton Wanderers) dans la première cellule vide sous les noms des équipes (voir l’image ci-dessous). Ajoutez ensuite la formule suivante dans la cellule adjacente :
=VLOOKUP($AB51,$AB$4:$AZ$50,2,FALSE)
Vous pouvez utiliser un raccourci pour économiser la saisie du numéro de référence de la colonne dans vos formules VLOOKUP. Si vous êtes dans une colonne différente de AC, ajustez le nombre 27 dans la formule pour tenir compte du nombre de colonnes avant votre table de données actuelle (c’est-à-dire que A à AA est de 27 colonnes avant que ma table de données de graphique ne commence dans la colonne AB).
=VLOOKUP($AB51,$AB$4:$AZ$50,COLUMN(AC1)-27,FALSE)
Mettez en surbrillance cette dernière ligne pour la distinguer en tant que ligne dynamique. Le jeu de données est maintenant le suivant :
(Ne vous inquiétez pas, nous reviendrons pour changer le nom de l’équipe pour qu’il soit dynamique, donc les données changent avec ces formules.)
Enfin, dessinons le graphique.
4. Mettez en surbrillance l’ensemble du tableau de données ( Ctrl + A) et insérez un graphique linéaire avec des marqueurs . Cela vous donnera ce petit tableau indéchiffrable :
5. Excel n’interprète pas les données comme nous le souhaitons, alors échangez les lignes et les colonnes à l’aide Chart Design > Switch Rows/Columnsde , pour obtenir un autre graphique indéchiffrable :
6. Développez le graphique et supprimez la légende (cliquez dessus pour le sélectionner, puis appuyez sur supprimer) et vous pouvez commencer à voir le graphique se rassembler :
7. Maintenant, nous devons changer la couleur de toutes ces lignes et marqueurs en gris et ajouter des étiquettes de données pour les noms d’équipe. Vous pouvez le faire manuellement en cliquant tour à tour sur chaque ligne et :
– changez la couleur – changez la bordure et la couleur de remplissage des marqueurs – ajoutez des étiquettes de données et changez pour « Nom de la série » uniquement – cliquez sur les étiquettes de données une par une et supprimez toutes sauf la dernière.
C’est super fastidieux avec toutes ces lignes croyez-moi. Utilisons donc un peu de VBA-fu pour le faire pour nous en moins d’une seconde (pour une introduction à VBA, lisez ceci).
Accédez à Developer menu tab > Visual Basic menu option > Modules > Module 1, comme indiqué dans cette image :
Dans la fenêtre de code, copiez et collez le code VBA suivant :
01
02
03
04
05
06
07
08
09
dix
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
OptionExplicit
SubformatBumpChart()
DimmySrs AsSeries
DimnPts AsLong
ForEachmySrs InActiveChart.SeriesCollection
WithmySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Name
.MarkerStyle = xlMarkerStyleSquare
.MarkerSize = 7
.MarkerForegroundColorIndex = 15
.MarkerBackgroundColorIndex = 15
.DataLabels.Font.Size = 14
EndWith
WithmySrs.Border
.ColorIndex = 15 ' grey lines
.Weight = xlThin
.LineStyle = xlContinuous
EndWith
Next
EndSub
Celui-ci contient une boucle qui s’exécute sur toutes les lignes (séries) du graphique actif et les formate en fines lignes grises avec les noms d’équipe ajoutés en tant qu’étiquette de données :
Maintenant que votre fichier Excel contient VBA, vous devez Save Assélectionner le fichier prenant en charge les macros .xlsm.
Cliquez sur la zone du graphique et déplacez-la pour donner plus de place aux étiquettes de données.
8. Avez-vous remarqué quelque chose de bizarre à propos de cet axe des ordonnées ? Ouais, c’est à l’envers pour nos besoins, montrant les meilleures positions de la ligue, 1er, 2e, 3e, etc., en bas de l’axe et les positions les plus basses de la ligue en haut de l’accident. Retournons-le.
Cliquez sur l’axe des y, cliquez sur Ctrl + 1 pour afficher les options de format et choisissez :
pour fixer l’axe, de sorte que le numéro 1 soit en haut.
Définissez les limites sur l’axe des ordonnées entre 0 et 22 inclus et les unités majeures sur 1,0 dans ce même panneau de format.
Enfin, dans ce panneau, changez le nombre en un format personnalisé : #,##0;;pour que le 0 ne s’affiche pas (chapeau à Chandoo pour cet indice ).
9. Il est également temps de ranger rapidement l’axe des x. Sélectionnez-le, puis dans le volet de format cochez la Text Axiscase » » et la Between tick marksposition de l’axe » « .
10. Ajoutez un nouvel onglet vide à votre classeur. Coupez et collez le graphique dans ce nouvel onglet.
11. J’ai changé le format de la meilleure équipe actuelle, Leicester City, pour être un bleu clair similaire à la couleur du club. Pour ce faire, cliquez sur la ligne de série de Leicester (le point de données en haut à droite du graphique) et mettez-la en forme de manière à ce qu’elle ressorte. J’ai utilisé:
– couleur de trait bleu clair – épaisseur de trait 2 pt – marqueur de cercle avec taille 20 – couleur de marqueur de cercle bleu clair
J’ai également formaté l’étiquette de données du nom de l’équipe pour qu’elle soit en gras et en bleu clair :
Pendant que vous y êtes, sélectionnez les quadrillages en cliquant sur l’un d’eux et en les supprimant car ils ne font qu’encombrer et confondre dans ce graphique.
12. Ajout de l’élément de ligne dynamique.
Au-dessus de votre graphique (ou à proximité), créez un menu déroulant qui permet à l’utilisateur de sélectionner une équipe, comme suit :
Données > Validation des données
Paramètres > Critères de validation > Autoriser : Liste
Cliquez sur la zone de saisie Source (n° 5 dans l’image ci-dessous).
Mettez ensuite en surbrillance les noms d’équipe dans votre tableau de données de graphique, à l’exclusion de celui dynamique en bas de la liste , pour sélectionner tous les noms d’équipe :
Revenez à la cellule où vous avez mis la validation des données et vous devriez avoir un menu déroulant (un triangle apparaîtra lorsque vous survolerez la cellule) :
13. Revenez au tableau de données du graphique, à la cellule en bas où nous avons saisi toutes ces formules VLOOKUP à l’étape 3.
Cliquez sur la dernière cellule de nom d’équipe (la toute dernière cellule de la colonne des noms d’équipe, celle que nous avons tapée à côté des VLOOKUP) et tapez « = », puis naviguez et cliquez sur la cellule de validation des données, de sorte que votre formule est quelque chose comme:
='EPL Chart'!J2
où « EPL Chart » est le nom de ma feuille de graphique avec la validation des données. Maintenant, cette cellule est liée au menu déroulant, donc quand une équipe différente est sélectionnée, cette cellule changera et donc les données dans les cellules VLOOKUP changeront puisqu’elles utilisent une valeur de recherche différente. Testez-le en choisissant une équipe différente dans le menu déroulant.
14. Formatez la ligne dynamique représentant la sélection de l’utilisateur.
Cette ligne est déjà dans le graphique en double de l’équipe que vous avez sélectionnée. Il suffit donc de le sélectionner et de le formater pour se démarquer. Sélectionnez la ligne dans le menu du graphique : Format > puis le menu déroulant des éléments du graphique > sélectionnez la toute dernière série de noms d’équipe (pas les étiquettes de données).
Appuyez sur Ctrl + 1 pour afficher le menu de formatage et modifier le format de ligne (rouge foncé + 2pt + grands marqueurs).
15. Formatez les étiquettes de données de ligne dynamiques.
C’est un peu délicat. Suivez l’étape 13, sélectionnez les toutes dernières étiquettes de données d’équipe dans le menu déroulant Format du graphique. Appuyez sur supprimer pour supprimer, car il ne s’agit que de la dernière étiquette de données indiquant le nom.
À la place, nous rajouterons les étiquettes de données en tant que valeurs :
– assurez-vous que la ligne dynamique à l’étape 13 est sélectionnée (suivez ces étapes pour sélectionner à nouveau si nécessaire) – Conception de graphique > Ajouter un élément de graphique > Étiquettes de données > Centre – cela ajoutera des étiquettes de données de valeur – Ctrl + 1 pour afficher le menu de format puis assurez-vous que la valeur est la seule case cochée – dans le menu d’accueil, modifiez la taille de la police en 14 pt et en gras, afin que ces valeurs se démarquent.
Les valeurs représentent la position que l’équipe a terminée au cours de cette saison.
Maintenant, votre graphique devrait ressembler à ceci :
16. Pour être vraiment chic, vous pouvez également ajouter une ombre très subtile à la ligne dynamique. Je laisse ça pour les devoirs 😉
À partir de là, vous pouvez ajouter un titre au-dessus du graphique et déplacer le menu déroulant dans la cellule adjacente au titre si vous le souhaitez.
Le graphique final devrait alors s’afficher et fonctionner comme ceci :
