![SUMPRODUCT 的替代方案](https://linux22.com/image/1564641/SUMPRODUCT%20%E7%9A%84%E6%9B%BF%E4%BB%A3%E6%96%B9%E6%A1%88.png)
我正在尝试从另一张 Excel 表中计算两列。假设第一张表是原始数据,第一列是日期,第二列是总计,第三列是持续时间,最后一列是我的计数器(用于检查重复项)。在计算应放在统计表中的总持续时间之前,它必须满足以下条件:日期必须等于统计表中的日期,计数器列必须等于 1。
统计表包含以下列(按顺序):日期和总持续时间。
在统计表的总持续时间列中,我使用以下公式
=SUMPRODUCT(SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1),
SUMIFS('RAW DATA'!C:C,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
但是该公式仅对持续时间进行求和,并不执行该SUMPRODUCT()
函数。如果我在 RAW DATA 表中执行该操作,它就可以=SUMPRODUCT(B:B,C:C)/SUM(B:B)
正常工作。
答案1
这不是修改公式=SUMPRODUCT(B:B,C:C)/SUM(B:B)
来检查指定条件的正确方法。
由于您在 内部进行求和,因此您的公式不会产生预期的交叉积SUMPRODUCT()
。
SUMPRODUCT(SUMIFS(«Total»),SUMIFS(«Duration»)/SUMIFS(«Total»)
和 一样,当然和 一样SUMIFS(«Total»)*SUMIFS(«Duration»)/SUMIFS(«Total»)
SUMIFS(«Duration»)
你需要使用不同的公式,一种能够产生数组里面SUMPRODUCT()
。以下样式适合仅有的B
和列中的可转换为数字的值C
。(公式返回的空字符串不可转换为数字。错误也不可转换为数字。)
输入此公式B2
并按 ctrl-enter/复制粘贴/向下填充到其余列中:
=SUMPRODUCT('RAW DATA'!$B$2:$B$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),
'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
请注意,对于这种形式的公式,不是建议在里面使用整个列SUMPRODUCT()
,因为它会减慢公式的执行速度很多。
只需记住用适当行数的范围替换范围,或使它们动态化,以便它们根据现有数据自动调整。
解释:
该('RAW DATA'!$A$2:$A$10=A2)
部分检查日期是否匹配,('RAW DATA'!$D$2:$D$10=1)
部分检查计数器是否相等1
。
当这些数组相乘时,TRUE
变成 a1
和FALSE
a 。因此,只有当两者都为真时,0
结果才会成立。1
当且仅当日期匹配且计数器为时,乘以'RAW DATA'!$B$2:$B$10
(或)才会得出适当的总计(或持续时间)值。'RAW DATA'!$C$2:$C$10
1
函数SUMPRODUCT()
将两个数组相乘,然后然后将它们相加。
当然,上述公式可以重构/简化为以下内容,因为检查的条件对于总数和持续时间都是相同的:
=SUMPRODUCT('RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
编辑:
B
如果列和中的值C
由公式生成,并且公式可以返回空字符串,则发生这种情况时,上述公式将导致错误#VALUE!
。以下将通过将空字符串视为零来解决这个问题:
=SUMPRODUCT(("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10)*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)
SUM()
可以使用和 构建另一个公式IF()
。
数组中输入(Ctrl++ Shift)Enter以下公式B2
并将其复制粘贴/填充到其余列中(不要忘记删除 and {
)}
:
{=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),'RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10,0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)}
请注意,SUM
可以用 替换SUMPRODUCT
,公式仍然有效。
编辑:
此公式也存在与上述相同的问题,即B
和C
列中存在空字符串。以下方法可修复此问题:
=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10),0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)