【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用

★気に入ったらシェアをお願いします!


みなさん、こんにちは!
前も言いましたが、いつもバナナを食べていますタカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目、そして業務フローを想定しながら人為的なミスを回避するテクニックについての後編となります。

前回の記事はこちらです。

【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズ第6回目です。人が入力する場合は必ず人為的なミスが入り込みます。それをうまく回避する方法の一つとしてファイル名を使う方法をお伝えします。

各担当者から受け取る経費精算書のファイル名から対象月データを取得してしまうことで、ミスを減らしてしまおうというテクニックをお伝えしました。

今回は、ミスを減らす有効なテクニックとしてもう一つ、マスタの活用についてお伝えしていきます。

では、行ってみましょう!

スポンサーリンク

前回までのおさらい

担当者から毎回受け取る経費精算書ですが

経費精算書の手入力箇所にミスがある

このように、手入力をする箇所にミスが発生する可能性があります。

それを解決すべく、前回一部を対策した時点のプログラムはこちらです。

経費精算書のファイル名はobjFile.Nameで取得でき、

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

ファイル名の頭から6桁の年月(YYYY:西暦で年を表す+MM:2桁で月を表す)というルールにしていましたので、それを加工して経費の対象月を算出しました。

今回は、所属部署氏名について対策をとっていきたいと思います。

ファイル名から社員番号を切り出す

ファイル名ですが末尾の4桁のXXXXが社員番号にあたりますので、まずこの社員番号を抜き出します。

ちなみに、なぜファイル名が有効かと言いますと

  • 担当者はファイルをコピーして新たな経費精算書を作るので社員番号をいじる必要がない
  • ファイル名ならファイルを開かずとも確認ができる

からです。

Replaceで拡張子を取り除く

まず、ファイル名の末尾には拡張子「.xlsx」がありますのでこれを取り除きます。

特定の文字列を取り除きたい場合はReplaceを使います。

Replace(対象とする文字列, 置換する文字列, 置換後の文字列)

取り除くというか、Replaceのもともとの機能は”置換”なのですが、置換後の文字列を「””」と指定することで取り除くのと同様の働きになります。

今回のケースでは

とすればOKです。

Rightで末尾から文字を切り出す

拡張しが取り除かれれば前回使用したRightを使って末尾から文字を切り出して取得、すなわち

とすれば社員番号を取得することができます。

Valで文字列型を数値型に変換する

ただ実はもう一工夫必要です。

というのも、実際の社員番号は「数値型」なのですが、上記ファイル名から取得した値は「文字列型」ですので、数値型に変換しておく必要があります。

文字列を数値に変換するときはValを使います。

Val(文字列)
文字列に含まれる数字を数値として認識し、数値型に変換する

今回の場合ですが、例えば数値型の変数をnumStaffとしますと

とすることで、社員番号を数値型で取得することができます。

社員マスタを準備する

では次に取得した社員番号を使って、間違えることなく氏名と部署名を取得したいと思います。

それには、マスタと呼ばれるものを準備します。

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

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

社員マスタ

社員番号さえあれば、社員の氏名と部署名に関してはおのずと決まってきます。そのデータベースを、“こちら側”に正しい情報として持っておき、それを参照するということです。

その役割を担う”こちら側”のデータをマスタ、もしくはマスタデータと言います。

これにより、都度提出してもらう担当者の情報にミスがあったり、半角・全角スペースに統一性がなかったりしても、社員番号さえ正しいものを取得できれば、いつも同一の正しいデータを取得できるわけです。

VBAでVLOOKUP関数を使う

社員番号をキーにして、その行の氏名と部署名を取得する…エクセルに詳しい方ならば、VLOOKUP関数を使えばよいとパッと思いつかれると思います。

ただし、VLOOKUP関数はエクセルの関数(ワークシート関数と言います)であり、VBAの関数ではありませんので、そのままでは使用することはできません。

そんなワークシート関数を使う場合はWorksheetFunctionというオブジェクトを利用する必要があります。

書いてみたほうが速いですね、こんな書き方になります。

WorksheetFunction.VLookup(検索値,範囲,列番号,検索の型)

各要素の指定の仕方に関しては、いわゆるエクセルののVLOOKUP関数と全く同じですので、詳しくはこちらをご覧くださいね。

VLOOKUP関数の使い方とその威力を存分に味わうデータ準備
Excel中級への最初の難関とも言えるVLOOKUP関数。使いこなせればこれほど便利な関数もないのですが、データの準備方法に問題があると、なかなかマスターすることができません。今回の記事では、そのデータ準備で気を付けるポイントについてお伝えします。

「社員マスタ」シートをワークシートオブジェクトwsStaffにセットしてあるとしますと、Vlookupの各要素は

  • 検索値:numStaff(=ファイル名から切り出して取得した社員番号)
  • 範囲:wsStaff.Range(“A:D”)
  • 列番号:2=指名、3=部署No、4=部署
  • 検索の型:False

と指定すればよいわけですから、それぞれの値の取得は

で取得することができます。

結果としてプログラム全体としてはこのようになります。

こちらが、実行結果です。

経費精算書のデータの収集結果

それぞれのデータを正しく蓄積することができました。

まとめ

今回はマスタデータを使って人為的なミスを回避する方法についてお伝えしました。

これはVBAに限らず、社内の情報管理という面では非常に重要な考え方です。

こちらの記事にある通り

企業やチームが業務効率化を確実に達成するための3つの原則
どうすれば企業やチームの業務効率を上げることができるかを考えるときに、必ずスタート地点とする「原則」が3つあります。一般的に適用することができるルールばかりですので、ぜひ心に留めて頂ければと思います。

効率化を実現するためには情報・データは一元化すべきで、同じデータを複数箇所に複数個数の保有していてはいけないのです。

このようなマスタを社内で1つと決めておくことで、データの正確さや検索性を高く確保することができます。

ぜひ、今後のデータ管理に活用頂ければと思います。

経費精算書データを蓄積するシリーズはもう少し続きますよ。

次回はこちらの記事で

【エクセルVBA入門】データの重複を防ぐSubプロシージャの作成
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズもいよいよ8回目、そして最終回です。同じ経費精算書データを取り込む際のデータ重複を防ぐ重複削除プロシージャを作成していきます。

重複データのチェックという機能を追加できればと思います。どうぞお楽しみに!

連載目次:経費精算書のデータをデータベースに集約する

  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loopで条件を満たす間繰り返し
  3. 【エクセルVBA入門】Withでプログラムをスッキリわかりやすく書く
  4. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  5. 【エクセルVBA入門】For Each~Nextでフォルダ内のブック全てを参照する
  6. 【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する
  7. 【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用
  8. 【エクセルVBA入門】データの重複を防ぐSubプロシージャの作成