Dans un précédent article, vous avez appris comment créer une liste déroulante simple sur Excel. Le problème, c’est qu’elle ne se met pas à jour automatiquement si vous ajoutez une nouvelle entrée.
Je vais vous présenter différentes manières de rendre votre liste déroulante dynamique.
La base en tableau structuré
C’est la méthode la plus simple à mettre en place.
Je vous en ai parlé lors de la création d’un tableau structuré, le tableau s’adapte au fur et à mesure de vos saisies.
Pour que ce soit plus clair, on va utiliser un exemple.
Je gère une entreprise et je crée un fichier Excel pour recenser mes employés. Dans ce fichier, j’ai une feuille Bases qui va me servir pour créer les listes déroulantes et une feuille Employés pour lister ces derniers.
- Je sélectionne mes entrées pour la liste déroulante Service dans la feuille Bases soit B1 à B6
- Je vais dans Accueil > Mettre sous forme de tableau puis je choisis celle qui me convient
- Dans la nouvelle boîte de dialogue, je coche Mon tableau comporte des en-têtes
- Je valide avec OK.
Ensuite, je renomme le tableau structuré en TabService
Si vous vous souvenez, Excel donne un nom à chaque zone d’un tableau structuré. Les données d’une colonne sont nommées de la manière suivante : NomTableau[NomColonne]
Dans notre exemple, cela donne TabService[Service], et ce quelque soit son nombre d’entrées (lignes).
Retournons dans la feuille Employés pour créer nos listes déroulantes.
Je vais aussi transformer les données de cette feuille en tableau structuré.
Je sélectionne la colonne Service puis me rend dans Données > Validation de données
Pour la source, nous serions tenté juste de mettre TabService[Service] mais Excel vous dira qu’il ne peut pas récupérer les données et mettra un message d’erreur
=INDIRECT("donnees")
Pour notre exemple, on va donc saisir comme source =INDIRECT(« TabService[Service] »)
Et magie, ça marche.
Actuellement, j’ai 5 entrées dans ma liste déroulante. Dans la feuille Bases, je vais ajouter un nouveau service : Juridique.
Vous pouvez constater qu’Excel l’a incorporé dans notre tableau structuré.
Si vous retournez dans la feuille Employés, ouvrez une liste déroulante : la nouvelle entrée apparaît sans qu’on aie besoin d’aller modifier la source dans Validation de données. 
Pourquoi ne pas avoir sélectionné les 2 colonnes pour créer notre tableau structuré dans la feuille Bases ?
En fait, cela va créer des lignes vides. Je m’explique en repartant de zéro.
- Je crée mon tableau structuré en prenant les colonnes A et B de ma feuille Bases.
- Je renomme mon tableau TablBases
- Vous pouvez voir qu’il y a 3 lignes vides dans ma colonne Civilité.
Si je crée une liste déroulante pour les civilités, Excel va rajouter ces 3 lignes vides vu qu’on prend toute la colonne du tableau structuré comme source (les 3 entrées + les 3 vides).
C’est pour cela que la meilleure manière de faire est de créer un tableau structuré pour chaque colonne de notre feuille Bases pour éviter ces lignes vides.
Les fonctions DECALER et NBVAL
Si vous ne souhaitez pas utiliser les tableaux structurés, il existe une autre méthode : l’imbrication des fonctions DECALER et NBVAL dans une formule de calcul.
Fonction DECALER
La fonction DECALER permet de créer une liste de données en fonction de la taille d’une plage de données. Notre objectif est donc de calculer le nombre d’entrées pour notre liste déroulante.
Elle s’écrit :
=DECALER(réf;lignes;colonnes;[hauteur];[largeur])
.
Étudions là argument par argument :
- réf : il s’agit du point de départ.
- lignes : il s’agit du nombre de lignes pour décaler notre point de départ (chiffre positif pour descendre, négatif pour monter)
- colonnes : il s’agit du nombre de colonnes pour décaler notre point de départ (chiffre positif pour aller à droite, négatif pour aller à gauche)
- [hauteur] : nombre de lignes jusqu’à notre point d’arrivée
- [largeur] : nombre de colonnes pour arriver à notre point d’arrivée.
Notre point de départ est la première entrée de notre liste déroulante.
Pour créer une liste déroulante dynamique, nous n’avons pas besoin des argument lignes et colonnes.
Ensuite, on va indiquer dans l’argument [hauteur] le nombre d’entrées pour notre liste.
Enfin, nous n’avons pas besoin de l’argument [largeur] car nos entrées se tiennent sur une même colonne.
Voyons tout ça avec un exemple avec notre fameux fichier pour les employés.
Dans notre feuille Bases, on va créer une liste déroulante pour les Services de l’entreprise.
J’ai actuellement des entrées de B2 à B5 soient 4 entrées.
La source de ma liste déroulante sera donc : =DECALER(Bases!$B$2;0;0;4)
Un problème se pose maintenant. On a créé une liste déroulante mais aura toujours que 4 entrées.
Comment la rendre dynamique ?
Hé bien, grâce à la fonction NBVAL.
Fonction NBVAL
La fonction NBVAL permet de compter le nombre de cellules remplies.
Sa syntaxe est :
=NBVAL(valeur1;valeur2;...)
Les arguments valeurs correspondent aux plages de données à décompter.
Pour notre exemple, on va compter le nombre de cellules dans la colonne B.
Pour cela, nous devrions écrire =NBVAL(Bases!B:B)
Or, nous avons un en-tête. Il faut donc enlever une ligne ce qui donne =NBVAL(Bases!B:B)-1
Nous avons donc dorénavant un argument qui sera dynamique en fonction du nombre d’entrées saisies. On peut donc remplacer 4 par NBVAL(B:B)-1 dans la fonction DECALER.
Si j’ajoute une nouvelle entrée à ma base, Juridique par exemple, ma liste se met à jour automatiquement.
Création de la liste déroulante dynamique
Maintenant que nous avons la bonne formule de calcul, créons notre liste déroulante dynamique :
- Sélectionnez les cellules qui auront la liste déroulante
- Cliquez sur Données > Validation de données
- Autoriser une liste
- Pour la source, saisissez =DECALER(Bases!$B$2;0;0;NBVAL(Bases!B:B)-1)
- Faites OK
Dorénavant, vos listes déroulantes sont dynamiques.
Utiliser les cellules propagées
Lorsque vous utilisez une fonction matricielle dynamique comme DECALER ci-dessus (ou la fonction UNIQUE), Excel renvoie le résultat sur autant de cellules que nécessaire et non pas une seule cellule.
C’est ce qu’on appelle une cellule propagée. Certains l’appellent aussi références/cellules étendues.
Dans notre exemple, la cellule H3 est une cellule propagée.
- Je sélectionne les cellules qui auront une liste déroulante
- Je clique sur Données > Validation de données
- J’autorise une liste
- Comme source, je saisis =H3#
- Je valide avec OK.
En fait, le # va récupérer toutes les cellules qui contiennent des données (H3 à H8).
Votre tableau possède dorénavant des listes déroulantes dynamiques grâce à cette 3e méthode.
Ping : Excel : fonction UNIQUE (liste sans doublons) - May6
Ping : Excel : Filtrer ses données dynamiquement avec la fonction FILTRE - May6