本文作者:小花

本文编辑:竺兰

Excel 中有很多可用于条件求和的函数,如 SUMIF、SUMIFS、SUMPRODUCT。

但在一些特殊的数据表中进行条件求和,我们或许还需要其他函数的帮助。

今天,小花就分享三种特殊情况下的求和公式。

1、筛选后求和

审计员小 K 发现某下属公司部分报销费用可能存在风险,于是逐笔初筛出来。

现需设置一个公式,按部门统计发生额。随后,小 K 将进行二次筛选,当风险项被排除时,也能够自动剔除该金额。

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

这一问题的难点在于,如何确定每一条记录是否被筛选出来?虽然肉眼很容易分清,但如何让计算机明白却不那么容易。

好在,Excel 中有一个专门的筛选统计函数 ——SUBTOTAL 函数。

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

借助 SUBTOTAL 剔除隐藏单元格后进行统计的功能,我们可以设置如下公式完成筛选情况下的条件求和。

筛选下的条件求和公式:

=SUMPRODUCT(($C$8:$C$41)*SUBTOTAL(3,OFFSET($C$7,ROW($C$8:$C$41)-7,0))*($A$8:$A$41=A2))

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

公式说明 👇

❶ OFFSET($C$7,ROW($C$8:$C$41)-7)

ROW ($C$8:$C$41)-7 函数返回一组 1-34 的有序数组,OFFSET 函数根据该数组,逐一偏移返回 C8:C41 的每一个单元格。这与直接引用 C8:C41 是不同的,前者将每个单元格都视为一个单独的区域,而后者则将 C8:C41 视为一个整体。

❷ SUBTOTAL(3,①)

3 是计数功能代码,此处 SUBTOTAL 识别 C8:C41 的每个单一单元格区域是否隐藏,如果该单元格隐藏,则统计结果为 0,否则为 1。即,②的结果为 1 则表示该单元格在筛选结果中。

❸ SUMPRODUCT(($C$8:$C$41)*②*($A$8:$A$41=A2))

SUMPRODUCT 函数的经典用法,C8:C41 是求和区域,②和 ($A$8:$A$41=A2) 是条件区域,求和区域和条件区域一一对应相乘再求和,即为条件求和结果。

2、合并单元格求和

合并单元格对报表的视觉呈有其「独到」之处,但其痛点也很「毒辣」—— 无法直接进行条件统计。

比如,在存在合并单元格的情况下,直接进行条件求和,计算结果多半都是错误的。

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

这是因为,合并单元格过程中,除首个单元格外,其余单元格的内容都会被清除。

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

要想能够在这种情况下实现条件求和,我们需要用 LOOKUP 函数来「复原」空白值。

合并单元格下的条件求和公式:

{=SUM(($C$8$C$20)*(LOOKUP(ROW($A$8$A$20)IF($A$8$A$20<>"ROW($A$8$A$20)"")$A$8$A$20)=A2))}

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

公式说明 👇

❶ IF($A$8:$A$20<>””,ROW($A$8:$A$20),””)

对 A8:A20 进行判断,如不为空白,返回行号,否则返回空白。也就是说,每一个合并单元格首行都返回其行号。

❷ LOOKUP(ROW($A$8:$A$20),①,$A$8:$A$20)

如果 A8:A20 中的某个单元格为所在合并单元格的首行,则其行号会在①中出现,LOOKUP 返回其本身;

如果 A8:A20 中的某个单元格不是所在合并单元格的首行,则它在①中与空白对应,查询其行号时,LOOKUP 匹配到小于且接近去其行号的最大值,即其所在合并单元格首行的行号,最终返回所在合并单元格首个单元格的值。由此,所有因合并而被清除的单元格值都得到复原。

❸ {=SUM(($C$8:$C$20)*(②=A2))}

利用 SUM 的数组运算,返回乘积和,和 SUMPRODUCT 条件求和原理类似,此处必须使用 SUM 函数,按【Ctrl+Shift+Enter】执行数组运算。

3、去除重复值求和

条件求和的另外一种特殊情况是,求和区域存在部分重复值,需要去重后再求和才能得到准确结果。

比如下图中,部分人员有重复的,无法直接进行条件求和。

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

这时候,我们需要使用 COUNTIF 函数来「稀释」重复值,再求和。

去重后的条件求和公式:

=SUMPRODUCT(($A$7$A$18=$A2)*($C$7$C$18)/COUNTIF($B$7$B$18$B$7$B$18))

Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF-风君雪科技博客

公式说明 👇

COUNTIF ($B$7:$B$18,$B$7:$B$18) 统计每个姓名出现的次数 n,将其每一行工资对应「稀释」为其本身的 1 / n,再进行条件求和,每个人的 1 / n 工资都被计算了 n 次,这就实现了去重后的条件求和。

以上,就是小花分享的三种特殊情况下的条件去和公式,包括:

❶ 利用 SUBTOTAL 识别筛选再完成条件求和;

❷ 利用 LOOKUP 复原合并单元格缺失值完成包含合并单元格的条件求和;

❸ 利用 COUNTIF 稀释原值完成去重后的条件求和。

你学会了吗?

随着 Excel 版本不断更新,也涌现了 SCAN、LAMBDA、UNIQUE、FILTER 等函数,能够简化上述公式或者公式中的某个片段,有条件的小伙伴不妨一试。

学无止尽,与君共勉!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小花