可搜索的下拉菜单，你见过吗？2步搞定，不要太简单！

点击蓝字【秋叶 Excel】

发送【插件】

免费领 Excel 必备 4 大插件！

本文作者：小爽

本文审核：玛奇鹅

本文编辑：竺兰

大家好，我是继续挖掘 Excel 各种技巧的小爽~

在工作中，我们经常需要在 Excel 中填写一些固定选项的数据。

对于「懂点 Excel」的小伙伴来说，一般会选择用【数据验证】的功能制作下拉列表。

不过一旦数据选项过多，用下拉列表选择还是会显得比较麻烦，手还很累。

这个时候，我们就急需用到搜索式的下拉列表，就像下图这样，用关键字进行搜索：

之前我们有写过一篇文章是用传统做法做的搜索式下拉列表，不过那时吓怕了一堆人（文章见文末~）。

呐，你看~

今天，我们就来介绍一下，用 Filter 函数如何去解决这类问题~

PS：Filter 函数需要 Office 365 版本，WPS2019 及以上的版本，则自带「搜索式下拉菜单」~

传统方法需要 N 步，对吧？用 Filter 函数，我们只需要两步，一起来看看吧！

创建搜索数据对应的列表

这里我们来看一个案例，需要搜索有关「广东」的所有数据。

只要在 E3 单元格中输入公式：

▲ 左右滑动查看

呐，关于「广东」的数据，一下子就出来啦~

我们可以尝试在 C3 单元格中输入其他的省份。输入搜索内容，对应的列表就一下子出来了～

公式看起来好复杂啊，不怕！接下来我们就来一步步拆解它。

（着急看下一步的同学，也可以直接滑到 02。）

公式：

=FILTER($A$2:$A$401,ISNUMBER(FIND(C3,$A$2:$A$401)))

▲ 左右滑动查看

公式解析：

在看函数公式前，我们先来看看公式涉及函数的基本语法~

Find 函数基本语法：

Find 函数是用来返回一个字符串在另一个字符串中出现的起始位置（区分大小写）；若找不到则返回错误值。

=FIND(find_text,within_text,[start_num])=FIND（查找值，在哪里找，[从第几个字符开始查找]）

▲ 左右滑动查看

Isnumber 函数是用来判断：引用的参数或指定单元格中的值是否为数字。是的话返回 True，否则返回 False。

Filter 函数基本语法：

Filter 函数是一个筛选函数，可以将数组中条件为 True 的结果筛选出来。

▲ 左右滑动查看

公式套路：

=FILTER(搜索区域,ISNUMBER(FIND(搜索内容,搜索区域)))

▲ 左右滑动查看

PS：由于 Filter 函数不支持使用通配符，所以我们用 Find 和 Isnumber 函数来弥补这一特点。

比如说，我们要搜索 Excel 对应的数据。

❶ 我们先用 Find 函数去查找搜索区域中有关 Excel 的位置，如果没有找到返回错误值；

❷ 接着用 Isnumber 函数判断是否是数值，是的话（也就是找得到）返回 True，否的话（也就是错误值）返回 False；

❸ 最后利用 Filter 函数将筛选条件为 True 的筛选区域返回过来。

回到案例，我们直接套用公式，就可以搞定！

=FILTER($A$2:$A$401,ISNUMBER(FIND(C3,$A$2:$A$401)))

▲ 左右滑动查看

设置数据验证

搜索列表已经出来了，下一步就是要设置数据验证啦~

具体操作：

❶ 选中 C3 单元格，选择【数据】选项卡-点击「数据验证」；

❷ 弹出数据验证对话框，「允许」选择序列；「来源」选择：$E$3#；

❸ 出错警告：取消勾选「输入无效数据时显示出错警告」，点击【确定】。

动图如下：

最后的效果~

看到这里或许有小伙伴疑惑了。

❶ 在数据验证，序列来源中，$E$3 单元格中后面为什么要加个#号，它是用来干嘛的？

❷ 为什么出错警告中，要取消勾选「输入无效数据时显示出错警告」？

下面我们就来一一解答～

Q1：$E$3 中后面的#是干什么的？

A：#符号称为溢出的范围运算符，它是引用整个数组范围的表示方式。

如下图，我们直接等于索引整个区域，单元格中自动会变成 E3#。

$E$3#可以自动扩选该单元格的数组区域，所以我们可以直接用在数据验证中的序列中。

Q2：取消勾选【输入无效数据时显示出错警告】。

A：由于我们输入的数据跟序列中不一样，所以为了防止出现错误提醒，所以需要取消勾选。

不同单元格搜索式查找

前面我们只是针对一个单元格进行搜索式查找，那如果是对于不同单元格呢，我们还需要一个个进行设置嘛？

其实这个时候，我们只需要把原本搜索的单元格，改成 Cell("contents")就可以！

Cell 函数可以用来返回有关单元格的格式、位置或内容的信息。

所以不难理解，Cell("contents")这一部分的作用，就是把当前输入的单元格直接作为 Find 函数的搜索值，也就是第一参数。

最终，我们在 E3 单元格输入如下公式：

=FILTER($A$2:$A$401,ISNUMBER(FIND(CELL("contents"),$A$2:$A$401)))

▲ 左右滑动查看

效果如下：

总结一下

本文介绍了用 Filter 函数制作搜索式下拉列表的做法：

❶ 通过 Filter，Isnumber 和 Find 函数进行模糊搜索；

❷ #符号是溢出的范围运算符，单元格#可以自动扩选数组区域；

❸ Cell("contents")可以获取当前单元格的内容。

更多 Filter 函数的用法，可以戳：

传统模糊下拉列表做法，可以戳：

文中的技巧还可以用在哪些地方？大家还想要知道哪些 Excel 小妙招？

欢迎在后台与我留言，我们继续来聊个两毛钱的~也欢迎动动手点点赞~

最后，为了帮助职场的 Excel 困难户提升工作效率，用数据分析技能、图表思维提升竞争力，咱们秋叶团队推出了《3天Excel集训营》课程！

大神带你学习表格飞速排版、数据高效整理、图表美化设计……！！和志同道合的小伙伴一起交流进步~

秋叶《3 天 Excel 集训营》

但只要你是秋叶 Excel 的读者

就能限时1 元秒杀！！

仅需 3 天

你就可能成为 Excel 高手！

赶紧扫码抢课吧！！

优惠名额有限，先到先得！

现在扫码报名

还能免费领《35 个函数使用手册》！

学习打卡

点亮一下~

喜欢今天的内容

就分享给朋友吧~