当前位置: 主页 > Office办公 > Excel专区 > Excel教程 > 比Excel透视表好用10倍都不止,“超级”透视表来了!

比Excel透视表好用10倍都不止,“超级”透视表来了!

  • 2022-11-16
  • 来源/作者: Wps Office教程网/ 菜鸟图库
  • 442 次浏览

数据透视表可以说是Excel中最最最实用的功能,它简单几步就可以生成数据分类汇总,而且可以随意改变布局。

但,你听说过超级透视表吗?

一、什么是超级透视表?

Excel2013版本起,Excel添加了一个数据分析新功能:Power pivot。它就是今天小编要说的超级透视表。

比Excel透视表好用10倍都不止,“超级”透视表来了!

如果你的Excel中没有,可以通过开发工具 – Com加载项 – Microsoft Power Pivot For Excel启用它。

比Excel透视表好用10倍都不止,“超级”透视表来了!

既然说它超级(Power),功能肯定要比现在的数据透视表强大很多。数据透视表可以实现的, Power pivot 都可以实现,数据透视表无法实现的,Power poivit也可以实现。

为什么Power pivot如此豪横?原因之一是它支持DAX系列(数据分析表达式函数。在excel中只能利用透视表提供的求和、计数、计算百分比等少数固定的数据分析命令,而DAX函数有二百多个,可以进行各种各样的自定义式的数据整理和分析。很多工作表中的函数都可以在power pivot中使用。

比Excel透视表好用10倍都不止,“超级”透视表来了!

说这么多,超级透视表怎么使用?到底有哪些超能应用?DAX函数公式在哪些?下面小编就用一个示例,让大家认识和学会使用它。

二、应用示例

【例】如下图所示,要求根据值班明细表,生成值班表。

比Excel透视表好用10倍都不止,“超级”透视表来了!

分析:数据透视表在值区域只能显示数字,无法显示字符的。而power pivot则可以。

操作步骤:

1、把数据导入到Power pivot

选取数据源表格 – Power pivot – 添加到数据模型

比Excel透视表好用10倍都不止,“超级”透视表来了!

添加后会弹出power pviot界面,表格完成导入。

比Excel透视表好用10倍都不止,“超级”透视表来了!

对于power pivot的界面大家还是很陌生的,有必要介绍一下:

工具栏、编辑栏和Excel表格位置一样

编辑下是导入的数据表和可以随时添加的新列

数据表下是一条可以移动的分隔线

在分隔线下的区域可以输入DAX函数公式

最下面是表格名称,和Excel工作表结构相同

比Excel透视表好用10倍都不止,“超级”透视表来了!

2、输入DAX公式

选取分隔线下区域的任一单元格,在编辑栏中输入公式:

=CONCATENATEX(‘表4’,[姓名],”,”)

注:

1、dax中的函数有的很长,可以输入第1个字母选取输入。表名和字段名也常选取方式输入。看着挺复杂,其实输入挺快的。

2、CONCATENATEX函数(表名,字段名,分隔符)用于合并某个字段的内容,用特定的字符连接

3、输入后会在公式前添加 “度量值 序号”,这是添加的计算字段名称,可以自定义修改。

比Excel透视表好用10倍都不止,“超级”透视表来了!

3、用透视表形式导回Excel表格

选取编辑好公式的power pivot 表格 – 数据透视表 – 选取导入的位置  – 布局数据透视表(带义fx是dax表达式生成的自定义字段)

比Excel透视表好用10倍都不止,“超级”透视表来了!

源表格更新数据后,刷新数据透视表可以同步更新。其他基本操作同我们常用的数据透视表。

如需要重新管理dax公式,可以通过 power pivot – 管理 打开 power pivot界面。

比Excel透视表好用10倍都不止,“超级”透视表来了!

CONCATENATEX函数还常用于同类项合并,和上面这个例子很像,下面的例子只是多了一个去重复函数。

比Excel透视表好用10倍都不止,“超级”透视表来了!

实现上图效果的dax公式:

=CONCATENATEX(VALUES(‘表3′[产品]),[产品],”,”)

其中,VALUES函数可以去重复,保留唯一值。