打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
办公小技巧:制作模糊查询效果Excel下拉菜单

在Excel单元格中输入特定范围的内容时,一般会利用数据有效性生成下拉列表的方式进行,但如果源列表的内容太多(比方说有数百个),这时再通过下拉列表查找需要的值就很不方便了。

如果采用智能感知的模糊查询下拉列表菜单输入,随着回车之前输入字符的增加,列表中可供选择的项目在逐渐减少,这样就更便于选择。那么这样的效果如何实现呢?其实,利用辅助列、CELL函数,再结合数据有效性,这样的模糊查询效果下拉菜单就很容易实现(图1)。

首先,在名单所在工作表中插入一个辅助列,在辅助列的第一个单元格中输入“=INDEX(B:B,SMALL(IF(ISNUMBER(FIND(CELL('contents'),B$2:B$13)),ROW($2:$13),4^8),ROW(A1)))&''”,然后,同时按下Ctrl、Shift、Enter,使得输入的内容变成数组公式,拖动鼠标向下填充(图2)。

小提示:

公式中CELL('contents')省略了第二参数,直接获得最后更改单元格的值;FIND(CELL('contents'),B$2:B$13)是查询CELL('contents')的结果是否在B2:B13单元格区域存在,如果存在则返回一个位置数值,如果不存在则返回错误值;IF(ISNUMBER(FIND(CELL('contents'),B$2:B$13)),ROW($2:$13),4^8)中,ISNUMBER函数判断FIND函数的结果是否为数值,如果为数值,则返回相关值所对应的行号,如果不是数值,则返回值4^8;SMALL函数对IF函数的结果进行从小到大取数,随着公式的向下填充,依次提取第1、2、3、4、5……N个最小值,依次得到包含最后更改单元格值的单元格的行号;INDEX函数根据SMALL函数返回的索引值,得出结果。

接下来,选取需要设置下拉菜单的单元格区域,比如E2:E10;选择“数据”选项卡,点击“数据验证数据验证”,在弹出窗口“设置”选项卡中,验证条件下“允许”处选择“序列”,“来源”处选择C2:C13(即辅助列下的单元格区域);再选择“出错警告”选项卡,将“输入无效数据时显示出错警告”的勾选去掉(图3)。

这些设置完成后,当在E2:E10各个单元格中输入姓名部分文字后,下拉菜单就会显示出包含该文字的所有选项。如此输入是不是非常方便呢?

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
Excel下拉菜单太长,你还在用鼠标拖拖拖?有个技巧很强大,你还不知道!
Excel技巧应用篇:统计包含数字或非数字值的单元格数量
分享几个实用的Excel函数给你
贴心小技巧:如何检查单元格里有没有数字?
目标单元格为空白时!怎么Vlookup才能不返回0?技巧在这
Excel函数教程:信息函数(2)
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服