制作二级下拉列表必备，这个奇葩方法，你可能真的没见过！
点击蓝字【秋叶 Excel】👆
发送【福利】
免费领 Excel 插件&工具，看精华文章！
本文作者：小爽
本文编辑：竺兰
大家好，我是在研究表格结构转换的小爽~
在制作二级下拉列表的时候，我们通常需要先制作下图这样的辅助表。
然后再对辅助表设置对应的「自定义名称」，最后利用 Indirect 函数，通过【数据验证】达到我们想要的效果。
那么问题来了，上面这种辅助表是怎么样做出来的呢？
今天我们就来聊聊~
如下图，左边为参数表区域，现在我们要做成右表的形式：
要完成这个效果，需要用到去重，还有一对多匹配的知识。
去重
我们需要对大类做一个去重操作，同时通过转置函数把竖向转为横向。
▋方法一：利用删除重复值
具体步骤：
❶ 将大类复制到 E 列中。
❷ 选中 E1:E12，在【数据】选项卡下，单击【删除重复项】-【确定】，即可将数据去重。
然后在 G2 单元格中输入公式，将去重后的数据进行转置：
=TRANSPOSE(E2:E4)当然，我们也可以使用选择性粘贴转置的功能。
动图效果如下：
▋方法二：利用 Office365 的 Unique 函数
Unique 是一个去重函数。
在 G2 单元格中输入公式：
=TRANSPOSE(UNIQUE(A2:A12))一对多匹配
去重之后，得到转置后的的标题后，接下来，我们就要根据大类标题，进行一对多查询啦~
▋方法一：用 Countif 做辅助列，再用 Vlookup 查询
由于 Vlookup 函数只能返回第一次出现的值，所以对于一对多匹配，我们的做法就是利用 Countif 函数拉灯模式做辅助列，然后利用 Vlookup 函数索引每一次出现的位置。
关于拉灯模式的解析可以戳：不管查找第几次出现的数据，用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(array,include,[if_empty])=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 函数就可以搞定一对多的效果。
反过来，我们把辅助表反转过来，其实也是可以的，如下图所示。
制作方法也是跟上文介绍的思路差不多，小伙伴有空可以去试试~
最后考考大家：
对于一对多查询匹配，大家见过哪种做法？
↓↓↓
遇到有价值的文章
不放过 !
点击【阅读原文】
读好书~
👇👇👇