快捷搜索:  汽车  科技

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)有没有觉得很高级?想知道怎么做的吗?紧跟我的步伐,三步就能做出搜索式下拉菜单!如果下拉列表太长在下拉选择的时候也是很遭嫌弃的。那我们就做个带关键词搜索的下拉列表吧!就像下图这样的:之后再选中区域,鼠标点击数据—数据验证,将验证条件设为“序列”,将序列来源设为“=城市”,鼠标点击确定即可。第三步:修改定义名称鼠标依次点击公式—定义的名称—名称管理器,在打开的窗口,将引用位置改为多行多列数据,如“A2:D9”,鼠标点击确定,即可完成多行多列下拉菜单的制作。

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(1)

在工作中,常常需要对多行多列的数据设置下拉菜单,但在使用数据验证,设置下拉菜单时,序列来源是不能执行引用多行多列区域的,否则即会出现下图提示框,应该如何进行设置呢?

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(2)

第一步:定义名称框

鼠标依次点击公式—定义的名称—定义名称,在打开的窗口中,输入名称“城市”,再将引用位置设为“A2:A9”,之后使用鼠标点击确定。

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(3)

第二步:设置下拉菜单

之后再选中区域,鼠标点击数据—数据验证,将验证条件设为“序列”,将序列来源设为“=城市”,鼠标点击确定即可。

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(4)

第三步:修改定义名称

鼠标依次点击公式—定义的名称—名称管理器,在打开的窗口,将引用位置改为多行多列数据,如“A2:D9”,鼠标点击确定,即可完成多行多列下拉菜单的制作。

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(5)

如果下拉列表太长在下拉选择的时候也是很遭嫌弃的。那我们就做个带关键词搜索的下拉列表吧!就像下图这样的:

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(6)

有没有觉得很高级?想知道怎么做的吗?紧跟我的步伐,三步就能做出搜索式下拉菜单!

1根据关键词创建辅助列

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

❷ 创建根据关键词筛选的辅助列:

将下列公式填入 B2 单元格,使用【CTRL SHIFT ENTER】组合键结束公式,向下填充。

公式:

=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怎样做下拉菜单(Excel下拉菜单制作的小技巧)(7)

上面的公式这么长,是不是把你吓到了?公式虽然很长很难,但直接套用即可。

套用方法很简单:

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

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

如果简单的套用公式,并不能满足你的求知欲。而是想知道这个公式,是怎么得出筛选列表的!

公式:

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(8)

公式解析:

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

❶ CELL 函数

公式:=CELL("contents")

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(9)

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

辅助列 1:

公式:B3=FIND("北" A3 1)

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

上述今天和大家分享的Excel下拉菜单的制作小技巧,非常实用哦!

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(10)

辅助列 2:

公式:C3=IF(B3>0 A3 "")

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

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(11)

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(12)

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

辅助列 3:

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

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

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(13)

辅助列 4:

公式:E3=IFERROR(D3 1048765)

目的:去除错误值。

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(14)

辅助列 5:

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

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

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(15)

辅助列 6:

公式:G3=NDEX($A$3:$A$19 F3)

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

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(16)

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

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

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

2定义辅助列名称

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(17)

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

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(18)

公式解析:

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(19)

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(20)

COUNTA(Sheet1!$B$2:$B$35)是获取辅助列非空单元格的个数。尽管 B5 单元格的公式结果是空值「」,但是仍然属于非空单元格。

COUNTIF(Sheet1!$B$2:$B$35 "")是获取辅助列空值「」的个数。

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

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

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

3设置下拉列表

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(21)

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(22)

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

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(23)

完成!

excel怎样做下拉菜单(Excel下拉菜单制作的小技巧)(24)

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

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

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

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

猜您喜欢: