エクセルにて、金額を配分する計算式関数の作り方を教えてください。

初心者なため、色々わかりづらいかと思いますが、よろしくお願いいたします。

(例)
   本社から各支店の各イベントに使う予算額を配分したいのです。

① 各イベントの予算額は決定している。

  Aイベント 500
  Bイベント 250
  Cイベント 500
  Dイベント 450
  Eイベント 300

② 各支店での全体予定額は決定してる。

 東京支店 500
 千葉支店 400
 横浜支店 300
 埼玉支店 300
 茨城支店 500

③ 各支店での全体予定額の合計は各イベントの予算額の全体額の内数とする。

④支店によっては行わないイベントもある。

 東京支店 A・Eは行わない。
 千葉支店 B・Dは行わない。
 横浜支店 C・Dは行わない。
 埼玉支店 C  は行わない。
 茨城支店 E  は行わない。
 
※一ケタ台は四捨五入でお願いします。
 縦 支店 横 イベント の表を作りたいと思っています。

よろしくお願いいたしますm(__)m 

回答の条件
  • URL必須
  • 1人5回まで
  • 登録:2009/07/26 11:33:34
  • 終了:2009/07/29 10:14:57

回答(3件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/07/26 14:44:14

ポイント60pt

これは数学的には多元連立方程式になるのですが、条件が足りないので解は一つには決まらないとなります。

それでExcelの関数で値を出すのは不可能ということ。


ただし、Excelではソルバーというアドインを使うことで多元連立方程式を近似できるのです。


ソルバーはアドインなのでツール→アドインからソルバーアドインをチェックしてOK。

インストールされてなければインストールを即されます。

そうすると、ツールのところにソルバーという項目ができます。


次に表の作成。次のように表を作成します。

A B C D E F G
1 _ Aイベント Bイベント Cイベント Dイベント Eイベント 合計
2 東京支店
3 千葉支店
4 横浜支店
5 埼玉支店
6 茨城支店
7 合計

縦横の合計の列と行にはsum関数を入れます。

④の除外イベントのところに0を入れます。2行目ならB2とF2


次にソルバーの起動です。ツールからソルバーを選びダイアログを表示してください。

目的セルを合計の一つである$G$2

目的値を「値」500

変化させるセルを

$C$2,$D$2,$E$2,$B$3,$D$3,$F$3,$B$4,$C$4,$F$4,$B$5,$C$5,$E$5,$F$5,$B$6,$C$6,$D$6,$E$6

Ctrlを押しながら、0が入っていないセルをクリックしていけば楽に入力できます。

次に追加を押します。

ここでは、別の条件先ほどsum関数を設定した値の目標値を入れていき追加します。

制約条件のところに

$G$3=400
$G$4=300
$G$5=300
$G$6=500
$B$7=500
$C$7=250
$D$7=500
$E$7=450
$F$7=300

と全ての条件を追加します。


最後に実行を押して、解を記入してみてください。


ここで問題となるのが、埼玉支店のBイベントがマイナスになるのはおかしいので、

またソルバーを起動して、$C$5>=0という条件を追加してみてください。


ソルバーの使い方で参考になるのはこちら。

http://www-tech.edu.kagoshima-u.ac.jp/~toya/gijutu/jugyo_siryo/e...

id:syosinnsyako9567

大変ありがとうございます。

ソルバーで条件を設定し、一度やってみたいと思います。

初心者なもので時間がかかってしまうと思いますが、またご相談すると思います。

よろしくお願いいたします。

2009/07/26 18:59:22
id:pahoo No.2

pahoo回答回数5960ベストアンサー獲得回数6332009/07/26 14:57:20

ポイント15pt

コメント欄に書き込めないので、回答欄にて確認することをご容赦下さい。


各イベントへの分担金は、各支店に均等割ということですか?

たとえばイベントEについては、千葉・横浜・埼玉の3支店で開催しますから、Eイベントの予算300を3で割り、各支店に100ずつ割り当てるという意味ですか?

でも、この条件で計算すると、おそらく全体予算をオーバーする支店が出てきます。


一方、これを行列式で解こうとすると、17の変数に対して10個の確定値しかありませんから、一意的な解は求まりません。

Excelの問題ではなく、問題の条件そのものに疑問があります。


http://q.hatena.ne.jp/1248575613 URLはダミーです。

id:syosinnsyako9567

大変ありがとうございました。

条件をもう一度整理してみます。初心者なものなので、時間がかかると思いますが、またご相談すると思います。

よろしくお願いいたします。

2009/07/26 19:13:05
id:joruno No.3

joruno回答回数152ベストアンサー獲得回数12009/07/27 01:51:28

ポイント5pt

予算の最高額なら簡単に足し算、引き算で出るのではないでしょうか。

もちろん、配分が均等かわかりませんので、

どこかのイベントには適当な額を毎回入力しなければいけませんが。


例えば、

東京支店だと、

Aイベント・Eイベントのセルには罫線で斜線でもひいておく。

Bイベントのセル(仮にC2)には、適当な予算を手入力

Cイベントのセル(仮にC3)には、=500-C2

Dイベントのセル(仮にC4)には、=500-C2-C3


均等の場合は、

C2~C4セルに =ROUNDDOWN(500/3,-1)でOK。

http://www.pasosapo.net/excel/contents/round.html

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

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

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

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

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