L'une des fonctionnalités les plus utiles de la validation des données est la possibilité de créer une liste déroulante qui permet aux utilisateurs de sélectionner une valeur dans une liste prédéfinie. Les listes déroulantes permettent aux utilisateurs de saisir facilement uniquement les données qui répondent à vos besoins.
Les listes déroulantes sont faciles à créer et à utiliser. Mais une fois que vous commencez à utiliser des menus déroulants dans vos feuilles de calcul, vous vous heurterez inévitablement à un défi : comment pouvez-vous faire dépendre les valeurs d'une liste déroulante des valeurs d'une autre ? En d'autres termes, comment rendre dynamique une liste déroulante ?
vba lit le fichier texte ligne par ligne
Voici quelques exemples:
- une liste de villes qui dépend du pays sélectionné
- une liste de saveurs qui dépend du type de crème glacée
- une liste de modèles qui dépend du fabricant
- une liste d'aliments qui dépend de la catégorie
Ce genre de listes s'appelle listes déroulantes dépendantes , puisque la liste dépend d'une autre valeur. Ils sont créés avec validation des données, à l'aide d'une formule personnalisée basée sur le Fonction INDIRECT et plages nommées . Cela peut sembler compliqué, mais c'est en fait très simple et un excellent exemple de la façon dont INDIRECT peut être utilisé.
Lisez la suite pour voir comment créer des listes déroulantes dépendantes dans Excel.
Exemple de liste déroulante dépendante
Dans l'exemple ci-dessous, la colonne B propose un menu déroulant pour la catégorie d'aliments et la colonne C propose des options dans la catégorie choisie. Si l'utilisateur sélectionne « Fruit », il voit une liste de fruits, s'il sélectionne « Noix », il voit une liste de noix, et s'il sélectionne « Légume », il voit une liste de légumes.
La validation des données dans la colonne B utilise cette formule personnalisée :
=category
Et la validation des données dans la colonne C utilise cette formule personnalisée :
= INDIRECT (B5)
Où la feuille de calcul contient les plages nommées suivantes :
catégorie = E4 : G4
légume = F5:F10
écrou = G5:G9
fruits = E5:E11
Comment cela fonctionne
La clé de cette technique est plages nommées + la fonction INDIRECT. INDIRECT accepte les valeurs de texte et essaie de les évaluer en tant que références de cellule. Par exemple, INDIRECT prendra le texte « A1 » et le transformera en une référence réelle :
= INDIRECT ('A1') =A1
De même, INDIRECT convertira le texte 'A1:A10' dans la plage A1:A10 à l'intérieur d'une autre fonction :
= SUM ( INDIRECT ('A1:A10') = SUM (A1:A10)
À première vue, vous pourriez trouver cette construction ennuyeuse, voire inutile. Pourquoi compliquer une belle formule simple avec INDIRECT ?
Rassurez-vous, il y a de la méthode à la folie :)
La beauté d'INDIRECT est qu'il vous permet d'utiliser du texte exactement comme une référence de cellule . Cela offre deux avantages clés :
- Vous pouvez assembler une référence de texte à l'intérieur d'une formule, ce qui est pratique pour certains types de références dynamiques .
- Vous pouvez récupérer des valeurs de texte sur une feuille de calcul et les utiliser comme référence de cellule dans une formule.
Dans l'exemple de cette page, nous combinons cette dernière idée avec des plages nommées pour créer des listes déroulantes dépendantes. INDIRECT mappe le texte sur une plage nommée, qui est ensuite résolue en une référence valide. Ainsi, dans cet exemple, nous récupérons les valeurs de texte dans la colonne B et utilisons INDIRECT pour les convertir en références de cellules en faisant correspondre les plages nommées existantes, comme ceci :
quelle fonctionnalité utilisez-vous pour ajouter des informations récapitulatives à la fin d'un tableau?
= INDIRECT (B5) = INDIRECT ('nut') =G5:G9
B5 se résout en le texte 'nut' qui se résout dans la plage G5:G9.
Comment configurer des listes déroulantes dépendantes
Cette section décrit comment configurer les listes déroulantes dépendantes illustrées dans l'exemple.
1. Créez les listes dont vous avez besoin. Dans l'exemple, créez une liste de fruits, de noix et de légumes dans une feuille de calcul.
2. Créez des plages nommées pour chaque liste : catégorie = E4:G4, légume = F5:F10, noix = G5:G9 et fruit = E5:E11.
Important : les valeurs de E4, F4 et G4 doivent correspondre aux trois dernières plages nommées ci-dessus (légumes, noix et fruits). En d'autres termes, vous devez vous assurer que les plages nommées que vous avez créées correspondent aux valeurs qui apparaîtront dans la liste déroulante Catégorie.
Remarque : si les plages nommées sont nouvelles pour vous, voir cette page .3. Créez et testez une règle de validation des données pour fournir une liste déroulante pour la catégorie à l'aide de la formule personnalisée suivante :
=category
Remarque : pour être clair, la plage nommée « catégorie » est utilisée uniquement à des fins de lisibilité et de commodité - l'utilisation d'une plage nommée ici n'est pas requise. Notez également que la validation des données avec une liste fonctionne bien avec les plages horizontales et verticales - les deux seront présentées sous forme de menu déroulant vertical.
4. Créez une règle de validation des données pour la liste déroulante dépendante avec une formule personnalisée basée sur la fonction INDIRECT :
= INDIRECT (B5)
Dans cette formule, INDIRECT évalue simplement les valeurs de la colonne B en tant que références, ce qui les lie aux plages nommées précédemment définies.
calculer le nombre d'années entre deux dates
5. Testez les listes déroulantes pour vous assurer qu'elles répondent dynamiquement aux valeurs de la colonne B.
Remarque : l'approche que nous adoptons ici n'est pas sensible à la casse. La plage nommée est appelée 'nut' et la valeur dans B6 est 'Nut' mais la fonction INDIRECT se résout correctement à la plage nommée, même si la casse diffère.
Gérer les espaces
Les plages nommées n'autorisent pas les espaces, la convention habituelle consiste donc à utiliser des caractères de soulignement à la place. Ainsi, par exemple, si vous souhaitez créer une plage nommée pour crème glacée , vous utiliseriez crème glacée. Cela fonctionne bien, mais les listes déroulantes dépendantes se briseront si elles essaient de mapper « ice cream » sur « ice_cream ». Pour résoudre ce problème, vous pouvez utiliser une formule personnalisée plus robuste pour la validation des données :
= INDIRECT ( SUBSTITUTE (A1,' ','_'))
Cette formule utilise toujours INDIRECT pour lier la valeur de texte dans A1 à une plage nommée, mais avant l'exécution INDIRECT, la fonction SUBSTITUTE remplace tous les espaces par des traits de soulignement. Si le texte ne contient aucun espace, SUBSTITUTE n'a aucun effet.
Dossier de pratique
Le fichier d'exemple est joint ci-dessous - consultez-le pour voir comment cela fonctionne. Si vous voulez apprendre la technique vous-même, je vous recommande de créer votre propre fichier. La meilleure façon d'apprendre est de faire.
Auteur Dave Bruns Pièces jointes dropdown.xlsx dépendant