大家好，我是陆水灵。

当你在工作中需要标准化的采集一类有固定选项的信息时，你一定应该使用[数据验证]来创建下拉列表。

下拉列表不仅可以规范单元格输入内容，而且快捷，看下面的图下拉菜单搜索关键字，是不是很快呢！

然而当我使用下拉列表采集员工户籍信息的时候却失败了！！！

因为户口名单太长，所以被拒绝了！！

因此，在放弃我的表格后，我的同事开始以“花哨”的方式填写表格。

其中仅广西省可以这样填写。

广西同胞们这是要气死我了、逗死我了么？

原来你们当中没有多少人知道你们省份的全名，只知道它是广西。

既然你只记得关键词，我就做一个带有关键词搜索的下拉列表吧！如下图所示：

是不是感觉很先进呢？

想知道怎么做吗？按照我的步骤，只需三步即可制作出搜索下拉菜单！

根据关键字创建辅助列

❶ 在A栏填写完整的省份列表；

❷ 创建关键字过滤的辅助列：

在B2单元格填写如下公式，使用[CTRL++]组合键结束公式，并向下填写。

公式：

▲左右滑动查看

上面的公式是不是很长，吓到你了？

虽然公式很长很难，但是你可以直接应用它。

使用方法非常简单：

因为公式中蓝色标记的4个部分一模一样：$A$2:$A$35，也就是完整省份列表所在的单元格。

因此，只需将蓝色部分替换为您要执行的列表区域！

简单地应用公式无法满足您的好奇心。

但我想知道这个公式是如何得出过滤列表的！

我！

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

现在我们要开始高能套娃了，你准备好了吗？

好的，我知道你已经准备好了！继续阅读！

公式：

▲左右滑动查看

公式解析：

要理解非常长的嵌套公式，最好将它们分解并逐步理解。

❶ CELL 功能

公式：

利用这个公式，我们可以得到最后编辑的单元格的内容下拉菜单搜索关键字，也就是我们要搜索的动态关键字。

为了便于理解，这里我们就不使用CELL函数了，而是以包含关键字“北”的搜索为例，将公式拆分成两部分。

辅助列1：

公式：B3=FIND("北",A3,1)

目的：判断是否包含关键词。

分析：FIND函数的功能是在 A3 单元格中从“河北省”的第一个字符开始查找字符串“北”，找到后返回字符“北”的位置。

“河北省”的第二个字是“北”，所以 B3 单元格显示 2，如果找不到关键字，则返回 #!。

辅助列2：

公式：C3=IF(B3>0,A3,"")

目的：将FIND的结果转换为省份名称。

分析：IF函数的作用是判断条件B3单元格2是否大于零。

若是，则A3显示“河北省”；若否，则显示空白值“”。

此时我们实际上已经获取到了包含关键字“北方”的省份列表（辅助列2）。

但是，由于它包含大量的#!，因此不能直接用作下拉菜单的列表。

所以，接下来我们需要去除错误值，并对包含关键字“北方”的省份列表重新排序。

辅助列3：

公式：D3=(C3,$A$3:$A$19,0)

目标：根据省份名称找到原始列表的相对位置。

分析：函数是返回数组单元格 A3 至 A19（即原始列表）中单元格 C3“河北省”的相对位置，匹配方式为 0（即精确匹配）。

因为河北省是原始列表 A3:A19 中的第一个值，所以结果为 1。

辅助列4：

公式：E3=(D3,)

目的：去除错误值。

分析：因为辅助列5使用了函数进行排序，但是该函数不支持错误值。

因此在这一步我们首先利用函数把错误值替换成空值“”。

辅助列5：

公式：F3=($E$3:$E$19,ROW(A1))

目的：对列表进行排序，使得包含关键词的省份位于最前面。

分析：ROW(A1)函数是获取单元格的行号，结果为1。这里的功能是建立一个以行号1，2，3增加的数列……

函数返回数组单元格 E3 至 E19（辅助列 4）中第一个最小的值，即 1。

辅助列6：

公式：G3=NDEX($A$3:$A$19,F3)

目的：根据相对行号找到对应的省份。

分析：该函数是在A3至A19单元格数组（原列表）中找出第一个单元格（F3单元格）的内容，结果为河北省。

这一步也会有很多错误值（#NUM！），也可以用空值代替。

在辅助列6中，我们获得了包含关键字的省份列表。

如果要用一列来做，只要把嵌套（功能）娃娃（数字）分组（嵌入）（嵌套）（嵌套）在一起就可以了！

要嵌套的话，需要对公式稍微做点改动，改成数组公式，这里就不多说了。

定义辅助列名

❶点击【公式】选项卡-【名称管理器】-创建新名称。

❷ 创建新名称，在名称区输入“省份列表”，在引用位置输入公式：

▲左右滑动查看

公式解析：

!$B$2:$B$35 是辅助列。

①(!$B$2:$B$35)是获取辅助列非空单元格个数，B5 单元格的公式结果虽然为空值“”下拉神器登录★64xl.com下拉优化平台，必应下拉，但依然是非空单元格。

② (!$B$2:$B$35,"")是获取辅助列“”的空值个数。

③利用函数构建动态列表区。以B2单元格为起点，向下平移0个单元格，向下移动0个单元格，长度为②-①（即关键字匹配个数），宽度为1。

这里的关键字是“北”，匹配数为3，所以整个公式的结果就是B2到B4之间的面积。

根据关键字，区域将动态变化。

设置下拉列表

❶ 选中需要设置下拉列表的单元格，点击【数据】选项卡-【数据验证】-【数据验证】；

❷ 在验证条件对话框中选择“序列”，并填写“=省份列表”作为来源；

❸ 点击“错误警告”选项卡，取消勾选“输入无效数据时显示错误警告（S）”。

结束！

总结

❶ 与多级下拉列表一样，搜索下拉列表本质上使用辅助列来创建动态下拉选项。

❷ 需要注意的是，由于设置下拉列表时取消了错误警告，数据验证无法限制填写的内容。

❸本文用到的公式虽然很长，但是好处是对版本要求不是太高，以上版本和WPS都可以用。

PS：如果是以上版本，自带“搜索下拉菜单”~

❹本文涵盖了很多函数知识点，有兴趣进一步学习的同学可以看我们之前的文章，觉得头疼的同学也可以直接照着公式抄。

这是一款实用又有趣的搜索式下拉菜单，相信你会想马上尝试制作它。

不过别急着走？继续往下看，这里还有好东西↓↓↓

职场必学技能，快速掌握升职加薪必备能力：

课程+福利总价值超416元的职场必学效率课程《如何做工作型PPT》现仅需199元！

活动倒计时仅剩1天！

同时赠送的还有价值69.8元的《说服力十足的工作PPT》新书、价值99元的专属工作打卡营、3500+页原创实用的PPT模板、700+图片制作资源、设计师必备的免费商用字体……

福利多多，买就有钱赚！想成为下一个升职加薪的人吗？扫描二维码，立即购买！

↑↑扫描二维码立即试用↑↑

本文用到了很多函数。你最想学习哪一个？

点击观看

学打卡啦~

动动你的手