Il existe plusieurs façons de créer une liste déroulante. Le choix dépend de la structure des données dont vous disposez.

La première façon de créer une liste à deux niveaux

La première méthode est basée sur la création d'un tableau « intelligent » dont l'en-tête contient les valeurs de la première liste déroulante (groupe), et les lignes du tableau correspondent aux valeurs de la deuxième liste déroulante (sous-groupe). Les valeurs des éléments du sous-groupe doivent être situées dans la colonne du groupe correspondante, comme dans la figure ci-dessous.

Commençons maintenant à créer la première liste déroulante du groupe (dans mon cas, une liste de pays) :

1. Sélectionnez la cellule dans laquelle vous allez insérer la liste déroulante ;
2. Accédez à l'onglet du ruban Données;
3. Choisir une équipe Vérification des données;
4. Sélectionnez une valeur dans la liste déroulante Liste;
5. Sur le terrain Source indiquez la formule suivante =INDIRECT("Tableau1[#En-têtes]").

Formule INDIRECT renvoie une référence à la plage d'en-têtes de table intelligente. L'avantage d'utiliser un tel tableau est qu'à mesure que vous ajoutez des colonnes, la liste déroulante se développera automatiquement.

Il reste à créer une deuxième liste déroulante dépendante - une liste de sous-groupes.

Nous répétons hardiment les 4 premiers points décrits ci-dessus. Source dans la fenêtre Vérification des données pour la deuxième liste déroulante la formule sera =INDIRECT("Table1["&F2&"]"). Cellule F2 dans ce cas, la valeur de la première liste déroulante.

Vous pouvez également utiliser une table muette ordinaire, mais dans ce cas, vous devrez modifier manuellement les plages d'en-tête et de lignes. Dans l'exemple considéré, cela se produit automatiquement.

La deuxième façon de créer une liste à deux niveaux

La deuxième méthode est pratique à utiliser lorsque les données de la liste déroulante sont écrites sur deux colonnes. Le premier contient le nom du groupe et le second contient le nom du sous-groupe.

IMPORTANT! Avant de créer une liste dépendante par sous-groupes, vous devez trier la table source par la première colonne (la colonne avec le groupe) ; il sera alors clair pourquoi cela est fait.

Pour créer un groupe déroulant, nous avons besoin d'une colonne supplémentaire contenant les valeurs de groupe uniques de la table source. Pour créer cette liste, utilisez la fonctionnalité Supprimer les doublons ou utilisez la commande Unique du complément VBA-Excel.

Créons maintenant une liste déroulante de groupes. Pour ce faire, suivez les 4 premiers points de la première méthode de création d'une liste à deux niveaux. Comme Source spécifier une plage de valeurs de groupe uniques. Ici, tout est standard.

Recommandation : Il est pratique de spécifier une plage nommée comme source. Pour le créer, ouvrez Gestionnaire de noms depuis l'onglet Formules et donnez un nom à la plage avec des valeurs uniques.

Maintenant, le plus difficile est de préciser dans Source un lien dynamique vers une plage avec les valeurs de la deuxième liste déroulante (liste des sous-groupes). Nous allons le résoudre en utilisant la fonction OFFSET(lien, row_offset, column_offset, [hauteur], [largeur]), qui renvoie une référence à une plage située à un nombre spécifié de lignes et de colonnes d'une cellule ou d'une plage de cellules.

• Lien dans notre cas - 1 $A$- coin supérieur gauche de la table source ;
• Décalage_par_lignes - MATCH(F3,$A$1 :$A$67,0)-1- numéro de ligne avec la valeur du groupe souhaité (dans mon cas, cellule pays F3) moins un;
• Décalage_par_colonnes - 1 - puisque nous avons besoin d'une colonne avec des sous-groupes (villes) ;
• [Hauteur] - COUNTIF($A$1 :$A$67,F3)- nombre de sous-groupes dans le groupe souhaité (nombre de villes du pays F3);
• [Largeur] - 1 - puisque c'est la largeur de notre colonne avec sous-groupes.

Bonjour, cher lecteur !

Dans cet article, j'aimerais parler de ce qu'est une liste déroulante dans une cellule, comment la créer et, par conséquent, à quoi sert-elle ?

Il s'agit d'une liste de valeurs fixes qui ne sont disponibles qu'à partir d'une plage de valeurs spécifiée. Cela signifie que la cellule que vous spécifiez ne peut contenir que des données correspondant aux valeurs de la plage spécifiée ; les données qui ne correspondent pas ne seront pas saisies. Dans une cellule, vous pouvez sélectionner les valeurs proposées par une liste fixe dans la cellule.

Eh bien, regardons la création de listes déroulantes et pourquoi cela est nécessaire :

Personnellement, j'utilise la liste déroulante tout le temps pour les 3 raisons. Et cela simplifie grandement mon travail avec les données : je réduis délibérément la possibilité de saisir des données primaires à 0 %.

Eh bien, voici 2 questions, quoi et pourquoi, je vous l'ai dit, mais nous parlerons de la façon de procéder ci-dessous.

Et nous allons créer une liste déroulante dans une cellule en plusieurs étapes :

1. Déterminez la plage de cellules dans laquelle nous allons créer une liste fixe.

2. Sélectionnez la plage dont nous avons besoin et sélectionnez l'élément dans le menu "Données" - "Vérification des données", dans la fenêtre contextuelle qui apparaît, sélectionnez l'élément dans la sélection spécifiée "Liste".

3. Dans la ligne déverrouillée ci-dessous, indiquez la plage de données qui doit figurer dans notre liste déroulante. Cliquez sur "D'ACCORD" et le travail est fait.

Dans les anciennes versions d'Excel, il n'existait aucun moyen de créer une liste déroulante dans une cellule en utilisant les données d'autres feuilles. Il est donc logique de créer des listes dans la même feuille et de les masquer. De plus, si nécessaire, vous pouvez créer une liste verticale - une liste horizontale à l'aide de la fonctionnalité.

Et c'est tout pour moi ! J'espère vraiment que tout ce qui précède est clair pour vous. Je serais très reconnaissant pour vos commentaires, car ils sont un indicateur de lisibilité et m'inspirent pour écrire de nouveaux articles ! Partagez ce que vous lisez avec vos amis et aimez-le !

Le progrès de l’humanité repose sur le désir de chacun de vivre au-dessus de ses moyens
Samuel Butler, philosophe

Lors de la création de tableaux, il est parfois très pratique d'utiliser des listes déroulantes (c'est-à-dire déroulantes). Excel 2010 vous permet de le faire de plusieurs manières. Regardons-les.

Méthode 1 : créer une liste déroulante dans Excel 2010 à l'aide de l'outil de validation des données

Cette méthode est considérée comme standard car elle est simple et pratique.

1. Sur l'espace libre de la feuille, notez tous les éléments de la liste déroulante dans une colonne, chaque élément dans sa propre cellule.

2. Donnez un nom à la plage de cellules. Pour ça:

• Cliquez sur la cellule supérieure de la liste et, en maintenant le bouton gauche de la souris enfoncé, faites glisser le curseur vers le bas jusqu'à ce que toute la liste soit sélectionnée.
• placez le curseur dans le champ « Nom », à gauche de la barre de formule ;
• entrez un nom pour la liste et appuyez sur Entrée.

Veuillez noter que le nom de la liste doit toujours commencer par une lettre et ne pas contenir d'espaces.

3. Sélectionnez la cellule du tableau où la liste déroulante sera placée.

4. Ouvrez l'onglet « Données » et cliquez sur le bouton « Vérification des données ». Dans la fenêtre qui s'ouvre, allez dans l'onglet « Options ». Dans la liste déroulante « Type de données », sélectionnez « Liste ».

5. Dans la ligne « Source », vous devez indiquer l'adresse d'où seront extraits les éléments de la liste créée. L'adresse sera le nom que vous donnerez à la plage de cellules. Il existe plusieurs façons de définir l'adresse.

• Saisissez-le manuellement en plaçant un signe égal devant, par exemple « = mois ». La casse des lettres n'est pas importante.
• En cliquant avec la souris dans la ligne « Source » (à activer), sélectionnez avec le curseur tous les éléments de la liste du tableau.

6. Si vous devez créer un message à saisir, ouvrez l'onglet du même nom. Écrivez le texte qui apparaîtra à côté de la cellule déroulante lorsqu'elle sera sélectionnée. Sur l'onglet suivant - "Message d'erreur", vous pouvez de la même manière écrire un texte signalant les erreurs.

7. Confirmez votre saisie en cliquant sur « OK » et la liste déroulante est prête. Pour l'ouvrir, cliquez sur la flèche vers le bas qui apparaît à côté de la cellule contenant la liste.

Méthode 2. Créez rapidement une liste déroulante

Une liste déroulante dans Excel 2010 peut être créée avec un seul raccourci clavier, mais elle ne peut être située qu'à un seul endroit : dans la cellule située sous les éléments de la liste.

1. Lister dans une colonne tous les éléments de la future liste déroulante.

2. Sélectionnez la cellule située sous le dernier élément et appuyez sur la combinaison de touches « Alt » + « flèche vers le bas » - la liste sera créée. Cette méthode vous permettra de définir la cellule à la valeur de l'un des éléments.

Méthode 3 : créer une liste déroulante en tant que contrôle

Pour utiliser cette méthode, activez l'affichage de l'onglet « Développeur » : ouvrez le menu « Fichier » - « Options » - « Personnaliser le ruban ». Dans la colonne « Onglets principaux », cochez la case « Développeur ». Confirmez l'action en cliquant sur "OK" - l'onglet sera créé.

1. Lister les éléments de la future liste dans une colonne.

2. Dans le menu « Insertion » de l'onglet « Développeur », sélectionnez « Contrôles de formulaire » - « Zone de liste déroulante ».

3. Dessinez votre future liste déroulante sur la feuille avec le curseur. Faites un clic droit dessus et sélectionnez « Formater l'objet » dans le menu.

4. La valeur du champ « Générer une liste par plage » doit être une liste d'éléments - sélectionnez-la avec le curseur et le champ sera rempli automatiquement. Dans le champ « Lien vers la cellule », indiquez l'adresse de la cellule où sera affiché le numéro de série de l'élément sélectionné. Sélectionnez une cellule et cliquez dessus. Le champ Nombre de lignes de liste vous permet de configurer le nombre d'éléments qui seront affichés lorsque la liste est développée.

5. Confirmez votre saisie et cliquez sur OK. La liste sera créée.

Méthode 4 : créer une liste déroulante en tant que contrôle ActiveX

La méthode la plus complexe, mais avec les paramètres les plus flexibles.

1. Créez une liste en utilisant la méthode décrite ci-dessus.

2. Dans le menu « Insertion » de l'onglet « Développeur », sélectionnez « Contrôles ActiveX » - « Combo Box ».

3. Dessinez la future liste déroulante sur la feuille.

4. L'option qui vous permet de modifier la liste déroulante s'appelle « Mode Conception ». Si ce mode est actif, le bouton du même nom sera mis en évidence dans la section « Contrôles », à côté du bouton « Insérer ». Si le bouton n'est pas mis en surbrillance, le mode d'édition est désactivé.

5. Pour définir les paramètres de la liste, cliquez sur le bouton « Propriétés » dans la même section. La fenêtre des paramètres « Propriétés » s'ouvrira. Les deux onglets de cette fenêtre contiennent les mêmes paramètres, triés dans le premier cas par ordre alphabétique, dans le second par catégorie.

6. La plupart des paramètres peuvent être laissés par défaut, mais les plus nécessaires sont répertoriés ci-dessous.

• ListRows - analogue à la valeur « Nombre de lignes dans la liste », indiquera combien de lignes seront affichées.
• Police : paramètres de police. Permet de sélectionner la police et son style.
• ForeColor - sélection d'une couleur de police dans le tableau.
• BackColor - couleur d'arrière-plan.
• ListFillRange - emplacement de la liste des éléments au format : feuille ("!" - séparateur) et une plage de cellules. Par exemple : Feuille2!D2:D6. Enregistré manuellement.
• LinkedCell - lien vers une cellule. Spécifiez manuellement l'adresse de la cellule où le numéro de série de l'élément de liste sélectionné sera affiché.

7. Enregistrez les paramètres et désactivez le mode conception en cliquant sur le bouton du même nom. La liste déroulante sera créée et vous pourrez vérifier son fonctionnement.

Si vous remplissez un tableau dans Excel et que les données d'une colonne peuvent parfois être répétées, par exemple le nom d'un produit, ou le nom d'un employé, alors afin de ne pas saisir à chaque fois le paramètre souhaité, il Il est plus simple et plus facile de créer une liste déroulante une fois et d'y sélectionner une valeur.

Dans cet article, nous verrons comment créer des listes déroulantes de différents types dans un tableau Excel.

Créez une simple liste déroulante

Pour ce faire, dans les cellules A1:A7, nous saisissons les données qui seront affichées dans la liste. Sélectionnons maintenant la cellule dans laquelle nous allons créer une liste déroulante - B2.

Allez dans l'onglet « Données » et cliquez sur le bouton « Vérification des données ».

Sous l'onglet « Paramètres », dans le champ « Type de données », sélectionnez « Liste ». Vous pouvez saisir des valeurs dans le champ Source de différentes manières :

1 – saisissez manuellement les valeurs de la liste, séparées par des points-virgules ;

2 – indiquez la plage de cellules dans laquelle les données de la liste déroulante sont saisies ;

3 – sélectionnez les cellules avec des noms, faites un clic droit dessus et sélectionnez « Attribuer un nom » dans le menu.

Sélectionnez la cellule B2 et mettez « = » dans le champ « Source », puis écrivez le nom créé.

Nous avons donc créé une simple liste déroulante dans Excel.

Si vous avez un en-tête pour une colonne et que vous devez remplir chaque ligne avec des valeurs, sélectionnez non pas une cellule, mais une plage de cellules - B2:B9. Ensuite, vous pouvez sélectionner la valeur souhaitée dans chaque cellule dans la liste déroulante.

Ajouter des valeurs à une liste déroulante – liste dynamique

Dans ce cas, nous ajouterons des valeurs à la plage requise et elles seront automatiquement ajoutées à la liste déroulante.

Sélectionnez la plage de cellules – D1: D8, puis dans l'onglet « Accueil », cliquez sur « Formater en tableau » et sélectionnez n'importe quel style.

Confirmez l'emplacement des données et cochez la case « Tableau avec en-têtes ».

En haut, nous écrivons le titre du tableau - « Employés » et le remplissons de données.

Sélectionnez la cellule dans laquelle se trouvera la liste déroulante et cliquez sur le bouton « Vérification des données ». Dans la fenêtre suivante, dans le champ « Source », écrivez ce qui suit : =INDIRECT(« Table1 »). J'ai un tableau sur une feuille, donc j'écris « Tableau1 », s'il y en a un deuxième – « Tableau2 », et ainsi de suite.

Ajoutons maintenant un nouvel employé à notre liste : Ira. Il apparaît dans la liste déroulante. Si nous supprimons un nom du tableau, il sera également supprimé de la liste.

Liste déroulante avec les valeurs d'une autre feuille

Si le tableau avec les listes déroulantes se trouve sur une feuille et que les données de ces listes se trouvent sur une autre, alors cette fonction nous aidera beaucoup.

Sur la feuille 2, sélectionnez une cellule ou une plage de cellules, puis cliquez sur le bouton « Validation des données ».

Allez sur la feuille 1, placez le curseur dans le champ « Source » et sélectionnez la plage de cellules souhaitée.

Vous pouvez maintenant ajouter des noms sur la feuille 1, ils seront ajoutés aux listes déroulantes sur la feuille 2.

Création de listes déroulantes dépendantes

Supposons que nous ayons trois plages : prénoms, noms et patronymes des employés. Pour chacun, vous devez attribuer un nom. Nous sélectionnons les cellules de cette plage, vous pouvez également les vider - au fil du temps, vous pourrez y ajouter des données, qui apparaîtront dans la liste déroulante. Faites un clic droit dessus et sélectionnez « Attribuer un nom » dans la liste.

Nous appelons le premier « Nom », le deuxième – « Nom de famille », le troisième – « Père ».

Créons une autre plage dans laquelle les noms attribués seront écrits. Appelons-le « Employés ».

Nous créons la première liste déroulante, qui sera composée des noms des plages. Sélectionnez la cellule E1 et dans l'onglet « Données » sélectionnez « Validation des données ».

Dans le champ « Type de données », sélectionnez « Liste » ; dans le champ Source, saisissez « =Employés » ou sélectionnez une plage de cellules auxquelles un nom a été attribué.

La première liste déroulante a été créée. Maintenant, dans la cellule F2, nous allons créer une deuxième liste, qui devrait dépendre de la première. Si nous sélectionnons « Nom » dans le premier, une liste de noms sera affichée dans le second ; si nous sélectionnons « Nom de famille », une liste de noms de famille sera affichée.

Sélectionnez la cellule et cliquez sur le bouton « Vérification des données ». Dans le champ « Type de données », sélectionnez « Liste » ; dans le champ source, saisissez ce qui suit : =INDIRECT($E$1). Ici, E1 est la cellule avec la première liste déroulante.

En utilisant ce principe, vous pouvez créer des listes déroulantes dépendantes.

Si à l'avenir, vous devrez saisir les valeurs dans une plage à laquelle est attribué un nom, par exemple « Nom de famille ». Accédez à l'onglet Formules et cliquez sur Gestionnaire de noms. Sélectionnez maintenant « Nom de famille » dans le nom de la plage, et ci-dessous, au lieu de la dernière cellule C3, écrivez C10. Cliquez sur la coche. Après cela, la plage augmentera et vous pourrez y ajouter des données, qui apparaîtront automatiquement dans la liste déroulante.

Vous savez maintenant comment créer une liste déroulante dans Excel.

Comment créer une liste déroulante composée de plusieurs cellules à la fois (par exemple, pour que le nom ait un coût)

Merci, tout a bien fonctionné.

Une liste déroulante avec les valeurs d'une autre feuille ne fonctionne pas, puisque la fenêtre d'ouverture de vérification des données ne permet pas de travailler avec d'autres fenêtres, surtout avec une autre feuille !

La liste déroulante dépendante vous permet de réaliser une astuce souvent saluée par les utilisateurs de modèles Excel. Une astuce qui rend le travail plus facile et plus rapide. Une astuce qui rendra vos courbes confortables et agréables.

Exemple de création d'une liste déroulante dépendante dans une cellule Excel

Un exemple d'utilisation d'une liste déroulante dépendante pour créer un formulaire pratique permettant de remplir les documents avec lesquels les vendeurs ont commandé des marchandises. Parmi l'ensemble de l'assortiment, ils devaient choisir les produits qu'ils allaient vendre.

Chaque vendeur a d'abord identifié un groupe de produits, puis un produit spécifique de ce groupe. Le formulaire doit inclure le nom complet du groupe et l'index spécifique du produit. Comme taper ceci à la main prendrait trop de temps (et serait ennuyeux), j'ai trouvé une solution très simple et rapide : 2 listes déroulantes dépendantes.

La première était une liste de toutes les catégories de produits, la seconde était une liste de tous les produits de la catégorie sélectionnée. J'ai donc créé une liste déroulante dépendante de la sélection effectuée dans la liste précédente (vous trouverez ici des informations sur la façon de créer deux listes déroulantes dépendantes).

L'utilisateur d'un modèle de budget domestique souhaite obtenir le même résultat lorsqu'il a besoin d'une catégorie et d'une sous-catégorie de dépenses. Un exemple de données est dans la figure ci-dessous :

Ainsi, par exemple, si nous sélectionnons la catégorie Divertissement, alors la liste des sous-catégories devrait inclure : Cinéma, Théâtre, Piscine. Une solution très rapide si vous souhaitez analyser des informations plus détaillées sur le budget de votre maison.

Liste des catégories et sous-catégories dans la liste déroulante dépendante d'Excel

J'avoue que dans la version de ma proposition de budget maison, je me limite à une seule catégorie, puisque pour moi une telle répartition des dépenses est largement suffisante (le nom des dépenses/revenus est considéré comme une sous-catégorie). Cependant, si vous devez les séparer en sous-catégories, la méthode que je décris ci-dessous est idéale. Sentez-vous libre de l'utiliser!

Et le résultat final ressemble à ceci :

Liste déroulante dépendante des sous-catégories

Pour y parvenir, nous devons créer un tableau de données légèrement différent de celui si nous créions une seule liste déroulante. Le tableau devrait ressembler à ceci (plage G2:H15) :

Feuille de calcul source Excel de travail

Dans ce tableau, vous devez saisir une catégorie et ses sous-catégories à côté. Le nom de la catégorie doit être répété autant de fois qu'il y a de sous-catégories. Il est très important que les données soient triées par colonne Catégorie. Cela sera extrêmement important lorsque nous écrirons la formule plus tard.

Vous pouvez également utiliser les tableaux de la première image. Bien entendu, les formules seraient différentes. Une fois, j'ai même trouvé une telle solution sur Internet, mais je ne l'ai pas aimé car elle avait une longueur de liste fixe : ce qui signifie que parfois la liste contenait des champs vides, et parfois elle n'affichait pas tous les éléments. Bien sûr, je peux éviter cette limitation, mais j'avoue que je préfère ma solution, donc je n'y suis jamais revenu.

Alors ok. Maintenant, je vais décrire les étapes de création d'une liste déroulante dépendante une par une.

1. Noms des plages de cellules

Il s’agit d’une étape facultative, sans laquelle nous pouvons la gérer sans aucun problème. Cependant, j'aime utiliser des noms car ils rendent la formule beaucoup plus facile à écrire et à lire.

Attribuons des noms aux deux plages. Liste de toutes les catégories et liste de travail des catégories. Ces plages seraient A3:A5 (liste des catégories dans la feuille de calcul verte dans la première image) et G3:G15 (liste des catégories répétitives dans la feuille de calcul violette).

Pour nommer une liste de catégories :

1. Sélectionnez la plage A3:A5.
2. Dans la zone Nom (la case à gauche de la barre de formule), entrez le nom « Catégorie ».
3. Confirmez avec la touche Entrée.

Effectuez la même action pour la plage de liste de travail de catégorie G3: G15, que vous pouvez appeler « Work_List ». Nous utiliserons cette plage dans la formule.

2. Créez une liste déroulante pour une catégorie

Ce sera simple :

1. Sélectionnez la cellule dans laquelle vous souhaitez placer la liste. Dans mon cas, c'est A12.
2. Dans le menu DONNÉES, sélectionnez l'outil de validation des données. La fenêtre « Vérifier les valeurs d'entrée » apparaît.
3. Sélectionnez Liste comme type de données.
4. Comme source, saisissez : =Catégorie (image ci-dessous).
5. Confirmez par OK.

Le résultat est le suivant :

Liste déroulante pour la catégorie.

3. Créez une liste déroulante dépendante pour une sous-catégorie

Maintenant, ce sera amusant. Nous savons comment créer des listes – nous l'avons simplement fait pour une catégorie. Juste une question : « Comment demander à Excel de sélectionner uniquement les valeurs destinées à une catégorie spécifique ? Comme vous pouvez probablement le deviner, j'utiliserai ici une feuille de calcul et, bien sûr, des formules.

Commençons par ce que nous savons déjà, c'est-à-dire en créant une liste déroulante dans la cellule B12. Sélectionnez donc cette cellule et cliquez sur Données/Validation des données et le type de données est Liste.

Dans la source de la liste, saisissez la formule suivante :

Vue de la fenêtre « Vérification des valeurs d'entrée » :

Validation des valeurs d'entrée pour une sous-catégorie dans une liste déroulante dépendante

Comme vous pouvez le voir, toute l'astuce pour une liste dépendante consiste à utiliser la fonction OFFSET. Bon, presque tout. Les fonctions MATCH et COUNTIF l'aident. La fonction OFFSET vous permet de définir dynamiquement des plages. Tout d'abord, nous définissons la cellule à partir de laquelle le changement de plage doit commencer, et dans les arguments suivants, nous définissons sa taille.

Dans notre exemple, la plage se déplacera dans la colonne Sous-catégorie de la feuille de calcul (G2:H15). Nous allons commencer à partir de la cellule H2, qui est également le premier argument de notre fonction. Dans la formule, la cellule H2 est écrite comme référence absolue car je suppose que nous utiliserons la liste déroulante dans de nombreuses cellules.

Étant donné que la feuille de calcul est triée par catégorie, la plage qui doit être la source de la liste déroulante commencera là où la catégorie sélectionnée apparaît pour la première fois. Par exemple, pour la catégorie Alimentation, nous souhaitons afficher la plage H6:H11, pour Transport - la plage H12:H15, etc. Notez que nous nous déplaçons tout le temps le long de la colonne H et que la seule chose qui change est le début. de la plage et sa hauteur (c'est-à-dire le nombre d'éléments dans la liste).

Le début de la plage sera déplacé par rapport à la cellule H2 d'autant de cellules vers le bas (en nombre) que le numéro de position de la première catégorie apparaissant dans la colonne Catégorie. Ce sera plus facile à comprendre avec un exemple : la plage de la catégorie Aliments a été déplacée de 4 cellules vers le bas par rapport à la cellule H2 (commence à partir de 4 cellules à partir de H2). Dans la 4ème cellule de la colonne Sous-catégorie (sans compter l'en-tête, puisqu'on parle d'une plage appelée Work_List), il y a le mot Nutrition (sa première apparition). Nous utilisons ce fait pour déterminer réellement le début de la plage. La fonction MATCH (introduite comme deuxième argument de la fonction OFFSET) nous servira à cet effet :

La hauteur de la plage est déterminée par la fonction COUNTIF. Elle compte toutes les occurrences de répétitions dans la catégorie, c'est-à-dire le mot Nutrition. Le nombre de fois où ce mot apparaîtra, le nombre de positions qu'il y aura dans notre gamme. Le nombre de positions dans une plage correspond à sa hauteur. Voici la fonction :

Bien entendu, les deux fonctions sont déjà incluses dans la fonction OFFSET décrite ci-dessus. Notez également que dans les fonctions MATCH et COUNTIF, il existe une référence à une plage appelée WorkList. Comme je l'ai mentionné plus tôt, vous n'êtes pas obligé d'utiliser des noms de plage, vous pouvez simplement saisir $H3:$H15. Cependant, l’utilisation de noms de plage dans la formule la rend plus simple et plus facile à lire.

C'est tout:

Téléchargez un exemple de liste déroulante dépendante dans Excel

Une formule, enfin, pas si simple, mais elle facilite le travail et protège des erreurs lors de la saisie des données !

L’élément list nous est familier grâce aux formulaires des sites Web. Il est pratique de sélectionner des valeurs toutes faites. Par exemple, personne ne saisit le mois manuellement, il est extrait d'une telle liste. Vous pouvez remplir une liste déroulante dans Excel à l'aide de divers outils. Dans cet article, nous examinerons chacun d'eux.

Comment faire une liste déroulante dans Excel

Comment créer une liste déroulante dans Excel 2010 ou 2016 à l'aide d'une seule commande de la barre d'outils ? Dans l'onglet « Données », dans la section « Utilisation des données », recherchez le bouton « Validation des données ». Cliquez dessus et sélectionnez le premier élément.

Une fenêtre s'ouvrira. Dans l'onglet « Options », dans la section déroulante « Type de données », sélectionnez « Liste ».


Une ligne apparaîtra en bas pour indiquer les sources.


Vous pouvez fournir des informations de différentes manières.

Commençons par attribuer un nom. Pour ce faire, créez un tel tableau sur n'importe quelle feuille.

Sélectionnez-le et faites un clic droit. Cliquez sur la commande « Attribuer un nom ».

Entrez votre nom dans la ligne ci-dessus.

Appelez la fenêtre « Data Check » et dans le champ « Source », précisez le nom en plaçant le signe « = » devant celui-ci.


Dans l’un des trois cas, vous verrez l’élément souhaité. La sélection d'une valeur dans une liste déroulante Excel se fait à l'aide de la souris. Cliquez dessus et une liste de données spécifiées apparaîtra.

Vous avez appris à créer une liste déroulante dans une cellule Excel. Mais on peut faire davantage.

Substitution dynamique de données Excel

Si vous ajoutez une valeur à la plage de données insérée dans la liste, aucune modification ne s'y produira jusqu'à ce que de nouvelles adresses soient spécifiées manuellement. Pour lier une plage et un élément actif, vous devez formater le premier sous forme de tableau. Créez un tableau comme celui-ci.

Sélectionnez-le et dans l'onglet « Accueil », sélectionnez n'importe quel style de tableau.


Assurez-vous de cocher la case ci-dessous.

Vous recevrez ce design.

Créez un élément actif comme décrit ci-dessus. Pour la source, entrez la formule

INDIRECT("Tableau1[Villes]")

Pour connaître le nom de la table, allez dans l'onglet Conception et regardez-le. Vous pouvez changer le nom en n'importe quel autre.


La fonction INDIRECT crée une référence à une cellule ou une plage. Maintenant, votre élément dans la cellule est lié au tableau de données.

Essayons d'augmenter le nombre de villes.


La procédure inverse – remplacer les données d’une liste déroulante par un tableau Excel – fonctionne très simplement. Dans la cellule où vous souhaitez insérer la valeur sélectionnée dans le tableau, saisissez la formule :

Adresse_cellule

Par exemple, si la liste des données se trouve dans la cellule D1, alors dans la cellule où seront affichés les résultats sélectionnés, saisissez la formule

Comment supprimer (supprimer) une liste déroulante dans Excel

Ouvrez la fenêtre des paramètres de la liste déroulante et sélectionnez « Toute valeur » dans la section « Type de données ».

L'élément inutile disparaîtra.

Éléments dépendants

Parfois, dans Excel, il est nécessaire de créer plusieurs listes lorsque l’une dépend de l’autre. Par exemple, chaque ville possède plusieurs adresses. En sélectionnant la première, nous devrions recevoir uniquement les adresses de la localité sélectionnée.


Dans ce cas, donnez un nom à chaque colonne. Sélectionnez sans la première cellule (titre) et cliquez avec le bouton droit. Sélectionnez "Nom".

Ce sera le nom de la ville.


Lorsque vous nommez Saint-Pétersbourg et Nijni Novgorod, vous recevrez une erreur, car le nom ne peut pas contenir d'espaces, de traits de soulignement, de caractères spéciaux, etc.


Nous renommerons donc ces villes avec un trait de soulignement.


Nous créons le premier élément de la cellule A9 de la manière habituelle.


Et dans la seconde on écrit la formule :

INDIRECT(A9)

Vous verrez d’abord un message d’erreur. Accepter.

Le problème est qu'il n'y a pas de valeur sélectionnée. Dès qu'une ville est sélectionnée dans la première liste, la seconde fonctionnera.

Comment configurer des listes déroulantes dépendantes dans Excel avec recherche

Vous pouvez utiliser une plage de données dynamique pour le deuxième élément. C'est plus pratique si le nombre d'adresses augmente.
Créons une liste déroulante de villes. La plage nommée est surlignée en orange.


Pour la deuxième liste, vous devez saisir la formule :

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH renvoie le numéro de la cellule avec la ville sélectionnée dans la première liste (E6) de la zone spécifiée SA:$A.
COUNTIF compte le nombre de correspondances dans une plage avec la valeur dans la cellule spécifiée (E6).

Nous avons obtenu des listes déroulantes liées dans Excel avec une condition de correspondance et une recherche par plage pour celle-ci.

Sélection multiple

Nous devons souvent obtenir plusieurs valeurs à partir d'un ensemble de données. Vous pouvez les afficher dans différentes cellules ou les combiner en une seule. Dans tous les cas, une macro est nécessaire.
Cliquez avec le bouton droit sur l'étiquette de la feuille en bas et sélectionnez Afficher le code.


La fenêtre du développeur s'ouvrira. Vous devez y insérer l'algorithme suivant.

Sous-feuille de travail privée_Change (ByVal Target As Range) en cas d'erreur, reprise suivante si aucune intersection (Target, Range ("C2: F2")) n'est rien et Target.Cells.Count = 1 Then Application.EnableEvents = False Si Len (Target.Offset (1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Veuillez noter que dans la ligne

Si ce n'est pas Intersect(Target, Range("E7")) N'est rien et Target.Cells.Count = 1 Alors

Vous devez entrer l'adresse de la cellule avec la liste. Pour nous, ce sera E7.

Revenez à votre feuille de calcul Excel et créez une liste dans la cellule E7.

Une fois sélectionnées, les valeurs apparaîtront en dessous.

Le code suivant vous permettra d'accumuler des valeurs dans une cellule.

Sous-feuille de travail privée_Change (ByVal Target As Range) En cas d'erreur, reprise suivante si aucune intersection (Target, Range ("E7")) n'est rien et Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Cible si Len (oldval)<>0 Et vieuxval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Dès que vous déplacez le pointeur vers une autre cellule, vous verrez une liste des villes sélectionnées. Pour lire cet article.


Nous vous avons montré comment ajouter et modifier une liste déroulante dans une cellule Excel. Nous espérons que ces informations vous aideront.

Passe une bonne journée!