关注【秋叶 Excel】👆发送【插件】
免费领 Excel 必备 4 大插件
作者：竺兰
编辑：竺兰、萌萌
小伙伴们大家好，我是懂点 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)」。
完成！
如果你只想知道怎么设置搜索式下拉列表，学会上面这些就够了；
如果你还想了解上面这些公式的含义，在【秋叶Excel】公众号后台回复列表获取教程；
如果你使用的是 Microsoft 365，那么只需要一个简单的函数「Filter」两步就能搞定，还是在【秋叶Excel】公众号后台回复列表获取教程。
最后，能够看到这里的同学，真的太不容易了！希望大家都能成功掌握上面这些知识点，提高效率，减轻工作压力，拥抱生活！
📑
秋叶家 5 月专享福利来袭——《3 天 Excel 集训体验营》。
专为职场人准备，全部基于职场真实表格案例设计，超实用 Excel 技巧集合教学。
从此不愿再为表格而加班，那就行动起来！3 天搞定表格难题！
《秋叶 Excel 3 天集训营》
想提升，趁现在！
福利名额 仅限前100
不再被加班支配，不再为表格发愁！
免费赠送【35 个常用函数说明】哦！
赶紧扫码预约吧！👇👇👇
▲ 报名成功后将自动弹出班主任二维码，切勿提前退出
若未能添加，请联系公众号客服获取
🎁
公众号【秋叶 Excel】
回复关键词【礼包】
还能免费领办公神器
Office 模板和免商字体！
↓↓↓
继续阅读
阅读原文
关键词
单元格
公式
表格
选项卡
内容