新函数GROUPBY和PIVOTBY函数，它们不但能对数据分类汇总，还能进行排序与筛选，其中PIVOTBY函数还可以制作成交叉表。一、GROUPBY函数GROUPBY：根据指定的列对数据进行分组、聚合、排序和筛选语法：=GROUPBY (row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array])参数说明row_fields（必需）一个面向列的数组或区域，其中包含用于对行进行分组和生成行标题的值。数组或区域可以包含多个列。 如果是这样，输出将具有多个行组级别。values（必需）要聚合计算的数据的面向列的数组或区域。数组或区域可以包含多个列。 如果是这样，输出将具有多个聚合。函数（必需）用于聚合值的显式或 eta 缩减 lambda (SUM、PERCENTOF、AVERAGE、COUNT 等 )可以提供 lambda 的向量。 如果是这样，输出将具有多个聚合。 矢量的方向将确定它们是按行还是按列布局。field_headers表一个数字，指定 row_fields 和 值是否具有标头，以及是否应在结果中返回字段标头。 可能的值为：缺失：自动 (默认)0：否1：是，不显示2：否，但生成3：是并显示注意: 自动假定数据包含基于 values 参数的标头。 如果第 1 个值为文本，第 2 个值为数字，则假定数据具有标头。 如果有多个行或列组级别，则显示字段标头。total_depth确定行标题是否应包含总计。 可能的值为：缺失：自动：总计，如果可能，小计 (默认)0：无总计1：总计，总计行显示在最后一行2：总计和小计，其中总计行显示在最后一行-1：总计行显示在第一行-2： 总计和小计，其中总计行显示在第一行注意: 对于小计， 字段 必须至少包含 2 列。 如果 字段具有足够的列，则支持大于 2 的数字。sort_order用来对行排序的新数组的列号。 如果数字为负数，则行按降序/反向排序。仅基于 row_fields进行排序时，可以提供数字向量。filter_array筛选条件。一个面向列的 1D 布尔值数组，指示是否应考虑相应的数据行。注意: 数组的长度必须与提供给 row_fields的长度匹配。条件用法可参数FILTER函数的第二个参数。field_relationship指定向row_fields提供多个列时的关系字段。 可能的值为：0：层次结构 (默认)1：表使用“层次结构”字段关系 (0) ，对后续字段列进行排序会考虑早期列的层次结构。如果表字段关系 (1) ，则每个字段列的排序是独立完成的。 不支持小计，因为它们依赖于具有层次结构的数据。这个函数参数一共有7个，虽然看起来多，但是也并不算复杂，只有第一到第三参数是必选参数，其余的参数都是可以省略的。下面举例说明：1、分类汇总=GROUPBY(A1:A10,E1:E10,SUM)参数1：A1:A10 采购方式所在的列参数2：E1:E10 采购总价所在的列参数3：SUM表示求和第三参数是有很多汇总方式的，详见下图，可以选择自己需要的，这个就是它的基本用法

图片

2、多层汇总公式：=GROUPBY(A1:B10,D1:E10,AVERAGE)这个函数不是仅仅只能对一列进行分类汇总，还能设置多个汇总字段。下图就是根据采购方式以及商品名称的数据用户金额的平均值其实在这里我们可以简单的把第一参数看作是透视表的行区域，第二参数看做是透视表的值区域。第三参数说计算方式。

图片

3、是否包含表头上面的案例中都有没有表头的，如果你想显示表头，就需要设置第四参数，输入对应的代码即可0：数据源中没有表头1：数据源中有表头但是不显示2：数据源中没有表头，但是需要生成1个新的表头3：数据源中有表头，并且显示表头在这个参数中0，1，3都比较容易理解，关键是参数2，如果数据源中没有表头，就会生成新的表头，以字段1，值1这样的命名方式来添加新表头，这个表头也是无法修改的，效果如下图所示

图片

4、是否需要显示总计与小计GROUPBY不但能显示总计还能显示小计，就需要设置第五参数，也是需要输入对应的代码来设置小计与总计0：没有总计1：显示总计2：显示总计与小计-1：在顶部显示总计-2：在顶部显示总计与小计具体的效果如下图所示，关键点需要明白，如果你想显示小计，第一参数至少需要设置2列数据，否则的话就会显示为错误值 -1与-2是将小计与总计放在表格上方的就不再演示了，大家可以操作这试一下

图片

5、是否需要排序在这里正数表示升序，负数表示降序，只能根据1列数据来做升序或者降序的排序。排序列数是根据函数的结果来指定的如下图，将第六参数设置为2，就表示根据结果表的第2列进行升序或者降序的排序

图片

6、同时汇总列示各销售人员的销售总额和销售额平均值想知道每个销售人员的销售总额，还想看看平均销售额是多少，可以用这个公式：=GROUPBY (B1:B201,E1:E201,HSTACK (SUM,AVERAGE),,0)

图片

这里的 HSTACK (SUM,AVERAGE) 表示对 “销售额” 这一列同时进行求和以及求平均值的操作。7、汇总各销售人员的销售总量和销售额平均值要是想对不同列进行不同的汇总操作，比如对 “数量” 求和，对 “销售额” 求平均值，公式可以这样写：=GROUPBY (B2:B201,D2:E201,HSTACK (SUM,AVERAGE))

图片

这样就能得到每个销售人员的销售总量和销售额平均值啦。8、按部门汇总人员姓名GROUPBY 函数不仅能处理数字，还能处理文本呢。要是想按部门把人员姓名汇总起来，可以用这个公式：=GROUPBY (A1:A20,B1:B20,ARRAYTOTEXT,3,0)

图片

这里的 ARRAYTOTEXT 表示把人员姓名的数组转换成文本形式进行汇总。以上公式与以下公式结果是一样的。=GROUPBY(A1:A20,B1:B20,LAMBDA(x,TEXTJOIN(",",1,x)),3,0)9、按条件筛选的汇总表如果只想汇总符合某些条件的数据，比如只汇总不同部门男士的姓名，公式可以这么写：=GROUPBY (A1:A20,B1:B20,ARRAYTOTEXT,3,0,,C1:C20="男")

图片

最后一个参数 C1:C20="男" 就是筛选条件，表示只对性别为 “男” 的数据进行汇总。二、PIVOTBY函数PIVOTBY函数，它其实就是透视表的函数版，功能非常非常的强大，搭配新函数还能实现多表统计，相较于透视表最大的优点就是能自动更新结果。PIVOTBY函数：根据指定的行列字段，对数据进行分组、聚合、排序、筛选语法：PIVOTBY (row_fields，col_fields，values，function，[field_headers]，[row_total_depth]，[row_sort_order]，[col_total_depth]，[col_sort_order]，[filter_array]，[relative_to])参数说明row_fields（必需）一个面向列的数组或区域，其中包含用于对行进行分组和生成行标题的值。数组或区域可以包含多个列。 如果是这样，输出将具有多个行组级别。col_fields（可选）一个面向列的数组或区域，其中包含用于对列进行分组和生成列标题的值。数组或区域可以包含多个列。 如果是这样，输出将具有多个列组级别。此参数若忽略，则功能与GROUPBY函数相似。values（必需）要聚合的数据的面向列的数组或区域。数组或区域可以包含多个列。 如果是这样，输出将具有多个聚合。函数（必需）定义如何聚合值的 lambda 函数或 eta-reduced lambda (SUM、AVERAGE、COUNT等) 。可以提供 lambda 的向量。 如果是这样，输出将具有多个聚合。 矢量的方向将确定它们是按行还是按列布局。field_headers一个数字，指定 row_fields、col_fields 和 值是否具有标头，以及是否应在结果中返回字段标头。 可能的值为：缺失：自动。0：否1：是且不显示2：否，但生成3：是并显示注意: 自动假定数据包含基于 values 参数的标头。 如果第 1 个值为文本，第 2 个值为数字，则假定数据具有标头。 如果有多个行或列组级别，则显示字段标头。row_total_depth确定行标题是否应包含总计。 可能的值为：缺失：自动：总计和小计（如果可能）。0：无总计1：总计，总计行显示在最后一行2：总计和小计，其中总计行显示在最后一行-1：总计行显示在第一行-2： 总计和小计，其中总计行显示在第一行注意: 对于小计， row_fields 必须至少包含 2 列。 如果row_field有足够的列，则支持大于 2 的数字。row_sort_order一个数字，指示应如何对列进行排序。 数字对应于 row_fields 中的列，后跟 值中的列。 如果数字为负数，则行按降序/反向排序。仅基于 row_fields进行排序时，可以提供数字向量。col_total_depth确定列标题是否应包含总计。 可能的值为：缺失：自动：总计和小计（如果可能）。0：无总计1：总计，总计行显示在最后一列2：总计和小计，其中总计行显示在最后一列-1：总计行显示在数值区域的第一列-2： 总计和小计，其中总计行显示在数值区域的第一列注意: 对于小计， col_fields 必须至少包含 2 列。 如果col_field有足够的列，则支持大于 2 的数字。col_sort_order一个数字，指示应如何对行进行排序。 数字对应于 col_fields 中的列，后跟 值中的列。 如果数字为负数，则行按降序/反向排序。仅基于 col_fields进行排序时，可以提供数字向量。filter_array筛选条件。一个面向列的 1D 布尔值数组，指示是否应考虑相应的数据行。注意: 数组的长度必须与提供给 row_fields 和 col_fields的长度匹配。条件用法可参数FILTER函数的第二个参数。relative_to使用需要两个参数的聚合函数时，relative_to 控制向聚合函数的第二个参数提供哪些值。 这通常在为函数提供PERCENTOF时使用。可能的值为：0：列汇总 (默认)1：行总计2：总计3：父总计4：父行总计注意: 仅当 函数 需要两个参数时，此参数才会产生影响。 如果向 函数提供自定义 lambda 函数，则它应遵循以下模式：LAMBDA (子集、totalset、SUM (子集) /SUM (totalset) )PIVOTBY函数的用法与GROUPBY函数差不多，只是多将源表列值转换为行标题的的参数的使用。具体用法如下：1、常规用法比如现在我们想要根据商品名称来计算每种采购方式的总数。公式：=PIVOTBY(B1:B10,A1:A10,D1:D10,SUM)第一参数：商品名称列第二参数：采购方式列第三参数：采购的数量列第四参数：SUM统计方式，求和这个函数它其实就是透视表的函数版，第一参数可以看做行区域，第二参数看做列区域，第三参数看做值区域，第四参数是统计的方式。第四参数是可以设置多种统计方式的，大家可以根据自己的需要来设置

图片

2、分类统计第一跟第二参数仅仅只能设置一列，还能设置多列数据，如下图，我们就统计了，每种采购方式下采购商品的总数与金额公式：=PIVOTBY(A1:B10,,D1:E10,SUM)第一参数：A1:B10列字段区域第二参数：忽略第三参数：统计区域第四参数：统计方式，sum求和PIVOTBY进行分类统计，一般要求数据区域是连续的，如果你的数据区域不是联系的可以考虑使用HSTACK来构建连续的区域，将其放入对应的参数即可

图片

3、结果是否包含表头设置是否需要包含表头，主要是设置第五参数，参数一共有4个参数为0：所选区域不包含表头，如果所选区域存在表头，表头会参与计算，如下图右上角演示参数为1：所选区域包含表头，但是不显示，如下入坐上第一个参数为2：所选区域不包含表头，但是自动生成表头，一般都是值1、值2、行字段1、列字段1这样的表头，效果如下图右下键中间位置参数为3：所选区域包含表头，并显示表头，效果如下图左下角

图片

4、行标题是否需要包含总计第6参数，主要是用来设计当前的结果是否包含小计与总计的参数为0：不需要汇总行参数为1：在下方显示总计参数为2：在下方显示总计与小计参数为-1：在上方显示总计参数为-2：在上方显示总计与小计具体效果大家可以参考下图，我们需要注意的是，如果你想要显示小计列字段至少需要选择2列，才能显示小计，列字段仅仅选择1列，是不会显示小计的，会显示为错误值。

图片

5、列区域的排序方式第7参数是用来设置列字段的排序方式的。我们需要输入其对应的列数来进行排序，正数表示升序，负数表示降序公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-1)在这里参数为-1，就是表结果会根据【采购方式】这一列进行降序【排序】，第二列【商品名称】默认升序排序公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-2)在这里我们参数为-2就表示根据第二列【商品名称】来进行【降序排序】，第一列【采购方式】默认升序排序，公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,{-1,2})也可以先对第一列排序，然后在对第二列排序，在这里就需要构建一个数组，具体效果如下图所示第8与第9参数的使用方法是一模一样的，只不过是针对的列方向，我们就不再演示了，大家可以试着做一下。

图片

6、筛选第10个参数是用来进行数据筛选的。如下图，我们想要筛选【采购方式】是APP的数据公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-1,,,A1:A10="APP")

图片

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。