【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと


avoid

photo credit: XoMEoX Constriction via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。

前回の記事はコチラ。

【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
エクセル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」の部分を抽出して、それらから「対象月」の日付型データを生成しています。

YYYYMM経費精算書_名前_XXXX.xlsx

エクセルファイル内よりもファイル名のほうが入力ミスをしづらい、または入力ミスを発見しやすいということを期待できます。

今回は、「XXXX」の社員Noの部分を利用して、部署No、部署、氏名について対策をとるべく準備を進めていきます。

マスタデータとは

まず、前準備として、現在マクロを書いているブックにシートを追加して「マスタデータ」を整備していきます。

マスタデータ 【 master data 】
マスタデータとは、企業内データベースなどで、業務を遂行する際の基礎情報となるデータのこと。また、それらを集約したファイルやデータベースのテーブルなど。単に「マスタ」と省略するのが一般的である。
(引用:IT用語辞典)

社員マスタを準備する

例えば、以下ような「社員マスタ」を準備します。

エクセルの社員マスタ

社員Noは社員ごとに一意に定められているものとすると、社員Noさえわかれば

  • 氏名
  • 部署No
  • 部署

は、おのずと決まってきます。

それら社員Noに紐づくデータを「コチラ側」に正しい情報として持っておいて、それを参照をするのです。

その役割を担う「コチラ側」のデータをマスタ、もしくはマスタデータと言うわけです。

部署マスタを準備する

さてもう一つ、今回は以下のような「部署マスタ」という別のマスタも用意しておきます。

エクセルの部署マスタ

前述の「社員マスタ」のD列「部署」は、C列「部署No」をキーにして「部署マスタ」から
VLOOKUP関数で引っ張ってきています。

例えば、「社員マスタ」のセルD2であれば、以下のような関数が仕込まれています。

=VLOOKUP(C2,部署マスタ!A:B,2,FALSE)

こうしておけば、社員の部署が変更された場合は「社員マスタ」の「部署No」を変更すれば、「部署」も連動して変更できます。

また、部署の名称が変更になった場合は「部署マスタ」の「部署」を変更することで、「社員マスタ」も連動して変更できます。

ユーザーの入力とその影響は最小限に

このように、マスタデータを準備さえしておけば、「社員No」のみをキーに、その他のデータは正しいものを引っ張ってくることができます。

そして、その社員Noはファイル名に仕込まれていますので、エクセルファイル内のユーザーの入力内容に依存せずに経費データを収集、蓄積できるようになります。

ですから、業務フローにもよりますが、そもそもシート上の入力欄自体なくしてもいいのかもしれません。

このように、ユーザーが入力すべきデータとそのミスによる影響範囲を最小限に留めておく、という仕組みづくりは、プログラミング自体とともに重要なポイントと言えます。

社内のマスタ管理の注意点

さて、今回のシリーズでは経費精算書だけにフォーカスをしていますが、部署や社員のデータというのは、他の業務でも使用される可能性は十分にあります。

ですから、本来はこれらの「おおもとのマスタ」は、経理担当者のローカルPC内のいちエクセルファイルで完結する場合は、あまりないかも知れません。

一般的には「おおもとのマスタ」は、管理システムにあったり、共有サーバーにあったりすることもありますね。

ですから、その「おおもとのマスタ」と、VBAで使用するマスタをいかにして連動させるか、というのは運用上考えておくべき課題となります。

その点、念頭に置いておいていただければと思います。

まとめ

以上、エクセルVBAでマクロを作る際に知っておきたいマスタデータのことについてお伝えしました。

繰り返しになりますが、ユーザーの入力にはどうやってもミスが混入します。

まずは、入力自体が最小限になること、そしてその入力のミスによる影響を最小限に留めておくことを目指してみましょう。

次回は具体的に、ファイル名から社員番号を抜き出す方法をお伝えします。

【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
エクセルVBAでバラバラの経費精算書のデータをまとめるマクロの作り方をお伝えしています。今回は、開いたブックのファイル名から番号を取り出して数値に変換するために、いくつかの便利な関数を紹介していきます。

どうぞお楽しみに!

連載目次:エクセルVBAで経費データをデータベースに集約する

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

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

タイトルとURLをコピーしました