工作中经常会遇到下面这种情况,需要计算加权平均数。
已知图中的两种水果,现在需要计算葡萄和西瓜分别的加权平均的单价。
普通的计算方法大家肯定知道,以葡萄为例:
(B2*C2+B3*C3)/(B2+B3) = 22.86
如果针对多个货号,都按照这样的公式计算,就会显得效率很低下。
这时候EXCEL的公式就要派上用场了。
假设我将求加权平均数的位置放在后面:
第一步,针对前面汇总求和部分,我们要用SUMPRODUCT,具体公式为:
=SUMPRODUCT(($A$2:$A$6=E2)*$B$2:$B$6*$C$2:$C$6)
公式解析:假设总的行数为6行,那么总的水果数据为A2:A6,此处的$$符号表示绝对引用符号。我会在之后的文章中详细解释。
$A$2:$A$6=E2,表示的是E2在A2:A6里面寻找等于E2的值,即我在A2:A6内寻找葡萄。
后面第一个*大家不用理解成乘号,可以记成一个关联符号。
后面的部分$B$2:$B$6*$C$2:$C$6,其实简单看就是重量和单价的乘积了。
最终我们可以把这个公式理解成,我现在A2到A6的范围里面寻找E2的值,每找到一个值,就把后面对应的B列和C列的单元格的值相乘,最后通过SUMPRODUCT的公式将这些乘积汇总。
所以通过公式,我们可以求出第一步总的金额。
第二步,我们需要求出后面重量的汇总。我们可以继续用SUMPRODUCT的公式,也可以换成SUMIF的公式。
如果用SUMIF的公式,则具体公式为:=SUMIF(A2:A6,E2,B2:B6)
公式解析:SUMIF(A,B,C)分成三部分,A表示我寻找字段的范围,B表示我要求和的具体字段,C表示寻找字段对应的值的范围。
在我们的例子里面,就可以理解成,A表示葡萄所在的范围;B表示我要找的是葡萄,而不是西瓜,C表示葡萄对应的重量所在的范围。
所以通过第二个公式,我们把重量的总和也求出来了。
最后合并两个公式,得到总的公式为:=SUMPRODUCT(($A$2:$A$6=E2)*$B$2:$B$6*$C$2:$C$6)/SUMIF($A$2:$A$6,E2,$B$2:$B$6)。
是不是很简单呢?
留两个小问题给大家思考一下:为什么有些地方需要用绝对应用,有些地方不需要加。
如果重量总和的公式用SUMPRODUCT写,应该怎么写?
如果觉得《加权平均数的例子_EXCEL 加权平均数的计算》对你有帮助,请点赞、收藏,并留下你的观点哦!