SUMPRODUCT 的替代方案（第 2 部分）

Question

对于位于同一行（只是不同的列）的两个持续时间和位于同一行的两个整数，我尝试计算等效公式，如下所示我之前的问题其值位于不同的行上。

我尝试使用

=SUMPRODUCT(duration1,duration2,number1,number2)/sum(number1,number2) 

但当它们位于不同的行（但相同的列）时，我没有得到正确的答案。

我还不知道如何像我上一个问题中那样将日期添加为标准。

您可以从以下位置下载示例工作簿这里。

Answer 1

看起来您遇到了问题，因为您误解了该SUMPRODUCT()功能的具体工作方式。

它的作用是繁殖全部将论据的各个要素放在一起，然后对结果数组求和。

请看以下示例：

=SUMPRODUCT(A1:A3, B1:B3, C1:C3, D1:D3)

评估此公式的两个步骤是：

=SUMPRODUCT({A1*B1*C1*D1; A2*B2*C2*D2; A3*B3*C3*D3})

=A1*B1*C1*D1 + A2*B2*C2*D2 + A3*B3*C3*D3

对于您的具体情况，您实际上需要做的是获得二乘积和：

=SUM(SUMPRODUCT(duration1,number1),SUMPRODUCT(duration2,number2))/SUM(number1,number2)

对于您提供的示例工作簿，公式F5为：

=SUM(SUMPRODUCT('CHAT US Raw Data'!I6,'CHAT US Raw Data'!P6),SUMPRODUCT('CHAT US Raw Data'!T6,'CHAT US Raw Data'!U6))/SUM('CHAT US Raw Data'!I6,'CHAT US Raw Data'!T6)

请注意，这相当于在SUMPRODUCT()函数内部明确执行乘法，并仅使用函数对结果数组求和（如在第 1 部分问题的答案中所做的那样）：

=SUM(SUMPRODUCT(duration1*number1),SUMPRODUCT(duration2*number2))/SUM(number1,number2)

实际公式为F5：

=SUM(SUMPRODUCT('CHAT US Raw Data'!I6*'CHAT US Raw Data'!P6),SUMPRODUCT('CHAT US Raw Data'!T6*'CHAT US Raw Data'!U6))/SUM('CHAT US Raw Data'!I6,'CHAT US Raw Data'!T6)

---

根据您之前的问题，此公式仍需要进行调整以检查匹配日期（以及第一个匹配的学生姓名，如果仍然适用）。

对于股息，我们只需添加额外的日期检查期限。

SUM()但是，在添加日期检查之前，需要修改除数函数。我们需要两个 sumif 函数的总和（假设您希望将公式扩展到多行）：

=SUM(SUMPRODUCT((date1=date)*duration1*number1),SUMPRODUCT((date2=date)*duration2*number2))
/SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))

实际公式为F5：

=SUM(SUMPRODUCT(('CHAT US Raw Data'!H6=E5)*'CHAT US Raw Data'!I6*'CHAT US Raw Data'!P6),SUMPRODUCT(('CHAT US Raw Data'!S6=E5)*'CHAT US Raw Data'!T6*'CHAT US Raw Data'!U6))/SUM(SUMIF('CHAT US Raw Data'!H6,E5,'CHAT US Raw Data'!I6),SUMIF('CHAT US Raw Data'!S6,E5,'CHAT US Raw Data'!T6))

除了手动执行内部乘法SUMPRODUCT()（以及由此产生的隐式布尔强制），另一种方法是使用双重否定一元运算符将日期检查明确强制为 1 和 0。例如，(date1=date)将变成--(date1=date)：

=SUM(SUMPRODUCT(--(date1=date),duration1,number1),SUMPRODUCT(--(date2=date),duration2,number2))
/SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))

实际公式为：

=SUM(SUMPRODUCT(--('CHAT US Raw Data'!H6=E5),'CHAT US Raw Data'!I6,'CHAT US Raw Data'!P6),SUMPRODUCT(--('CHAT US Raw Data'!S6=E5),'CHAT US Raw Data'!T6,'CHAT US Raw Data'!U6))/SUM(SUMIF('CHAT US Raw Data'!H6,E5,'CHAT US Raw Data'!I6),SUMIF('CHAT US Raw Data'!S6,E5,'CHAT US Raw Data'!T6))

---

最后要注意的是，sumif 除数有等效的 sumproduct 版本。

例如，

SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))

相当于

SUM(SUMPRODUCT((date1=date)*number1),SUMPRODUCT((date2=date)*number2))

也

SUM(SUMPRODUCT(--(date1=date),number1),SUMPRODUCT(--(date2=date),number2))