
photo credit: XoMEoX Constriction via photopin (license)
みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。
前回の記事はコチラ。

みんなから集めたエクセルファイルのファイル名を使って、入力ミスを避けるテクニックについてお伝えしました。
今回もその続き。もっと運用上ミスが減らせるようにマスタというものを準備します。
ということで、エクセルVBAでマクロを作るときに知っておきたいマスタデータのことについてお伝えしていきます。
では、行ってみましょう!
前回のおさらい
毎月、社内の皆さんから受け取る経費精算書ですが、以下の部分は手入力なので、人為的なミスが混入する可能性がありました。
そのミスを回避すべく、ファイル名から「対象月」を取得してしまおう、という作戦をとってコードを作成しました。
コチラです。
Sub 経費精算データ取り込み()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
With Workbooks.Open(f.Path)
With .Worksheets(1)
Dim period As String: period = Left(f.Name, 6)
Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
Dim departmentName As String: departmentName = .Range("G6").Value
Dim staffId As Long: staffId = .Range("G8").Value
Dim staffName As String: staffName = .Range("G7").Value
Dim i As Long: i = 12
Do While .Cells(i, 1).Value <> ""
wsData.Cells(di, 1).Value = month '1 対象月
wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
wsData.Cells(di, 3).Value = "" '3 部署No
wsData.Cells(di, 4).Value = departmentName '4 部署
wsData.Cells(di, 5).Value = staffId '5 社員No
wsData.Cells(di, 6).Value = staffName '6 氏名
wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
i = i + 1: di = di + 1
Loop
End With
.Close
End With
Next f
End Sub
12,13行目で、以下の形式のファイル名から「YYYYMM」の部分を抽出して、それらから「対象月」の日付型データを生成しています。
エクセルファイル内よりもファイル名のほうが入力ミスをしづらい、または入力ミスを発見しやすいということを期待できます。
今回は、「XXXX」の社員Noの部分を利用して、部署No、部署、氏名について対策をとるべく準備を進めていきます。
マスタデータとは
まず、前準備として、現在マクロを書いているブックにシートを追加して「マスタデータ」を整備していきます。
マスタデータ 【 master data 】
マスタデータとは、企業内データベースなどで、業務を遂行する際の基礎情報となるデータのこと。また、それらを集約したファイルやデータベースのテーブルなど。単に「マスタ」と省略するのが一般的である。
(引用:IT用語辞典)
社員マスタを準備する
例えば、以下ような「社員マスタ」を準備します。
社員Noは社員ごとに一意に定められているものとすると、社員Noさえわかれば
- 氏名
- 部署No
- 部署
は、おのずと決まってきます。
それら社員Noに紐づくデータを「コチラ側」に正しい情報として持っておいて、それを参照をするのです。
その役割を担う「コチラ側」のデータをマスタ、もしくはマスタデータと言うわけです。
部署マスタを準備する
さてもう一つ、今回は以下のような「部署マスタ」という別のマスタも用意しておきます。
前述の「社員マスタ」のD列「部署」は、C列「部署No」をキーにして「部署マスタ」から
VLOOKUP関数で引っ張ってきています。
例えば、「社員マスタ」のセルD2であれば、以下のような関数が仕込まれています。
こうしておけば、社員の部署が変更された場合は「社員マスタ」の「部署No」を変更すれば、「部署」も連動して変更できます。
また、部署の名称が変更になった場合は「部署マスタ」の「部署」を変更することで、「社員マスタ」も連動して変更できます。
ユーザーの入力とその影響は最小限に
このように、マスタデータを準備さえしておけば、「社員No」のみをキーに、その他のデータは正しいものを引っ張ってくることができます。
そして、その社員Noはファイル名に仕込まれていますので、エクセルファイル内のユーザーの入力内容に依存せずに経費データを収集、蓄積できるようになります。
ですから、業務フローにもよりますが、そもそもシート上の入力欄自体なくしてもいいのかもしれません。
このように、ユーザーが入力すべきデータとそのミスによる影響範囲を最小限に留めておく、という仕組みづくりは、プログラミング自体とともに重要なポイントと言えます。
社内のマスタ管理の注意点
さて、今回のシリーズでは経費精算書だけにフォーカスをしていますが、部署や社員のデータというのは、他の業務でも使用される可能性は十分にあります。
ですから、本来はこれらの「おおもとのマスタ」は、経理担当者のローカルPC内のいちエクセルファイルで完結する場合は、あまりないかも知れません。
一般的には「おおもとのマスタ」は、管理システムにあったり、共有サーバーにあったりすることもありますね。
ですから、その「おおもとのマスタ」と、VBAで使用するマスタをいかにして連動させるか、というのは運用上考えておくべき課題となります。
その点、念頭に置いておいていただければと思います。
まとめ
以上、エクセルVBAでマクロを作る際に知っておきたいマスタデータのことについてお伝えしました。
繰り返しになりますが、ユーザーの入力にはどうやってもミスが混入します。
まずは、入力自体が最小限になること、そしてその入力のミスによる影響を最小限に留めておくことを目指してみましょう。
次回は具体的に、ファイル名から社員番号を抜き出す方法をお伝えします。

どうぞお楽しみに!
連載目次:エクセルVBAで経費データをデータベースに集約する
請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。- 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
- 【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
- 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの2つのポイント
- 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
- 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
- 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
- 【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
- 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
- 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
- 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
- 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
- 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
- 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
- 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
- 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する