SUMPRODUCT的替代品

阿斯特丽德22

我正在尝试从另一个Excel工作表计算两列。假设第一张是RAW DATA,第一列是Date,第二列是Total,第三列是Duration,最后一个是我的计数器(检查重复项)。在我计算应该放在TALLY SHEET中的总持续时间之前,它必须满足以下条件:日期必须等于TALLY SHEET中的日期,并且计数器列必须等于1。

TALLY SHEET具有以下列(按顺序):日期和总持续时间。

在“ TALLY SHEET”的“ Total Duration”列中,我使用以下公式

=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)它将起作用。

robinCTS

这不是修改公式=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()如果仅存在数字可转换的值,则以下样式是合适的(公式返回的空字符串不可数字转换。错误也不是。)BC

在下面输入此公式,B2然后ctrl-enter / copy-paste / fill-down进入该列的其余部分:

=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将变成a1FALSEa 0因此,1只有两个条件都为真时,结果才是。

当且仅当日期匹配且计数器为a时,乘以'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)

编辑:

如果column中的BC是由公式生成的,并且公式可以返回空字符串,则发生这种情况时,上述公式将导致#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然后复制粘贴/填充到该列的其余部分(不要忘记删除{}):

{=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)

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章