Excel 三种特殊情况下的求和公式:SUBTOTAL、LOOKUP 以及 COUNTIF
- 秋叶Excel
2024-05-22 19:58
本文作者:小花
本文编辑:竺兰
Excel 中有很多可用于条件求和的函数,如 SUMIF、SUMIFS、SUMPRODUCT。
但在一些特殊的数据表中进行条件求和,我们或许还需要其他函数的帮助。
今天,小花就分享三种特殊情况下的求和公式。
1、筛选后求和
审计员小 K 发现某下属公司部分报销费用可能存在风险,于是逐笔初筛出来。
现需设置一个公式,按部门统计发生额。随后,小 K 将进行二次筛选,当风险项被排除时,也能够自动剔除该金额。
这一问题的难点在于,如何确定每一条记录是否被筛选出来?虽然肉眼很容易分清,但如何让计算机明白却不那么容易。
好在,Excel 中有一个专门的筛选统计函数 ——SUBTOTAL 函数。
借助 SUBTOTAL 剔除隐藏单元格后进行统计的功能,我们可以设置如下公式完成筛选情况下的条件求和。
筛选下的条件求和公式:
=SUMPRODUCT(($C$8:$C$41)*SUBTOTAL(3,OFFSET($C$7,ROW($C$8:$C$41)-7,0))*($A$8:$A$41=A2))
公式说明 👇
❶ 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、合并单元格求和
合并单元格对报表的视觉呈有其「独到」之处,但其痛点也很「毒辣」—— 无法直接进行条件统计。
比如,在存在合并单元格的情况下,直接进行条件求和,计算结果多半都是错误的。
这是因为,合并单元格过程中,除首个单元格外,其余单元格的内容都会被清除。
要想能够在这种情况下实现条件求和,我们需要用 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))}
公式说明 👇
❶ 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、去除重复值求和
条件求和的另外一种特殊情况是,求和区域存在部分重复值,需要去重后再求和才能得到准确结果。
比如下图中,部分人员有重复的,无法直接进行条件求和。
这时候,我们需要使用 COUNTIF 函数来「稀释」重复值,再求和。
去重后的条件求和公式:
=SUMPRODUCT(($A$7$A$18=$A2)*($C$7$C$18)/COUNTIF($B$7$B$18$B$7$B$18))
公式说明 👇
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),作者:小花
广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。