3g|精通Sumproduct函数，可以打下Excel的半壁江山，你会吗？ excel|销售额

文章图片

文章图片

文章图片

文章图片

文章图片

文章图片

文章图片

今天给大家分享一个很常用、很实用、很强大、也很有意思的函数， Sumproduct 。众所周知，条件计数和求和是数据的统计和分析中经常要用到的2种技巧，而Sumproduct函数不但集合了条件计数、求和功能，还可以用于复杂情景下的排名处理等情景，所以有人说，精通Sumproduct函数，就可以打下Excel的半壁江山。
一、功能及语法结构。
功能：返回相应的数组或区域乘积的和。
语法结构：=Sumproduct(数组1[数组2
[数组3
……) 。
解读：
1、我们可以将函数名称Sumproduct分为2部分， Sum和Product ， Sum是求和的意思， Product是乘积的意思，参数之间先乘积再求和。
2、从语法结构可以看出， Sumproduct函数本身默认执行数组运算。
3、Sumproduct函数会将参数中非数值类型的数组元素作为0处理。
4、各参数的维度必须相同，否则会返回错误值。
二、应用案例。
1、计算总销售额。

方法：
在目标单元格中输入公式：=SUMPRODUCT(D3:D12E3:E12) 。
解读：
1、如上图，要计算商品的总销售额，一般思路为首先计算出每个商品的销售额，然后求和。也就是需要2步来完成。
2、而Sumproduct函数将上述的两个步骤合二为一，公式=SUMPRODUCT(D3:D12E3:E12)的计算过程为：D3*E3+D4*E4……D12*E12 ，也就是每个数组对应的元素相乘，再求和。
3、部分亲可能也遇到过将公式写成：=SUMPRODUCT(D3:D12*E3:E12)的情况，同样能够得到正确的结果。 Why？我们接着往下学习。
2、计算总销售额。
在计算之前，我们先将数据表稍作改动，如下图：

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

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

结果没有任何问题，是正确的。
原因在于Sumproduct函数会将参数中非数值类型的数组元素作为0处理。
综上所述，敲一下黑板：
当Sumproduct函数的参数为数值类型时，公式=Sumproduct(数组1[数组2
[数组3
……)或=Sumproduct(数组1*[数组2
*[数组3
……)均可用于计算；而参数中含有非数值类型时，只能用=Sumproduct(数组1[数组2
[数组3
……)进行计算。
3、参数的维度必须相同。
目的：计算商品的总销售额。
在目标单元格中输入公式：=SUMPRODUCT(D3:D12E3:E10) ，结果如下图：

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

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

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