【温馨提示】亲爱的朋友,阅读之前请您点击【关注】,您的支持将是我最大的动力!
在Excel中有一个制作下拉菜单的功能,小编以前的教程中讲述过,既方便了录入数据,又统一了数据规范,而且也可以二级联动菜单选择。
但有时下拉菜单引用的数据很多,我们在使用下拉菜单时,拖动查找数据也是很麻烦的。
我们可以制作一个模糊查找菜单功能,当在下拉菜单单元格输入一个姓,再点击下拉菜单按钮时,包含这个姓氏的名字就显示出来了,这样菜单数据就少了,选择起来也方便了。
具体操作步骤:
1、点击【数据】选项卡中的【数据验证】按钮(其他版本【数据有效性】),在弹出的数据验证窗口中,首先选择【设置】页面,验证条件允许选择【序列】,来源输入公式:=OFFSET($A$1,MATCH("*"&C2&"*",A:A,0)-1,0,COUNTIFS(A:A,"*"&C2&"*"),1)
2、再选择【出错警告】页面,取消【输入无效数据时显示出错警告】前面的勾选,最后点击【确定】,一个带模糊搜索功能的下拉菜单就做好了。
这个功能的重点是公式,公式有些复杂,不熟悉的小伙伴可以直接套用。公式中OFFSET()函数是核心。
OFFSET()函数
【用途】以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域,并可以指定返回的行数或列数。
【语法】OFFSET(reference,rows,cols,height,width)。
【参数】Reference是作为偏移量参照系的引用区域,它必须是单元格或相连单元格区域的引用;Rows是相对于偏移量参照系的左上角单元格,上(下)偏移的行数。如果使用5作为参数Rows,则说明目标引用区域的左上角单元格比reference低5行。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方);Cols是相对于偏移量参照系的左上角单元格,左(右)偏移的列数。如果使用5作为参数Cols,则说明目标引用区域的左上角的单元格比reference靠右5列。列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边);Height是要返回的引用区域的行数,Height必须为正数;Width是要返回的引用区域的列数,Width必须为正数。
也可以这样理解:
OFFSET(起始位置,向下移动几行,向右移动几行,向下取几行,向右取几列)
公式中OFFSET函数的第二个参数:MATCH("*"&C2&"*",A:A,0)-1
查找C2单元格中内容在A列第几行
第四个参数:COUNTIFS(A:A,"*"&C2&"*"),统计A列中包含C2单元格内容的数量
通过第四个参数,要求我们的A列数据要重新排序,比如两个张姓,必须是上下在一起的,否则查找出来的就不是了。
还是抓紧动手试试吧!
小伙伴们,在使用Excel中还碰到过哪些问题,评论区留言一起讨论学习,坚持原创不易,您的点赞转发就是对小编最大的支持,更多教程点击下方专栏学习。
Exce中关于重复数据的知识你懂的,但你未必会用
Excel中鲜为人知的技巧:多条件模糊筛选,后悔没早点知道
Excel二维表格转一维,方法简单实用