エクセル 計算

　A 　 B 　
1 長さ　本数
2 2900 　 1
3 5650 　 16
4
5切断　本数
6　422 200
7 45 120

以上のように入力されているシートがあります。
長さ2950cmの棒が１本と5700cmの棒が16本あり、これらを使って422ｃｍにカットされた棒を200本、45cmにカットされた棒を120本用意したい。もっとも無駄がない切断の配分はどうなるか？

というのをエクセルでやりたいのですがどなか教えていただけませんか？
お願いします。

No.5 回答者： MackyNo1 回答日時： 2012/02/15 02:35

補足と訂正です。

ソルバーの条件を一部落としていました。

お分かりのことと思いますが、目標値は「最小値」にチェックを入れてください。

また、検索条件を今回の結果を参考にB6がB7の1.3倍より大きい条件（$B$6>=$B$7*1.31）でソルバーすると、すべて422ｃｍにカットする方法が最適となりましたので、回答した分割方法が最適値になっていることになります。

No.4 回答者： MackyNo1 回答日時： 2012/02/15 02:24

ご希望の切断をするには条件が複数あるのでソルバーの機能を利用します。

基本的に5700cmの棒がメインなのでこの分割方法がポイントになります。

準備としてＥ７セルに以下の式を入力しておきます。
=ABS(A3-A6*B6-A7*B7)

次にＥｘｃｅｌ2007ならデータタブの「ソルバー」を起動し、目的セルをE7、変化させるセルをB6：B7にして、制約条件に、以下の条件を追加します。

$A$3>=$A$6*$B$6+$A$7*$B$7
$B$6=整数
$B$6>=0
$B$7=整数
$B$7>=0

この4つの条件でソルバーすると添付画像の下のように1本の材料から422cm が11本、45cmが29本とると1本当たりの残りの部分が3ｃｍの最小となります。
しかし、この条件では422ｃｍの棒を残りの材料からたくさん作る必要があり、最も効率的な方法はありません（14本とると1本あたり42ｃｍの残りが生じてしまいます。

そこで、制約条件を追加し、B6の本数がB7よりも大きいという条件（$B$6>$B$7）でソルバーした結果が添付ファイルの結果です。
結果は、1本の材料から422cm が13本、45cmが10本とると1本当たりの残りの部分が14ｃｍとなります。
これで5700cmの材料10本で45cmの部品はすべて作成できますので、残りの422cmは44本必要となり、これを5700cmの材料3本で42本作成し、残りの2本を長さ2950cmの材料から取ります。

必要本数などのセルに入れる数式はご自分で工夫してみてください。

#Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

No.3 回答者： m_and_dmp 回答日時： 2012/02/14 14:00

エクセルシートの一部の画像を添付しましたので参照ください。

(1)B3～B18には手入力で422cmものの本数を入力します。余り（D列）の数値がマイナスになったときは本数が多すぎます。

(2)B19には、B3～B18の合計が200に不足する分が自動的に計算されます。
数式は、=IF(SUM(B3:B18)>=200,0,200-SUM(B3:B18))

(3)C3～C19は、材料から422cmものを指定した本数切り取ったときの残りの寸法から45cmものを何本切り取ることができるか、計算します。
数式は、
C3=ROUNDDOWN((A3-B3*B$1)/C$1,0)
C4=ROUNDDOWN((A4-B4*B$1)/C$1,0)
C5=ROUNDDOWN((A5-B5*B$1)/C$1,0)
・
・
・
C19=ROUNDDOWN((A19-B19*B$1)/C$1,0)
ただし、最終的には、合計本数が120になるように後段の行の計算結果を削除し、手入力します。
サンプルでは、C18, C19を削除してC18に4を手入力してあります。

(4)D列にはあまりの長さが自動計算されます。

5650の材料から422cmものの取れる本数はそれほど自由度はありません。（このシートを使ってみるとすぐにわかると思いますが、）最大数は13(13を超える数値を入れると余りがマイナスになる。）です。合計本数を200にするためには、小さい数では足りなくなるからです。（B19の値が13を超え、余りがマイナスになります。）

無駄のない切断とは、短い切れ端がたくさん出ることだと理解します。したがって、B3～B18に適当に数値を入力しながら、余りの長さをチェックします。
材料一本丸々余るようにできると良いのですが、それはありませんでした。
材料を10数本以上に切り分けるのですから切断機の刃（砥石）の厚さを考慮する必要があります。サンプルでは余りが30cm程度になっていますが、金ノコで切るならこれだけ余裕があれば十分と思いますが、切断機の場合はこれでは不十分だと思います。砥石の厚さを考慮する場合は、B1(422)、C1(45)の値を砥石の厚さ分プラスしてください。

パラメータがたくさんあって、自動的に最適な数値を求めることは難しいので、サンプルのシートを使って試行錯誤してください。

No.2 回答者： KURUMITO 回答日時： 2012/02/14 07:50

文章と表が一致していませんね。

どちらが正しいのでしょう。

No.1 回答者： bin-chan 回答日時： 2012/02/13 23:21

「ゴールシーク」という機能が役立つかも。

のこ歯の削り代も含めて計算してください。