點(diǎn)擊關(guān)注【秋葉 Excel】
發(fā)送【7】領(lǐng) 1000 篇 Excel 精選教程！
作者：小爽
編輯：衛(wèi)星醬
大家好，這里是秋葉編輯部~
作為表哥表姐的我們，在做數(shù)據(jù)分析的時(shí)候，經(jīng)常需要對指定的字段信息進(jìn)行匯總統(tǒng)計(jì)。
常規(guī)做法
有時(shí)我們會使用 Sumifs 函數(shù)。
如下圖 : 使用 Sumifs 函數(shù)對姓名和部門字段進(jìn)行交叉匯總求和。
但是我們有時(shí)需要對指定列進(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í)候，上面的做法需要重新編寫函數(shù)公式，要先對行列字段進(jìn)行去重，再編寫匯總函數(shù)公式。
這就有點(diǎn)麻煩~
因此，我們還可以通過數(shù)據(jù)透視表的方式進(jìn)行統(tǒng)計(jì)，僅需鼠標(biāo)操作即可完成。
但數(shù)據(jù)透視表方便是方便，它卻無法像函數(shù)一樣實(shí)時(shí)更新。
所以，透視表函數(shù)出現(xiàn)了，它就是 Pivotby 函數(shù)！
什么，你說 0 基礎(chǔ)小白看不懂這些函數(shù)？
那現(xiàn)在就掃碼加入《秋葉 Excel 3 天集訓(xùn)營》！
? 視頻+錄播+實(shí)操練習(xí)+助教答疑
? Excel 和 WPS 雙軟件教學(xué)
? 免費(fèi)贈送表格模板+35個(gè)函數(shù)公式手冊
和秋葉一起學(xué) Excel，助你高效辦公不加班
別再猶豫！立即掃碼報(bào)名吧
Pivotby 函數(shù)
Pivotby 函數(shù)是一個(gè)透視表函數(shù)，它跟我們的數(shù)據(jù)透視表類似，但是跟數(shù)據(jù)透視表沒直接關(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ù)就會了。
我們來看它的參數(shù)，它的參數(shù)看起來很多，實(shí)際上并不難，就對著數(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會 Pivotby 函數(shù)。
必選參數(shù)：
行字段，列字段，值字段，匯總方式：
row_fields，col_fields，values，function前三參數(shù)依次對應(yīng)的就是數(shù)據(jù)透視表顯示字段三個(gè)字段。
如下圖，對應(yīng)的 Pivotby 函數(shù)公式。
第四參數(shù)我們可以選擇多種匯總方式，比如說求和 Sum，求平均（Average）等等~
可選參數(shù)：
是否包含表頭：
[field_headers]，
缺失：自動。
0：否
1：是且不顯示
2：否，但生成
3：是并顯示它其實(shí)就是針對行列值字段名稱進(jìn)行顯示，一般來說我們很少用。因?yàn)橛悬c(diǎn)丑
行總計(jì)/小計(jì)，行排序：
[row_total_depth]，
缺失：自動：總計(jì)和小計(jì)（如果可能）。
0：無總計(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è)置。
對應(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]，//篩選假如，我們事先需要對數(shù)據(jù)源進(jìn)行篩選后，再來進(jìn)行透視匯總，這個(gè)參數(shù)就有用了。
它就有點(diǎn)類似于數(shù)據(jù)透視表中的篩選字段功能。
如下圖，先將數(shù)據(jù)中數(shù)字大于 60 的數(shù)據(jù)篩選出來，再進(jìn)行透視分析。
相關(guān)方式：
[relative_to]
可能的值為：
0：列匯總 (默認(rèn)值)
1：行總計(jì)
2：總計(jì)
3：父列總計(jì)
4：父級行總計(jì)這個(gè)參數(shù)可能較難理解，但不用擔(dān)心，我們可以通過類比數(shù)據(jù)透視表來理解它。
我們在數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域單擊鼠標(biāo)右鍵，選擇值顯示方式，可以看到值顯示方式有，總計(jì)的百分比，列匯總的百分比，行匯總的百分比等等。
當(dāng)?shù)谒膮?shù)，函數(shù)匯總方式為 Percentof 時(shí)，相關(guān)參數(shù)就是來控制值顯示方式的。
Percentof 函數(shù)對子集中的值求和，并將其除以所有值。 它通常用于 GROUPBY 和 PIVOTBY 函數(shù)，用來求相關(guān)的百分比。 =PERCENTOF (data_subset，data_all) 就是 Sum(子集)/Sum(總集)
我們將數(shù)據(jù)透視表中的值顯示方式設(shè)置為「列匯總的百分比」。
如下圖，對應(yīng)的 Pivotby 函數(shù)公式，我們將最后參數(shù)設(shè)置為 0，也就是「列總計(jì)」，效果是跟上面數(shù)據(jù)透視表結(jié)果是一樣的。
同理，我們將數(shù)據(jù)透視表的值顯示方式設(shè)置為「行總計(jì)的百分比」，這與 Pivotby 函數(shù)中將最后一個(gè)參數(shù)設(shè)置為「行總計(jì)」的效果是一樣。
這樣，通過數(shù)據(jù)透視表進(jìn)行類比，我們是不是就更容易理解 Pivotby 函數(shù)了~
到這里，我們對 Pivotby 函數(shù)的多個(gè)參數(shù)已經(jīng)介紹完了。
我們在做數(shù)據(jù)分析的時(shí)候，通常需要對數(shù)據(jù)進(jìn)行多個(gè)維度的透視分析。
以前，傳統(tǒng)做法我們會使用條件函數(shù)對數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)（比如使用 Sumifs，Countifs 函數(shù)等等），有時(shí)我們還會使用 Sumproduct 函數(shù)進(jìn)行匯總。
針對多字段匯總分析，用條件函數(shù)就顯得不太方便了，所以我們會使用數(shù)據(jù)透視表進(jìn)行分析。
但是數(shù)據(jù)透視表不能夠?qū)崟r(shí)更新，還需要我們手動刷新。
現(xiàn)在，Pivotby 函數(shù)出現(xiàn)，它基于透視分析進(jìn)行設(shè)計(jì)，滿足了我們實(shí)時(shí)更新的需求。
它一共有 11 個(gè)參數(shù)，通過類比我們熟悉的數(shù)據(jù)透視表，可以快速理解這些參數(shù)的用法。
四個(gè)必選參數(shù)：
前三個(gè)參數(shù)對應(yīng)行字段，列字段，值字段，對應(yīng)數(shù)據(jù)透視表三區(qū)域。
匯總方式（它是個(gè)函數(shù)參數(shù)，使得這個(gè)函數(shù)更加靈活，后面有機(jī)會我們繼續(xù)介紹）。
七個(gè)可選參數(shù)：
其中行總計(jì)小計(jì)，行排序方式，有點(diǎn)類似于數(shù)據(jù)透視表的總計(jì)和分類匯總。
篩選參數(shù)，可以事先對數(shù)據(jù)源進(jìn)行篩選，類似數(shù)據(jù)透視表的篩選字段。
相關(guān)方式，當(dāng)匯總方式為 Percentof，該參數(shù)可以控制值顯示的百分比方式。
大家在工作中還遇到過哪些 Excel 問題，也可以在留言區(qū)中聊聊~
如果你還想讓老師手把手教你更多提高工作效率的 Excel 實(shí)用技巧，那就來《秋葉 Excel 3 天集訓(xùn)營》吧！
這里有專業(yè)老師@拉登Dony教你表格設(shè)計(jì)+數(shù)據(jù)處理+數(shù)據(jù)可視化，帶你玩轉(zhuǎn) Excel，解鎖你的職場晉升密碼！
《秋葉 Excel 3 天集訓(xùn)營》
原價(jià) 99 元
現(xiàn)在限時(shí) 0 元
搶占學(xué)習(xí)名額
開啟高效辦公之旅吧！
點(diǎn)擊關(guān)注【秋葉 Excel】
免費(fèi)獲取 Excel 干貨、模板
熱門跟貼