【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法


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

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

前回の記事はコチラ。

【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。今回は、実行のたびにデータをシートに追加できるようにします。また、最終行番号を求めるステートメントについて徹底的に解説をしていきます。

最終行数を求め、経費データを追加していけるように修正をしました。

さて、実際に業務に使う場合、他のユーザーが間違えて想定しない入力をしてしまったりすることがあります。

そのような問題を先回りして備えることで、業務全体の精度や効率を上げていくことができるようになりますよね。

今回から、そのいくつかのテクニックについてお伝えしていきますよ。

まずは、「ファイル名」を活用してみたいと思います。

ということで、エクセルVBAで開いたブックのファイル名から指定した文字数の文字列を抜き出す方法です。

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

前回のおさらい

まずおさらいからです。

マクロを記述しているブックには以下のようなデータを集めるための「経費データ」というシートがあります。

経費データフォーマット

各スタッフがそれぞれ作成した経費精算書が、同じフォルダの「data」というフォルダ配下にゴソっと格納されていて、その経費データを収集しようというものです。

例として太郎さんの経費精算書はこんな感じです。

経費精算書ひな形

それで、フォルダ内のファイルすべてについて開いて、データを収集するプログラムがコチラです。

前回仕込んだ最終行番号を求めるステートメントのおかげで、毎月実行するとデータがどんどん追加で蓄積されるようになりまいた。

人が入力することによる人為的なミスは必ず起こる

さて、では新しい月になり新たに集まってきた経費精算書について、このプログラムを使ってデータ追加をしてみましょう。

経費データに入力ミスが含まれている

データに入力ミスが…ありますね。

まず、対象月ですが本来は8月とすべきところを、先月のファイルを使いまわしたのでしょう、7月のままになっています。

また、氏名の箇所ですが、苗字と名前の間のスペースが先月はあったのに、今回は入れ忘れてしまっています。これはエクセルから見ると、同一人物という判断にはならず、フィルタでも関数でも集計の邪魔になってしまいます。

上記のようなミスはよくあるパターンですが、皆さんはどのように対処をされますか?

担当者にミスを指摘して「どうしてあなたはいつも間違えるのですか?次こそは絶対に間違えないようにしなさい」などと、口酸っぱく言い続けますか?

しかし、残念ながらそれでは解決は難しいはずです。

人間はコンピュータと違い、必ずどこかでうっかりミスをします。100%とか絶対ということはありません。

うっかりミスに対して怒るだけ無駄です。

それよりも、業務フローを組み立てる際は、このうっかりが必ず存在するということを前提に組み立ててしまうのが有効です。

人為的な入力ミスを事前に回避もしくは無効化する方法

では、この件について具体的に業務フローで解決をしてしまいましょう。

経費精算書のフォーマットを再度確認してみましょう。

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

この赤囲みの部分ですが、手入力なので、それをそのまま使うと入力ミスが混在する可能性があります。

ただ、その全てについて、その入力ミスをほぼ事前に発見したり、無効化したりができるようになります。

まず、その1つ目の手法がファイル名を使うことです。

ファイル名に命名規則を与える

経費精算書を提出してもらうときのファイル名の命名ルール

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

としてもらいます。

重要なポイントはファイル名の頭から6桁の年月(YYYY:西暦で年を表す+MM:2桁で月を表す)と、末尾の4桁の社員番号です。

例えば、太郎さんの社員番号が3001であれば、その8月分の経費精算書のファイル名は

201808経費精算書_経費_3001.xlsx

となります。

これで、ミスを減らすことができるロジックは以下のとおりです。

まず、経費精算書の場合は、前月のファイルをコピーして使う場合が多いと思います。

社員番号はいじる必要がないので、年月だけ修正をする形になるはずです。

むしろ、年月を変えないと複製ができない(同じファイル名では保存できませんから)ので、おそらく変更をすることになるでしょう。

また、受け取った側も年月のミスは見つけやすいはずです。

というのも、フォルダにまとめた時点で、最初の6桁がパッと見で揃っていないと、おかしいですからね。

ファイル名から文字を切り出し対象月を生成

For Each~Nextルーチン内で使用しているファイル名はf.Nameで取得できますから、このファイル名を加工していきます。

Fileオブジェクト.Name

特定の文字列の最初もしくは最後から指定して文字数を抜き出す場合は、Left関数Right関数を使います。

Left(文字列,文字数)
Right(文字列,文字数)

では、これらを使って、まずはファイル名から年月を抜き出します。

年月はファイル名の最初の6文字ですから、文字列型の変数periodを使って以下のようにすればOKです。

次に、この6文字をさらに年と月に分けて、日付形式にします。

年、月、日を指定して日付を生成したい場合はDateSerial関数を使って

とします。日は今回は1で固定です。DateSerialの使い方についてはこちらを参考ください。

【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
初心者向けエクセルVBAのシリーズとして請求データから請求書を作るマクロの作り方についてお伝えしています。 今回はDateSerial関数を使って月末日または翌月末日を自動算出する方法についてお伝えします。

ファイル名の年月を利用した経費データ収集プロシージャ

まとめのコードはコチラです。

実行結果はコチラ。入力ミスがあってもファイル名から正しい対象月を拾うことができています。

030 ファイル名から対象月を取得して転記した

この方法を使えば、そもそも書類に対象月を入力してもらう必要もなくなりますね。

まとめ

今回は、まず人が入力することによって人為的なミスは必ず起こり得ること、そしてそれを前提にシステムを組み立てることについてお伝えしました。

また、入力ミスをしづらい、または発見しやすい方法として、ファイル名からLeft関数やRight関数を使って、年月を抜き出して使う方法を紹介しました。

さて、次回はマスタデータを準備して、氏名や部署についてデータを追加していく方法をお伝えします。

【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。

どうぞお楽しみに!

連載目次:エクセル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の開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

コメント