Excel新出的Pivotby函数，实在太好用了！

点击蓝字【秋叶 Excel】

发送【7】

免费领 1000+篇 Excel 精选教程！

本文作者：小爽

本文编辑：卫星酱

大家好，这里是秋叶编辑部~

作为表哥表姐的我们，在做数据分析的时候，经常需要对指定的字段信息进行汇总统计。

常规做法

有时我们会使用 Sumifs 函数。

如下图 : 使用 Sumifs 函数对姓名和部门字段进行交叉汇总求和。

但是我们有时需要对指定列进行修饰，在进行判断的时候，由于 Sumifs 函数不支持数组参数，我们只能使用 Sumproduct 函数。

如下图，Sumifs 函数公式错误。

如下图，我们转用 Sumproduct 函数，进行逻辑判断得出了结果。

当我们需要切换其他字段进行分析的时候，上面的做法需要重新编写函数公式，要先对行列字段进行去重，再编写汇总函数公式。

这就有点麻烦~

因此，我们还可以通过数据透视表的方式进行统计，仅需鼠标操作即可完成。

但数据透视表方便是方便，它却无法像函数一样实时更新。

所以，透视表函数出现了，它就是 Pivotby 函数！

什么，你说 0 基础小白看不懂这些函数？

那现在就扫码加入《秋叶 Excel 3 天集训营》！

✅ 视频+录播+实操练习+助教答疑

✅ Excel 和 WPS 双软件教学

✅ 免费赠送表格模板+35个函数公式手册

和秋叶一起学 Excel，助你高效办公不加班

别再犹豫！立即扫码报名吧

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 函数的多个参数已经介绍完了。

总结一下

我们在做数据分析的时候，通常需要对数据进行多个维度的透视分析。

以前，传统做法我们会使用条件函数对数据进行汇总统计 （比如使用 Sumifs，Countifs 函数等等） ，有时我们还会使用 Sumproduct 函数进行汇总。

针对多字段汇总分析，用条件函数就显得不太方便了，所以我们会使用数据透视表进行分析。

但是数据透视表不能够实时更新，还需要我们手动刷新。

现在，Pivotby 函数出现，它基于透视分析进行设计，满足了我们实时更新的需求。

它一共有 11 个参数，通过类比我们熟悉的数据透视表，可以快速理解这些参数的用法。

四个必选参数：

前三个参数对应行字段，列字段，值字段，对应数据透视表三区域。

汇总方式（它是个函数参数，使得这个函数更加灵活，后面有机会我们继续介绍） 。

七个可选参数：

其中行总计小计，行排序方式，有点类似于数据透视表的总计和分类汇总。

筛选参数，可以事先对数据源进行筛选，类似数据透视表的筛选字段。

相关方式，当汇总方式为 Percentof，该参数可以控制值显示的百分比方式。

大家在工作中还遇到过哪些 Excel 问题，也可以在留言区中聊聊~

如果你还想让老师手把手教你更多提高工作效率的 Excel 实用技巧，那就来《秋叶 Excel 3 天集训营》吧！

这里有专业老师@拉登Dony教你表格设计+数据处理+数据可视化，带你玩转 Excel，解锁你的职场晋升密码！

《秋叶 Excel 3 天集训营》

原价 99 元

现在限时 1 元

抢占学习名额

开启高效办公之旅吧！

▲ 报名后自动弹出班主任微信

扫码添加，才能上课、领取资料：