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

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 :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.