拒绝加班！这份Excel下拉列表超全合集你值得拥有！

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进秋叶 Excel 读者快乐交流群！

本文作者：竺兰

本文编辑：竺兰

小伙伴们大家好，我是懂点 Excel 的小兰~

前段时间，我们发布了一篇关于【数据验证】的文章（点这里查看），里面有个投票，调查大家想不想学习 Excel 里的数据录入神技——下拉列表。

看了下结果，一共 205 位小伙伴参与，92%都表示「非常想学」。

所以今天，我就来满足大家的愿望，讲一讲「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)」。

完成！

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

如果你还想了解上面这些公式的含义，在我们公众号后台回复 列表 获取教程；

如果你使用的是 Microsoft 365，那么只需要一个简单的函数「Filter」两步就能搞定，还是在我们公众号后台回复 列表 获取教程。

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

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇