Comment créer des listes déroulantes en cascade (dépendantes) sur Excel
Dans notre précédent tutoriel, nous avons vu comment mettre en place un menu déroulant afin de permettre à un utilisateur de sélectionner une entrée parmi une liste de choix prédéfinie. Nous allons à présent voir comment créer un second menu déroulant, dont les propositions vont dépendre de la sélection du premier. Une fois de plus, nous n’allons pas avoir recours à l’utilisation du VBA, afin de simplifier au maximum la création de ce menu.
Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier ci-dessous :
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Ce tutoriel est la suite du précédent intitulé : Comment créer une liste déroulante sans VBA sur Excel, dans lequel nous avons vu comment réaliser un menu déroulant sur Excel, en utilisant la validation des données. Si vous ne l'avez encore consulté, je vous invite à le faire avant de continuer en cliquant ici. Je vous conseil également le visionnage de la vidéo.
1. Modification du sens de la liste (transposition)
Jusqu’à présent, dans notre exemple, nous avons utilisé une liste dont les champs sont proposés les uns à la suite des autres, sous la forme d’une table verticale à une seule colonne. Pour rendre notre exemple dynamique, et permettre à notre second menu de proposer des choix en dépendants du premier, nous allons transformer notre base de données sources en un tableau de données à deux dimensions.
Pour cela, nous commençons par sélectionner la liste des pays :
Puis nous allons copier cette mini-base (en cliquant sur le bouton Copier de l’onglet Accueil du ruban, ou encore grâce au raccourci clavier [Ctrl]+[c]) :
Dans la cellule C1, nous allons coller ces données, mais utilisant le collage spécial :
Cela va nous afficher une fenêtre dans la laquelle nous allons pouvoir choisir de Transposer les données copiées.
Derrière ce terme se cache une fonctionnalité très pratique d’Excel qui consiste à inverser les colonnes et les lignes. Ainsi, nos données initialement proposées sous la forme d’une liste verticales vont se retrouver sous la forme d’une liste horizontale !
Pour cela, il suffit de cocher l’option Transposé, puis de valider avec le bouton [OK].
Nos données sont maintenant présentes deux fois dans la feuille de calcul, mais le nom de plage de données auquel se réfère le menu déroulant (« _listePays ») pointe toujours sur les données de la plage A1:A6.
Nous allons donc nous rendre dans le gestionnaire de noms afin de mettre à jour ce dernier (Menu Formules > Gestionnaire de noms) :
Puis, nous allons modifier la référence de la plage « _listePays ».
Pour cela, nous cliquons sur la flèche présente dans le champ « Fait référence à : » :
Ce qui nous permet de sélectionner la nouvelle plage de données que nous venons d’insérer :
Nous validons en appuyant sur la touche [Entrée] du clavier, puis sur le bouton en forme de coche verte :
Enfin, cela étant fait, nous pouvons fermer la fenêtre du gestionnaire des noms.
À présent, comme vous pouvez le constater, le nom « _listePays » appel la liste des noms de pays verticale :
Nous pouvons supprimer les deux premières colonnes, devenues inutiles :
Les noms de pays vont maintenant nous servir d’en-tête de colonnes, nous allons saisir nos villes dans les cellules du dessous :
Pour notre exemple, nous avons saisi trois villes pour chaque pays, mais évidemment, nous pourrions en saisir une infinité.
2. Récupération des noms de villes
Maintenant nous allons ajouter une nouvelle liste dans laquelle nous allons insérer une formule afin de récupérer les villes correspondantes au pays sélectionné.
Pour commencer, nous allons récupérer le nom du pays sélectionné dans la cellule H1 (rappelez-vous dans le premier tutoriel, nous l’avions saisi dans la cellule B1 de la première feuille de calcul Feuil1) :
=Feuil1!B1
De cette manière, nous allons maintenant pouvoir récupérer les villes correspondantes, grâce à la formule imbriquée INDEX(EQUIV()) (qui est en fait un ensemble de deux formules imbriquées, qui fonctionnent l’une avec l’autre) :
Dans la cellule H2, nous allons donc saisir la formule suivante :
=INDEX(A2:F2;EQUIV($H$1;_listePays;0))
Pour synthétiser l’utilisation de ces deux formules : La formule INDEX() permet de récupérer le n-ième élément de la plage saisie en premier paramètre (donc la plage A2:F2). Ce n-ième élément étant donné par la formule EQUIV() qui recherche à quelle position se trouve un élément ($H$1) dans une plage de cellules (_listePays) :
- A2:F2 : il s’agit de la plage de données correspondant à la première ligne des villes. La référence à cette plage de données est de type relative, afin que lorsque nous allons juste après étendre la formule au cellules du dessous, Excel passe à la seconde ligne des villes, puis la troisième, …
- $H$1 : il s’agit de la cellule dans laquelle nous venons de récupérer le nom du pays sélectionné. Cette fois-ci nous utilisons une référence absolue, afin qu’une fois étendue, toutes les cellules pointent sur cette même cellule,
- _listePays : il s’agit de la liste des pays que nous avions inséré dans le premier tutoriel, et que nous venons de passer sur un affichage horizontal.
- 0 : nous précisons à Excel que nous souhaitons rechercher le pays exact, si ce dernier n’existe pas dans la liste, ou s’il est mal orthographié, alors Excel va nous renvoyer une erreur de type #N/A
Une fois cette formule correctement saisie, nous pouvons la valider, en appuyant sur la touche [Entrée] du clavier.
Enfin, nous pouvons l’étendre aux deux lignes situées juste en dessous en utilisant la poignée (le petit carré situé en bas à droite de la cellule H2, lorsque celle-ci est sélectionnée), que nous faisons glisser :
À présent que nous disposons d’une nouvelle liste avec les noms des villes du pays sélectionné, nous allons pouvoir lui donner un nom en le saisissant dans la zone prévue à cet effet (en haut, à gauche de la barre des formules) :
Nous choisissons par exemple d’appeler cette liste : « _listeVilles ».
3. Création du menu-déroulant dépendant
Il ne nous reste plus qu’à insérer une validation des données dans la cellule B2 de la première feuille (voir le tutoriel sur la création d’un menu déroulant) :
- Sélectionner la cellule B2 :
- Dans l’onglet Accueil du ruban, choisir Validation des données :
- Dans le menu Autoriser, nous allons choisir l’option Liste, puis dans le champ Source, nous choisissons le nom que nous venons de donner à notre liste « =_listeVilles » :
- Enfin, nous validons la création du menu déroulant en appuyant sur le bouton [OK]
