excel如何设置下拉选项多选 表格制作选项按钮

在工作中，当需要用固定选项来规范一类信息的采集时，用【数据验证】做下拉列表绝对是首选。
下拉列表不仅可以规范单元格录入的内容，而且快捷。看下图，是不是快了！
然而我在用下拉

在工作中，当需要用固定选项来规范一类信息的采集时，用【数据验证】做下拉列表绝对是首选。

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

然而我在用下拉列表采集员工户籍信息的时候，居然翻车了！！！因为户籍名单太长，被拒绝了！！

于是，在抛弃了我的表格后，同事们开始“花样”地填表。其中只有一个广西省可以这样填。

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

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

既然你只记得关键词，我就用关键词搜索做个下拉列表吧！比如下图:

是不是感觉很高级~

想知道怎么做？跟上我，三步就能做出搜索型下拉菜单！

根据关键字创建辅助列

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

创建按关键字过滤的辅助列:

在单元格B2中填充以下公式，使用[Ctrl+Shift+Enter]结束公式并向下填充。

公式:

=IFERROR(INDEX($A:$A，SMALL(IF error(MATCH(IF(FIND(CELL(“目录”)，$A:$A)>0澳元2澳元35澳元。”)、$ A $ 2:$ A $ 35.0)、””)、ROW(A1)))、””)

上面的公式这么长，吓到你了吗？

虽然公式又长又难，但是可以直接套用。

应用方法很简单:

因为公式中标记蓝色的四个部分完全相同:$A:$A，这是完整的省份列表所在的单元格。

所以，只要把标蓝色的部分换到你想做的列表区就行了！

如果单纯套用公式，是满足不了你的求知欲的。

我想知道这个公式是怎么上筛选名单的！

跟我来！

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

接下来我们要开始高能娃娃了。你准备好了吗？

好了，我知道你准备好了！继续往下看！

公式:

=IFERROR(INDEX([完整列表区]，small (if error (match (if(单元格(“目录”)，[全列表区]) >: 0，[全列表区]，””)，[完整列表区]，0)，””)、ROW(A1)))、””)

公式分析:

要理解一个很长的嵌套公式，最好把它拆分开来，一步步理解。

细胞功能

公式:

=单元格(“目录”)

利用这个公式，我们可以得到最后编辑的单元格的内容，也就是我们要搜索的动态关键词。

为了更好的理解，这里，我们先不使用单元格函数，而是直接搜索关键词”北”比如我们把公式拆分一下看看。

辅助列1:

公式:

B3 =查找(“北”，A3，1)

目的:确定是否有关键词。

解析:FIND函数的作用是从单元格A3 & # 8221北”字，找到后会回到字“北”的位置。

“河北省”的第二个字是”北”因此，单元格B3显示2，如果找不到关键字，则返回#值！。

辅助列2:

公式:

C3 = IF(B3 >0，A3，””)

目的:将查找的结果编号转换为省名。

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

如果是，显示A3“河北”；如果不是，则显示空值""。

到目前为止，我们实际上已经得到了关键字为“北方”(辅助列2)的省份列表。

但是，它不能直接作为下拉菜单列表，因为它还包含很多#值！.

所以，接下来我们将移除错误值，并用关键字“North”对省份列表进行重新排序。

辅助栏3:

公式:

D3 =匹配(C3，$ A $ 3:$ A $ 19.0)

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

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

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

辅助列4:

公式:

E3 = if error(3，1048765)

目的:去除误差值。

解析:

因为辅助列5使用

小函数

排序，但是这个函数不支持错误值。

所以，在这一步，先用

IFEERROR函数

将错误值替换为空值””。

辅助列5:

公式:

F3 =小(3美元:19美元，第(A1)行)

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

解析:ROW(A1)

的作用是获取某个单元格的行号，结果为1。此处的功能是构建一系列数字1、2、3，并增加相应的数字...

small函数

用于返回数组中单元格E3到E19(辅助列4)的第一个最小值，结果为1。

辅助列6:

公式:

G3 = NDEX(3澳元:19澳元，F3)

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

解析:INDEX函数

的作用是查找数组A3到A19单元格(原列表)中第1(F3)个单元格的内容，结果是河北省。

这一步也会有很多错误值(#NUM！)，也可以用

IFERROR

替换为空 value。

到辅助栏6的位置，我们得到了带关键词的省份列表。

想用一个栏目搞定，只需要把集(数)打包(嵌入)就行了！

嵌套。如果要稍微把公式改成数组公式，这里就不展开了~

定义辅助列名

点击

[公式]

tab-

[名称管理器]

-新建名称。

新建一个名称，在名称区输入

“省列表”

，参照位置输入公式:

=OFFSET(Sheet1！$B，0，0，COUNTA(Sheet1！$B:$B)-COUNTIF(Sheet1！$B:$B，””),1)

公式分析:

Sheet1！$B:$B是辅助栏。

?县(小型张1！$B:$B)

辅助列中不为空的单元格数。B5单元格的公式结果虽然是空 value " "，但仍然属于非空单元格。

?县(Sheet1！$B:$B，””)

是辅助行空的值“”的编号。

使用偏移函数

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

这里的关键词是

“北”

，匹配的数字是3，所以整个公式的结果是B2到B4。

根据关键字，区域会动态变化。

设置下拉列表

选择需要设置下拉列表的单元格，点击

数据

页签-

数据校验

–

“数据验证”

；

在验证条件对话框的权限中选择

“系列”

，填写

“=省份列表”

作为来源；

单击

“错误警告”

选项卡，取消选中

“输入无效数据时显示错误警告”

。

搞定！

总结一下

总结一下

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

需要注意的是，由于设置下拉列表时取消了错误警告，所以数据校验不能限制要填写的内容。

虽然本文使用的公式很长，但是它的优点是对Excel的版本没有太高的要求。

Office 2007以上版本和WPS都可以。

PS:如果是WPS2019及以上，自带“搜索下拉菜单”~

本文涉及到的关于函数的知识点相当多。对拓展学习感兴趣的同学可以看看我们之前的文章。觉得头疼的同学也可以直接抄公式。