Excel: Créer des listes déroulantes dépendantes

Publié le 05 août 2013
par Sophie Marchand M.Sc., CPA, CGA, MVP

Excel: Créer des listes déroulantes dépendantes

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

Listes dépendantes - Niveaux

.

Les parents

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

Listes dépendantes - Parents

.

Les formules et les fonctionnalités

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.

Listes dépendantes - Choix

.

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

Listes dépendantes - Champs nommés

.

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.

Listes dépendantes - Parents 2

.

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.

Listes dépendantes - Nombre

.

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.

Listes dépendantes - zone

.

Ajouts et modifications aux choix des listes

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.

Listes dépendantes - Ajouts

.

Et voilà! Le travail est fait 🙂..

.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

7 réflexions sur “Excel: Créer des listes déroulantes dépendantes”

  1. Ping : Excel : Créer des listes déroulan...

  2. bonjour et merci pour cette explication. cependant, je n’arrive pas à comprendre la notion de “choix”. pouvez vous m’éclairer avec un exemple ? merci

    1. Bonjour,

      Le choix correspond simplement au rang de l’item choisi par l’utilisateur dans chacune des listes.

      Au plaisir,

      Sophie

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

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

  5. Dominique de Lanty

    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

Laisser un commentaire

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

Scroll to Top