Comment créer des listes déroulantes en cascade (dépendantes) sur Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - Comment créer des listes déroulantes en cascade - 01

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]) :

Excel formation - Comment créer des listes déroulantes en cascade - 02

Dans la cellule C1, nous allons coller ces données, mais utilisant le collage spécial :

Excel formation - Comment créer des listes déroulantes en cascade - 03

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 !

Excel formation - Comment créer des listes déroulantes en cascade - 04

Pour cela, il suffit de cocher l’option Transposé, puis de valider avec le bouton [OK].

Excel formation - Comment créer des listes déroulantes en cascade - 05

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

Excel formation - Comment créer des listes déroulantes en cascade - 06

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 à : » :

Excel formation - Comment créer des listes déroulantes en cascade - 07

Ce qui nous permet de sélectionner la nouvelle plage de données que nous venons d’insérer :

Excel formation - Comment créer des listes déroulantes en cascade - 08

Nous validons en appuyant sur la touche [Entrée] du clavier, puis sur le bouton en forme de coche verte :

Excel formation - Comment créer des listes déroulantes en cascade - 09

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 :

Excel formation - Comment créer des listes déroulantes en cascade - 10

Nous pouvons supprimer les deux premières colonnes, devenues inutiles :

Excel formation - Comment créer des listes déroulantes en cascade - 11

Les noms de pays vont maintenant nous servir d’en-tête de colonnes, nous allons saisir nos villes dans les cellules du dessous :

Excel formation - Comment créer des listes déroulantes en cascade - 12

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

Excel formation - Comment créer des listes déroulantes en cascade - 13

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

Excel formation - Comment créer des listes déroulantes en cascade - 14

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.

Excel formation - Comment créer des listes déroulantes en cascade - 15

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 :

Excel formation - Comment créer des listes déroulantes en cascade - 16

Excel formation - Comment créer des listes déroulantes en cascade - 17

À 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) :

Excel formation - Comment créer des listes déroulantes en cascade - 18

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 :

Excel formation - Comment créer des listes déroulantes en cascade - 19

  • Dans l’onglet Accueil du ruban, choisir Validation des données :

Excel formation - Comment créer des listes déroulantes en cascade - 20

  • 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 » :

Excel formation - Comment créer des listes déroulantes en cascade - 21

  • Enfin, nous validons la création du menu déroulant en appuyant sur le bouton [OK]

Excel formation - Comment créer des listes déroulantes en cascade - 22

Excel formation - Comment créer des listes déroulantes en cascade - 23

 



Articles qui pourraient vous intéresser

Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?
Comment créer un graphique dynamique en quelques minutes sur Excel sans VBA ni TCD ?
Comment créer des feux de signalisation tricolores dynamiques dans Excel sans VBA ?
Comment créer un graphique englobant 2 en 1 sur Excel ?
Évitez ces erreurs fatales dans vos graphiques Excel !
Comment créer un graphique jauge (compteur de vitesse) interactif sous Excel en quelques minutes ?
Le secret d'un cumul instantané de vos données dans Excel !
Comment insérer des commentaires automatiques et intelligents dans Excel !