本文作者：竺兰
本文编辑：竺兰
小伙伴们大家好，我是清明放假飞奔回家的小兰~
很多小伙伴都知道，Excel 里的【下拉列表】功能，不仅能够帮助我们快速录入数据，还能防止同事胡乱填写，提高工作效率。
但是，80% 的人都只会最基础的「一级下拉列表」，而对于「多级下拉列表」不太了解。
所以今天，我就来满 足你的愿望，讲一讲「Excel 下拉列表的那些事儿」~
一级下拉列表
一个简单的数据验证，搞定一级下拉列表。
制作思路是将分类项目单独放在一个参数表中，然后通过【数据验证】引用这些参数作为数据源。
具体的设置方法如下：
❶ 打开【数据验证】窗口。
❷ 验证条件选择【序列】。
❸ 选择添加数据来源。
像产品类型、部门、省市等比较固定的分类信息，都可以利用下拉列表限制输入的内容，避免一种分类、多种写法的情况出现。
二级下拉列表
定义名称+数据验证+INDIRECT 函数，轻松创建二级下拉列表。
所谓二级下拉列表，就是第 2 级的列表选项，可以根据第 1 级的数据动态更新。
具体的设置方法如下：
❶准备二级下拉列表数据
❷定义名称
选择所有的列表数据，单击【公式】选项卡，找到「定义的名称」-【根据所选内容创建】：
在弹出的窗口中，勾选「首行」，单击【确定】。
这样就给二级内容起了个总体的名字，这个名字是「首行」单元格的内容。比如：
❸制作下拉列表
先设置好一级下拉列表，具体操作前文讲过，这里就不重复了。
二级和一级下拉列表的步骤大同小异，只是在选择来源时，需要用到 Indirect 函数：
PS：提示「源当前包含错误」，是因为「二级列表」引用的「一级列表」单元格中没有数据，导致源错误，不用理会，点「是」。
敲黑板：
=indirect(A2)
Indirect 函数是间接引用函数，可以返回由文本字符串所指定的引用。
比如这里引用的是 A2 单元格，但返回的结果是参数表 C2:C5 单元格里的值。即：引用 C 列省份中所包含的市级。
如果你对 Excel 函数所知不多，想系统学习，快来加入《秋叶 Excel 3 天集训营》！
这里不仅有专业讲师手把手教你函数知识，还会免费送你《35 个常用函数说明手册》，随查随用！
秋叶 Excel 3 天集训营
限时 0 元
立即扫描下方二维码加班班微信
免费领取课程吧~
三级下拉列表
三级下拉列表其实并没有大多数小伙伴想象中那么难，如果你能学会一级、二级列表，我相信，这个小技巧也难不住你。
相比前两者，三级下拉列表最大的不同，就是数据源。
三级内容的表头，是由一二级连接在一起的。看图更直观↓
具体的设置方法如下：
❶定义名称
选择列表数据，使用【定位】功能快速选择所有的非空单元格。
再使用【公式】选项卡中的【根据所选内容创建】的功能，批量的创建省份对应城市选区的自定义名称。
哦豁，好像翻车了……
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 3 天集训营，专为职场人量身打造，所用案例都是职场实战相关！
原价 99 元，现在扫描下方二维码，添加班主任微信，即可免费加入，还送100 套 Excel 模板！
秋叶 Excel 3 天集训营
动态报表+数据处理+汇报表达
一站式搞定职场数据难题
赶紧扫码加班主任微信报名
给自己一个提升的机会~
以上内容包含广告
热门跟贴