質問: Excelの質問です。 A列に合計金額があります。 B列、C列、D列にそれを一定の比率で配分したいのですが、 四捨五入すると合計と合わなくなることもあります。 小数点以下の数字が大きいものから切り上げ(同じであれば左側の列を優先)、 必ずB、C、D列の合計がA列の数字と合致する計算式を教えて下さい。 比率は毎回変わりますが、ここでは仮に3:2:1としておきます。 あくまでも計算式であって、VBAやマクロを使った処理は望みません。 疲れて考える力があまり残っていないので、早い回答よりもシンプルでわかりやすい方が助かります。 どうかよろしくお願い致します。
回答: Google ダミーで失礼します。 ちょっと作ってみたのですが、簡単じゃないので・・・。 手順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 どちらの場合もちゃんと求められました。