2016年8月31日 星期三

Excel-計算累進的乘積和(SUMPRODUCT,OFFSET)

網友問到一個 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。
Excel-計算累進的乘積和(SUMPRODUCT,OFFSET)
【公式設計與解析】
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)
找出最後一個區間的比重。

沒有留言:

張貼留言

好康東東