Regarder la vidéo - Création d'une liste déroulante dépendante dans Excel
Une liste déroulante Excel est une fonctionnalité utile lorsque vous créez des formulaires de saisie de données ou des tableaux de bord Excel.
Il affiche une liste d'éléments sous forme de liste déroulante dans une cellule et l'utilisateur peut effectuer une sélection dans la liste déroulante. Cela peut être utile lorsque vous avez une liste de noms, de produits ou de régions que vous devez souvent saisir dans un ensemble de cellules.
Voici un exemple de liste déroulante Excel :
Dans l'exemple ci-dessus, j'ai utilisé les éléments de A2:A6 pour créer une liste déroulante dans C3.
Lis: Voici un guide détaillé sur la façon de créer une liste déroulante Excel.
Parfois, cependant, vous souhaiterez peut-être utiliser plusieurs listes déroulantes dans Excel de sorte que les éléments disponibles dans une deuxième liste déroulante dépendent de la sélection effectuée dans la première liste déroulante.
Celles-ci sont appelées listes déroulantes dépendantes dans Excel.
Vous trouverez ci-dessous un exemple de ce que j'entends par une liste déroulante dépendante dans Excel :
Vous pouvez voir que les options de la liste déroulante 2 dépendent de la sélection effectuée dans la liste déroulante 1. Si je sélectionne "Fruits" dans la liste déroulante 1, le nom des fruits s'affiche, mais si je sélectionne Légumes dans la liste déroulante 1, je on me montre les noms de légumes dans le menu déroulant 2.
C'est ce qu'on appelle une liste déroulante conditionnelle ou dépendante dans Excel.
Création d'une liste déroulante dépendante dans Excel
Voici les étapes pour créer une liste déroulante dépendante dans Excel :
- Sélectionnez la cellule où vous voulez la première liste déroulante (principale).
- Allez dans Données -> Validation des données. Cela ouvrira la boîte de dialogue de validation des données.
- Dans la boîte de dialogue de validation des données, dans l'onglet Paramètres, sélectionnez Liste.
- Dans le champ Source, spécifiez la plage qui contient les éléments qui doivent être affichés dans la première liste déroulante.
- Cliquez sur OK. Cela créera le menu déroulant 1.
- Sélectionnez l'ensemble des données (A1:B6 dans cet exemple).
- Allez dans Formules -> Noms définis -> Créer à partir de la sélection (ou vous pouvez utiliser le raccourci clavier Ctrl + Maj + F3).
- Dans la boîte de dialogue « Créer un nom à partir de la sélection », cochez l'option Ligne du haut et décochez toutes les autres. Cela crée 2 plages de noms (« Fruits » et « Légumes »). La plage de fruits nommés fait référence à tous les fruits de la liste et la plage de légumes nommés fait référence à tous les légumes de la liste.
- Cliquez sur OK.
- Sélectionnez la cellule dans laquelle vous voulez la liste déroulante Dépendante/Conditionnelle (E3 dans cet exemple).
- Allez dans Données -> Validation des données.
- Dans la boîte de dialogue Validation des données, dans l'onglet Paramètres, assurez-vous que Liste est sélectionné.
- Dans le champ Source, saisissez la formule =INDIRECT(D3). Ici, D3 est la cellule qui contient la liste déroulante principale.
- Cliquez sur OK.
Désormais, lorsque vous effectuez la sélection dans la liste déroulante 1, les options répertoriées dans la liste déroulante 2 sont automatiquement mises à jour.
Télécharger le fichier exemple
Comment cela marche-t-il? - La liste déroulante conditionnelle (dans la cellule E3) fait référence à =INDIRECT(D3). Cela signifie que lorsque vous sélectionnez « Fruits » dans la cellule D3, la liste déroulante dans E3 fait référence à la plage nommée « Fruits » (via la fonction INDIRECT) et répertorie donc tous les éléments de cette catégorie.
Note importante: Si la catégorie principale est constituée de plusieurs mots (par exemple, « Fruits de saison » au lieu de « Fruits »), alors vous devez utiliser la formule =INDIRECT(SUBSTITUT(D3,” “,”_”)), au lieu de la fonction INDIRECT simple illustrée ci-dessus.
- La raison en est qu'Excel n'autorise pas les espaces dans les plages nommées. Ainsi, lorsque vous créez une plage nommée utilisant plusieurs mots, Excel insère automatiquement un trait de soulignement entre les mots. Par exemple, lorsque vous créez une plage nommée avec « Fruits de saison », elle sera nommée Season_Fruits dans le backend. L'utilisation de la fonction SUBSTITUT dans la fonction INDIRECT garantit que les espaces sommes convertis en traits de soulignement.
Réinitialiser/Effacer automatiquement le contenu de la liste déroulante dépendante
Lorsque vous avez effectué la sélection et que vous avez modifié la liste déroulante parent, la liste déroulante dépendante ne changerait pas et serait donc une mauvaise entrée.
Par exemple, si vous sélectionnez « Fruits » comme catégorie, puis sélectionnez Pomme comme élément, puis revenez en arrière et modifiez la catégorie en « Légumes », la liste déroulante dépendante continuerait à afficher Apple comme élément.
Vous pouvez utiliser VBA pour vous assurer que le contenu de la liste déroulante dépendante est réinitialisé chaque fois que la liste déroulante principale est modifiée.
Voici le code VBA pour effacer le contenu d'une liste déroulante dépendante :
Private Sub Worksheet_Change(ByVal Target As Range) En cas d'erreur Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler : Application.EnableEvents = True Exit Sub End Sub
Le mérite de ce code revient à ce didacticiel de Debra sur l'effacement des listes déroulantes dépendantes dans Excel lorsque la sélection est modifiée.
Voici comment faire fonctionner ce code :
- Copiez le code VBA.
- Dans le classeur Excel où vous avez la liste déroulante dépendante, accédez à l'onglet Développeur et dans le groupe "Code", cliquez sur Visual Basic (vous pouvez également utiliser le raccourci clavier - ALT + F11).
- Dans la fenêtre de l'éditeur VB, à gauche dans l'explorateur de projet, vous verriez tous les noms de feuille de calcul. Double-cliquez sur celui qui a la liste déroulante.
- Collez le code dans la fenêtre de code à droite.
- Fermez l'éditeur VB.
Désormais, chaque fois que vous modifiez la liste déroulante principale, le code VBA est déclenché et le contenu de la liste déroulante dépendante est effacé (comme indiqué ci-dessous).
Si vous n'êtes pas un fan de VBA, vous pouvez également utiliser une simple astuce de mise en forme conditionnelle qui mettra en évidence la cellule en cas de non-correspondance. Cela peut vous aider à voir et à corriger visuellement la non-concordance (comme indiqué ci-dessous).
Voici les étapes pour mettre en évidence les incompatibilités dans les listes déroulantes dépendantes :
- Sélectionnez la cellule qui a la ou les listes déroulantes dépendantes.
- Allez dans Accueil -> Mise en forme conditionnelle -> Nouvelle règle.
- Dans la boîte de dialogue Nouvelle règle de formatage, sélectionnez "Utiliser une formule pour déterminer les cellules à formater".
- Dans le champ de formule, entrez la formule suivante : =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
- Définissez le format.
- Cliquez sur OK.
La formule utilise la fonction RECHERCHEV pour vérifier si l'élément de la liste déroulante dépendante est celui de la catégorie principale ou non. Si ce n'est pas le cas, la formule renvoie une erreur. Ceci est utilisé par la fonction ISERROR pour renvoyer TRUE qui indique au formatage conditionnel de mettre la cellule en surbrillance.
Vous aimerez peut-être aussi les didacticiels Excel suivants:
- Extraire les données en fonction d'une sélection de liste déroulante.
- Création d'une liste déroulante avec des suggestions de recherche.
- Sélectionnez plusieurs éléments dans une liste déroulante.
- Créez plusieurs listes déroulantes sans répétition.
- Gagnez du temps avec les formulaires de saisie de données dans Excel.
