La liste déroulante s'ouvre. Listes déroulantes associées. Substitution de données dynamique Excel

Lors du remplissage des données des cellules, il est souvent nécessaire de limiter la possibilité de saisir une liste spécifique des valeurs. Par exemple, il existe une cellule où l'utilisateur doit faire le nom du département, indiquant où cela fonctionne. Il est logique, de pré-créer une liste de départements de l'organisation et permet à l'utilisateur de choisir les valeurs dans cette liste. Cette approche aidera à accélérer le processus d'entrée et à réduire la quantité de fautes de frappe.

La liste déroulante peut être créé en utilisant

Dans cet article créera La liste déroulanteavec () avec type de données Lister.

La liste déroulante Vous pouvez vous former de différentes manières.

A. La liste déroulante la plus simple - Entrez l'élément de la liste directement dans le champ

Supposons dans la cellule B. 1 Besoin de créer la liste déroulante Entrer des unités de mesures. Nous mettons en évidence la cellule B. 1 et appeler Vérification des données.

Si dans le champ Une source Spécifiez à travers un point avec une unité de virgule pc; kg; m²; mètres cubesLe choix sera limité à ces quatre valeurs.

Maintenant, nous regardons ce qui s'est passé. Nous mettons en évidence la cellule B. 1 . Lorsque vous soulignez la cellule à droite de la cellule, un bouton carré avec une flèche apparaît pour sélectionner des éléments de la liste déroulante.

désavantages Cette approche: les éléments de liste sont faciles à perdre (par exemple, enlever une chaîne ou une colonne contenant une cellule B. 1 ); Il n'est pas pratique d'introduire un grand nombre d'articles. L'approche convient aux petites (3-5 valeurs) de listes inchangées.
Avantage: La vitesse de création d'une liste.

B. Saisie des éléments de liste dans la plage (sur la même feuille que la liste déroulante)

Les éléments de la liste déroulante peuvent être placés dans la plage sur feuille de calcul Excelpuis sur le terrain Une sourcel'outil spécifie un lien vers cette plage.

Supposons que les articles de la liste pc; kg; m²; mètres cubesentré dans la gamme de la gamme UNE. 1: UNE. 4 , alors le champ Une source contiendra \u003d list1! $ A $ 1: $ $ 4

Avantage: Liste de visualisation des éléments et simplicité de sa modification. L'approche convient à des listes changeantes rarement.
désavantages: Si de nouveaux éléments sont ajoutés, vous devez modifier manuellement le lien vers la plage. True, vous pouvez définir une plage plus large immédiatement comme source, par exemple, UNE. 1: UNE. 100 . Mais, la liste déroulante peut contenir des chaînes vides (si, par exemple, une partie des éléments a été supprimée ou que la liste vient d'être créée). Pour que les chaînes vides disparaissent, vous devez enregistrer le fichier.

Le deuxième inconvénient: la plage source doit être située sur la même feuille que la liste déroulantecar Pour les règles, il est impossible d'utiliser des liens vers d'autres feuilles ou livres (cela est vrai pour Excel 2007 et plus tôt).

Débarrassez-vous d'abord du deuxième inconvénient - postez la liste des éléments la liste déroulante Sur une autre feuille.

B. Entrez les éléments de la liste dans la plage (sur n'importe quelle feuille)

Entrez les éléments de la liste dans la gamme de cellules situées dans un autre livre

Si vous devez transférer la plage avec les éléments de la liste déroulante vers un autre livre (par exemple, dans un livre Une source.xLSX), alors vous devez faire ce qui suit:

dans le livre Une source.xLSX Créer une liste d'éléments nécessaire;
dans le livre Source.xlsx La gamme de cellules contenant la liste des éléments est donnée, par exemple Lettre de liste;
ouvrez le livre dans lequel il est censé placer des cellules avec une liste déroulante;
mettre en surbrillance la plage de cellules souhaitée, appelez l'outil , dans le champ Une source Spécifier \u003d DVSSL ("[Source.xlsx] Sheet1! Lettre de liste");

Lorsque vous travaillez avec une liste d'éléments situés dans un autre livre, fichier Une source.xLSX Doit être ouvert et situé dans le même dossier, sinon vous devez spécifier le chemin complet du fichier. Les références généralement à d'autres feuilles sont préférences pour éviter ou utiliser Livre personnel Macros Personnal.xlsx ou Superstructure.

S'il n'y a aucun désir d'attribuer le nom de la plage dans le fichier Source.xlsx, alors la formule doit être modifiée par \u003d DVSSL ("[Source.xlsx] feuille1! $ A $ 1: $ 4")

Conseil:
S'il y a beaucoup de cellules avec des règles sur une feuille Contrôles de donnéesVous pouvez utiliser l'outil ( Accueil / Trouver et en surbrillance / Sélectionner des groupes de cellules). Option Vérification des données Cet outil vous permet de sélectionner les cellules pour lesquelles la validité des données est valide (spécifiée à l'aide de la commande. Vérification des données / données / données). Lors de la sélection d'un commutateur Tout Toutes ces cellules seront surlignées. Lorsque vous choisissez une option Celles-ci même Seules ces cellules pour lesquelles les mêmes règles de vérification des données sont installées comme pour une cellule active.

Noter:
Si un la liste déroulante Contient plus de 25-30 valeurs, alors il devient gênant de travailler avec elle. La liste déroulante Dans le même temps, seuls 8 éléments s'affichent et pour voir le reste, vous devez utiliser la barre de défilement, ce qui n'est pas toujours pratique.

Excel ne fournit pas de réglage de la taille de la police La liste déroulante. Avec un grand nombre d'éléments, il est logique que la liste des éléments et utilise une classification supplémentaire d'éléments (c'est-à-dire une liste déroulante est divisée par 2 ou plus).

Par exemple, pour travailler efficacement avec la liste des employés de plus de 300 employés, il devrait d'abord être trié par ordre alphabétique. Puis créer la liste déroulantecontenant des lettres d'alphabet. Deuxième la liste déroulante Ne doit contenir que ces noms qui commencent par la lettre sélectionnée par la première liste. Pour résoudre une telle tâche, une structure ou est utilisée.

Liste déroulante dans Excel est peut-être l'un des plus façons commodes Travailler avec des données. Vous pouvez les utiliser à la fois lors de la remplacement des formulaires et de la création de tableaux de bord et de tables volumétriques. Les listes déroulantes sont souvent utilisées dans des applications sur les smartphones, les sites Web. Ils sont intuitivement compréhensibles à l'utilisateur ordinaire.

Cliquez sur le bouton ci-dessous pour télécharger le fichier avec des exemples de liste déroulante dans Excel:

Didacticiel vidéo

Comment créer une liste déroulante dans Excele en fonction des données de la liste

Imaginez que nous avons une liste de fruits:

Pour créer une liste déroulante, nous devons procéder comme suit:

Aller à l'onglet " Données "\u003d\u003e Section" Travailler avec des données "Dans la barre d'outils \u003d\u003e Sélectionner l'élément" Vérification des données “.

Dans la fenêtre contextuelle " Vérifiez les valeurs d'entrée "Sur l'onglet" Paramètres "Dans le type de données à choisir" Lister “:

Dans le champ " Une source "Entrez dans la gamme de noms de fruits \u003d $ A $ 2: $ $ 6 $ Ou simplement mettre le curseur de la souris dans le champ de saisie des valeurs " Une source "Et puis la plage de données de sélection de la souris:

Si vous souhaitez créer des listes déroulantes dans plusieurs cellules à la fois, sélectionnez toutes les cellules dans lesquelles vous souhaitez les créer, puis suivez les étapes ci-dessus. Il est important de s'assurer que les références de cellules sont absolues (par exemple, $ A $ 2 ), pas relatif (par exemple, A2. ou alors Un $ 2. ou alors $ A2. ).

Comment faire une liste déroulante dans Excel à l'aide de la saisie de données manuelle

Sur l'exemple ci-dessus, nous avons entré une liste de données pour la liste déroulante en sélectionnant la plage de cellules. En plus de cette méthode, vous pouvez entrer des données pour créer une liste déroulante manuellement (éventuellement les stockées dans toutes les cellules).

Par exemple, imaginez que dans le menu déroulant, nous voulons refléter les deux mots "oui" et "non". Pour ce faire, nous avons besoin de:

Sélectionnez la cellule dans laquelle nous souhaitons créer une liste déroulante;
Aller à l'onglet " Données "\u003d\u003e Section" Travailler avec des données "Dans la barre d'outils \u003d\u003e Sélectionner l'élément" Vérification des données “:

Dans la fenêtre contextuelle " Vérifiez les valeurs d'entrée "Sur l'onglet" Paramètres "Dans le type de données à choisir" Lister “:

Dans le champ " Une source"Entrez la valeur" Oui; Pas".
Cliquez sur " d'accord “

Après cela, le système créera une liste déroulante dans la cellule sélectionnée. Tous les éléments énumérés dans le champ " Une source ", Séparé d'un point-virgule sera reflété dans différentes lignes du menu déroulant.

Si vous souhaitez créer simultanément une liste déroulante dans plusieurs cellules - sélectionnez les cellules souhaitées et suivez les instructions ci-dessus.

Comment créer une liste déroulante pour exceller à l'aide de la fonction de déplacement

Avec les méthodes décrites ci-dessus, vous pouvez également utiliser la formule pour créer des listes déroulantes.

Par exemple, nous avons une liste avec une liste de fruits:

Pour effectuer la liste déroulante à l'aide de la formule, vous devez procéder comme suit:

Sélectionnez la cellule dans laquelle nous souhaitons créer une liste déroulante;
Aller à l'onglet " Données "\u003d\u003e Section" Travailler avec des données "Dans la barre d'outils \u003d\u003e Sélectionner l'élément" Vérification des données “:

Dans la fenêtre contextuelle " Vérifiez les valeurs d'entrée "Sur l'onglet" Paramètres "Dans le type de données à choisir" Lister “:

Dans le champ " Une source "Entrez la formule: \u003d Changements (un $ 2 $; 0; 0; 5)
presse d'accord “

Le système créera une liste déroulante avec une liste de fruits.

Comment fonctionne cette formule?

Par exemple, nous avons utilisé la formule (Système; district_po_sets; Divalence_op_stolbans; [hauteur]; [largeur]).

Cette fonctionnalité contient cinq arguments. Dans l'argument " relier"(Dans l'exemple, $ a 2 $), il est indiqué à partir de quelle cellule démarrer le décalage. Dans les arguments "Jaucheuse_PPORT_STALKAM"JE. "Disposé_ppie_stolbans" (Dans l'exemple, la valeur est "0") - pour le nombre de lignes / colonnes que vous devez être déplacées pour afficher les données. Dans l'argument " [la taille]"La valeur" 5 "est indiquée, qui dénote la hauteur de la gamme de cellules. Argument " [largeur]«Nous ne spécififions pas, car dans notre exemple, la plage est constituée d'une colonne.

En utilisant cette formule, le système vous retourne en tant que données pour la liste déroulante des cellules, en commençant par une cellule $ A $ 2, composée de 5 cellules.

Comment effectuer une liste déroulante dans Excel avec une substitution de données (à l'aide de la fonction de déplacement)

Si vous utilisez pour créer une formule de liste sur l'exemple ci-dessus, vous créez une liste de données fixées dans une plage spécifique de cellules. Si vous souhaitez ajouter une valeur en tant qu'élément de liste, vous devrez régler la formule manuellement. Ci-dessous, vous apprendrez à créer une liste déroulante dynamique, qui chargera automatiquement de nouvelles données à afficher.

Pour créer une liste, vous aurez besoin de:

Sélectionnez la cellule dans laquelle nous souhaitons créer une liste déroulante;
Aller à l'onglet " Données "\u003d\u003e Section" Travailler avec des données "Dans la barre d'outils \u003d\u003e Sélectionner l'élément" Vérification des données “;
Dans la fenêtre contextuelle " Vérifiez les valeurs d'entrée "Sur l'onglet" Paramètres "Dans le type de données à choisir" Lister “;
Dans le champ " Une source "Entrez la formule: \u003d Changements (un $ 2 $; 0; 0; résultant ($ a 2 $: 100 $; 100 $; "<>”))
presse d'accord “

Dans cette formule, dans l'argument "[ la taille] "Nous soulignons comme un argument qui dénote la hauteur de la liste avec les données - la formule qui calcule dans la plage spécifiée A2: A100 Le nombre de cellules non vides.

Noter: Pour le bon fonctionnement de la formule, il est important que dans la liste des données affichent dans le menu déroulant, il n'y avait pas de lignes vides.

Comment créer une liste déroulante dans Excel avec la substitution automatique des données

Pour la liste déroulante que vous avez créée, de nouvelles données sont automatiquement chargées, vous devez procéder comme suit:

Créez une liste de données à afficher dans la liste déroulante. Dans notre cas, c'est une liste de couleurs. Nous mettons en évidence la liste du bouton gauche de la souris:

Dans la barre d'outils, appuyez sur l'élément " Format comme une table “:

Dans le menu déroulant, sélectionnez le style de conception de la table:

En appuyant sur la touche d'accord "Dans la fenêtre contextuelle, confirmez la gamme sélectionnée de cellules:

Ensuite, sélectionnez la plage de données de la table pour la liste déroulante et attribuez un nom dans le champ gauche au-dessus de la colonne "A":

La table avec données est prête, nous pouvons maintenant créer une liste déroulante. Pour cela, vous avez besoin de:

Sélectionnez la cellule dans laquelle nous souhaitons créer une liste;
Aller à l'onglet " Données "\u003d\u003e Section" Travailler avec des données "Dans la barre d'outils \u003d\u003e Sélectionner l'élément" Vérification des données “:

Dans la fenêtre contextuelle " Vérifiez les valeurs d'entrée "Sur l'onglet" Paramètres "Dans le type de données à choisir" Lister “:

Dans le champ source indiquer \u003d "Nom de votre table" . Dans notre cas, nous l'avons appelé " Lister “:

Prêt! La liste déroulante est créée, il affiche toutes les données de la table spécifiée:

Pour ajouter une nouvelle valeur à la liste déroulante - ajoutez simplement des informations à la suivante une après la table avec les données de la cellule:

La table élargira automatiquement sa plage de données. La liste déroulante sera reconstituée avec une nouvelle valeur de la table:

Comment copier la liste déroulante dans Excel

Excel a la possibilité de copier les listes déroulantes créées. Par exemple, dans la cellule A1, nous avons une liste déroulante que nous voulons copier dans la gamme de cellules. A2: A6. .

Afin de copier la liste déroulante avec le formatage actuel:

appuyez sur le bouton gauche de la souris de la cellule avec la liste déroulante que vous souhaitez copier.
Ctrl + C. ;
sélectionnez les cellules dans la plage A2: A6. dans lequel vous souhaitez insérer la liste déroulante;
appuyez sur le clavier du clavier Ctrl + V. .

Donc, vous copiez la liste déroulante en sauvegardant format original Liste (couleur, police i.t.d). Si vous souhaitez copier / insérer une liste déroulante sans enregistrer le format, alors:

appuyez sur le bouton gauche de la souris de la cellule avec la liste déroulante que vous souhaitez copier.
appuyez sur le clavier du clavier Ctrl + C. ;
sélectionnez la cellule sur laquelle vous souhaitez insérer la liste déroulante;
appuyez sur le bouton droit de la souris \u003d\u003e appelez le menu déroulant et cliquez sur " Insertion spéciale “;

Dans la fenêtre qui apparaît dans la section " Insérer "Sélectionner" conditions sur le sens “:

Cliquez sur " d'accord “

Après cela, Excel copie uniquement les données de la liste déroulante, sans enregistrer la mise en forme de la cellule d'origine.

Comment mettre en évidence toutes les cellules contenant une liste déroulante dans Excel

Parfois, il est difficile de comprendre combien de cellules dans fichier Excel Contenir des listes déroulantes. Il y a un moyen facile de les afficher. Pour ça:

Cliquez sur l'onglet " le principal "Sur la barre d'outils;
Cliquez sur " Trouver et mettre en évidence "Et sélectionnez" Sélectionnez un groupe de cellules “:

Dans la boîte de dialogue, sélectionnez " Vérification des données ". Dans ce domaine, il y a une occasion de choisir des points " Tout "Et" Ces mêmes “. “Tout "Vous permet de mettre en évidence toutes les annonces déroulantes sur la feuille. Article " ces mêmes "Affiche les listes déroulantes similaires au contenu des données dans le menu déroulant. Dans notre cas, nous avons choisi " tout “:

Dans cet article, examinez comment créer liste déroulante dans Excel 2007. Prenez un exemple lorsque nous devons sélectionner les valeurs spécifiées de 1 à 5 dans la liste déroulante de la cellule. Créez une liste elle-même et mettez-la en surbrillance avec le bouton gauche de la souris. Cliquez sur clic-droit Souris dans la zone sélectionnée et sélectionnez le nom de la plage.

Dans la fenêtre qui s'ouvre dans le champ Nom, nous entrons le nom de notre liste, appelons appelons Valeur. Dans la zone Champ de la liste déroulante, sélectionnez Livre (ou le numéro de la feuille à laquelle vous souhaitez appliquer la liste). Cliquez sur OK.

De plus, pour définir le nom de la liste, vous devez sélectionner la liste disponible et dans le champ Nom de la cellule, attribuer le nom de la liste. Nous examinons la photo ci-dessous.

La liste est créée. Maintenant, nous appliquons cette liste à la cellule.

Nous mettons en évidence la cellule sur laquelle la liste sera liée. Dans la bande, allez à l'onglet Données et dans le groupe. Travailler avec des données Cliquez sur le bouton Vérification des données. Dans la fenêtre suivante de l'onglet Options dans le champ Type de données, sélectionnez l'élément de la liste. Lister.

Sur le terrain, la source met le signe "égal" et écrire un nom qui a été attribué à la liste. La liste s'appelle "valeur". En conséquence, l'enregistrement doit être comme indiqué dans la figure ci-dessous.

Cliquez sur OK, et maintenant, ils ont une cellule avec une liste déroulante. À quoi ressemble, regardez ci-dessous dans la photo. Lorsque vous cliquez sur la cellule, nous voyons un carré avec un triangle vers le bas. Cliquez sur la place et révélez ainsi la liste.

Vous pouvez créer, contourner le nom du nom de la liste. C'est à dire:

créer une liste elle-même;
allez sur la bande à l'onglet Données, cliquez sur le bouton Vérification des données;
dans la fenêtre qui s'ouvre, dans l'onglet Paramètres dans le champ Type de données, sélectionnez Lister;
dans le champ source, cliquez sur le bouton gauche de la souris pour activer ce champ. Ensuite, nous allouons les cellules formant la liste;
cliquez sur OK.

Tout, liste déroulante dans Excel 2007 Prêt.

Si vous remplissez le tableau dans Excel et que les données de la colonne peuvent parfois être répétées, par exemple, le nom des marchandises, ou le nom de l'employé, afin de ne pas entrer le paramètre souhaité Chaque fois, il est plus facile et plus facile de créer une liste déroulante une fois et de choisir une valeur.

Dans l'article, nous examinerons comment effectuer des listes déroulantes de différents types dans la table Excel.

Créer une liste déroulante simple

Pour cela, dans les cellules A1: A7 Entrez les données qui seront affichées dans la liste. Sélectionnez maintenant la cellule dans laquelle nous allons créer la liste déroulante - B2.

Accédez à l'onglet "Data" et cliquez sur le bouton "Vérification des données".

Dans l'onglet "Paramètres", dans le champ Type de données, sélectionnez "Liste". Dans le champ "Source", vous pouvez entrer des valeurs de différentes manières:

1 - Entrez les valeurs de la liste manuellement, à travers le point avec une virgule;

2 - Spécifiez la plage de cellules dans laquelle les données sont entrées pour la liste déroulante;

3 - Sélectionnez les cellules avec des noms, cliquez dessus avec le bouton droit de la souris et sélectionnez dans le menu "Attribuer un nom".

Nous mettons en valeur la cellule B2 et dans le champ "Source" "\u003d", puis écrivez le nom créé.

Ainsi, nous avons créé une simple liste déroulante dans Excel.

Si vous avez une en-tête de colonne et que les valeurs doivent remplir chaque ligne, sélectionnez PAS une cellule, mais la gamme de cellules - B2: B9. Ensuite, vous pouvez choisir parmi la liste déroulante la valeur souhaitée dans chaque cellule.

Ajouter des valeurs à la liste déroulante - Liste dynamique

Dans le même temps, nous ajouterons des valeurs à la plage souhaitée et elles seront automatiquement ajoutées à la liste déroulante.

Sélectionnez la plage de cellules - D1: D8, puis sur l'onglet Home, cliquez sur "Format comme table" et sélectionnez n'importe quel style.

Confirmez l'emplacement des données et mettez une tique dans le champ "Table avec des titres".

Au sommet, nous écrivons un titre de table - "Employés" et remplissez-le avec des données.

Nous mettons en valeur la cellule dans laquelle la liste déroulante et cliquez sur le bouton "Vérification des données". Dans la fenêtre suivante, dans le champ "Source", nous écrivons les éléments suivants: \u003d DVSSL ("Table1"). J'ai une table sur la feuille, alors j'écris "Table1", s'il y a une seconde - "Table2", etc.

Ajoutez maintenant un nouveau nom d'employé à notre liste: IRA. Dans la liste déroulante, il est apparu. Si nous supprimons n'importe quel nom de la table, il sera également supprimé de la liste.

Liste déroulante avec des valeurs à partir d'une autre feuille

Si une table avec des listes déroulantes est sur une feuille et que les données de ces listes sont différentes, cette fonction nous aidera beaucoup.

Sur une feuille 2, sélectionnez une cellule ou une plage de cellules, puis cliquez sur le bouton "Vérification des données".

Allez sur la feuille 1, nous mettons le curseur dans le champ "source" et mettez en surbrillance la plage de cellules souhaitée.

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

Créer des listes déroulantes dépendantes

Supposons que nous ayons trois gammes: noms, noms de famille et personnel patronymique. Pour tout le monde, vous devez attribuer un nom. Nous mettons en évidence les cellules des données, vous pouvez et vide - en eux, avec le temps, vous pouvez ajouter des données à apparaître dans la liste déroulante. Je clique dessus avec le bouton droit de la souris et choisissez à partir de la liste "Attribuer un nom".

Le premier appelez le "nom", le second - "nom de famille", troisième - "message".

Nous ferons une autre plage dans laquelle les noms attribués seront épelés. Appelons-lui des "employés".

Nous faisons la première liste déroulante qui consistera aux noms des gammes. Nous sélectionnons la cellule E1 et sélectionnez "Vérification des données" dans l'onglet Données.

Dans le champ Type de données, sélectionnez "Liste", dans le champ source - ou entrez "\u003d employés" ou sélectionnez la plage des cellules auquel le nom est attribué.

La première liste déroulante est créée. Maintenant, dans la cellule F2 créera une deuxième liste qui devrait dépendre du premier. Si dans la première sélection, le "nom", la liste des noms sera affichée dans la seconde si vous choisissez le nom "Connexion" - Liste des noms de famille.

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, nous prescrivons les éléments suivants: \u003d DVSSL ($ 1 $). Ici E1 est une cellule avec la première liste déroulante.

Par ce principe, vous pouvez effectuer des listes déroulantes dépendantes.

Si à l'avenir, vous devrez entrer des valeurs dans la plage à laquelle le nom est spécifié, par exemple, le "nom de famille". Cliquez sur l'onglet "Formule" et cliquez sur Nom Manager. Maintenant, au nom de la plage, choisissez «Nom de famille», et ci-dessous, au lieu de la dernière cellule C3, écrivez C10. Appuyez sur la tique. Après cela, la plage augmentera, ce qui peut y ajouter des données qui apparaîtront automatiquement dans la liste déroulante.

Vous savez maintenant comment effectuer la liste déroulante dans Excel.

Comment créer une liste déroulante composée immédiatement de plusieurs cellules (disons que le nom était avec le coût)

Merci, tout a bien fonctionné.

La liste déroulante avec les valeurs d'une autre feuille ne fonctionne pas, car la fenêtre lorsque les données sont ouvertes ne vous permettent pas de travailler avec d'autres fenêtres, en particulier avec une autre feuille!

La liste déroulante dépendante vous permet de faire une astuce que les utilisateurs sont très souvent loués. modèles Excel. L'astuce qui facilite le travail et plus vite. L'astuce, grâce à laquelle vos formes seront confortables et agréables.

Un exemple de création d'une liste déroulante dépendante dans la cellule Excel

Un exemple d'utilisation de la liste déroulante dépendante afin de créer une forme commode de documents de remplissage, avec lesquels les vendeurs ont commandé des marchandises. De toute la gamme, ils ont dû choisir les produits qu'ils allaient vendre.

Chaque vendeur a d'abord déterminé le groupe de produits de base, puis un produit spécifique de ce groupe. Le formulaire doit inclure le nom complet du groupe et un index spécifique du produit. Comme il serait trop chronométrant pour le taper manuellement, j'ai proposé une solution très rapide et simple - 2 liste déroulante dépendante.

La première était la liste de toutes les catégories de produits, la seconde est une liste de tous les produits situés dans la catégorie sélectionnée. Par conséquent, j'ai créé une liste déroulante dépendante de la sélection effectuée dans la liste précédente (vous trouverez ici un matériau sur la manière de créer une liste déroulante deux dépendants).

Le même résultat veut obtenir un utilisateur de modèle budget Où vous avez besoin d'une catégorie et de sous-catégories de dépenses. Un exemple des données est dans la figure ci-dessous:

Par exemple, si nous choisissons la catégorie de divertissement, la sous-catégorie doit être dans la liste: cinéma, théâtre, piscine. Très décision rapideSi vous souhaitez analyser des informations plus détaillées dans votre budget de maison.

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

J'avoue que dans le budget de la maison proposé par moi, je limite la catégorie, car pour moi, cette séparation est suffisante (le nom / revenu / revenu est considéré comme une sous-catégorie). Toutefois, si vous devez les diviser sur la sous-catégorie, la méthode que je décris ci-dessous sera parfaite. Utilisez hardiment!

Et le résultat final est le suivant:

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

Pour y parvenir, vous devez effectuer une table de données légèrement différente que si nous avons créé une liste déroulante. La table devrait ressembler à ceci (gamme G2: H15):

Table de sources de travail Excel

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

Il serait également possible d'utiliser les tables de la première image. Bien sûr, les formules seraient différentes. Une fois même j'ai trouvé une telle décision sur le réseau, mais je ne l'aimais pas, car il y avait une longueur de liste fixe: et par conséquent, la liste contenait parfois des champs vides et parfois, il n'a pas affiché tous les éléments. Bien sûr, je peux éviter cette limitation, mais j'avoue que j'aime davantage ma décision, donc je ne suis plus retourné à la décision.

Tant pis. Maintenant, à son tour, je vais décrire les étapes pour créer une liste déroulante dépendante.

1. Noms des plages de cellules

Ceci est une étape facultative, sans cela, nous pouvons facilement faire face à cela. Cependant, j'aime utiliser des noms, car ils facilitent grandement l'écriture et la lecture de la formule.

Nous attribuons des noms à deux gammes. Liste de toutes les catégories et catégories de la liste de travail. Ce seront les plages A3: A5 (liste des catégories dans la table verte de la première image) et G3: G15 (liste des catégories répétées dans la table de travail pourpre).

Afin de nommer une liste de catégories:

Sélectionnez la plage A3: A5.
Dans le champ Nom (champ à gauche de la chaîne de formule), entrez le nom "Catégorie".
Confirmez avec la touche Entrée.

La même action est faite pour la catégorie G3 Catégories du G3: G15, que vous pouvez appeler le "travailleur_stand". Nous allons utiliser cette gamme dans la formule.

2. Créer une liste déroulante pour la catégorie

Ce sera simple:

Sélectionnez la cellule sur laquelle vous souhaitez placer la liste. Dans mon cas, c'est A12.
Dans le menu DATA, sélectionnez l'outil de vérification des données. La fenêtre "Vérifier l'entrée introduite" apparaît.
En termes de données de qualité, sélectionnez la liste.
Entrez: \u003d catégorie (figure ci-dessous).
Confirmer avec OK.

Le résultat est le suivant:

Liste déroulante pour la catégorie.

3. Création d'une liste déroulante dépendante pour les sous-catégories

Maintenant ce sera amusant. Nous savons comment créer des listes - juste le faire pour la catégorie. Seule la seule question: "Comment dire Excelle Choisissez uniquement les valeurs destinées à une catégorie particulière?" Comme vous devinez probablement, je vais utiliser une table de travail ici et, bien sûr, formules.

Commençons par le fait que nous sommes déjà capables, c'est-à-dire avec la création de la liste déroulante dans la cellule B12. Par conséquent, sélectionnez cette cellule et cliquez sur "Données" / "Vérification des données" et en tant que type de données - "Liste".

Entrez la formule suivante à la source de liste:

Vue de la fenêtre "Vérifier les valeurs d'entrée":

Vérifiez les valeurs d'entrée des sous-catégories dans la liste déroulante dépendante

Comme vous pouvez le constater, tout le truc de la liste dépendante consiste à utiliser la fonction de déplacement. Eh bien, presque tous. Aide les fonctions de la carte de recherche et le résultat. La fonction de déplacement vous permet de définir dynamiquement les gammes. Premièrement, nous définissons la cellule à partir duquel le changement de plage devrait commencer et dans les arguments ultérieurs, nous déterminons ses dimensions.

Dans notre exemple, la plage sera déplacée dans la colonne Sous-catégorie de la table de travail (G2: H15). Le déplacement commencera à partir de la cellule H2, qui est également le premier argument de notre fonction. Dans la formule, la cellule H2 enregistrée comme une liaison absolue, car nous supposons que nous utiliserons la liste déroulante dans de nombreuses cellules.

Étant donné que la table de travail est triée par la catégorie, la plage de la source doit être la source de la liste déroulante commencera où la catégorie sélectionnée est trouvée pour la première fois. Par exemple, pour la catégorie de nutrition, nous voulons afficher la plage H6: H11, pour le transport - la gamme H12: H15, etc. Veuillez noter que tout le temps que nous déplacons le long de la colonne H et la seule chose qui change est Le début de la plage et sa hauteur (c'est-à-dire le nombre d'éléments de la liste).

Le début de la plage sera déplacé par rapport à la cellule H2 sur un tel nombre de cellules vers le bas (par numéro), combien coûte le numéro de position de la première catégorie rencontrée dans la colonne de catégorie. Il sera plus facile de comprendre à l'aide de l'exemple: la plage de la puissance de la catégorie est déplacée vers 4 cellules par rapport à la cellule H2 (commence par 4 cellules de H2). Dans la 4ème cellule de la colonne Sous-catégorie (n'incluant pas le titre, car nous parlons de la gamme avec le nom du travailleur_stand), il existe une nutrition de mot (sa première apparition). Nous utilisons ce fait en fait pour déterminer le début de la gamme. Cela nous servira pour cette fonction de la carte de recherche (entrée en tant que deuxième argument de la fonction de déplacement):

La hauteur de la gamme détermine la fonction du résultat. Elle considère toutes les répétitions rencontrées dans la catégorie, c'est-à-dire le mot nutrition. Combien de fois ce mot est ce mot, combien et il y aura des postes dans notre gamme. Le nombre de positions dans la gamme est sa hauteur. Voici une fonction:

Bien entendu, les deux fonctions sont déjà incluses dans la fonction du déplacement, décrite ci-dessus. De plus, notez que les deux dans les fonctions de la recherche et, dans le résultat, il existe un lien vers la gamme appelée travailleur_stand. Comme je l'ai mentionné plus tôt, il n'est pas nécessaire d'utiliser les noms des gammes, vous pouvez simplement entrer $ H3: $ H15. Cependant, l'utilisation des noms des gammes dans la formule facilite et facilement lisible.

C'est tout:

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

Une formule, eh bien, pas si simple, mais facilitant un travail et protège contre les erreurs lors de la saisie de données!

Très souvent, ceux qui nourrissent certaines données ou remplissent simplement la table, vous devez marquer les données répétées. Afin de ne pas marquer la même chose, pour ces cas, Excel dispose d'une liste d'outils de liste déroulante. Nous regardons comment le faire

Faire étape par étape

Comment ça marche - vous formez une liste à l'avance, puis dans certains champs, sélectionnez la valeur souhaitée dans la liste, la souris. Scies de temps et de nerfs.

Il est simplement mis en œuvre:

Premièrement former la liste de données elle-même.

Allouer ces domaines qui envisagent de remplir des données.

Ensuite, entrez dans l'onglet Data, la commande de vérification de données - le type de données (liste) dans le champ source définit la plage.

Si vous envisagez de compléter la liste des nouvelles données, laissez la plage inclut également un nombre ennuyant de cellules sous la liste.

Option alternative - pas une plage, mais le nom de la gamme. Dans ce cas, il est gagné de la probabilité d'augmenter la liste plus que celle spécifiée dans la liste. Il suffit de vous assurer que les nouvelles valeurs que vous conduisez dans la gamme sont vraiment incluses.

Marquez la case à cocher «Ignorer les cellules vides».

Maintenant, lors de la sélection d'une cellule à partir de ceux à laquelle la commande a été appliquée à droite apparaîtra sur le bouton fléchant.

Nuance - avec cette méthode dans la cellule, vous pouvez entrer uniquement les valeurs contenues dans la liste.

Nuances

La liste déroulante est la propriété de la cellule. Et par conséquent, si vous copiez une autre cellule et que vous insérez dans celle où il existe une liste déroulante, les propriétés de la cellule seront couvertes et la liste déroulante disparaîtra. En conséquence, si vous avez besoin de la même liste déroulante dans une autre cellule, vous pouvez simplement copier la cellule et insérer à l'adresse souhaitée (ou via une insertion spéciale, vous devez copier les "conditions sur les valeurs").

Après avoir rempli les champs, si vous ne planifiez plus rien, vous pouvez supprimer la fonction de la liste déroulante (il arrive que la flèche évite visuellement lors de la collaboration avec des cellules). Il est fait comme ça - la plage est allouée, où la liste déroulante n'est plus nécessaire, la même commande, mais vous appuyez déjà sur le bouton "Tout effacer". Ces données se poursuivront.

Deuxième manière

Si vous ne remplissez pas les données répétées et que vous souhaitez faire quelque chose comme une application, car Une table où les champs sont arbitrataires avec des listes, il est préférable d'utiliser un autre moyen de créer une liste déroulante.

Pour ce faire, allez à l'onglet Développeur (Excel 2007-2010). Par défaut, cet onglet n'est pas affiché. Pour l'ouvrir, vous devez accéder aux paramètres (Excel Office / Paramètres / Main / Afficher l'onglet "Developer" sur la bande).

Sur l'onglet Développeur, il existe un bouton "INSERT", où il existe des éléments de forme. Nous avons besoin d'un "champ avec une liste".

Circuit la touche du bouton, puis cliquez avec le bouton droit de la souris sur le champ résultant et sélectionnez le "format d'objet".

Dans le premier champ, vous choisissez la gamme.
Dans le deuxième champ, vous pouvez mettre une cellule où le numéro de séquence de l'élément sera apposé. Il peut être utile lorsque vous traitez ensuite des données.
Vous pouvez également mettre une liste d'éléments qui seront affichés. DANS méthode précédente Seuls 8 éléments de sélection sont automatiquement donnés, bien que le réglage soit plus compliqué, mais les avantages sont plus.

Cliquez ensuite sur "OK" et utilisez-le.

"Les yeux ont peur et les mains font"