SUMPRODUCT函数返回相应的数组或区域乘积的和。其语法为:

SUMPRODUCT(数组1, [数组2], [数组3], …)

其中:

1. 数组1,必需,指定想要相乘并相加的数组数据。

2. 数组2,数组3,…,可选,指定想要相乘并相加的数组数据。数组个数不超过255个。

3. 指定的数组可以是数组、单元格区域或者代表单元格区域的名称、代表公式的名称,例如{1;2;3}、A1:A10或者MyRange。

例如,如下所示:

SUMPRODUCT函数基本原理详解

公式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函数可直接求出结果:

=SUMPRODUCT(D4:D8,E4:E8)

SUMPRODUCT函数是怎么做的呢?看下:

SUMPRODUCT函数基本原理详解

可以清楚地看出,SUMPRODUCT函数先使数组中相对应的元素相乘,然后将这些乘积相加,得到最终结果。

以上是SUMPRODUCT函数最基础的用法。在Excel中,创造性地使用函数,往往能够收到令人意想不到的效果。

应用技巧

下面,我们使用SUMPRODUCT函数来实现条件求和。

由于SUMPRODUCT函数总是将其参数数组相对应的元素相乘后求和,因此我们可以使用一个数组,其中包含的元素由TRUE/FALSE或1/0组成,TRUE/1是对应着想要求和的元素,而FALSE/0对应着不想求和的元素。

如下所示,仅仅想求出北区的商品销售总额。

SUMPRODUCT函数基本原理详解

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

SUMPRODUCT函数基本原理详解

但是,当我们使用公式:

=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函数基本原理详解

然而,我们不可能每次在公式中添加一个确定值的数组,因为如果要求和的条件发生改变的话,我们必须将这个数组进行相应的更改。因此,我们对公式进行改进:

=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))

再举一个示例。如下所示,想要求出洗手液在西区超市的销售量之和。

SUMPRODUCT函数基本原理详解

可使用公式:

=SUMPRODUCT(–(A2:A21=”洗手液”),–(B2:B21=”西区”),C2:C21)

在公式中,使用了双减号:

–(A2:A21=”洗手液”) =-1×-1×(A2:A21=”洗手液”)=1×(A2:A21=”洗手液”)

其作用是强制将包含TRUE/FALSE值的数组转换成包含1/0的数组。

发表回复