你会超级透视表吗?比Excel透视表好用10倍都不止

大家好 , 
前面我们讲过利用常规的方法 , 制作二级下拉列表辅助表 。
今天我们就来介绍一下 , Dax 度量值(Power Pivot)和 Power Query 的 M 函数做法 , 主要带大家拓展思路 。
如下图 , 我们现在需要将左表转换右边的模式 。

你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
01 利用 Dax 度量值 
我们先来看看操作:
? 制作一个辅助列 , 公式为:
=COUNTIF($B$2:B2,B2)
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
? 插入数据透视表 , 并勾选「将此数据添加到数据模型」 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
PS.我们想要使用 Dax , 只需要在创建数据透视表时 , 勾选【将此数据添加到数据模型】即可 。
? 单击数据透视表区域 , 在【Power Pivot】选项卡下 , 单击【度量值】-【新建度量值】 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
? 在公式栏中输入公式 , 度量值名称为度量值 1 。
=CONCATENATEX('区域','区域'[小类])
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
公式中 , 区域是我们的数据源表格 , 区域[小类]是区域表中小类列 。
CONCATENATEX 函数的作用 , 就是将多个文本合并到一起 , 类似于 Excel 中的 TEXTJOIN 函数 。
CONCATENATEX 函数的结构如下:
=CONCATENATEX(表 , 表达式 , 分隔符)
=CONCATENATEX('区域','区域'[小类])
 
所以上面 Dax 函数公式的含义 , 就是对区域表中的小类列进行文本合并 。
? 将辅助列放在行区域 , 将大类放在列区域 , 将度量值 1 放在值区域 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
? 将总计行和列禁用 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
? 到这里 , 就制作完成了 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
关于 Dax , 大家可能有些疑惑 , 下面我来简单的介绍一下 。
传统的数据透视表无法对文本进行透视 , 但是由于超级透视表(Power Pivot)的出现 , 利用 Dax 度量值我们就可以实现这一功能 。
Power 是超级的意思 , 所以 Power Pivot 就是超级数据透视表 。
DAX 是 Data Analysis Expression 的缩写 , 即数据分析表达式 , Dax 是在 Power Pivot 的基础上使用的数据统计函数 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
使用 DAX 的好处是:
? 可以弥补数据透视表中的【计算字段】的诸多缺陷 。
? Dax 函数可以修改聚合计算的方法 。
在普通数据透视表中 , 值汇总方式 , 只有求和 , 计数……等几种方式 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
而在 Power Pivot 中 , 可以通过多种 Dax 函数达到更灵活的汇总 。
比如这个案例中 , 我们使用 CONCATENATEX 函数对文本进行合并 。
 
02 利用 PowerQuery 
PowerQuery 是数据清理和数据转换的利器 , 现在我们就来看看 , 利用它 , 是如何达到所想要的效果的 。
具体步骤:
? 将数据导入到 PQ 编辑器中 。
选择数据区域-在【数据】选项卡下 , 选择【来自工作表】-【确定】 , 进入 PQ 编辑器中 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 
? 选择大类列 , 在【主页】选项卡下 , 单击【分组依据】-所有行-【确定】 。
PS:分组依据功能是对数据进行分组统计的 , 这里我们想要的是对大类进行分组 , 同时 , 汇总项需要的是 , 大类中的小类形成的 list 。
你会超级透视表吗?比Excel透视表好用10倍都不止

文章插图
 


推荐阅读