學不完．教不停．用不盡

網友問到一個 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","")

複製儲存格G2，貼至儲存格G2:G13。

 

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)

找出最後一個區間的比重。