[[20171010160137]] 『「組み合わせを選ぶ」[みすたーびーん] について』(まきげのたか) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『「組み合わせを選ぶ」[みすたーびーん] について』(まきげのたか)

投稿
[[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


長さを変えるだけで良いならば、Integer型で4mを宣言していますが、これをLong型に変えれば、もっと大きい値でも指定できますよ。
(Integer型では、32767までしか指定できません)
(???) 2017/10/12(木) 09:06

(カリーニン)さん、参考になりました、ありがとうございます。

過去ログや検索してみました、ナップサック・カッティングストック・ピン・・・など
奥が深かったのですね。他のログのプログラムを試そうと思いましたがスキルが足りません。
勉強したいと思います。

(半平太)さん、コメントありがとうございます。

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


私が以前作ったコード([[20160329175750]])に、ご提示の寸法を入力してみたところ、以下の解になりました。 いかがでしょうか?

20000の素材4本を使用。

11000 + 8600 = 19600(残400)
8600 + 8600 = 17200(残2800)
8200 + 8200 = 1640(残3600)
4400 + 4400 + 4400 = 13200(残6800)

残材の寸法を、在庫に追記することで、残材の再利用計算ができると思います。
(???) 2017/10/12(木) 18:25


残材に追記して試してみましたが、残材使ってくれませんでした…。前の条件は、大きい素材から使う事だったので、小さいのから使うとなると、かなり手を入れないと駄目っぽいですね。 まぁ、もの凄くロジックが難しいものだ、というのは実感して頂けるかと思います。
(???) 2017/10/12(木) 18:34

 >20000の製品が4つあれば切り分けれますが、その残を次の2フロア目に有効的に使えないかなと思ってました。

 成程、端材の有効利用は当然のことなんですね。納得です。

 そうなると、作業が進むにつれ、色んな(規格外の)寸法の材料が出てきますが、
 それって、完全に分別管理が出来る態勢にあるんですか?

 計算の方は、余り多数種になると計算量の方も増えます(もしくは、精度が落ちてきます)

 なんとなく、現場では、一定範囲の長さのものは、同じ部材にしている様な気がするんですけど、
 そうなっていないですか?

 例えば、4000以上は4000物として○本在庫有
     5000以上は5000物として○本在庫有
      :     :     :
     19000以上は19000物として○本在庫有

           20000物(足りない分は必要なだけ追加注文)

 てな在庫管理なのかな、と思っているんですが・・・

 それだと、部材の種類が途方もなく増えることは心配しないで済むので
 こちらとしては、少しホッとします。

 <追記>
 あと、再利用できない部材(つまり、廃棄するしかない部材)の最長寸法ってないですか?

(半平太) 2017/10/12(木) 19:27


(???)さん、ありがとうございます。
>私が以前作ったコード([[20160329175750]])に、ご提示の寸法を入力してみたところ、以下の解になりました。 いかがでしょうか?

大変助かります、手作業より早く、視覚的にも見やすく、次と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

1FのみCタイプ 1 HPPC 7500 5000
	     	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


私の過去のマクロを動かす場合、必要なマクロは最後のtest7だけで良いですが、シートを3つ(在庫、最適計算、詳細)使います。シート名を変更していないのではないでしょうか?(レイアウトは、過去の元質問にあります)

設定は、「在庫」の製品名は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.