Bonjour à tous,
Il s'agit d'une question récurrente, donc voici une réponse approfondie.
Tout d'abord quelques explications techniques, puis des vidéos récapitulatives plus bas.
Quelques définitions
Par liste dynamique, on entend la création d'un nom faisant référence à une plage de manière dynamique, c'est à dire que lorsqu'on ajoute un nouvel élément en dessous de la liste, ce nom va automatiquement prendre en compte ce nouvel élément.
Par exemple, si un nom fait référence à une liste de villes, en ajoutant une nouvelle ville en dessous de cette liste, le nom va automatiquement inclure cette nouvelle liste.
Ceci est très pratique pour créer des sources de données dynamiques pour des tableaux croisés dynamiques, ou encore en lien avec une liste déroulante via Données - Validation - Liste (voir vidéo).
Il y a pour cela deux techniques.
Première technique (Excel 2007 et suivantes) : avec un tableau
Cette solution est la plus simple, il suffit de mettre la liste en format de tableau (onglet Accueil), puis de lui attribuer un nom.
Au passage, vous verrez que le nom ne sera visible que via le gestionnaire de noms (onglet Formules)
Deuxième technique (toutes les versions d'Excel) : avec la fonction DECALER
Vous avez une présentation détaillée de cette fonction dans ce fichier et ici.
La fonction DECALER permet de définir une plage en fonction d'une référence de départ, d'un décalage de ligne et de colonne par rapport à cette référence, ainsi que d'une définition de la hauteur et de la largeur de cette plage. Si la hauteur et la largeur ne sont pas définies, il y aura un simple décalage de la cellule.
Par exemple, =DECALER(Feuil1!$A$1;2;3;4;4) renverra la plage allant
- de la cellule A1+2 lignes vers le bas et 3 colonnes vers la droite, soit donc la cellule D3
- à la cellule D3 + 4 lignes vers le bas et 4 colonnes vers la droite, soit donc la cellule G6
Au final donc, la plage D4:G6 comme présenté ci-dessous.
On peut ensuite améliorer ce nom pour prendre en compte une liste dynamique disponible dans une colonne.
On utilise pour cela la fonction NBVAL qui permet de retourner le nombre de valeurs dans une cellule.
Ça nous donne donc ceci : =DECALER(Feuil1!$A$1;1;0;NBVAL(Feuil1!$A:$A)-1;1)
qui se lit comme :
- Partir de A1,
- décaler d'une cellule vers le bas (donc A2),
- de 0 cellule vers la droite (donc A2),
- puis établir la hauteur comme étant le nombre de valeur de la colonne A (ici 4) moins 1 pour ne pas prendre en compte la ligne de titre.
- Finalement, on définit la largeur de la plage à 1, pour ne renvoyer qu'une colonne.
On a donc au final la plage A2:A4.
Si on ajoute une nouvelle ligne, la plage sera modifié automatiquement pour renvoyer A2:A5.
Utilisation des noms dynamiques
Une fois le nom dynamique créé, vous pouvez l'utiliser de multiples manières :
- soit comme source d'une liste de validation, via Données - Validation - Listes et taper =Nom_Dynamique
- Pour 2003, soit comme source d'un tableau croisé dynamique, dans ce cas, on définira aussi la largeur de la plage comme étant dynamique. On a donc la formula suivante (en considérant que la plage commence en A1 et que la feuille ne contient uniquement que la table de données) :
=DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A);NBVAL(Feuil1!$1:$1))
Et finalement en vidéo :
Pour 2007 et versions ultérieures
Pour toutes les versions d'Excel (enregistré sur 2003)
Pour toutes les versions d'Excel (enregistré sur 2010)
J'espère que vos neurones sont toujours vivants !
N'hésitez pas si vous avez des questions ou des commentaires.
Gaëtan