Comment faire une liste déroulante dans Excel

La liste déroulante est un outil incroyablement utile qui peut aider à rendre le travail avec des informations plus confortable. Il permet de contenir plusieurs valeurs dans une cellule à la fois, avec lesquelles vous pouvez travailler, comme avec n'importe quelles autres. Pour sélectionner celle dont vous avez besoin, cliquez simplement sur l'icône de flèche, après quoi une liste de valeurs uXNUMXbuXNUMXbis s'affiche. Après en avoir sélectionné une spécifique, la cellule en est automatiquement remplie et les formules sont recalculées en fonction de celle-ci.

Excel propose de nombreuses méthodes différentes pour générer un menu déroulant et, en outre, il vous permet de les personnaliser de manière flexible. Analysons ces méthodes plus en détail.

Processus de création de liste

Pour générer un menu contextuel, cliquez sur les éléments de menu le long du chemin "Données" - "Validation des données". Une boîte de dialogue s'ouvrira dans laquelle vous devrez trouver l'onglet "Paramètres" et cliquer dessus s'il n'a pas été ouvert auparavant. Il a beaucoup de paramètres, mais l'élément "Type de données" est important pour nous. De toutes les significations, "Liste" est la bonne.

Le nombre de méthodes par lesquelles les informations sont saisies dans la liste contextuelle est assez important.

1. Indication indépendante des éléments de la liste séparés par un point-virgule dans le champ « Source » situé sur le même onglet de la même boîte de dialogue.
   

   

2. Indication préliminaire des valeurs. Le champ Source contient la plage dans laquelle les informations requises sont disponibles.
   

   

3. Spécification d'une plage nommée. Une méthode qui répète la précédente, mais il suffit de nommer préalablement la plage.
   

   

Chacune de ces méthodes produira le résultat souhaité. Examinons les méthodes de génération de listes déroulantes dans des situations réelles.

Basé sur les données de la liste

Disons que nous avons un tableau décrivant les types de différents fruits.

Pour créer une liste dans un menu déroulant basé sur cet ensemble d'informations, vous devez procéder comme suit :

1. Sélectionnez la cellule réservée à la future liste.
2. Recherchez l'onglet Données sur le ruban. Là, nous cliquons sur "Vérifier les données".
   

   

3. Trouvez l'élément "Type de données" et changez la valeur en "Liste".
   

   

4. Dans le champ indiquant l'option "Source", entrez la plage souhaitée. Veuillez noter que des références absolues doivent être spécifiées afin que lors de la copie de la liste, les informations ne se déplacent pas.
   

De plus, il existe une fonction pour générer des listes à la fois dans plusieurs cellules. Pour ce faire, vous devez tous les sélectionner et effectuer les mêmes étapes que celles décrites précédemment. Encore une fois, vous devez vous assurer que les références absolues sont écrites. Si l'adresse n'a pas de signe dollar à côté des noms de colonne et de ligne, vous devez les ajouter en appuyant sur la touche F4 jusqu'à ce que le signe $ soit à côté des noms de colonne et de ligne.

Avec enregistrement manuel des données

Dans la situation ci-dessus, la liste a été écrite en mettant en évidence la plage requise. C'est une méthode pratique, mais il est parfois nécessaire d'enregistrer manuellement les données. Cela permettra d'éviter la duplication d'informations dans le classeur.

Supposons que nous soyons confrontés à la tâche de créer une liste contenant deux choix possibles : oui et non. Pour accomplir la tâche, il faut :

1. Cliquez sur la cellule pour la liste.
2. Ouvrez "Données" et trouvez-y la section "Vérification des données" qui nous est familière.
   

   

3. Encore une fois, sélectionnez le type "Liste".
   

   

4. Ici, vous devez entrer "Oui ; Non » comme source. On voit que les informations sont saisies manuellement à l'aide d'un point-virgule pour l'énumération.

Après avoir cliqué sur OK, nous avons le résultat suivant.

Ensuite, le programme créera automatiquement un menu déroulant dans la cellule appropriée. Toutes les informations que l'utilisateur a spécifiées comme éléments dans la liste contextuelle. Les règles de création d'une liste dans plusieurs cellules sont similaires aux précédentes, à la seule exception que vous devez spécifier les informations manuellement à l'aide d'un point-virgule.

Création d'une liste déroulante à l'aide de la fonction DECALER

En plus de la méthode classique, il est possible d'utiliser la fonction ÉLIMINATIONpour générer des menus déroulants.

Ouvrons la feuille.

Pour utiliser la fonction pour la liste déroulante, vous devez procéder comme suit :

1. Sélectionnez la cellule qui vous intéresse où vous souhaitez placer la future liste.
2. Ouvrez l'onglet "Données" et la fenêtre "Validation des données" dans l'ordre.
   

   

3. Définissez "Liste". Cela se fait de la même manière que les exemples précédents. Enfin, la formule suivante est utilisée : =DECALAGE(A$2$;0;0;5). Nous l'entrons là où les cellules qui seront utilisées comme argument sont spécifiées.

Ensuite, le programme créera un menu avec une liste de fruits.

La syntaxe pour cela est :

=DECALAGE(référence,décalage_ligne,décalage_colonne,[hauteur],[largeur])

On voit que cette fonction a 5 arguments. Tout d'abord, la première adresse de cellule à décaler est donnée. Les deux arguments suivants spécifient le nombre de lignes et de colonnes à décaler. En parlant de nous, l'argument Hauteur est 5 car il représente la hauteur de la liste. 

Liste déroulante dans Excel avec substitution de données (+ en utilisant la fonction OFFSET)

Dans le cas donné ÉLIMINATION autorisé à créer un menu contextuel situé dans une plage fixe. L'inconvénient de cette méthode est qu'après avoir ajouté l'élément, vous devrez modifier vous-même la formule.

Pour créer une liste dynamique avec prise en charge de la saisie de nouvelles informations, vous devez :

1. Sélectionnez la cellule qui vous intéresse.
2. Développez l'onglet "Données" et cliquez sur "Validation des données".
3. Dans la fenêtre qui s'ouvre, sélectionnez à nouveau l'élément "Liste" et spécifiez la formule suivante comme source de données : =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Cliquez sur OK.

Celui-ci contient une fonction COUNTIF, pour déterminer immédiatement combien de cellules sont remplies (bien qu'il ait un nombre d'utilisations beaucoup plus important, nous l'écrivons simplement ici dans un but précis).

Pour que la formule fonctionne normalement, il est nécessaire de savoir s'il y a des cellules vides sur le chemin de la formule. Ils ne devraient pas l'être.

Liste déroulante avec des données d'une autre feuille ou d'un fichier Excel

La méthode classique ne fonctionne pas si vous avez besoin d'obtenir des informations d'un autre document ou même d'une feuille contenue dans le même fichier. Pour cela, la fonction est utilisée INDIRECT, qui vous permet d'entrer dans le bon format un lien vers une cellule située dans une autre feuille ou en général - un fichier. Vous devez effectuer les opérations suivantes :

1. Activez la cellule où nous plaçons la liste.
2. Nous ouvrons la fenêtre que nous connaissons déjà. Au même endroit où nous avons précédemment indiqué les sources pour les autres gammes, une formule est indiquée au format =INDIRECT("[Liste1.xlsx]Feuille1 !$A$1:$A$9"). Naturellement, au lieu de List1 et Sheet1, vous pouvez insérer respectivement vos noms de livre et de feuille. 

Attention! Le nom du fichier est indiqué entre crochets. Dans ce cas, Excel ne pourra pas utiliser le fichier actuellement fermé comme source d'informations.

Il convient également de noter que le nom du fichier lui-même n'a de sens que si le document requis se trouve dans le même dossier que celui où la liste sera insérée. Si ce n'est pas le cas, vous devez indiquer l'adresse de ce document en entier.

Création de listes déroulantes dépendantes

Une liste dépendante est une liste dont le contenu est affecté par le choix de l'utilisateur dans une autre liste. Supposons que nous ayons une table ouverte devant nous qui contient trois plages, chacune ayant reçu un nom.

Vous devez suivre ces étapes pour générer des listes dont le résultat est affecté par l'option sélectionnée dans une autre liste.

1. Créez une première liste avec des noms de plage.
   

   

2. Au point d'entrée de la source, les indicateurs requis sont mis en évidence un par un.
   

   

3. Créez une 2ème liste en fonction du type de plante que la personne a choisi. Alternativement, si vous spécifiez des arbres dans la première liste, les informations de la deuxième liste seront "chêne, charme, châtaignier" et au-delà. Il est nécessaire d'écrire la formule à la place de l'entrée de la source de données =INDIRECT(E3). E3 – cellule contenant le nom de la plage 1.=INDIRECT(E3). E3 – cellule avec le nom de la liste 1.

Maintenant, tout est prêt.

Comment sélectionner plusieurs valeurs dans une liste déroulante ?

Parfois, il n'est pas possible de donner la préférence à une seule valeur, il faut donc en sélectionner plusieurs. Ensuite, vous devez ajouter une macro au code de la page. L'utilisation de la combinaison de touches Alt + F11 ouvre l'éditeur Visual Basic. Et le code y est inséré.

Sous-feuille de calcul privée_Change (cible ByVal en tant que plage)

    On Error Resume Next

    Si Non Intersect(Target, Range(«Е2:Е9»)) N'est rien et Target.Cells.Count = 1 Alors

        Application.EnableEvents = False

        Si Len (Target.Offset (0, 1)) = 0 Alors

            Cible. Décalage (0, 1) = Cible

        d'autre

            Cible.End (xlToRight) .Offset (0, 1) = Cible

        Si fin

        Target.ClearContents

        Application.EnableEvents = True

    Si fin

End Sub 

Pour que le contenu des cellules soit affiché ci-dessous, nous insérons le code suivant dans l'éditeur.

Sous-feuille de calcul privée_Change (cible ByVal en tant que plage)

    On Error Resume Next

    Si Non Intersect(Target, Range(«Н2:К2»)) N'est rien et Target.Cells.Count = 1 Alors

        Application.EnableEvents = False

        Si Len (Target.Offset (1, 0)) = 0 Alors

            Cible. Décalage (1, 0) = Cible

        d'autre

            Cible.End (xlDown) .Offset (1, 0) = Cible

        Si fin

        Target.ClearContents

        Application.EnableEvents = True

    Si fin

End Sub

Et enfin, ce code sert à écrire dans une cellule.

Sous-feuille de calcul privée_Change (cible ByVal en tant que plage)

    On Error Resume Next

    Si Non Intersect(Target, Range("C2:C5")) N'est rien et Target.Cells.Count = 1 Alors

        Application.EnableEvents = False

        newVal = Cible

        Application.Annuler

        oldval = Cible

        Si Len (oldval) <> 0 Et oldval <> newVal Alors

            Cible = Cible & « », » & nouvelleVal

        d'autre

            Cible = nouvelleVal

        Si fin

        Si Len (newVal) = 0 Alors Target.ClearContents

        Application.EnableEvents = True

    Si fin

End Sub

Les plages sont modifiables.

Comment faire une liste déroulante avec une recherche ?

Dans ce cas, vous devez d'abord utiliser un autre type de liste. L'onglet "Développeur" s'ouvre, après quoi vous devez cliquer ou appuyer (si l'écran est tactile) sur l'élément "Insérer" - "ActiveX". Il a une zone de liste déroulante. Vous serez invité à dessiner cette liste, après quoi elle sera ajoutée au document.

De plus, il est configuré via les propriétés, où une plage est spécifiée dans l'option ListFillRange. La cellule dans laquelle la valeur définie par l'utilisateur est affichée est configurée à l'aide de l'option LinkedCell. Ensuite, il vous suffit d'écrire les premiers caractères, car le programme proposera automatiquement des valeurs possibles.

Liste déroulante avec substitution automatique des données

Il existe également une fonction permettant de remplacer automatiquement les données après leur ajout à la plage. C'est facile à faire :

1. Créez un ensemble de cellules pour la future liste. Dans notre cas, il s'agit d'un ensemble de couleurs. Nous le sélectionnons.
   

   

2. Ensuite, il doit être formaté sous forme de tableau. Vous devez cliquer sur le bouton du même nom et sélectionner le style de tableau.
   

   

   

Ensuite, vous devez confirmer cette plage en appuyant sur le bouton "OK".

Nous sélectionnons la table résultante et lui donnons un nom via le champ de saisie situé en haut de la colonne A.

Voilà, il y a un tableau, et il peut servir de base à une liste déroulante, pour laquelle il faut :

1. Sélectionnez la cellule où se trouve la liste.
2. Ouvrez la boîte de dialogue Validation des données.
   

   

3. Nous définissons le type de données sur "Liste" et, en tant que valeurs, nous donnons le nom de la table via le signe =.
   

   

   

Tout, la cellule est prête et les noms des couleurs y sont affichés, comme nous en avions besoin à l'origine. Maintenant, vous pouvez ajouter de nouvelles positions simplement en les écrivant dans une cellule située un peu plus bas immédiatement après la dernière.

C'est l'avantage de la table, que la plage augmente automatiquement lorsque de nouvelles données sont ajoutées. En conséquence, c'est le moyen le plus pratique d'ajouter une liste.

Comment copier la liste déroulante ?

Pour copier, il suffit d'utiliser la combinaison de touches Ctrl + C et Ctrl + V. Ainsi, la liste déroulante sera copiée avec le formatage. Pour supprimer la mise en forme, vous devez utiliser un collage spécial (dans le menu contextuel, cette option apparaît après la copie de la liste), où l'option «conditions sur les valeurs» est définie.

Sélectionner toutes les cellules contenant une liste déroulante

Pour accomplir cette tâche, vous devez utiliser la fonction "Sélectionner un groupe de cellules" dans le groupe "Rechercher et sélectionner".

Après cela, une boîte de dialogue s'ouvrira, dans laquelle vous devrez sélectionner les éléments "Tous" et "Ces mêmes" dans le menu "Validation des données". Le premier élément sélectionne toutes les listes, et le second sélectionne uniquement celles qui sont similaires à certaines.