Adapter la vue du graphique au clic dans une colonne

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Adapter la vue graphique au clic de la souris

Dans la formation précédente, nous avons apporté la solution pour adapter la représentation graphique des données au choix de l'un des mois par le biais d'une liste déroulante. Nous souhaitons pousser ce principe pour offrir encore plus d'ergonomie.



Dans l'exemple illustré par la capture, au clic de l'utilisateur dans l'une des colonnes du tableau, le graphique s'ajuste automatiquement pour générer la représentation des ventes pour le mois de cette rangée.

Source et présentation
Pour aboutir cette solution, nous suggérons de réceptionner les travaux précédents.

• Télécharger le classeur vue-graphique-au-clic.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Ignorer le message d'erreur s'il survient,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

C'est la neutralisation de sécurité qui génère cette alerte du fait de l'emploi d'une plage variable utilisée comme source de données du graphique. Le tableau des ventes est donc placé sur la gauche de la feuille. Sa représentation graphique dynamique est placée sur sa droite.

• Sélectionner la cellule K3 au-dessus du graphique,
• Déployer sa liste déroulante et choisir un autre mois,

Comme vous pouvez le voir, la représentation graphique des données s'ajuste automatiquement en cohérence avec les ventes du tableau pour le mois sélectionné.

• Cliquer sur l'une des barres du graphique,

Cette action a pour effet d'intégrer la série complète dans la sélection. En consultant la barre de formule, nous prenons connaissance de la syntaxe pour la source de données du graphique :

=SERIE(Ventes!$K$3;Ventes!$B$6:$B$13; 'vue-graphique-au-clic.xlsm'!Periode; 1)

C'est le troisième argument qui nous intéresse ici : 'vue-graphique-au-clic.xlsm'!Periode. Il concerne les données numériques des ventes pour le mois choisi. Il fait référence à une plage nommée Periode. Celle-ci est nécessairement préfixée du nom du classeur. C'est cette plage qui, retravaillée avec la fonction Excel Decaler, permet de pointer sur la colonne du mois désigné. C'est elle que nous allons devoir adapter pour réaliser ce décalage en fonction désormais de la colonne cliquée par l'utilisateur dans le tableau.

Générer le calcul au clic de la souris
Nous l'avons déjà évoqué dans de précédentes formations, le recalcul intégral d'une feuille Excel est enclenché à chaque modification de cellule. Mais il n'intervient pas au clic de l'utilisateur. Pour qu'il en soit ainsi, nous devons créer une toute petite procédure événementielle en VBA.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1(Ventes),

Nous affichons ainsi sa feuille de code associée au centre de l'écran.

• Au-dessus de la feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,

Cet objet désigne la feuille en cours. Et cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Son code se déclenchera à chaque changement de sélection, donc à chaque clic.

• Entre les bornes de cette procédure, ajouter les lignes de code suivantes :

Private Sub Worksheet_SelectionChange(ByValTarget As Range)

If Target.Column >= 3 And Target.Column <= 8 Then
Application.Calculate
End If

End Sub



Nous exploitons l'objet Target passé en paramètre de la procédure événementielle. Il désigne la cellule cliquée. Sa propriété Column renseigne sur l'indice de colonne de cette cellule cliquée. Nous nous assurons simplement que cet indice est bien compris entre le troisième et le huitième, soit entre les colonnes C et H. Il s'agit des bornes latérales du tableau. Ainsi, nous nous assurons de n'engager aucun traitement lorsque le clic n'est pas cohérent. Lorsqu'il l'est en revanche, nous exploitons la méthode Calculate de l'objet Application. Celle-ci ordonne le recalcul de la feuille.

Ajuster la plage au clic de la souris
Désormais, c'est la fonction Excel Cellule avec son paramètre Colonne qui renseigne sur l'indice de colonne de la cellule cliquée. Nous devons l'exploiter dans la fonction Decaler pour ajuster la source de données du graphique sur la colonne cliquée par l'utilisateur.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + F11),
• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue du gestionnaire de noms, sélectionner la plage Periode,

La zone Fait référence à, en bas de la boîte de dialogue, révèle son actuelle construction :

=DECALER(Ventes!$C$6;; EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5;0)-1; 8)

Pour y voir plus clair, vous pouvez étirer cette boîte de dialogue en largeur. C'est le troisième argument de la fonction Decaler que nous devons changer : EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1. Il concerne le décalage en colonne. En l'état, il s'ajuste sur le choix du mois fait par l'utilisateur dans la liste déroulante. C'est la fonction Equiv qui trouve sa position dans le tableau des ventes. Ce décalage doit désormais réagir en fonction de l'indice de la colonne cliquée par l'utilisateur.

• Adapter la syntaxe de la fonction Decaler comme suit :

=DECALER(Ventes!$C$6;; Cellule('Colonne')-3; 8)

Nous changeons donc pour une syntaxe beaucoup plus simple. Nous exploitons effectivement la fonction Cellule avec son paramètre Colonne entre guillemets. Il en résulte l'indice de la colonne cliquée. Mais cette fonction, contrairement à la fonction Equiv, raisonne en valeur absolue et non pas relativement au tableau. C'est la raison pour laquelle nous lui retranchons trois unités. Les deux premières colonnes A et B sont à ignorer. De plus, le décalage s'opère par rapport à la colonne C, dont la cellule C6 est passée comme point de départ en premier argument de la fonction Decaler. Nous devons donc l'extraire pour ne pas la doubler dans le décompte.

• Sur la gauche de la zone, cliquer sur le bouton à la coche verte pour valider la syntaxe,
• Puis, cliquer sur le bouton Fermer du gestionnaire de noms pour revenir sur la feuille Excel,
• Cliquer alors tour à tour sur différentes cellules du tableau,

Comme vous pouvez le voir, à chaque changement de colonne, la vue du graphique s'adapte pour représenter les ventes des commerciaux pour le mois concerné. En revanche, le titre du graphique, placé en bas à droite de ce dernier, ne suit pas le mouvement pour l'instant. En effet, il est lié à l'information délivrée par la liste déroulante en cellule K3. Or celle-ci ne nous est plus d'aucune utilité.

• Sélectionner la cellule K3 au-dessus du graphique,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de la boîte de dialogue, choisir l'option Tout,

De cette façon, nous supprimons la liste déroulante.

• Valider cette modification en cliquant sur le bouton Ok de la boîte de dialogue,
• Toujours en cellule K3, saisir désormais la formule suivante :

=DECALER(C5;; CELLULE('colonne')-3)

En partant du premier mois en cellule C5, nous opérons un décalage impulsé par le clic utilisateur (CELLULE('colonne')), et corrigé (-3) pour les mêmes raisons que précédemment. Nous prélevons donc l'information sur le mois pour la colonne cliquée. Et cette donnée est utilisée pour le titre du graphique.

Repérer la plage du calcul
Pour renforcer l'interprétation de la représentation graphique, nous proposons de surligner la colonne de la cellule cliquée dans le tableau. De cette manière, les résultats obtenus apparaîtront encore plus évidents et ce, dès la première lecture. Pour ce faire, nous avons besoin de bâtir une règle de mise en forme conditionnelle. Sa conception est triviale. Elle doit comparer chaque indice de colonne dans le tableau avec celui de la colonne cliquée. Lorsqu'ils correspondent, la cellule doit être surlignée. Nous devons donc de nouveau exploiter la fonction Excel Cellule.

• Sélectionner la plage de cellules C5:H13,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, construire la syntaxe suivante :

=COLONNE(C$5)=CELLULE('colonne')

• En bas de la fenêtre Excel, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange assez proche du graphique,
• Dans l'onglet Police, choisir un gris clair pour le texte avec la liste déroulante,
• Valider ces attributs de format puis valider la règle avec le bouton Ok,

Désormais, à chaque clic dans le tableau, en même temps que la représentation graphique s'ajuste, la colonne utilisée comme source de données est automatiquement surlignée.