【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法


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

エクセルVBAでバラバラの経費精算書のデータを集めるマクロを作成する方法をお伝えしています。

前回の記事はコチラ。

【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
エクセル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を抜き出すというのが前回のテーマでした。

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

そして、この社員Noさえわかれば、それをキーにして以下のシートに準備した「社員マスタ」から、必要な他のデータも正しく取得できるわけです。

エクセルの社員マスタ

その部分を今回作り込んでいきますよ。

VBAでVLOOKUP関数を使う

WorksheetFunctionオブジェクトとは

社員Noをキーにして、その行の他のデータを取得する…エクセルに詳しい方ならば、VLOOKUP関数を使えばよいとパッと思いつかれるはずです。

ただし、VLOOKUP関数はエクセルのワークシートのセルに入力する関数(ワークシート関数と言います)であり、VBA関数ではありません

ですが、そのワークシート関数を使うための便利なオブジェクトがあるんです。

WorksheetFunctionオブジェクトです。

WorksheetFunctionオブジェクトは、ApplicationオブジェクトのメンバーであるWorksheetFunctionプロパティで取得することができます。

Application.WorksheetFunction

ここで、Applicationは省略可能ですので、以下のように書けばOKです。

WorksheetFunction

VLookupメソッドでデータを検索する

そのWorksheetFunctionオブジェクトですが、ワークシート関数と同様の機能を持つメンバーを持っています。

例えば、今回の目的であるVLOOKUP関数に該当するのは、VLookupメソッドです。

こんな書き方になります。

WorksheetFunction.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

実行結果がコチラです。

VLookupメソッドを仕様した結果

今までしれっと空欄だった、部署Noの欄も入るようになりました。

ちなみに、VLookupメソッドは検索値が見つからないとエラーとなり、実行が中断してしまいます。

マスタの作りには注意してくださいね。

なお、どうしてもエラーが出てしまう場合は、以下の記事にそのエラーを回避する方法がありますので、よろしければご覧ください。

【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックをお伝えしています。今回はエクセルVBAでVlookupを使ったときに発生し得るエラーを回避する方法についてお伝えします。

まとめ

以上、エクセルVBAでVLookupメソッドを使ってデータを検索する方法をお伝えしました。

さて、これまで手入力によるミスをどうやって回避するか…ということにフォーカスしてお伝えしてきました。

こちらの記事にある通り、効率化を実現するためには情報・データは一元化すべきなんですね。

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

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

次回は、今回仕込んだVLookupメソッドでエラーが出てしまったときの対応を考えていきます。

【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
エクセルVBAでバラバラの経費精算書を一つのデータベースにまとめるマクロの作り方についてお伝えしています。今回は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入門】オートフィルタや行の非表示で隠れている行を全て表示する

コメント

  1. トオリスガリーナ より:

    初めまして。
    此方の構文で勉強させて頂いています。

    「VBAは構文読解が大切と」、SEの知人に教わりました。
    構文読解に適したお勧め「書籍・サイト」が有ればお教え願います。

  2. トオリスガリーナ より:

    追記します

    構文

    解読

    解答

    という手順で勉強出来れば、凄く有難いのです。

    • トオリスガリーナさん

      私がそこまで「構文」にこだわって学んできていないので、パッと思いつかないですね…
      ネットよりは書籍のほうがまとまっているようには思えますので、書店でパラパラとご覧になってみてはいかがでしょうか。

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