SUMPRODUCT函数基本原理详解 _知识经验

SUMPRODUCT函数返回相应的数组或区域乘积的和。其语法为：
SUMPRODUCT(数组1, [数组2], [数组3], …)
其中：
1. 数组1，必需，指定想要相乘并相加的数组数据。
2. 数组2,数组3,…，可选，指定想要相乘并相加的数组数据。数组个数不超过255个。
3. 指定的数组可以是数组、单元格区域或者代表单元格区域的名称、代表公式的名称，例如{1;2;3}、A1:A10或者MyRange 。
例如，如下所示：

文章插图
公式1：
=SUMPRODUCT({1;2;3})
得到结果6，即对数组元素求和：
=1+2+3
公式2：
=SUMPRODUCT(C4:C6)
对指定单元格区域求和，得到结果6，
公式3：
=SUMPRODUCT(MyRange1)
对命名区域求和，名称MyRange1代表的区域为C4:C6，结果为6 。
公式4：
=SUMPRODUCT(MyRange2)
对命名公式求和，名称MyRange2引用的公式为：=OFFSET(Sheet1!$C$3,1,0,3,1)，即单元格区域C4:C6，得到结果为6 。
顾名思义，SUMPRODUCT函数由SUM+PRODUCT组成，即求乘积再求和。例如下所示的例子。

文章插图
如果要求所有商品的销售总额，可以先使用数量乘以单价求得每件商品的销售额，然后将结果相加。然而，使用SUMPRODUCT函数可直接求出结果：
=SUMPRODUCT(D4:D8,E4:E8)
SUMPRODUCT函数是怎么做的呢？看下：

文章插图
可以清楚地看出，SUMPRODUCT函数先使数组中相对应的元素相乘，然后将这些乘积相加，得到最终结果。
以上是SUMPRODUCT函数最基础的用法。在Excel中，创造性地使用函数，往往能够收到令人意想不到的效果。
应用技巧
下面，我们使用SUMPRODUCT函数来实现条件求和。
由于SUMPRODUCT函数总是将其参数数组相对应的元素相乘后求和，因此我们可以使用一个数组，其中包含的元素由TRUE/FALSE或1/0组成，TRUE/1是对应着想要求和的元素，而FALSE/0对应着不想求和的元素。
如下所示，仅仅想求出北区的商品销售总额。

文章插图
此时，我们可以添加一个包含逻辑值的数组，过滤掉不需要求和的数据，其原理如下所示。

文章插图
但是，当我们使用公式：
=SUMPRODUCT(D4:D8,E4:E8,{FALSE;TRUE;FALSE;FALSE;TRUE})
会得到结果：-
因为此时Excel并不会将TRUE/FALSE值自动转换成1/0 。那么，我们需要强制将TRUE/FALSE转换：
=SUMPRODUCT(D4:D8,E4:E8,1*{FALSE;TRUE;FALSE;FALSE;TRUE})
我们使用1与TRUE/FALSE值的数组相乘，使其转换成数组：{0;1;0;0;1} 。这样，相乘的三个数组如下所示：

文章插图
然而，我们不可能每次在公式中添加一个确定值的数组，因为如果要求和的条件发生改变的话，我们必须将这个数组进行相应的更改。因此，我们对公式进行改进：
=SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=”北区”))
此公式中的：1*(C4:C8=”北区”)与1*{FALSE;TRUE;FALSE;FALSE;TRUE}等价。
实际上，如果我们将要查找的值放置在某个单元格（例如G3）中，那么可以将公式修改为：
=SUMPRODUCT(D4:D8,E4:E8,1*(C4:C8=G3))
这样，直接修改该单元格中的值，就可以得到相应的结果。
如果不想额外的乘以1，可以使用公式：
=SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=G3))
再举一个示例。如下所示，想要求出洗手液在西区超市的销售量之和。