のぼゆエンジニアリング

ゼロから機械設計を勉強してみたりするブログ

エクセルで作る簡易な部品表の紹介。BOMもどきで部材手配が楽になったよ



スポンサーリンク

エクセルで作る簡易な部品表の紹介。BOMもどきで部材手配が楽になったよ

簡易な部品表(BOMもどき)をエクセルで作ったので、紹介します。
私が個人的に使っているものです。
モドキとはいえ、直感的にリストアップできて手配漏れが減りました。

親と子ASSYの部材重複を集計したい

小規模な装置など、設計から現物手配まで一人でやることがあります。
そんな時に私はエクセルで部品表を作り、管理しています。

「ただリストアップするだけでは?」と思われるかもしれません。
それがすんなりとはいきません・・・

小規模でも装置になると、親と子アッセンブリに共通・重複して使われる部材がでてきます。(sub-assyやネジなど)

それを小規模だからと人力で集計していると、混乱して手配の重複・不足が発生していました。

3DCADである程度の部品票は出力できますが、最終的にはエクセルの方が使い勝手が良くてどうしたものかと。

そこで作ったのがコレです。

クリックorタップで拡大 エクセルでbomもどき

エクセルでbomもどき

BOMもどきとは

「BOM」とはBill of materialsの略称で、製造業における部品票を指します。
一個人では高度なシステムは煩雑なので、手軽にエクセルで何とかしようという目論見です。

サンプル課題

部品表のサンプルを作るため、適当なASSY構成を設定しました。

装置全体が「TOP ASSY」 そこにA、B、Cの各ASSYが組み付いています。

さらに、BにはサブASSYとしてAが各2個。

CにもサブASSYとしてDが各1個ぶら下がっています。

機能の説明

名称やユニット単体個数を入力すると、C列とD列にASSY数の合計が算出されます。

するとJ列に必要数量の小計が計算されます。 エクセルでbomもどき

あとは、それを元に手配数量を入力していきます。

小計イコール手配数量では?と思われるかもしれませんが、別欄を設けました。
モノによってはまとめ買いしたり、多めに発注する場合もあるからです。

エクセルでbomもどき

ピボットテーブルで集計すると、手配リストのできあがり

手配数量が決まれば、あとはピボットテーブルで集計します。

リストの任意位置を選択しておいて、「挿入」→「ピボットテーブル」→「Ok」を実行します。

すると、各ユニットで重複している部材の合計数量が出せます。
あとは、リスト通りに発注するだけです。 エクセルでbomもどき

エクセルでの計算の仕組み

計算の流れは下記の通りです。

リスト作成のルール

まず、リスト作成のルールです。親から子ASSYの順番に、リストを入力するようにします。

例でも、まずTOP assyのリスト、次に子のリスト、という順に挙げています。

名称からASSY名を検索、積の和を出す

「部位名」を検索語句、自分の上の行の「名称」を検索範囲として検索。
該当行のASSY数と個数の積の和が「ASSY数1」欄に計算されます。

「ASSY数2」欄は自分の下の行を検索範囲としています。

一度にやると循環になったり、複雑になるので分けて計算しています。

SUMPRODUCT」という便利な関数を使いました。

「ASSY数1」と「ASSY数2」の和が、各ユニットの合計だというわけです。

サンプルファイルの配布

この記事で紹介したエクセルファイルを、参考にダウンロードできるようにしました。
VBAは使っていません。関数のみです。

得意な方なら、作り直したほうが速いかもしれません。

(作成環境:Windows10 64bit/ Excel 2016 )

あくまで簡易的に自分用に作ったファイルです。
ご使用の際はご注意下さい。
セルの参照が狂うとワケワカメになります。

© 2018 のぼゆエンジニアリング
About記事一覧お問合せ