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

文章插图
01 利用 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 函数对文本进行合并 。
02 利用 PowerQuery
PowerQuery 是数据清理和数据转换的利器,现在我们就来看看,利用它,是如何达到所想要的效果的 。
具体步骤:
? 将数据导入到 PQ 编辑器中 。
选择数据区域-在【数据】选项卡下,选择【来自工作表】-【确定】,进入 PQ 编辑器中 。

文章插图
? 选择大类列,在【主页】选项卡下,单击【分组依据】-所有行-【确定】 。
PS:分组依据功能是对数据进行分组统计的,这里我们想要的是对大类进行分组,同时,汇总项需要的是,大类中的小类形成的 list 。

文章插图

文章插图
? 将 M 函数公式后面改成 each [小类] 。

文章插图
? 单击【fx】新增一个步骤,输入公式:
= Table.FromColumns(分组的行[计数],分组的行[大类])
- 【星座控】颜值超高,却超级难追的4个星座女,素颜女神,倾国倾城!
- 选购葡萄酒有时需要运气
- 超级真菌真的那么可怕吗 超级病菌是真菌吗
- Word必备的下划线技巧,你会几种? word下划线怎么打
- 【游戏迷】梦幻西游:这是第二个李永生么?打图半年只为买超级神龙,人才!
- 你会换手机号吗 你需要换手机号吗
- 手机上超级有意思的app有哪些 异地情侣App推荐以下几个:
- 完美世界游戏太“霸道”了!超级代理IP强势破圈,网友直呼专业
- 梦见在超级市场购物怎么回事 梦见在超级市场购物有什么预兆(图文)
- 超级高清手机或者电脑背景图 手机背景图高清