我正在尝试从另一张 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和FALSEa 。因此，只有当两者都为真时，0结果才会成立。1
当且仅当日期匹配且计数器为时，乘以'RAW DATA'!$B$2:$B$10（或）才会得出适当的总计（或持续时间）值。'RAW DATA'!$C$2:$C$101
函数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)