学Excel函数怎能不会SUMPRODUCT?

学Excel函数怎能不会SUMPRODUCT?

哈罗，大家好，今天给大家分享一个很常用也很实用的函数：SUMPRODUCT。众所周知，条件求和及计数是表格使用者最经常碰到的两个问题，而该函数恰恰兼具条件求和与计数两大功能于一身;于是便不可不学了。

基础语法

SUMPRODUCT的官方语法说明是在给定的几组数组(array)中，将数组间对应的元素相乘，并返回乘积之和。语法格式如下：

=SUMPRODUCT(array1,array2,array3, …)

——SUM是求和的意思，PRODUCT是相乘的意思，参数之间相乘之后再求和，你看，SUMPRODUCT确实人如其名了。

看我手，歪、图、斯瑞……总结起来，SUMPRODUCT函数具有以下三个特点：

1> 它本身默认执行数组运算。

2> 它会将参数中非数值型的数组元素作为0处理。

3> 参数必须有相同的尺寸，否则返回错误值。

特点解析

看完了SUMPRODUCT的简历，想必很多朋友是雾里看花，仅仅对它有个模糊的认知，它的这些特点是啥意思?它到底能够胜任什么样子的工作?其实并不了然。

打个响指，我举几个例子。

" 如上图所示的数据表，C列是商品单价，D列是销售数量，现在需要在C9单元格计算销售总额。

C9输入以下公式，即可得出结果11620.60

=SUMPRODUCT(C3:C7, D3:D7)

这便是一个简单的SUMPRODUCT函数了。它的运算过程是：C3:C7和D3:D7两个区域数组内的元素分别相乘，也就是C3*D3,C4*D4,C5*D5……直至C7*D7

" 等于先将每个商品的销售金额计算出来，最后汇总求和。

由于SUMPRODUCT函数第一个特点，本身是支持数组间运算的，所以虽然该公式执行了多项运算，但并不需要按数组三键结束公式输入。

有的朋友说啦，公式也可以写成这样：

=SUMPRODUCT(C3:C7*D3:D7)

或者使用以下数组公式，也是可以的。

=SUM(C3:C7*D3:D7)

那么这三个公式之间有什么区别呢?

首先，大部分情况下，SUMPRODUCT函数都不需要数组三键结束公式输入即可执行数组运算，而SUM函数是需要的。

其次，就要说到SUMPRODUCT函数另一个非常重要的特点了。

……

我们将上面的表稍做改动，将“钢笔”的销售数量更改为：暂未统计。同样需要在C9单元格计算销售总额。

" 这时候，如果使用公式：

=SUMPRODUCT(C3:C7*D3:D7)

或者数组公式：

=SUM(C3:C7*D3:D7)

都将返回错误值#VALUE!

返回错误值的原因在于D4单元格“暂未统计”为文本值，文本值是无法直接参与数学运算的，于是C4*D4返回错误值#VALUE!，进而造成整个公式的结果返回错误值。

而使用以下公式就没有这方面的困扰，会直接返回正确结果：

=SUMPRODUCT(C3:C7,D3:D7)

这便是SUMPRODUCT函数的第二个特点：将非数值型的数组元素作为0处理。

以该示例来说，D4单元格的值“暂未统计”为文本，并非数值，SUMPRODUCT将其主动视为零，于是C4*D4，结果亦为零，其余数组元素照常计算，得出11385.60的结果。

需要特别说明的是，SUMPRODUCT将非数值型的数组元素作为0处理，所谓的非数值型数组元素，包含逻辑值、文本，但并不包含错误值，如果数组元素中包含错误值，该公式亦返回错误值，比如该示例的第一条公式。

……

说完了SUMPRODUCT函数的两个特点，我们就再来聊聊它的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。

我们依然用上述图片的例题为例，继续计算商品的销售总额。如果我们在C9输入公式：

=SUMPRODUCT(C3:C7,D3:D6)

结果会是怎么样的呢?

错误值：#VALUE!

为什么?

细心的您肯定已经注意到了，两个区域数组，C3:C7明显显比D3:D6多了一个元素，C3和D3结对子，C4和D4结对子……那么C7和谁结对子呢?女人们都嫁了，结果剩下一个光棍，这日子没法过了!一个萝卜一个坑，只有萝卜没有坑，这不是要萝卜死吗?

——于是SUMPRODUCT就不高兴了，它给你一个错误值#VALUE!，明确告诉你，和谐时代幸福岁月，日子不能这么过。

这就是SUMPRODUCT函数的第三个特点：数组参数必须有相同的尺寸，否则返回错误值。

下面是一道练习题，你看看，能用SUMPRODUCT函数做出来吗?

" 案例拓展

假设下面这张图，是某个公司工资发放的部分记录表(数据纯属虚拟，如有雷同，那是穿越)。A列是工资发放的时间，B列是员工所属的部门，C列是员工姓名，D列是相关员工领取的工资金额。

" ——那么，问题和广告都来了：

1

员工西门庆领取了几次工资?

这是一个单条件计数的问题,通常我们使用COUNTIF函数,但如果使用SUMPRODUCT函数，一般写成这样：

=SUMPRODUCT((C2:C13=”西门庆”)*1)

先判断C2:C13的值是否等于”西门庆”，相等则返回TRUE，不等则返回FALSE，由此建立一个有逻辑值构成的内存数组。

上文已经说过，SUMPRODUCT有一个特性，它会将非数值型的数组元素作为0处理，逻辑值自然是属于非数值型的数组元素，为了避免SUMPRODUCT函数把逻辑值视为0，造成统计错误，我们使用*1的方式，把逻辑值转化为数值，TRUE转化为1，FALSE转化为0，最后统计求和。

2

员工西门庆领取了多少工资?

这是一个单条件求和的问题,通常我们使用SUMIF函数,如果使用SUMPRODUCT函数，我们可以写成这样：

=SUMPRODUCT((C2:C13=”西门庆”)*D2:D13)

依然首先判断C2:C13的值是否等于”西门庆”，得到逻辑值FALSE或TRUE，再和D2:D13的值对应相乘。TRUE乘以数值，得到数值本身。FALSE乘以数值返回0。最后统计求和得出结果。

看完了上面两个问题，有些朋友可能会在心里想，貌似SUMPRODUCT能干的事，SUMIF和COUNTIF也能做到，而且做的更好，那么还要SUMPRODUCT干啥嘞?

乡亲们呐，话不能这么说，SUMPRODUCT可是上得厅堂下得厨房，对工作环境不挑不拣，它对参数类型没有啥特别要求，COUNTIF和SUMIF就不同了，他俩要求个别参数，必须是区域(Range型)，不支持数组，比如下面这两个问题，COUNTIF和SUMIF就要绕了。

3

二月份外交部发放了几次工资?总额是多少?

第1个问题，二月份外交部发放了几次工资?

这是一个多条件计数的问题。

第一个条件，发放工资的时间必须是二月份;第二个条件，发放工资的部门必须是外交部。

如果使用多条件计数函数COUNTIFS，判断发放工资的时间是否属于六月份，会简单问题复杂化。而使用SUMPRODUCT函数，咱们可以把公式写成这样：

=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13=”外交部”))

第2个问题，统计二月份外交部发放了多少工资?

这是一个常见的多条件求和问题。

如果使用SUMIFS函数，判断发放工资的时间是否属于六月份，也会简单问题复杂化。

SUMPRODUCT跃然而至：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”),D2:D13)

或者：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”)*D2:D13)

打个响指，关于这两个形式的SUMPRODUCT函数的区别，咱们上文已有详细说明——你还记得吗?

上面这个公式可以说是SUMPRODUCT多条件求和的典型用法啦，可以归纳为:

=SUMPRODUCT((条件一)*(条件二)……,求和区域)

4

二月份外交部和步兵部合计发放了多少工资?

解决了上面的问题，相信大家已经晓得如何计算二月份外交部发放多少工资了，那么二月份外交部和步兵部合计发了多少工资，又当怎么计算呢

我们经常见有些性格朴素的表亲们把公式写成这样：

代码如看不全，可以左右拖动..▼

=SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13=”外交部”)*D2:D13)+SUMPRODUCT((MONTH(A2:A13)=2)*(B2:B13=”步兵部”)*D2:D13)

这些表亲们估计心想，不就是计算两个部门吗?甭说两个，二十个咱也能算，一个加一个，一直加到二十个，世上无难事，只怕有心人嘛，一砖加一砖，长城咱也能垒成喽……

公式写的那么长，先不谈计算速度，首先它累手啊，万一写错了，又要修改，那也是麻烦他妈哭麻烦——麻烦死了。

其实我们可以写成这样：

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13={“财务部”,”市场部”})*D2:D13)

5

排名应用

认识了SUMPRODUCT函数在条件计数和求和方面的用法，最后，咱们再来看一个它在排名上的使用方法。

" 如上图所示，某个月某个公司某些人领了某些工资，然后呢，他们想看看自己的工资，在部门内的排名情况，比如说步兵部的鲁智深都是老员工了，非常想知道自个工资在各自部门排几号。

当然啦，不排不知道，一排就傻掉。

SUMPRODUCT是这么解决这个问题的，D2输入公式向下复制：

=SUMPRODUCT(($A$2:$A$9=A2)*(C2<$C$2:$C$9))+1

(思考，为什么公式的最后+1，而不是直接写成如下：)

=SUMPRODUCT(($A$2:$A$9=A2)*(C2<=$C$2:$C$9))

结束语

唠唠叨叨说了这么多，眼睛都说酸麻了，是到了该结束的时候啦。

最后，请思考两个小问题：

第一个问题：下面SUMPRODUCT函数有几个参数?

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”)*D2:D13)

下面这个SUMPRODUCT函数又有几个参数?

=SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13=”财务部”)，D2:D13)

第二个问题：

SUMPRODUCT为什么有时候比SUMIF/COUNTIF计算速度慢?

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们zhicunshuangzi@foxmail.com 处理，核实后本网站将在24小时内删除侵权内容。