Excel中的“万能函数”，解决工作中80％的常见问题

今天要给大家介绍下Excel中的“万能函数”sumproduct函数，为什么说他是万能的呢，因为它能做的事情是实在多了，废话不多说我们开始把

sumproduct函数以及参数

sumproduct函数：返回相应的数组或区域乘积的和

第一参数：Array1

第二参数：array2

第三参数：array3,

…….最多255个Array

Array：代表数组或者单元格区域

需要注意的是:使用sumproduct函数，参数中的元素数必须相等，比如第一个区域选择了6个单元格，第二个区域也必须选择6个单元格，否则会返回错误值

参数图如下

参数这么单一，具体怎么用呢，让我来实际操作下，如下图要求总销售额

Sumproduct函数中的参数分别是单价列和售出列，我们可以看作是对应元素相乘之后再求和。

我们可以这样理解先用苹果单价与售出相乘得到苹果销售额，然后用橘子的单价与售出相乘然后再得到橘子销售额，以此类推，当所有水果都得到总销售额后，再相加求和。这是sumproduct函数最基础，下面让我get一些sumproduct函数的高级用法把

1.单条件计数

公式：=SUMPRODUCT((B2:B25=$G$3)*1))

函数中部门列等于“成型车间”就等于部门列中的每一个元素与成型车间判断一次，看其结果是否等于成型车间，一共计算部门列元素个数次，比如部门列一共25人，就判断25次，如果等于成型车间就返回TRUE，如果不等于就返回FALSE，，TRUE可以看做等于1，FALSE可以看做等于0，最后又乘以1，1*1=1，1*0=0，只有当返回结果为TRUE时才等于1，然后在相加得到结果，下图便是函数的运算方式，

2. 多条件计数

要求为成型车间员工且等级为2级的人数

公式：=SUMPRODUCT((B2:B25=$G$3)*1,(C2:C25=2)*1)

多条计数与单条件求和十分相似，只添加了一个条件，我们还是把计算结果列出来便于理解

首先判断部门列是否有等于成型车间的元素，然后判断等级列是否有等于成型车间的元素，然后将其结果乘以1等到数值，最后两组数组的结果相乘，然后求和

3.  排序

公式：=SUMPRODUCT(($B$2:$B$13>B2)*1)+1

这里其实就是一个单条件计数，不过最后为结果加1罢了

4. 单条件求和

公式：=SUMPRODUCT((C3:C19=G4)*1,E3:E19)

先用条件判断成型车间人数，然后成绩薪资列，进而求和，其实无非就是单条件计数后加入薪资列

5. 多条件求和

公式：=SUMPRODUCT((B2:B25=$G$3)*1,(C2:C25=2)*1,D2:D25)

与多条件计数十分相似，无非就是在其后加入薪资列用于求和如果你还是不太明白下图从左到右为其计算关系相信一看就明白了

6.隔行求和

如下图我们想要求第一季度各个仓库出库与入库的总和，这样的问题我们也可以使用sumproduct函数来完成

出库公式：=SUMPRODUCT(($B$2:$G$2=$H$2)*(B3:G3))

入库公式：=SUMPRODUCT(($B$2:$G$2=$I$2)*(B3:G3))

我们只需要输入对应的公式向下填充即可，这个本质上其实也是SUMPRODUCT函数多条件查询的一个应用

7.统计不重复数据的个数

如下图，在这里我们想要统计1班的人数，对于这样的的问题其实就是计算姓名不重复的个数，我们只需要将公式设置为=SUMPRODUCT(1/(COUNTIF(B2:B26,B2:B26)))，即可得到不重复的个数，也就是1班的人数

在这里COUNTIF(B2:B26,B2:B26)他是一个数组公式，比如在这里我们假设鲁班出现了三次，那么这个公式就会得到三个3，然后我们用1除以这个结果就是得到3个三分之一，最后SUMPRODUCT会将这3个三分之一相加它的结果就是1，这样的话我们就能保证每个人的结果都是1以此来达到求不重复的效果

8.一维表格转二维表格

首先我们先来了解下什么是一维表格，什么是二维表格，简单来说对于一维表格我们只要看一个维度就能够明白数据表达的是什么意思，而对于二维表格我们需要看两个维度才能明白数据表达的是什么意思，他们各有优点，一维表格更加适合函数运算，二维表格会减少数据所占单元格的个数，如下图我们将一维表格转二维表格只需要输入公式

=SUMPRODUCT(($A$3:$A$22=$F3)*($B$3:$B$22=G$2)*($C$3:$C$22))

向右拖动，向下填充即可，这个本质上也是一个多条件查询，之前跟大家讲解过原理，就不多做介绍了

以上就是SUMPRODUCT函数的9种用法，你知道几个呢？

我是excel从零到一，关注我持续分享更多excel技巧

觉得有用，麻烦点个“在看”吧