很多小伙伴都以为Excel函数中的sumproduct函数只可以用来求和,其实不然。
sumproduct函数可以用来做什么?
数组相乘再求和、单条件/多条件计数、单条件/多条件求和、模糊求和、隔列求和、多个工作表指定求和、多条件去重计数等这些都可以用这个函数搞定,如果你只知道可以用来求和,说明这个函数你只了解到一点点皮毛知识!
![5ee843b613a9a896ff2daccef35cfe2f.png](https://i-blog.csdnimg.cn/blog_migrate/048f54ba8319b63c6c21a437f8433d93.jpeg)
下面给大家详细讲解这个函数的各种用法,如果小伙伴们在学习这个函数的过程中遇到了不明白的地方,记得在评论区留言。如果觉得本篇文章对您有帮助,记得点赞和转发,小编会努力写出更多对大家有帮助的函数教程哦~
一、数组相乘再求和。
sumproduct函数的功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和——也就是计算两个以上的数组乘积之和。
要求:求出下面表格中B数组与C数组对应元素相乘之后的和。
步骤:在F7单元格中输入公式“=SUMPRODUCT(B5:B9,C5:C9)”,按回车键即可。
![21fac5f7061c0fe384d2326847880884.png](https://i-blog.csdnimg.cn/blog_migrate/fc0d389a374be07a27dd902a27dddcd3.jpeg)
以上的公式等于:=SUMPRODUCT(B5*C5+B6*C6+B7*C7+B8*C8+B9*C9).
二、单条件计数。
语法:=SUMPRODUCT(条件)。
要求:求出下面表格中性别为“男”的员工有多少人。
步骤:在G7单元格中输入公式“=SUMPRODUCT(N(C4:C11="男"))”,按回车键即可。
![29022640d83c750d15ba8a281046d183.png](https://i-blog.csdnimg.cn/blog_migrate/c5fc69cf5d1318001127a522b35e6346.jpeg)
以上公式用到N函数,该函数有个特点就是可以把TRUE转换成1,把FALSE转换为0,把文本转换为0。所以N(C4:C11="男")的意思是将等于“男”的值TRUE返回1,不等于“男”的值FALSE返回0。
三、多条件计数。
语法:=SUMPRODUCT((条件1)*(条件2)*(条件3)*…*(条件n))。
要求:求出下面表格中性别为“男”并且部门为“技术部”的员工人数。
步骤:在G7单元格中输入公式“=SUMPRODUCT((C4:C11=G5)*(D4:D11=H5))”,按回车键即可。
![cad7c0c67ce85e6ab8b875e8d4b9ddac.png](https://i-blog.csdnimg.cn/blog_migrate/3b5f02da2d67ca501dfc9fadd74bd0b4.jpeg)
以上的公式也可以直接写成:=SUMPRODUCT((C4:C11="男")*(D4:D11="技术部"))。
四、单条件求和。
语法:=SUMPRODUCT((条件)*求和区域)
要求:求出下面表格中性别为“女”的业绩之和。
步骤:在G8单元格中输入公式“=SUMPRODUCT((C4:C11="女")*D4:D11)”,按回车键即可。
![8c4f2ecc4b7b1cc03736d9490b18de0a.png](https://i-blog.csdnimg.cn/blog_migrate/2d50d4dfd8e119bc319a5d066f19499f.jpeg)
【公式解析】上述公式的意思是:将C4:C11内的每个单元格的值与“女”比较,如果相等,返回TRUE,否则,返回FALSE。(C4:C11="女"):结果返回的是一组逻辑值{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE},逻辑值TRUE对应的单元格的值分别为{79;86;249;83;76},相加起来即可得到G8单元格的结果573。
五、多条件求和。
语法:=sumproduct((条件1)*(条件2)*…*(条件n),求和区域)。
要求:求出下面表格中性别为女并且业绩大于80的和。
步骤:在G8单元格中输入公式“=SUMPRODUCT((C4:C11="女")*(D4:D11>80),D4:D11)”,按回车键即可。
![456c0927d49e6630a20ad01b55408acd.png](https://i-blog.csdnimg.cn/blog_migrate/e0c061cd938b5c0668764646372d5327.jpeg)
【公式解析】(C4:C11="女"):先将C4:C11内的每个单元格的值与“女”比较,如果相等,返回TRUE,否则,返回FALSE。(D4:D11>80):再将D4:D11内的每个单元格的值是否大于80,如果是,返回TRUE,否则,返回FALSE 。(C4:C11="女")*(D4:D11>80):结果返回一组逻辑值{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}。再与D4:D11内的每一个单元格对应的值相乘,返回结果{0;0;86;0;0;249;83;0},最后再将这个数组里面的值相加即可得到G8单元格的值418。
六、模糊求和。
要求:求出下面表格中性别为“女”并且部门为销售部的业绩之和。
步骤:在C13单元格中输入公式“=SUMPRODUCT(ISNUMBER(FIND("销售