groupby基本用法与参数说明

目前MS OFFICE版本2408，Excel终于可以使用groupby公式，留一篇学习笔记。groupby公式的完整写法如下：

groupby(row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array]，[field_relationship])

下面演示将基于这个数据源以用于说明各个参数的作用

A1={
"attendance_type","date_type","attendance_date","hours","pay";
"出勤","工作日","2024-04-01",8,99;
"出勤","工作日","2024-04-02",8,109;
"出勤","工作日","2024-04-03",8,101;
"出勤","调休日","2024-04-07",8,101;
"出勤","工作日","2024-04-08",8,99;
"出勤","工作日","2024-04-09",8,117;
"出勤","工作日","2024-04-10",8,107;
"出勤","工作日","2024-04-11",8,116;
"出勤","工作日","2024-04-12",8,98;
"出勤","工作日","2024-04-15",8,104;
"出勤","工作日","2024-04-16",8,109;
"出勤","工作日","2024-04-17",8,112;
"出勤","工作日","2024-04-18",8,108;
"出勤","工作日","2024-04-19",8,105;
"出勤","工作日","2024-04-22",8,104;
"出勤","工作日","2024-04-23",8,106;
"出勤","工作日","2024-04-24",8,105;
"出勤","工作日","2024-04-25",8,103;
"出勤","工作日","2024-04-26",8,112;
"出勤","调休日","2024-04-28",8,109;
"出勤","工作日","2024-04-29",8,102;
"出勤","工作日","2024-04-30",8,109;
"加班","工作日","2024-04-02",1,7.5;
"加班","工作日","2024-04-11",1.5,19.25;
"加班","工作日","2024-04-18",4,56;
"加班","调休日","2024-04-28",1.5,22.25;
"加班","工作日","2024-04-29",1,15.5
}

基本用法

这个函数必输的参数为前三个，其含义如下：

• row_fields：分组依据
• values：要聚合的值
• function：聚合的方式，可以使用官方已经提供的eta规范的公式，或者使用lambda自定义聚合函数

例如当在G2单元格输入=GROUPBY(B1:B28,D1:D28,SUM)，row_fields参数为date_type的值，指按照date_type进行分组；values参数为hours的值，表示分组后对hours聚合；function参数为使用SUM说明对聚合的值进行的计算方式为求和。所以得到结果如下：

前三参的多列用法

如题前三参可以使用多列，其中row_fields参数多列代表多个分组维度，values参数多列表示聚合多个维度的值，在使用需要保证row_fields和values行数等长。例如在H2单元格中输入公式=GROUPBY(A1:B28,D1:E28,SUM)，表示根据
attendance_type和date_type进行分组，计算每组中hours和pay的合计值，结果如下：

第三个参数的多列需要使用拼接函数（Hstack()、Vstack()）对结果进行拼接，使用后的效果需要联系第二参数分情况讨论：

• 如果第二参数只有一列，则第三参可以任意多列，表示对第二参数据的多种运算并展示结果，如图：
• 如果第三参数只有一种聚合方式，则第二参数可以任意多列，表示对第二参多列数据的进行同一种运算并展示结果，如图：
• 如果第二参数与第三参数都是多列，则需要第二参数的列与第三参数的列数相等，计算逻辑也是按照其一一对应的方式，如图：

上面例子中的演示都为标题横向排列，也可以通过将Hstack()修改为Vstack(),实现纵向排列，如图：

lambda自定义第三参

以上例子中第三参数都是使用系统预制公式，实际上可以使用lambda实现自定义聚合函数，在定义聚合函数时最多可以使用两个参数。
第二参数可以省略不写。当只有一个参数时，该参数表示分组后的值。例如公式=GROUPBY(A1:B28,D1:D28,LAMBDA(x,REPT("*",COUNTA(x))))，表示根据分组后的非空单元格个数显示多个“*”构成的字符串，如图：
第二参数不忽略时，第一参数依然代表分组后的值，第二参数代表groupby中第二参数(values)对应列的值。例如公式=GROUPBY(A1:B28,D1:D28,LAMBDA(x,y,TEXT(AVERAGE(x)/AVERAGE(y),"0.00%")))，表示组内平均值与全列平均的比值，如图：

第四参数

第四参数field_headers作用为控制分组结果的标题显示，通过0、1、2、3四个值对在前面参数中是否包含标题和标题是否需要显示在结果中这两个维度上进行控制。下面分别对0、1、2、3四个参数值的作用进行说明：

• 【0】：row_fields、values的数据不含标题，并且结果中不显示标题。例如公式=GROUPBY(A1:A28,D1:D28,COUNTA,0)，由于field_headers值为0，会将第一行数据进行分组操作从而出现在分组后的结果数据中，所以会得到下列结果
• 【1】：row_fields、values的数据包含标题，但是标题结果中不显示。例如公式=GROUPBY(A1:A28,D1:D28,COUNTA,1)，因为field_headers值为1，所以第一行数据不会被进行分组操作出现在结果数据中，同时也不会出现在分组结果的标题中，所以有下面的结果
• 【2】：row_fields、values的数据不含标题，但是会在分组后自动生成标题。例如公式=GROUPBY(A1:A28,D1:D28,COUNTA,2)，由于field_headers值为2，所以第一行数据被进行分组操作后出现在结果数据中，同时系统将自动生成标题，所以有下面的结果
• 【3】：row_fields、values的数据包含标题，并且将标题显示在结果中。例如公式GROUPBY(A1:A28,D1:D28,COUNTA,3)，由于field_headers值为3，所以第一行数据不会被分组进行结果数据中，但是会作为分组后的标题显示，所以有如下结果

特别的，当该参数未指定值时，系统会默认不显示标题，并自动检查前面第二参数values中第一行值类型与第二行是否相同来判断第一行数据是否为标题从而得出要进行分组的数据。所以公式=GROUPBY(A1:A28,D1:D28,COUNTA)与=GROUPBY(A2:A28,D2:D28,COUNTA)得结果一致，如下图：

第五参数

第五参数total_depth作用为控制分组结果的合计显示，通过0、1、2、-1、-2五个参数值从是否显示合计、显示哪些合计、在哪里显示合计进行递进控制。下面分别对0、1、2、-1、-2五个参数作用进行说明：

• 【0】：不显示合计
• 【1】：结果最后一行显示总计，不显示组内小计
• 【2】：结果最后一行显示总计，每一个组的最后一行显示小计。只有分组列数大于1才会出现小计，并只会按照第一列小计而不是按照多维度分别小计在未显式指定第八参数时
• 【-1】：结果数据顶端显示总计，不显示组内小计
• 【-2】：结果数据顶端显示总计，每一个组的顶端显示小计。只有分组列数大于1才会出现小计，并只会按照第一列小计而不是按照多维度分别小计在未显式指定第八参数时

第六参数

第六参数sort_order用于对分组后的结果排序，按照排序对象可分为分组列排序和分组数据排序，按照排序数量可分为单列排序和多列排序。
对分组列使用整数或者整数数组实现单列或者多列排序，其中数字的绝对值为分组后要排序的列，正负号表示升序或者降序排序。例如公式=GROUPBY(HSTACK(A1:A28,VSTACK("weekday",WEEKDAY(C2:C28,2))),D1:E28,MAX,,,-2)，在未显式指定第八参数或者第八参数值不为1时，表示对第二列weekday进行降序排列，未指定的第一列默认升序（拼音首字母升序）排列，如下图
上面公式与=GROUPBY(HSTACK(A1:A28,VSTACK("weekday",WEEKDAY(C2:C28,2))),D1:E28,MAX,,,{1,-2})以及=GROUPBY(HSTACK(A1:A28,VSTACK("weekday",WEEKDAY(C2:C28,2))),D1:E28,MAX,,,{-2,1})等价，因为分组列排序顺序：左起第一列>左起第二列>…，如下图

对分组数据的排序只能单列进行排序，排序方式类似对分组列排序，使用整数实现对数据列的排序，其中数的绝对值代表要排序的是分组后中的第几列，正负号表示升序或者降序。例如公式=GROUPBY(HSTACK(A1:A28,VSTACK("weekday",WEEKDAY(C2:C28,2))),D1:E28,MAX,,,-4)，表示对第四列进行降序排列，在未显式指定第八参数或者第八参数值不为1的情况下，系统会先根据各组最大值进行组件排列，再对每一组组内进行降序排列，同时对值的排序会默认添加组小计，如下图

第七参数

第七参数filter_array用于对数据在分组前进行筛选，其参数值为与第一、第二参数行等长的布尔数组。需要注意的是如果前面已经确认（不论是否显式）第一二参数中第一行为标题，则filter_array值的第一行数据无效，也就是公式=GROUPBY(A1:B28,E1:E28,COUNTA,,,,VSTACK(TRUE,MAKEARRAY(27,1,LAMBDA(r,c,TRUE))))与=GROUPBY(A1:B28,E1:E28,COUNTA,,,,VSTACK(FALSE,MAKEARRAY(27,1,LAMBDA(r,c,TRUE))))结果相同,，结果如下图

第七参数filter_array支持多列进行输入，筛选时根据第一列数组处理。所以公式=GROUPBY(A1:B28,E1:E28,COUNTA,,,,HSTACK({1;1;1;1;0;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1},{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;1;1}))与=GROUPBY(A1:B28,E1:E28,COUNTA,,,,{1;1;1;1;0;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})结果相同，

第八参数

第八参数field_relationship用于对指明第一参数row_fields（分组列、行标签）的各列之间是否存在层次结构。例如第一参数的数据为省市县、类别名称、一级科目二级科目或者其它类似关系，则存在层次结构。

系统默认第一参数row_fields的数据都存在层次结构，当然也可以将参数值设置为0显式指明存在层次结构。存在层次结构的数据才可以显示小计，并且排序时按照上文“第六参数”中描述的规则进行排序。

当第八参数field_relationship的值设置为1时，无法进行小计，例如公式=GROUPBY(A1:B28,E1:E28,COUNTA,,2,,,1)将提示错误，如图
排序时不在份组内与组间排序，将直接针对结果数据进行排序。例如公式=GROUPBY(A1:B28,E1:E28,COUNTA,,,-3,,1)按照第三列降序排列时，将不再先获取“加班”和“出勤”的最大值进行组间排序而后再组内排序，而是会按照第三列每一行的值直接降序排列，如下图