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

brain1
Bon­jour à tous,

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

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 tech­niques.

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.

liste_villes 1

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

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 cel­lule.

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.

decaler 1

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 cel­lule.

Ç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.

deacelr_dynamiqe 1

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

validation 1

- 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 don­né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 com­men­taires.

Gaë­tan

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel
Pour marque-pages : permalien.

5 réactions à Exploser vos neurones: création de listes et noms dynamiques

  1. LAKHLIFI a écrit:

    mer­ci infin­i­ment

  2. SOGH3 a écrit:

    Atten­tion, les listes sous forme de tableau que vous citez en début d’ar­ti­cle sont effec­tive­ment très pra­tique => Je les utilise régulière­ment pour mes tableaux. Un incon­vénient toute­fois, elles ne fonc­tion­nent pas si vous partagez votre tableau Excel avec plusieurs per­son­nes. Dans ce cas vous êtes obligé de créer des noms dynamiques avec la fonc­tion Decaler.

  3. Gaetan Mourmant a écrit:

    Mer­ci pour la pré­ci­sion, mais vous voulez dire quoi par plusieurs per­son­nes? Si vous partagez avec des gens qui ont 2003, oui, ca pose prob­lème.
    Gae­tan

  4. GROSCLAUDE Bernard a écrit:

    Pour moi c’est enfin quelque chose que j’ai longtemps cher­ché à faire mais bien sur sans résul­tat. Donc bra­vo Gae­tan d’en avoir mis + d’un comme moi sur la piste. Par con­tre kje pense qu’il aurait été intéres­sant de pour­suiv­re 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 pass­er de met­tre cette liste en ordre alpha. Alors com­ment faire pour que cette liste soit ensuite triée (sans macro sup­plé­men­taire).
    A bien­tôt

  5. Gaetan Mourmant a écrit:

    Bon­jour Bernard,

    Excel­lente ques­tion.

    En VBA, c’est très rapi­de à faire, notam­ment avec une procé­dure événe­men­tielle.

    Sinon, ca doit être pos­si­ble sans VBA en ajoutant une colonne à droite de la pre­mière et en util­isant une for­mule qui tri­erait cette colonne.

    Je vais en faire un prochain post.

    A bien­tôt.

    Gae­tan

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.