作者:乔山办公网日期:
返回目录:excel表格制作
Excel问答,是对老徐漫谈粉丝们提出的优秀问题进行的答疑解惑。希望能帮助到每一个看到此文的读者。
更多Excel问答文章请关注老徐漫谈头条号。
粉丝问题
如何计算累进的乘积和。参考下图,其中有一个数值区间和比重,如果在单元格H2输入一个数值,例如:750,而此数可以分解为:
750=100+100+200+200+100+50
再将每个区间的数量乘以比重:
100*6%+100*6%+200*3%+200*2%+100*5%+50*1%=27.5
输入:750→输出:27.5。
【公式设计与解析】
1. 本例需要二个辅助字段。
第一个字段(F栏),内容是每个区间的范围量。
第二个字段(G栏),找出那些区间要被并入计算,给予「V」记号。(不包含最后一个区间)
单元格G2:=IF($I$2>SUM($F$2:F2),"V","")
2. 计算输出结果:
单元格
I6=SUMPRODUCT(F2:F13*D2:D13*(G2:G13="V"))+(I2-SUMPRODUCT(F2:F13*G2:G13="V")))*OFFSET(D2,COUNTIF(G2:G13,"V"),0)
(1) SUMPRODUCT(F2:F13*D2:D13*(G2:G13="V"))
计算有「记号」的范围数值和比重的乘积和。
(2) I2-SUMPRODUCT(F2:F13*(G2:G13="V"))
计算最后一个区间的数值。(本例中为 700~800 之间的数值为 50)
(3) OFFSET(D2,COUNTIF(G2:G13,"V"),0)
找出最后一个区间的比重。