函数集合sum产品用途

SUMPRODUCT函数是Excel中的一个数学函数,也是一个“神函数”。它之所以被称为“神”,是因为它可以求和、计数、用多个权重计数和排名。

一、功能解释

基本语法是:

SUMPRODUCT(数组1,

公式:=SUMPRODUCT(B2:B9,C2:C9)

这个公式的含义是:

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

2.单一条件的和

如下图所示,计算绩效得分高于15的女性员工的得分:

如下所示,计算女性员工的绩效得分,并:

神函数SUMPRODUCT用法集锦-大盘站插图

公式:

= SUMPRODUCT((B2:B11 = & # 8221;女& # 8221;)*C2:C11)

其中包括:

B2:B11 = & # 8221;女& # 8221;:

比较B2每个单元格的值:B11与“女性”。如果性别为“女”,则为真;否则就是假的。结果返回一组逻辑值:

{假;真实;假的;真实;假的;真实;假的;真实;假的;真实;}

(B2:B11 = & # 8221;女& # 8221;)*C2:C11:

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

3.多条件求和

如下图所示,计算绩效得分高于15的女性员工的得分:

神函数SUMPRODUCT用法集锦-大盘站插图1

公式:

= SUMPRODUCT((B2:B11 = & # 8221;女& # 8221;)*(C2:C11 & gt;15),C2:C11)

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

=SUMPRODUCT((条件1) *(条件2) * …… *(条件N),求和范围)

4.模糊条件的和

如下图,计算销售部门女性员工的绩效得分,并:

神函数SUMPRODUCT用法集锦-大盘站插图2

销售部门不止一个。要找到所有的销售部门,你得根据关键字“销售”进行搜索,这是一种模糊搜索。

公式:

= sum product(is number(FIND(& # 8220;销售& # 8221;,A2:A11))*(C2:C11 = & # 8221;女& # 8221;),D2:D11)

其中包括:

查找(& # 8220;销售& # 8221;,A2:A11):

寻找& # 8221;A2:A11销售的每个单元格中的值& # 8221;,如果能找到,就返回& # 8221;销售& # 8221;单元格中的位置值,如果差值小于,则返回错误值#VALUE!。

这一部分的结果是:

{ #值!;1;1;1;#值!;1;#值!;#值!;1;#值!}

ISNUMBER(查找(& # 8220;销售& # 8221;A2:A11)):

判断上述数值中的每一个值是否都是数字,如果是,则返回TRUE,否则返回FALSE。因此,这部分公式的结果是:

{假;真实;真实;真实;假的;真实;假的;假的;真实;FALSE}

5.单一条件计数

计算女性员工的数量:

公式:

= sum product(N(B2:B11 = & # 8221;女& # 8221;))

神函数SUMPRODUCT用法集锦-大盘站插图3

n函数:

语法:N(值);

函数:将非数值转换为数值;

不同的参数值,对应的返回值:

神函数SUMPRODUCT用法集锦-大盘站插图4

在这个例子中,N(B2:B11 = & # 8221;女& # 8221;),它将值TRUE等于female返回为1,将值FALSE不等于female返回为0。

6.多条件计数

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

公式:

= SUMPRODUCT((B2:B11 = & # 8221;女& # 8221;)*(C2:C11 & gt;15))

神函数SUMPRODUCT用法集锦-大盘站插图5

7.模糊条件计数

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

公式:

= sum product(is number(FIND(& # 8220;销售& # 8221;,A2:A11))*(C2:C11 = & # 8221;女& # 8221;))

神函数SUMPRODUCT用法集锦-大盘站插图6

8.月度统计数据

要求:

每月总销售额

公式是:

=SUMPRODUCT((月$ 2:A $ 13)= D2)*($ B $ 2:B $ 13))

神函数SUMPRODUCT用法集锦-大盘站插图7

9.交叉列统计

要求:

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

公式是:

= sum product(($ B $ 2:$ G $ 2 = H $ 2)* $ B3:$ G3)

(在这个公式中,我们必须注意绝对引用中相对引用的使用)

神函数SUMPRODUCT用法集锦-大盘站插图8

10.多权重统计

要求:

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

公式是:

= sum product(B$2 :D$2,B3:D3)

神函数SUMPRODUCT用法集锦-大盘站插图9

11.二维区域统计

要求:

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

公式是:

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

神函数SUMPRODUCT用法集锦-大盘站插图10

12、不间断排名

RANK函数用于排名。如果价值观相同,排名会有差距。SUMPRODUCT函数用于避免这种排名差距。

如下图:

神函数SUMPRODUCT用法集锦-大盘站插图11

细胞C6的公式是:

= sum product(($ B $ 2:$ B $ 7 & gt;= B6)/COUNTIF($ B $ 2:B $ 7,$ B $ 2:B $ 7))

($ B $ 2:$ B $ 7 & gt;=B6),返回值为:

{真;真实;真实;真实;真实;FALSE}

即:{ 1;1;1;1;1;0}

COUNTIF($B$2:$B$7,$B$2:$B$7),返回值为:

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

sum product(($ B $ 2:$ B $ 7 & gt;= B6)/COUNTIF($ B $ 2:B $ 7,$ B $ 2:B $ 7))

即:sum product({ 1;1;0.5;0.5;1;0}),也就是排名4。