制作二级下拉列表必备，这个奇葩方法，你可能真的没见过！

点击蓝字关注【秋叶AIExcel】

发送【7】

免费领 1000+篇 Excel 精选教程！

本文作者：小爽

本文编辑：小兰

在制作二级下拉列表的时候，我们通常需要先制作下图这样的辅助表。

然后再对辅助表设置对应的「自定义名称」，最后利用 Indirect 函数，通过【数据验证】达到我们想要的效果。

那么问题来了，上面这种辅助表是怎么样做出来的呢？

今天我们就来聊聊~

如下图，左边为参数表区域，现在我们要做成右表的形式：

要完成这个效果，需要用到去重，还有一对多匹配的知识。

如果你还想学习更多 Excel 实用办公技巧，让工作变得高效又省力！

那可千万别错过《和秋叶一起学 Excel 速成实战课》这门网课！

原价 999 元

231 节实战课程

还送 900+套精选 Excel 模板

307 个函数清单

现在超值优惠价

到手仅需99元！

去重

我们需要对大类做一个去重操作，同时通过转置函数把竖向转为横向。

▋方法一：利用删除重复值

具体步骤：

❶ 将大类复制到 E 列中。

❷ 选中 E1:E12，在【数据】选项卡下，单击【删除重复项】-【确定】，即可将数据去重。

然后在 G2 单元格中输入公式，将去重后的数据进行转置：

=TRANSPOSE(E2:E4)

当然，我们也可以使用选择性粘贴转置的功能。

动图效果如下：

▋方法二：利用 Office365 的 Unique 函数

Unique 是一个去重函数。

在 G2 单元格中输入公式：

=TRANSPOSE(UNIQUE(A2:A12))

一对多匹配

去重之后，得到转置后的的标题后，接下来，我们就要根据大类标题，进行一对多查询啦~

▋方法一：用 Countif 做辅助列，再用 Vlookup 查询

由于 Vlookup 函数只能返回第一次出现的值，所以对于一对多匹配，我们的做法就是利用 Countif 函数拉灯模式做辅助列，然后利用 Vlookup 函数索引每一次出现的位置。

具体操作：

❶ 选中 A 列，按住快捷键【Ctrl+Shift++】，向左新增一列。

❷ 在 A2 单元格中输入如下公式并向下填充：

=COUNTIF($B$2:B2,B2)&B2

动图效果如下：

❸ 在 G3 单元格中输入如下公式，并向下向右填充公式：

=IFERROR(VLOOKUP(ROW(A1)&G$2,$A$1:$C$12,3,0),"")

Row 函数能够返回对应的行数。

Row(A1)&G$2 就是 1Word； 向下拉就是，Row(A2)&G$2 ，就是 2Word； 向右拉就是，Row(B1)&F$2，就是 1PPT。

所以我们直接用 Vlookup 函数进行匹配就可以达到所想要的结果，Iferror 函数将匹配不到的错误值替换为空值。

▋方法二：Office365 的 Filter 函数

Filter 函数是一个筛选函数，它是做一对多查询的利器，前面我们通过辅助列的做法完成，现在使用 Filter 函数，只需一个公式！

在 F3 单元格中，输入如下公式，向右填充：

=FILTER($B$2:$B$12,$A$2:$A$12=F$2)

Filter 函数基本语法：

=FILTER(要筛选的数组或区域,筛选条件,[是否忽略空值])

Filter 函数是一个筛选函数，它可以将数组中条件为 True 的结果筛选出来。

公式中：

=FILTER($B$2:$B$12,$A$2:$A$12=F$2)

① 要筛选的数组或区域：$B$2:$B$12 小类列。

② 筛选条件：$A$2:$A$12=F$2 大类是否等于「Word」。

就是将小类列中包含 Word 的，全部筛选出来，也就达到我们想要的一对多查询效果啦~

总结一下

在制作二级下拉列表的时候，我们通常需要制作一个辅助表，以便做好准备工作。

本文就介绍了下图的制作方法。

去重的话，我们用的是删除重复值功能，如果是 Office365 的话，直接 Unique 函数就可以搞定！

一对多匹配的话，我们就用到了 Countif 函数做一个辅助列，最后再用 Vlookup 进行查询匹配，这是一个很常见的思路。

如果是 Office365 的话，一个 Filter 函数就可以搞定一对多的效果。

反过来，我们把辅助表反转过来，其实也是可以的，如下图所示。

制作方法也是跟上文介绍的思路差不多，小伙伴有空可以去试试~

看完本文，是不是直呼「涨见识了！！！」

如果你想提升 Excel 技能，那么强烈推荐你学习《和秋叶一起学 Excel 速成实战课》。

课程包含 231节实战课程，系统全面带你掌握Excel 系统操作、商务图表、函数公式、数据透视表、高效技巧，一站式学透 Excel 表格！

《和秋叶一起学 Excel 速成实战课》

课程原价 999 元

限时优惠价，仅需 99 元

名师授课+系统教学+配套练习

长期有效，可反复回看

别犹豫了！赶紧扫码抢课

每天学点小技巧，工作效率up~up~

如果本篇文章对你有帮助，那就点个赞支持一下吧！