如何灵活运用透视表(你会超级透视表吗)

大家好,前面我们讲过利用常规的方法,制作二级下拉列表辅助表。

今天我们就来介绍一下,Dax 度量值(Power Pivot)和 Power Query 的 M 函数做法,主要带大家拓展思路

如下图,我们现在需要将左表转换右边的模式。

如何灵活运用透视表(你会超级透视表吗)(1)

01 利用 Dax 度量值

我们先来看看操作:

❶ 制作一个辅助列,公式为:

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

如何灵活运用透视表(你会超级透视表吗)(2)

❷ 插入数据透视表,并勾选「将此数据添加到数据模型」。

如何灵活运用透视表(你会超级透视表吗)(3)

PS.我们想要使用 Dax,只需要在创建数据透视表时,勾选【将此数据添加到数据模型】即可。

❸ 单击数据透视表区域,在【Power Pivot】选项卡下,单击【度量值】-【新建度量值】。

如何灵活运用透视表(你会超级透视表吗)(4)

❹ 在公式栏中输入公式,度量值名称为度量值 1。

=CONCATENATEX('区域','区域'[小类])

如何灵活运用透视表(你会超级透视表吗)(5)

公式中,区域是我们的数据源表格,区域[小类]是区域表中小类列。

CONCATENATEX 函数的作用,就是将多个文本合并到一起,类似于 Excel 中的 TEXTJOIN 函数。

CONCATENATEX 函数的结构如下:

=CONCATENATEX(表,表达式,分隔符)

=CONCATENATEX('区域','区域'[小类])

所以上面 Dax 函数公式的含义,就是对区域表中的小类列进行文本合并。

❺ 将辅助列放在行区域,将大类放在列区域,将度量值 1 放在值区域。

如何灵活运用透视表(你会超级透视表吗)(6)

❻ 将总计行和列禁用。

如何灵活运用透视表(你会超级透视表吗)(7)

❼ 到这里,就制作完成了。

如何灵活运用透视表(你会超级透视表吗)(8)

关于 Dax,大家可能有些疑惑,下面我来简单的介绍一下。

传统的数据透视表无法对文本进行透视,但是由于超级透视表(Power Pivot)的出现,利用 Dax 度量值我们就可以实现这一功能。

Power 是超级的意思,所以 Power Pivot 就是超级数据透视表。

DAX 是 Data Analysis Expression 的缩写,即数据分析表达式,Dax 是在 Power Pivot 的基础上使用的数据统计函数。

如何灵活运用透视表(你会超级透视表吗)(9)

使用 DAX 的好处是:

❶ 可以弥补数据透视表中的【计算字段】的诸多缺陷。

❷ Dax 函数可以修改聚合计算的方法。

普通数据透视表中,值汇总方式,只有求和,计数……等几种方式。

如何灵活运用透视表(你会超级透视表吗)(10)

而在 Power Pivot 中,可以通过多种 Dax 函数达到更灵活的汇总。

比如这个案例中,我们使用 CONCATENATEX 函数对文本进行合并。

02 利用 PowerQuery

PowerQuery 是数据清理和数据转换的利器,现在我们就来看看,利用它,是如何达到所想要的效果的。

具体步骤:

❶ 将数据导入到 PQ 编辑器中。

选择数据区域-在【数据】选项卡下,选择【来自工作表】-【确定】,进入 PQ 编辑器中。

如何灵活运用透视表(你会超级透视表吗)(11)

❷ 选择大类列,在【主页】选项卡下,单击【分组依据】-所有行-【确定】。

PS:分组依据功能是对数据进行分组统计的,这里我们想要的是对大类进行分组,同时,汇总项需要的是,大类中的小类形成的 list。

如何灵活运用透视表(你会超级透视表吗)(12)

如何灵活运用透视表(你会超级透视表吗)(13)

❸ 将 M 函数公式后面改成 each [小类]。

如何灵活运用透视表(你会超级透视表吗)(14)

❹ 单击【fx】新增一个步骤,输入公式:

= Table.FromColumns(分组的行[计数],分组的行[大类])

如何灵活运用透视表(你会超级透视表吗)(15)

Table.FromColumns 函数能够将各列形成 list 转换为各列的表格。

= Table.FromColumns(lists,标题形成的list)

如何灵活运用透视表(你会超级透视表吗)(16)

案例中:

= Table.FromColumns(分组后的小类形成的lists,标题大类的list)

所以公式为,

= Table.FromColumns(分组的行[计数],分组的行[大类])

❺ 关闭并上载表格。

如何灵活运用透视表(你会超级透视表吗)(17)

到这里,PQ 方法就完成了。

如何灵活运用透视表(你会超级透视表吗)(18)

03 总结一下

本文介绍了二级下拉列表辅助表的延伸拓展方法:

使用 Dax 度量值:

  • 利用 Countif 函数辅助列,统计第几次出现。
  • 新建度量值,CONCATENCEX 函数将文本进行合并。
  • 大类为列区域,辅助列为行区域,度量值为值区域。

使用 PowerQuery:

  • 分组依据。
  • Table.FromColumns。

关于二级下拉列表的辅助表的制作方法你学会了嘛~

如何灵活运用透视表(你会超级透视表吗)(19)

如果大家还想要知道 PQ 和 PP 其他相关的知识,欢迎留言区告诉我哦~(觉得太难或者不需要,也可以在留言区中聊聊)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页