原文标题：《你会超级透视表吗？比 Excel 透视表好用 10 倍都不止！》
大家好，我是在研究表格结构转换的小爽~
前面我们讲过利用常规的方法，制作二级下拉列表辅助表：制作二级下拉列表必备，这个奇葩方法，你可能真的没见过！
今天我们就来介绍一下，Dax 度量值（Power Pivot）和 Power Query 的 M 函数做法，主要带大家拓展思路。
如下图，我们现在需要将左表转换右边的模式。
利用 Dax 度量值
我们先来看看操作：
❶ 制作一个辅助列，公式为：
=COUNTIF($B$2:B2,B2)
❷ 插入数据透视表，并勾选「将此数据添加到数据模型」。
PS.我们想要使用 Dax，只需要在创建数据透视表时，勾选【将此数据添加到数据模型】即可。
❸ 单击数据透视表区域，在【Power Pivot】选项卡下，单击【度量值】-【新建度量值】。
❹ 在公式栏中输入公式，度量值名称为度量值 1。
=CONCATENATEX('区域','区域'[小类])
公式中，区域是我们的数据源表格，区域 [小类] 是区域表中小类列。
CONCATENATEX 函数的作用，就是将多个文本合并到一起，类似于 Excel 中的 TEXTJOIN 函数。
CONCATENATEX 函数的结构如下：
=CONCATENATEX(表，表达式，分隔符)=CONCATENATEX('区域','区域'[小类])所以上面 Dax 函数公式的含义，就是对区域表中的小类列进行文本合并。
❺ 将辅助列放在行区域，将大类放在列区域，将度量值 1 放在值区域。
❻ 将总计行和列禁用。
❼ 到这里，就制作完成了。
关于 Dax，大家可能有些疑惑，下面我来简单的介绍一下。
传统的数据透视表无法对文本进行透视，但是由于超级透视表（Power Pivot）的出现，利用 Dax 度量值我们就可以实现这一功能。
Power 是超级的意思，所以 Power Pivot 就是超级数据透视表。
DAX 是 Data Analysis Expression 的缩写，即数据分析表达式，Dax 是在 Power Pivot 的基础上使用的数据统计函数。
使用 DAX 的好处是：
❶ 可以弥补数据透视表中的【计算字段】的诸多缺陷。
❷ Dax 函数可以修改聚合计算的方法。
在普通数据透视表中，值汇总方式，只有求和，计数…… 等几种方式。
而在 Power Pivot 中，可以通过多种 Dax 函数达到更灵活的汇总。
比如这个案例中，我们使用 CONCATENATEX 函数对文本进行合并。
利用 PowerQuery
PowerQuery 是数据清理和数据转换的利器，现在我们就来看看，利用它，是如何达到所想要的效果的。
具体步骤：
❶ 将数据导入到 PQ 编辑器中。
选择数据区域-在【数据】选项卡下，选择【来自工作表】-【确定】，进入 PQ 编辑器中。
❷ 选择大类列，在【主页】选项卡下，单击【分组依据】-所有行-【确定】。
PS：分组依据功能是对数据进行分组统计的，这里我们想要的是对大类进行分组，同时，汇总项需要的是，大类中的小类形成的 list。
❸ 将 M 函数公式后面改成 each [小类]。
❹ 单击【fx】新增一个步骤，输入公式：
= Table.FromColumns(分组的行[计数],分组的行[大类])
Table.FromColumns 函数能够将各列形成 list 转换为各列的表格。
= Table.FromColumns(lists标题形成的list)
案例中：
= Table.FromColumn（分组后的小类形成的lists,标题大类的list)
所以公式为，
= Table.FromColumns分组的行[计数],分组的行[大类])
❺ 关闭并上载表格。
到这里，PQ 方法就完成了。
总结一下
本文介绍了二级下拉列表辅助表的延伸拓展方法：
使用 Dax 度量值：
利用 Countif 函数辅助列，统计第几次出现。
新建度量值，CONCATENCEX 函数将文本进行合并。
大类为列区域，辅助列为行区域，度量值为值区域。
使用 PowerQuery：
分组依据。
Table.FromColumns。
关于二级下拉列表的辅助表的制作方法你学会了嘛~
本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小爽，编辑：竺兰
广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。