实用Excel技巧分享：制作高效的搜索式下拉菜单

好酷屋

发布于2023-04-18

好酷屋教程网小编为您收集和整理了实用Excel技巧分享：制作高效的搜索式下拉菜单的相关教程：在之前的文章《实用Excel技巧分享：制作动态的甘特图》中，我们学习了Excel制作动态甘特图的方法。今天我们来聊聊Excel数据下拉菜单，介绍一下制作高效搜索式下拉菜单的方法，快来看看！手机如何做表

在之前的文章《实用Excel技巧分享：制作动态的甘特图》中，我们学习了Excel制作动态甘特图的方法。今天我们来聊聊Excel数据下拉菜单，介绍一下制作高效搜索式下拉菜单的方法，快来看看！

手机如何做表格：点击查看

工作中大家经常会用Excel数据验证制作下拉菜单，规范数据输入、节省数据输入时间。但是当下拉菜单的数据选项很多的时候，就会出现数据难找的困扰。比如下图，下拉菜单中的数据选项太多，通过拖动旁边的滚动条来“找出”需要的数据项很费时，直接降低了我们的工作效率。

那有没有办法解决这种选项多数据难找的问题呢？

有，我的方法就是搜索式下拉菜单！

就如同在百度上搜索，输入关键字后会弹出下拉菜单显示包含关键字的搜索题目供选择。我们要做的效果就是在单元格中输入关键字，然后点击下拉菜单，菜单中只显示包含关键字的数据，从而提高数据录入效率。

下图是我们本次教程的数据源。注意，必须对数据源按关键字排序，升序降序都可以。

选择E2:E6单元格区域点击【数据】选项卡，单击【数据验证】，在弹出的“数据验证”对话框的“设置”选卡中设置验证条件为“序列”。

在来源中输入公式：

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

公式说明：

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

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

1.第一参数引用了A1单元格作为参照系。

2.第二参数用MATCH(E2&”*”,$A$2:$A$17,0)确定行偏移量。MATCH为查找函数，根据查找值E2&”*”(*号是通配符，代表任意不确定字符)在$A$2:$A$17区域中查找，查找方式为0（精确查找）。当E2单元格输入关键字时，该函数将查找出包含关键字的数据在$A$2:$A$17区域中第一次出现的位置。

3.第三参数为0，因为我们的数据源只有A列一列，所以列偏移量为0，表示不偏移。简单来说，就是offset函数以A1单元格为参照，不横向偏移，只向下偏移。

4.第四参数 COUNTIF($A$2:$A$17,E2&”*”)统计A2-A17区域内满足条件E2&”*”，也就是包含E2单元格内的关键词的单元格出现的次数，也就是最终在数据验证下拉菜单中一共会出现几行。

5.第五参数为新引用区域的列数，因为只有A列一列，所以为1。

如下所示：offset函数以A1为参照系向下查找，通过match函数在A2-A17中找到包含E2关键字“碎花”的数据第一次出现的位置是从A2开始的第10行，再通过countif函数找到总共有3行，最终在下拉菜单中返回这3行1列的数据。