acqua

Graphique avec une source de données dynamique

30/07/2014

Un graphique dépend d’un tableau amené à être modifié. Afin que le graphique se mette à jour automatiquement en fonction de la taille de son tableau de référence, il faut que les deux soient dynamiques.

Pour cela, il faut avoir au préalable créé une liste dynamique. Celle-ci ne sera toutefois pas utilisée en guise de menu déroulant mais comme plage de référence pour le graphique.

Ainsi, si le principe d’utilisation de la fonction « décaler » reste d’actualité, ses paramètres ne sont pas les mêmes que pour une liste dynamique.

1) Création de la liste dynamique

Dans notre exemple, nous allons prendre un premier tableau auquel nous associerons le graphique :

Graphique avec une source de données dynamique - image 1

Pour rendre la plage dynamique, aller tout d’abord dans l’onglet Formules puis cliquer sur « Gestionnaire de noms » (Name Manager en anglais).

Apparaissent dans cette fenêtre toutes les cellules/plage de cellules que vous avez déjà nommées via la case située en haut à gauche de votre feuille (vide si rien de nommé).

Il s’agit désormais de nommer la plage de cellule qui changera ultérieurement de taille.

1) Nom de la plage

Dans « Gestionnaire de noms », cliquer sur Nouveau. Nommer la plage de cellule concernée (« source_données_graph » dans notre exemple car nous nous en servirons pour créer un graphique dynamique)

Graphique avec une source de données dynamique - image 2

2) Zone de la plage

Dans la case « Fait référence à » (« Refers to » en anglais), nous allons donc utiliser la fonction décaler.

Nous souhaitons que notre graphique prenne ici comme plage de données : $A$1:$D$6 appartenant à notre onglet nommé Graph. Si nous rajoutons une ligne à ce dernier, la plage de données deviendra : $A$1:$D$7

Graphique avec une source de données dynamique - image 3

Ainsi, en utilisant la fonction décaler, nous prendrons comme paramètres :

1. « Réf » : cellule de référence :  la cellule $A$1 de notre onglet Graph

2 et 3 : « Lignes » et « Colonnes » : 0 car pas de décalage horizontal/vertical entre la référence et la cellule résultante (nous voulons une plage de données : la liste commence dans la cellule de référence A1)

4. « Hauteur » : elle correspond au nombre de lignes de la plage et sera donc dynamique. Ici, elle est égale à 6 mais si nous insérons une ligne supplémentaire, celle-ci sera égale à 7. Ce problème se résout facilement grâce à la fonction NBVAL (COUNTA en anglais) qui compte le nombre de cellules non vide d’une plage choisie. Nous choisissons la colonne A qui comprend 6 valeurs non vides : NBVAL(Graph!$A:$A)

5. « Largeur » : elle correspond au nombre de colonnes de la plage et est fixe dans notre cas : 4

Dans la case « Fait référence à », nous allons finalement rentrer :

=DECALER(Graph!$A$1,0,0,NBVAL(Graph!$A:$A),4)

(en anglais : =OFFSET(Graph!$A$1;0;0;COUNTA(Graph!$A:$A);4))

Cliquer sur la case de validation de « Fait référence à ».

Dans la fenêtre « Gestionnaire de noms », nous avons désormais notre plage dynamique nommée « source_données_graph». En cliquant dans la formule de « Fait référence à », la plage sélectionnée se verra entourée de pointillés.

Graphique avec une source de données dynamique - image 4

Si nous ajoutons la ligne « Administratif et comptabilité » à notre tableau, la plage « source_données_graph » s’adapte bien automatiquement à la nouvelle taille du tableau :

Graphique avec une source de données dynamique - image 5

De la même façon, si l’on souhaite ajouter d’autres années à notre tableau et que celles-ci soient prises en compte, il faudra aussi compter le nombre de valeurs présentes en largeur (ligne 1 par exemple) et finalement rentrer dans la case « Fait référence à » :

=DECALER(Graph!$A$1,0,0,NBVAL(Graph!$A:$A), NBVAL(Graph!$1:$1))

(en anglais : =OFFSET(Graph!$A$1;0;0;COUNTA(Graph!$A:$A);COUNTA(Graph!$1:$1)) )

Graphique avec une source de données dynamique - image 6

2) Création du graphique associé à la liste dynamique

Insérons maintenant un graphique classique associé au premier tableau :

Graphique avec une source de données dynamique - image 7

Afin que celui-ci s’adapte à la taille du tableau si ce dernier change de taille, il faut ouvrir Visual Basic (via la commande ALT + F11 en raccourci clavier) et taper le code suivant qui permettra à notre graphique de se mettre à jour (il est possible de copier-coller ce code directement dans la fenêtre Visual Basic) :

‘on crée la macro “rafraichir_graph” :

Sub rafraichir_graph ()

‘on active notre graphique (il s’agit du premier de notre classeur, son nom est “Chart 1”, visible en haut à gauche de notre feuille)

ActiveSheet.ChartObjects(« Chart 1 »).Activate

‘on définit comme source de données du graphique notre plage dynamique « source_données_graphs » préalablement programmée:

ActiveChart.SetSourceData Source:=Range(« source_données_graphs »)

‘on indique la fin de la macro

End Sub

 

Ainsi, lorsque l’on rajoute au tableau la ligne « Administratif et comptabilité » ainsi qu’une colonne « Année 4 », le graphique est automatiquement mis à jour suite au lancement de la macro :

Graphique avec une source de données dynamique - image 8