有網友問到一個 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。

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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