photo credit: Banana Fana Fo Famine via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでバラバラの経費精算書のデータを集めるマクロを作成する方法をお伝えしています。
前回の記事はコチラ。
開いたブックのファイル名から番号を切り出して、さらにそれを数値型に変換する方法をお伝えしました。
このようにして、社員Noを取得することができましたので、それをキーにして他のデータを引っ張ってきます。そう、エクセルユーザーはみんな大好き、VLOOKUP関数です。
ということで、エクセルVBAでVLOOKUP関数を使ってデータを検索する方法です。
では、行ってみましょう！
前回までのおさらい
前回までに作成したコードはコチラです。
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 = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
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
社内の皆さんから集めてきた経費精算書を同じフォルダに格納した上で実行すると、「経費データ」というシート（オブジェクト名はwsData）に、経費データを蓄積してくれます。
手入力によるミスをなくすための方針
だだし、経費精算書の以下の部分が手入力なので、人為的なミスが混入しやすいわけです。
コードでいうと15行目にあたりますが、以下の形式で構成された経費精算書のファイル名から社員Noを抜き出すというのが前回のテーマでした。
そして、この社員Noさえわかれば、それをキーにして以下のシートに準備した「社員マスタ」から、必要な他のデータも正しく取得できるわけです。
その部分を今回作り込んでいきますよ。
VBAでVLOOKUP関数を使う
WorksheetFunctionオブジェクトとは
社員Noをキーにして、その行の他のデータを取得する…エクセルに詳しい方ならば、VLOOKUP関数を使えばよいとパッと思いつかれるはずです。
ただし、VLOOKUP関数はエクセルのワークシートのセルに入力する関数（ワークシート関数と言います）であり、VBA関数ではありません。
ですが、そのワークシート関数を使うための便利なオブジェクトがあるんです。
WorksheetFunctionオブジェクトです。
WorksheetFunctionオブジェクトは、ApplicationオブジェクトのメンバーであるWorksheetFunctionプロパティで取得することができます。
ここで、Applicationは省略可能ですので、以下のように書けばOKです。
VLookupメソッドでデータを検索する
そのWorksheetFunctionオブジェクトですが、ワークシート関数と同様の機能を持つメンバーを持っています。
例えば、今回の目的であるVLOOKUP関数に該当するのは、VLookupメソッドです。
こんな書き方になります。
引数の指定の仕方に関しては、いわゆるエクセルののVLOOKUP関数と同じに見えますよね。
今回の場合は、「社員マスタ」シートのA列について社員Noで検索をするわけですから、各引数の指定は以下のようになります。
- 検索値: staffId（＝ファイル名から切り出して取得した社員番号）
- 範囲: wsStaff.Range(“A:D”)
- 列番号: 2＝氏名、3＝部署No、4＝部署
- 検索の型：False
ですから、それぞれの値の取得は以下のようにすることができるわけです。
Dim staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
Dim departmentId As Long: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)
Vlookupメソッドを使ってデータを収集するプログラム
以上を踏まえて、コードを修正したものがコチラです。
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 staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
Dim departmentId As Long: departmentId = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)
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 = departmentId '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
実行結果がコチラです。
今までしれっと空欄だった、部署Noの欄も入るようになりました。
ちなみに、VLookupメソッドは検索値が見つからないとエラーとなり、実行が中断してしまいます。
マスタの作りには注意してくださいね。
なお、どうしてもエラーが出てしまう場合は、以下の記事にそのエラーを回避する方法がありますので、よろしければご覧ください。
まとめ
以上、エクセルVBAでVLookupメソッドを使ってデータを検索する方法をお伝えしました。
さて、これまで手入力によるミスをどうやって回避するか…ということにフォーカスしてお伝えしてきました。
こちらの記事にある通り、効率化を実現するためには情報・データは一元化すべきなんですね。
ぜひ、今後のデータ管理に活用頂ければと思います。
次回は、今回仕込んだVLookupメソッドでエラーが出てしまったときの対応を考えていきます。
どうぞお楽しみに！
連載目次：エクセルVBAで経費データをデータベースに集約する
請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。- 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
- 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
- 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
- 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
- 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
- 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
- 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
- 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
- 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
- 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
- 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
- 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
- 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
- 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
- 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
コメント
初めまして。
此方の構文で勉強させて頂いています。
「VBAは構文読解が大切と」、SEの知人に教わりました。
構文読解に適したお勧め「書籍・サイト」が有ればお教え願います。
追記します
構文
↓
解読
↓
解答
という手順で勉強出来れば、凄く有難いのです。
トオリスガリーナさん
私がそこまで「構文」にこだわって学んできていないので、パッと思いつかないですね…
ネットよりは書籍のほうがまとまっているようには思えますので、書店でパラパラとご覧になってみてはいかがでしょうか。