如何在 Excel 中添加分类汇总：公式、数据透视表与 Python 方案

面对动辄数千行的明细数据，缺乏结构化汇总的 Excel 表格往往可读性差、分析效率低。在 Excel 的数据整理工具中，分类汇总（Subtotal）是提升数据结构化程度最直接、高效的功能之一。

无论是财务人员核对账目、销售岗分析区域业绩，还是运营人员追踪活动指标，掌握在 Excel 中添加分类汇总的方法，都能大幅减少手动统计的工作量，提升数据分析效率。本文将系统介绍 4 种实现 Excel 分类汇总的方案，覆盖从快速手动操作到自动化批量处理的不同场景。

什么是 Excel 分类汇总？

分类汇总是指在数据表格中，按指定列的分组规则插入汇总行，对每组数据执行求和、计数、平均值等统计计算的功能。核心作用包括：

  • 将零散的明细数据按逻辑分组，提升表格可读性
  • 快速查看分组维度下的求和、平均值、计数等统计结果
  • 通过分级折叠 / 展开功能，灵活切换汇总视图与明细视图
  • 在同一张工作表中同时保留原始明细与分层汇总结果

方法一：使用 Excel 内置分类汇总工具

内置分类汇总工具是最快捷的手动方案，适合一次性的快速统计场景。它会自动插入汇总行、生成分级视图，支持一键折叠展开不同层级的数据。

前置操作：对分组列进行排序

分类汇总的核心逻辑是 “按列值变化分组”，因此必须先对用于分组的列进行排序：

  • 选中需要作为分组依据的列（如 “区域” 列）
  • 切换到数据选项卡，点击升序或降序按钮完成排序

Excel内置数据排序功能

添加分类汇总步骤

  • 选中数据区域内的任意单元格
  • 再次切换到数据选项卡，点击分级显示组中的分类汇总按钮
  • 弹出的分类汇总对话框中配置核心参数：
    • 分类字段：选择你已完成排序的列（例如”销售区域”）
    • 汇总方式：选择统计逻辑，支持求和、计数、平均值、最大值、最小值等
    • 选定汇总项：勾选需要执行统计的数值列

带选项设置的Excel分类汇总对话框

点击确定后，Excel 会自动在每个分组末尾插入汇总行，并在数据集底部添加总计行。

带有分类汇总行和分级显示按钮的Excel工作表

实用技巧：分级视图切换

表格左侧会出现分级控制按钮（1、2、3），可快速切换数据视图：

  • 点击 1：仅显示总计结果
  • 点击 2：显示所有分组汇总行 + 总计行
  • 点击 3：显示全部明细数据 + 所有汇总行
  • 点击 +/- 按钮：单独展开或折叠指定分组

⚠️ 注意：“数据” 选项卡的分类汇总按钮不支持 Excel 表格（超级表）。若数据在表格中，可通过 “表设计 - 汇总行” 使用 SUBTOTAL 函数，或先将表格转换为普通区域再使用该功能。

方法二：使用 SUBTOTAL 函数实现动态汇总

SUBTOTAL 函数是动态公式，会随数据变化、筛选操作实时重算，适合制作可复用的报表模板。

函数语法：

=SUBTOTAL(函数序号, 数据区域1, [数据区域2], ...)

  • 函数序号（必填）：用数字代码指定统计方式，分为两类：
    • 基础代码（包含手动隐藏的行）：1 = 平均值、2 = 计数、4 = 最大值、5 = 最小值、9 = 求和（最常用）
    • 在基础代码基础上加100，即可忽略手动隐藏的行（例如109 = 求和，且排除手动隐藏的行）
  • 数据区域1（必填）：需要进行分类汇总计算的单元格区域
  • [数据区域2]（选填）：如果数据区域不连续，可添加多个额外区域

分类汇总公式示例

如果数据位于 D2:D10 单元格区域：

  • 要对筛选后的列表数值求和，使用 =SUBTOTAL(9, D2:D10)
  • 要统计条目数量，使用 =SUBTOTAL(2, D2:D10)

在Excel中插入求和与计数SUBTOTAL函数

核心优势：为什么不用 SUM？

  1. 自动避免重复计算：SUBTOTAL 会自动忽略范围内的其他 SUBTOTAL 结果，嵌套使用时不会重复统计
  2. 适配筛选操作：开启表格筛选后，仅可见行会被纳入计算，是筛选场景下的首选统计函数

方法三：使用数据透视表实现灵活汇总

数据透视表是 Excel 内置的动态汇总引擎，会基于源数据生成独立的汇总表，支持多维度、多指标的灵活统计，是比传统分类汇总更现代化的方案。

如何在 Excel 中创建数据透视表：

  1. 选中完整的源数据区域
  2. 切换到插入选项卡，点击数据透视表
  3. 在字段面板中，将分类字段（例如”销售区域”）拖到行区域
  4. 将数值字段（例如”销售额”）拖到值区域

默认情况下，数值字段自动执行求和统计，文本字段自动执行计数统计。

Excel中添加数据透视表，自动生成区域分类汇总

自定义分类汇总规则

右键点击行标签，选择字段设置，可在分类汇总栏调整规则：

  • 自动：使用默认汇总方式
  • 自定义：支持同时添加多种统计逻辑（如同一个字段同时显示求和、平均值）
  • 无：完全移除分类汇总，仅保留总计

在数据透视表中自定义分类汇总

✅ 优势： 与传统分类汇总工具相比，数据透视表无需提前对数据排序，源数据更新后可一键刷新，且支持轻松添加多层分组、多维度交叉统计，无需修改公式。

方法四：Python 程序化批量添加分类汇总

对于需要批量处理大量 Excel 文件、嵌入数据自动化流程的场景，可通过 Free Spire.XLS for Python 库实现分类汇总的程序化生成，无需手动打开 Excel 即可批量处理文件。

环境准备

首先确保已安装 Python（3.7及以上版本）。打开终端或命令提示符，运行以下命令：

1
pip install Spire.Xls.Free

在 Excel 中插入分类汇总的 Python 代码

以下代码会加载一个Excel工作簿，为指定数据区域应用分类汇总，并将结果保存为新文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from spire.xls import *
from spire.xls.common import *

# 创建工作簿对象并加载Excel文件
workbook = Workbook()
workbook.LoadFromFile("测试数据.xlsx")
sheet = workbook.Worksheets[0]

# 定义用于分类汇总计算的数据区域
# 范围为B2到E10（仅数据行，不含表头）
data_range = sheet.Range["B2:E10"]

# 调用Subtotal方法并传入完整参数
sheet.Subtotal(data_range, 0, [3], SubtotalTypes.Sum, True, False, True)

# 保存结果文件
workbook.SaveToFile("分类汇总.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

输出效果： 在 B2:E10 区域内，按第一列（”销售区域”）分组，对第四列（”销售额”）进行求和分类汇总。

通过Python生成区域分类汇总的Excel工作表

方法参数详解

Subtotal() 方法支持七个参数，可精准控制汇总效果：

参数 类型 说明
range IXLSRange 待进行分类汇总的源数据单元格区域
groupByIndex int 分组依据列在选中区域内的 0 起始索引
totalFields int[] 需统计的列在所选范围内的 0 起始索引数组
subtotalType SubtotalTypes 统计类型，支持求和、平均值、计数、最大值、最小值等
replace bool 是否替换范围内已有的分类汇总
addPageBreak bool 是否在每个分组后插入分页符（适用于打印场景）
addsummaryBelowData bool 是否将汇总行放置在分组明细的下方

⚠️ 注意： 与 Excel 界面操作一致，调用 Subtotal() 前源数据必须已按分组列完成排序。未排序的数据会在每次数值变化时都生成分类汇总行，产生大量无效的细碎汇总行。

延伸阅读： Python 在 Excel 中创建分组或取消分组

常见问题（FAQ）

Q: 可以为同一列添加多种汇总方式吗？

可以。如需同时显示求和、平均值等多种统计结果：

  • 按常规步骤添加第一种汇总
  • 再次打开分类汇总对话框，选择新的汇总方式
  • 取消勾选 “替换当前分类汇总”
  • 点击确定

在 Python 方案中，两次调用 Subtotal() 方法，第二次将 replace 参数设为 False 即可实现。

Q: Excel 分类汇总有没有快捷键？

没有直接一键生成的快捷键，但可通过快捷键组合 Alt + A + B 快速打开分类汇总对话框。

Q: 如何快速删除所有分类汇总？

如果操作失误或想要恢复原始数据，回到 “数据 > 分类汇总”。在对话框中点击左下角的全部删除按钮，即可立即删除所有分类汇总行与分级显示符号，恢复数据排序后的原始状态。

Q: 如何只复制分类汇总的结果？

点击分级显示按钮2，仅显示汇总结果，按下 Alt + ;（分号）仅选中可见单元格，再复制粘贴到新工作表即可。

总结

Excel 分类汇总是数据整理与分析的核心技能，选择哪种方案取决于具体的使用场景。对于一次性的快速分析，内置分类汇总工具最为适用；SUBTOTAL 函数支持动态的、适配筛选的计算；数据透视表则擅长灵活的多维度报表制作。当你需要自动化重复任务、批量处理文件时，使用 Free Spire.XLS for Python 免费库可大幅提升效率。熟练掌握这四种方法，可覆盖绝大多数 Excel 数据汇总场景，显著提升数据处理效率。

相关阅读