Introduction
Lorsque l’on veut traduire en graphique une plage de données comportant plusieurs colonnes et plusieurs lignes, il est aisé de le faire avec un graphique de type « histogramme » mais impossible avec un graphique de type « Secteur » ou « Camenbert« . En effet dans ce dernier cas, le graphique n’affiche qu’une colonne de la plage sélectionnée.
La plage de données.
Voici sa représentation graphique en histogramme..
Et la même en Secteur.
Le but de cette présentation est de montrer comment à l’aide d’une liste déroulante, nous allons visualiser ce graphique pour les mois de janvier, février ou mars en fonction de l’élément sélectionné dans la liste.
Préparation
Pour ce travail, nous utiliserons l’outil Validation de données, les plages nommées et les formules SERIE, EQUIV et DECALER
Validation de données
Pour permettre de rendre dynamique notre graphique, nous allons mettre en place une liste sélectionnable par l’utilisateur et à l’aide de l’outil d’excel Validation de données avec l’option Liste comme critère de validation.
Cet outil est accessible par la commande Validation de données du groupe Outils de données depuis l’onglet [Données]
Dans l’onglet [Options] de la boîte de dialogue Validation de données, choisir comme critère de validation Liste et dans la zone Source, entrez =B1;D1 et terminez par OK.
Les formules
SERIE : Si vous sélectionnez la série dans le graphique (voir image).
Vous pourrez observer dans la barre de formule de la feuille Excel la fonction suivante.
=SERIE( Start!$B$1 ; Feuil1!$A$2:$A$5 ; Start!$B$2:$B$5 ; 1 )
Cette formule correspond au graphique Secteur de notre exemple
Examinons les arguments de la fonction SERIE
Si nous souhaitons visualiser le graphique pour le mois de février, il suffit de modifier le premier et le troisième argument.
=SERIE(Start!$C$1;Start!$A$2:$A$5;Start!$C$2:$C$5;1)
Nous en concluons donc que nous allons devoir créer deux arguments dont les valeurs seront changées dynamiquement suivant l’élément sélectionné dans la liste contrôlée par l’outil de Validation de données. Soit pour
Pour rendre dynamique ces arguments, nous allons utiliser une cellule nommée pour le nom de la série et une plage de données pour la plage d’étiquettes. Toutes deux seront calculées dynamiquement par la formule DECALER.
DECALER : La formule DECALER à cinq arguments qui sont Référence, Ligne, Colonne, Hauteur, Largeur. Cette formule renvoie une référence de cellules.
Ainsi pour obtenir les données de janvier, la formule DECALER devrait se lire
=DECALER( $A$1 ; 1 ; 1 ; 4 )
A1, cellule de départ, 1 pour le décalage de ligne (la première ligne de données est en ligne 2), 1 pour le décalage de colonne (la colonne 2 contient les données de Janvier) et la hauteur est de 4 lignes (les données concernant : Excel, Access, Word, PowerPoint)
Pour les données de février
=DECALER( $A$1 ; 1 ; 2 ; 4 )
Seul le 3ème argument a changé (le nombre de colonne de décalage).
Pour calculer ce déplacement de colonne, nous allons utiliser la formule EQUIV.
EQUIV : Cette formule renvoie la position d’un élément dans une plage.
Sa syntaxe est Valeur cherchée, Tableau de recherche, Type
Soit pour notre exemple
=EQUIV(G1;$B$1:$D$1;0)
G1 est la valeur renvoyée par la sélection faîtes dans l’outil Validation de données (Janvier, Février ou Mars).
B1:D1 est la plage où se trouvent les étiquettes de colonnes et le 3ème argument ayant la valeur 0 signifie que nous souhaitons une recherche sur valeur exacte et non proche.
Plage et cellule nommée
:
Modifier la plage source du graphique
Après avoir sélectionné les données dans le graphique, il suffira de modifier les arguments de la fonction SERIE
=SERIE( Graph!Etiquette ; Graph!$A$2:$A$5 ; Graph!Valeur ; 1 )
Attention : Il est important de faire précéder le nom de la plage ou de la cellule nommée par le nom de la feuille suivi du point d’exclamation (ici Graph!).
Commentaires récents