![]()
戳蓝字“Excel星球”关注我哦。
HI，大家好，我是星光。
今天给大家聊一下微软MS365测试版本中的一个新函数：groupby。如果你经常身体在公司上班灵魂却在excel圈子里快活的话![]()
应该对这个函数的名字比较熟悉。圈子里热闹和传言都没有错，这家伙确实是数据分组统计的函数神器，在未来，也必然属于最常用的函数之一，就像VLOOKUP以及她姐LOOKUP和她妹XLOOKUP那样。
GROUPBY ( row_fields， values， function， [field_headers]， [total_depth]， [sort_order]， [filter_array] [filter_relationship] )
嘿~它居然一共有8个参数，前3个必需，剩下5个可选。不过参数虽然有点多，但每个参数的功能性都很明确，实际上并不难理解——除了第3参数。
第3参数的类型是function。在迭代函数体系里，但凡类型是function的参数，都不是什么温柔的好东西![]()
摊手，它会十分强大但也可以万分复杂。
下面举几个例子，给大家介绍一下GROUPBY函数的基本用法和各个参数的计算顺序及相关作用。
假设有一份你从小到老![]()
都很熟悉的成绩表，如下图所示。
F2单元格输入以下公式，可以统计各个班级每个学生的成绩总分=GROUPBY(A1:B13,D1:D13,SUM)
GROUPBY第1参数是分组依据的区域或数组，第2参数是需要聚合的值区域或数组，第3参数是聚合表达式。本例中，我们需要按A1:B13的班级和姓名作为分组依据，对D1:D13区域的成绩聚合，聚合的方式是求和，也就是SUM。除了SUM之外，系统还内置了MAX/MIN/COUNTA/CONCAT等快捷聚合方式。
GROUPBY的第4参数表示第1参数的分组数据是否有表头，可以根据实际需要进行设置，一共有4种类型。0表示没有表头；1表示有表头，但不显示。2表示没有表头，但需要生成默认表头。3表示有表头且显示。
我们将上述公式中的第4参数设置为2，表示1参没有表头，但需要生成默认，返回结果如下：=GROUPBY(A1:B13,D1:D13,SUM,2)
![]()
GROUPBY的第5参数表示是否显示总计或小计行，它有5种类型可选，如下图所示。![]()
我们将上述公式中的第5参数设置为2，就可以显示总计和小计行。
=GROUPBY(A1:B13,D1:D13,SUM,,2)
![]()
4丨 排序
GROUPBY的第6参数可以对返回的结果表进行基于分组依据列内部的数据排序，用一个数字对应结果表中的列数，当数字为正数时表示升序，负数时表示降序。例如，数字2，表示对结果表中的第2列数据执行升序排序，数字-3，表示对结果表中第3列的数据执行降序排序。
将上述公式中的第6参数设置为-3，可以对各个班级内部的成绩，也就是结果表第3列的数据，执行降序排序，结果如下：
=GROUPBY(A1:B13,D1:D13,SUM,,0,-3)
![]()
需要注意的是，它这里是对分组的数据执行分类排序，而不是整张结果表。如果需要对整张结果表的成绩降序排序，可以将第8参数设置为1，表示排序不受分组字段的分类限制，执行全表排序（同时也会自动取消生成总计和小计行）。
=GROUPBY(A1:B13,D1:D13,SUM,,0,-3,,1)
![]()
5丨 筛选
GROUPBY的第7参数可以对第1参数的分组依据执行筛选操作，它是一个由逻辑值构成的数组，这个数组的尺寸大小需要和第1参数保持一致。
如果只需要对一班的学生统计总分，同时降序排序，参考代码如下：
=GROUPBY(A1:B13,D1:D13,SUM,,0,-3, A1:A13="一班")
函数的第7参数为A1:A13="一班"，先筛选A1:A13区域是否等于一班，然后再执行聚合等操作。
以上是7个参数的基本功能，这里需要补充说一下7个参数的运算顺序，它并不是按照出现的先后顺序作运算的，就像你的他/她…们。Excel首先运行的是第1和第2参数，读取分组依据和对应值，然后运行第7参数，对第1步的计算结果进行筛选，接着是第3参数，执行聚合运算，再排序，添加总计和小计行，最后设置表头。
打个响指，接下来重点说一下第3参数，也就是function。
看我小眼神，看懂了吧？——不管是洋文还是译文总之讲的都不是人话![]()
。
摊手，总结起来，这个第3参数主要有两个规则或者说特点。首先，它有内置的LAMBDA表达式，存在一个默认的参数，指向第1参数分组后的每个值区域块。其次，它的结果可以是由多个内置的lambda表达式聚合后的元素构成的数组，数组的方向将决定结果是按行还是按列展开。
以下代码可以计算每个班级每个学员的成绩总分。这是你已经知道的👆![]()
=GROUPBY(A1:B13,D1:D13,SUM)
但你可能不知道的是，这个公式的第3参数SUM，是一种语法糖，所谓语法糖就是语句的简写形式，完整的形式是一个LAMBDA表达式，如下：
=GROUPBY(A1:B13,D1:D13, LAMBDA(x,SUM(x)))
换而言之，这里的SUM是LAMBDA(x,SUM(x))的简写形式。LAMBDA是GROUPBY内置的一个匿名函数，它有一个默认的参数，指向分组后的每组成绩。这里设置其名为x(x的名字不是固定的，你可以自由选择，叫阿猫阿狗也行)，然后使用SUM函数对其聚合。
同样的道理，以下代码可以将每个班级的人名去重后合并成一个字符串，彼此之间用短横杠相连。
=GROUPBY(A1:A13,B1:B13, LAMBDA(阿猫, TEXTJOIN("-",1,UNIQUE(阿猫)) ), 1,0)
![]()
公式的第3参数是LAMBDA(阿猫,TEXTJOIN("-",1,UNIQUE(阿猫))，先使用unique函数对分组后的人名去重，再使用TEXTJOIN函数聚合成一个字符串。假设不但需要统计每个班级每个学员的总分，同时还需要统计平均分。
=GROUPBY(A1:B13,D1:D13, VSTACK(SUM,AVERAGE), 1,0)
公式的第3参数是VSTACK(SUM,AVERAGE)，它包含了两种内置的lambda聚合函数语法糖，分别执行SUM求和与AVERAGE求平均的聚合运算，最后使用VSTACK纵向按行合并。由于它是纵向按行合并的，返回的是一维垂直数组，根据第2个规则，计算结果也是纵向按行展开。展开后的标题名字默认为SUM/AVERAGE，看起来有点奇怪，对此，我们可以做一个修改，改成中文名称总分和平均分，参考公式如下：=GROUPBY(A1:B13,D1:D13, HSTACK( VSTACK(SUM,AVERAGE), {"总分";"平均分"} ) ,1,0)
公式中，使用了HSTACK函数将由多个lambda表达式聚合的元素 构成纵向数组和常量数组{"总分";"平均分"}横向合并，数组的第2列值即为值对应的标题名，注意常量数组中元素分隔使用的是分号，而不是逗号，两者之间的区别，我们在「什么是函数数组」里详细讲过了，希望你还有印象![]()
以上公式是将总分和平均分按行纵向展开，如果你需要横向按列展开，只需要将多个lambda表达式聚合的元素横向合并即可:=GROUPBY(A1:B13,D1:D13, VSTACK( HSTACK(SUM,AVERAGE), {"总分","平均分"} ), 3,0)
![]()
这就是第3参数的第2个特点，它的结果可以是由多个内置的lambda表达式的聚合元素构成的数组，数组的方向将决定结果是按行还是按列展开。除此之外，它还有一些其它小特点甚至是大毛病，篇幅原因，咱们就不展开说了——打这么多字，我倦了，倦的像一朵被风折断的野花![]()
。看一道综合小练习题，放松一下吧![]()
![]()
。如下图所示，A:C列是数据源，包含了姓名、月份和销售额。需要统计每个人每个季度的在个人总销售额的占比情况，并横向展开。=GROUPBY( A1:A13,C1:C13, VSTACK( MAP( {1,2,3,4}, LAMBDA(_m,LAMBDA(_x,@INDEX(_x,_m)/SUM(_x))) ), {1,2,3,4}&"季度" ), 3,0)
第3行到第9行代码是GROUPBY函数的第3参数，它先使用迭代函数MAP+三参内置的LAMBDA表达式，迭代聚合每个人销售额占自身总销售额的占比，返回一个由内置LAMBDA表达式聚合生成的多个元素的水平数组。
LAMBDA(_m,LAMBDA(_x,@INDEX(_x,_m)/SUM(_x)))
第1个LAMBDA的变量_m指向MAP的第1参数 {1,2,3,4}，第2个LAMBDA的变量_x，指向GROUPBY分组后的每块区域。@INDEX(_x,_m)/SUM(_x)依次取每季度销售额和总销售额做占比运算，由于它返回的是单值，可以使用@表示聚合运算。这里你也可以使用SUM等函数替代@实现聚合的要求：
LAMBDA(_m,LAMBDA(_x,SUM(INDEX(_x,_m)/SUM(_x))))
如果你已经忘记了MAP函数，可以重阅往期教程：「MAP」
另外，这部分不能写成以下形式，如我们上面所讲述的，它并不符合3参的规则。![]()
打个响指，看我真诚的小眼神，本期推文只是给大家简单介绍下GROUPBY的基本用法、参数计算顺序和第3参数的主要规则，更多典型的案例我们在后面的推文或者知识星球内再聊。有什么问题照例可以在VIP会员微信群中提问交流。下期再见，挥手，拜拜。
⏩需要系统学习Excel却找不到优质教程？学习Excel的过程中遇到疑难问题，却找不到人及时作出解答？加入我的付费社群，学习+训练+答疑，与5000+在线会员一起，同微软&金山最有价值专家MVP&KVP同行，全面精进Excel之道![]()
