这个Excel新函数，厉害到颤抖！

点击蓝字【秋叶 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 元

今天限时免费

抢占学习名额

开启高效办公之旅吧！

▲ 现在报名，即可免费领取超多福利资料