Excel 小计、总计公式全都能自动计算新增行, 套路公式存好

今天的教程不算新知识点，只是希望提醒大家记得：学了那么多知识点一定要融会贯通，每一个细节都要做到快速、简洁、自动化。

案例：

给下图 1 快速添加“小计”和“总计”公式，当增加新的月份数据后，求和公式会自动加入新的区域。

效果如下图 2 所示。

解决方案：

即便用最普通的求和公式，也是有窍门的。

1. 选中 C 列中包含第一个和最后一个“小计”单元格的区域 --> 按 Ctrl+G

2. 在弹出的对话框中点击“定位条件”按钮。

3. 在弹出的对话框中选择“空值”--> 点击“确定”

此时就选中了区域中的所有空单元格。

4. 按 Alt+=，就批量填充了所有“小计”行的公式。

5. 在 C17 单元格中输入以下公式：

=SUMPRODUCT((A2:A16=\"小计\")*C2:C16)

公式释义：

A2:A16=\"小计\"：判断区域中的单元格的值是否为“小计”，生成 true 或 false 组成的数组；

...*C2:C16：将上述数组与该区域的数值相乘，只保留上述为 true 的行所对应的值，其余皆为 0；

SUMPRODUCT(...)：用该函数对数组求和，即可计算出所有“小计”行的和

虽然公式的设置过程已经很简化了，但是现在的公式是“死”的，如果中间插入新的行，求和公式的参数区域并不会自动增加，那就需要每次再手动调整区域，非常不智能。

要解决这个痛点也非常简单，只要稍微升级一下公式即可。

6. 将“小计”公式的参数的截止单元格修改为 INDEX(C:C,ROW-1)，修改后公式如下：

=SUM(C2:INDEX(C:C,ROW-1))

公式释义：

INDEX(C:C,ROW-1)：

index 函数的作用是返回表格或区域中的值或值的引用；

row-1：row 返回单元格所在的行号，-1 即可得到当前单元格上方的行号；

这段公式表示从 C 列中提取当前单元格上方一个单元格的值；

SUM(C2:...)：对 C2 至上述单元格的区域求和

7. “总计”的公式也按同样原理修改：所有引用区域的截止单元格都替换成 index 函数。

=SUMPRODUCT((A2:INDEX(A:A,ROW-1)=\"小计\")*C2:INDEX(C:C,ROW-1))

在任意位置插入新的行，且输入设置，公式结果都会自动更新。