投稿
  1. 小新网首页
  2. 投稿

sumproduct函数多条件求和用法讲解

运维笔记ywbj • • 投稿

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

一、功能及语法结构。

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

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

解读：

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

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

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

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

二、应用案例。

1、计算总销售额。

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

方法：

在目标单元格中输入公式：=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、计算总销售额。

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

从结果中可以看出，返回了错误值，Why？

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

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

综上所述，敲一下黑板：

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

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

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

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

4、单条件计数。

如下图：

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

从示例图中可以看出，公式为：=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函数也可以实现。

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

方法：

在目标单元格中输入公式：=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函数。

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

公式为：=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次，手累就不谈了，万一写错，………………是不是很麻烦？看小编是如何处理的？

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

求和公式为：=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、排序。

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

精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？

 

方法：

在目标单元格中输入公式：=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))？欢迎在留言区留言讨论哦！

本文来自作者:运维笔记ywbj，不代表小新网立场！

转载请注明：https://www.xiaoxinys.cn/103567.html

赞 (0)
运维笔记ywbj运维笔记ywbj注册用户
0
上一篇 2022-07-06 18:06:00
下一篇 2022-07-06 18:25:00

相关推荐

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。