SUMPRODUCT的用法 一个神圣的功能

SUM函数不仅是Excel中的一个数学函数，更是一个“神函数”。之所以被称为“神”，是因为它能完成求和、计数、多权统计和排序。

功能解释

基本语法是：

SUMPRODUCT(array1，[array2]，[array3]，…)

SUM函数语法有以下参数：

Array1:必选。第一个数组参数，其对应的元素需要相乘和求和。

Array2，array3，…:可选。从2到255个数组参数，对应的元素需要相乘和求和。

请特别注意：

参数必须具有相同的维度。否则，函数SUMPRODUCT将返回#VALUE！错误值#REF！

1.基本用法。

SUMPRODUCT函数最基本的用法是：

将数组之间的对应元素相乘，并返回乘积之和。

下图：

公式：=sumproduct (b2:b9，c2:c9)。

这个公式的意思是：

B2 * C2 B3 * C3 B4 * C4 B5 * C5 B6 * C6 B7 * C7 B8 * C8 B9 * C9

2.单条件求和。

如下图所示，计算女性员工的绩效得分：

公式：

=SUMPRODUCT((B2:B11=“女”)* c23360c11)

其中：

B2:B11="女性":

将B2:B11中每个单元格的值与“女性”进行比较，如果性别为“女性”，则为真，否则为假，结果返回一组逻辑值：

{ FALSE真；假；真；假；真；假；真；假；真；}

(B2:B11=“女”)*C2:C11:

将上述逻辑数组中的值乘以相应的值C2:C11。

3.多条件求和。

如下图，计算绩效分数高于15的女性员工的分数：

公式：

=SUMPRODUCT((B2:B11="女")* (c23360c115)，c23360c11)。

多条件求和的一般写法是：

=SUMPRODUCT((条件1) *(条件2) *.*(条件n，总和范围)。

4.模糊条件的和。

如下图所示，计算销售部门女性员工的绩效得分：

不止一个销售部门。要想找到所有的销售部门，必须根据关键词“sales”进行搜索，这是一种模糊搜索。

公式：

=SUMPRODUCT(is number(FIND(" sales "，A2:A11))*(C2:C11="女")，D2:D11)。

其中：

查找(“销售”，A2:A11):

在A2:A11的每个单元格值中查找“销售额”。如果可以找到，返回单元格值中“sales”的位置。如果差值不够，返回错误值#VALUE！

这部分的结果是：

{#VALUE！1;1;1;#VALUE！1;#VALUE！#VALUE！1;#VALUE！}

ISNUMBER(FIND ("sales "，A2:A11)):

判断上述数值中的每个值是否为数字，如果是，则返回TRUE，否则返回FALSE，所以这部分公式的结果为：

{ FALSE真；真；真；假；真；假；假；真；FALSE}

5.单条件计数。

计算女性员工人数：

公式：

=SUMPRODUCT(N(B2:B11=“女”))

功能：

语法：N(VALUE)；

功能：将非数值转换为数值形式；

不同的参数值，对应的返回值：

在本例中，N(B2:B11=“女性”)是一个返回TRUE的值，等于女性对1，但不等于女性。

FALSE返回0。

6.多条件计数。

计算绩效得分高于15的女员工人数。

公式：

=SUMPRODUCT((B2:B11="女")* (c23360c115))

7.模糊条件计数。

计算销售部门的女性员工人数。

公式：

=SUMPRODUCT(is number(FIND(" sales "，A2:A11))*(C2:C11="女")。

8.按月统计数据。

要求：

按月统计的总销售额。

公式是：

=SUMPRODUCT((MONTH(2:澳元13澳元)=D2)*(2:澳元13澳元))

9.跨列统计。

要求：

统计三个仓库的总销售额和库存。

公式是：

=SUMPRODUCT($ B $ 2: $ G $ 2=H $ 2)* $ B 3: $ G3)

(我们一定要注意这个公式中绝对引用中相对引用的使用。)

10.多权重统计。

要求：

根据分项得分和权重的比例计算总分。

公式是：

=SUMPRODUCT(B$2:D$2，B3:D3)

11、二维区域统计。

要求：

统计每个销售部门每种商品的总销量。

公式是：

=SUMPRODUCT(($ B $ 2: $ B $ 13=$ E2)*(A $ 2: $ A $ 13=F $ 1)* $ C $ 2: $ C $ 13)

12.不间断排名。

用RANK函数排名，如果有相同的数值，就会出现排名不连续。使用SUMPRODUCT函数，可以很好地避免这种排名的不连续性。

下图：

C6细胞公式是：

=SUMPRODUCT($B$2:$B$7=B6)/COUNTIF(B $ 2: $ B $ 7，$ B $ 2: $ B $ 7))

($B$2:$B$7=B6)，返回值为：

{真；真；真；真；真；FALSE}

即：{ 1；1;1;1;1;0}

Countif ($ b $2: $ b $7，$ b $2: $ b $7)，返回值为：

{1;1;2;2;1;1}

SUMPRODUCT($B$2:$B$7=B6)/COUNTIF(B $ 2: $ B $ 7，$ B $ 2: $ B $ 7))

即： umproduct({ 1；1;0.5;0.5;1;0})，即排名第4。