Excel 新出的 Pivotby 函数

原标题：《Excel新推出的Pivotby函数，实在非常实用！》

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

作为表格的制作达人，在处理数据分析时，我们经常需要对特定字段进行汇总统计。

1、常规做法

有时我们使用 Sumifs 函数来实现统计目的。

如下所示：用 Sumifs 函数对姓名与部门进行交叉汇总。

不过，在一些情况下，我们需要对某些列进行修正，而 Sumifs 函数不支持数组参数，因而只能使用 Sumproduct 函数。

如下图所示，Sumifs 函数的公式返回错误。

我们转而使用 Sumproduct 函数进行逻辑判断，从而获得了结果。

在需要切换其他字段进行分析时，上述方法需要重新构建函数公式，并先进行行列去重，再写汇总公式，着实麻烦~

因此，我们还可以通过数据透视表的方式统计，只需简单点击即可完成。

尽管数据透视表操作简单，但它无法像函数那样进行实时更新。

因此，透视表函数应运而生，那就是 Pivotby 函数！

2、Pivotby 函数简介

Pivotby 函数是一种透视表函数，尽管与数据透视表相似，但它们之间没有直接关系。

目前，Pivotby 函数已经在 Office 365 和 WPS 中发布。

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），作者：小爽

广告声明：文中包含的外部链接（如超链接、二维码、口令等），用于提供更多信息以节省筛选时间，最终结果仅供参考。