Partage de compétences pratiques sur Excel : créer des menus déroulants efficaces de style recherche

Dans l'article précédent « Partage d'astuces pratiques sur Excel : Création d'un diagramme de Gantt dynamique », nous avons appris à créer un diagramme de Gantt dynamique dans Excel. Aujourd'hui, nous allons parler des menus déroulants de données Excel et présenter comment créer des menus déroulants de style recherche efficaces. Venez jeter un œil !

Au travail, nous utilisons souvent la vérification des données Excel pour créer des menus déroulants afin de standardiser la saisie des données et de gagner du temps de saisie des données. Mais lorsqu’il existe de nombreuses options de données dans le menu déroulant, il sera difficile de trouver les données. Par exemple, dans l'image ci-dessous, il y a trop d'options de données dans le menu déroulant. Il faut du temps pour « trouver » les éléments de données requis en faisant glisser la barre de défilement à côté, ce qui réduit directement notre efficacité de travail.

Alors, existe-t-il un moyen de résoudre ce problème du trop grand nombre d'options et des données difficiles à trouver ?

Oui, ma méthode est le menu déroulant de recherche !

Tout comme la recherche sur Baidu, après avoir saisi le mot-clé, un menu déroulant apparaîtra pour afficher les questions de recherche contenant le mot-clé à sélectionner. L'effet que nous souhaitons obtenir est de saisir des mots-clés dans la cellule, puis de cliquer sur le menu déroulant. Seules les données contenant les mots-clés seront affichées dans le menu, améliorant ainsi l'efficacité de la saisie des données.

L'image ci-dessous est la source de données de notre tutoriel. Notez que la source de données doit être triée par mot-clé, par ordre croissant ou décroissant.

Sélectionnez la plage de cellules E2:E6, cliquez sur l'onglet [Données], cliquez sur [Validation des données] et définissez la condition de validation sur « Séquence » dans l'onglet « Paramètres » de la fenêtre contextuelle « Validation des données ». boîte de dialogue.

Entrez la formule dans la source :

=OFFSET($A$1,MATCH(E2&"*",$A$2:$A$17,0),0,COUNTIF($A$2: $ 17 A$, E2&"*"),1)=OFFSET($A,MATCH(E2&"*",$A:$A,0),0,COUNTIF($A:$A,E2&"*"),1)

公式说明：

这里主要使用OFFSET函数返回包含关键字的数据。OFFSET函数的作用是根据指定的参照单元格，通过给定的偏移量返回新的引用数据。

OFFSET（参照系，行偏移量，列偏移量，新引用区域的行数，新引用区域的列数）

• Description de la formule :
  Ici, nous utilisons principalement la fonction OFFSET pour renvoyer des données contenant des mots-clés. La fonction de la fonction OFFSET est de renvoyer de nouvelles données de référence via le décalage donné en fonction de la cellule de référence spécifiée.
• OFFSET (système de référence, décalage de ligne, décalage de colonne, nombre de lignes dans la nouvelle zone de référence, nombre de colonnes dans la nouvelle zone de référence)

• 1. La cellule sert de cadre de référence.

• 2. Le deuxième paramètre utilise MATCH(E2&"*",$A$2:$A$17,0) pour déterminer le décalage de ligne. MATCH est une fonction de recherche. Elle recherche dans la zone $A$2:$A$17 en fonction de la valeur de recherche E2&"*" (* est un caractère générique, représentant tout caractère incertain. Le mode de recherche est 0 (recherche exacte). Lorsqu'un mot-clé est saisi dans la cellule E2, cette fonction trouvera la première occurrence des données contenant le mot-clé dans la plage $A$2 :$A$17.

• 3. Le troisième paramètre est 0, car notre source de données n'a qu'une seule colonne, la colonne A, donc le décalage de la colonne est 0, ce qui signifie aucun décalage. Pour faire simple, la fonction de décalage utilise la cellule A1 comme référence et ne décale pas horizontalement, mais uniquement vers le bas.

4. Le quatrième paramètre COUNTIF($A$2:$A$17,E2&"*") compte les cellules de la zone A2-A17 qui remplissent la condition E2&"*", c'est-à-dire les cellules qui contiennent le mots-clés dans la cellule E2 Le nombre d'occurrences, c'est-à-dire le nombre de lignes qui apparaîtront dans le menu déroulant de validation des données.

5. Le cinquième paramètre est le nombre de colonnes dans la nouvelle zone de référence. Puisqu'il n'y a que la colonne A, il est de 1.

Comme indiqué ci-dessous : la fonction de décalage utilise A1 comme système de référence pour rechercher vers le bas et utilise la fonction de correspondance pour trouver les données contenant le mot-clé E2 "fleurs cassées" dans A2-A17. La première position d'occurrence est de A2. . 10 lignes, puis utilisez la fonction countif pour trouver un total de 3 lignes, et enfin renvoyez les données de ces 3 lignes et 1 colonne dans le menu déroulant.

🎜Retour au tutoriel. Nous entrons la formule dans la "Source" de la boîte de dialogue "Validation des données" et cliquons sur "OK". Mais lorsque nous entrons le mot-clé « fleurs cassées » dans la cellule E2, une boîte d'avertissement apparaîtra immédiatement. Pourquoi ? 🎜🎜🎜🎜🎜La raison est qu'après avoir entré le mot-clé "fleur cassée", le menu déroulant obtenu par la formule n'a pas d'option contenant uniquement le mot "fleur cassée", donc une erreur sera signalée. 🎜

Comme le montre la figure ci-dessous, nous devons sélectionner à nouveau la zone de cellule E2:E6 et cliquer sur le bouton [Données] de l'onglet [Vérification des données] pour accéder à la boîte de dialogue « Vérification des données », puis décocher le bouton [Entrer] dans le Onglet "Avertissement d'erreur". Afficher un avertissement d'erreur en cas de données non valides], puis cliquez sur "OK".

Enfin, entrez la formule =SIERREUR(VLOOKUP(E2,$A$2:$B$17,2,0),"") dans la cellule F2. Utilisez la fonction RECHERCHEV pour trouver l'emplacement de la valeur E2 dans la plage de cellules A2-B17 et renvoyez l'inventaire correspondant dans la colonne 2 (c'est-à-dire que la colonne B représente une recherche précise). Lorsqu'une valeur d'erreur est introuvable et qu'une valeur d'erreur est renvoyée, utilisez la fonction SIERREUR pour convertir la valeur d'erreur en valeur nulle.

À ce stade, le menu déroulant de recherche est terminé !

Les menus déroulants de style recherche peuvent améliorer de manière exponentielle l'efficacité de la saisie des données, en particulier lorsqu'il existe de nombreuses options de menu déroulant. Camarade de classe, ouvrez rapidement votre Excel et effectuez quelques opérations.

Recommandations d'apprentissage associées : tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!