Comment créer une liste déroulante à deux niveaux sur Excel : La méthode simple et visuelle

Dans ce tutoriel, je vais vous montrer comment créer une validation de données à deux niveaux qui affiche visuellement toute la hiérarchie de vos données (catégories ET éléments) tout en permettant uniquement la sélection des éléments finaux.

Cette technique va transformer vos listes déroulantes classiques en outils professionnels et intuitifs, sans nécessiter de macros complexes ni de multiples étapes de sélection.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons l'inventaire d'un supermarché.

Ce tableau contient plusieurs rayons du magasin, et pour chaque rayon, nous avons une liste des produits disponibles que nos équipes doivent pouvoir sélectionner rapidement et sans erreur.

Dans ce tableau, les informations sont réparties sur deux colonnes où le rayon se répète pour chaque produit, est la base de notre système.

Le problème que nous rencontrons habituellement avec ce type de données est que les validations classiques nous obligent à créer deux listes déroulantes successives. L'utilisateur doit d'abord choisir le rayon, puis dans une seconde liste, choisir le produit.

Cette approche est fastidieuse et ralentit considérablement la saisie, surtout quand nous connaissons déjà le produit que nous cherchons.

Notre objectif est de créer une seule liste déroulante qui affiche tous les rayons et tous les produits dans une structure hiérarchique visuelle, mais qui n'accepte que la sélection des produits.

Ainsi, les utilisateurs voient immédiatement dans quel rayon se trouve chaque article, mais ils ne peuvent valider que le produit final, ce qui évite les erreurs de saisie tout en conservant le contexte visuel.​

2. Création du tableau croisé dynamique structuré

Nous commençons par sélectionner l'ensemble de notre tableau de données en cliquant sur n'importe quelle cellule du tableau.

Ensuite, nous allons le convertir en tableau de données, ce qui va nous simplifier la suite des opérations, et en plus va rendre notre liste dynamique.

Si nous ajoutons de nouveaux éléments par la suite, ces derniers seront bien repris.

Pour effectuer cette opération, nous appuyons sur les touches [Ctrl]+[L].

Excel affiche alors une boîte de dialogue pour que nous puissions contrôler la plage des cellules sélectionnées automatiquement. Nous vérifions également que l’option « Mon tableau comporte des en-têtes » est bien cochée, puis nous validons en appuyons sur « OK » :

Ensuite, nous nous rendons dans le menu « Insertion » en haut de la fenêtre Excel, puis nous cliquons sur le bouton « Tableau croisé dynamique » situé dans le groupe Tableaux. Excel nous propose alors une boîte de dialogue nous demandant où nous souhaitons créer ce tableau croisé dynamique. Nous sélectionnons l'option « Nouvelle feuille de calcul » car cela nous permettra de travailler sur une zone propre sans risquer d'écraser nos données existantes.​

Nous cliquons sur « OK » et Excel crée instantanément une nouvelle feuille avec un tableau croisé dynamique vide sur la gauche et le volet des champs sur la droite. Ce volet est notre centre de contrôle pour construire la structure exacte dont nous avons besoin.​

Dans le volet des champs situé à droite, nous voyons apparaître nos deux champs : « Rayon » et « Produit ». Nous allons maintenant construire notre hiérarchie en faisant glisser ces champs :

• Nous cliquons sur le champ « Rayon » et nous le faisons glisser vers la zone « Lignes » située en bas du volet.
• Ensuite, nous cliquons sur le champ « Produit » et nous le faisons également glisser dans la zone « Lignes », mais cette fois en le plaçant SOUS le champ « Rayon », pour indiquer à Excel que les produits sont des sous-éléments des rayons. Nous voyons maintenant une structure arborescente avec des petits symboles « + » et « - » permettant de déplier ou replier chaque rayon.​

Cependant, la disposition actuelle n'est pas celle que nous recherchons. Par défaut, Excel crée ce qu'on appelle une « disposition compacte » où tout est regroupé dans une seule colonne.

Pour notre technique, nous avons besoin que chaque niveau de hiérarchie soit dans sa propre colonne distincte.

Dans l’onglet « Création de tableau croisé dynamique », nous cliquons sur « Disposition du rapport » dans le groupe Disposition et nous sélectionnons « Afficher sous forme de plan », ce qui permet d’obtenir une colonne pour les rayons et une colonne séparée pour les produits​

Il nous reste plus qu’à cliquer sur « Totaux généraux » et sélectionner « Désactivé pour les lignes et les colonnes ». Ces totaux ne nous servent à rien dans notre contexte et pourraient perturber notre formule finale.

3. Construction de la formule de hiérarchisation visuelle

Maintenant que notre tableau croisé dynamique est prêt, nous allons créer la formule qui va fusionner intelligemment nos deux colonnes en créant une indentation visuelle pour les produits.

La logique de notre formule est la suivante : si la cellule de la colonne « Rayon » contient quelque chose, nous affichons simplement le nom du rayon tel quel. Si par contre la cellule de la colonne « Rayon » est vide, cela signifie que nous sommes sur une ligne de produit, et dans ce cas nous voulons afficher le produit avec une indentation visuelle en le faisant précéder de plusieurs espaces.

Nous saisissons donc la formule suivante dans la cellule D4 :

 =SI(A4<>"";A4;SI(B4<>"";"      "&B4;"")) 

Dans cette formule, le premier SI() teste si la cellule A4 (qui contient potentiellement un rayon) n'est pas vide grâce à la condition « A4<>"" ». Si cette condition est vraie, c'est-à-dire si A4 contient bien un rayon, alors la formule retourne simplement le contenu de A4, soit le nom du rayon sans modification.

Si par contre A4 est vide, nous passons à la deuxième partie de la formule : SI(B4<>"";" "&B4;"").

Cette deuxième fonction SI() vérifie si B4 (qui contient potentiellement un produit) n'est pas vide.

Si B4 contient effectivement un produit, la formule retourne cinq espaces suivis du symbole & qui permet de concaténer (coller ensemble) ces espaces avec le nom du produit.

Ces cinq espaces créent l'indentation visuelle qui donnera l'effet de hiérarchie dans notre liste déroulante finale.

Enfin, si ni A4 ni B4 ne contiennent de données, la formule retourne simplement une chaîne vide "", ce qui laisse la cellule vide.

Nous validons cette formule en appuyant sur [Entrée], et nous observons le résultat dans la cellule D4.

Selon que nous soyons sur une ligne de rayon ou de produit, nous voyons soit le nom du rayon, soit le nom du produit précédé de cinq espaces.

Maintenant, nous devons recopier cette formule vers le bas pour couvrir toutes nos données actuelles et anticiper les ajouts futurs. Nous cliquons sur la cellule D4 pour la sélectionner, puis nous positionnons notre souris sur le petit carré vert situé en bas à droite de la cellule (qu'on appelle la « poignée de recopie »).

Notre curseur se transforme en une petite croix noire. Nous cliquons et maintenons le bouton de la souris enfoncé, puis nous faisons glisser vers le bas jusqu'à la ligne 30, ce qui nous donne une marge pour de nouveaux produits dans notre tableau source.

Notre hiérarchie visuelle est parfaitement créée.

4. Configuration de la validation de données avec l'opérateur

Nous allons maintenant créer notre validation de données dans la cellule où nous voulons que nos utilisateurs puissent sélectionner un produit.

Nous cliquons sur cette cellule pour la sélectionner, puis nous nous rendons dans le menu « Données » et nous cliquons sur « Validation des données » dans le groupe Outils de données. Une boîte de dialogue s'ouvre avec plusieurs options de validation.​

Dans l'onglet « Options », nous cliquons sur le menu déroulant « Autoriser » et nous sélectionnons « Liste ». Ce choix indique à Excel que nous voulons créer une liste déroulante où l'utilisateur ne pourra choisir que parmi les valeurs que nous aurons définies.

Vient maintenant l'étape la plus importante : renseigner la source de notre liste.

Dans le champ « Source », nous allons sélectionner la plage des cellules contenant notre hiérarchie, en incluant la marge laissée vide:

L'avantage énorme de cette approche est qu'elle est complètement dynamique.

Si demain nous ajoutons un nouveau produit dans notre tableau source, que nous actualisons le tableau croisé dynamique, la nouvelle référence sera automatiquement ajoutée dans la validation de données.

Nous validons en cliquant sur le bouton « OK ».

Notre cellule affiche maintenant une petite flèche à droite, signe qu'une liste déroulante est active.

La liste s'ouvre et nous y retrouvons tous nos rayons et produits avec l'indentation visuelle : « Boissons » aligné à gauche, puis en dessous avec un décalage « Coca-Cola », « Eau minérale », etc.

Nous pouvons sélectionner n'importe quel élément de la liste. Le système fonctionne !

Cependant, nous remarquons un problème : rien n'empêche l'utilisateur de sélectionner un rayon (par exemple « Boissons ») au lieu d'un produit.

Nous allons maintenant ajouter un contrôle visuel pour signaler cette erreur.

5. Ajout d'un contrôle visuel

Pour guider notre utilisateur et lui signaler s'il a correctement sélectionné un produit ou s'il a par erreur sélectionné un rayon, nous allons créer une formule de vérification accompagnée d'une mise en forme conditionnelle.

Nous nous positionnons dans la cellule juste à côté de notre validation.

Nous allons créer une formule qui vérifie si les cinq premiers caractères de la cellule F9 sont des espaces.

Si c'est le cas, cela signifie qu'un produit a été correctement sélectionné.

Sinon, c'est qu'un rayon a été sélectionné, ce qui n'est pas valide.

Nous saisissons la formule suivante :

 =SI(GAUCHE(F9;5)="    ";"V";"X Sélectionnez un produit")  

La fonction GAUCHE() extrait les cinq premiers caractères de F9. Nous les comparons avec cinq espaces (les mêmes que nous avions mis dans notre formule de hiérarchisation).

Si ces cinq caractères sont bien cinq espaces, nous affichons le symbole V pour valider la saisie. Sinon, nous affichons « X Sélectionnez un produit » pour alerter l'utilisateur.

Nous validons avec [Entrée] afin de tester le résultat.

Si nous sélectionnons un produit dans F9, G9 affiche V.

Si nous sélectionnons un rayon, G9 affiche notre message d'erreur.