我正在尝试从另一个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)
它,它将起作用。
这不是修改公式=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 / 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
将变成a1
和FALSE
a 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中的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
然后复制粘贴/填充到该列的其余部分(不要忘记删除{
和}
):
{=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] 删除。
我来说两句