點擊藍字【秋葉 Excel】

發送【7】

免費領 1000+篇 Excel 精選教程！

本文作者：小爽

本文編輯：竺蘭

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

之前，我帶大家了解了 Pivotby 函數的基礎用法，里面我們提及了 11 個參數（）。

• 行字段，列字段，值字段

• 聚合方式 （由于是個函數參數，所以使得這個函數更加靈活）

• 是否顯示表頭 （顯示的表頭很丑，我們一般不用）

• 行總計/小計，行排序

• 列總計/小計，列排序

• 數據源篩選

• 相關方式，當匯總函數為pecentof時，可以控制值顯示方式。比如列總計的百分比，行總計的百分比...

今天我們就來了解一下，第四參數：聚合方式。

這個參數有兩個特點。

第一：自帶內置的 Lambda 函數，定義如何進行聚合。

第二：它可以是多個 Lambda 函數聚合后的矢量數組，數組的方向決定擴展的方向。

由于 Pivotby 函數的第四個參數是 function 參數，通過編寫自定義函數 Lambda，我們可以實現更靈活的匯總方式。

聚合參數中的 Lambda 函數可以接收兩個參數，我們接下來來看看它的用法。

傳遞一個參數

如下圖，我們對值區域【數字字段】進行求和，聚合方式為 Sum。

上面的聚合方式我們也可以使用 Lambda 函數。

Sum 等同于 LAMBDA(x, SUM(x))。

當 Lambda 函數傳遞一個參數時，如 Lambda(x,x)，其中 x 代表行列篩選后的值字段區域。

我們來類比數據透視表。

如下圖，我們鼠標雙擊 80 這個單元格。

80 這個值就是數據源對行列篩選后，數字字段進行聚合求和 Sum 的結果。

知道參數的含義后，我們可以有更靈活的做法。

比如我們想取行列篩選后的第一個數值，如下圖：

=PIVOTBY(A2:A13,B2:B13,E2:E13,LAMBDA(x,SUM(TAKE(x,1))))

這里的聚合方式，使用 Take 函數取第一個值，最后用 Sum 函數進行聚合，就可以得到我們想要的結果。

同理也可以取數字字段的最后一個值。

PS.Take 函數的第二個參數為 1 時，取第一個值，為 -1 時取最后一個值。

=PIVOTBY(A2:A13,B2:B13,E2:E13,LAMBDA(x,SUM(TAKE(x,-1))))

接下來，我們來看個例子~

如下圖，我們想找到每個部門每周最后一個值班人員的姓名。

這里的聚合方式自定義函數編寫中，由于姓名字段中存在短橫線，我們使用 Filter 函數將「-」篩選掉，再使用 Take 函數取最后一個值，最后由于值是文本，我們使用 Concat 函數進行聚合。

=PIVOTBY(D2:D35,B2:B35,E2:E35,LAMBDA(x,CONCAT(TAKE(FILTER(x,x<>"-"),-1))),,0,,0)

除了要知道最后一個值班人的姓名，我們還要知道值班人的個數。

這里我們利用它的第二個特點，通過多個 Lambda 函數實現矢量擴展。

=PIVOTBY(D2:D35,"第"&B2:B35&"周",E2:E35,HSTACK(LAMBDA(x,CONCAT(TAKE(FILTER(x,x<>"-"),-1))),LAMBDA(x,COUNTA(FILTER(x,x<>"-")))),,0,,0)

自定義 1 和自定義 2，我們可以使用 Vstack 函數將標題接上去。

PS.二維數組中的第一行會被 Excel 識別為值，第二行被識別為標題。所以使用 Vstack(值數組,標題數組)，進行拼接。標題數組需要為行，所以{"最后的值班姓名","人數"}為逗號拼接。

=PIVOTBY(D2:D35,"第"&B2:B35&"周",E2:E35,VSTACK(HSTACK(LAMBDA(x,CONCAT(TAKE(FILTER(x,x<>"-"),-1))),LAMBDA(x,COUNTA(FILTER(x,x<>"-")))),{"最后的值班姓名","人數"}),,0,,0)

看函數公式感覺會很復雜，其實只是自定義函數的拼接。

LAMBDA(x,CONCAT(TAKE(FILTER(x,x<>"-"),-1))) //求最后一個值班人的姓名
LAMBDA(x,COUNTA(FILTER(x,x<>"-")))  //求總個數

使用 Hstack 函數將兩個自定義函數進行橫向拼接。
使用 VSTACK 函數將值數組，標題數組進行拼接。

傳遞兩個參數

如下圖，當我們的聚合方式為 pecentof 時，最后有一個參數默認列總計。

這時求的是列的百分比。

聚合方式我們也可以使用 Lambda 函數。

上面等同于 LAMBDA(x,y,SUM(x)/SUM(y))

當 Lambda 函數傳遞兩個參數時，

第一個參數：行列篩選后的值區域

第二個參數：由最后一個相關方式參數控制。該參數默認為列總計，即表示每一列按照列篩選形成的區域，相當于就是忽略了行的篩選。

舉個例子~

如下圖，最后相關方式參數不寫，默認參數代表列總計。

對應的 y 參數，就代表忽略行字段篩選，只篩選列字段的區域。

我們使用 Sumif 函數進行測試，只篩選部門列并求和，結果跟 Pivotby 函數是一樣的。

有了聚合方式的這兩個參數，我們可以做更靈活的聚合做法。

如下圖，我們要求學生的每科成績是否大于當前總成績的平均值。

函數公式如下：

=PIVOTBY(A2:A13,B2:B13,C2:C13,LAMBDA(x,y,IF(SUM(x)>AVERAGE(y),"√","")),,0,,0,,,1)

簡單解釋一下函數公式，主要看聚合方式和相關方式。

相關方式中，參數設置為 1 也就是行總計：

聚合方式中：

LAMBDA(x,y,IF(SUM(x)>AVERAGE(y),"√",""))

Sum(x)，將行列篩選后的成績進行聚合。

AVERAGE(y)，相關方式參數為 1，按照行總計。

參數 y 對應的就是，保留行篩選 （姓名） ，忽略列篩選 （科目）

每一個姓名都有三個科目的成績，也就是 y，直接求平均值即可。

然后進行判斷 SUM(x)>AVERAGE(y)，if 函數執行如果為 TRUE 顯示為√，否則為空。

到這里，聚合方式參數中神奇的 Lambda 函數就介紹完了。

最后的話

本文介紹了Pivotby 函數中的高階用法。

由于它的第四參數是個 function 參數，支持 Lambda 函數進行自定義，所以使得它更加靈活，但是也同時增加了該函數的學習難度。

它可以傳遞兩個參數，第一個參數是行列篩選后的值區域，第二參數由最后一個參數相關方式所決定，它決定我們篩選時，保留或者忽略哪些字段。 （一般不怎么用）

存在 function 參數的函數，除了 Pivotby 函數外，還有 Reduce 函數，Scan 函數......

掌握了 function 參數，就是從函數的進階邁向了高階！

大家在工作中還遇到過哪些 Excel 問題，也可以在留言區中聊聊~

Excel 熟練=給每天偷回 2 小時，人生苦短，可別浪費在低效工作中！

如果你也想提升 Excel 技能，升職加薪不加班，

那就一定要加入《秋葉 Excel 3 天集訓營》課程——

專業老師手把手教學表格排版+數據處理+工作匯報， 助你提高效率、減輕工作壓力~

《秋葉 Excel 3 天集訓營》

原價 99 元

今天限時免費

搶占學習名額

開啟高效辦公之旅吧！

▲ 現在報名，即可免費領取超多福利資料