部品構成表をExcelで作る——構成の書き出しから画像付きの部品リストまで
アセンブリの構成を Excel の部品構成表にする作業は、設計の現場で何度も繰り返されます。構成をたどって書き出す所までは難しくないのに、同じ部品を数え、列を揃え、罫線を引き、部品の画像を貼る段になると、時間がかかります。弊社の Q-LAB には、この作業をマクロにした事例があり、実測では1回あたり15分かかっていたものが0.1分になりました。この記事では、部品構成表づくりの中身を分け、マクロに任せられる所と人が決める所、画像付きの部品リストの作り方、出したあとの確かめ方を、始める順番とあわせて書きます。
Photo: Unsplash部品構成表づくりは、書き出しと帳票づくりの二つの仕事
部品構成表と聞くと、CATIA の構成を Excel に写す作業を思い浮かべる方が多いと思います。実際に手を動かしてみると、この作業は二つに分かれます。ひとつは、アセンブリの構成をたどって、品番と員数を拾う書き出し。もうひとつは、拾った結果を帳票として読める形に整える作業です。
書き出しは、規則が決まっているので機械向きです。帳票づくりは、列の並び、罫線、列幅、画像の位置と、見た目の決めごとが多く、毎回同じように整えるのが意外と大変です。書き出しが終わってからが長い、というのがこの作業の実感です。
部品表を CAD から直接出す設計の考え方は、部品表(BOM)作成の自働化——CADから直接出すに書きました。こちらは、その先の話です。出てきた構成を、Excel の帳票としてどう作るか、画像付きの部品リストまでどう広げるかを書きます。
なお、この記事の部品構成表は、設計部門が図面や手配の確認に使う一覧を指します。生産管理のシステムに載せる正式な部品表とは、役割も項目も違うことがあります。
手で作ると、時間が消えるのは体裁と画像
手で作る流れを追ってみます。CATIA の構成ツリーを見ながら、品番と名称を Excel に写します。同じ品番が複数あれば、数えて員数の列に書きます。そのあと、見出し行を太字にし、罫線を引き、列幅を直し、必要なら部品の画像を一枚ずつ貼ります。
写す作業は、集中すれば進みます。ところが、同じ部品が構成の別の場所にも出てくる、似た品番がある、といった所で手が止まり、数え直しが入ります。数え直しが入ると、員数が合っているかが不安になり、確認にも時間がかかります。
画像を貼る作業はさらに重く、部品を一つずつ開き、画面を撮り、Excel に貼り、大きさを揃えます。この繰り返しが、部品の数だけ続きます。転記そのものより、数え直しと体裁と画像の貼り付けに時間が消えるのが、手作業の実情です。
Q-LAB に載せている弊社の実測事例『Product構成部品リスト出力』では、Product の構成状態から部品表を出力し、CATIA と Excel の間の転記をマクロが行います。集計や帳票の体裁まで整え、1回あたり15分かかっていた作業が0.1分になりました。転記のたびに起きていた書き間違いも無くなっています。
マクロに任せる所——構成をたどって、数えて、Excel に書く
任せる範囲を三つに分けると、考えやすくなります。構成をたどって部品を拾うこと、同じ品番を数えること、Excel に書いて体裁を整えること。この三つは、決まった規則で進むので、マクロが得意です。
下の例は、その三つを VBA で書いたものです。アセンブリを開いて実行すると、子を持たない要素を部品として数え、品番ごとに員数を出し、新しいブックに表を作ります。既存のブックには触れないので、やり直したいときはそのブックを閉じて、もう一度実行するだけです。
マクロにする良さは、同じ入力に同じ出力が返ることです。手で作ると、日によって、人によって、列の順や罫線の引き方が変わります。マクロなら、体裁が毎回同じになるので、受け取る側も読み慣れます。選択や構成のたどり方の基本はCATIAマクロの主役はSelectionオブジェクト——現場で使う選択テクニック集にまとめています。
品番が空の部品があったとき、マクロは黙って飛ばさず、「品番なし」と書いた行を残します。見つかった問題を表に残しておくほうが、あとで人が直しやすいからです。
写す時間は機械に渡し、
何を数えるかを決める時間は人に残す。
' CATIA の Product 構成をたどり、同じ部品を数えて、新しい Excel ブックに部品構成表を書く(CATIA V5 / アセンブリ)。
' 列の並びと員数の数え方は、人が決めて下に書く。既存のブックには触らない。
Option Explicit
Private mQty As Object ' 品番 -> 員数
Private mDesc As Object ' 品番 -> 名称
Private Sub Walk(ByVal oProd As Product)
Dim i As Long, oChild As Product, key As String
For i = 1 To oProd.Products.Count
Set oChild = oProd.Products.Item(i)
If oChild.Products.Count = 0 Then ' 子が無い=部品として数える
key = oChild.PartNumber
If Len(key) = 0 Then key = "(品番なし)" & oChild.Name
If mQty.Exists(key) Then
mQty(key) = mQty(key) + 1
Else
mQty.Add key, 1
mDesc.Add key, oChild.DescriptionRef
End If
Else
Walk oChild ' 子があれば、その中も同じようにたどる
End If
Next
End Sub
Sub ExportBom()
Dim oDoc As Document, xl As Object, ws As Object, k As Variant, r As Long
If TypeName(CATIA.ActiveDocument) <> "ProductDocument" Then
MsgBox "アセンブリ(Product)を開いて実行してください。": Exit Sub
End If
Set oDoc = CATIA.ActiveDocument
Set mQty = CreateObject("Scripting.Dictionary")
Set mDesc = CreateObject("Scripting.Dictionary")
Walk oDoc.Product
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set ws = xl.Workbooks.Add.Worksheets(1)
ws.Range("A1:D1").Value = Array("No", "品番", "名称", "員数")
r = 2
For Each k In mQty.Keys
ws.Cells(r, 1).Value = r - 1
ws.Cells(r, 2).Value = CStr(k)
ws.Cells(r, 3).Value = mDesc(k)
ws.Cells(r, 4).Value = mQty(k)
r = r + 1
Next
' 帳票の体裁: 見出し行・罫線・列幅。ここも毎回同じ形で出る
ws.Rows(1).Font.Bold = True
ws.Range("A1:D" & (r - 1)).Borders.LineStyle = 1
ws.Columns("A:D").AutoFit
MsgBox "書き出しました(" & (r - 2) & " 品番)。品番なしの行と員数を確かめてください。"
End Sub画像付きの部品リスト——一枚ずつ撮って、セルに貼る
部品の画像を並べた一覧は、構成表より一歩進んだ使い方です。手配先や製造現場に渡すとき、品番だけより、形が見えるほうが取り違えが減ります。Q-LAB の事例『構成部品リスト作成ツール』は、Product から構成リストと画像付きの部品リストを作るものです。
作り方の流れは単純です。部品を一つずつ開き、見る向きを揃えて画像に書き出し、その画像を Excel の行に貼ります。画像の大きさを行の高さに合わせ、貼る位置を列で決めておけば、行と画像が対応します。
つまずきやすいのは、見る向きと拡大率です。部品によって形が違うので、同じ設定のまま撮ると、小さすぎたり、切れたりします。全体が入る向きを先に決めて撮り、画像は元の縦横比を保ったまま貼ります。
画像は容量が増えるので、書き出しの大きさを決めておきます。一覧で形が分かる程度で足りるなら、小さめの画像で十分です。作った一覧は、構成表と同じブックの別のシートに置くと、使い分けやすくなります。
人が決める所——列の並び、品番の読み方、員数の数え方
マクロに任せないほうがよいものもあります。まず、列の並びと名前です。どの列を何と呼び、どの順に並べるかは、受け取る人の使い方で決まります。マクロの中に埋め込まず、見出しの定義として一か所に置き、変えやすくしておきます。
次に、品番の読み方です。品番に版数や枝番が付く会社では、「同じ部品」の判断が変わります。版違いを同じ部品と数えるのか、別と数えるのか。ここをマクロが推測すると、員数が静かにずれます。
そして、員数の数え方です。部品を数える対象に、締結部品や共通部品を含めるのか。構成の中の簡易な形状や参照用の要素をどう扱うのか。数える範囲は、人が決めて、規則として書くほうが安全です。
決めた規則は、マクロの先頭の定数や、見出しの表にまとめておきます。規則が文書として残っていれば、人が代わっても同じ表が出ます。形を機械に引かせる範囲の考え方は見切り線を、すき間一定で引き直す——ボデーの見切り線作成をマクロに任せるにも書きました。
Excel の部品構成表は、どんな列で作るか——レベル・品番・員数の並べ方
「部品構成表をエクセルで作りたい」と考えたとき、最初に迷うのは表の形だと思います。ここでは、設計部門が図面や手配の確認に使う一覧として、そろえておきたい列を挙げます。会社ごとに足し引きがあって構いません。出発点として見てください。
基本になるのは、レベル・品番・名称・員数・単位の五つの列です。レベルは構成の深さで、製品を0、その直下のアセンブリを1、さらにその下を2、と数字で書きます。こうしておくと、一枚の平らな表でも、どの部品がどのアセンブリの下にあるかが読めます。レベルの分だけ名称を字下げして見せる方法もありますが、並べ替えや集計をするなら、数字の列を残しておくほうが扱いやすくなります。
員数は、親1台あたりの数で書くのか、製品1台あたりの総数で書くのかを先に決めます。階層のある構成表では親1台あたりで書き、手配に使う一覧では総数に掛け直す、という使い分けがよく見られます。どちらで書いたかを見出しに書いておくと、受け取った人が取り違えません。
そのほかの列は、使う人に合わせて足します。製造に渡すなら材質と表面処理、購入品があるなら手配区分(内製・購入)とメーカー・型式、改訂を追うなら版数、そして備考。Excel では見出し行を固定し、オートフィルターを付けておくと、レベルや手配区分で絞り込めます。前の節で書いた、マクロの外に置く見出しの定義は、この列の一覧そのものです。
出したあとに確かめて、1つの帳票から始める
表を出したら、中身を確かめます。品番の空き、重複、員数が整数かどうか、画像の数が行数と合っているか。この四つは機械で見られます。下の例は、書き出した Excel をその点検にかける Python です。見つかったものを一覧で出すだけで、直し方は決めません。
員数の合計が、CATIA で見える部品の数と合うかも、最初のうちは人の目で確かめます。数え方の規則が合っているかを、数字で確かめる機会になります。マクロにテストを書く考え方はマクロにテストを書く——CADツールの品質保証にまとめました。
始めるなら、いちばん作る回数が多い帳票を1つ選びます。最初は、書き出しと員数の数え方だけをマクロにして、体裁と画像は今までどおりでも構いません。毎回同じ手直しが入る所が見えたら、それがマクロに足す次の機能です。小さく始める進め方はPoCで小さく始める——1ツール2週間の進め方にまとめました。
御社では、部品構成表を1回作るのに、どれくらいの時間がかかっていますか。作る回数と時間を、一度書き出してみてください。クロニクスでは、現場の作業を見せていただき、CATIA でどこまで自働化できるかをヒアリングし、自働化設計書として無料でご提案しています。ご相談は contact@qronix.jp までどうぞ。
# 書き出した部品構成表(xlsx)の点検: 品番の空き・重複、員数、貼った画像の数。直すかどうかは人が決める。
# 使い方: python check_bom.py 部品構成表.xlsx
import sys
from openpyxl import load_workbook
def check(path):
ws = load_workbook(path).active
rows = [r for r in ws.iter_rows(min_row=2, values_only=True) if any(v is not None for v in r)]
problems, seen, total = [], set(), 0
for line, (no, pn, name, qty, *_rest) in enumerate(rows, start=2):
if not pn or str(pn).startswith("(品番なし)"):
problems.append(f"{line}行目: 品番が空です({name})")
elif pn in seen:
problems.append(f"{line}行目: 品番 {pn} が重複しています")
seen.add(pn)
if not isinstance(qty, int) or qty < 1:
problems.append(f"{line}行目: 員数が整数ではありません({qty!r})")
else:
total += qty
pics = len(getattr(ws, "_images", []))
print(f"行数 {len(rows)} / 員数の合計 {total} / 貼り付けた画像 {pics}")
if pics and pics != len(rows):
problems.append(f"画像の数({pics})が行数({len(rows)})と合いません")
if not problems:
print("点検で見つかったものはありません。")
for p in problems:
print(" " + p)
return problems
if __name__ == "__main__":
check(sys.argv[1])