许多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

目前终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。

专栏

Excel从入门到精通

作者：Excel学习世界

99币

84人已购

查看

在实际工作中，求和区域常常需要新增，这很常见，列如每个月增加一条记录。

对于这种情况，就应该将求和公式的区域写成灵活的，无论增加多少行，都能自动纳入计算区域，这样就不必每次手工调整计算区域，万一忘记就出错。

自动调整区域的求和公式之前我教过，今天的案例难度升级了，在此基础上还要思考带条件求和。

案例：

计算下图 1 中各部门每月的业绩小计以及公司总业绩，要求如下：

• 由于人员常常有增减，为了方便和避免出错，希望小计和总计的计算区域能自动变化，特别是新增行的时候，能自动将它算进去；
• C 列中状态不为空的人的业绩不要计算在内。

效果如下图 2 所示。

解决方案：

1. 在 D7 单元格中输入以下公式 --> 向右拖动，复制公式：

=SUMIF($C2:INDEX($C:$C,ROW()-1),"",D2:INDEX(D:D,ROW()-1))

公式释义：

• INDEX(D:D,ROW()-1)：ROW() 是当前行所在的行号，ROW()-1 就是当前单元格上一行的行号；这个公式表明在 D 列中，定位到当前单元格上一行的单元格；
• D2:...：
• D2 单元格是“销售一部”的第一个人的 1 月业绩所在的单元格；如果没有其他符加条件，只需加个 sum，就是将本部门的第一个人至最后一个人的业绩相加；
• 到这里，已经解决了第一个需求，就是始终计算该部门的所有人，即便将来新增了人，也是如此，由于末尾那个人是活动的，永远定位到公式的上一行；这个套路公式需要记住；
• SUMIF($C2:INDEX($C:$C,ROW()-1),""...)：这里用了 sumif 来判断 C 列对应区域的单元格是否为空，如果为空，则对 D 列的区域求和，不为空的不求和；这样就满足了需求中的第二个条件。

* 请注意公式中的绝对和相对引用。

2. 向右拖动，复制公式。

3. 将公式复制到其他部门的“小计”区域，记得修改两个 index 函数前面的起始单元格，须改为当前要计算区域的第一个单元格。

接下来计算总计公式。

4. 在 D20 单元格中输入以下公式 --> 向右拖动，复制公式：

=SUMPRODUCT(($A2:INDEX($A:$A,ROW()-1)="小计")*D2:INDEX(D:D,ROW()-1))

公式释义：

• $A2:INDEX($A:$A,ROW()-1)：这个区域的第二个参数的设置方法前面已经讲过了，永远定位到当前单元格的上一行；
• ...="小计"：判断上述区域中的每个单元格内容是否为“小计”，结果为 true 或 false；
• SUMPRODUCT(...*D2:INDEX(D:D,ROW()-1))：将上述逻辑值与 D 列的所有数值相乘，只有上述条件为 true 的，D 列结果才能保留下来；最后用 sumproduct 求和。