Matricielles

Menu déroulant dépendant

Temps de lecture 3 minutes

Cet article va vous expliquer comment créer des menus déroulants dépendant les uns par rapport aux autres à partir d'un tableau sans extraction à réaliser au préalable 😉

Tableau avec des données hiérarchisées

Nous allons partir d'un tableau où les données sont rangées de façon hiérarchisées. Il n'est pas nécessaire que les données soient triées.

Tableau avec des données hiérarchisées

Et nous allons utiliser ces informations pour construire 2 menus déroulants dépendants l'un de l'autre. Par exemple, dans l'image suivante, seules les sous-catégories en relation avec les Boissons s'affiche dans le menu déroulant

Menu Deroulant Dependant

Pour réaliser ce travail, nous allons utiliser les fonctions matricielles dynamiques qui sont présentes dans Microsoft 365 ou Excel Online.

Création du premier menu déroulant

Pour le premier menu déroulant, celui qui correspond au niveau hiérarchique le plus élevé, nous allons récupérer les valeurs uniques de la colonne. Pour cela, nous allons utiliser la fonction UNIQUE.

=UNIQUE($A$2:$A$57)

Fonction UNIQUE pour récupérer les valeurs uniques du premier niveau hiérarchique

Ensuite, nous allons créer un menu déroulant en activant le menu Données > Validation de données.

Choisissez l'option Liste et écrivez la fonction suivante

=$E$2#

Le signe # correspond à la référence étendue de la formule matricielle.

La formule du menu déroulant utilise les références étendues

Avec cette astuce, vous avez créé le menu déroulant à partir de toutes les valeurs contenues dans votre tableau initial.

Premier menu déroulant à partir de la fonction UNIQUE

Créer le menu déroulant dépendant

Maintenant, nous allons construire une nouvelle formule dans une seconde colonne. Cette fois-ci, nous allons utiliser la fonction FILTRE qui va utiliser pour voir utiliser le résultat du premier menu déroulant.

Pour écrire la formule, nous allons

  • Indiquer la colonne qui contient les données à retourner
  • Indiquer la colonne sur laquelle effectuer le filtrage
  • le test à réaliser (ici =)
  • la cellule contenant la valeur du test (ici la cellule I3)

=FILTRE(B2:B56;A2:A56=I3;"")

La formule FILTRE permet dextraire les données sur un critère

Bien sur, nous n'avons besoin de ne conserver que les valeurs uniques. Et de nouveau, nous allons utiliser la fonction UNIQUE

=UNIQUE(FILTRE(B2:B56;A2:A56=I3;""))

Et pour rendre les choses parfaite, on peut aussi trier les données dynamiquement

=TRIER(UNIQUE(FILTRE(B2:B56;A2:A56=I3;"")))

Fonction utilisante 3 fonctions matricielles UNIQUE TRIER et FILTRE

Et comme nous l'avons fait pour le premier menu déroulant, nous allons de nouveau nous servir des références étendues pour concevoir le second menu déroulant avec le résultat de la seconde formule

=$F$2#

Et avec cette nouvelle fonction, nous avons créé un menu déroulant dépendant du premier résultat

Menu déroulant dynamique

Related posts

RECHERCHEV retourne plusieurs lignes

Frédéric LE GUEN

Créer un calendrier par semaine

Frédéric LE GUEN

Extraire toutes les lettres d’un texte

Frédéric LE GUEN

6 commentaires

Bibou 03/12/2020 at 11:55

Bonjour,

Merci beaucoup pour ces explications très détaillée.
Petite concernant concernant le résultat de la formule Filtre, peut-on afficher le résultat sur une seule ligne plutôt qu'en colonne?
J'ai besoin de dupliquer la formule sur plusieurs lignes et du coup l'affichage du résultat de la fonction Filtre étant en colonne, je me retrouve bloqué dès que le résultat comporte plusieurs résultats.

Merci par avance de votre aide
Jérôme

Répondre
Frédéric LE GUEN 03/12/2020 at 12:36

Il faut utiliser la fonction TRANSPOSE pour inverser le résultat. =TRANSPOSE(FILTRE ...))

Répondre
Marina 19/01/2021 at 20:23

Bonjour, merci pour vos explications très claires.
Une petite question sur laquelle je bloque: est-il possible de construire une fonction filtre applicable sur plusieurs cellules de selection.
Pour être plus explicite: dans votre exemple, votre condition2 est toujours basée sur la valeur en I3. J'aimerais pouvoir utiliser le même filtre sur I4, I5 etc. J'ai un tableau à 150 lignes et si je pouvais m'éviter de créer 150 fois la formule ça m'aiderait beaucoup.
Ou alors, la fonction Filtre n'est peut être pas celle à utiliser pour mon objectif.
Au cas où vous ayez une autre idée de formule: Je chercher pour chaque ligne de mon tableau (150 lignes minimum) à selectionner dans un menu déroulant mon "Fournisseurs" puis en fonction du fournisseurs à sélectionner dans un menu déroulant le "produit".
Merci de votre aide.
PS: je sais que le sujet est excel mais je suis obligée d'utiliser Google sheet au boulot. J'espère que vous accepterez tout de même de m'aider.

Répondre
Frédéric LE GUEN 21/01/2021 at 15:22

Effectivement, c'est une excellente question et ce n'est pas du tout la fonction FILTRE qu'il faut utiliser. La fonction FILTRE n'est pas optimiser pour faire des recherches multiples. Il est préférable dans ce cas de figure d'utiliser la fonction RECHERCHEX car on peut sélectionner plusieurs colonnes dans le 3e paramètre

Répondre
Joel TANJAKA 20/10/2020 at 12:36

Bonjour,

Tous mes remerciements sur vos articles qui sont très enrichissants.
J'aimerai vous demander comment créer des menus déroulants dépendants avec la version MS Excel 2016.
J'ai déjà utilisé "Liste" dans Validation de données mais ça ne peut pas produire une liste dépendante.
J'ai essayé aussi "Personnalisé" dans Validation de données mais je n'y arrive pas.

Je vous remercie pour votre réponse.
Cordialement.

Répondre
Frédéric LE GUEN 20/10/2020 at 14:50

Non, seules les versions de Microsoft 365 et Excel Online possèdent les fonctions matricielles qui permettent de résoudre ce problème

Répondre

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.