我们在处理日常工作的时候,函数是一个不可缺少的部分,Excel中除了有vlookup等万能查询函数,还有我们必须要熟悉的sumproduct函数,它可以实现求和、单一多条件和复杂情况下的各类计数及综合排名等数据处理,今天我们就来学习一下这个函数的全部9种用法。

用法1:简单数组求和

product函数(excel技巧中sum product使用方法)(1)

案例:求出所有人员最终的补贴之和

函数1=SUMPRODUCT(D3:D8,E3:E8)

函数2=SUMPRODUCT(D3:D8*E3:E8)

案例讲解:sumproduct函数将相应元素之间通过相乘并求和计算,可以用逗号或是用*号进行连接,返回相应的数组或区域乘积的和。数组设计的区域必须是相同的,如基础补贴区域为:D3:D8,难度系数选择的范围也是3-8。这里实现的效果也可以是将每一个值相乘后再相加,结果都是一样的5960。

用法2:别具一格的单一条件计数

product函数(excel技巧中sum product使用方法)(2)

案例:求出男女人数

男=SUMPRODUCT(N($D$3:$D$8=H5))

女=SUMPRODUCT(N($D$3:$D$8=H6))

案例讲解:在计数的时候我们在中间使用了N函数,这个函数代表将True的值转化为1,将False的值转换为0,最后sumproduct函数将所有符合条件的值进行求和。我们可以选择N($D$3:$D$8=H5)函数之后,按F9进行函数解析为:SUMPRODUCT({1;0;1;1;0;1})。

用法3:比sumifs更简单的多条件数据求和

product函数(excel技巧中sum product使用方法)(3)

案例:求出男员工中难度系数在1以上的总的工作完成度。

函数=SUMPRODUCT(($D$4:$D$9="男")*($F$4:$F$9>1)*($E$4:$E$9))

函数解析:在多条件求和中,我们的操作方法跟用法1一致,将多个条件用*进行连接即可实现。

用法4:比countifs看起来更舒服的多条件计数

product函数(excel技巧中sum product使用方法)(4)

案例:求工作完成度大于5的男员工人数。

函数=SUMPRODUCT(($D$4:$D$9="男")*($E$4:$E$9>5))

案例讲解:操作方法同用法3,唯一的不同是后面没有再*数值,所以我们最终的结果只是将符合条件的个数进行求和。选择($D$4:$D$9="男")*($E$4:$E$9>5),按F9最终的结果会解析为如下:

product函数(excel技巧中sum product使用方法)(5)

用法5:不一样的综合多参数综合权重排名

product函数(excel技巧中sum product使用方法)(6)

案例:将人员按照工作完成度、执行力、满意度等不同维度占比进行综合排名。

函数=SUMPRODUCT($D$3:$F$3,D4:F4)

案例讲解:首先通过用sumproduct函数进行综合得分的计算,最后用RANK函数进行最终的数据排名。

用法6:不同条件下的跨列数据求和

product函数(excel技巧中sum product使用方法)(7)

案例:计算第一季度每个人的目标值及最终完成值。

目标=SUMPRODUCT((D$3:I$3=$J$3)*($D4:$I4))

实际=SUMPRODUCT((D$3:I$3=$K$3)*($D4:$I4))

案例讲解:在多条件求和的情况下,这个函数会比sumifs来的更加简单。

用法7:最快速度的数据拆分展示

product函数(excel技巧中sum product使用方法)(8)

案例:将左边按竖排展示的数据最快转换为右边的多维数据展示

函数=SUMPRODUCT(($B$3:$B$14=$G3)*($C$3:$C$14=H$2)*($D$3:$D$14))

案例讲解:这个方法与多条件数据求和方法一样,利用好相对引用和绝对引用就可以实现数据的最快速度转化显示。

用法8:求出销量排名前三产品的总销售量

product函数(excel技巧中sum product使用方法)(9)

案例:求出A-G产品中销量排名前三的总销售量

函数=SUMPRODUCT(LARGE($C$4:$C$10,ROW(1:3)))

案例解析:在这里我们使用了一个返回值的函数LARGE函数,他的作用在于可以返回区域中排名多少位的值。我们通过用ROW来返回1-3对应的数值,加上sumproduct函数的数组计算的特点,这样就可以实现排名前多少对应的总量。

用法9:求出今天仓库总共出库了多少种产品

product函数(excel技巧中sum product使用方法)(10)

函数=SUMPRODUCT(1/COUNTIF($C$3:$C$14,$C$3:$C$14))

案例讲解:在这里我们主要通过用countif函数计算出每种产品出现了多少次,再用1/countif,这样可以将出现的多的次数进行分解,选择countif函数按F9可以得到如下:

product函数(excel技巧中sum product使用方法)(11)

所以最后用1来除的时候,就可以将出现2次的改为2个1/2,出现3次的改为3个1/3。最后求和即可。

现在你学会如何使用sumproduct函数了吗?