【エクセルVBA入門】人為的なミスを回避する方法その1~ファイル名を活用する

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


みなさん、こんにちは!
よくうっかり間違いをしますタカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズですが、今回で第6回目となりました。

前回は、各社員の皆さんから集めた経費精算書をフォルダの中に集めておいて、自動でフォルダ内のファイル全てをデータベースに追加するというところまで進めました。

【エクセルVBA入門】For Each~Nextでフォルダ内のブック全てを参照する
エクセルVBAを使ってバラバラの経費精算書のデータをデータベースに集約するシリーズの第5回です。今回はフォルダ内の複数のワークブック全てを順番に参照して処理していく方法についてお伝えします。

For Each~NextやFSOオブジェクトなど新たな項目も盛りだくさんでしたが、これでだいぶ実用的なプログラムにすることができました。

今回以降で、これをよりブラッシュアップしていきたいと思います。

実際に業務で使うことを想定しながらいくつか書類やデータの作り方、プログラムを工夫することで、業務フロー全体の精度や効率を上げていくことができるようになります。

前後編に分けてそのいくつかのテクニックについてお伝えしていきたいと思います。

今回はその前編、よろしくお願いします!

スポンサーリンク

前回のおさらい

まずおさらいですが、前回のプログラムはこちらでした。

フォルダを取り扱うために、FSOオブジェクト変数objFSOを準備し、For Each~Nextでフォルダ内のすべてのファイルについて処理をする繰り返し処理としています。

個々の経費精算書ファイルからのデータの転記は、ほぼこれまでと同様のものを活用できました。

少しプログラムが長くなってきましたが、一つ一つのブロックで考えればそんなに難しいことはしていませんので、前回の記事も参考にしながら復習してみてください。

データを追加できるようにする

さて、新しい月になったらその分の新たな経費精算書が集まってきますね。

このままでは追加ではなくて上書きになってしまいますので、少し修正をする必要があります。

プログラムでいうと「経費データ」シートのセル位置をつかさどるカウント変数jの開始位置が

にセットされていることが良くないですね。毎回2行目からの書き込みになってしまいます。

従ってこれを

とします。これで、すでにあるデータの次の行から書き込みを開始するようになります。

Rows.Countについては、詳しくはこちらをご覧ください。

【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

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

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

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

…間違っていますね。

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

また氏名の箇所ですが、苗字と名前の間のスペースが先月は全角だったのに、今回は半角になっています。

これはエクセルから見ると、同一人物という判断にはならず、フィルタでも関数でも集計の邪魔になってしまいます。

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

担当者にミスを指摘して「どうしてあなたはいつも間違えるのですか?次こそは絶対に間違えないようにしなさい」などという言葉とともに精神的なダメージを与えて、次の注意を促しますか?

それでは絶対にミスはなくなりません。なぜなら、ミスをする理由は

  • うっかりした
  • 「楽をしよう」という気持ち

のどちらかだからです。

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

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

では「楽をしよう」という気持ちには説教が効果的でしょうか?

それも効果がありません。なぜなら、書類に日付をちゃんと書くとか、姓名の間のスペースを半角にしたり全角にするなどといったことは、多くの担当者にとってはあまり大事なことではないのです。

本人が大事と思っていないのにガミガミ言っても効果はありません。

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

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

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

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

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

この赤囲みの部分

  • 対象月
  • 所属部署
  • 氏名

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

それはファイル名を使うことです。

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

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

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

としてもらいます。

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

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

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

となります。

経費精算書の場合は、前月のファイルをコピーして使う場合が多いと思います。社員番号はいじる必要がないので、年月だけ修正をする形になりますが、むしろ年月を変えないと複製ができない(同じファイル名では保存できませんから)ので、おそらく変更をすることになるでしょう。

また受け取った側も年月のミスは見つけやすいと思います。フォルダにまとめた時点で、最初の6桁がそろってないとおかしいですからね。

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

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

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

Left(文字列,文字数)

Right(文字列,文字数)

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

年月はファイル名の最初の6文字ですから、文字列型の変数strPeriodを使って

とすればOKです。次に、この6文字をさらに年と月に分けて、日付形式にします。年、月、日を指定して日付を生成したい場合はDateSerial
を使って

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

【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
請求データ一覧から請求書を自動で作成するシリーズのVBA講座の7回目です!今回はInputBoxによる入力フォームの表示と月末日の自動算出を活用して実用に耐えうるプログラムの完成を目指していきます。

ここまでをまとめますと

となります。

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

まとめ

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

また、ファイル名を使ってその人為的なミスを事前に回避、無効化する具体的なテクニックの前半部分として、特定の文字列から指定文字数を抜き出すLeft,Rightを使った方法についてお伝えしました。

次回はこちらの記事で

【エクセルVBA入門】人為的なミスを回避する方法その2~マスタを利用
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務フロー上の人為的なミスを回避する有効なテクニックとしてもう一つ、マスタの活用についてお伝えしていきます。

社員番号とマスタデータをうまく活用して、氏名や部署についてミスなくデータ追加をしていく方法についてお伝えしていきます。

どうぞお楽しみに!

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

  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プロシージャの作成