1万多条记录,算了2天才做完500条记录,求助多位老师都解决不了的加权平均价难题
某粉丝的求助。
看完第1段话,以为什么大难题。看完第2段话,觉得稍微有点难,加权平均价在某些情况下也挺难算的。
看完表格以及手工的计算方法,觉得这些所谓的“老师”水平真的很一般。
表格内容非常多,为了方便说明,我就只保留一小部分内容,其他都删除掉。
这里我选里面最短的一条公式,也就是说加权平均价就是当前商品的单价*数量的和,再除以总数量。
=(B19*C19+B20*C20+B21*C21)/(C19+C20+C21)
这种问题也太简单了吧,白白浪费了2天时间,分分钟搞定。
相乘后求和,可以用SUMPRODUCT函数。
=SUMPRODUCT(B19:B21*C19:C21)
对数量求和可以用SUM函数,两者结合起来就是加权平均价。
=SUMPRODUCT(B19:B21*C19:C21)/SUM(C19:C21)
这种是针对一个商品,而实际上有很多商品,也就是按条件统计,再增加一个条件就行。
=SUMPRODUCT(($A$2:$A$21=A2)*$B$2:$B$21*$C$2:$C$21)/SUMIF(A:A,A2,C:C)
大方向已经出来了,再处理一下小细节。
1)让空单元格显示空白。
=IF(A2="","",SUMPRODUCT(($A$2:$A$21=A2)*$B$2:$B$21*$C$2:$C$21)/SUMIF(A:A,A2,C:C))
2)让非首次出现的商品显示空白。
这种一般情况下是用COUNTIF函数判断是否为第一次出现。不过因为相同商品都在一起,只要判断当前所在单元格不等于上一个单元格,就是第一次出现的。
=IF(A2="","",IF(A2<>A1,SUMPRODUCT(($A$2:$A$21=A2)*$B$2:$B$21*$C$2:$C$21)/SUMIF(A:A,A2,C:C),""))
3)让加权平均价保留2位小数点。
这个可以直接设置单元格格式,或者嵌套ROUND函数。
=IF(A2="","",IF(A2<>A1,ROUND(SUMPRODUCT(($A$2:$A$21=A2)*$B$2:$B$21*$C$2:$C$21)/SUMIF(A:A,A2,C:C),2),""))
多一次学习,就少一次求助他人。万一运气不好,遇到水平差的“老师”,那就更惨,又浪费时间又浪费精力,最后还得欠别人的人情。
提取码:er0x
卢子每天的工作就是解答学员的问题,并整理成文章,学员的问题源源不断,文章也就源源不断。
你想跟着卢子一起学习,让自己的Excel水平更上一层楼吗?
1)清华大学畅销书作者,超过十年的实战经验。
2)有10名答疑老师,学习或者工作上的问题都可以群内提问,都能及时被解决。
3)视频都可以长期学习,不受时间限制。
学习方法:采用视频教学+微信在线答疑的形式。
报名费用:268元。
报名福利:每人赠送一本Excel不加班书籍。
报名以后,加卢子微信chenxilu2019,发送报名截图,邀请进群。
你体验过遇到难题,想求助别人,又不知道求助谁的感受吗?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)