返回目录:excel表格制作
今日目标
学习制作多级下拉列表
老师,请问这个多级下拉列表怎么做?
要明白这个效果怎么做,你必须要清楚下面这几点,这是Excel必备的基础。
第1步,如何制作下拉列表?下拉列表的本质是什么?
第2步,如何给每个单元格制作对应的下拉选项?
从下拉列表的原理出发,再来看一下这个效果的本质是什么?
注意看,当「产品线」变化时,M列的「大类」选项是动态变化的。
类似的,选择「大类」的时候,N列的「中类」也是随之更新的。
因为单元格数据是根据条件更新的,所以对应创建的下拉列表,也是动态变化的。
所以,我们需要做的,就是给每一个单元格,设置一个对应的动态下拉选项。
接下来是具体的解决方法。
1- 解决方法
这个效果使用前两天讲过的,FILTER和UNIQUE可以轻松的实现。
具体参考文末「推荐文章」。
首先,要准备好对应类别的明细。
我们挨个看一下,每个类别下拉列表的做法。
1- 产品线,下拉列表
首先针对「产品」使用UNIQUE函数,提取非重复值。
公式如下:
=UNIQUE(B3:B32)
然后,给单元格设置「数据验证」,添加下拉列表
2- 大类,下拉列表
产品的「大类」是需要根据「产品线」内容动态更新的。比如选择食品,那么就把食品对应的选项提取出来。
这里可以分成两个步骤。
1- 筛选「食品」对应的「大类」
这个简单,用FILTER函数就可以实现
=FILTER(C3:C32,B3:B32=G3)
2- 对「大类」内容提取唯一值
这个是UNIQUE函数要干的活,在上一步的公式基础上,套一个UNIQUE函数就可以了。
=UNIQUE(FILTER(C3:C32,B3:B32=G3))
下拉列表的创建,和「产品线」完全一样,就不再重复演示了。重点看一下公式。
3- 中类,下拉列表
接下来提取「中类」的内容,思路和提取「大类」是一样的。
1- 筛选对应「中类」的内容
首先找出大类对应的中类所有内容。这里使用FILTER来实现。
公式如下:
=FILTER(D3:D32,C3:C32=H3)
2- 提取「中类」唯一值
然后使用UNIQUE函数对内容提取唯一值。
公式和「大类」基本一样:
=UNIQUE(FILTER(D3:D32,C3:C32=H3))
4- 明细,下拉列表
以此类推,接下来的「明细」也是相同的思路提取出来的,公式是这个样子的。
=UNIQUE(FILTER(E3:E32,D3:D32=I3))
2-总结
我们再来总结一下。
1-多级下拉列表的本质是:
本质上,就是要给每个单元格设定对应的下拉选项。
2- 如何设置动态的下拉选项?
使用FILTER函数,有条件的筛选下拉选项,然后用UNIQUE函数提取唯一值。
因为单元格内容是动态的,那么下拉选项肯定也是动态的。
点击下面链接,学习FILTER和UNIQUE函数。
明白了这个原理之后,我们还可以做出很多其他的效果,比如下面按照关键字进行模糊匹配,再输出对应的下拉列表选项。
这个效果对应的公式是这个样子的。
=FILTER(A2:A15,ISNUMBER(FIND(C2,A2:A15)))
考一考你
你能够用文字解释一下,这一段公式的作用和原理吗?
评论区等你的答案。
我是拉小登,一个会设计表格的Excel老师