令和三年度 岐阜県生活技術研究所研究報告

No.24

技術レポート家具製造現場におけるトレーサビリティ基盤技術の開発（第4報）
Excel VBAを用いた生産現場の改善事例
森茂智彦*
Basic Technology Development of Traceability in the Furniture Manufacturing Field (Ⅳ)
Examples of Production Site Improvements Using Excel VBA
MORIMO Tomohiko*
Microsoft Excelは、生産現場でも多く使用されている。VBAを用いることで、より高度な動作をさせることが可能となる。しかし、どんなことができるのか想像が沸かないという声も多く聞く。そこで本報では、VBAを用いて生産現場の改善に取り組んだ事例を3つ紹介する。

1.

緒言

Microsoft Excelは、企業活動の様々なところで使われており、当然ながら生産現場でも多く用いられている。
Excelに標準で搭載されているマクロ機能を用いることで、操作を自動化し効率的に業務を進めることができる。マクロは、「マクロの記録」機能で作成できる他、プログラミング言語である
VBA(Visual Basic for Applications)を用いたプ
ログラミングによりさらに高度な動作を実装することもできる。しかし、プログラミングという観点から難しそうという意見や、どんなことができるのか想像が沸かないという声も多く聞く。そこで
本報ではVBAを用いて生産現場の改善に取り組んだ事例を紹介する。なお、各事例で紹介する画面は、説明のため実際の改善に用いたものを編集し簡略化している。
2. 事例1 フォームによる貸出台帳
2.1

概要

生産現場では、多くの人が同時に作業を行っており、また工具や刃物、治具、測定器など使用する物品の数は多い。また、物品を製造委託先へ貸し出すこともあり、物品の貸出状況を簡単に把握できることが求められていた。そこで、物品の貸出状況──────────────────────
*
試験研究部を管理するための貸出台帳を作成した。考慮した点として、Excelのシート画面を表示させず、フォーム画面のみ表示させた1)。これにより、台帳から対象物品を探す手間を短縮でき、作業性が向上すると考えた。
2.2

使い方

図1に画面遷移図を示す。ファイルを開くと最初にトップページが表示される。使用するにはまず「物品登録フォーム」画面にて物品の登録を行う。
登録時には物品の所持数も入力する。登録した内容は、「物品リスト閲覧・編集フォーム」画面にて確認や編集を行うことができる。編集は「物品編集フォーム」にて行う。
貸出は「貸出フォーム」画面で行う。貸出物品は「物品登録フォーム」から登録した物品の中から選択する。検索ボタンを押すことで、「物品検索フォーム」が表示され、その中のリストから物品を選択する。数量に未貸出中の数量（物品リストに登録した所持数から既に貸出中の数量を引いた数）を超える数を入力するとエラーとなり、貸出情報の登録はできない。
返却は「返却フォーム」画面で行う。返却したい物品の貸出情報をリストから選択し、返却ボタンを押せば返却される。返却した貸出情報はリストから消去される。
貸出状況を確認するには、「貸出台帳閲覧フォー
ム」画面で行う。リストには現在貸出中の内容に加69令和三年度岐阜県生活技術研究所研究報告

No.24

え、既に返却した内容も履歴として残している。
「返却フォーム」画面にて誤って返却した場合は、この画面から返却取消を行うことができる。また、不要となった貸出履歴は、リストから選択して削除するか、1年前までの履歴をまとめて削除することができる。
「物品検索フォーム」、「物品リスト閲覧・編集フォーム」、「返却フォーム」、「貸出台帳閲覧フ
ォーム」では、画面上部のテキストボックスに物品情報の一部を入力し、エンターキーを押すことで、リストの中から該当するものを絞り込むことができる。検索ボタンがある画面では、一部の情報入力後、検索ボタンを押しても検索できる。

図1 貸出台帳の画面遷移図

70

令和三年度 岐阜県生活技術研究所研究報告

3. 事例2 シートへの入力支援
3.1

概要

Excelを台帳として使用し、多数の情報を入力する場合、効率的にデータを入力できることが求められる。そこで、製品の試作時や製造時、修理時などに使用した部品の記録台帳を例に、作成した入力支援機能を紹介する。この台帳は、使用した部品の数量と購入価格を積算するために記録する台帳である。図2に記録台帳の画面を示す。台帳として、「管理台帳シート」、「部署台帳シート」、「部品台帳シート」がある。「部署台帳シート」と「部品台帳シート」には、予め情報を登録しておく。「管理台帳シート」には、使用した部品の情報や部品を使用した部署の情報を入力する。
3.2

入力支援機能図2に示す画面を元に、実装した「管理台帳シート」への入力支援機能を説明する。入力を支援する項目は、「部署」と「型式」である。なお、「型式」を入力すると、自動で「部品台帳シート」を参照し、該当する「品名」、「メーカ」、「購入価格」が入力されるが、この機能はVBAを使用しておらず、VLOOKUP関数のみで実現できる。
3.2.1

部署の入力

部署欄は、「部署台帳シート」のリストに該当するNoの値のみで入力できるようにした。例えば、「ドライブ課」と入力したい場合、「3」を入力すれば自動で「ドライブ課」と変換される。VLOOKUP
関数と異なり、入力したセルの値が変更される。
直接「ドライブ課」と打ち込んだ場合やリストにNo.24ない番号を入力した場合は、変換されず入力した値が表示される。「製造1課」や「製造2課」のような数値が含まれる部署名に該当するリストのNoを「1」や「2」としておくと、部署名とNoの対応が分かりやすくなる。また、リストのNoを数値ではなく、文字にしても良い。「ユニット開発課」に該当する
Noを「ゆ」とした場合、「管理台帳シート」へは「ゆ」と入力するだけで「ユニット開発課」と表示される。
3.2.2

型式の入力

型式欄は、「部品台帳シート」に記載のある型式の一部の文字を入力しエンターキーを押すと、自動的にその文字が含まれる型式名に変換されるよ
うにした2)。例えば、「A」と入力した場合、「A」が含まれる型式は「AA-1」のみであるため、「AA1」に変換される。「AA」の場合も同様である。また、「D」と入力した場合は該当する型式が3項目あるため、この場合は、一番上の行にある「DD-1-100」に変換される。「DD-1-1」と入力した場合、上の行にある「DD-1-100」に変換されてしまうため、対策として「部品台帳シート」への登録と「管理台帳シート」への入力時には、型式名の後ろに半角スペースを入れて「DD-1-1 」と入力することとした。な
お、改善を行った際、このようなパターンは少なかったため、全体としては本支援機能による改善効果は得られた。また、リストにない型式を入力した場合は、変換されず入力したまま表示される。さらに、メッセージボックスにより、「登録されていません。登録しますか？」と質問される。ここで、「はい」を選択すると「部品台帳シート」の行末に入力した型式が自動で追記される。
「部品台帳シート」に「品名」などその他の情報を記載すれば、その内容が管理台帳に反映される。

図2 記録台帳の画面
71

令和三年度岐阜県生活技術研究所研究報告

No.24

する。

図3 生産管理システムの概要

4. 事例3 生産管理システム4.1

概要

木製家具製造業のように、部材(部品)によって製造工程が異なり、さらに多品種少量生産の業種の場合、工程の流れが複雑になるため、手入力での管理では次工程への連絡の遅れや漏れ、工程順序の誤りなどが生じる可能性がある。そこで、この課題を解決する生産管理システムを開発した。
システムの概要を図3に示す。工程管理ファイル、マスターデータファイル、製造工程A～Cファイル
はすべて個別のExcelファイルである。工程管理フ
ァイルは、工程管理者が操作するファイルである。
部品の製造指示情報の登録と製造中の部品の進捗状況の閲覧を行う。
製造工程A～Cファイルは、各製造工程の製造者が操作するファイルである。登録されている製造指示情報の中から自身の工程に関する情報のみを抽出し閲覧することができる。また、自身の工程で製造を完了した部品の完了登録を行うことで、次の工程の製造工程ファイルにその部品の製造指示情報が表示される。製造工程を増やしたい場合には、製造工程ファイルを複製し、VBAのコードに記述している工程名のみ変更すれば良い。
マスターデータファイルは、データベースとして使用しており、製造指示情報や状況を記録している。工程管理ファイルや製造工程A～Cファイルから自動でアクセスされるため、基本的に人がマスターデータファイルを開くことはない。
4.2

使い方

図4に示す画面を元に、使い方を説明する。
4.2.1

工程管理ファイルの操作(1) 事前準備
事前準備として、部品リストシート内の部品リストに、部品名とその部品を製造するための工程を記載する。工程は、カンマ区切りで工程順に入力

72(2) 製造指示情報の登録
製造指示情報登録シートから登録を行う。納期、ロットNo、部品名、数量、(必要に応じて)備考を入力し、「登録」を押すと、マスターデータファイルにその情報が登録される。製造指示情報が複数ある場合は、複数行に記載する。また、登録時に(1)項で記載した部品リストの中から部品名に該当する工程を参照し、マスターデータファイルに登録する。部品リストに記載がない場合は、エラーとなり製造指示情報を登録することはできない。なお、マスターデータファイルの項目にあるIDは、登録時に自動で割り振られる数値である。
(3) 製造状況の閲覧
製造状況閲覧シートから製造状況の閲覧を行う。
「表示を更新」ボタンを押すと、マスターデータフ
ァイルから製造指示情報を読み取り、表示を行う。
(2)項で登録した情報の他に、現在の工程と残りの工程が表示される。現在の工程は、現在製造中の部品の製造工程名であり、残りの工程は、現在の工程完了後に実施される残りの製造工程名である。残りの工程が複数ある場合は、順にカンマ区切りで表示される。また、全ての工程が終了した部品については、現在の工程欄に完了と表示される。完了した部品の情報は、「完了分を削除」ボタンを押すと、まとめて削除される。なお、登録された製造指示情報を変更する機能も実装したが説明は省略する。
4.2.2

製造工程ファイルの操作(1) 製造指示情報の閲覧
製造指示情報閲覧シートから製造指示情報の閲覧を行う。「表示を更新」ボタンを押すと、マスターデータファイルから製造指示情報を読み取り、自身の製造工程に関係する情報の表示を行う。表
示内容は、4.2.1(2)項で登録した情報である。また、背景色が白色の行は、自身の工程で製造中の部品の情報であり、灰色の行は、他の工程で製造中であるが、他の工程完了後、自身の工程で製造する部品の情報である。
自身の工程の製造完了後、該当する部品の完了欄をダブルクリックすると完了と表示され、背景色が水色になる。その状態で「完了反映」ボタンを押すと、マスターデータファイルに完了情報が反映される。完了欄に完了と表示された状態で、再度完了欄をダブルクリックすると、完了欄の表示は消えて、背景色が白色に戻る。

令和三年度 岐阜県生活技術研究所研究報告

No.24

図4 生産管理システムの画面73

令和三年度4.3

岐阜県生活技術研究所研究報告

No.24

マスターデータファイルでの記録内容

マスターデータファイルには、4.2.1(2)項で登録した情報に加え、現在の工程と工程の数の情報を記録している。現在の工程は、工程欄に記載している部品の全工程の中から、現在何番目の工程にあるかを数値で表している。工程の数は、工程欄に記載している工程の総数である。
製造状況や製造指示情報の閲覧時には、工程欄の内容と現在の工程欄に記載の数値から、現在の工程名を得ている。
工程管理ファイルから製造指示情報の登録を行う際には、現在の工程欄は「1」と記載される。製造工程ファイルからその工程の完了登録を行う際には、現在の工程欄の数値に「+1」を加算している。
また、(現在の工程欄の数値)＞(工程の数欄の数値)である場合、工程管理ファイルでは全ての工程が完了していると判断している。
4.4
4.4.1

考慮した点
同時アクセスへの対応

Excelは複数人が同時に同じファイルにアクセスすることができない。そこで、データ記録用のファイルとしてマスターデータファイルを設け、情報の登録や閲覧時のみマスターデータファイルを開き、情報の書き込みと読み取りを行った後、すぐに閉じる仕様とした。マスターデータファイルへのアクセス時間は、1回の処理で約１秒程度であり、もしそのタイミングで他の人がマスターデータファイルにアクセスしても、１秒待てばアクセスできるため、業務に支障が出る可能性は低い。
4.4.2

項目の増減への対応

登録や表示を行う項目の追加や削除を行う場合における、VBAのコードの修正箇所を減らす方法について検討した。
VBA に て シ ー ト 上 の セ ル を 指 定 す る 場 合 、Cells(行数,列数)と記述する。例えばB列の5行目のセルならCells(5,2)である(Range("B5")という記述もできる)。そのため、例えばシートのA列目とB列目の間に列を挿入し、新しく登録する情報を追加した場合、これまでB列目にあった情報はC列目に、C列目にあった情報はD列目にと順に移動する

74

ため、セルを指定している箇所は追加前の列から追加後の列に全て変更しなければならない。
そこで、Cells内の列数を、Dictionaryオブジェ
クトを用いて指定することとした3)。Dictionaryオ
ブジェクトによりシート内の項目名と列数を対応させることで、項目名でセルを指定することが可能となる。列数に変更が生じても項目名で指定しているため、この部分のコードを修正する必要はない。例として、Dictionaryオブジェクトをdic、B列の項目名を部品名とした場合、先の例においてB列の5行目のセルを指定する場合は、Cells(5,dic("部品名"))と記述できる。また、列数の変更に対応するため、登録や更新の処理の最初に、都度、各シート内の項目名とその項目の列数を取得し、Dictionaryオブジェクトに格納している。
Dictionaryオブジェクトを用いることで、登録項目の追加や削除が生じた際の修正時間を大幅に短縮することができた。
5.

まとめ

本報では、VBAにより現場の改善を行った事例として、フォームによる貸出台帳、シートへの入力支援、生産管理システムについて紹介した。今回紹介した事例が業務改善の一助になれば幸いである。
謝辞
改善のためのニーズを提供していただいた関係者の方々にこの場を借りて厚く御礼申し上げます。
参考文献
1) 横山達大: アプリ作成で学ぶ ExcelVBA プログ
ラミングユーザーフォーム&コントロール, 秀和システム, 2012, pp. 440-441.
2) エクセル VBA nge イベントと EnableEvents プロパティで部分一致検索をする方法, https://
tonari-it.com/excel-vba-event-change-enabl
eevents/#toc2, 2022 年 3 月 2 日参照
3) VBA 入門 ictionary オブジェクト(連想配列)の使い方 侍エンジニアブログ, https://www.sejuku.net/blog/29736, 2022 年 3 月 10 日参照