簡易所要量計算（Excel/VBA)の説明とサンプルコード

美味しい水をお探しですか？
定額制だからお水は使い放題！全て“月額料金”にコミコミ！
利用料金は業界最安値！

何十枚、何百枚もある図面の中から使用しているボルトの数を数え上げたり、必要なナットの数を数え上げたりするのは大変な作業ですし、間違いも多くなりますね。
　そうなると製品を作る時に部材が不足して緊急手配が必要になったり、無駄な在庫が増えてしまいます。そこで！

　製品構成展開されたツリーデータを使用して、部品の所要量（製品を組み立てるために必要な部品の必要個数）を計算するExcel/VBAのコードを説明します。
わずか３０行ほどの簡単なコードですが、手計算に比べて劇的に生産性と正確性を向上できます。
　製品の最上位の部品から下位の部品へと構成展開をしながら、組立毎の必要個数を累計して計算します。
※複数の組立品で使用される同一部番毎の集計や在庫の引き当て等はしていません。

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

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

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

VBAを実行して計算すると、製品を製作数製造するために必要な部品の所要量が４列目に入ります。
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

このように、わずか３０行ほどの簡単なコードですが、手作業に比べて劇的に生産性と正確性を向上できます。
プログラミングに興味を持ったら、まずは体験入学！
さらに高度で大規模なプログラムを外注するなら、最適な会社を探してくれるサービスもあります！

PR広告！TechAcademy [テックアカデミー]

話の分かる優秀なコンサルタントをお探しですか？

PR広告！一流コンサルタントに3.85万（税込み）〜WEB相談できる！【コンパスシェア】

DXに強いシステム開発会社をお探しですか？

PR広告！システム開発業者を完全無料でご紹介します！【EMEAO!】

浄水型ウォータサーバー　安全で美味しい水を、いつでも、必要なだけ確保したいならこれ！

PR広告！おいしい水を水道水から【Locca】

PR広告！ITエンジニアへの道　TechAcademy [テックアカデミー]

■１２０社１０００人以上が受講！！
１．オンライン講座　　　　　　
　　経験と勘だけのレビューから脱却する！レビューを体系的に学び、成果につなげる！　　　　　　　　　　
　　『【リスク指向】超ドキュメントレビュー実践法』
　　※実務ですぐに使えるチェックリストを使用した演習付き！
　　
　　危険予知能力を高め、リスクを見つける！
　　『100の失敗事例に学ぶ！企業システム戦略の危険予知訓練』
　　※すぐに実践で役立つ100の失敗事例による危険予知訓練の演習付き！