Question to say "I can!"

SUMPRODUCT函数详解

2014-10-22

前面,我们学习和运用了许多关于SUMPRODUCT函数的例子,但是,对SUMPRODUCT函数的了解可能还有些陌生,那么,今天我们来对SUMPRODUCT函数进行详细的解释。

本文资料来源于xlDynamic.com,供参考。

SUMPRODUCT函数的运作原理

理解SUMPRODUCT函数如何工作能够帮助我们决定在什么情况下使用它,以及如何构建公式并扩展其用法。

下面图片中是我们将要使用的示例数据。

图中,A1:A12是商品,B1:B12是分类,C1:C12是销售的数量。本例中,求卖了多少属于类别A的商品Ford。

使用下面的公式可以得到结果:

=SUMPRODUCT((A1:A12="Ford")*(B1:B12="A")*(C1:C12))

该公式的第一部分(A1:A12="Ford")检查值为Ford的商品,返回值为TRUE/FALSE的数组,本例中是:

{TRUE,FALSE,TRUE,TRUE, TRUE,TRUE, TRUE,FALSE,TRUE, TRUE, TRUE, TRUE}

同样,使用(B1:B12="A")检查值为A的分类,返回值为TRUE/FALSE的数组,本例中是:

{FALSE,FALSE,TRUE,TRUE,FALSE, TRUE, TRUE, TRUE,TRUE, TRUE, TRUE, TRUE}

最后,(C1:C12)返回含有一系列数值的数组:

{3,4,2,1,4,3,2,8,6,8,7,6}

现在我们获得了3个数组,两个含有TRUE/FALSE值,一个含有数值,如下图所示:

SUMPRODUCT通常处理数值数组,然而我们的公式中既有数值数组,也有含有TRUE/FALSE值的数组。通过使用“*”(乘)运算符,能够获得可以求和的数字值。“*”运算符强制使这两个含有TRUE/FALSE值的数组变为含有1/0值的单个数组。TRUE乘以TRUE将得到1(在工作表中试试,在单元格中输入=TRUE*TRUE,查看结果),任何其它的组合的结果为0。因此,当两个条件都满足时,结果为1,当任一或者两个条件都不满足时,结果为0。第一个数组中的TRUE/FALSE值与第二个数组中的TRUE/FALSE值相乘,结果是一个含有1/0值的数组,即{0,0,1,1,0,1,1,0,1,1,1,1}。这个数组的值接着和销售数量数组的值相乘,结果为满足两个测试条件的销售数量的数组。接下来,SUMPRODUCT函数汇总该数组中的值,得到最终的结果。

上图展示了“*”运算符处理前条件测试所分解的值。

下图展示了TRUE/FALSE值等价于数字值1/0,并分别相乘后的结果。在这里,我们应该能够看到SUMPRODUCT函数是如何运算直至获得最终结果,即35。

下图展示了没有销售数量列时等价的1/0值,即使用SUMPRODUCT函数统计满足两个条件的行数:

按照我们上面的解释,在使用SUMPRODUCT函数时,“*”运算符解决了将多个数组转换成单个的合成数组,剩下由SUMPRODUCT函数来简单地对这个合成数组的成员求和。SUMPRODUCT函数能够处理单个数组(例如,在单元格A1、A2、A3中分别输入数字1、2、3,然后在一个单元格中插入公式=SUMPRODUCT(A1:A3),将返回结果6)。事实上,我们仅需要“*”运算符来强制对特定条件进行测试的数组进行转换,而不需要它来对不是条件测试的数组进行操作。因此,对于上例,我们也可以使用下面的公式:

=SUMPRODUCT((A1:A12="Ford")*(B1:B12="A"),(C1:C12))

在使用SUMPRODUCT函数时,所有的数组都必须有相同的大小,这样每个数组相对应的成员能够彼此相乘。

在使用SUMPRODUCT函数时,数组不能为整列(例如A:A),数组必须是一列里的某个单元格区域。但是,可以使用整行(例如1:1)。

在SUMPRODUCT函数中,数组不能够同时包含列和行区域,它们必须全是列,或者全是行。然而,行数据能够使用函数转置成列,参见后面的示例。



作者:admin | Categories:办公能手 | Tags:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

无觅相关文章插件,快速提升流量