分类汇总和 subtotal：分层看数据

[TOC] 一张两千行的销售流水，领导只想看每个部门卖了多少钱。直接拉公式一个个部门去算，既慢又容易漏。这种情况有两个现成工具：菜单里的分类汇总，和函数里的 SUBTOTAL。前者三秒钟把扁平表格叠成分层报表，后者让筛选状态下的求和永远算对。本篇把两者讲透，并说清它们和透视表各自的最佳战场。

先排序，后汇总：一条铁律

用菜单分类汇总之前，必须先按分类字段排序。这是死规矩，没有例外。

原因很简单：分类汇总的工作方式是逐行扫描，遇到和上一行不同的值就插一个汇总行。数据没排序时，销售部的记录散落在第十行、第三百行、第一千二百行，Excel 会给每一次出现都插一个小计，一张两千行的表能给你插出几十个销售部分计，全废了。

所以标准流程是三步：先按分类字段排序（部门、地区、品类都行），再点分类汇总，最后看分级视图。排序用升序降序都可以，关键是有序。这一步如果偷懒，后面全是返工。

排序之后，路径是：数据选项卡、分类汇总。对话框里三个关键设置：分类字段选排过序的那一列，汇总方式选求和（也可以选计数、平均值、最大值、最小值），选定汇总项勾上要算的列（比如销售额）。勾选“替换当前分类汇总”会清掉之前建过的汇总层，想做多层嵌套汇总时记得去掉这个勾。

汇总方式的选择有讲究，别逢求和就选。统计每个部门有多少笔单子，用计数；比较各部门平均客单价，用平均值；找各品类的最高单笔金额，用最大值。最容易被忽略的是计数：它经常不需要你选数值列，选文本列照样计数，做签到、做回访记录的人数统计时这招最省事。三个设置填完看一眼预览区域的列名，确认汇总的是你要的那一列，再点确定。

三步出报表，顺便看懂分级视图

设置完点确定，表格左侧多出两列竖条，顶端有 1、2、3 三个数字按钮——这就是分级视图。

点 2 是最常用的状态：每个部门一行小计，最底下一行总计，一张两千行的流水瞬间变成五行报表，清爽得像重新做了一张表。每行汇总左边有个减号，点一下把该组明细折叠起来，减号变加号，再点展开。逐组核对数据时，这个折叠开关比筛选还方便。

汇总行插在每组数据的下方，这是默认设置。对话框里还有两项可选：汇总结果显示在数据下方（默认勾上），以及“每组数据分页”，后者适合把每个部门的明细分页打印成单独一页，做部门业绩手册时很有用。

需要特别提醒：分类汇总会改动表格结构（插入新行），它不是纯粹的显示效果。所以汇总之后再也不原样了，要恢复明细表，点数据选项卡的“取消组合”旁边的“清除分类汇总”，所有汇总行被删除，表恢复原状。这个操作不可撤销关闭文件，做之前留个备份更稳妥。

还有一点容易被忽略：汇总行里的数字本身就是普通公式（SUBTOTAL 开头的），它不是固定值。明细数据改了，小计会自动更新；但如果你在汇总行里手工填了别的字进去，那一格就不再是计算值了，后续更新会和你填的字打架。原则上汇总行只看不动，要写备注就在旁边的空列写，别写进小计格。

打印分类汇总的结果也有讲究。如果直接打印带明细的三级状态，几十页打不完。先在分级视图里调到 2 或 1，只打印小计和总计；或者勾选“每组数据分页”，每个部门的明细自动独占一页。打印设置的细节在模板和打印篇里有完整说明。

SUBTOTAL 函数：筛选状态下求和的正确姿势

菜单分类汇总适合一次性看结果，但工作表里更常遇到的是动态场景：表还是那张扁平表，只是加了筛选，现在要在底部显示筛选结果的合计。用 SUM 行不行？不行。SUM 把隐藏行一起算进去，筛选选出来的明明是三行，它按三百行算。

这时候要请出 SUBTOTAL。经典用法：

=SUBTOTAL(9,B2:B1000)

第一参数 9 表示求和，第二参数是要汇总的区域。它只对当前可见的行做计算，加了筛选之后，公式自动只算筛出来的那几行，状态栏的计数和公式结果永远一致。

SUBTOTAL 的第一参数有 11 种，常用的记住五个就够了：

9 和 109 的区别老有人问，其实一句话：9 忽略被筛选隐藏的行，109 额外忽略被右键手动隐藏的行。日常用筛选配合 9 就够了；如果你习惯手动藏行整理版面，用 109 更保险。做报表汇总行时，建议直接 109，因为两种隐藏情况它都防得住。

再补一个组合用法：SUBTOTAL 加条件格式做动态高亮。底部合计行用 SUBTOTAL 算筛选后的总数，再用条件格式给合计行套一个规则：当总数大于某个阈值时整行变色。这样每次换筛选条件，合计行自己变颜色提醒你当前口径下的总量，做监控类报表非常实用。同理，SUBTOTAL 还能和 IFERROR、ROUND 这些函数组合，比如把求和结果除以人数再四舍五入，做出筛选状态下的动态人均值。

SUBTOTAL 还有个隐藏特性：它自动忽略区域里其他 SUBTOTAL 的结果，不会重复加总。所以用 SUBTOTAL 做多层小计时不用担心嵌套算重，这比 SUM 嵌套省心得多。

嵌套汇总：一层不够就叠两层

需求常常不止一个维度。比如先按部门汇总销售额，再在部门内按季度做二层汇总。菜单分类汇全是支持的，关键是顺序。

操作：先做第一层。按部门、季度做两级排序（部门为主、季度为次），然后分类汇总，分类字段选部门，确定。再做第二层：再次打开分类汇总，分类字段换成季度，把“替换当前分类汇总”的勾去掉，确定。于是每个部门有小计，部门内每个季度还有小计，层级清晰。

注意两层汇总的字段顺序必须和排序顺序一致：先排的做第一层，后排的做第二层。如果排序是部门、季度，汇总却先按季度再按部门，层级就会乱套——季度汇总行插在半路上，把部门的记录切开。

做嵌套汇总时还有个常见意外：汇总结果看着不对，多半是某层汇总的列勾错了。分类汇总对话框里“选定汇总项”可以同时勾多列，比如同时汇总销售额和成本，确定后一次出两个小计列。很方便，但勾错列也会一次错两列，确定前看清勾的是哪几列。

汇总结果怎么复制走

这是分类汇总最大的坑：想把分级视图的各部门小计贴到别的表里，直接复制粘贴会把明细也一起带走，或者粘过来一堆折叠行的空白。

正确流程：先在分级视图里点 2（只显示各层汇总），然后选中需要的区域，按 Alt+; 选中可见单元格（这一步绝不能省），再 Ctrl+C 复制，到目标表 Ctrl+V。粘过来的就只有汇总行，干干净净。

如果粘过去发现还是带明细，多半是 Alt+; 没按成功（需要先选中区域再按，按完没有任何提示，但状态栏会有“选中可见单元格”字样一闪）。另一个稳妥办法：点开始选项卡的查找和选择、定位条件、可见单元格，效果一样，适合记不住快捷键的时候。

汇总结果拿去做图也常见。用小计行做柱状图时，注意系列引用的是带绝对引用的区域，源表一旦清除分类汇总，图立刻空掉。做交付物之前想清楚：这页是要动态更新的分析模板，还是一次性报告。一次性报告就把汇总结果固化成数值，省得源表一动全套完蛋。

什么时候用它，什么时候用透视表

同样一张流水表，分类汇总和透视表都能出部门小计，选哪个？看两点。

只想快速看一眼分组结果、或者需要把分页明细打印给各部门，分类汇总更快。要是这份表你要反复按不同角度切（这个月看部门、下个月看品类、再看地区），别犹豫，上数据透视表，拖拽换维度的效率不是分类汇总能比的。筛选配合 SUBTOTAL 则是介于两者之间的轻量方案：表保持原样，公式自动跟随筛选，适合在表格底部放一个动态合计行。

两个实战案例连起来看

光讲操作容易忘，把两个真实场景完整走一遍。

场景一，月度费用分析。财务给的明细表有四千行：日期、部门、费用科目、金额。目标是出一张每个部门各科目小计加合计的汇总页。第一步按部门、科目两级排序，部门为主关键字。第二步分类汇总：分类字段部门、求和、金额，确定，得到部门层。第三步再次分类汇总：分类字段科目、“替换当前分类汇总”勾去掉，确定。第四步点分级按钮 2，只看各层小计。第五步 Alt+; 复制可见行，贴到新的汇总页，配上条件格式把超预算的部门标红，一张月度费用简报成型，全程没写一个 SUMIF。

场景二，活动现场签到统计。签到表只有两列：姓名、场次。想看每场来了多少人。用 COUNTIF 当然可以，但更快的方法是按场次排序，然后分类汇总：分类字段场次、汇总方式计数、选定汇总项场次（计数选哪一列都行，关键是选非空的那列）。确定后每场一个小计行，总人数一行总计。这种计数型汇总做考勤、做问卷统计特别顺手，比写 COUNTIF 系列函数直观得多。

两个案例的共同套路值得记住：排序、汇总、折叠、复制。任何分组统计需求套这四步都能落地，临时来一个分析需求时按这个流程走，比临时抱佛脚查函数快十倍。

把汇总结果固化下来

分类汇总是个易碎品。它在原表里插入行，任何一次排序、任何一次筛选都可能让汇总行错位。所以做完汇总之后，最好养成固化的习惯。

固化分两步。第一步，把分级视图调到要交付的层级（通常点 2），Alt+; 选中可见单元格，复制。第二步，新建一张工作表，选择性粘贴、只粘数值。粘过来的就是纯数据，没有汇总行结构，也没有公式，发给谁都不会乱。

固化之后，原明细表该怎么维护还怎么维护，下次要新汇总重做一遍即是。如果你发现自己每周都在重做同样的汇总，说明该升级到透视表了——透视表一刷新就出最新结果，重复劳动归零。这也是为什么越成熟的团队越少用菜单分类汇总，它好用在临时和一次性，不在日常。

常见问题

分类汇总做出来有几十个小计，全是重复的？

百分百是没排序就汇总了。先撤销（Ctrl+Z 原地撤销），按分类字段排一次序，重新做。养成铁律：分类汇总按钮的左手边永远是排序按钮。如果排过序还是乱，检查排序是否只排了一列（其他列没跟着动），用排序和筛选篇里说的扩展选定区域重排一次。

SUBTOTAL 和 SUM 套在一个区域里，结果不一样？

对，这就是它们存在的意义。SUM 谁都拦不住，隐藏行照算；SUBTOTAL 只认可见行。两者结果相等说明你没筛选也没隐藏行。记住底部合计行永远用 SUBTOTAL，别图省事用 SUM，否则一筛选合计就错，领导和客户第一眼就看那个数。

怎么把分类汇总彻底删掉恢复明细表？

数据选项卡、分类汇总、对话框左下角有个“全部删除”按钮，点它，所有汇总行删掉，表恢复原样。删之前最好另存一份备份，因为汇总行里如果有人手工填过别的数据，删除时也会一起没了。

分级视图的加减号突然不见了？

两种情况。一是窗口缩放比例太小，左边的分级栏被压没了，往右拉一下分割条或者放大显示比例就回来。二是你切换了工作表又回来，分级显示偶尔没刷新，按 Ctrl+Z 一步再重做就有了。如果整张表连分级栏都没有，说明根本没有分类汇总或组合，自然没有加减号可看。

相关篇章

分类汇总解决了静态分组，动态分析见数据透视表：拖拽出统计报表；想让汇总出来的异常自动变色，翻条件格式：让异常自己跳出来；排序基础不牢回头看排序和筛选：多级排序不混乱，交付打印的版面则交给模板和打印篇。