Excelの質問です。

A列に合計金額があります。
B列、C列、D列にそれを一定の比率で配分したいのですが、
四捨五入すると合計と合わなくなることもあります。
小数点以下の数字が大きいものから切り上げ(同じであれば左側の列を優先)、
必ずB、C、D列の合計がA列の数字と合致する計算式を教えて下さい。
比率は毎回変わりますが、ここでは仮に3:2:1としておきます。
あくまでも計算式であって、VBAやマクロを使った処理は望みません。
疲れて考える力があまり残っていないので、早い回答よりもシンプルでわかりやすい方が助かります。
どうかよろしくお願い致します。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2006/03/10 19:19:43
  • 終了:--

回答(4件)

id:kimizu No.1

kimizu回答回数726ベストアンサー獲得回数212006/03/10 20:05:37

ポイント10pt

まず、切り上げはROUNDUPです。

例は、4行目に数値、3行目に比率を入れた場合です。


B3,C3,D3に各比率を入れます。

A4に合計の金額があるとします。


B4=ROUNDUP(A4*B3/(B3+C3+D3),0)

C4=ROUNDUP((A4-B4)*C3/(C3+D3),0)

D4=A4-(B4+C4)


まず、切り上げでBの列の値を出します。

出し方は、

 A列*比率1/(比率1+比率2+比率3)

ですね。


次に、Cの列の母数は、A列からB列の数値を引いたものから算出します。


 (A列-B列)*比率2/(比率2+比率3)


要は、

B以降を計算する際は、既に出ているBの列分を合計から引き、

残りを、C列とD列で分ける

という寸法です。


最後のD列は、


 A列-(B列+C列)


です。残りはいくつ?という感じですね。

id:aqua-marine

ありがとうございます。

でも上記だと少し違ってしまうのです。

例えば合計が「98」だとして比率が5:3:2だったとします。

B「49」C「29.4」D「19.6」が小数点も含めた答えです。

それを切り上げる時にBは問題ありませんが、CはDよりも小数点位下の数値が小さいのでDが切り上げされます。

答えはB「49」C「30」D「19」ではなくて、B「49」C「29」D「20」にならなくてはいけないのです。

小数点以下の一桁目を見る関数を入れなければならないのでしょうね・・・(>_<)

引き続きどうかよろしくお願い致しますm(__)m

2006/03/10 20:48:51
id:kumaimizuki No.2

くまいみずき回答回数614ベストアンサー獲得回数312006/03/10 22:39:51

ポイント20pt

ダミーで失礼します。

ちょっと作ってみたのですが、簡単じゃないので・・・。


手順1.

A1セルに合計を入力

B1~D1セルに比率を入力


手順2.

B1セルに以下の関数を入力

=ROUNDDOWN($A$1/SUM($B$1:$D$1)*B1, 0)

-----

これで比率ごとの数字を求めます。

この時は小数点以下を切り捨てます。


手順3.

手順2の式をC2~D2へコピーする


手順4.

B3セルに以下の関数を入力

=$A$1-B2/B1*SUM($B$1:$D$1)

-----

合計から先ほど切り捨てた数値を利用して差分を求める。

差分0なら割り切れた。差分があれば端数が出た証拠です。


手順5.

手順4の式をC3~D3へコピーする


手順6.

B4セルに以下の関数を入力

=B1*B3

----

上記比率が大きい方が小数点以下の数字が大きくなるため。

加算でも大丈夫かも?

とにかく、差分が同じになった場合のためのものです。


手順7.

手順6の式をC4~D4へコピーする


手順8.

E1セルに以下の関数を入力

=A1-SUM(B2:D2)

-----

B~Dと合計の差分を求めます。


手順9.

B5セルに以下の関数を入力

=IF($E$1=0, B2, IF($E$1=1, IF(MAX($B$4:$D$4)=B4, B2+1, B2), IF(MIN($B$4:$D$4)=B4, B2, B2+1)))

-----

左から順に。

最初のifの場合、既にB2~D2の合計が既にAの合計と一致した場合、数字に手を加えません。

次のifでは、端数が1だった場合。

この場合は、1つだけ「1」を加えます。

その条件はB4~D4までの値が1番大きいところ。

ここが小数点以下の数値が1番大きいところになります。

もし、最大値なら、B2~D2の値に1を加え、そうでなければ値は触りません。

最後は端数が2の場合。

この場合は、1の時と逆で、1番小さいものには1を加えないようにします。

条件も最小値を求め、一致の場合は触らず、不一致の場合は1を加えるというものになっています。


説明だけでは難しいので、エクセルに実際に入力してみてください。

多分大丈夫だと思います。


(試験結果)

合計:98、比率5:3:2

合計:98、比率1:2:3

どちらの場合もちゃんと求められました。

id:aqua-marine

どうもありがとうございます。

きちんと数字が出ました。お時間をかけてここまで説明していただいて、感謝の言葉もありません。

2006/03/11 23:48:10
id:kimuram No.3

kimuram回答回数13ベストアンサー獲得回数52006/03/10 23:49:52

ポイント20pt

http://kimuram.plala.jp/

���@�悤�����j�������������z�[���ց@��

urlはダミーです。

実例ファイルの実際の示し方がよくわからないので、わかりにくいと思いますが説明だけでしてみます。


計算式だけではかなり難しいものがあると思います。

まとめることも可能でしょうが、3段階に分けた形で示してみます。

すべて検証したわけではないので、たとえば切り捨て側による誤差の発生、あるいは配分数が多くなり誤差が1より大きくなるケースがあるとした場合、等には対応できていないかもしれませんが、とりあえず速報のような形で示してみます。

手順は以下の通りです。


1行目は配分の比率がB、C、D列にそれぞれ3,2,1、のように入る。

2行目は列の見出し文字。


(※計算式は3行目を例として示します。他の行にコピーして使えます。)


(1)まずB列、C列、D列に「普通の配分比率での四捨五入の結果+補正」の式を入れます。(補正はそれぞれM、N、O列を減算する指定とします)

B3:=ROUND($A3*B$1/SUM($B$1:$D$1),0)-M3

  B4、B5はB3をコピー


(2)G列に、四捨五入方式により配分した結果の総和から元の合計金額を引いた差(つまり誤差)を求める式をセット

G3==(ROUND($A3*B$1/SUM($B$1:$D$1),0)+ROUND($A3*C$1/SUM($B$1:$D$1),0)+ROUND($A3*D$1/SUM($B$1:$D$1),0))-A3


(3)次はI、J、K列にそれぞれ四捨五入有無それぞれでの配分結果による差を求める計算式を入れます。

I3:=ROUND($A3*B$1/SUM($B$1:$D$1),0)-$A3*B$1/SUM($B$1:$D$1)

  J3、K3はこのI3をコピー


(5)M、N、O列に、G列が1の場合(つまり単純配分誤差ありの場合)を条件として、I、J、K列のうち丸め誤差のもっとも大きいものに補正用「1」をセットする計算式を入れる。

M3==IF(G3=1,IF(MAX($I$6:$K$6)=I$6,1,0),0)

  N3、O3にはこのM3をコピー


と言うことで、補正された結果がB、C、D列に求められます。

確認用としてE列に配分結果の再合計を出して元と同じであることを確認すると、結果が同じであることが見える。

E3:=SUM(B3:D3)


とりあえずですがいかがでしょうか?あとはまとめ方と特殊ケースへの対応かと安易に考えたりしていますが。


excelを添付できると確認が簡単なのですが、示し方がわからなくてすみません。

計算式を入れてA列の合計金額をいろいろ変化してみるといいと思います。

とりあえず、見るのが遅かったので急いでしまいました。ちょんぼがありましたらごめんください。

id:aqua-marine

ありがとうございます。

処理件数が数千件分になるので、これから早速試してみます。

2006/03/12 14:58:10
id:kimuram No.4

kimuram回答回数13ベストアンサー獲得回数52006/03/11 00:52:47

ポイント30pt

すみません!。式を間違えていました。

一部訂正させてください。


セルM3は次のようになります。

M3:=IF($G3=1,IF(MAX($I3:$K3)=I3,1,0),0)


また、N3はM3と同じではなく

N3:=IF($G3=1,IF(SUM($M3:M3)=0,IF(MAX($I3:$K3)=J3,1,0),0),0)

です。

そしてO3はN3をコピーです。

(補正候補が複数あった場合に左側を優先としてはじめの1個だけとするもの)


urlにexcelファイルを直接指定してみました。ファイルダウンロードの扱いとなると思います。(うまくいけばいいですが、はて)

id:aqua-marine

ファイルまで作成していただいて、どうもありがとうございました。

早速仕事に生かせそうです。

皆様、本当にどうもありがとうございました。

これで作業も精神的にも楽になりました。

心からお礼を申し上げますm(__)m

2006/03/12 15:13:33

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません