Excel 新出的 Pivotby 函数
- 秋叶Excel
2025-03-09 12:10
原文标题:《Excel 新出的 Pivotby 函数,实在太好用了!》
大家好,这里是秋叶编辑部~
作为表哥表姐的我们,在做数据分析的时候,经常需要对指定的字段信息进行汇总统计。
1、常规做法
有时我们会使用 Sumifs 函数。
如下图: 使用 Sumifs 函数对姓名和部门字段进行交叉汇总求和。
但是我们有时需要对指定列进行修饰,在进行判断的时候,由于 Sumifs 函数不支持数组参数,我们只能使用 Sumproduct 函数。
如下图,Sumifs 函数公式错误。
如下图,我们转用 Sumproduct 函数,进行逻辑判断得出了结果。
当我们需要切换其他字段进行分析的时候,上面的做法需要重新编写函数公式,要先对行列字段进行去重,再编写汇总函数公式。
这就有点麻烦~
因此,我们还可以通过数据透视表的方式进行统计,仅需鼠标操作即可完成。
但数据透视表方便是方便,它却无法像函数一样实时更新。
所以,透视表函数出现了,它就是 Pivotby 函数!
2、Pivotby 函数
Pivotby 函数是一个透视表函数,它跟我们的数据透视表类似,但是跟数据透视表没直接关系。
目前,Office 365 和 WPS 都有 Pivotby 函数。
Groupby 函数(即分组函数)是基于某个字段进行汇总统计的。
Pivotby 函数是基于行列字段筛选后进行汇总统计的。
Groupby 函数是 Pivotby 函数的一种特殊情况,所以这两个函数参数差不多,掌握了 Pivotby 函数函数,Groupby 函数就会了。
我们来看它的参数,它的参数看起来很多,实际上并不难,就对着数据透视表学就是了!
=PIVOTBY (row_fields,col_fields,values,//行字段,列字段,值字段 function,//汇总方式,是个函数[field_headers],//是否包含标题[row_total_depth],[row_sort_order],//是否显示行总计/小计,行排序方式[col_total_depth],[col_sort_order],//是否显示行总计/小计,行排序方式[filter_array],//筛选[relative_to]) //相关方式,一般用在百分比
只要你了解数据透视表,就很快能够学会 Pivotby 函数。
必选参数:
行字段,列字段,值字段,汇总方式:
row_fields,col_fields,values,function
前三参数依次对应的就是数据透视表显示字段三个字段。
如下图,对应的 Pivotby 函数公式。
第四参数我们可以选择多种汇总方式,比如说求和 Sum,求平均(Average)等等~
可选参数:
是否包含表头:
[field_headers],缺失:自动。0:否 1:是且不显示 2:否,但生成 3:是并显示
它其实就是针对行列值字段名称进行显示,一般来说我们很少用。因为有点丑
行总计 / 小计,行排序:
[row_total_depth],缺失:自动:总计和小计(如果可能)。0:无总计 1:总计 2:总计和小计-1:顶部的总和-2:顶部的总计和小计[row_sort_order],一个数字,1 代表行字段的第一列,以此类推……正数表示升序,负数代表降序
类比于数据透视表 👇
总计类似于数据透视表的总计设置。
小计类似于数据透视表的分类汇总设置。
对应的 Pivotby 函数公式如下,参数为 2 是显示总计和小计。
效果跟数据透视表一样。
行排序参数,-1 就是代表行汇总的第一列,也就是姓名列进行降序排序,反之 1 就是升序。
列小计 / 总计,列排序(与上面同理):
[col_total_depth],[col_sort_order],
数据源筛选:
[filter_array],//筛选
假如,我们事先需要对数据源进行筛选后,再来进行透视汇总,这个参数就有用了。
它就有点类似于数据透视表中的筛选字段功能。
如下图,先将数据中数字大于 60 的数据筛选出来,再进行透视分析。
相关方式:
[relative_to]可能的值为:0:列汇总 (默认值) 1:行总计 2:总计 3:父列总计 4:父级行总计
这个参数可能较难理解,但不用担心,我们可以通过类比数据透视表来理解它。
我们在数据透视表的数据区域单击鼠标右键,选择值显示方式,可以看到值显示方式有,总计的百分比,列汇总的百分比,行汇总的百分比等等。
当第四参数,函数汇总方式为 Percentof 时,相关参数就是来控制值显示方式的。
Percentof 函数对子集中的值求和,并将其除以所有值。
它通常用于 GROUPBY 和 PIVOTBY 函数,用来求相关的百分比。
=PERCENTOF (data_subset,data_all)
就是 Sum (子集)/Sum (总集)
我们将数据透视表中的值显示方式设置为「列汇总的百分比」。
如下图,对应的 Pivotby 函数公式,我们将最后参数设置为 0,也就是「列总计」,效果是跟上面数据透视表结果是一样的。
同理,我们将数据透视表的值显示方式设置为「行总计的百分比」,这与 Pivotby 函数中将最后一个参数设置为「行总计」的效果是一样。
这样,通过数据透视表进行类比,我们是不是就更容易理解 Pivotby 函数了~
到这里,我们对 Pivotby 函数的多个参数已经介绍完了。
3、总结一下
我们在做数据分析的时候,通常需要对数据进行多个维度的透视分析。
以前,传统做法我们会使用条件函数对数据进行汇总统计(比如使用 Sumifs,Countifs 函数等等),有时我们还会使用 Sumproduct 函数进行汇总。
针对多字段汇总分析,用条件函数就显得不太方便了,所以我们会使用数据透视表进行分析。
但是数据透视表不能够实时更新,还需要我们手动刷新。
现在,Pivotby 函数出现,它基于透视分析进行设计,满足了我们实时更新的需求。
它一共有 11 个参数,通过类比我们熟悉的数据透视表,可以快速理解这些参数的用法。
四个必选参数:
前三个参数对应行字段,列字段,值字段,对应数据透视表三区域。
汇总方式(它是个函数参数,使得这个函数更加灵活,后面有机会我们继续介绍)。
七个可选参数:
其中行总计小计,行排序方式,有点类似于数据透视表的总计和分类汇总。
筛选参数,可以事先对数据源进行筛选,类似数据透视表的筛选字段。
相关方式,当汇总方式为 Percentof,该参数可以控制值显示的百分比方式。
本文来自微信公众号:秋叶 Excel(ID:excel100),作者:小爽
广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。