TOP
Listes déroulantes liées
Description
Dans Excel, vous pouvez rapidement et facilement créer une liste déroulante , mais avez-vous déjà essayé de créer une liste déroulante dépendante ? Il existe plusieurs façons de créer une telle liste, alors examinons-les.
Méthode 1. Fonction INDIRECT
Cette technique est basée sur l'application de la fonction INDIRECT, qui peut faire une chose simple : convertir le contenu de n'importe quelle cellule spécifiée en une adresse de plage que Excel comprend. Autrement dit, si la cellule contient le texte " А1 ", alors la fonction donnera une référence à la cellule en conséquence А1 . Si la cellule contient le mot " Auto ", alors la fonction affichera une référence à la plage nommée avec le nom Auto etc.
Prenez, par exemple, cette liste de modèles de voitures Toyota, Ford et Nissan :
Sélectionnez la liste complète des modèles Toyota (à partir de la cellule А2 et jusqu'à la fin de la liste) et donnez un nom à cette plage Toyota dans le menu Insérer - Nom - Attribuer (Insert - Name - Define) . Ensuite nous répéterons la même chose avec les listes Ford et Nissan , en précisant les noms des plages en conséquence Ford et Nissan .
Lors de l'attribution de noms, n'oubliez pas que les noms des plages dans Excel ne doivent pas contenir d'espaces, de signes de ponctuation et doivent commencer par une lettre. Par conséquent, s'il y avait une lacune dans l'une des marques de voiture (par exemple Ssang Yong), il faudrait alors le remplacer dans la cellule et dans le nom de la plage par un trait de soulignement (c'est-à-dire Ssang_Yong).
Créons maintenant la première liste déroulante pour choisir une marque de voiture. Sélectionnez une cellule vide et ouvrez le menu Données - Vérifier (Data - Validation) , puis dans la liste déroulante Type de données choisir une option Liste et sur le terrain Source - mettez en surbrillance les cellules avec les noms de marques (cellules jaunes dans notre exemple). Après avoir cliqué sur ОК la première liste déroulante est prête :
Créons maintenant une deuxième liste déroulante, qui affichera les modèles de la marque sélectionnée dans la première liste. Comme dans le cas précédent, sélectionnez une cellule vide et ouvrez le menu Données - Vérifier - plus loin Liste . Sur le terrain Source vous devrez saisir la formule suivante :
=INDIRECT(F3)
Où:
- F3 - l'adresse de la cellule avec la première liste déroulante - remplacez-la par la vôtre.
Tous. Après avoir cliqué sur D'ACCORD le contenu de la deuxième liste sera sélectionné par le nom de la plage sélectionnée dans la première liste.
Inconvénients de cette méthode :
- Les plages dynamiques spécifiées par les formules de type OFFSET ne peuvent pas agir comme des plages secondaires (dépendantes). Vous pouvez les utiliser pour la liste primaire (indépendante), mais la liste secondaire doit être définie de manière rigide, sans formules.
- Les noms de plages secondaires doivent correspondre aux éléments de la liste déroulante principale. Autrement dit, s'il contient du texte avec des espaces, ils devront être remplacés par des soulignements, etc.
- Vous devez créer manuellement de nombreuses plages nommées.
Méthode 2. Liste de correspondance (OFFSET et MATCH)
Cette méthode nécessite une liste triée de correspondances marque-modèle du type suivant :
Pour créer une liste déroulante principale de marques, vous pouvez utiliser la méthode habituelle décrite ci-dessus, à savoir :
- Donnez le nom de la plage D1:D3 (par exemple Marques )
- Sélectionnez dans l'onglet Données (Data) équipe Verification des données (Data validation)
- Sélectionnez une option de vérification dans la liste déroulante Liste (List) et précisez comme qualité Sources (Source) = Marques ou sélectionnez simplement les cellules D1: D3 (si elles se trouvent sur la même feuille que la liste).
Mais pour une liste de modèles dépendante, vous devrez créer une plage nommée avec la fonction OFFSET, qui fera référence dynamiquement uniquement aux cellules des modèles d'une certaine marque. Pour ça:
- Appuyez sur Ctrl + F3 ou utilisez le bouton Gestionnaire de noms (Name manager) sur l'onglet Formules (Formulas) . Dans les versions antérieures à 2003, il s'agissait d'une commande de menu Insérer - Nom - Attribuer (Insert - Name - Define)
- Créez une nouvelle plage nommée avec n'importe quel nom (par exemple Des modèles ) et sur le terrain Lien (Reference) dans la partie basse de la fenêtre, saisissez manuellement la formule suivante :
=OFFSET( 1 $A$ ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )
Les références doivent être absolues (avec des signes $ ). Après avoir appuyé sur Enter, les noms des feuilles seront automatiquement ajoutés à la formule.
La fonction OFFSET est capable d'émettre une référence à une plage de la taille souhaitée, décalée par rapport à la cellule initiale du nombre de lignes et de colonnes donné. Dans une version plus compréhensible, la syntaxe de cette fonction est la suivante :
=OFFSET( start_cell ; rétrograde ; shift_right ; range_size_in_rows ; range_size_in_columns )
Donc:
- start_cell - on prend la première cellule de notre liste, c'est à dire A1 ;
- rétrograde - on compte la fonction MATCH, qui, en termes simples, renvoie le numéro de série de la cellule avec la marque sélectionnée (G7) dans la plage donnée (colonnes ET );
- shift_right =1 , nous voulons donc nous référer aux modèles de la colonne adjacente ( DANS );
- range_size_in_rows - nous calculons à l'aide de la fonction COUNTIF, qui est capable de compter le nombre d'occurrences dans la liste (colonne A) des valeurs dont nous avons besoin - marques de voitures ( G7 );
- range_size_in_columns =1 , nous avons donc besoin d'une colonne avec des modèles.
Le résultat devrait ressembler à ceci :
Il reste à ajouter une liste déroulante basée sur la formule créée à la cellule G8 . Pour ça:
- Sélectionnez une cellule G8
- Sélectionnez dans l'onglet Données (Data) équipe Verification des données (Data validation) ou dans le menu Données - Vérifier (Data - Validation)
- Sélectionnez l'option de vérification dans la liste déroulante Liste (List) et entrez comme Sources (Source) le signe est égal au nom de notre gamme, c'est à dire = Des modèles .
Articles sur le sujet :