2016年2月1日 星期一

Excel-將一個數值分配給多個項目,且總和與原數值相同(INT,MOD,ROW)

有網友問到一個 Excel 的問題:如果要將一個數值分配給指定的項目數,如何設計公式能自動分配,並使其總和與原數值相同?
參考下圖中的例子,數值24833要分配給7個項目,其中有幾個會比其他數值多 1。分配後的總和與原數值相同。
Excel-將一個數值分配給多個項目,且總和與原數值相同(INT,MOD,ROW)
(1)
儲存格A4:=ROW(1:1)
儲存格B4:=INT($B$1/$D$1)+(ROW(1:1)<=MOD($B$1,$D$1))
INT($B$1/$D$1):取得『數值/分配數』的商數。
ROW(1:1)<=MOD($B$1,$D$1):判斷是否要『加 1』。該判斷結果和上式相加時,若判斷結果若為 FALSE,則視為 0,若判斷結果若為 TRUE,則視為 1。
複製儲存格A4:B4,貼至儲存格A4:B19。

(2)
儲存格D4:=IF(ROW(1:1)<=$D$1,ROW(1:1),"")
儲存格E4:
=IF(ROW(1:1)<=$D$1,INT($B$1/$D$1)+(ROW(1:1)<=MOD($B$1,$D$1)),"")
判斷項目個數如果超過分配數,則儲存格顯示空白。
複製儲存格D4:E4,貼至儲存格D4:E19。

沒有留言:

張貼留言

檢視其他文章

好康東東