下拉菜单样式(miui12下拉菜单样式设置)

张工 2022-05-25 18:54:24 阅读:36
  

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  小E为大家准备了40+Excel函数大全

  领取直接关注公棕号【秋叶Excel】,回复【头条】!

  大家好,我是绿水零。

  在工作中,当需要用固定选项来规范一种信息的收集时,用【数据验证】制作下拉列表。绝对是第一选择。

  下拉列表不仅可以规范单元格录入的内容,而且快捷。看下图,是不是快了!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  但是,当我用下拉列表采集员工户籍信息时,竟然翻车了!因为户籍列表太长,而惨遭嫌弃!!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  所以,在放弃我的表格后,我的同事开始在「花样」.填写。其中,只有一个广西省可以这样填写。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  广西同胞是不是想气死我?还是笑死我?

  知道自己省份全称的人不多,但是广西。

  既然你只记得关键词,那我就做一个下图这样的带关键词搜索的下拉列表!吧:

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  是不是感觉很高级~

  你知道怎么做吗?跟上我,三步就能做出搜索式下拉菜单!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  根据关键词创建辅助列

  在A栏中填写完整的省份列表;

  创建按关键字筛选的辅助列:

  使用【CTRL+SHIFT+ENTER】在B2单元格中填写以下公式

span >组合键结束公式,向下填充。

  
公式:

  =IFERROR(INDEX($A$2:$A$35,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$35)>0,$A$2:$A$35,""),$A$2:$A$35,0),""),ROW(A1))),"")

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
上面的公式这么长,是不是把你吓到了?

  
公式虽然很长很难,但直接套用即可。

  
套用方法很简单:

  
因为公式中 4 处标蓝的部分是完全一样的:$A$2:$A$35,就是完整的省份列表所在单元格。

  
所以,
直接把标蓝的部分换成你要做的列表区域就可以了!

  
如果简单地套用公式,并不能满足你的求知欲。

  而是想知道这个公式,是怎么得出筛选列表的!

  
FOLLOW ME!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  (着急看下一步的同学,也可以直接滑到 02。)

  
下面要开始高能套娃了,准备好了吗?

  
好的,我知道你们准备好了!接着往下看吧!

  公式:

  =IFERROR(INDEX([完整列表区域],SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),[完整列表区域])>0,[完整列表区域],""),[完整列表区域],0),""),ROW(A1))),"")

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  

公式解析:

  理解很长很长的嵌套公式,最好是把它拆分出来逐步理解。

  
CELL 函数

  
公式:

  =CELL("contents")

  使用这个公式可以获取最后编辑的单元格内容,就是我们要搜索的动态关键词。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
为了更好理解,这里先不使用 CELL 函数,直接以搜索包含关键词"北"为例,我们把公式拆分出来看看。

  
辅助列 1:

  公式:

  B3=FIND("北",A3,1)

  目标:判断是否含有关键词。

  
解析:FIND 函数的作用,是从 A3 单元格「河北省」的第 1 个字开始查找字符串"北"字,找到后就返回「北」字的位置。

  
「河北省」的第 2 个字符是"北"所以 B3 单元格显示 2,如果找不到关键词则返回#VALUE!。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
辅助列 2:

  公式:

  C3=IF(B3>0,A3,"")

  目标:将 FIND 的结果数字转换为省份名称。

  
解析:IF 函数的作用是,判断条件 B3 单元格 2 是否大于零。

  
如果是,显示 A3
「河北省」;如果不是,显示空值「」

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
到这里,我们其实已经得到了含有关键词
「北」的省份列表(辅助列 2)

  
但是,它不能直接作为下拉菜单的列表,因为还包含了很多
#VALUE!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
所以,接下来我们要
去掉错误值,并且给含有关键词「北」的省份列表重新排序。

  辅助列 3:

  公式:

  D3=MATCH(C3,$A$3:$A$19,0)

  目标:根据省份名称,找到该原始列表的相对位置。

  
解析:MATCH 的作用是返回 C3 单元格「河北省」,在数组 A3 到 A19 单元格(即原始列表)中的相对位置,匹配方式是 0(即精确匹配)

  
因为河北省在是原始列表 A3:A19 的第一个值,所以结果为 1。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
辅助列 4:

  公式:

  E3=IFERROR(D3,1048765)

  目的:去除错误值。

  
解析:因为辅助列 5 使用 SMALL 函数进行排顺序,但是该函数不支持错误值。

  
所以,这一步先使用
IFEERROR 函数将错误值替换为空值""。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
辅助列 5:

  公式:

  F3=SMALL($E$3:$E$19,ROW(A1))

  目的:对列表进行排序,使有关键词的省份排在前面。

  
解析:ROW(A1)函数的作用是获取单元格的行号,结果是 1,这里的作用是构建一个随行号递增的数列 1、2、3……

  
SMALL 函数的作用是返回数组 E3 至 E19 单元格(辅助列 4)中第 1 小的值,结果是 1。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
辅助列 6:

  公式:

  G3=NDEX($A$3:$A$19,F3)

  目的:根据相对行号找到对应省份。

  
解析:INDEX 函数的作用是在数组 A3 至 A19 单元格(原始列表)中找到第 1(F3 单元格)个单元格的内容,结果是河北省。

  
这一步也会有很多错误值
(#NUM!),同样可以使用 IFERROR 将其替换为空值。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
到辅助列 6 位置,我们已经获得了含关键词的省份列表。

  
如果想要使用一列搞定的话,就是把套(函)娃(数)给组(嵌)装(套)起来!

  
嵌套要将公式稍作改动,改成数组公式,这里就不展开啦~

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  定义辅助列名称


   点击【公式】选项卡-【名称管理器】-新建名称。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
新建名称,名称区输入
「省份列表」,引用位置输入公式:

  =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$35)-COUNTIF(Sheet1!$B$2:$B$35,""),1)

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  


公式解析:

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  Sheet1!$B$2:$B$35 就是辅助列。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
COUNTA(Sheet1!$B$2:$B$35)

  是获取辅助列非空单元格的个数。尽管 B5 单元格的公式结果是空值「」,但是仍然属于非空单元格。

  
COUNTIF(Sheet1!$B$2:$B$35,"")

  是获取辅助列空值「」的个数。

  
使用 OFFSET 函数

  构建一个动态的列表区域。以 B2 单元格为起点,向下偏移 0 个单元格,向下偏移 0 个单元格,长度为②-①(即关键词的匹配数),宽度是 1。

  
这里关键词是
「北」,匹配数是 3,所以整个公式得到的结果就是 B2 到 B4 这个区域。

  
根据关键词,区域会动态变化。

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  设置下拉列表


   选中需要设置下拉列表的单元格,点击【数据】选项卡-【数据验证】-「数据验证」

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

   在验证条件对话框的允许中选择「序列」,来源填写「=省份列表」

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

   点击「出错警告」选项卡,取消勾选「输入无效数据时显示出错警告(S)」

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
完成!

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  总结一下


   搜索式下拉列表和多级下拉列表一样,本质都是利用辅助列,创建动态的下拉选项。

  
需要注意的是,因为设置下拉列表时取消了出错警告,所以数据验证无法限制填写内容。

  
本文使用的公式虽然很长,但好处是对 Excel 的版本没有太高要求,
Office2007 以上的版本和 WPS 都可以使用。

  
PS:如果是 WPS2019 及以上的版本,则自带「搜索式下拉菜单」~

Excel里超好用的“搜索式”下拉菜单,很多人都不知道

  
本文涉及的函数知识点相当多,有兴趣延伸学习的同学可以看看我们往期的文章。觉得脑壳疼的同学也可以直接复制公式。

  小E为大家准备了40+Excel函数大全

  领取直接关注公棕号【秋叶Excel】,回复【头条】!

二维码