EXCEL高级函数应用-GROUPBY 函数

office

**Excel GROUPBY 函数：数据分组聚合的 “高效引擎”，统计分析一步到位！

在 Excel 数据统计中，“按指定维度分组并计算聚合值” 是高频需求 —— 比如按部门统计员工平均薪资、按产品类别汇总销售额、按日期 + 区域双维度计算订单量。过去要么用数据透视表（静态统计，需手动调整字段），要么用 SUMIFS/COUNTIFS 嵌套（多维度统计公式冗长，易出错），而GROUPBY 函数（Excel 365 2024 及以后版本新增）能像 “高效引擎” 一样，按指定分组列和聚合函数，一键生成动态分组统计结果，支持多列分组、多指标聚合，还能与动态数组联动，彻底解决传统分组统计 “静态、繁琐、不灵活” 的问题。今天就带大家从基础到进阶，全面掌握这个实用函数！

一、吃透基础：GROUPBY 函数的语法与参数

GROUPBY 函数的核心是 “对目标数据区域按指定列分组，对数值列执行聚合计算，返回动态分组统计数组”，语法包含多个参数，但核心围绕 “分组列” 和 “聚合规则”，理解 “分组维度” 和 “聚合函数应用” 是关键。

1. 基本语法

GROUPBY(group\_by\_column, aggregate\_column, aggregate\_function, \[ignore\_empty], \[sort], \[by\_col])

前 3 个参数（group_by_column、aggregate_column、aggregate_function）为必选项，后 3 个为可选参数（默认忽略空值、按分组列升序排序、按列分组）；
返回结果为 “二维统计数组”：首列为分组列的唯一值，后续列为对应聚合结果，与原数据动态联动。

2. 参数详细说明

结合 “员工薪资统计” 场景（按部门分组，计算薪资的平均值和总和），参数含义拆解如下，重点标注 “分组逻辑” 和 “聚合规则”，避免统计偏差：

参数名称	作用解释	通俗举例（员工薪资统计场景）	关键注意事项
group_by_column	用于 “分组的列”（单元格区域或动态数组，支持文本、数值、日期类型，需与聚合列行数一致）	按部门分组：`group_by_column=B2:B10`（员工部门列）	1. 分组列可包含重复值，函数会自动提取唯一值作为分组项；2. 支持多列分组（用数组形式，如`{B2:B10,C2:C10}`，按部门 + 工龄双维度分组）
aggregate_column	用于 “聚合计算的数值列”（单元格区域或动态数组，仅支持数值类型，与分组列行数一致）	聚合薪资列：`aggregate_column=D2:D10`（员工薪资列）	1. 若需多指标聚合（如同时计算总和、平均值），用数组形式指定多列（如`{D2:D10,D2:D10}`）；2. 非数值列无法聚合，会返回 #VALUE! 错误
aggregate_function	聚合计算的 “函数”（文本形式或函数对象，支持 SUM、AVERAGE、COUNT、MAX、MIN 等）	计算平均值：`aggregate_function="AVERAGE"`；计算总和：`aggregate_function="SUM"`	1. 多指标聚合时，函数需与聚合列一一对应（如`{D2:D10,D2:D10}`对应`{"SUM","AVERAGE"}`）；2. 支持自定义 LAMBDA 聚合函数（如`LAMBDA(x, SUM(x*0.8))`，计算 8 折后总和）
[ignore_empty]	可选，控制 “是否忽略分组列的空值”（TRUE = 忽略，FALSE = 保留，默认 = TRUE）	忽略空部门：`ignore_empty=TRUE`；保留空部门：`ignore_empty=FALSE`	空值会被视为独立分组项（设为 FALSE 时），需根据业务需求选择，避免统计冗余
[sort]	可选，控制 “分组结果的排序方式”（TRUE = 按分组列升序，FALSE = 不排序，默认 = TRUE）	不排序：`sort=FALSE`；按部门降序（需结合 by_col）：`sort=-1`	1. 仅支持升序（TRUE/1）或不排序（FALSE/0），降序需通过后续 SORT 函数处理；2. 多列分组时，按分组列顺序依次排序
[by_col]	可选，控制 “分组方向”（TRUE = 按列分组，FALSE = 按行分组，默认 = FALSE）	按列分组（适用于横向数据）：`by_col=TRUE`；默认按行分组（纵向数据）	常规纵向数据（如员工表）用 FALSE，横向数据（如月度销量表）用 TRUE，避免分组方向错误

关键提醒：

与数据透视表的区别：GROUPBY 返回动态数组（随原数据实时更新），数据透视表是静态报表（需手动刷新）；GROUPBY 支持自定义聚合函数，数据透视表仅支持预设聚合方式；
与 SUMIFS 的区别：SUMIFS 仅支持单指标单维度统计，GROUPBY 支持多维度多指标统计，公式更简洁（如双维度统计用 GROUPBY 仅需 1 行公式，SUMIFS 需嵌套多次）。

二、核心逻辑：GROUPBY 函数的 3 个关键特性

使用 GROUPBY 前，必须先掌握它的核心逻辑，这是避免出现 “分组偏差”“聚合错误” 的基础，尤其是以下 3 个特性，是新手最易混淆的点：

特性 1：自动提取分组列唯一值

GROUPBY 会自动从group_by_column中提取唯一值作为分组项，无需手动去重（如部门列含 “销售部”“技术部”“销售部”，会自动生成 “销售部”“技术部” 两个分组）；

示例：GROUPBY(B2:B10, D2:D10, "SUM")→部门列唯一值为 “销售部”“技术部”，返回两组统计结果；
优势：替代 “先去重再统计” 的两步操作，直接生成唯一分组，减少手动干预。

特性 2：多维度与多指标聚合的数组适配

多维度分组：用数组形式指定多个分组列（如{B2:B10,C2:C10}，部门 + 工龄），函数会按 “先第一列、再第二列” 的顺序生成组合分组（如 “销售部 - 3 年”“销售部 - 5 年”“技术部 - 2 年”）；
多指标聚合：用数组形式指定多个聚合列和对应函数（如{D2:D10,D2:D10}对应{"SUM","AVERAGE"}），返回结果包含 “分组列 + 多个聚合列”（如部门、薪资总和、薪资平均值）；
优势：无需编写多个单维度公式，一行代码完成复杂的多维度多指标统计，逻辑更清晰。

特性 3：动态联动原数据

若group_by_column或aggregate_column是动态数组（如 FILTER 筛选结果、MAKEARRAY 生成的数组），GROUPBY 会实时响应原数据变化，自动更新分组统计结果；

示例：GROUPBY(FILTER(B2:B10,C2:C10>3), FILTER(D2:D10,C2:C10>3), "SUM")→筛选工龄 > 3 年的员工后分组统计，筛选结果变化时，统计结果同步更新；
优势：替代静态数据透视表，实现 “数据筛选 + 分组统计” 的动态一体化，适合制作实时更新的业务报表。

三、实战场景：GROUPBY 函数的 6 大核心应用

GROUPBY 的价值在于 “高效实现多维度多指标的动态分组统计”，下面结合 6 个高频办公场景，带大家掌握从基础到进阶的用法，每个示例均包含 “需求 + 公式 + 解析 + 对比传统操作”，突出效率优势。

示例 1：基础应用 —— 单维度单指标分组（按部门统计薪资总和）

需求：在 “员工表” B2:D10 区域（部门、工龄、薪资）中，按部门（B 列）分组，统计每个部门的薪资总和，用于部门人力成本分析。

传统操作（无 GROUPBY）：

手动复制 B 列部门数据，用 “删除重复项” 功能获取唯一部门列表；
在唯一部门旁输入=SUMIFS(D:D,B:B,唯一部门)，逐部门计算总和，需重复写公式，效率低。

GROUPBY 公式（一键统计）：

\=GROUPBY(B2:B10, D2:D10, "SUM")

解析：

group_by_column=B2:B10：按部门分组；aggregate_column=D2:D10：聚合薪资列；aggregate_function="SUM"：计算总和；
结果：返回 2 列数组，第 1 列为唯一部门（如 “销售部”“技术部”），第 2 列为对应部门的薪资总和；
优势：无需去重和重复写公式，一步生成统计结果，原数据修改时（如新增员工），总和自动更新。

示例 2：进阶应用 —— 单维度多指标分组（按产品类别统计销量）

需求：在 “产品销售表” B2:D100 区域（产品类别、销量、单价）中，按产品类别（B 列）分组，同时统计销量总和、平均单价、最大销量，用于产品业绩分析。

传统操作（无 GROUPBY）：

去重获取唯一产品类别列表；
分别输入=SUMIFS(C:C,B:B,类别)（总和）、=AVERAGEIFS(D:D,B:B,类别)（平均单价）、=MAXIFS(C:C,B:B,类别)（最大销量），需 3 列公式，维护成本高。

GROUPBY 公式（多指标统计）：

\=GROUPBY(B2:B100, {C2:C100, D2:D100, C2:C100}, {"SUM", "AVERAGE", "MAX"})

解析：

聚合列数组{C2:C100, D2:D100, C2:C100}：分别对应销量、单价、销量；
函数数组{"SUM", "AVERAGE", "MAX"}：分别计算销量总和、单价平均值、销量最大值；
结果：返回 4 列数组，第 1 列为产品类别，第 2-4 列分别为三个聚合指标，逻辑清晰；
优势：一行公式替代 3 列独立公式，新增指标时仅需扩展数组（如加 “MIN” 函数统计最小销量），灵活性高。

示例 3：多维度分组（按部门 + 工龄统计员工数量）

需求：在 “员工表” B2:C10 区域（部门、工龄）中，按 “部门 + 工龄” 双维度分组，统计每个组合的员工数量（如 “销售部 - 3 年” 有 2 人），用于部门人员结构分析。

传统操作（无 GROUPBY）：

用 “数据透视表” 将 “部门” 拖入行字段，“工龄” 拖入行字段，“员工 ID” 拖入值字段（计数）；
数据更新时需手动点击 “刷新”，且无法与动态数组联动。

GROUPBY 公式（多维度统计）：

\=GROUPBY({B2:B10, C2:C10}, B2:B10, "COUNT")

解析：

分组列数组{B2:B10, C2:C10}：按部门 + 工龄双维度分组；
聚合列用B2:B10（非数值列需用 COUNT 函数，仅统计非空单元格数量），aggregate_function="COUNT"：计算员工数量；
结果：返回 3 列数组，第 1-2 列为 “部门 + 工龄” 组合，第 3 列为对应组合的员工数量；
优势：动态响应数据变化，无需手动刷新，双维度组合自动生成，比数据透视表更灵活。

示例 4：自定义聚合函数（按区域统计折扣后销售额）

需求：在 “销售表” B2:D10 区域（区域、销量、单价）中，按区域（B 列）分组，统计每个区域的 “折扣后销售额”（销量 × 单价 ×0.9，统一 9 折），用于实际营收分析。

传统操作（无 GROUPBY）：

在 E 列新增 “折扣后销售额” 辅助列，输入=C2*D2*0.9，下拉填充；
用 SUMIFS 按区域统计 E 列总和，需额外添加辅助列，破坏原表结构。

GROUPBY 公式（自定义聚合）：

\=GROUPBY(B2:B10, C2:D10, LAMBDA(x, SUM(INDEX(x,,1)\*INDEX(x,,2)\*0.9)))

解析：

聚合列C2:D10：包含销量（第 1 列）和单价（第 2 列）；
自定义 LAMBDA 函数：INDEX(x,,1)提取销量，INDEX(x,,2)提取单价，计算销量×单价×0.9的总和；
结果：返回 2 列数组，第 1 列为区域，第 2 列为折扣后销售额总和；
优势：无需添加辅助列，直接在聚合函数中实现自定义计算，保持原表结构整洁。

示例 5：动态筛选 + 分组（按区域统计高销量产品销售额）

需求：在 “销售表” B2:D100 区域（区域、产品、销售额）中，先筛选销售额 > 5000 的记录，再按区域（B 列）分组统计筛选后的销售额总和，用于高价值订单分析。

传统操作（无 GROUPBY）：

用 “数据→筛选” 手动筛选销售额 > 5000 的记录；
复制筛选后的区域，用数据透视表按区域统计总和，筛选条件变化时需重新复制和统计。

GROUPBY+FILTER 公式（动态统计）：

\=GROUPBY(   FILTER(B2:B100, D2:D100>5000), // 筛选销售额>5000的区域   FILTER(D2:D100, D2:D100>5000), // 筛选销售额>5000的金额   "SUM" )

解析：

用 FILTER 先筛选出符合条件的区域和销售额，再用 GROUPBY 分组统计；
结果：仅显示销售额 > 5000 的区域及其总和，筛选条件修改时（如改为 > 8000），统计结果同步更新；
优势：实现 “筛选 + 分组” 一体化，无需手动复制数据，动态性远超传统方法。

示例 6：处理空值与排序（按类别统计库存，保留空类别）

需求：在 “产品表” B2:C10 区域（产品类别、库存）中，按产品类别分组统计库存总和，保留空类别（视为 “未分类”），并按库存总和降序排序，用于库存清理优先级分析。

传统操作（无 GROUPBY）：

用 SUMIFS 统计各类别库存，空类别需单独用=SUMIFS(C:C,B:B,"")计算；
手动复制统计结果，用 “数据→排序” 按库存降序，步骤繁琐且无法动态更新。

GROUPBY+SORT 公式（空值处理 + 排序）：

\=SORT(   GROUPBY(B2:B10, C2:C10, "SUM", FALSE), // ignore\_empty=FALSE保留空类别   2, -1 // 按第2列（库存总和）降序排序 )

解析：

ignore_empty=FALSE：保留空类别，统计未分类产品的库存总和；
外层 SORT 函数：按 GROUPBY 结果的第 2 列（库存总和）降序排序，空类别按实际库存参与排序；
结果：返回 2 列数组，第 1 列为包含空类别的产品类别，第 2 列为库存总和，按总和从高到低排列；
优势：一步完成 “空值保留 + 分组统计 + 排序”，传统操作需 3 步以上，效率提升 70%。

四、总结：GROUPBY 函数的核心优势与注意事项

1. 核心优势（对比传统分组统计工具）

对比维度	GROUPBY 函数	传统 “数据透视表”/SUMIFS 嵌套
动态性	与原数据实时联动，新增 / 修改数据时统计结果自动更新	数据透视表需手动点击 “刷新”；SUMIFS 需重新计算（部分场景需下拉填充），动态性差
灵活性	支持多维度分组、多指标聚合、自定义 LAMBDA 函数，场景全覆盖	数据透视表多维度分组需手动拖放字段；SUMIFS 多维度需嵌套多层，自定义计算需加辅助列
简洁性	一行公式完成复杂统计（如双维度三指标），逻辑集中	数据透视表需多步设置字段；SUMIFS 多指标需多列独立公式，逻辑分散，维护成本高
兼容性	可与 FILTER、SORT、LET 等动态函数嵌套，实现 “筛选 + 分组 + 排序” 一体化	数据透视表与动态数组联动性差；SUMIFS 嵌套复杂函数时易出错，可读性低

2. 必记注意事项

版本兼容性：仅支持 Excel 365 2024 及以后版本（部分预览版可能提前支持），旧版本（如 2021、2019）无此函数，会返回 #NAME? 错误。若需在旧版本实现类似功能，多维度统计需用 “数据透视表”，自定义计算需用 “辅助列 + SUMIFS” 组合；
分组列与聚合列行数一致性：group_by_column与aggregate_column的行数必须完全相同（如均为 10 行），否则返回 #VALUE! 错误。若为动态数组（如 FILTER 结果），需确保两者筛选条件一致，避免行数偏差；
聚合函数适用范围：
支持的预设函数：SUM（求和）、AVERAGE（平均值）、COUNT（计数，非空单元格）、COUNTA（计数，非空值）、MAX（最大值）、MIN（最小值）、STDEV.S（样本标准差）、STDEV.P（总体标准差）等；
自定义 LAMBDA 函数限制：仅支持单参数输入（参数x代表分组后的聚合列数据），且需返回单一数值（如LAMBDA(x, SUM(x*0.9))合法，LAMBDA(x, x*0.9)不合法，因返回数组）；
空值处理逻辑：ignore_empty=TRUE（默认）会忽略分组列中的空值，不生成对应分组；ignore_empty=FALSE会将空值视为独立分组项（分组名为空文本），需根据业务需求选择（如 “未分类” 数据需保留时设为 FALSE）；
排序与后续处理：GROUPBY 默认按分组列升序排序，若需按聚合结果排序（如按销售额总和降序），需嵌套 SORT 函数（如示例 6），且排序参数需对应聚合列的位置（如第 2 列聚合结果用SORT(...,2,-1)）；
性能优化：处理超大数据量（如 10 万行以上）时，建议：① 先用 FILTER 筛选出核心数据，再用 GROUPBY 统计（减少数据量）；② 避免在自定义 LAMBDA 函数中嵌套复杂计算（如多层 IF），防止 Excel 卡顿。

GROUPBY 函数作为 Excel 分组统计的 “革新工具”，彻底突破了传统方法 “静态、繁琐、自定义难” 的局限，尤其适合动态报表制作、多维度业务分析、自定义指标统计等场景。掌握它后，你可以用一行公式替代多步操作或冗长嵌套，让数据统计效率提升数倍。建议从基础的 “单维度单指标统计”（示例 1）开始练习，逐步尝试多维度、自定义聚合等进阶用法，结合实际业务中的分析需求灵活调整参数，真正让这个函数成为提升办公效率的 “核心利器”！