原文標(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)行交叉匯總求和。
但是我們有時(shí)需要對(duì)指定列進(jìn)行修飾，在進(jìn)行判斷的時(shí)候，由于 Sumifs 函數(shù)不支持?jǐn)?shù)組參數(shù)，我們只能使用 Sumproduct 函數(shù)。
如下圖，Sumifs 函數(shù)公式錯(cuò)誤。
如下圖，我們轉(zhuǎn)用 Sumproduct 函數(shù)，進(jìn)行邏輯判斷得出了結(jié)果。
當(dāng)我們需要切換其他字段進(jìn)行分析的時(shí)候，上面的做法需要重新編寫(xiě)函數(shù)公式，要先對(duì)行列字段進(jìn)行去重，再編寫(xiě)匯總函數(shù)公式。
這就有點(diǎn)麻煩~
因此，我們還可以通過(guò)數(shù)據(jù)透視表的方式進(jìn)行統(tǒng)計(jì)，僅需鼠標(biāo)操作即可完成。
但數(shù)據(jù)透視表方便是方便，它卻無(wú)法像函數(shù)一樣實(shí)時(shí)更新。
所以，透視表函數(shù)出現(xiàn)了，它就是 Pivotby 函數(shù)！
2、Pivotby 函數(shù)
Pivotby 函數(shù)是一個(gè)透視表函數(shù)，它跟我們的數(shù)據(jù)透視表類似，但是跟數(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è)字段。
如下圖，對(duì)應(yīng)的 Pivotby 函數(shù)公式。
第四參數(shù)我們可以選擇多種匯總方式，比如說(shuō)求和 Sum，求平均（Average）等等~
可選參數(shù)：
是否包含表頭：
[field_headers]， 缺失：自動(dòng)。 0：否 1：是且不顯示 2：否，但生成 3：是并顯示
它其實(shí)就是針對(duì)行列值字段名稱進(jìn)行顯示，一般來(lái)說(shuō)我們很少用。因?yàn)橛悬c(diǎn)丑
行總計(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 代表行字段的第一列，以此類推…… 正數(shù)表示升序， 負(fù)數(shù)代表降序
類比于數(shù)據(jù)透視表 ??
總計(jì)類似于數(shù)據(jù)透視表的總計(jì)設(shè)置。
小計(jì)類似于數(shù)據(jù)透視表的分類匯總設(shè)置。
對(duì)應(yīng)的 Pivotby 函數(shù)公式如下，參數(shù)為 2 是顯示總計(jì)和小計(jì)。
效果跟數(shù)據(jù)透視表一樣。
行排序參數(shù)，-1 就是代表行匯總的第一列，也就是姓名列進(jìn)行降序排序，反之 1 就是升序。
列小計(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)類似于數(shù)據(jù)透視表中的篩選字段功能。
如下圖，先將數(shù)據(jù)中數(shù)字大于 60 的數(shù)據(jù)篩選出來(lái)，再進(jìn)行透視分析。
相關(guān)方式：
[relative_to] 可能的值為： 0：列匯總 (默認(rèn)值) 1：行總計(jì) 2：總計(jì) 3：父列總計(jì) 4：父級(jí)行總計(jì)
這個(gè)參數(shù)可能較難理解，但不用擔(dān)心，我們可以通過(guò)類比數(shù)據(jù)透視表來(lái)理解它。
我們?cè)跀?shù)據(jù)透視表的數(shù)據(jù)區(qū)域單擊鼠標(biāo)右鍵，選擇值顯示方式，可以看到值顯示方式有，總計(jì)的百分比，列匯總的百分比，行匯總的百分比等等。
當(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è)置為「列匯總的百分比」。
如下圖，對(duì)應(yīng)的 Pivotby 函數(shù)公式，我們將最后參數(shù)設(shè)置為 0，也就是「列總計(jì)」，效果是跟上面數(shù)據(jù)透視表結(jié)果是一樣的。
同理，我們將數(shù)據(jù)透視表的值顯示方式設(shè)置為「行總計(jì)的百分比」，這與 Pivotby 函數(shù)中將最后一個(gè)參數(shù)設(shè)置為「行總計(jì)」的效果是一樣。
這樣，通過(guò)數(shù)據(jù)透視表進(jìn)行類比，我們是不是就更容易理解 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ò)類比我們熟悉的數(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)類似于數(shù)據(jù)透視表的總計(jì)和分類匯總。
篩選參數(shù)，可以事先對(duì)數(shù)據(jù)源進(jìn)行篩選，類似數(shù)據(jù)透視表的篩選字段。
相關(guān)方式，當(dāng)匯總方式為 Percentof，該參數(shù)可以控制值顯示的百分比方式。
本文來(lái)自微信公眾號(hào)：秋葉 Excel（ID：excel100），作者：小爽
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。