|
完美Excel公众号：excelperfect
本文资料来源于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函数中，数组不能够同时包含列和行区域，它们必须全是列，或者全是行。然而，行数据能够使用函数转置成列，参见后面的示例。 |
|
|
