原文標題(ti)：《Excel 新出的 Pivotby 函數，實在太好用了！》

大家好，這里是秋葉編(bian)輯(ji)部(bu)~

作為(wei)表哥(ge)表姐的我(wo)們，在(zai)做數據(ju)分析(xi)的時(shi)候，經常(chang)需要(yao)對指定的字段信息進(jin)行(xing)匯(hui)總(zong)統計(ji)。

1、常規做法

有時(shi)我們會使用(yong) Sumifs 函數。

如下圖: 使用 Sumifs 函數對(dui)姓(xing)名和部門(men)字段進行交叉匯總求和。

但是我們有時需要(yao)對指(zhi)定列進行(xing)修飾，在(zai)進行(xing)判斷的(de)時候，由于 Sumifs 函(han)數不支持數組參(can)數，我們只能使用 Sumproduct 函(han)數。

如下(xia)圖(tu)，Sumifs 函(han)數公式錯誤。

如下圖(tu)，我(wo)們轉用 Sumproduct 函數，進行邏輯判斷得(de)出(chu)了結果(guo)。

當(dang)我們需要切換其他字(zi)段進行分析(xi)的(de)時(shi)候，上面的(de)做法(fa)需要重新(xin)編寫函數公(gong)(gong)式，要先對行列(lie)字(zi)段進行去(qu)重，再(zai)編寫匯(hui)總函數公(gong)(gong)式。

這就有點麻煩~

因此，我們還可(ke)以通過(guo)數據透視(shi)表的方(fang)式進行(xing)統計(ji)，僅(jin)需鼠標操(cao)作即可(ke)完成。

但數據透視表方(fang)便是方(fang)便，它卻(que)無法像(xiang)函數一(yi)樣實時更新。

所以，透視表函(han)數出現了，它就是 Pivotby 函(han)數！

2、Pivotby 函數

Pivotby 函數(shu)是(shi)一個透視(shi)表(biao)函數(shu)，它跟我們的數(shu)據(ju)透視(shi)表(biao)類似，但是(shi)跟數(shu)據(ju)透視(shi)表(biao)沒直接(jie)關系。

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

Groupby 函(han)數(shu)（即分組函(han)數(shu)）是基于某個(ge)字(zi)段(duan)進行匯總統計的。

Pivotby 函數是基于行列字段篩選后進(jin)行匯總統計(ji)的(de)。

Groupby 函(han)(han)數是 Pivotby 函(han)(han)數的一種特(te)殊情況，所以(yi)這兩個函(han)(han)數參(can)數差不多(duo)，掌(zhang)握(wo)了 Pivotby 函(han)(han)數函(han)(han)數，Groupby 函(han)(han)數就會了。

我們來看它(ta)的參數(shu)，它(ta)的參數(shu)看起來很多(duo)，實際(ji)上并不難(nan)，就(jiu)對著(zhu)數(shu)據透視(shi)表學就(jiu)是了！

=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]) //相關方式，一般用在百分比

只要你了解數據透視表，就(jiu)很快能夠學(xue)會 Pivotby 函數。

必選參數：

行字(zi)段，列字(zi)段，值字(zi)段，匯總方式(shi)：

row_fields，col_fields，values，function

前三參數(shu)依次對應的就是(shi)數(shu)據透視表顯示(shi)字(zi)段三個字(zi)段。

如下圖(tu)，對應的 Pivotby 函數公式。

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

可選參數：

是否包含表頭：

[field_headers]，
缺失：自動。
0：否 
1：是且不顯示 
2：否，但生成 
3：是并顯示

它其實就是針(zhen)對行(xing)列值字(zi)段名(ming)稱進(jin)行(xing)顯示(shi)，一般來說我們很少用。因為有點丑

行(xing)總計 / 小(xiao)計，行(xing)排序：

[row_total_depth]，
缺失：自動：總計和小計（如果可能）。
0：無總計 
1：總計 
2：總計和小計
-1：頂部的總和
-2：頂部的總計和小計

[row_sort_order]，
一個數字，1 代表行字段的第一列，以此類推……
正數表示升序，
負數代表降序

類(lei)比(bi)于數據透視(shi)表 ??

總計類似于數據透視表的總計設(she)置。

小計類似(si)于數(shu)據透視表的分(fen)類匯總設置。

對應的(de) Pivotby 函數公(gong)式如下(xia)，參(can)數為 2 是顯(xian)示總計和小計。

效果(guo)跟數據透視表一(yi)樣。

行排(pai)(pai)序參(can)數，-1 就是(shi)代表行匯總的(de)第一列(lie)(lie)，也就是(shi)姓名列(lie)(lie)進行降序排(pai)(pai)序，反之 1 就是(shi)升序。

列小(xiao)計 / 總計，列排(pai)序(xu)（與上面(mian)同理）：

[col_total_depth]，[col_sort_order]，

數據源篩選：

[filter_array]，//篩選

假如(ru)，我們事先需要(yao)對(dui)數據源(yuan)進(jin)行篩選后，再(zai)來進(jin)行透視匯總，這個參數就有用了。

它(ta)就有點(dian)類似于數據(ju)透視表(biao)中的篩選字(zi)段功能。

如下圖，先將(jiang)數據中數字大于 60 的數據篩選出來，再進行(xing)透視分析。

相關方式：

[relative_to]
可能的值為：
0：列匯總 (默認值) 
1：行總計 
2：總計 
3：父列總計 
4：父級行總計

這個參數(shu)可(ke)能較難理解(jie)，但不用擔心，我們可(ke)以通(tong)過類比數(shu)據透視表來理解(jie)它。

我(wo)們在數(shu)據(ju)透(tou)視(shi)表的(de)數(shu)據(ju)區域(yu)單擊鼠標右鍵，選(xuan)擇值顯(xian)示方式(shi)，可以看到值顯(xian)示方式(shi)有(you)，總計的(de)百分比(bi)(bi)，列匯總的(de)百分比(bi)(bi)，行匯總的(de)百分比(bi)(bi)等等。

當第四參數(shu)，函數(shu)匯總方式(shi)為 Percentof 時，相關參數(shu)就是來控制值顯(xian)示方式(shi)的。

Percentof 函數對子(zi)集中的值(zhi)求(qiu)和(he)，并將(jiang)其除以所有值(zhi)。

它通常用(yong)于 GROUPBY 和 PIVOTBY 函數，用(yong)來求(qiu)相關的(de)百(bai)分(fen)比。

=PERCENTOF (data_subset，data_all)

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

我們將數據透視表中(zhong)的值顯(xian)示方式設置為「列(lie)匯總(zong)的百分比」。

如下圖，對應的 Pivotby 函數(shu)公式，我們將最后參數(shu)設置為 0，也就是「列(lie)總計」，效果是跟上面數(shu)據透視表結果是一樣的。

同(tong)理(li)，我(wo)們將數(shu)據透視表(biao)的(de)(de)值顯示方式設(she)置(zhi)為(wei)(wei)「行總計(ji)的(de)(de)百分(fen)比」，這與 Pivotby 函數(shu)中將最后一個(ge)參數(shu)設(she)置(zhi)為(wei)(wei)「行總計(ji)」的(de)(de)效果(guo)是一樣。

這樣(yang)，通過數(shu)據透(tou)視表進(jin)行(xing)類(lei)比，我(wo)們是(shi)不是(shi)就更(geng)容易理解 Pivotby 函數(shu)了~

到(dao)這里，我(wo)們對 Pivotby 函(han)數的多個參數已經介紹(shao)完(wan)了。

3、總結一下

我(wo)們在做數據(ju)分析的(de)時(shi)候(hou)，通常需要(yao)對數據(ju)進行多個維度(du)的(de)透視分析。

以前，傳(chuan)統做法(fa)我們會(hui)使(shi)(shi)用條件函(han)數(shu)對數(shu)據進行匯總統計（比如使(shi)(shi)用 Sumifs，Countifs 函(han)數(shu)等(deng)(deng)等(deng)(deng)），有時我們還會(hui)使(shi)(shi)用 Sumproduct 函(han)數(shu)進行匯總。

針對多字段(duan)匯總分析，用(yong)條件函數(shu)就顯得不太(tai)方便了(le)，所以(yi)我(wo)們(men)會使(shi)用(yong)數(shu)據(ju)透視表進行分析。

但是數據透視表不能夠實時更(geng)新，還需(xu)要我們手動刷新。

現在，Pivotby 函數出(chu)現，它基于(yu)透視分析進(jin)行設計，滿足了(le)我們(men)實時更新的需求(qiu)。

它一(yi)共(gong)有 11 個參數(shu)，通(tong)過類(lei)比(bi)我們熟悉的(de)數(shu)據透視表，可以快速理(li)解這些參數(shu)的(de)用法。

四個必選參數：

前三個參數對應行字段，列字段，值字段，對應數據透(tou)視表三區域。

匯總方式（它是個函(han)(han)數參數，使得這個函(han)(han)數更加靈活，后面有機會我們繼續介紹(shao)）。

七個可選參數：

其中行總計小計，行排序方式，有點類(lei)似于數(shu)據透視表的總(zong)計和分類(lei)匯總(zong)。

篩選參數，可以事先對數據源進行篩(shai)選(xuan)，類似數據透視(shi)表的篩(shai)選(xuan)字段。

相關方式，當匯(hui)總(zong)方式為 Percentof，該參(can)數可以控(kong)制值顯示(shi)的百分比方式。

本文來自微信公眾號(hao)：，作者：小爽

廣告聲明(ming)：文內含有的(de)對外跳轉鏈(lian)接（包括不限于超鏈(lian)接、二維碼、口(kou)令等形式），用于傳(chuan)遞更多信息，節省甄(zhen)選時(shi)間，結果僅供參(can)考，IT之家所(suo)有文章(zhang)均包含本聲明(ming)。