Excelで最適な割付を求める関数

質問させていただきます。

部材A→長さ1.5m・単価1800円
部材B→長さ2.5m・単価2800円

上記2つの部材を、測定した長さに対して最も安くなるように割り付ける関数は出来ないでしょうか。
例えば10mの長さにするには

1.5mx7本＝10.5m(12600円)
1.5mx5本＝7.5m、2.5mx1本＝2.5m　計10m(11800円)
1.5mx2本＝3m、2.5mx3本＝7.5m　計10.5m(13800m)

など、色々な組み合わせがありますが、
「10m以上の長さになり、かつ最も合計価格が安い組み合わせ」を
関数で求めたいと考えています。
もしそのような関数があればご教授ください。よろしくお願いいたします。

No.4 回答者： taigoito 回答日時： 2017/05/10 18:39

No.1ですが、失礼しました。

A材のときはIntじゃなくて、Roundupでした。

A列に求める長さ
B列にA材の本数
C列にB材の本数
D列に長さの合計
を入力するならば、

A2は適当な長さを入力
B2=ROUNDUP(MOD(A2,C2*2.5),0)
C2=INT(A2/2.5)
D2=B2*1.5+C2*2.5

これでB～D列をオートフィルすれば、
様々な長さの場合の最適解が得られると思うのですが。

No.3 回答者： fujillin 回答日時： 2017/05/10 17:24

こんにちは

一種の最適化問題ですね。
私は専門家ではないので一般解は出せませんが、ご質問の程度単純なものであれば、少し検討しさえすれば結果を求めるのは容易です。

No1様もおっしゃっているように、B材のほうが単価が安いのでB材を利用した方が有利ですね。
このことから、1.5mと2.5mの最小公倍数の7.5mの倍数の長さまではB材を利用すれば良いことはすぐにわかります。
この結果、7.5m以下の余りの部分についてだけ、検討しておけばよいことになります。
値段が段階的に変わる可能性があるのは双方の長さの公約数が0.5mなので、このピッチで検討しておけばよいことになりますね。

この結果は、以下のようになると思います。
（範囲の下端は含まず、上端は含む）
0～1.5m　　A材×1　（\1800)
1.5～2.5m　B材×1　 (\2800)
2.5～3m　　A材×2　 (\3600）
3～4m　　A材×1＋B材×1　(\4600)
4～4.5m　A材×3　　　(\5400)
4.5～5m　B材×2　　　(\5600)
5～5.5m　A材×2＋B材×1　(\6400)
5.5～6m　A材×4　　(\7200)
6～6.5m　A材×1＋B材×2　(\7400)
6.5～7m　A材×3＋B材×1　(\8200)
7～7.5m　A材×5　　　(\9000)
（↑5分でやったので、間違いがあるかも…ご確認願います）

エクセル利用なら、この範囲に関して表形式で計算させてしまうか、グラフ等にしておくのがわかりやすいのではないかと思います。

＞関数で求めたいと考えています。
関数にしたいのであれば、上記の場合分けをそのまま式にしても良いですし、あるいは表を作成しておいてLookup系の関数で参照するなどの方法でも関数化は容易に可能でしょう。


※ きちんと一般解を求めたい場合は、最適化手法などを参考になさるとよろしいかと思います。
（私には無理ですが、多分、単純な関数ではなくゴールシークなどを利用することになるのではないかと・・・あるいは、いっそのことユーザ関数で定義しちゃうとか。）
https://ja.wikipedia.org/wiki/%E6%9C%80%E9%81%A9 …

No.2 回答者： t_fumiaki 回答日時： 2017/05/10 16:17

自動で求める関数なんか有りません。

不等式を満足するa,bを求めるだけです。
部材Aの本数をa、部材Bの本数をbとすると

1.5a+2.5b≧10　①
a≧0、b≧0　②

①、②の条件下で1800a+2800bの最小値を求める問題を解いて答えを自分で出すしか有りません。

①より3a+5b≧20 ∴a≧20/3 - 5b/3≧0
②より0≦b≦4
値段は12000-200bとなり、これを最小にするbは4
∴aは0本

部材Bを4本にする。
2.5×4=10m
2800×4=11,200円

No.1 回答者： taigoito 回答日時： 2017/05/10 15:53

A材よりB材の方が、1mあたりの単価は安い訳ですから、

考え方としては、全長をB材の長さで割って、
余りをA材の長さで割れば、
割り付けの一番安い各本数が求められると思います。

端数を切り捨てるInt関数と、割り算の余りを求めるMod関数を使えばいけますよね。

最適な解かどうか分かりませんが、
A1セルに求めたい長さを入力したとしたら、
最適なB材の本数（A2セルとする）が、
=INT(A1/2.5)
最適なA材の本数（A3セルとする）が、
=INT(MOD(A1,A2*2.5)/1.5)+1

間違っていたらすみません。