Excelで加工の最適解を求めたい

質問

部品加工をしていますが、最適な解の求め方について質問があります。

条件１：　機械は４台あり、それぞれに加工できる部品と１個加工する為の時間が違います。
　　　　　　詳細は、画像をご覧下さい。
　　　　　　（「－」は、その機械でその部品の加工ができない事を表します。）

条件２：　最終的に加工しなければならない部品の数は、Ａ～Ｅとも、おのおの違います。
　　　　　　（例の場合だけでなく、１００～５０００程度の間で、日によってバラバラな値です。）

条件３：　部品が変わる場合の設定変更の時間は考慮しなくてかまいません。

条件４：　一つの機械では同時に複数の種類の部品を加工できません。

条件５：　複数の機械で同じ種類の部品を同時に加工できるだけ材料に余裕があります。

Ｅｘｃｅｌを用いて、それぞれの機械が（ほぼ）同じ時間稼働するように、
それぞれの機械でそれぞれの部品を加工する個数を計算させたいのですが、
どのような計算式にすればよいでしょうか。

極力、マクロ、ゴールシーク、ソルバーを使わないでワークシート上の計算式のみで
できるものを教えて頂きたいです。

回答No.4

neKo_deux · ベストアンサー

> 機械No.2では、A・B・Cを同時に加工する事はできないので、

だと、問題の条件を勘違いしていました。
そうなると、ちょっと条件のややこしいナップザック問題になると思います。
この手の問題は、解析的に解くのは難しいのが知られています。

質問の例なら、例えば部品B,Cを作るのは機械No.2だけですからとっかかりはありますが、一般化した場合には厳しいです。

計算式だけって事だと、目標値に収束するような逐次計算をシートの行を使って行うとかでしょうが、ちょっと条件ややこしいので、ちょっとお手上げです。

お礼

ありがとうございました。
Excelの枠からはみ出してしまいますが「ナップザック問題」を調べてみようと思います。

回答No.3

neKo_deux

> しかしながらこの時間では、質問の趣旨である「それぞれの機械でそれぞれの部品を何個加工すれば、機械の稼働時間が揃ってくるのか」が全く分かりません。

それぞれの機械での加工時間が分かったんですから、掛け算するだけです。
全部の機械をK9の21.4時間動かすんですから、
機械No.1は、
　部品Aの93個×21.4時間を切り上げて、1991個
機械No.2は、
　部品Aの84個×21.4時間を切り上げて、1798個
　部品Bの84個×21.4時間を切り上げて、1798個
　部品Cの84個×21.4時間を切り上げて、1798個
機械No.3は、
　部品Aの105個×21.4時間を切り上げて、2247個
　部品Dの105個×21.4時間を切り上げて、2247個
　部品Eの105個×21.4時間を切り上げて、2247個
機械No.4は、
　部品Dの99個×21.4時間を切り上げて、2119個
　部品Eの99個×21.4時間を切り上げて、2119個
とか。

Excelの切り上げはROUNDUP関数を使います。

補足

重ねて感謝いたします。

しかしながら、この計算も成り立ちません。

機械No.2では、A・B・Cを同時に加工する事はできないので、
どれか１部品だけ加工することになります。

また、機械No.1、No.2、No.3とも、部品Aを２１．４時間加工してしまうと、
６０００個を超えてしまい、作りすぎです。
品物を作りすぎてはいけません。ぴったり指示数通りに作りたいのです。

お知恵を頂きたく思います。

回答No.2

neKo_deux

> それぞれの機械が（ほぼ）同じ時間稼働するように、

解析的に解くんなら、そういう曖昧な条件は処理できないので、同じ時間稼動することにしちゃいましょう。
５５＋△がほぼ１００になるような△は？って問題で、４５以外の回答をって言われても困ります。

--
F列は空けて、

G列に、すべての機械を1時間稼動した場合の加工数を、
G2:=SUM(B2:E2)
G3:=SUM(B3:E3)
G4:=SUM(B4:E4)
G5:=SUM(B5:E5)
G6:=SUM(B6:E6)
で計算。

H列は空けて、

I列に加工すべき数を転記

J列は空けて、

K列に、必要な加工時間を、
K2:=I2/G2
以下コピペ
で計算。

最低限必要な加工時間は上の最大値なので、
K9:=MAX(K2;K6)

とか。

補足

ありがとうございます。

しかしながらこの時間では、質問の趣旨である「それぞれの機械でそれぞれの部品を何個加工すれば、機械の稼働時間が揃ってくるのか」が全く分かりません。

補足回答をお願いします。

回答No.1

trytobe

「（ほぼ）同じ時間」としても、最適解を試行錯誤なしの「計算式のみ」で表すのは不可能だと思います。

ただ、最適ではないが、自動的に導かれるにしてはかなりいい線までいく（あとは人が微調整するのは短時間ですむ）という手はあるかもしれません。

まず、部品B と 部品C を加工できるのは機械No.2 （８４個／時間）しかないので、部品B,C は機械No.2 に割り当てます。

部品D と 部品E は、機械No.3（１０５個／時間） と 機械No.4（９９個／時間） で作れますが、一旦は機械No.4 に割り当てます。

部品A は、機械No.1（９３個／時間） 以外にも割り当てられますが、今のところ上で埋まっているマシンがあるので、一旦は機械No.1 に割り当てます。

すると、機械No.2 が空いたときには、部品A の併産ができるようになります。機械No.3 が空いたときには、部品D,E の併産ができるようになります。

部品A がそれでも間に合わないようなら、機械No.3 が部品D,E の併産を終えた後にも 部品A の生産に回れます。

以上の順番にわりあてれば、どんな部品必要生産量に対しても、とりあえずそれなりのスケジュールができます。

ただ、先に申しましたように最適解とは限りませんし、別の部品F を作る場合や、機械No.1～4 の能力変更などがあれば、再度、冒頭からの要領で考え直して式を立て直さねばなりません。