Excel 下拉列表技巧超全合集

原文标题：《拒绝加班！这份 Excel 下拉列表超全合集你值得拥有！》

今天讲一讲「Excel 下拉列表的那些事儿」~

一级下拉列表

一个简单的数据验证，搞定一级下拉列表。

制作思路是将分类项目单独放在一个参数表中，然后通过【数据验证】引用这些参数作为数据源。

具体的设置方法如下：

❶ 打开【数据验证】窗口。

❷ 验证条件选择【序列】。

❸ 选择添加数据来源。

像产品类型、部门、省市等比较固定的分类信息，都可以利用下拉列表限制输入的内容，避免一种分类、多种写法的情况出现。

二级下拉列表

定义名称 + 数据验证 + INDIRECT 函数，轻松创建二级下拉列表。

所谓二级下拉列表，就是第 2 级的列表选项，可以根据第 1 级的数据动态更新。

具体的设置方法如下：

❶ 准备二级下拉列表数据

❷ 定义名称

选择所有的列表数据，单击【公式】选项卡，找到「定义的名称」-【根据所选内容创建】：

在弹出的窗口中，勾选「首行」，单击【确定】。

这样就给二级内容起了个总体的名字，这个名字是「首行」单元格的内容。比如：

❸ 制作下拉列表

先设置好一级下拉列表，具体操作前文讲过，这里就不重复了。

二级和一级下拉列表的步骤大同小异，只是在选择来源时，需要用到 Indirect 函数：

PS：提示「源当前包含错误」，是因为「二级列表」引用的「一级列表」单元格中没有数据，导致源错误，不用理会，点「是」。

敲黑板：

=indirect(A2)

Indirect 函数是间接引用函数，可以返回由文本字符串所指定的引用。

比如这里引用的是 A2 单元格，但返回的结果是参数表 C2:C5 单元格里的值。即：引用 C 列省份中所包含的市级。

三级下拉列表

三级下拉列表其实并没有大多数小伙伴想象中那么难，如果你能学会一级、二级列表，我相信，这个小技巧也难不住你。

相比前两者，三级下拉列表最大的不同，就是数据源。

三级内容的表头，是由一二级连接在一起的。看图更直观 ↓

具体的设置方法如下：

❶ 定义名称

选择列表数据，使用【定位】功能快速选择所有的非空单元格。

再使用【公式】选项卡中的【根据所选内容创建】的功能，批量的创建省份对应城市选区的自定义名称。

哦豁，好像翻车了……

Excel 提示「此选择无效」。

仔细观察一下表格，我们发现，有一处明显没有和其他区域连在一起，这个就是报错的原因，Excel 重新识别并选中了一个新的区域。

这是由于 Excel 本身的机制引起的，如果出现了连续两列行数相等且后面的列比它们的行数少，就会报错。

不信的话，我们稍微调整一下列顺序，把这两列分开 ↓

然后再试一遍【定义名称】。

最后检查一下：

设置成功！

❷ 创建三级下拉列表。

这时还是需要借助【Indirect】函数，但是又稍微有点不同，公式：

=Indirect（A2&B2）

即：同时引用一级列表和二级列表。

是不是很简单！

一二三级下拉列表，只涉及到了几个非常基础的知识点：

❶ 数据验证

❷ 定义名称

❸ Indirect 函数

看完这篇文章，再动手练习一下，想必就掌握得七七八八了。

但我接下来要讲的【搜索式下拉列表】，就稍微有那么 亿  一点点难了。

搜索式下拉列表

当你在微信搜索框里输入关键词【秋叶 Excel】并确定，你可以搜索出这个平台里所有与【秋叶 Excel】相关的内容。

而搜索式下拉列表也是如此，虽然没有微信搜索那么强大，但是它可以实现在 Excel 通过搜索关键词，找到设置好的、固定的数据，快速选择且录入表格。

具体设置方法：

❶ 根据关键词创建辅助列

① 在 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))),)

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

套用方法很简单：

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

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

❷ 定义辅助列名称

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

② 新建名称，名称区输入「省份列表」，引用位置输入公式：

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

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

PS：如果是 WPS2019 及以上的版本，则自带「搜索式下拉列表」~

❸ 设置下拉列表

选中需要设置下拉列表的单元格，打开数据验证窗口，在验证条件对话框的允许中选择「序列」，来源填写「= 省份列表」；

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

完成！

如果你只想知道怎么设置搜索式下拉列表，学会上面这些就够了；

最后，能够看到这里的同学，真的太不容易了！希望大家都能成功掌握上面这些知识点，提高效率，减轻工作压力，拥抱生活！

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：竺兰，编辑：竺兰