[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『「組み合わせを選ぶ」[みすたーびーん] について』(まきげのたか)
投稿
[[20030816230207]] 『組み合わせを選ぶ』(みすたーびーん)
について...
組み合わせで検索してこの投稿にたどり着きました。
設備屋なんで大変参考になり、マクロ初心者ですが
なんとかこのマクロを起動することができて嬉しいです。
このプログラムについてなのですが、配管4mのみではなく
ペアコイル20mや、電線100mなどに数値を変更するには
どうすればよいのでしょうか?
Const b_parts As Integer = 4000 の所を4ケタ以内の変更なら起動するみたいですが・・・
切断寸法の項目も7項目以上にしたいです。
宜しくお願い致します。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
「材料 切断」で過去ログ検索すると、参考過去ログが沢山ヒットすると思います。 その過去ログで、フリーウェア、シェアウェアのツールが紹介されていると思います。
直接紹介してもいいのですが、以前、紹介したらそのツールの開発者が直接スレッド に登場して広告のようなのを始めたので、以降はそのツールの直接紹介は控えており ます。 (カリーニン) 2017/10/10(火) 19:44
検索キーワード「ナップサック」でも検索してみてください。 (カリーニン) 2017/10/10(火) 19:49
>設備屋なんで大変参考になり
そうなんですか。 私は、どう活用するのかさっぱり分かりませんでした。(-_-;)
>このプログラムについてなのですが、配管4mのみではなく >ペアコイル20mや、電線100mなどに数値を変更するには
1.ペアコイルとか電線とか、素材に意味がありそうな雰囲気が出ているんですが、 単なる長さの違いの話なのでしょうか?
2.電線だったら「切削ロス」なんて無いですよね?
3.引用されたトピックで論じられていましたが、 ロスの意味を詰めた方がいいんじゃないでしょうか?
細かい半端は、ロスそのものなんでしょうが、 長い半端は、1本くらい出ても、別にマイナスに考えることも無いんじゃないですか?
そりゃ、そういう物も出ないに越したことはないですが、 存続を前提とする会社において、十分有効なストックになるんじゃないですか?
そんな半端には目をつぶることにすれば、計算が少しは楽になると思うのですけども・・ どんなもんでしょう?
(半平太) 2017/10/12(木) 00:09
過去ログや検索してみました、ナップサック・カッティングストック・ピン・・・など
奥が深かったのですね。他のログのプログラムを試そうと思いましたがスキルが足りません。
勉強したいと思います。
(半平太)さん、コメントありがとうございます。
1、素材に意味ありません、長さの違いです。
2、切断ロスはないです。(ロスも含んだ切断長さ)
3、その通りです。が今回は量がちょっと多くてエクセルでできないかなと検索してた次第です。
内容としては
2階から9階まで8フロアありまして、1フロアずつ作業します。
1フロアに必要なコイル長さは 8200×2本 11000×1 8600×3 4400×3 です。
これを20000の製品から切り分けます。
20000の製品が4つあれば切り分けれますが、その残を次の2フロア目に有効的に使えないかなと思ってました。
9階まで終わるとまた2階から4000×5 5000×・・・・と2サイクルあるので
(まきげのたか) 2017/10/12(木) 17:42
20000の素材4本を使用。
11000 + 8600 = 19600(残400)
8600 + 8600 = 17200(残2800)
8200 + 8200 = 1640(残3600)
4400 + 4400 + 4400 = 13200(残6800)
残材の寸法を、在庫に追記することで、残材の再利用計算ができると思います。
(???) 2017/10/12(木) 18:25
>20000の製品が4つあれば切り分けれますが、その残を次の2フロア目に有効的に使えないかなと思ってました。
成程、端材の有効利用は当然のことなんですね。納得です。
そうなると、作業が進むにつれ、色んな(規格外の)寸法の材料が出てきますが、 それって、完全に分別管理が出来る態勢にあるんですか?
計算の方は、余り多数種になると計算量の方も増えます(もしくは、精度が落ちてきます)
なんとなく、現場では、一定範囲の長さのものは、同じ部材にしている様な気がするんですけど、 そうなっていないですか?
例えば、4000以上は4000物として○本在庫有 5000以上は5000物として○本在庫有 : : : 19000以上は19000物として○本在庫有
20000物(足りない分は必要なだけ追加注文)
てな在庫管理なのかな、と思っているんですが・・・
それだと、部材の種類が途方もなく増えることは心配しないで済むので こちらとしては、少しホッとします。
<追記> あと、再利用できない部材(つまり、廃棄するしかない部材)の最長寸法ってないですか?
(半平太) 2017/10/12(木) 19:27
大変助かります、手作業より早く、視覚的にも見やすく、次と2サイクル目のイメージがしやすいです。
しかしコードを実行するためのスキルが足りません。
エクセル2010 開発ボタンを表示して、マクロの記録押してOK、マクロボタンを押して編集
コード([[20160329175750]])の Sub test7()からEnd Subまでのプログラムをコピペ
再生ボタン押す
実行時エラー’g’:
インデックスが有効範囲にありません
と出ます。
sheetを設定しないとだめなのかな、そもそもの基本が判らなくすみません。
検索してみます^^
(まきげのたか) 2017/10/12(木) 19:47
詳細を書くとこんな感じです
部屋のタイプ 戸数 使用機種 室内数量 室外数量(2サイクル目)
Cタイプ 8 HPPC 8200 4500
ACPC 8200 3700 Dタイプ 8 HPPC 11100 - ACPC - 3500 Aタイプ 8 HPPC 8600 4500 ACPC 4400 3600 A’タイプ 8 HPPC 8600 3700 ACPC 4400 2600 Bタイプ 8 HPPC 8600 4500 ACPC 4400 3600
ACPC 7500 4200
この現場では2600が最短なのでそれ未満は使える現場がでるまで在庫か大掃除時に廃棄処分です
在庫管理はやはり上記のように
1フロアごとに
20000の素材4本を使用。
11000 + 8600 = 19600(残400)
8600 + 8600 = 17200(残2800) 8200 + 8200 = 1640(残3600) 4400 + 4400 + 4400 = 13200(残6800)
切断してしまいますね。400は廃棄で残りはそのままのmmでの在庫管理となるでしょう。
こう詳細で整理すると2サイクル目に使えそうに見えてきますね^^
ありがとうございます。
(まきげのたか) 2017/10/12(木) 22:09
>残りはそのままのmmでの在庫管理となるでしょう。
その方針だと、PCの計算結果はだんだん粗雑になっていかざるを得ないと思われます。
端材を自動的に転記して、次のフロアの計算の備えをする様なものを考えていましたが、 ちょっと無理そうなので、以下、ベーシックな部分だけとします。
端材は平均的に短いものが多い方が、ベターと判定する基準です。
※1本の長い端材とうんと短い端材が出る方がベターとする基準もありそうですが、 当方(部外者)が考えても妄想になるだけなので、あれこれ検討いたしません。
<使い方> 所要データを入力して(※1)、「A1セルを右クリック」すると結果が現れます。
<実行前> 行 _____A_____ _______B_______ ___C___ ___D___ __E__ __F__ 1 計算実行 2 所要 寸法 8,200 11,000 8,600 4,400 3 (KINDS) 本数 2 1 3 3 4 5 6 在庫 寸法 20,000 7 (KINDS) 本数 8 9 10 参考 REFNO オリジナル寸法
<A1セルをクリックしてマクロを実行した結果図> 1例です。常に同じになるとは限りません。 行 _____A_____ _______B_______ ___C___ ___D___ __E__ __F__ ___G___ 1 計算実行 2 所要 寸法 8,200 11,000 8,600 4,400 3 (KINDS) 本数 2 1 3 3 4 5 6 在庫 寸法 20,000 7 (KINDS) 本数 8 9 10 参考 REFNO オリジナル寸法 2 1 3 3 残余寸法 11 1 20,000 1 1 3,200 12 2 20,000 1 1 4,600 13 3 20,000 2 2,800 14 10 20,000 1 2 3,000
(※1) 所要データは、基本材の寸法ををC6セルに書いてください。 その下は空白にしてください。
基本材以外は、D6セルから右に書き込んでください。 こっちは、その下に必ず本数を書いてください。
例 行 ____A____ __B__ ___C___ ___D___ __E__ __F__ 1 計算実行 2 所要 寸法 8,200 11,000 8,600 4,400 3 (KINDS) 本数 2 1 3 3 4 5 6 在庫 寸法 20,000 13,600 4,800 7 (KINDS) 本数 1 1
<A1セルをクリックしてマクロを実行した結果図> 1例です。常に同じになるとは限りません。
行 _____A_____ _______B_______ ___C___ ___D___ __E__ __F__ __G__ 1 計算実行 2 所要 寸法 8,200 11,000 8,600 4,400 3 (KINDS) 本数 2 1 3 3 4 5 6 在庫 寸法 20,000 13,600 4,800 7 (KINDS) 本数 1 1 8 9 10 参考 REFNO オリジナル寸法 2 1 3 3 残余寸法 11 1 20,000 1 1 800 12 2 20,000 2 2,800 13 3 20,000 1 1 3,200 14 10 13,600 3 400
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
マクロは2種類あります。 いずれもシートモジュールに書いてください。つまり、標準モジュール上ではない(重要)
1つ目は、レイアウトを自動転記するものです。
<自動転記手順> 新規シートの「シート見出し」を右クリックして、「コードの表示(V)」を選ぶと 画面中央に白いエリアが表れます。(VBE画面です)
その白いエリアに後記マクロ(onlyOnce)をコピぺし、F5キーを押下してください.
(すると、マクロ「onlyOnce」が実行され、自動的に所要データが入力されます)
※実行は1回だけですので、終わったら「Ctrl+Z」でコードを消去してください。 ALT+F11でエクセルに戻れます。
Private Sub onlyOnce() Rem 生データのセルをまとめて処理 Range("A1").Value = "計算実行" Range("A2").Value = "所要" Range("B2,B6").Value = "寸法" Range("C2").Value = 8200 Range("D2").Value = 11000 Range("E2").Value = 8600 Range("F2").Value = 4400 Range("A3,A7").Value = "(KINDS)" Range("B3,B7").Value = "本数" Range("C3").Value = 2 Range("D3").Value = 1 Range("E3:F3").Value = 3 Range("A6").Value = "在庫" Range("C6").Value = 20000 Range("A10").Value = "参考 REFNO" Range("B10").Value = "オリジナル寸法"
Rem 標準外書式セルをまとめて処理 Range("C2:F3,C5:F10").NumberFormatLocal = "#,##0;[赤]-#,##0"
Range("A1").Interior.ColorIndex = 24 End Sub
2つ目が本題です。さっきonlyOnceを貼り付けた所に貼り付けてください。 これは消去しない。Alt+F11 でエクセルに戻ってください。
Private Type Stock Zecke As Long OrgLen As Long CurLen As Long Consumed As Long End Type
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$1" Then Cancel = True Call Cutting End If End Sub
Private Sub Cutting() Dim Stocks() As Stock '在庫内訳 Dim Parts() '所要寸法*所要数の配列 Dim 本数AryScale, LenAryScale Dim 本数AryStock, LenAryStock Dim numOfParts As Long '総パーツ数 Dim numOfKinds As Long '総種類数 Dim numOfPartsStock As Long '総在庫パーツ数 Dim numOfKindsStock As Long '総在庫種類数 Dim OrderToCut '切る方(所要寸法) Dim OrderBeCut '切られる方(在庫材) Dim i As Long, NN As Long, KK As Long Dim Trial As Long Dim OrgParts() Dim OrgStocks() As Stock Dim Org本数AryScale, OrgLenAryScale Dim Org本数AryStock, OrgLenAryStock() Dim OrgNumOfPartsStock As Long Dim stockIdx Dim rngScale As Range '所要寸法 Dim BestScore, BestV '出来栄えチェック用 Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
Randomize
'切出しデータを格納する Set rngScale = Range("C2", Cells(2, "Z").End(xlToLeft)) LenAryScale = rngScale.Value 本数AryScale = Range("C3", Cells(3, "Z").End(xlToLeft)).Value
' ReDim OutAry(1 To rngScale.Columns.count + 3)
numOfParts = Application.Sum(本数AryScale) '切出す総数 numOfKinds = Application.count(本数AryScale) '切出す種類数
ReDim Parts(1 To numOfParts) '切出しファクター格納用
i = 0 For NN = 1 To numOfKinds '総本数と同じ数だけParts配列に格納する For KK = 1 To 本数AryScale(1, NN) i = i + 1 Parts(i) = LenAryScale(1, NN) Next KK Next NN
Rem 在庫状況を格納する(最低2列以上にはする) If IsEmpty(Range("D6").Value) Then '一種類しかない場合 numOfKindsStock = 2 '最大長を2列にする
ReDim 本数AryStock(1 To 1, 1 To 2) ReDim LenAryStock(1 To 1, 1 To 2) LenAryStock(1, 1) = Range("C6").Value LenAryStock(1, 2) = LenAryStock(1, 1) 本数AryStock(1, 2) = 1
Else 'ノーマルベースな情報 '切出されるデータを格納する LenAryStock = Range("C6", Cells(6, "Z").End(xlToLeft)).Value 本数AryStock = Range("C7", Cells(7, "Z").End(xlToLeft)).Value
本数AryStock(1, 1) = numOfParts End If
本数AryStock(1, 1) = numOfParts '所要パーツ総数と同じにする(無尽蔵と等価) numOfPartsStock = Application.Sum(本数AryStock) '切出される総数(+1の時あり) numOfKindsStock = Application.count(LenAryStock) '切出される種類数(+1の時あり)
ReDim Stocks(1 To numOfPartsStock)
i = 0 For NN = 1 To numOfKindsStock '初回は総本数の分だけParts配列に格納する For KK = 1 To 本数AryStock(1, NN) i = i + 1 Stocks(i).Zecke = i Stocks(i).OrgLen = LenAryStock(1, NN) Stocks(i).CurLen = Stocks(i).OrgLen Next KK Next NN
'オリジナル状態を温存 OrgParts = Parts OrgStocks = Stocks Org本数AryScale = 本数AryScale OrgLenAryScale = LenAryScale Org本数AryStock = 本数AryStock OrgLenAryStock = LenAryStock OrgNumOfPartsStock = numOfPartsStock
For Trial = 1 To 500 '500を増やせば精度は上がるが、時間が掛かる
'オリジナル状態に復帰 Parts = OrgParts Stocks = OrgStocks 本数AryScale = Org本数AryScale LenAryScale = OrgLenAryScale 本数AryStock = Org本数AryStock LenAryStock = OrgLenAryStock numOfPartsStock = OrgNumOfPartsStock
'所要本数の切取り順序を決定する OrderToCut = getShuffuledOrder(numOfParts, 0)
'在庫材の切取られ順序を決定する(半端材を先にランダムに並べた後、無尽蔵材を順番に並べる) OrderBeCut = getShuffuledOrder(numOfPartsStock, 本数AryStock(1, 1))
For KK = 1 To numOfParts For NN = 1 To numOfPartsStock stockIdx = OrderBeCut(NN)
If Stocks(stockIdx).CurLen >= Parts(OrderToCut(KK)) Then
Stocks(OrderBeCut(NN)).CurLen = Stocks(stockIdx).CurLen - Parts(OrderToCut(KK)) '残長更新
numOfPartsStock = numOfPartsStock + 1 '切出した部分を配列の最後尾に追加 ReDim Preserve Stocks(1 To numOfPartsStock)
Stocks(numOfPartsStock).Zecke = Stocks(stockIdx).Zecke Stocks(numOfPartsStock).OrgLen = Stocks(stockIdx).OrgLen Stocks(numOfPartsStock).Consumed = Parts(OrderToCut(KK))
Exit For End If Next NN Next KK
'余りを計算する。何回かトライして、一番少ないものを採用する。 集計 Stocks, rngScale, BestScore, BestV, Dict
Dict.RemoveAll Next Trial
'打ち出し Rows("10:100").ClearContents
With Cells(11, "A").Resize(UBound(BestV) + 1, UBound(LenAryScale, 2) + 3) .Value = Application.Index(BestV, 0, 0) .Offset(-1, 2).Resize(1, .Columns.count - 3).Formula = "=SUM(C11:C100)" .Offset(-1).Range("A1:B1").Value = Array("参考 REFNO", "オリジナル寸法") .Offset(-1, .Columns.count - 1).Resize(1, 1).Value = "残余寸法" End With End Sub
Private Sub 集計(Stocks() As Stock, rngScale As Range, BestScore, BestV, Dict As Object) Dim NN As Long Dim temp As Stock Dim Pos Dim tempV Dim ary() Dim Score, OddMat
For NN = 1 To UBound(Stocks)
temp = Stocks(NN)
If temp.Consumed > 0 Then '使用された材料 '適用長 Pos = Application.Match(temp.Consumed, rngScale, 0) + 2
If Dict.exists(temp.Zecke) Then ary = Dict(temp.Zecke) ary(Pos) = ary(Pos) + 1 Else ReDim ary(1 To rngScale.Columns.count + 3) '初期化 ary(1) = temp.Zecke ary(2) = temp.OrgLen ary(Pos) = 1 End If
Dict(temp.Zecke) = ary
ElseIf temp.CurLen < temp.OrgLen Then If Dict.exists(temp.Zecke) Then ary = Dict(temp.Zecke) ary(UBound(ary)) = ary(UBound(ary)) + temp.CurLen Else ReDim ary(1 To rngScale.Columns.count + 3) '初期化 ary(1) = temp.Zecke ary(2) = temp.OrgLen ary(UBound(ary)) = temp.CurLen End If
Dict(temp.Zecke) = ary End If Next NN
tempV = Dict.items OddMat = Application.Index(tempV, 0, UBound(ary))
Score = Application.SumProduct(OddMat, OddMat)
If IsEmpty(BestScore) Or Score < BestScore Then BestScore = Score BestV = tempV End If
End Sub
Private Function getShuffuledOrder(ByVal NumofPiece As Long, SimpleAddNum As Variant) '順番をランダムにする。ただし、単純に順番追加する番号は最後尾に並べる Dim NN As Long Dim 乱数rand() Dim OrderInArow Dim temp Dim realOrder Dim Temp2()
realOrder = NumofPiece - SimpleAddNum OrderInArow = Evaluate("ROW(1:" & realOrder & ")")
ReDim 乱数(1 To realOrder)
For NN = 1 To realOrder 乱数(NN) = Rnd Next NN
With Application temp = .Transpose(.Match(.Small(乱数, OrderInArow), 乱数, 0)) End With
If SimpleAddNum > 0 Then ReDim Preserve temp(1 To NumofPiece)
For NN = 1 To NumofPiece If NN <= realOrder Then temp(NN) = temp(NN) + NumofPiece - realOrder Else temp(NN) = NN - realOrder End If Next NN End If
getShuffuledOrder = temp End Function
(半平太) 2017/10/12(木) 23:54
上記、差し替え後です。2017/10/13(金) 08:52
設定は、「在庫」の製品名はAとでもして、在庫数4、長さ20000という感じに1行入力しておきます。
「最適計算」の製品名もAというように在庫に合わせておき、切断後の個数と長さを4種類分入力。 それからマクロ実行してみてください。
(???) 2017/10/13(金) 09:26
> 端材は平均的に短いものが多い方が、ベターと判定する基準です。
と言う方針にしてみたものの、 長さがピッタリの部材があったら、 さすがに、それを優先的に使うべきだったですねぇ。 (そう言う作りにはなっていないです)
ただそうなると、たとえピッタリじゃなくても、 ほんの少し大きい部材も優先的に使うべきではないか、となりそう。
けど「ほんの少し」とは具体的にどれだけか、となると悩ましい。
暇ができたら考えてみます。
(半平太) 2017/10/13(金) 09:34
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.