Repérer les données choisies sur un graphique Excel

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


Sujets que vous pourriez aussi aimer :


Repérer des données dans un graphique

Lorsqu'un graphique Excel représente plusieurs tendances de données réunies, il n'est pas forcément aisé d'identifier rapidement celle souhaitée. Cette nouvelle astuce présente une technique relativement simple et diablement efficace. L'objectif étant d'interpréter facilement les informations ciblées.



Sur le résultat illustré par la capture, un graphique relate sous forme de courbes, l'évolution des ventes réalisées sur trois années. L'utilisateur peut désigner une année à isoler. Pour cela, il clique sur l'un des entêtes du tableau, soit sur l'une des années. Aussitôt, en même temps que les données changent de couleur dans le tableau, la courbe correspondante est repérée dans le graphique par des marqueurs de couleur en forme de losange. Cette solution est un très bon moyen d'observer et d'interpréter les tendances, surtout lorsque les données sont nombreuses.

Classeur source
Nous proposons d'appuyer cette étude sur un tableau existant et offrant ces données.

• Télécharger le classeur graphique-dynamique.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons ce tableau des ventes sur les années 2020, 2021 et 2022. Pour l'instant, aucune représentation graphique ne pointe à l'horizon. Sur la droite de ce tableau et plus précisément en colonne G, vous notez le format appliqué aux cellules sur la même hauteur que le tableau. Elles sont destinées à recevoir l'extraction des données de l'année cliquée. Nous le comprendrons, l'astuce consiste à superposer des données sur un graphique pour les faire ressortir.

• Cliquer dans l'une des cellules du tableau, par exemple C4,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Gérer les règles,

Vous notez la présence d'une règle de mise en forme conditionnelle associée à la couleur verte.

• En haut de la boîte de dialogue, cliquer sur le bouton Modifier la règle,

Vous découvrez ainsi la syntaxe de la règle en vigueur sur ce tableau :

=COLONNE()=CELLULE("colonne")

Si l'indice de la colonne en cours d'analyse par la règle est le même que celui de la cellule active, alors toutes les données de la colonne sont formatées en vert. La cellule active est censée être l'année cliquée par l'utilisateur pour modifier l'emplacement des marqueurs sur le graphique.

Calculer au clic de la souris
Pour que cette cellule active puisse être détectée au clic sur l'une des années, le calcul de la formule doit être actualisé. Et pour cela, nous avons besoin de déclencher un tout petit code VBA Excel sur un événement spécifique mais que nous connaissons bien désormais.

• Cliquer sur le bouton Annuler de la boîte de dialogue,
• Puis, cliquer sur le bouton Fermer du gestionnaire des règles,
• De retour sur la feuille Excel, réaliser le raccourci clavier Alt + F11,

De cette manière, nous basculons dans l'éditeur de code Visual Basic Excel.

• Dans l'explorateur sur la gauche, double cliquer sur l'élément Feuil1(Resultats),

Ainsi, nous affichons au centre de l'écran, la page de code associée à notre feuille de calcul. Et cette feuille de code est vide pour l'instant.

• En haut de la page de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,

L'objet Worksheet désigne une feuille, en l'occurrence ici la feuille active. Et cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Son code se déclenchera au clic de la souris dans une autre cellule de la feuille. C'est ainsi que nous allons pouvoir intimer l'ordre d'actualiser les formules sur des clics très précis car ils ne concernent que la ligne d'entête du tableau.



Vous notez la présence de l'objet Target passé en argument de cette procédure. Grâce à lui et à ses propriétés, nous allons pouvoir cibler les indices de ligne et de colonne cliqués.

• Entre les bornes de cette procédure, ajouter le code VBA suivant :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If (Target.Row = 3 And (Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5)) Then
Application.Calculate
End If
End Sub

Grâce à la propriété Row de l'objet Target, nous nous assurons que le clic a bien lieu en ligne 3. Grâce à la propriété Column, nous nous assurons que ce clic est bien réalisé en même temps entre la colonne 3 et la colonne 5, soit entre C3 et E3. En d'autres termes, si la cellule cliquée n'est pas l'une des années de l'entête du tableau, le code VBA n'est pas déclenché. Dans le cas contraire, c'est la méthode Calculate de l'objet Application qui est exécutée. Et cette dernière impose à toutes les formules de la feuille de se recalculer.

• Enregistrer les modifications (CTRL + S),
• Puis, fermer l'éditeur VBA Excel,
• De retour sur la feuille, cliquer sur l'une des années en entête du tableau,

Comme nous l'avons expliqué précédemment, la mise en forme conditionnelle n'attendait plus que ce signal pour se déclencher. Désormais, c'est bien l'intégralité de la colonne qui est surlignée au clic.

Récupérer la cellule cliquée
Désormais, pour pouvoir produire l'extraction des données numériques en colonne G, nous devons commencer par extraire l'information sur l'année de la cellule cliquée. Et cette extraction doit intervenir en cellule G3. C'est grâce à cette donnée que nous pourrons rapatrier les valeurs numériques à superposer sur le graphique.

Nous le savons, la fonction Cellule peut renseigner sur différentes propriétés de la cellule active, comme son indice de ligne ou son indice de colonne. En passant ces indices à la fonction Adresse, nous saurons les coordonnées de la cellule cliquée. Et grâce à la fonction Indirect, nous pourrons récupérer le contenu de la cellule pointée par ces coordonnées. Vous l'avez compris, tout est une question d'imbrication de fonctions.

• Cliquer sur la cellule G3 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire la fonction pour les coordonnées suivie d'une parenthèse, soit : Adresse(,
• Inscrire la fonction d'information suivie d'une parenthèse, soit : Cellule(,
• Lui passer le paramètre ligne entre guillemets, soit : "ligne",

Il est aussi possible de double cliquer ce choix dans la liste des propositions qui se déclenche.

• Fermer la parenthèse de la fonction Cellule,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Adresse,
• Taper de nouveau la fonction d'information suivie d'une parenthèse, soit : Cellule(,
• Lui passer le paramètre colonne entre guillemets, soit : "colonne",
• Fermer la parenthèse de la fonction Cellule,
• Fermer la parenthèse de la fonction Adresse,
• Puis, fermer la parenthèse de la fonction Indirect,
• Enfin, valider la formule à l'aide de la touche Entrée du clavier,

Une alerte apparaît. Elle indique que des références circulaires sont repérées. Ce phénomène est fort logique. En l'état, la cellule du calcul étant active, la formule fait référence à elle-même. Donc, elle se mord la queue et ne peut se calculer.

• Ignorer cette alerte en cliquant sur le bouton Ok,

Désormais, si vous cliquez sur l'une des années du tableau, en même temps que la mise en forme conditionnelle se déclenche pour repérer la colonne, l'information sur l'année est rapatriée dans la cellule G3. Et c'est bien l'information dont nous avons besoin pour importer les chiffres associés dans les cellules du dessous.



La syntaxe complète de la formule que nous avons construite est la suivante :

=INDIRECT(ADRESSE(CELLULE("ligne"); CELLULE("colonne")))

Nous pouvons maintenant poursuivre.

Données attachées à la cellule cliquée
Désormais, pour extraire les chiffres d'affaires associés à l'année cliquée, nous avons besoin de deux informations pour pouvoir exploiter les fonctions Excel index et Equiv. Il s'agit de l'indice de ligne et de l'indice de colonne de l'information à importer. L'indice de colonne peut être trouvé grâce à la recherche de l'année importée par la fonction Equiv dans la ligne de titre du tableau. L'indice de ligne n'est ni plus ni moins que celui de la cellule du calcul en cours. Il suffit d'exploiter la fonction Ligne sur une cellule du même indice pour produire un calcul réplicable sur les cellules du dessous.

• Cliquer sur la cellule G4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de gestion des anomalies, suivie d'une parenthèse, soit : Sierreur(,

Comme vous le savez, une fonction d'extraction répond par une erreur lorsqu'elle ne trouve pas la donnée cherchée. Ce cas se présentera forcément lorsqu'aucune année du tableau ne sera cliquée. Nous décidons donc de gérer ces signaux.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau en sélectionnant la plage C3:E7,

En effet, pour le repérage par l'indice de ligne, il ne faut pas qu'il y ait de décalage par rapport au début de la recherche. Nous incluons donc les années avec tous les chiffres potentiellement concernés par la recherche.

• Enfoncer la touche F4 du clavier pour figer intégralement cette plage, soit : $C$3:$E$7,

La formule est destinée à être répliquée sur les lignes du dessous pour l'extraction de tous les chiffres concordants. Malgré ce déplacement, les bornes de l'extraction elles, ne doivent pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne de la fonction Index,
• Inscrire la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Puis, désigner une cellule de la ligne 2 en cliquant par exemple sur A2,

En effet, le premier chiffre à extraire est situé sur la deuxième ligne du tableau, relativement à la sélection exercée en premier argument de la fonction Index. Ainsi, en répliquant la formule sur les lignes du dessous, avec Ligne(A3) puis ligne(A4) et ligne(A5), nous obtiendrons bien l'extraction des chiffres dans l'ordre logique.

• Fermer la parenthèse de la fonction Ligne,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,
• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• Désigner l'année cherchée en cliquant sur la cellule G3,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $G$3,

Là encore, nous nous apprêtons à répliquer la formule sur les lignes du dessous pour l'extraction de tous les chiffres associés à l'année cliquée. Malgré le déplacement à la verticale, les formules répliquées devront immuablement faire référence à cette cellule pour trouver la position de l'année en colonne dans le tableau d'extraction.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,
• Désigner la ligne des années en sélectionnant la plage de cellules C3:E3,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$3:$E$3,

La raison est la même que les précédentes. Malgré le déplacement de la formule répliquée, la recherche des années se fait immuablement sur cette ligne qui ne doit pas suivre le déplacement.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour engager une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Dès lors, taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour garder la cellule du résultat vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider la formule à l'aide du raccourci clavier CTRL + Entrée,

Grâce à cette astuce, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Une fois encore et pour les mêmes raisons, une alerte sur les références circulaires surgit. Il convient donc de l'ignorer en cliquant sur le bouton Ok.

• Cliquer et glisser la poignée du résultat jusqu'en cellule G7,
• Puis, cliquer sur l'une des années dans l'entête du tableau,

Comme vous pouvez l'apprécier, tous les chiffres attachés à l'année cliquée sont instantanément rapatriés. C'est cette source d'information extraite que nous souhaitons superposer sur le graphique à construire pour faire ressortir les données ciblées par l'utilisateur.



La syntaxe complète de la formule d'extraction que nous avons construite est la suivante :

=SIERREUR(INDEX($C$3:$E$7; LIGNE(A2); EQUIV($G$3; $C$3:$E$3; 0)); "")

Représentation graphique
Il est temps désormais de bâtir le graphique en courbes rendant compte de l'évolution des chiffres d'affaires sur ces trois années. Pour cela, rien de plus simple et comme vous le savez, il s'agit premièrement de sélectionner toutes les données à représenter.

• Sélectionner toutes les données du tableau, soit la plage de cellules B3:E7,
• En haut de la fenêtre Excel, cliquer sur l'onglet Insertion pour activer son ruban,
• Dans la section Graphiques du ruban, cliquer sur le bouton des courbes,

Une liste de propositions dérivées se suggère pour représenter ces évolutions.

• Dans la section Courbe 2D, cliquer sur la miniature de la courbe avec Marques,

La représentation graphique est ainsi instantanément construite et posée sur la feuille.

• Double cliquer sur l'un des chiffres de l'axe des ordonnées,

Grâce à cette action, nous déclenchons l'affichage du volet Format de l'axe sur la droite de l'écran.

• Dans la zone Minimum, saisir la valeur 40000,

Nous réajustons l'échelle à l'ordre de grandeur des données pour que la représentation graphique optimise l'espace disponible.

• Dans la section Styles du graphique du ruban Création, cliquer sur la miniature du Style 6,

Nous appliquons ainsi des préférences de mise en forme et notamment de couleurs.

• Sélectionner le titre du graphique et enfoncer la touche Suppr du clavier,

De cette manière et pour des raisons de sobriété, nous épurons au mieux la représentation.

• En haut à droite de la fenêtre Excel, cliquer sur l'onglet contextuel Mise en forme,
• Cliquer alors sur le bouton Remplissage dans la section Styles de formes du graphique,
• Dans la liste des propositions, cliquer sur l'option Aucun remplissage,

De cette manière, la représentation graphique s'intègre parfaitement dans son environnement.



De plus, il peut être intéressant d'appliquer un bleu clair, semblable à celui de la feuille aux données des deux axes. Pour plus de sobriété une fois encore, il paraît opportun de supprimer le quadrillage en arrière-plan.

Superposer les données sur un graphique
Il est temps d'intégrer les données extraites par le clic de l'utilisateur dans ce graphique. Nous pourrons ainsi retravailler la courbe résultante pour faire ressortir explicitement la période choisie. Et c'est une nouvelle astuce qui va nous permettre d'intégrer ces données a posteriori dans la représentation graphique.

• Cliquer sur l'une des années du tableau pour actualiser l'extraction en colonne G,
• Sélectionner la plage de cellules G4:G7,
• La copier à l'aide du raccourci clavier CTRL + C,
• Cliquer alors sur la représentation graphique pour l'activer,
• Dès lors, coller les données à l'aide du raccourci clavier CTRL + V,

Comme vous pouvez le voir, une nouvelle courbe dans une nouvelle couleur s'invite. Elle superpose parfaitement la courbe de l'année cliquée.

• Double cliquer sur la courbe de la nouvelle couleur,

Nous affichons ainsi ses paramètres dans le volet qui se suggère sur la droite de l'écran.

• Dans ce volet, cliquer sur l'icône de menu Remplissage et trait,
• Dans la section Courbe, cocher la case Aucun trait,

De fait, la courbe jumelle qui était masquée jusqu'alors réapparaît.

• Cliquer sur le sous menu Marque pour activer sa rubrique,
• Dans la section Bordure, cocher la case Aucun trait,
• Cliquer alors sur la section Option des marques pour la déployer,
• Cocher ensuite la case Prédéfini,
• Avec la liste Type du dessous, choisir une forme en losange,
• Puis, régler la taille à 15 pt,

De cette manière, seuls les points de référence sont mis en valeur sur la courbe relatant l'année cliquée par l'utilisateur.

• Cliquer sur la rubrique Remplissage pour déployer sa section,
• Avec le pot de peinture, choisir un vert clair semblable à celui de la colonne active,
• Cliquer sur la croix du volet pour le refermer,
• Puis, cliquer sur une autre année en entête du tableau.

Désormais et comme vous pouvez le constater avec satisfaction, à chaque clic, la courbe représentative est mise en valeur par ces marqueurs personnalisés. L'utilisateur sait instantanément repérer l'évolution qu'il a choisie sur la représentation graphique.