Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
塗料の必要量に対する最安値の金額を算出方法
投稿日時: 17/06/06 15:35:26
投稿者: ピー

 いつもお世話になっております。・
お客様の仕様で下記のようなことが関数のみで可能でしょうか?
(例) 塗料の必要量 500kg
  塗料の購入単位      単価
    128kg   15000円
     64kg   10000円
     32kg    5600円
     18kg    3000円
とした場合(上記は例ですので、必要量・購入単価のkgなどは色々ことなります)
@ =整数求める関数(塗料の必要量、塗料の購入単位最大)と余り求める関数
  ここでは、整数はQUOTIENT(500,128):3 余りはMOD(500,128):116 よって 128kgの単価*3=45000円
A @の余りから同様の計算をする
  整数はQUOTIENT(116,64):1 余りはMOD(116,64):52 64kgの単価*1=10000円
B Aの余りから同様の計算をする
  整数はQUOTIENT(52,32):1 余りはMOD(52,32):20 32kgの単価*1=5600円
C Bの余りから同様の計算をする
  整数はQUOTIENT(20,18):1 余りはMOD(20,18):2 18kgの単価*1=3000円
D Cの余りは2なので18kgの単価*1=3000円を追加
最終金額合計 45000+10000+5600+3000+3000=66600円となりますが、最安値での購入は
  128kg単価*4=60000円が最安値と思うのです(検証していないですが)
 
このような計算(最安値と算出)をexcel関数のみで可能でしょうか?
※VBAで作成して結構ややこしいかなと思っておりますが・・・
ご教授の程 よろしくお願い致します。
 

回答
投稿日時: 17/06/06 15:53:47
投稿者: Mike

》 上記は例ですので、必要量・購入単価の
》 kgなどは色々ことなります
とのことだけど、不合理な例を提示することは禁物です。
例えば、18kg→32kgの場合だけ、購入量が多いほど単価が大きいとかネ。

回答
投稿日時: 17/06/06 16:21:22
投稿者: Suzu

引用:
不合理な例を提示することは禁物です。
例えば、18kg→32kgの場合だけ、購入量が多いほど単価が大きいとかネ。

 
不合理 と 言いますか、
数量が多ければ、単価が安くなるとは限らない物もあるかと。
その「不合理」も含めての問題なのかも。。
  
 
 
「組み合わせ 最適化」の問題ですよね。
それだけで、結構な内容のお話ですよね。
 
お客様の仕様と言うくらいですから、お金を頂いての案件ですよね
それを検証もせず丸投げはどうかと。。
 
少なくとも当方はVBAでゴリゴリしか思いつきません。
 
お金を頂けているのであれば、数学者の方にアドバイスを求められてはいかがですか。

投稿日時: 17/06/07 15:04:55
投稿者: ピー

 ソルバーを使用することで解決しそうです。
ありがとうございました。