エクセルの関数で計算できますか？

質問

数学？2500mmの長さの物から複数の長さを切り出します。
353を250本1003を42本898を26本503を180本といった具合です。
エクセルなどで数式計算する方法ってありますか？

回答No.4

arinth

こんにちは、もう見ておられないかも。

Excel関数だけではこの種の問題を解くのは無理のような気がします。
それは基本的に反復処理が必要なためです。
やはりVBA等のプログラムを作成するしかないでしょうね。
提示された数値例は1例と思いますが、切出す長さの必要数が結構
多いので計算するのはかなり大変でしょう。

次のものはその数値例の最適解の1つです。

P1　21本　[ 1003 * 2, 353 * 1]
P2　26本　[ 898 * 1, 503 * 3]
P3　25本　[ 503 * 4, 353 * 1]
P4　29本　[ 353 * 7]
P5　1本　 [ 503 * 2, 353 * 1]

切出すパターンは5種類あります。(P1～P5)
1本の材料から切出す内訳を、[ ]の中に書いてます。
最初の例(P1)では、1003mmのものを２本、353mmのものを1本切出す。
切出しパターンP1で21本切出します。
同様にして、各パターンで指定本数を切出します。

切り出す長さの総和は 244264mmなので、2500mmの材料は最低
98本必要ですが、実際は上記のように 102本となります。

Excelソルバーを利用した参考になるサイトのURLを忘れたため
今転記できないので思い出せたら別途連絡します。
ちなみにこのような問題は、材料切出し問題、材料取合せ問題と言われて
います。cutting stock問題でネット検索するといろいろ参考になる
サイトが見つかるでしょう。

回答No.3

kagakusuki

長さが異なる部材が3種類以上ある場合には、「各部材を何本ずつ取るのが最適なのか？」という事を、数学的に求める方法は存在しません。
　余りが最短となる組合せを最適とした場合においても、余りの長さが等しくなる組合せが複数存在するかもしれませんから、一律には決める事は出来ません。
　只、Excelを利用して、「切出す事の出来る全ての組合せを、リストに表示する」事であれば可能です。
　多数ある組合せの中から、どの組合せにするのかは、人間側の都合によって変わりますから、パソコンが勝手に判断して決める事は出来ず、組合せを選択して決めるのは人間の仕事になります。

　例えば、添付画像の方法では、B1セルに元となる材料（以下「原材」と仮称）の長さを入力し、A2以下に切出す各部材の名称を入力し、B2以下に切出す各部材の長さを、それぞれ入力しますと、
D3セルに原材の長さが表示され、
E3から右方向に向かって、各部材の長さが、長いものから順番に表示され、
D列よりも右側にある列の6行目以下には、切出す事が可能な、全ての組合せが表示され、
D6以下には、各組合せごとの、切出した後に残る余った部分の長さが表示されます。
　尚、D1よりも右側にあるセルに表示されている数字は、各部材の長さの順位です。

　この様なリストを自動的に作成させるための方法は、以下の様なものです。
　まず、A1セルに「原材」、D2セルに「原材長」、D5セルに「余り長」、と入力して下さい。
　次に、D3セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/SQRT($B$1)),$B$1,"")

　次に、E3セルに次の数式を入力して下さい。

=IF(OR($D$3="",COUNT($B:$B)<COLUMNS($D:E)),"",LARGE(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B)),COLUMNS($E:E)))

　次に、E1セルに次の数式を入力して下さい。

=IF(ISNUMBER(E$3),RANK(E$3,(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B)))),"")

　次に、E2セルに次の数式を入力して下さい。

=IF(ISNUMBER(E$3),INDEX($A:$A,SUMPRODUCT(ROW(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B)))*(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B))=E$3)*(COUNTIF(OFFSET(INDEX($B:$B,ROW($A$1)),1,,ROW(INDEX($B:$B,ROW($A$1)+1):INDEX($B:$B,MATCH(9^9,$B:$B)))-ROW($A$1)),E$3)=COUNTIF($E$3:E$3,E$3))))&"長","")

　次に、E5セルに次の数式を入力して下さい。

=IF(E$2="","",SUBSTITUTE(E$2,"長","数量",LEN(E$2)-LEN(SUBSTITUTE(E$2,"長",))))

　次に、E1～E5の範囲をコピーして、F1～F5の範囲に貼り付けて下さい。
　次に、E6セルに次の数式を入力して下さい。

=IF(ISNUMBER(E$3),INT($D$3/E$3),"")

　次に、F6セルに次の数式を入力して下さい。

=IF(ISNUMBER(F$3),INT(($D$3-SUMPRODUCT($E$3:E$3*$E6:E6))/F$3),"")

　次に、E7セルに次の数式を入力して下さい。

=IF(ISNUMBER(E6),IF(SUM(E6:INDEX(6:6,MATCH(9^9,$3:$3)))<=E6+INDEX(6:6,MATCH(9^9,$3:$3)),IF(E6=0,"",E6-1),E6),"")

　次に、F7セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(F$3),ISNUMBER($E7)),IF(OR(G$3="",SUM(F6:INDEX(6:6,MATCH(9^9,$3:$3)))=INDEX(6:6,MATCH(9^9,$3:$3))),INT(($D$3-SUMPRODUCT($E$3:E$3*$E7:E7))/F$3),F6-(SUM(F6:INDEX(6:6,MATCH(9^9,$3:$3)))<=F6+INDEX(6:6,MATCH(9^9,$3:$3)))),"")

　次に、F1～F7の範囲をコピーして、同じ行の右方向に向かって（「1本の原材から切り出す部材の種類の数」を上回るのに充分な列数だけ）貼り付けて下さい。
　次に、D6セルに次の数式を入力して下さい。

=IF(OR($D$3="",COUNT($B:$B)<2),"",$D$3-SUMPRODUCT($E$3:INDEX($3:$3,MATCH(9^9,$3:$3))*$E6:INDEX(6:6,MATCH(9^9,$3:$3))))

　次に、D6セルコピーして、D7セルに貼り付けて下さい。
　次に、7行目全体をコピーして、8行目以下に貼り付けて下さい。

　枠線は適時設定して下さい。

　これで準備は完了で、後はB1セルに原材の長さを、A2以下に切出す各部材の名称を、B2以下に切出す各部材の長さを、それぞれ入力しますと、切出す事が可能な、全ての組合せのリストが自動的に表示されます。

お礼

数式と入力方法の解説、及び私の足らない説明への配慮
ありがとうございました。

ご教授頂いた表を活用して行きます。

回答No.2

aokisika

Ｎｏ．１です。

2500ｍｍがあまり多くなければ、１．で答えたのと同じものをコピーペーストで本数分つくり、試行錯誤で調べることはできます。
基本的に、「ある組み合わせでとれるか？」を調べるだけですから、「取れる組み合わせをすべて列挙せよ」といったことは無理です。

お礼

ご回答ありがとうございました。

組み合わせ工夫したいと思います。

とても助かりました。

回答No.1

aokisika

こんなのはどうですか？

まず、以下の語句をセルに入力します。

Ｂ４：残りの長さ
Ｂ５：元の長さ
Ｂ６：合計
Ａ６：切る長さ
Ｂ６：本数
Ｃ６：小計

次にＣ：７に　=A7*B7
を入力し、これをコピーしてＣ：８からＣ：２０にペーストします。

Ｃ：４に　=SUM(C7:C20)
を入力

Ｃ：２に　=C3-C4
を入力します。

Ｃ：３に元の長さ２５００を入力し、
Ａ：７以降に切る長さ、Ｂ：７以降に本数を入力すると、
Ｃ：７以降に、それぞれの切った長さの合計が表示され、
Ｃ：４に全体の合計が表示され、
Ｃ：２に残りの長さが表示されます。

ちなみに、３５３を２５０本切ると、それだけで８８２５０になり、２５００ｍｍをオーバーしてしまいます。

補足

2500mmが複数本あります。
その中から指定の長さのモノを複数種類切り落としていき
組み合わせ後、2500mmからはどれとどれの組み合わせが取れるか？
といった回答は可能でしょうか。