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

複製儲存格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)

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

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()