如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)

【数据验证】是Excel2013版本中【数据】选项卡-数据工具组里面的一个功能,在Excel2013之前的版本叫做【数据有效性】。

使用数据验证可以限制用户录入规范数据,节省录入时间,确保数据录入的正确性。在Excel中用数据验证制作下拉菜单是大家经常用的一个功能,可以快速准确的选择要录入的数据,并且可以根据自身需要制作一级下拉菜单、二级下拉菜单或动态下拉菜单等。


下拉菜单虽然好用,但是当数据源过多的情况下,就会出现找数据要找很久的困扰,比如下图,下拉菜单中的数据太多,需要拖动旁边的滚动条去选择数据,则会拖延我们的工作效率。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(1)

遇到上述情况,我们可以用excel制作一个模糊的搜索式下拉菜单,通过在单元格输入关键字,则下拉菜单显示包含关键字的数据。像这种搜索式下拉菜单其实我之前已经发布过一篇了,大家可以点击查看 用excel也能做出像百度搜索框一样的下拉菜单,数据录入更方便!

但是之前发布的教程是有缺陷的,就是必须要将数据源按关键字排序才能实现。本次教程就是为了解决这个缺陷,可以不用对数据源排序,且关键字可以是数据中的任意位置,如下效果图。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(2)

接下来我们就来学习下如何实现这个效果,下图是本次教程的数据源。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(3)

此时我们需要创建一列辅助列,在D2单元格输入公式:=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),$A$2:$A$17)),ROW($2:$17)),ROW(A1))),"")

此公式为数组公式,需按Ctrl Shift Enter键结束公式,拖动公式填充到D17单元格,按三键后会弹出如下图提示循环引用,直接点击确定即可。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(4)

公式说明:

CELL("contents") 公式中主要就是这一部分,CELL第一个参数使用contents 表示引用左上角单元格的值,如下图,输入公式:=CELL("contents",A1:A4) 则表示引用A1:A4区域中左上角单元格的值,也就是A1单元格,返回“小螃蟹”。然而我们输入的公式是:CELL("contents") 忽略了第二个参数,则引用当前单元格的值,所以才会提示循环引用。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(5)

FIND(CELL("contents"),$A$2:$A$17) 使用FIND查找CELL函数的结果在$A$2:$A$17区域中出现的字符位置返回一个数值,如果在$A$2:$A$17区域中没有出现过则返回错误值。

IF(ISNUMBER(FIND(CELL("contents"),$A$2:$A$17)),ROW($2:$17)) 用IF函数嵌套ISNUMBER函数判断FIND函数的结果是否为数字,是的话则返回数字对应的行号。

SMALL(IF(ISNUMBER(FIND(CELL("contents"),$A$2:$A$17)),ROW($2:$17)),ROW(A1))) 用SMALL函数根据得出的行号提取第一个最小值,通过公式往下填充依次提取第二、第三……最小值。

IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),$A$2:$A$17)),ROW($2:$17)),ROW(A1))),"") 用INDEX根据SMALL给出的值偏移返回新的引用数据,然后再用IFERROR将错误值转为空。


再回到教程当中,当我们创建D列为辅助列后,选中F2:F6单元格区域点击【数据】选项卡,单击【数据验证】验证条件选择【序列】,在来源中选择=$D$2:$D$17,并且需要在【出错警告】中取消勾选【输入无效数据时显示出错警告】否则会弹出警告提示。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(6)

最后我们可以将D列右键隐藏起来,然后再G2单元格输入公式:=IFERROR(VLOOKUP(F2,$A$2:$B$17,2,0)&"元","") 将服装对应的价格查找过来,搜索式下拉菜单就制作好啦,今天的公式比较难理解,大家多去操作几遍哦。

如何学习用excel制作下拉菜单(学会制作这样的excel下拉菜单让你的工作效率翻一番)(7)

需要素材文件的同学私信我回复“搜索式下拉菜单”自动获取素材文件,需要关注我才能发私信的哦!

我是小螃蟹,如果您喜欢这篇教程,请帮忙点赞和转发哦,感谢您的支持!

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页