SUMPRODUCT函数详解(3)

SUMPRODUCT函数的语法格式

到目前为止我们所给出的示例中，SUMPRODUCT函数的格式是：

=SUMPRODUCT((数组1=条件1)*(数组2=条件2)*(数组3))

正如上文所提到的，我们也可以使用：

=SUMPRODUCT((数组1=条件1)*(数组2=条件2),(数组3))

运算符“*”仅需要将条件数组TRUE/FALSE强制转换为数值。

当使用算术运算符强制将TRUE/FALSE值转换为1/0时，我们可以使用一些不同的运算符获得相同的结果。通过给每个条件数组分别乘以1也可以进行这样的强制转换：

=SUMPRODUCT((数组1=条件1)*1,(数组2=条件2)*1,(数组3))

或者：

=SUMPRODUCT(1*(数组1=条件1),1*(数组2=条件2),(数组3))

或者为每个条件数组进行1次方：

=SUMPRODUCT((数组1=条件1)^1,(数组2=条件2)^1,(数组3))

或者每个条件数组加0：

=SUMPRODUCT((数组1=条件1)+0,(数组2=条件2)+0,(数组3))

或者：

=SUMPRODUCT(0+(数组1=条件1),0+(数组2=条件2),(数组3))

或者通过使用N函数：

=SUMPRODUCT(N(数组1=条件1),N(数组2=条件2),(数组3))

这些方法不同于“*“运算符是它们应用于单个的数组，而“*”运算于两个数组。

上面介绍的方法取决于您的偏好，当然，只有单个条件数组时，不能使用“*”运算符。

还有一种方法是，使用双目操作符：

=SUMPRODUCT(–(数组1=条件1),–(数组2=条件2),(数组3))

对于笔者来说，偏好于使用双目–操作符，因为这样避免了函数调用，并且在所有情形下都工作。

此外，还有另一种变异的方法，使用单目操作符“-”，例如：

=SUMPRODUCT(-(数组1=条件1),-(数组2=条件2),(数组3))

但笔者不提倡使用这种方法，因为它没有实际的优点，并且必须两两配合，否则会返回一个负值。

小结

在单元格中输入“=A1=10”进行测试，通常会返回TRUE或FALSE。如果想将TRUE/FALSE值强制转换为1/0，使用诸如下面的公式：

=SUMPRODUCT(–(B5:B1953=101))

SUMPRODUCT数组通常由逗号分隔。因此，为了保持这种格式，如果有多个条件，则可以在条件中使用–，例如：

=SUMPRODUCT(–(B5:B1953=101),–(C5:C1953=7))

但是，如果简单地将两个含TRUE/FALSE值的数组相乘，则隐式地将值解析为1/0，然后求和，不需要逗号，例如：

=SUMPRODUCT((B5:B1953=101)*(C5:C1953=7))

更进一步，数值数组可以使用相同的运算符，或者恢复为逗号。因此，公式可以写为：

=SUMPRODUCT(–(B5:B1953=101),–(C5:C1953=7),(D5:D1953))

或者：

=SUMPRODUCT((B5:B1953=101)*(C5:C1953=7),(D5:D1953))

或者：

=SUMPRODUCT(–(B5:B1953=101),–(C5:C1953=7),–(D5:D1953))

或者：

=SUMPRODUCT((B5:B1953=101)*(C5:C1953=7)*(D5:D1953))

或者：

=SUMPRODUCT(–(B5:B1953=101),–(C5:C1953=7)*(D5:D1953))

如果结果是将两个条件相乘的乘积，那么最好将两个条件数组相乘，这将TRUE/FALSE强制转换为1/0并求和：

=SUMPRODUCT((条件1)*(条件2))

与上面的公式等价的是：

=SUMPRODUCT(–(条件1),–(条件2))

然而，如果仅有一个条件，则使用双目运算符–强制转换为1/0：

=SUMPRODUCT(–(条件1))

与上面的公式等价的是：

=SUMPRODUCT((1*(条件1)))

在SUMPRODUCT函数中，使用–的情形都能使用”*”，反之，如果在SUMPRODUCT函数中使用TRANSPOSE函数，那么必须使用“*”。