excel中sum用法大全(可以打下Excel的半壁江山)(1)

今天给大家分享一个很常用、很实用、很强大、也很有意思的函数,Sumproduct。众所周知,条件计数和求和是数据的统计和分析中经常要用到的2种技巧,而Sumproduct函数不但集合了条件计数、求和功能,还可以用于复杂情景下的排名处理等情景,所以有人说,精通Sumproduct函数,就可以打下Excel的半壁江山。


一、功能及语法结构。

功能:返回相应的数组或区域乘积的和。

语法结构:=Sumproduct(数组1,[数组2],[数组3]……)。

解读:

1、我们可以将函数名称Sumproduct分为2部分,Sum和Product,Sum是求和的意思,Product是乘积的意思,参数之间先乘积再求和。

2、从语法结构可以看出,Sumproduct函数本身默认执行数组运算。

3、Sumproduct函数会将参数中非数值类型的数组元素作为0处理。

4、各参数的维度必须相同,否则会返回错误值。


二、应用案例。

1、计算总销售额。

excel中sum用法大全(可以打下Excel的半壁江山)(2)

方法:

在目标单元格中输入公式:=SUMPRODUCT(D3:D12,E3:E12)。

解读:

1、如上图,要计算商品的总销售额,一般思路为首先计算出每个商品的销售额,然后求和。也就是需要2步来完成。

2、而Sumproduct函数将上述的两个步骤合二为一,公式=SUMPRODUCT(D3:D12,E3:E12)的计算过程为:D3*E3 D4*E4……D12*E12,也就是每个数组对应的元素相乘,再求和。

3、部分亲可能也遇到过将公式写成:=SUMPRODUCT(D3:D12*E3:E12)的情况,同样能够得到正确的结果。Why?我们接着往下学习。


2、计算总销售额。

在计算之前,我们先将数据表稍作改动,如下图:

excel中sum用法大全(可以打下Excel的半壁江山)(3)

从图中可以看出,商品“打印机”的销量“待统计”,此时用公式:=SUMPRODUCT(D3:D12*E3:E12)来计算,结果如下图:

excel中sum用法大全(可以打下Excel的半壁江山)(4)

从结果中可以看出,返回了错误值,Why?

分析数据源,不难发现,原因在于“打印机”的“销量”为“待统计”,为文本值,而文本值是无法直接参与数据运算的,所以D5*E5返回错误值,进而导致整个公式返回错误值。但如果将公式修改为:=SUMPRODUCT(D3:D12,E3:E12),结果如下图:

excel中sum用法大全(可以打下Excel的半壁江山)(5)

结果没有任何问题,是正确的。

原因在于Sumproduct函数会将参数中非数值类型的数组元素作为0处理。

综上所述,敲一下黑板

当Sumproduct函数的参数为数值类型时,公式=Sumproduct(数组1,[数组2],[数组3]……)或=Sumproduct(数组1*[数组2]*[数组3]……)均可用于计算;而参数中含有非数值类型时,只能用=Sumproduct(数组1,[数组2],[数组3]……)进行计算。


3、参数的维度必须相同。

目的:计算商品的总销售额。

在目标单元格中输入公式:=SUMPRODUCT(D3:D12,E3:E10),结果如下图:

excel中sum用法大全(可以打下Excel的半壁江山)(6)

结果返回错误值,从公式中不难发现,公式中的两个元素的维度不同,E3:E10明显比D3:D12少了2个元素,D3和E3结对,D4和E4结对……D11以及D12和谁结对呢?一个萝卜一个坑,D11和D12有萝卜没坑,所以就返回了错误值。


4、单条件计数。

如下图:

excel中sum用法大全(可以打下Excel的半壁江山)(7)

现在要计算员工中相应“学历”的人数,如果用Countif函数计算,公式为:=COUNTIF(F3:F24,J3),但如果用Sumproduct去计算,怎么操作呢?

excel中sum用法大全(可以打下Excel的半壁江山)(8)

从示例图中可以看出,公式为:=SUMPRODUCT((F3:F24=J3)*1);先判断F3:F24=J3是否成立,如果成立,返回TRUE,否则返回FALSE,由此建立一个由逻辑值组成的数组,前文中已经讲过,Sumproduct函数会将非数值型的数组元素作为0处理,逻辑值自然属于非数值型的数组元素,为了避免Sumproduct函数将逻辑值视为0,造成统计错误,所以乘以辅助值1,把逻辑值转换为数值类型的值,最后统计求和,得到计数结果。


5、单条件求和。

目的:统计相应“学历”人员的总“月薪”。

单条件求和,应该是Sumif函数的本职工作,公式为:=SUMIF(F3:F24,J3,G3:G24),其实除了用Sumif函数之外,用Sumproduct函数也可以实现。

excel中sum用法大全(可以打下Excel的半壁江山)(9)

方法:

在目标单元格中输入公式:=SUMPRODUCT((F3:F24=J3)*G3:G24)。

解读:

1、首先判断F3:F24=J3是否成立,建立一个由逻辑值组成的数组,然后和G3:G24元素中相应的值乘积,再求和。

2、如果此处将公式更改为:=SUMPRODUCT(F3:F24=J3,G3:G24),能否得到想要的结果呢?大家可以思考一下,将自己的意见发表到留言区或者私信和小编讨论交流哦!


6、多条件计数、求和。

目的:按“性别”统计相应“学历”的人数和总“月薪”。

按“性别”统计相应“学历”的人数,这是一个多条件计数的问题,如果用Countifs函数去计算,公式为:=COUNTIFS(D3:D24,J3,F3:F24,K3),参数相对来说较多,有点儿简单问题复杂化的感觉,所以除了用Countifs函数外,还可以用Sumproduct函数。

excel中sum用法大全(可以打下Excel的半壁江山)(10)

公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))。

除了计数之外,还要按“性别”统计相应“学历”的总“月薪”,就属于求和的问题,而且是多条件求和,公式为:=SUMIFS(G3:G24,D3:D24,J3,F3:F24,K3);也有点儿简单问题复杂化,如果用Sumproduct函数来实现:

excel中sum用法大全(可以打下Excel的半壁江山)(11)

公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)。

也可以是:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24),Why?留言区或私信告诉小编哦!

应用拓展:

如果老板要知道“男”同志或“女”同志“大本”、“大专”、“职高”的总人数和总“月薪”,该如何操作呢?

通过上文的学习,相信大家已经能够解决这个问题,就是将公式进行叠加。

计数公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)) SUMPRODUCT((D3:D24=J3)*(F3:F24=K4)) SUMPRODUCT((D3:D24=J3)*(F3:F24=K5))。

求和公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24) SUMPRODUCT((D3:D24=J3)*(F3:F24=K4)*G3:G24) SUMPRODUCT((D3:D24=J3)*(F3:F24=K5)*G3:G24)

貌似没有任何问题,但仔细分析,如果有20个学历等次或其他等次,公式要重复20次,手累就不谈了,万一写错,………………是不是很麻烦?看小编是如何处理的?

excel中sum用法大全(可以打下Excel的半壁江山)(12)

计数公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24={"大本","大专","职高"}))。

excel中sum用法大全(可以打下Excel的半壁江山)(13)

求和公式为:=SUMPRODUCT((D3:D24=J3)*(F3:F24={"大本","大专","职高"})*G3:G24)。

是不是清晰了很多,只需要给大括号中添加条件即可。

解读:

1、多条件计数时,能够将公式:=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))更改为:=SUMPRODUCT((D3:D24=J3),(F3:F24=K3))?

2、多条件求和时,为什么=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)和=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24)均可使用?


7、排序。

目的:对相应“学历”下的“月薪”进行排序。

excel中sum用法大全(可以打下Excel的半壁江山)(14)

方法:

在目标单元格中输入公式:=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3)) 1。

思考

1、公式有几个参数?

2、为什么 1,而不是直接写成:=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3))?欢迎在留言区留言讨论哦!


结束语:

说了很多,都是关于Sumproduct的实际应用,可以计数、求和,还不止,计数中还可以实现单条件计数、多条件计数;求和中可以实现单条件求和、多条件求和;除此之外,还可以排序,当然了,排序不仅仅是对数值的排序(怎么排???),还可以附加多个条件进行排序,覆盖了Sum、Sumif、Sumifs、Countif、Countifs、Rank等函数的功能,难道还不够强大?


,