Inscription à la newsletter

Tutoriel réalisé avec Excel Microsoft 365

Dans un précédent tutoriel, nous avons vu comment créer une liste déroulante en cascade à 2 niveaux sur Excel et, dans un autre, comment créer une liste déroulante en cascade à 2 niveaux dans un formulaire Excel en VBA. Aujourd’hui, nous allons voir comment créer une liste déroulante en cascade à 3 niveaux sur Excel.

Pour créer des listes déroulantes en cascade à plusieurs niveaux, il existe 2 méthodes :

  • La première, plus simple à mettre en place au départ, peut contenir une base de données plus complexe à maintenir à jour ;
  • La seconde, un peu plus complexe à mettre en place, sera plus simple au niveau de la mise à jour de la base de données.

Votre choix devra se faire en fonction de la taille de votre base de données et de la fréquence de ses mises à jour. Mais, aujourd’hui, nous n’aborderons que la première méthode, la seconde sera abordée une prochaine fois, de même que pour le cas de cascade où la troisième liste doit être filtrée selon ce qui se trouve dans la deuxième, mais aussi la première.

Dans ce tutoriel, nous allons traiter de continents, pays et villes, la sélection d’une ville se faisant dans une liste filtrée par le pays précédemment sélectionné, lui-même filtré par le continent choisi. Bien sûr, l’exemple peut être transposé à d’autres cas comme les différentes régions et départements d’un pays, voire les différents départements d’une entreprise ou ses différentes gammes de produits.

 

Présentation des données utilisées

Dans le fichier que je vais utiliser, nous avons 3 tableaux comportant chacun des continents, des pays et des villes. Les continents se trouvent en colonne A, les pays des colonnes C à G et les villes des colonnes I à AG. Notez que, pour cette méthode, je n’ai pas créé de plage sous forme de tableau via le menu Insertion > Tableau Bouton tableau Excel pour les pays et villes, car je veux pouvoir trier ces dernières par ordre alphabétique donc il faut que les colonnes restent indépendantes. J’ai donc, simplement, utilisé la commande Mettre sous forme de tableau Mettre sous forme de tableau du menu Accueil.

 

Nommage des plages de cellules

Comme pour la liste en cascade à 2 niveaux, vous devez nommer vos plages. Pour les continents, je sélectionne les cellules de la plage A2 à A6 et, dans le menu Formules, je clique sur Définir un nom Définir un nom et j’écris Continents comme Nom avant de cliquer sur OK. Je peux aussi donner un nom directement dans la Zone Nom en sélectionnant les mêmes cellules.

Pour les autres plages, nous allons gagner du temps via le nommage par sélection. Sélectionnez les cellules C1 à G6 et, toujours dans le menu Formules, cliquez sur Depuis sélection Depuis sélection. Ne gardez coché que Ligne du haut et cliquez sur OK.Faites de même pour la plage de cellules I1 à AG6. Dans le Gestionnaire de noms Gestionnaire de noms, vous pouvez constater que les noms ont été générés et que les tirets (-) ont été remplacés par des underscores (_).

 

Création des listes déroulantes en cascade sur Excel

Création de la liste déroulante pour les continents

Dans la feuille Choix, je me place en A1 et, dans le menu Données, je clique sur Validation des données Validation des données Excel. À l’onglet Options, je choisis Autoriser Liste et, dans la Source, j’écris =Continents avant de cliquer sur OK. Ici, c’est une liste déroulante simple comme on l’a déjà vu dans un précédent tutoriel où l’on se base sur une source dynamique. Je peux ensuite utiliser la poignée de recopie pour utiliser cette liste sur plusieurs lignes (jusqu’à A10 par exemple).

 

Création de la liste déroulante pour les pays

Je me place en B1, je clique de nouveau sur Validation des données et je choisis Autoriser Liste. Cette fois-ci, tout comme dans le tutoriel sur la création de listes déroulantes en cascade avec Excel, je saisis :

=INDIRECT(A1)

A1 étant la cellule où se trouve ma première liste de continents dans la feuille Choix. Je clique sur OK, un message apparaît, je clique sur Oui. Ici, aussi, je peux utiliser la poignée de recopie jusqu’à la ligne 10. Attention, dans ce cas, assurez-vous de retirer le signe du dollar ($) devant le 1 de A1 si vous avez cliqué directement sur la cellule lors de la saisie de la formule afin que celle-ci reste relative. Pour en savoir plus sur les cellules relatives, absolues ou mixtes, je vous conseille le Kit du bon démarrage avec les formules Excel.

 

Création de la liste déroulante pour les villes

Enfin, en C1, je réitère la même procédure et, à la place de A1, j’indique B1. Je peux aussi utiliser la poignée de recopie jusqu’à la ligne 10.

 

Cas des éléments de liste comportant des tirets ou des espaces

Comme vous pouvez le remarquer, pour les continents ou les pays qui contiennent des tirets ou des espaces (c’est le cas, dans cet exemple, des États-Unis, de la Nouvelle-Zélande, de la Papouasie‑Nouvelle‑Guinée et du Viêt Nam) je ne peux pas afficher les villes correspondantes, tout simplement parce que, pour Excel, il n’y a pas de villes correspondantes à ces pays, car leurs plages respectives ont été nommées avec des underscores (les tirets et espaces n’étant pas autorisés dans les noms de plage). Il en serait de même avec d’autres caractères spéciaux qui auraient été remplacés des underscores.

Pour remédier à cela, nous allons modifier la formule de la validation des données des villes en imbriquant deux fonctions SUBSTITUE qui permettront de remplacer un ou plusieurs caractères par d’autres. L’une remplacera les tirets et l’autre les espaces. Je modifie donc la formule :

=INDIRECT(B1)

Par :

=INDIRECT(SUBSTITUE(SUBSTITUE(B1;" ";"_");"-";"_"))

Cette fonction remplace les pays saisis avec des tirets ou des espaces dans la liste B1 par des noms de plage avec des underscores. Cela permet donc d’avoir des correspondances entre les éléments sélectionnés et les plages.

Vous pouvez faire de même avec la liste des pays pour anticiper une éventuelle saisie de continent avec des espaces ou caractères spéciaux en remplaçant :

=INDIRECT(A1)

Par :

=INDIRECT(SUBSTITUE(SUBSTITUE(A1;" ";"_");"-";"_"))

Ici, n’oubliez pas que l’élément dans la liste des continents et dans celui des pays doit posséder le même nom, ainsi que le nom de plage défini.

Si vos éléments de liste contiennent d’autres caractères spéciaux, vous pouvez imbriquer d’autres fonctions SUBSTITUE.

 

Cas d’un choix précédent omis

Si l’utilisateur clique sur la liste déroulante des pays alors qu’aucun continent n’est sélectionné ou qu’il clique sur la liste déroulante des villes alors qu’aucun pays n’est sélectionné, rien ne se passe. Cependant, pour une meilleure expérience utilisateur, il est préférable de lui indiquer quel est le problème. Pour cela, je vais imbriquer une fonction SI qui affichera un texte comme unique élément de la liste.

 

Création des éléments de liste déroulante

Avant de modifier la formule, dans une cellule quelconque que je peux ensuite masquer, je saisis Sélectionnez un continent. En restant sur cette cellule, je crée un nouveau nom de plage : Continent_vide. Puis, dans une autre cellule, je saisis Sélectionnez un pays et je nomme cette plage Pays_vide.

 

Modification des formules des listes déroulantes en cascade

Remplacez la formule de la liste des pays :

=INDIRECT(SUBSTITUE(SUBSTITUE(A1;" ";"_");"-";"_"))

Par :

=SI(A1="";Continent_vide;INDIRECT(SUBSTITUE(SUBSTITUE(A1;" ";"_");"-";"_")))

Cela signifie que si rien n’est sélectionné dans la liste des continents, alors on fait référence à la cellule qui contient Sélectionnez un continent, sinon on affichera les pays correspondants comme précédemment.

Sur le même principe, remplacez la formule de la liste des villes :

=INDIRECT(SUBSTITUE(SUBSTITUE(B1;" ";"_");"-";"_"))

Par :

=SI(B1="";Pays_vide;INDIRECT(SUBSTITUE(SUBSTITUE(B1;" ";"_");"-";"_")))

Pour cette dernière liste, vous pouvez même remplacer la formule par :

=SI(OU(B1="";B1=Continent_vide);Pays_vide;INDIRECT(SUBSTITUE(SUBSTITUE(B1;" ";"_");"-";"_")))

Ici, j’imbrique une fonction OU qui permet de conditionner si B1 est vide ou contient le texte Sélectionnez un continent afin de m’éviter d’avoir, de nouveau, une liste déroulante qui semble ne pas fonctionner.

 

Mise à jour des données alimentant les listes

Avec cette méthode, il faudra rajouter le continent, le pays ou la ville dans le bon tableau, puis rajouter les colonnes nécessaires et s’assurer que la plage de données est prise en compte, sinon les listes déroulantes ne comporteront pas ces nouveaux éléments. Nous verrons dans un prochain tutoriel comment placer tous les éléments dans le même tableau, ce qui peut être plus facile à maintenir à jour.

Vous retrouverez le fichier utilisé dans l’Espace Membres accessible aux abonnés à la newsletter.

Inscription à la newsletter