點擊關注【秋葉 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 元

現在限時 0 元

搶占學習名額

開啟高效辦公之旅吧！

點擊關注【秋葉 Excel】

免費獲取 Excel 干貨、模板