Excel 新出的 Pivotby 函數(shù)

秋葉Excel 2025/3/9 12:10:05 責(zé)編：夢(mèng)澤評(píng)論：

原文標(biāo)題：《Excel 新出的 Pivotby 函數(shù)，實(shí)在太好用了！》

大家好，這里是秋葉編輯部~

作為表哥表姐的我們，在做數(shù)據(jù)分析的時(shí)候，經(jīng)常需要對(duì)指定的字段信息進(jìn)行匯總統(tǒng)計(jì)。

1、常規(guī)做法

有時(shí)我們會(huì)使用 Sumifs 函數(shù)。

如下圖: 使用 Sumifs 函數(shù)對(duì)姓名和部門(mén)字段進(jìn)行交叉匯總求和。

Excel 新出的 Pivotby 函數(shù)

但是我們有時(shí)需要對(duì)指定列進(jìn)行修飾，在進(jìn)行判斷的時(shí)候，由于 Sumifs 函數(shù)不支持?jǐn)?shù)組參數(shù)，我們只能使用 Sumproduct 函數(shù)。

如下圖，Sumifs 函數(shù)公式錯(cuò)誤。

Excel 新出的 Pivotby 函數(shù)

如下圖，我們轉(zhuǎn)用 Sumproduct 函數(shù)，進(jìn)行邏輯判斷得出了結(jié)果。

Excel 新出的 Pivotby 函數(shù)

當(dāng)我們需要切換其他字段進(jìn)行分析的時(shí)候，上面的做法需要重新編寫(xiě)函數(shù)公式，要先對(duì)行列字段進(jìn)行去重，再編寫(xiě)匯總函數(shù)公式。

這就有點(diǎn)麻煩~

因此，我們還可以通過(guò)數(shù)據(jù)透視表的方式進(jìn)行統(tǒng)計(jì)，僅需鼠標(biāo)操作即可完成。

Excel 新出的 Pivotby 函數(shù)

但數(shù)據(jù)透視表方便是方便，它卻無(wú)法像函數(shù)一樣實(shí)時(shí)更新。

所以，透視表函數(shù)出現(xiàn)了，它就是 Pivotby 函數(shù)！

2、Pivotby 函數(shù)

Pivotby 函數(shù)是一個(gè)透視表函數(shù)，它跟我們的數(shù)據(jù)透視表類(lèi)似，但是跟數(shù)據(jù)透視表沒(méi)直接關(guān)系。

目前，Office 365 和 WPS 都有 Pivotby 函數(shù)。

Groupby 函數(shù)（即分組函數(shù)）是基于某個(gè)字段進(jìn)行匯總統(tǒng)計(jì)的。

Pivotby 函數(shù)是基于行列字段篩選后進(jìn)行匯總統(tǒng)計(jì)的。

Groupby 函數(shù)是 Pivotby 函數(shù)的一種特殊情況，所以這兩個(gè)函數(shù)參數(shù)差不多，掌握了 Pivotby 函數(shù)函數(shù)，Groupby 函數(shù)就會(huì)了。

我們來(lái)看它的參數(shù)，它的參數(shù)看起來(lái)很多，實(shí)際上并不難，就對(duì)著數(shù)據(jù)透視表學(xué)就是了！

=PIVOTBY (row_fields，col_fields，values，//行字段，列字段，值字段 

function，//匯總方式，是個(gè)函數(shù)[field_headers]，//是否包含標(biāo)題

[row_total_depth]，[row_sort_order]，//是否顯示行總計(jì)/小計(jì)，行排序方式

[col_total_depth]，[col_sort_order]，//是否顯示行總計(jì)/小計(jì)，行排序方式

[filter_array]，//篩選[relative_to]) //相關(guān)方式，一般用在百分比

只要你了解數(shù)據(jù)透視表，就很快能夠?qū)W會(huì) Pivotby 函數(shù)。

必選參數(shù)：

行字段，列字段，值字段，匯總方式：

row_fields，col_fields，values，function

前三參數(shù)依次對(duì)應(yīng)的就是數(shù)據(jù)透視表顯示字段三個(gè)字段。

Excel 新出的 Pivotby 函數(shù)

如下圖，對(duì)應(yīng)的 Pivotby 函數(shù)公式。

Excel 新出的 Pivotby 函數(shù)

第四參數(shù)我們可以選擇多種匯總方式，比如說(shuō)求和 Sum，求平均（Average）等等~

Excel 新出的 Pivotby 函數(shù)

可選參數(shù)：

是否包含表頭：

[field_headers]，

缺失：自動(dòng)。

0：否 

1：是且不顯示 

2：否，但生成 

3：是并顯示

Excel 新出的 Pivotby 函數(shù)

它其實(shí)就是針對(duì)行列值字段名稱進(jìn)行顯示，一般來(lái)說(shuō)我們很少用。因?yàn)橛悬c(diǎn)丑

Excel 新出的 Pivotby 函數(shù)

行總計(jì) / 小計(jì)，行排序：

[row_total_depth]，

缺失：自動(dòng)：總計(jì)和小計(jì)（如果可能）。

0：無(wú)總計(jì) 

1：總計(jì) 

2：總計(jì)和小計(jì)

-1：頂部的總和

-2：頂部的總計(jì)和小計(jì)

[row_sort_order]，

一個(gè)數(shù)字，1 代表行字段的第一列，以此類(lèi)推……

正數(shù)表示升序，

負(fù)數(shù)代表降序

類(lèi)比于數(shù)據(jù)透視表 ??

總計(jì)類(lèi)似于數(shù)據(jù)透視表的總計(jì)設(shè)置。

Excel 新出的 Pivotby 函數(shù)

小計(jì)類(lèi)似于數(shù)據(jù)透視表的分類(lèi)匯總設(shè)置。

Excel 新出的 Pivotby 函數(shù)

對(duì)應(yīng)的 Pivotby 函數(shù)公式如下，參數(shù)為 2 是顯示總計(jì)和小計(jì)。

效果跟數(shù)據(jù)透視表一樣。

Excel 新出的 Pivotby 函數(shù)

行排序參數(shù)，-1 就是代表行匯總的第一列，也就是姓名列進(jìn)行降序排序，反之 1 就是升序。

Excel 新出的 Pivotby 函數(shù)

列小計(jì) / 總計(jì)，列排序（與上面同理）：

[col_total_depth]，[col_sort_order]，

數(shù)據(jù)源篩選：

[filter_array]，//篩選

假如，我們事先需要對(duì)數(shù)據(jù)源進(jìn)行篩選后，再來(lái)進(jìn)行透視匯總，這個(gè)參數(shù)就有用了。

它就有點(diǎn)類(lèi)似于數(shù)據(jù)透視表中的篩選字段功能。

Excel 新出的 Pivotby 函數(shù)

如下圖，先將數(shù)據(jù)中數(shù)字大于 60 的數(shù)據(jù)篩選出來(lái)，再進(jìn)行透視分析。

Excel 新出的 Pivotby 函數(shù)

相關(guān)方式：

[relative_to]

可能的值為：

0：列匯總 (默認(rèn)值) 

1：行總計(jì) 

2：總計(jì) 

3：父列總計(jì) 

4：父級(jí)行總計(jì)

這個(gè)參數(shù)可能較難理解，但不用擔(dān)心，我們可以通過(guò)類(lèi)比數(shù)據(jù)透視表來(lái)理解它。

我們?cè)跀?shù)據(jù)透視表的數(shù)據(jù)區(qū)域單擊鼠標(biāo)右鍵，選擇值顯示方式，可以看到值顯示方式有，總計(jì)的百分比，列匯總的百分比，行匯總的百分比等等。

Excel 新出的 Pivotby 函數(shù)

當(dāng)?shù)谒膮?shù)，函數(shù)匯總方式為 Percentof 時(shí)，相關(guān)參數(shù)就是來(lái)控制值顯示方式的。

Percentof 函數(shù)對(duì)子集中的值求和，并將其除以所有值。

它通常用于 GROUPBY 和 PIVOTBY 函數(shù)，用來(lái)求相關(guān)的百分比。

=PERCENTOF (data_subset，data_all)

就是 Sum (子集)/Sum (總集)

我們將數(shù)據(jù)透視表中的值顯示方式設(shè)置為「列匯總的百分比」。

Excel 新出的 Pivotby 函數(shù)

如下圖，對(duì)應(yīng)的 Pivotby 函數(shù)公式，我們將最后參數(shù)設(shè)置為 0，也就是「列總計(jì)」，效果是跟上面數(shù)據(jù)透視表結(jié)果是一樣的。

Excel 新出的 Pivotby 函數(shù)

同理，我們將數(shù)據(jù)透視表的值顯示方式設(shè)置為「行總計(jì)的百分比」，這與 Pivotby 函數(shù)中將最后一個(gè)參數(shù)設(shè)置為「行總計(jì)」的效果是一樣。

Excel 新出的 Pivotby 函數(shù)

這樣，通過(guò)數(shù)據(jù)透視表進(jìn)行類(lèi)比，我們是不是就更容易理解 Pivotby 函數(shù)了~

到這里，我們對(duì) Pivotby 函數(shù)的多個(gè)參數(shù)已經(jīng)介紹完了。

3、總結(jié)一下

我們?cè)谧鰯?shù)據(jù)分析的時(shí)候，通常需要對(duì)數(shù)據(jù)進(jìn)行多個(gè)維度的透視分析。

以前，傳統(tǒng)做法我們會(huì)使用條件函數(shù)對(duì)數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)（比如使用 Sumifs，Countifs 函數(shù)等等），有時(shí)我們還會(huì)使用 Sumproduct 函數(shù)進(jìn)行匯總。

針對(duì)多字段匯總分析，用條件函數(shù)就顯得不太方便了，所以我們會(huì)使用數(shù)據(jù)透視表進(jìn)行分析。

但是數(shù)據(jù)透視表不能夠?qū)崟r(shí)更新，還需要我們手動(dòng)刷新。

現(xiàn)在，Pivotby 函數(shù)出現(xiàn)，它基于透視分析進(jìn)行設(shè)計(jì)，滿足了我們實(shí)時(shí)更新的需求。

它一共有 11 個(gè)參數(shù)，通過(guò)類(lèi)比我們熟悉的數(shù)據(jù)透視表，可以快速理解這些參數(shù)的用法。

四個(gè)必選參數(shù)：

前三個(gè)參數(shù)對(duì)應(yīng)行字段，列字段，值字段，對(duì)應(yīng)數(shù)據(jù)透視表三區(qū)域。

匯總方式（它是個(gè)函數(shù)參數(shù)，使得這個(gè)函數(shù)更加靈活，后面有機(jī)會(huì)我們繼續(xù)介紹）。

七個(gè)可選參數(shù)：

其中行總計(jì)小計(jì)，行排序方式，有點(diǎn)類(lèi)似于數(shù)據(jù)透視表的總計(jì)和分類(lèi)匯總。

篩選參數(shù)，可以事先對(duì)數(shù)據(jù)源進(jìn)行篩選，類(lèi)似數(shù)據(jù)透視表的篩選字段。

相關(guān)方式，當(dāng)匯總方式為 Percentof，該參數(shù)可以控制值顯示的百分比方式。

本文來(lái)自微信公眾號(hào)：秋葉 Excel（ID：excel100），作者：小爽