エクセルの簡単なプログラム作成方法を教えてください。

X=10,000±5%KG(=MIN.9,500KG、MAX.10,500KG)、a+b+c=10,000KG、A=a±5%KG、B=b±5%KG、C=c±5%KG
かつ、価格に関してはA>B>Cの場合
全体金額を最大化させるA,B,Cの按分(例:A=a±5%KG、B=b±5%KG、C=残り)をXの場合分け(例:X=10,000-5%)により自動判別出来るプログラムを教えてください。

回答の条件
  • 1人30回まで
  • 登録:2009/04/25 10:35:53
  • 終了:2009/04/26 23:33:39

ベストアンサー

id:airplant No.3

airplant回答回数220ベストアンサー獲得回数492009/04/26 22:44:12

ポイント40pt

何度もすみません。サンプル数値を見て、意味を理解しました。

Excelのサンプルを載せておきます。

X=a+b+c

fx(X)=fa(a)+fb(b)+fc(c) (faなどは、価格を出す式)

fa(a)=A*a, fb(b)=B*b, fc(c)=C*c (A,B,Cはa,b,cの単価)

A > B > C

a, b, cを定数にし、それぞれを±5%範囲内で値を変更できる。

複数のX毎に、fx(X)を最大にするa, b, cを求めよ。

多分、下記の考え方を機械にやらせたかったと思うので、ダイレクトに聞いて頂いたほうが他の方からもすぐに回答あったと思われます。

高いものはできるだけ+5%で保って、安いものから使用量を下げていく、下げ切れなくなったら、次に安いものを下げていく。最後に一番高いものを下げていく。

>a+b+c=10,000KG、A=a±5%KG、、、

のようにあったので、てっきりAは個数と思ったのですが、A>B>Cというのは、単価の話だったのですね。


Excelで次の式を入れてみてください(セルに貼り付けてみてください)。

Xの値はD列4行目に入れます。

a,b,cの対応が取れるように、A列からaを始めています。

又、±5%の範囲外の場合は、#N/Aを出すようになっています。

  A B C D
1 A B C X
2 3000 5000 2000 =SUM(A2:C2)
3
4 =IF(OR(D4>$D$2*1.05,D4<$D$2*0.95),NA(),IF(D4<$A$2*1.05+($B$2+$C$2)*0.95,D4-($B$2+$C$2)*0.95,$A$2*1.05)) =IF(OR(D4>$D$2*1.05,D4<$D$2*0.95),NA(),IF(D4<($A$2+$B$2)*1.05+$C$2*0.95,D4-(A4+$C$2*0.95),$B$2*1.05)) =D4-A4-B4 目標入力エリア

複数を一括で出すには、4行目をドラッグしてください。適当に数値を入れてみた結果は次の表です。

合っているはずです。

(10300,9800ともkomainubzlさん提示の結果と違いますが、komainubzlさんの計算ミスと考えていいですよね?)

  A B C D
1 A B C X
2 3,000 5,000 2,000 10,000
3
4 3,150 5,250 2,100 10,500
5 3,150 5,250 1,900 10,300
6 3,150 4,950 1,900 10,000
7 3,150 4,750 1,900 9,800
8 2,850 4,750 1,900 9,500
9 #N/A #N/A #N/A 9,400

P.S.もしポイントいただけるときは、0は避けて均等割りしていただけると助かります。0だと悪質回答者とみなされる仕組みですので。。。

id:komainubzl

すばらしい!ありがとうございます

2009/04/26 23:32:59

その他の回答(2件)

id:airplant No.1

airplant回答回数220ベストアンサー獲得回数492009/04/26 10:04:53

ポイント40pt

前提となる条件を明確にすることで、回答があると思います。

今の条件だと、回答ができないのではないかと。


簡潔にするために、±5%は省略したとして、次の命題を解くということですね。

X=a+b+c

fx(X)=fa(a)+fb(b)+fc(c) (faなどは、価格を出す式)

fa(a) > fb(b) > fc(c)

X=10000のときのfx(X)が最大となるa,b,cを求めよ

要は、fa(a)、fb(b)、fc(c)及びa,b,cの按分条件が分からないと、求められないということです。

例えばaの1Kgあたり価格が一番高いとすると、すべてaを使うのが全体金額が最大になります。

a,b,cに按分するほかの条件がきっとあるはずですので、そちらも記載されてみてはどうでしょう。

価格を最大にするということは、Kgあたり単価が高いものを最大限使えばいいので、手計算ですぐに出てしまうような気がしますが・・・

多分私が何か誤解しているんですね。

全くの誤解だった場合は、ごめんなさい。


なお、変数が一つで、結果の目標値が決まっている場合は、ゴールシーク機能を利用すれば、値を求めることができます。

ゴールシーク機能で逆算してみよう - [Excelの使い方]All About

id:komainubzl

仰るとおりでした。ご指摘ありがとうございます。

a,b,cに関する按分条件があります。例えばa=3,000、b=5,000、c=2,OOOなど。Xは9,500から10,500まで色々あります。

確かに手計算ですぐには出来るのですが、検算のために当該プログラムを作れないかと思いまして。fa>fb>fcの場合でも9,500と10,500ではa,b,cの±5%の按分が異なるので(aが5%max出来ない場合もある)。

2009/04/26 10:28:48
id:airplant No.2

airplant回答回数220ベストアンサー獲得回数492009/04/26 17:26:54

ポイント40pt

コメント欄がオープンになっていないので、こちらで失礼します。

(コメント欄のオープンをお願いしたいと思います)


要望が良く分からないのですが、「全体金額を最大化させるA,B,Cの按分」は人が考えるのですね?

それが条件に合っているかどうかをExcelで検算したいということでよろしいでしょうか?

そうして、A, B, Cの按分は既に決まっていて、±5%の範囲内でのみ調整をするということでしょうか?

金額を最大化させるためには、常に単価の高いものをXの±に合わせて行えば良さそうですが、fa、fb、fcの数式が分からないと、Excelで関数にするのは難しいかなと思います。

id:komainubzl

要望が良く分からないのですが、「全体金額を最大化させるA,B,Cの按分」は人が考えるのですね?

>御理解の通り。

それが条件に合っているかどうかをExcelで検算したいということでよろしいでしょうか?

>御理解の通り。

そうして、A, B, Cの按分は既に決まっていて、±5%の範囲内でのみ調整をするということでしょうか?

>御理解の通り。

fa,fb,fcは数式ではなくて定数でfa>fb>fcと考えていただければよいです。例えば、fa=200、fb=150、fc=100。

X=10000の場合、a=3,150、b=4,950、c=1,900

X=10,300の場合、a=3,150、b=5,150、c=1,900

X=9,800の場合、a=3,000、b=4,900、c=1,900

これを全ての場合でプログラム化できないものか。。

2009/04/26 17:46:07
id:airplant No.3

airplant回答回数220ベストアンサー獲得回数492009/04/26 22:44:12ここでベストアンサー

ポイント40pt

何度もすみません。サンプル数値を見て、意味を理解しました。

Excelのサンプルを載せておきます。

X=a+b+c

fx(X)=fa(a)+fb(b)+fc(c) (faなどは、価格を出す式)

fa(a)=A*a, fb(b)=B*b, fc(c)=C*c (A,B,Cはa,b,cの単価)

A > B > C

a, b, cを定数にし、それぞれを±5%範囲内で値を変更できる。

複数のX毎に、fx(X)を最大にするa, b, cを求めよ。

多分、下記の考え方を機械にやらせたかったと思うので、ダイレクトに聞いて頂いたほうが他の方からもすぐに回答あったと思われます。

高いものはできるだけ+5%で保って、安いものから使用量を下げていく、下げ切れなくなったら、次に安いものを下げていく。最後に一番高いものを下げていく。

>a+b+c=10,000KG、A=a±5%KG、、、

のようにあったので、てっきりAは個数と思ったのですが、A>B>Cというのは、単価の話だったのですね。


Excelで次の式を入れてみてください(セルに貼り付けてみてください)。

Xの値はD列4行目に入れます。

a,b,cの対応が取れるように、A列からaを始めています。

又、±5%の範囲外の場合は、#N/Aを出すようになっています。

  A B C D
1 A B C X
2 3000 5000 2000 =SUM(A2:C2)
3
4 =IF(OR(D4>$D$2*1.05,D4<$D$2*0.95),NA(),IF(D4<$A$2*1.05+($B$2+$C$2)*0.95,D4-($B$2+$C$2)*0.95,$A$2*1.05)) =IF(OR(D4>$D$2*1.05,D4<$D$2*0.95),NA(),IF(D4<($A$2+$B$2)*1.05+$C$2*0.95,D4-(A4+$C$2*0.95),$B$2*1.05)) =D4-A4-B4 目標入力エリア

複数を一括で出すには、4行目をドラッグしてください。適当に数値を入れてみた結果は次の表です。

合っているはずです。

(10300,9800ともkomainubzlさん提示の結果と違いますが、komainubzlさんの計算ミスと考えていいですよね?)

  A B C D
1 A B C X
2 3,000 5,000 2,000 10,000
3
4 3,150 5,250 2,100 10,500
5 3,150 5,250 1,900 10,300
6 3,150 4,950 1,900 10,000
7 3,150 4,750 1,900 9,800
8 2,850 4,750 1,900 9,500
9 #N/A #N/A #N/A 9,400

P.S.もしポイントいただけるときは、0は避けて均等割りしていただけると助かります。0だと悪質回答者とみなされる仕組みですので。。。

id:komainubzl

すばらしい!ありがとうございます

2009/04/26 23:32:59

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

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

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

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

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