首页 > 化工知识 > Excel“搜索”式下拉菜单，无需排序，小白慎进！

Excel“搜索”式下拉菜单，无需排序，小白慎进！

时间：2021-10-20 来源： 浏览：

Excel“搜索”式下拉菜单，无需排序，小白慎进！

Excel数据可视化
Excel数据可视化

Excel1994

实用的数据可视化案例教程，源于Excel，不止于Excel

收录于话题

以下文章来源于Excel表哥 ，作者FunExcel

Excel表哥 .

Excel rapid the world!

上期我们分享了 Excel联想式下拉菜单制作教程 ，可以简化大家在下拉菜单过长时的数据录入工作。

文章末尾给大家提出了一个问题： 为什么下拉列表数据源需要先排序？

今天我们继续分享一个不用对数据源排序也能实现的下拉列表制作方法。

先看最终的效果动图：

今日分享难度指数：★★★★☆

01
创建辅助列
在需要设置下拉列表的工作表中创建一列辅助列，如下图所示：

在辅助列F3单元格输入下面的公式：
=IFERROR(INDEX(数据源!$B$3:$B$999,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),数据源!$B$3:$B$999),数据源!$B$3:$B$999,""),数据源!$B$3:$B$999,0),2^16),ROW(B1))),"")

▎注意：这是一个数组公式，输入完成后需要按Ctrl+Shift+Enter三键完成数组公式的设置。

公式三键设置结束后，会弹出循环引用的提示，忽视即可。

这个公式似乎有点长啊！不过公式虽长，我们却可以通过定义名称的形式进行缩减。

然后我们的公式就变短了：

=IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
后面如果List数据源位置调整了在名称管理器里修改也是比较简单的。
同时通过名称管理器的定义也可以方便其他使用者直接套用此公式。

将F3中的公式向下填充至足够多行即可完成辅助列的设置。

公式设置完成后我们先来测试一下：

通过测试我们发现在除了辅助列之外的任意单元格输入关键词，F列中的查找内容就会根据这些关键词进行自动更新，而这正是我们需要实现的效果。

02
设置数据有效性公式
选中需要设置数据下拉列表的区域，如举例中的B3:B10，按照下图设置数据录入工作表的数据有效性。

至此，所有设置均已完成。最终的联想式下拉列表如文章开头的动图所示。
通过测试我们发现使用这种方法设置的下拉列表不仅不需要提前对数据源进行排序而且关键词还不用区分大小写，可以说是比较方便了。
03
核心公式解释
实现联想式输入下拉列表的核心在于此公式：
=IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
CELL("contents")函数的作用是 可以获取最后编辑的单元格内容，即我们要搜索的动态关键词。

在之前的这篇文章我们有讲到过CELL函数， 戳此复习▼

为什么说聚光灯是练习单元格引用方式最好的教材

此处为了理解方便，我们将CELL("contents")换成B3单元格。
整个公式的设置其实还是一个模糊查找问题。
由于公式嵌套较多，且比较复杂，我们通过辅助列来加深理解：

  • FIND函数返回输入的字符 $B$3 在数据源!$B$3:$B$999中每一行字符的起始位置。

  • IF函数如果匹配到数据则返回数据源中该行数据，否则返回空""(,""也可省略)。

  • MATCH函数根据IF函数的结果返回在数据源中相应的行号，如果未匹配到$B$3中的关键字则返回#VALUE!错误值。

  • IFERROR函数则将#VALUE!错误值修改为一个极大值2^16=65536。

  • SMALL函数根据J列查找出来的行号进行排序。由于SMALL函数不支持错误值，这也是上一步必须用IFERROR函数修正MATCH返回结果的原因。

  • INDEX函数根据SMALL函数返回的符合项从小到大的行号查找出所有的包含关键词的选项。

  • 最后再次通过IFERROR函数屏蔽#REF!引用错误。

至此，终于完成了核心公式的设置。

最后我们把查找关键词替换为CELL("contents")就可以实现任意位置(除了辅助列)输入字符，在F列匹配出数据源中所有包含该关键词的符合项了。

至于数据验证中OFFSET函数就很好理解了，和上期分享的方法一致此处就不再赘述了。

今天分享的公式理解起来比较困难，大家可以通过构造多列辅助列的方式进行学习掌握。
同时也可以下载表哥提供的模板文件进行对照学习。
有疑问欢迎在文末留言区进行提问交流。

我是分隔线
虽然写了2千字的教程，然而WPS用户的读者朋友看了后却一脸嫌弃，难道这种联想式输入列表不应该是软件自带的吗？

仔细看，WPS确实自带联想式输入技能！

不得不说这是Excel用户非常羡慕的一个功能。那么恰好看到表哥这篇文章的Excel用户的读者朋友，希望能弥补这个小遗憾。

WPS在使用细节上面确实够人性化，类似的功能还有根据内容批量合并某一列相同单元格，Excel用户则需要借助VBA才能实现，表哥的文章也有介绍过哦：
一键批量合并Excel工作表相同内容单元格

感谢Excel表哥的干货，如果你想了解更多 Excel文件管理、项目管理模板 ，请关注下方表哥的公众号 @Excel表哥 ，在该公众号聊天框回复【 联想式下拉菜单2 】，获取本文件。

链接:https://pan.baidu.com/s/1iEEIdzZeB8w_lVdGGMXARA 

提取码:389s

版权：如无特殊注明，文章转载自网络，侵权请联系cnmhg168#163.com删除！文件均为网友上传，仅供研究和学习使用，务必24小时内删除。
相关推荐
热门信息