疯狂的Excel公式，只为条件求和

标签：公式练习

在使用Excel时，经常会遇到根据多个条件求相应的和的问题。

示例数据如下图1所示。工作表中有两个表，一个是活动的全部数据，另一个列出了其中暂停活动的列表。

图1

注：示例数据来源于chandoo.org。

要求：

1.计算除暂停列表中的活动外，其它所有活动的总小时数。

2.计算除暂停列表中的活动外，其它所有活动中例行活动的总小时数。

问题的解决实质上仍然涉及到查找，找到不属于暂停列表中的活动，求相应的小时数之和，然后再求这些活动中例行活动的小时数之和。

我首先想到使用经典的MATCH/INDEX函数组合的数组公式，但没有成功，接着转向使用矩阵公式。第1问的思路为：将总列表中的活动与暂停列表中的活动比较，求出其对应小时数之和，然后使用总小时数相减，即得到除暂停列表中的活动外的总小时数：

=SUM(C2:C16)-SUM(MMULT(TRANSPOSE(C2:C16),--(A2:A16=TRANSPOSE(F2:F8))))

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

第2问在第1问的基础上，还在剔除类别为“一次性”的活动所对应的次数：

=SUM((MMULT((A2:A16=TRANSPOSE(F2:F8))+(B2:B16="一次性"),{1;1;1;1;1;1;1})=0)*(C2:C16))

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

第2种解法，更简单一些的公式，不过仍然是数组公式。

第1问，使用了LOOKUP函数：

=SUM(C2:C16)-SUMPRODUCT(LOOKUP(F2:F8,A2:C16,C2:C16))

总小时数减去暂停列表中活动对应的小时数。

第2问，使用了SUMIF函数：

=SUMIF(B2:B16,"例行",C2:C16)-SUM(--(A2:A16=TRANSPOSE(F2:F8))*(C2:C16)*--(B2:B16="例行"))

总表中例行活动总小时数减去暂停列表中例行活动小时数。

第3种解法。

第1问，使用COUNTIFS函数查找暂停活动：

=SUM(C2:C16)-SUMPRODUCT(COUNTIFS(F2:F8,A2:A16),C2:C16)

或者使用SUMIF函数：

=SUM(C2:C16)-SUM(SUMIF(A2:A16,F2:F8,C2:C16))

第2问，与上一种解法类似，只是这里使用了COUNTIFS函数：

=SUMIF(B2:B16,"例行",C2:C16)-SUMPRODUCT(COUNTIFS(F2:F8,A2:A16),C2:C16,--(B2:B16="例行"))

或者：

=SUMIF(B2:B16,"例行",C2:C16)-SUM(SUMIFS(C2:C16,B2:B16,"例行",A2:A16,F2:F8))

第4种解法，使用了MATCH函数。

第1问：

=SUM(C2:C16)-SUM(C2:C16*(IFERROR(MATCH(A2:A16,F2:F8,0),0)>0))

第2问：

=SUMIFS(C2:C16,B2:B16,"例行")-SUM(C2:C16*(IFERROR(MATCH(A2:A16,F2:F16,0),0)>0)*(B2:B16="例行"))

都是数组公式。

第5种解法，更简洁。

第1问：

=SUM(ISNA(MATCH(A2:A16,F2:F8,0))*C2:C16)

第2问：

=SUM(ISNA(MATCH(A2:A16,F2:F8,0))*(B2:B16="例行")*C2:C16)

都是数组公式。

经过上述公式的编写后，对问题的理解更深入了，可以得出第6种解法。

第1问：

=SUMPRODUCT(1-COUNTIF(F2:F8,A2:A16),C2:C16)

第2问：

=SUMPRODUCT(1-COUNTIF(F2:F8,A2:A16),N(B2:B16="例行"),C2:C16)

还有解法吗？欢迎留言。

注：在知识星球完美Excel社群可以下载本文配套示例工作簿。