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
merci infiniment
Attention, les listes sous forme de tableau que vous citez en début d’article sont effectivement très pratique => Je les utilise régulièrement pour mes tableaux. Un inconvénient toutefois, elles ne fonctionnent pas si vous partagez votre tableau Excel avec plusieurs personnes. Dans ce cas vous êtes obligé de créer des noms dynamiques avec la fonction Decaler.
Merci pour la précision, mais vous voulez dire quoi par plusieurs personnes? Si vous partagez avec des gens qui ont 2003, oui, ca pose problème.
Gaetan
Pour moi c’est enfin quelque chose que j’ai longtemps cherché à faire mais bien sur sans résultat. Donc bravo Gaetan d’en avoir mis + d’un comme moi sur la piste. Par contre kje pense qu’il aurait été intéressant de poursuivre jusqu’au tri de la liste car quand on a établi la liste, qu’on la fait évoluer de façon dynamique, on ne peut pas se passer de mettre cette liste en ordre alpha. Alors comment faire pour que cette liste soit ensuite triée (sans macro supplémentaire).
A bientôt
Bonjour Bernard,
Excellente question.
En VBA, c’est très rapide à faire, notamment avec une procédure événementielle.
Sinon, ca doit être possible sans VBA en ajoutant une colonne à droite de la première et en utilisant une formule qui trierait cette colonne.
Je vais en faire un prochain post.
A bientôt.
Gaetan