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函数,那么必须使用“*”。

赞(1)
office办公分享 » SUMPRODUCT函数详解(3)

本文链接:SUMPRODUCT函数详解(3)https://www.officeapi.cn/98874.html

OFFICE,天天学一点,进步多一点

联系我们