数式を模索していますが、うまくいきません

Question

お世話になります

小さな製造業の会社で経理を担当しております。無駄を抑えた鋼材の発注に努めようと、高齢の職人さんたちが電卓を片手に、時間をかけて計算していることを知り、エクセルでなんとかお手伝いできないかと、IF、OR、AND、SUM関数を駆使し、あれこれ試してみましたがうまくいきません。

例えば下のような長さの板が必要で、定尺6000の板を切断するとします（幅は考えません）

1,410が8枚、695が8枚、120が16枚、90が16枚、計20,200です

定尺の板を何枚使ってどういう組み合わせで切断したら無駄を最小限にできるのか、寸法を入力するだけで答えを導き出す方法はないものでしょうか。

ひとまずあきらめて、次のような表を作りました。

（表1）
	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	…
[1]	6,000
[2]	1,410	1,410	1,410	1,410	1,410	1,410	1,410	1,410												-5,280
[3]	695	695	695	695	695	695	695	695												440
[4]	120	120	120	120	120	120	120	120	120	120	120	120	120	120	120	120				4,080
[5]	90	90	90	90	90	90	90	90	90	90	90	90	90	90	90	90				4,560
[6]																				6,000
[7]																				6,000
[8]																				6,000
[9]																				6,000
[10]																				6,000

A1に、定尺の長さ6000を入力し、A2以降に切断する板の寸法を必要数入力。任意のセル（例えばU2)に、=$A$1-SUM（A2:T2）と入れ、フィルハンドルで下方向へ（表1）。あとはパズルのように切りとっては貼り付け、答えがマイナスにならず、余りをできるだけ1枚にまとめ、それ以外は限りなくゼロに近づく組み合わせをさがします。結果4枚使い、余りは20と3,780各1枚です（表2）。

（表2）
	[A]	[B]	[C]	[D]	[E]	[F]	[G]	[H]	…
[1]	6,000
[2]	1,410	1,410	1,410	1,410	90	90	90	90												0
[3]	695	695	695	695	695	695	695	695	120	120	90	90								20
[4]	120	120	120	120	120	120	120	120	120	120	90	90	120	1,410	1,410	1,410	90	90	90	0
[5]	90	90	90	90	90						120	120	120				1,410			3,780
[6]																				6,000
[7]																				6,000
[8]																				6,000
[9]																				6,000
[10]																				6,000

しかしこれだと、目視で試行錯誤するため、数が増えるとそれなりに手間取り、それほど役にたちそうもありません。拙い説明で申し訳ございません。少しでも妙案があればご教示いただきたく、よろしくお願いいたします。

Answer 1

y_sakuda　さん、ご紹介頂きありがとうございますm(_ _)m

先のコメントの通り、この問題は「組合せ最適化」の問題で、

切出し問題（CSP＝CuttingStockProblem）と呼ばれていて、

詰め込み問題（BPP=BinPackingProblem）と等価な問題です。

可能な組合せを全て列挙すれば必ず解が見つかる簡単な問題ですが

規模が大きくなると組合せの数が爆発的に増加して、実質的には解けなくなる問題です。

提示された問題の規模であれば、拙作 seek によって簡単に解を得ることが出来ます↓

http://supermab.com/seek.html

フリーですので、お試しください＾＾；；；

Answer 2

この手の問題が、結構大変な話であるという例示ですが、我々のお遊びで単純化して遊んだことがあります。

http://www.geocities.jp/kuma_pooh1958/index.htm

の第3問、第53問を覗いて見て下さい。

なお、これは達者な連中の仲間内の遊びであり、提示されている式を模倣してほしいという意味ではありません。

業務上でこのような式やシートを作ったら、作成者が居る内はともかくいなくなったらメンテ不能になること請け合いです。

Answer 3

所謂最適化の話だと思います。

勿論すべての組み合わせをしらみつぶしに調べればよいわけですが、膨大な組み合わせになりますので、効率的にやるのは非常に難しい課題になってしまいます。

最適化ソフトを使用する方が安全確実だと思います。

supermabさんが↓のようなものを提供されていますので、覗いて見ることをお勧めします。

http://supermab.com/

 

http://supermab.com/wp/excel-dna-%e3%81%a7-xll-%e3%82%92%e3%81%a4%e3%81%8f%e3%82%8b%ef%bc%88%e3%81%9d%e3%81%ae17%ef%bc%89/

 

早速のご回答ありがとうございます。後ほど確認させていただきます。今後ともどうかよろしくお願いいたします

Answer 4

所謂最適化の話だと思います。

勿論すべての組み合わせをしらみつぶしに調べればよいわけですが、膨大な組み合わせになりますので、効率的にやるのは非常に難しい課題になってしまいます。

最適化ソフトを使用する方が安全確実だと思います。

supermabさんが↓のようなものを提供されていますので、覗いて見ることをお勧めします。

http://supermab.com/

http://supermab.com/wp/excel-dna-%e3%81%a7-xll-%e3%82%92%e3%81%a4%e3%81%8f%e3%82%8b%ef%bc%88%e3%81%9d%e3%81%ae17%ef%bc%89/