Graphique par cellule sélectionnée

Supposons que vous et moi ayons besoin de visualiser les données du tableau suivant avec les valeurs des ventes de voitures par différents pays en 2021 (données réelles tirées d'ici, soit dit en passant) :

Étant donné que le nombre de séries de données (pays) est important, essayer de les regrouper toutes dans un seul graphique à la fois conduira soit à un terrible "graphique spaghetti", soit à la création de graphiques séparés pour chaque série, ce qui est très fastidieux.

Une solution élégante à ce problème peut être de tracer un graphique uniquement sur les données de la ligne actuelle, c'est-à-dire la ligne où se trouve la cellule active :

La mise en œuvre est très simple - vous n'avez besoin que de deux formules et d'une petite macro en 3 lignes.

Étape 1. Numéro de ligne actuel

La première chose dont nous avons besoin est une plage nommée qui calcule le numéro de ligne sur la feuille où se trouve maintenant notre cellule active. Ouverture sur un onglet Formules - Gestionnaire de noms (Formules — Gestionnaire de noms), cliquez sur le bouton Création (Créer) et entrez-y la structure suivante :

Étape 2. Lien vers le titre

Pour afficher le pays sélectionné dans le titre et la légende du graphique, nous devons obtenir une référence à la cellule avec son nom (pays) dans la première colonne. Pour ce faire, nous créons un autre local (c'est-à-dire Région = feuille courante, pas Livre !) une plage nommée avec la formule suivante :

Ici, la fonction INDEX sélectionne dans une plage donnée (colonne A, où se trouvent nos pays signataires) une cellule avec le numéro de ligne que nous avons précédemment déterminé.

Étape 3. Lien vers les données

Maintenant, de la même manière, obtenons un lien vers une plage avec toutes les données de vente de la ligne actuelle, où se trouve maintenant la cellule active. Créez une autre plage nommée avec la formule suivante :

Ici, le troisième argument, qui est zéro, oblige INDEX à ne pas renvoyer une seule valeur, mais la ligne entière en conséquence.

Étape 4. Substitution de liens dans le graphique

Sélectionnez maintenant l'en-tête du tableau et la première ligne avec des données (plage) et créez un graphique basé sur eux en utilisant Insertion – Graphiques (Insérer — Graphiques). Si vous sélectionnez une ligne avec des données dans le graphique, la fonction s'affichera dans la barre de formule RANGÉE (SÉRIE) est une fonction spéciale qu'Excel utilise automatiquement lors de la création d'un graphique pour faire référence aux données et étiquettes d'origine :

Remplaçons soigneusement les premier (signature) et troisième (données) arguments de cette fonction par les noms de nos plages des étapes 2 et 3 :

Le graphique commencera à afficher les données de ventes de la ligne actuelle.

Étape 5. Macro de recalcul

La touche finale reste. Microsoft Excel recalcule les formules uniquement lorsque les données de la feuille changent ou lorsqu'une touche est enfoncée F9, et nous voulons que le recalcul se produise lorsque la sélection change, c'est-à-dire lorsque la cellule active est déplacée sur la feuille. Pour ce faire, nous devons ajouter une simple macro à notre classeur.

Faites un clic droit sur l'onglet de la fiche technique et sélectionnez la commande Identifier (Code source). Dans la fenêtre qui s'ouvre, saisissez le code du macro-gestionnaire de l'événement de changement de sélection :

Comme vous pouvez facilement l'imaginer, il ne fait que déclencher un recalcul de la feuille chaque fois que la position de la cellule active change.

Étape 6. Mettre en surbrillance la ligne actuelle

Pour plus de clarté, vous pouvez également ajouter une règle de mise en forme conditionnelle pour mettre en évidence le pays actuellement affiché sur le graphique. Pour ce faire, sélectionnez le tableau et sélectionnez Accueil — Mise en forme conditionnelle — Créer une règle — Utiliser une formule pour déterminer les cellules à formater (Accueil — Mise en forme conditionnelle — Nouvelle règle — Utiliser une formule pour déterminer les cellules à mettre en forme):

Ici, la formule vérifie pour chaque cellule du tableau que son numéro de ligne correspond au numéro stocké dans la variable TekRow, et s'il y a une correspondance, le remplissage avec la couleur sélectionnée est déclenché.

C'est tout - simple et beau, non ?

Notes

• Sur les grandes tables, toute cette beauté peut ralentir - la mise en forme conditionnelle est une chose gourmande en ressources, et le recalcul pour chaque sélection peut également être lourd.
• Pour éviter que des données ne disparaissent sur le graphique lorsqu'une cellule est accidentellement sélectionnée au-dessus ou en dessous du tableau, vous pouvez ajouter une vérification supplémentaire au nom TekRow à l'aide des fonctions IF imbriquées du formulaire :

  =SI(CELLULE(“ligne”)<4,SI(CELLULE("ligne")>4,CELLULE(“ligne”)))
  

• Mise en surbrillance des colonnes spécifiées dans un graphique
• Comment créer un graphique interactif dans Excel
• Sélection de coordonnées