Vous êtes nombreux à demander s’il existe une façon de créer des listes déroulantes dépendantes dans Excel. La réponse est “oui”, mais la technique n’est pas simple à maîtriser. L’article suivant vous explique comment procéder pour créer et maintenir des listes déroulantes dépendantes.
Listes déroulantes : 4 niveaux de dépendance
Supposons un exemple de listes déroulantes dépendantes avec 4 niveaux de dépendance, tel qu’illustré sur l’image ci-bas. La première étape consiste à faire la liste de tous les items qui seront utilisés dans vos listes et de les numéroter. Ensuite, vous devrez inscrire à côté de chacun d’entre eux, le niveau correspondant. Dans l’exemple ci-dessous, le premier niveau est composé des items : Musique, Films et Livres. Ensuite, lorsque l’on sélectionne Musique, on peut choisir entre Rock ou Pop. Donc, Rock et Pop sont des items de deuxième niveau. Lorsque l’on parle de niveau, on parle de la position de la liste dépendante. Les items qui font partie de la liste de niveau 1 ne dépendent d’aucun item, tandis que les items qui font partie de la liste de niveau 2 dépendent des items de la liste de niveau 1 et ainsi de suite. Dans une autre section de votre fichier, voyez également à créer une ligne avec tous les niveaux (tel qu’illustré dans les cellules F2 à J2 dans l’image ci-dessous).
Les parents : La base des listes déroulantes dépendantes
Dans une autre colonne, vous devrez inscrire les parents de chaque item. Par exemple, l’item no.10 (BD) est parent avec l’item no.3 (les livres). En effet, lorsque l’utilisateur sélectionnera l’option Livres (niveau 1 / 1ère liste déroulante), il devra avoir le choix entre Romans, Biographies et BD (niveau 2 / 2ième liste déroulante). De même, lorsque l’utilisateur sélectionnera Musique (niveau 1 / 1ère liste déroulante), il aura le choix entre Rock et Pop (parent 1) et lorsqu’il choisira Pop (niveau 2 / 2ième liste déroulante), il aura le choix entre francophone, anglophone et langue étrangère (parent 5) et lorsqu’il choisira Langues étrangères (niveau 3 / 3ième liste déroulante), il aura le choix entre Espagnol, Italien ou Allemand (niveau 4 / 4ième liste déroulante / parent 16).
Les formules et les fonctionnalités pour mettre en action les listes déroulantes dépendantes
Dans votre fichier, juste en-dessous de votre ligne avec vos niveaux, insérez une ligne avec les choix (position des items choisis dans les listes). Sur cette ligne, vous n’insérerez pas de formules. Vous lierez plutôt des menus déroulants vers ces cellules via un contrôle de formulaire. Pour vous remémorer comment fonctionne un contrôle de formulaire, je vous invite à relire l’article suivant: Excel: Améliorez vos présentations grâce aux contrôles de formulaires. Remarquez sur l’image ci-bas que la 4ième liste déroulante est liée à la cellule J3 et que nous avons limité le nombre d’items dans la liste à 8 éléments. Pour ce qui est de la plage d’entrée de données (input range), nous avons inséré un champs nommé, qui sera vu au point suivant.
Les 4 listes déroulantes sont liées à des champs nommés (Liste 1, Liste 2, Liste 3 et Liste 4). Les formules de ces champs nommés sont illustrées ci-bas. Si vous avez besoin de revoir les notions de la fonction Offset (Decaler), relisez l’article suivant: Excel: Fonction Decaler (Offset). Ces formules sont liées à la zone de cellules allant de G6 à J14. Cette zone de cellules est expliquée plus loin dans cet article.
Cliquez sur l’image pour l’agrandir
En-dessous de votre ligne avec vos choix, insérez une ligne pour les parents avec la formule que vous apercevez sur l’image ci-dessous. Si vous avez besoin de vous remémorer en quoi consiste la fonction Index/Match (Index/Equiv), relisez l’article: Fonction Excel: Index/Equiv (Index/Match). Cette formule vous permettra d’obtenir le numéro de parent correspondant au choix de l’utilisateur. À noter que le premier parent est toujours 0 et n’a donc pas besoin de formule.
En-dessous de la ligne Parent, insérez une ligne Nombre, avec la formule illustrée sur l’image ci-bas. Ceci vous permettra d’obtenir le nombre d’items dans chacune des listes déroulantes.
Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel. |
---|
Finalement, créez une zone de cellules avec les résultats des listes déroulantes dépendantes. Pour ce faire, vous devrez avoir recours à la formule illustrée ci-bas.
Ajouts et modifications aux choix des listes déroulantes dépendantes
Il est possible qu’au fil du temps, vous deviez modifier les options de vos listes ou ajouter des options à celles-ci. Le cas échéant, continuez d’alimenter votre liste d’items de la même façon. Par exemple, dans l’image ci-dessous, nous venons d’ajouter 3 choix de langues étrangères à la musique Rock.
Et voilà! Le travail est fait 🙂..
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour apprendre des façons créatives d’utiliser Excel pour créer des solutions surprenantes et régler des problématiques qui semblent parfois sans solution, suivez la formation Excel – Trucs de pro.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
Ping : Excel : Créer des listes déroulan...
bonjour et merci pour cette explication. cependant, je n’arrive pas à comprendre la notion de “choix”. pouvez vous m’éclairer avec un exemple ? merci
Bonjour,
Le choix correspond simplement au rang de l’item choisi par l’utilisateur dans chacune des listes.
Au plaisir,
Sophie
Bonjour,
Confrontée également à la question, j’avais résolu le problème différemment : je mets les titres de mes listes sur ma première ligne (A1, B1…). Mes listes de choix sont sur la ligne suivante (A2, B2…). J’ai mis toutes mes données dans une feuille cachée, chaque intitulé de colonne correspondant à chaque titre de ma feuille visible. A chaque fois que je change mon choix dans une liste de ma feuille visible, je déclenche un filtre avancé dont la source est : mon titre + mon choix et qui va chercher les données dans la feuille cachée. Je ne sais pas si je suis très claire…
Bref, merci pour vos bons conseils que je suis assidûment depuis la France.
Catherine Beauclercq
Bonjour,
Je sais que la question a deja été posé mais je ne comprend toujours pas.
Est ce vraiment tres important les chiffres que l’on met dans choix ou peut on mettre n’importe quoi?
Car je me retrouve avec 17 niveaux et je ne comprend pas comment les classer par rang
Merci
Bonsoir madame,
je cherche à utiliser une formule matricielle (“ctrl+shift+entrée”) dans la “Source” d’une “Liste” de “Validation de Données” mais un message d’erreur m’indique que “la Source est reconnue comme erronée” (alors que la fonction est valide dans une cellule) !
Savez-vous me dire si cette utilisation est licite ?
D’avance merci
Dominique de Lanty
Bonjour,
Vous ne pouvez pas utiliser ce type de formule dans une liste.
Au plaisir,
Sophie