Exploser vos neurones: création de listes et noms dynamiques

Bon­jour à tous,

Il s’ag­it d’une ques­tion récur­rente, donc voici une réponse approfondie.

Tout d’abord quelques expli­ca­tions tech­niques, puis des vidéos réca­pit­u­la­tives plus bas.

Quelques définitions

Par liste dynamique, on entend la créa­tion d’un nom faisant référence à une plage de manière dynamique, c’est à dire que lorsqu’on ajoute un nou­v­el élé­ment en dessous de la liste, ce nom va automa­tique­ment pren­dre en compte ce nou­v­el élément.

Par exem­ple, si un nom fait référence à une liste de villes, en ajoutant une nou­velle ville en dessous de cette liste, le nom va automa­tique­ment inclure cette nou­velle liste.

Ceci est très pra­tique pour créer des sources de don­nées dynamiques pour des tableaux croisés dynamiques, ou encore en lien avec une liste déroulante via Don­nées — Val­i­da­tion — Liste (voir vidéo).

Il y a pour cela deux techniques.

Première technique (Excel 2007 et suivantes) : avec un tableau

Cette solu­tion est la plus sim­ple, il suf­fit de met­tre la liste en for­mat de tableau (onglet Accueil), puis de lui attribuer un nom.

Au pas­sage, vous ver­rez que le nom ne sera vis­i­ble que via le ges­tion­naire de noms (onglet Formules)

Deuxième technique (toutes les versions d’Excel) : avec la fonction DECALER

Vous avez une présen­ta­tion détail­lée de cette fonc­tion dans ce fichi­er et ici.

La fonc­tion DECALER per­met de définir une plage en fonc­tion d’une référence de départ, d’un décalage de ligne et de colonne par rap­port à cette référence, ain­si que d’une déf­i­ni­tion de la hau­teur et de la largeur de cette plage. Si la hau­teur et la largeur ne sont pas définies, il y aura un sim­ple décalage de la cellule.

Par exem­ple, =DECALER(Feuil1!$A$1;2;3;4;4) ren­ver­ra la plage allant

- de la cel­lule A1+2 lignes vers le bas et 3 colonnes vers la droite, soit donc la cel­lule D3

- à la cel­lule D3 + 4 lignes vers le bas et 4 colonnes vers la droite, soit donc la cel­lule G6

Au final donc, la plage D4:G6 comme présen­té ci-dessous.

On peut ensuite amélior­er ce nom pour pren­dre en compte une liste dynamique disponible dans une colonne.

On utilise pour cela la fonc­tion NBVAL qui per­met de retourn­er le nom­bre 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 :

• Par­tir de A1,
• décaler d’une cel­lule vers le bas (donc A2),
• de 0 cel­lule vers la droite (donc A2),
• puis établir la hau­teur comme étant le nom­bre de valeur de la colonne A (ici 4) moins 1 pour ne pas pren­dre en compte la ligne de titre.
• Finale­ment, on définit la largeur de la plage à 1, pour ne ren­voy­er qu’une colonne.

On a donc au final la plage A2:A4.

Si on ajoute une nou­velle ligne, la plage sera mod­i­fié automa­tique­ment pour ren­voy­er A2:A5.

Utilisation des noms dynamiques

Une fois le nom dynamique créé, vous pou­vez l’u­tilis­er de mul­ti­ples manières :

- soit comme source d’une liste de val­i­da­tion, via Don­nées — Val­i­da­tion — Listes et taper =Nom_Dynamique

- Pour 2003, soit comme source d’un tableau croisé dynamique, dans ce cas, on défini­ra aus­si la largeur de la plage comme étant dynamique. On a donc la for­mu­la suiv­ante (en con­sid­érant que la plage com­mence en A1 et que la feuille ne con­tient unique­ment 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 ver­sions ultérieures

Pour toutes les ver­sions d’Ex­cel (enreg­istré sur 2003)

Pour toutes les ver­sions d’Ex­cel (enreg­istré sur 2010)

J’e­spère que vos neu­rones sont tou­jours vivants !

N’hésitez pas si vous avez des ques­tions ou des commentaires.

Gaë­tan