エクセル関数 予算を割り切れない比率で分配するには

質問

102円を25％、31％、44％で分配しなければならない時に使える関数はありますか？

これを手計算でする場合は次のようにしています。
102円の25％＝25.50　（Ａ）
　　　　 31％＝31.62　（Ｂ）
　　　　 44％＝44.88　（Ｃ）

【手順1】
それぞれの小数点以下を比較し、一番大きい（Ｃ）を切り上げる。（Ａ）（Ｂ）は切捨てて合計を出してみる。
　（Ａ）ROUNDDOWN　→25.00円
　（Ｂ）ROUNDDOWN　→31.00円
　（Ｃ）ROUNDUP　　　→45.00円
合計が101円となり、予算に一致しないので【手順2】へすすむ。

【手順2】
小数点以下が二番目に大きい（Ｂ）も切り上げる。（Ａ）は切捨てて合計を出してみる。
　（Ａ）ROUNDDOWN　→25.00円
　（Ｂ）ROUNDUP　　　→32.00円
　（Ｃ）ROUNDUP　　　→45.00円
合計が102円となり、予算と一致。手計算終了！

以上のことを簡単な関数を使って算出する方法がありましたら是非教えて下さい。このような分配結果を得られれば、必ずしも手計算の過程を踏まなくてもいいです。よろしくお願いいたします。

回答No.4

tascany · ベストアンサー

＃１です。
たびたび失礼します。
さて、四つ以上に分配する場合にも妥当するこたえがわかったので、書いておきます。
こっちのほうが簡単です。

Ａ１に102円、
Ｂ列に25％（Ｂ１）、31％（Ｂ２）、44％（Ｂ３）、
Ｃ列に上から25.50、31.62、44.88とあるとします。

１）Ｄ１に「=C1-INT(C1)」を貼り付けてＤ３までコピペ。
２）Ｄ１からＤ３のＳＵＭをＤ４に。
２）Ｅ１に
「=IF(RANK(D1,D$1:D$3)-0.5<D$4,ROUNDUP(C1,0),ROUNDDOWN(C1,0))」
を貼り付けてＥ３までコピペ。

※小数点以下の合計が２なら、大きい方から二つ（RANKの値が１と２のもの）切り上げて、あとは切り捨てればすむことですね。この考え方だと、分配する数をいくらでも増やせます。

お礼

ありがとうございました！！

回答No.5

maruru01

こんにちは。maruru01です。

データがA1にあり、B1～に分配率を「25%」（=0.25）のように入力するとします。
C1に、

=IF(SUMPRODUCT((MOD($B$1:$B$4*$A$1,1)>MOD(B1*$A$1,1))*1)+SUMPRODUCT((MOD($B$1:B1*$A$1,1)=MOD(B1*$A$1,1))*1)-1<SUMPRODUCT($B$1:$B$4*$A$1)-SUMPRODUCT(INT($B$1:$B$4*$A$1)),ROUNDUP(B1*$A$1,),ROUNDDOWN(B1*$A$1,))

と入力して、下の行へコピーします。
この数式ではB1:B4の4つの分配の例になります。
分配数によって、数式中の「$B$4」を変更して下さい。
また、分配率が同じ場合は、上の行を優先的に切り上げます。

回答No.3

BLUEPIXY

分配するユーザー定義関数を作ってみました。
以下をＶＢＥで標準モジュールに貼り付けて下さい
'value:分配元値、a:分配する比率の配列、n:その何番目
'結果は小数部分が大きいモノから分配する
Public Function 分配(v, a As Variant, Optional n = 1)
Dim sum, i
Dim b(), c()
ReDim b(LBound(a) To UBound(a))
ReDim c(LBound(a) To UBound(a))
sum = Application.WorksheetFunction.sum(a)
For i = LBound(a) To UBound(a)
a(i) = a(i) / sum '比率に変える
b(i) = Fix(v * a(i)) '端数を捨てる
c(i) = v * a(i) - b(i) '端数を取っておく
Next
Do While Application.WorksheetFunction.sum(b) <> v '修正した値がトータルに等しくならない
i = Application.WorksheetFunction.Match(Application.WorksheetFunction.Max(c), c, 0) '小数部分が一番大きい
b(i) = b(i) + 1
c(i) = 0
Loop
分配 = b(n)
End Function
使い方
=分配(分配する元の数値またはセル,{分配する比率},取り出す値の順番)
例
A1が102の時
=分配($A$1,{25,31,44},ROW(A1))
にして下にコピー
あるいは、直接
=分配(102,{25,31,44},1)
=分配(102,{25,31,44},2)
=分配(102,{25,31,44},3)
{25,31,44}は、２５：３１：４４の意。

お礼

ご回答、ありがとうございました！初心者の私には少々難しく感じましたが、とりあえず貼り付けてやってみます。私ももうちょっと勉強しなくては・・・ありがとうございました！

回答No.2

tascany

＃１です。
訂正を・・・。

「=CHOOSE(RANK(D1,D1:D3),ROUNDUP(C1,0),ROUND(C1,0),ROUNDDOWN(C1,0))」
ではなく、
「=CHOOSE(RANK(D1,D$1:D$3),ROUNDUP(C1,0),ROUND(C1,0),ROUNDDOWN(C1,0))」
でした。

失礼しました。

回答No.1

tascany

おもしろい問題ですね。
で、三つに分配する場合についてしか通用しない考え方ですが、それでよければ・・・いかがでしょうか。

Ａ１に102円、
Ｂ列に25％（Ｂ１）、31％（Ｂ２）、44％（Ｂ３）、
Ｃ列に上から25.50、31.62、44.88とあるとします。

１）Ｄ１に「=C1-INT(C1)」を貼り付けてＤ３までコピペ。
２）Ｅ１に「=CHOOSE(RANK(D1,D1:D3),ROUNDUP(C1,0),ROUND(C1,0),ROUNDDOWN(C1,0))」を貼り付けてＥ３までコピペ。
※整数点以下が一番大きいものを切り上げ、二番目は四捨五入、三番目は切り捨て。何故これで予算と一致することになるかというと・・・・、
（三つに分配した場合）整数点以下の合計は、１，２のいずれかなんですけど（簡単のため０は無視）、合計２になるにもかかわらず、二番目に大きいものが四捨五入・切り捨てになるということはありえません。というのも、もし二番目に大きなものが０．５未満だとすると、それより小さな三番目の数との和は１未満になる、したがって、それらと一番目の数との和も２未満になるはず（背理）、だから。

お礼

分かりやすくご説明していただき、ありがとうございます！この後のご回答もあわせてとても参考になりました。