• 技术文章 >专题 >excel

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

    青灯夜游青灯夜游2022-06-08 11:16:23转载807
    在之前的文章《实用Excel技巧分享:制作动态的甘特图》中,我们学习了Excel制作动态甘特图的方法。今天我们来聊聊Excel数据下拉菜单,介绍一下制作高效搜索式下拉菜单的方法,快来看看!

    EXCEL手机版(内含百种各行模版):点击查看

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

    1.png

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

    有,我的方法就是搜索式下拉菜单!

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

    2.gif

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

    3.png

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

    4.png

    在来源中输入公式:

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

    5.png

    公式说明:

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

    OFFSET(参照系,行偏移量,列偏移量,新引用区域的行数,新引用区域的列数)

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

    6.png

    再回到教程当中。我们在“数据验证”对话框“来源”中输入公式后点击“确定”。但是当我们在E2单元格输入关键字“碎花”后,会立马弹出警告框,这是为什么呢?

    7.png

    原因是我们输入关键字“碎花”后,由公式得到的下拉菜单中没有只含“碎花”两个字的选项,所以会报错。

    如下图所示,我们需要再次选择E2:E6单元格区域点击【数据】选项卡【数据验证】按钮进入到“数据验证”对话框中,在“出错警告”选卡中取消勾选【输入无效数据时显示出错警告】选项,然后点击“确定”即可。

    8.png

    最后在F2单元格输入公式=IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") 。使用VLOOKUP函数在A2-B17单元格区域中查找E2数值所在位置,并返回对应的第2列(也就是B列)库存,0代表精确查找。当查找不到返回错误值时用IFERROR函数将错误值转为空。

    9.png

    至此,搜索式下拉菜单就制作完成啦!

    搜索式下拉菜单可以成倍提高数据录入效率,尤其是下拉菜单选项很多的时候特别高效。同学,赶紧打开你的excel去操作一下吧。

    10.gif

    相关学习推荐:excel教程

    以上就是实用Excel技巧分享:制作高效的搜索式下拉菜单的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:部落窝教育,如有侵犯,请联系admin@php.cn删除

    广告:Excel视频教程零基础入门到精通高级教学视频

    专题推荐:Excel
    上一篇:一文搞懂Excel的数据类型 下一篇:Excel筛选状态下的计算总结
    手机EXCEL

    相关文章推荐

    • 【活动】充值PHP中文网VIP即送云服务器• 实用Excel技巧分享:“条件格式”和“函数公式”配合使用• 实用Excel技巧分享:怎么忽略隐藏列进行求和?• 实用Excel技巧分享:16000行数据自动分组编号• 实用Excel技巧分享:如何让条形图表更生动形象!• 实用Excel技巧分享:制作一张智能的考勤表• 实用Excel技巧分享:两种快捷制作工资条的方法• 实用Excel技巧分享:制作动态的甘特图
    1/1

    PHP中文网