スポンサーリンク

簡易所要量計算(Excel/VBA)

道具箱

 製品構成展開されたツリーデータを使用して、部品の所要量製品を組み立てるために必要な部品の必要個数)を 計算するExcel/VBAを説明します。
 製品の最上位の部品から下位の部品へと構成展開をしながら、組立毎の必要個数を累計して計算します。※複数の組立品で使用される同一部番毎の集計や在庫の引き当て等はしていません。

 まず、Excelには以下のようなデータがあることを前提としています。
 (PLMの構成展開機能等で抽出できます。

図 製品構成図と部品表


1行目
 列見出:階層(Level),部番,数量,所要量
   数量:TOPは製品の製作数
      以降は、1組立当たりの必要個数
   例えば、製品1個当たり、ASSY1は2個必要
   ASSY1の1個当たり、PART1は5個必要
2行目以降
 製品Topから階層順に親子孫…

0,TOP,2
1,ASSY1,2
2,PART1,5
2,PART2,5
1,ASSY2,3
2,PART3,5
2,PART4,5


VBAを実行して計算すると、製品を製作数製造するために必要な部品の所要量が4列目に入ります。
0,TOP,2,5
1,ASSY1,2,10 (5×2)
2,PART1,5,50 (10×5)
2,PART2,5,50 (10×5)
1,ASSY2,3,15 (5×3)
2,PART3,5,75 (15×5)
2,PART4,5,75
(15×5)

以下に所要量を計算するVBAのサンプルコードと要点の説明を記載します。
コピーしてExcelのVBエディタに貼り付けて実行して下さい。

Sub SetSyoyou()
Dim QTY_S As Long
Dim QTY_Pre(20) As Long
Dim Level,Level_pre,Row As Long
Dim PartNo As String
Set strShtName=ActiveSheet
Row=2
PartNo=strShtName.Cells(Row,2).Value
'部番が空白になるまで繰り返す。
Do While Not(PartNo="")
'現在行のLevel(1列目)と前行のLevel(1列目)を取得する。
Level=strShtName.Cells(Row,1).Value
Level_Pre=strShtName.Cells(Row-1,1).Value
If Level=0 Then
    '製品の所要量を計算する。
    If strShtName.Cells(Row,3).Value<>""Then
      QTY_S=strShtName.Cells(Row,3).Value
    Else
      QTY_S=1
    End If
    Level_Pre=0
Else
  'Levelが下がった場合、現所要量をひとつ上のLevelの親所要量に保持する。
  If Level_Pre<Level Then
      QTY_Pre(Level-1)=QTY_S
  End If
  'ひとつ上のLevelの親所要量に数量(3列目)を乗じて所要量を算出する。
    QTY_S=QTY_Pre(Level-1)*strShtName.Cells(Row,3).Value
End If
strShtName.Cells(Row,4).Value=QTY_S
Row=Row+1
PartNo=strShtName.Cells(Row,2).Value
Loop
On Error Goto 0
End Sub

コメント

タイトルとURLをコピーしました