Comment éviter de refaire mes graphiques chaque mois. Comment utiliser une liste déroulante pour sélectionner les données à représenter.

Cette question a déjà été abordée à plusieurs endroits du site mais mérite d'être remise à jour, notamment pour montrer comment faire avec les versions 2007 et suivantes. Ce tutoriel exploite une fonctionnalité vraiment pratique de ces versions : les tables (appellées listes dans la version 2003, inexistantes avant) pour incorporer automatiquement de nouvelles données dans un graphique.
Ce tutoriel est illustré par le classeur fc-graphiquedynamique2010 que vous pouvez télécharger. Le lecteur ayant une version antérieure à 2007 sera plus intéressé par sa version précédente : fc-graphdyn.

NB : il y a eu de subtiles différences de notation des références dans les tables entrela version 2007 et la version 2010. Le principe ne change pas mais sachez que ce tuto a été écrit avec la version 2010. Si vous ouvrez le classeur avec une version 2007, les formules que vous verrez peuvent être légèrement différentes. Adaptez vous !

Introduction

Dans l'exemple illustrant ce tutoriel, on suit 3 indices, Indice1, Indice2, Indice3 en fonction du temps.
On dispose de ces données sur une très longue période (pour l'exemple je n'en n'ai mis que quelques unes !) et pour suivre ces indices, on souhaite pouvoir définir facilement la plage des dates et l'indice à analyser sans avoir à refaire le graphique à chaque fois. On veut également que les valeurs ajoutées chaque jour soient automatiquement intégrées au graphique.

Nous allons construire pas à pas ce graphique en commençant par entrer correctement les données que nous placerons dans la feuille "data".
Oubliez tout de suite les cellules fusionnées, les présentations sophistiquées. Si vous voulez présenter vos données chiffrées avec un "joli" tableau faites une feuille séparée pour ça, qui se mette à jour à partir de la feuille data. Excel travaille bien quand on lui fournit des données en colonnes, sans vides, avec des entêtes sur chaque colonne :

Nommer des plages de façon dynamique avec la création d'une Table

On va ensuite transformer la plage de données en table : dans l'onglet accueil, cliquez sur le bouton "mettre sous forme de tableau" et choisissez un format à votre gout.

Dans l'onglet tableau qui s'affiche, à gauche vous voyez le nom du tableau qui s'affiche. Par défaut c'est tableau1. On va le changer pour l'appeler "Base" : tapez juste ce nom dans la case. Il est bien plus facile de se retrouver dans un gros classeurs si vous nommez vos tableaux au fur et à mesure de leur création.

Ceci nous simplifie le travail car excel définit alors automatiquement des noms dynamiques associés aux différents éléments de cette table (voir le tutoriel sur ce sujet pour plus d'infos).
Dynamique ? c'est à dire ?

Et bien si vous ajoutez des données sous celles déjà présentes dans la feuille, elles sont automatiquement intégrées au tableau. Nul besoin de modifier les formules ou les noms, ça se fait TOUT SEUL.

Le graphique dynamique pour les nuls

Sur la page ExempleSimplifié du classeur associé, vous avez un petit tableau avec quelques dates, et deux colonnes de valeurs (indice1 et indice2), le second étant le double du premier.
Construisez un graphique avec les dates et l'indice 2. Pour cela, sélectionnez la première colonne du tableau en positionnant le curseur en haut de la colonne 1 (il se transforme en flèche) puis, tout en appuyant sur la touche ctrl, sélectionnez la 3° colonne. Vous voyez qu'excel ne sélectionne pas les étiquettes des colonnes (mais il ne les oublie pas !). Cette façon de sélectionner des données est particulièrement rapide quand vous avez un grand nombre de lignes. N'utilisez plus les ascenceurs ! Créez maintenant un simple histogramme.

Sous la dernière valeur de la colonne A, ajoutez une date. Excel intègre automatiquement cette donnée dans la table. Plus fort : il recopie les formules des lignes du dessus dans la nouvelle ligne créée. Dans la 3° colonne vous trouvez donc [@Indice1]*2, ce qu'on peut traduire par "prendre la valeur de la colonne indice1 sur la même ligne et multiplier par 2. Entrez une valeur pour indice1.

Remarquez que les valeurs ajoutées dans la table s'ajoutent automatiquement dans le graphique.
Si vous avez assez peu de données ou que vous souhaitez voir toutes vos données sur un même graphique, pas la peine de vous casser d'avantage la tête.

Beaucoup de tableaux de bord contiennent des formules faisant référence à des données qui sont entrées au cours du temps. Une demande fréquente c'est d'éviter de voir une courbe plonger tant que les données alimentant les données sources ne sont pas entrées. On peut ruser en faisant afficher des #N/A mais ça pose d'autres problèmes parfois.
Le plus simple : vous ne construisez votre table qu'avec des données valides. Autrement dit, vous n'ajoutez une date que quand vous avez les données correspondantes. Ca peut bien sur se faire par macro, ou par requête à partir d'un autre classeur ou d'un fichier texte. Pas besoin de tirer les formules vers le bas, ça se fait tout seul.

Un graphique dynamique avec quelques options supplémentaires

Si vous avez plusieurs centaines voire milliers de lignes et de de très nombreuses colonnes dans votre tableau, il n'est pas possible d'analyser cela sur un seul graphique. Comment dire en 3 clics à excel de ne représenter sur le graphique que 10 valeurs de l'indice 3 à partir du 5 mars 2010 ?
Pour mettre plus facilement au point notre graphique, nous allons mettre ces 3 valeurs dans 3 cellules de la feuille, en H2, H3 et H4 dans l'exemple. Ces 3 cellules, auxquelles nous allons attribuer un nom suffiront à modifier le graphique.
date_départ (en H2) = 05/03/2010
nb_jours (en H3) = 10
Indice_choix (en H4) = Indice3

Avec nos premières données dans la ligne 2 de la feuille (la ligne 1 contient les entêtes) on veut donc construire un graphique avec pour référence des X (les dates) la plage $A$53:$A$62 et pour les valeurs de l'indice3 la plage et $D$53:$D$62. On va s'affranchir de ces références absolues en ne faisant référence qu'aux 3 cellules que nous venons de définir. La formule générée sera mise dans un nom défini qu'on utilisera comme source de données.

Un nom défini peut en effet faire référence à une valeur fixe (0.196 pour un taux de TVA par exemple), à une référence de cellule ($H$4) mais on peut surtout mettre des formules pour définir un nom et nous allons utiliser cette possibilité pour faire varier les données sources de notre graphique.

Définir les noms utilisés dans les séries

La création de la table a généré  automatiquement des noms définis par défaut.

• Le nom de la Table: Base fait référence à la plage des valeurs de la table SANS les entêtes de colonne.
• Base[Date] fait référence à la plage contenant les dates. 

Ce sont des noms dynamiques. 

Dans les versions antérieures d'excel on aurait du utiliser la fonction DECALER (offset en anglais) pour que la plage se mette à jour quand on enlève ou ajoute des données. Ici avec une table c'est automatique. 
Autre avantage : quand on utilise la fonction décaler il ne faut pas avoir de trous dans les colonnes de valeurs, ni modifier la présentation de la feuille en inserant par exemple des lignes au dessus. Rien de tel avec les tables. Vous pouvez même mettre deux tables alimentant des graphiques différents les unes en dessous des autres.

INDEX(Base;EQUIV(date_départ;Base[Date];0);1)

Renvoie la position de la cellule contenant 15/03/2010 (date_départ) dans la colonne Date de la table. Ici la cellule de la 1° colonne et de la 52° ligne.

INDEX(Base;EQUIV(date_départ;Base[Date])+nb_jours-1;1)

renvoie la position de la cellules située dans la colonne date à la 52+10-1= 61° ligne

=INDEX(Base;EQUIV(date_départ;Base[Date];0);1):INDEX(Base;EQUIV(date_départ;Base[Date])+nb_jours-1;1) 

renvoie par conséquent une plage de cellules de référence $A$53:$A$62 si les entêtes de la table sont dans la première ligne de la feuille. Si on déplace la table, la formule restera valable.

On a ainsi supprimé toute écriture d'adresses de cellules en dur. Evidemment si on change les valeurs de nos 3 cellules clé, la plage s'adapte automatiquement.

On va faire de même pour définir la plage des ordonnées.
Index3 est la 4° colonne de notre Base. 

INDEX(Base;EQUIV(date_départ;Base[Date];0);4) renvoie donc $D$53

Cette valeur 4 est renvoyée par la formule

EQUIV(Indice_choix;Base[#En-têtes];0))

Base[#En-têtes] est un nom qui est automatiquement créé lors de la création de la Table.

En remplaçant là ou il faut, la formule

=INDEX(Base;EQUIV(date_départ;Base[Date];0);EQUIV(Indice_choix;Base[#En-têtes];0)):INDEX(Base;EQUIV(date_départ;Base[Date])+nb_jours-1;EQUIV(Indice_choix;Base[#En-têtes];0))

renvoie la plage $D$53:$D$62 que l'on va utiliser pour les Y

Entrer les noms ainsi définis
Dans l'onglet Formules/définir un nom on va maintenant définir 2 nouveaux noms : Indice_plage et dates_plage en utilisant ces formules

Il ne nous reste plus qu'à utiliser ces noms/formules comme source de données pour notre graphique.
Commençons par construire un histogramme standard en utilisant les deux premières colonnes. Il est plus simple je trouve de modifier un graphique existant que d'en créer un à partir de rien.

Utiliser des noms définis comme données sources d'un graphique

Pour faire cela on va modifier les références qu'on trouve dans les séries du graphique que nous avons contruit à partir de nos données.
Cliquez sur l'histogramme. Dans la barre de formule vous voyez ceci :

=SERIE(data!$B$1;data!$A$2:$A$410;data!$B$2:$B$410;1)

• Data!$B$1 c'est le titre du graphique. 
• data!$A$2:$A$410 représente la plage des abscisses (les X, ici nos dates)
• data!$B$2:$B$410 représente celle des ordonnées (les Y, ici les valeurs de nos indices)

On va modifier cela en remplaçant ces plages écrites "en dur" par les noms/formules que nous avons défini plus haut.
Pour utiliser un nom défini dans une série d'un graphique, il faut le faire précéder du nom de le feuille ou du classeur, au choix. Lorsque l'on valide, si on mis le nom de la feuille (souvent plus court !) excel met celui du classeur.

On va utiliser comme titre du graphique la valeur de la cellule Indice_Choix.
Dans la barre de formule, on modifie directement en remplaçant chaque argument par le nom défini:

=SERIE(data!Indice_choix;data!dates_plage;data!Indice_plage;1)

ce qui devient après validation

Et voilà !
si vous modifiez les valeurs des 3 cellules, votre graphique se met à jour tout seul.

Rendre une copie du graphique indépendante des données sources

Si vous souhaitez conserver une "copie" de ce graphique et la rendre indépendante des modifications que vous effectuez dans ces 3 séries, procédez de la façon suivante:
Sélectionnez le graphiquee. Fait un clic gauche sur le graphique. Maintenez le bouton de la souris enfoncée. Appuyez sur la touche Ctrl et en maintenant cette touche et la souris enfoncées, déplacez la souris puis delachez. Vous avez une copie du graphique.
Pour figer les valeurs des séries, cliquez sur une des barres de l'histogramme. Placez le pointeur dans la barre de formule et appuyez sur la touche F9, ce qui remplace les références par leurs valeurs.

Utiliser des listes déroulantes pour choisir les valeurs des 3 cellules clés.

Plutôt que de devoir écrire les valeurs dans les 3 cellules clés, ce qui peut être source d'erreurs, il est parfois plus simple de choisir ces valeurs grace à des listes déroulantes.
Ceci se fait tout simplement grace à l'outil données/validation
Cliquez sur la cellule H2 Données/validation. Sélectionnez liste dans la liste déroulante puis avec la souris cliquez sur la première colonne de la table.
On ne peut pas entrer un nom défini automatiquement par un tableau comme référence (un oubli ? un bug ?). Ce qui s'inscrit c'est la référence en dur de la plage des dates mais heureusement, si on ajoute des valeurs dans la table elles sont prises en compte dans la validation de données.

En H3 vous pouvez soit imposer un choix sur l'étendu des dates à étudier (10 jours, 30, 365... en écrivant les différentes valeurs séparées par un point-virgule. Vous pouvez aussi juste imposer que ce soit un nombre entier.
En H4 , pour ne pas avoir l'étiquette de la première colonne comme choix possible, vous pouvez soit écrire en dur la référence de la plage $A$1:$D$1, soit encore définir un nom dynamique mais cette fois en utilisant la fonction décaler :

=DECALER(Base[[#En-têtes];[Date]];0;1;;NBVAL(Base[#En-têtes])-1)

De cette façon si vous déplacez votre table les choix resteront valables.
Sélectionnez H4 Données/validation/liste et dans la zone de forume écrivez
=Etiquettes
puis validez.
Cette fois c'est fini :-) Amusez vous bien.
Ceci peut paraitre long à la description mais c'est en fait très rapide et si vous devez régulièrement modifier des graphiques type tableaux de bord, vous comprendrez vite l'intérêt !