【VBA】VLookupを使いこなす【実務で使えるレベルになります】
Excel VBAで、VLookup関数を使いこなす方法を解説していきます。「基本的な使用方法」から、「最終行までの検索」、「別シート参照」、「別ブック参照」、「エラー回避」、「高速化」といった実務で使える内容を解説していきます。
はじめに
この記事では、VBAでVLookup関数を使う方法について、網羅的にご紹介します。
VBAでVLookup関数を使うには「WorksheetFunction」と「埋め込み数式」を使う方法があります。
取得したデータをVBAで使いたい場合は「WorksheetFunction」を使って、高速に処理したい場合は「埋め込み数式」が使えます。
VLookup関数は、値を検索して取得できるかなり便利な関数です。
VBAでVLookup関数を活用するとさらに便利につかっていきましょう。
では、VBAでVLookup関数を使う方法について、解説していきます。
この記事を読むメリット
- VBAでVLookup関数を使いこなすことができます
目次
WorksheetFunctionでVLookup関数を使う
「WorksheetFunction」でVLookup関数を使ってみます。
検索して値を取得する
「WorksheetFunction」で「VLookup関数」を使って、検索して値を取得してみます。
VLookup関数に入力する引数は、次のようになります。
VLookup関数に入力する引数
'VLookupの引数 A = WorksheetFunction.VLookup(検索値, 範囲, 列番号, FalseかTrue) '4番目の引数・・・完全一致：True、部分一致：False
セル内でVLookup関数を使う場合と同じですね。
WorksheetFunctionでVLookupを使ってみる
WorksheetFunctionでVLookupを使うVBAコードは、次のようになります。
Sub TEST1()
'「"D"」を検索して、2列目を取得
Range("D2") = WorksheetFunction.VLookup("D", Range("A2:B10"), 2, False)
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
WorksheetFunctionでVLookupを使って検索できました。
「変数」や「セルの値」を使う
VLookup関数には「変数」や「セルの値」を使うことができます。
「変数」や「セルの値」を使ってVLookup関数を使う方法について、詳細はこちらでまとめています。
最終行まで繰り返し使う
最終行まで繰り返し使って、複数の値で検索してみます。
実務では、検索したい値は1つとは限らないので、ループして使えるようになると便利です。
最終行まで繰り返し使う
最終行まで繰り返し使ってみます。
最終行は、「End」を使うと取得することができます。
Sub TEST2()
'最終行までループ
For i = 2 To Cells(Rows.Count, "D").End(xlUp).Row
With Cells(i, "D")
'VLookup関数を計算する
.Offset(0, 1) = WorksheetFunction.VLookup(.Value, Range("A2:B15"), 2, False)
End With
Next
End Sub
では、VBAコードを実行してみます。
Endで最終行を取得します。
最終行までVLookup関数をループします
取得した結果をみてみます。
最終行まで繰り返し使ってVLookup関数で検索できました。
最終行まで繰り返し使う方法の詳細な手順
「WorksheetFunction」を使って最終行まで検索するには、繰り返す必要があります。
最終行まで繰り返し使う方法の詳細な手順について、こちらでまとめています。
別シートを参照する
VLookup関数で、「別シート」を参照してみます。
実務では、参照したいデータが同じシート内ではなく、「別シート」にあることが多いです。
別シートを参照して検索できるようにしましょう。
別シートを参照する
別シートを参照して、値を検索してみます。
Sub TEST3()
'VLookup関数で「別シート」を参照
With Worksheets("Sheet2")
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.VLookup("B", .Range("A2:B15"), 2, False)
End With
End Sub
別シートを用意しておきます。
では、VBAコードを実行してみます。
別シートを参照してVLookup関数で検索できました。
全シートを参照する
全シートを参照するには、「シート番号」を使ってループする方法と、「For Each」を使う方法があります。
VLookup関数で全シートを参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
VLookup関数で、「別ブック」を参照してみます。
実務では、別シートだけでなく、「別ブック」に参照したいデータがあることもあります。
別ブックを参照する方法もマスターしておきましょう。
別ブックを参照する
別ブックを参照して、値を検索してみます。
Sub TEST4()
'VLookup関数で「別ブック」を参照
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
End With
End Sub
ポイントは、別ブックを開いておく、ということです。
では、VBAコードを実行してみます。
「別ブック」を参照してVLookup関数で検索できました。
別ブックを「開いて」参照する
「WorksheetFunction」で別ブックを参照したい場合は、「別ブックを開く」VBAコードを追加すると便利です。
別ブックを「開いて」VLookup関数で参照する方法について、詳細はこちらでまとめています。
エラー回避する
VLookup関数のエラー回避する方法です。
WorksheetFunctionでVLookup関数を使った場合、検索結果がないときは、「エラー」となってしまいます。
VLookup関数のエラーを回避していきましょう。
「On Error Resume Next」を使う
エラーを回避するには、「On Error Resume Next」を使います。
エラーの時に任意の値を表示するには「Err.Number > 0」を利用します。
VLookup関数を使った後は、エラー回避をリセットしたいので「On Error Goto 0」を使います。
Sub TEST5()
'エラーを無視する
On Error Resume Next
'エラーを出す
Range("D2") = WorksheetFunction.VLookup("GGGG", Range("A2:B15"), 2, False)
'エラーがある場合
If Err.Number > 0 Then
Range("D2") = "エラー"
End If
'これ以降エラーを表示する
On Error GoTo 0
End Sub
表にない「GGGG」で検索してみます。
では、VBAコードを実行してみます。
「On Error Resume Next」を使って、エラー回避できました。
エラー回避の詳細な手順の解説
「Err.Number >0 」や「On Error Goto 0」の使い方について、詳細はこちらでまとめています。
埋め込み数式でVLookup関数を使う
VLookup関数を使うもう一つの方法で、「埋め込み数式」でVLookup関数を使ってみます。
検索して値を取得する
VLookup関数を埋め込む方法で、値を検索してみます。
「埋め込み数式」でVLookupを使う
「埋め込み数式」でVLookupを使うVBAコードは、こんな感じです。
ポイントは、文字列を入力する際は、「"D"」を「"」で囲って「""D""」というように入力するところです。
Sub TEST6()
'「"D"」を検索して、2列目を取得
Range("D2") = "=VLOOKUP(""D"",A2:B10,2,FALSE)"
Range("D2").Value = Range("D2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「埋め込み数式」でVLookupを使って検索できました。
「変数」や「セルの値」を使う
埋め込み数式でも「変数」や「セルの値」を使うことができます。
「変数」や「セルの値」を使ってVLookup関数を使う方法について、詳細はこちらでまとめています。
最終行まで値を検索する
「埋め込み数式」を使って、最終行まで値を検索してみます。
「埋め込み数式」の場合は、ループする必要がなく、値を一括で入力すると最終行まで検索することができます。
最終行まで値を検索する
最終行まで値を検索するVBAコードは、こんな感じです。
Sub TEST7()
'最終行を取得
A = Cells(Rows.Count, "D").End(xlUp).Row
'VLookup関数を埋め込む
Range("E2:E" & A) = "=VLOOKUP(D2,$A$2:$B$15,2,FALSE)"
Range("E2:E" & A).Value = Range("E2:E" & A).Value '値に変換
End Sub
では、VBAコードを実行してみます。
Endで最終行を取得します。
最終行までのセル範囲に数式を埋め込みます。
では、実行した結果をみてみます。
最終行まで値を検索できました。
最終行までの値を検索する詳細な手順
埋め込み数式を一括で入力する方法は、VBAコードがシンプルで使いやすいです。
最終行までの値を検索する手順について、詳細はこちらでまとめています。
別シートを参照する
「埋め込み数式」でも別シートを参照することができます。
別シートを参照する
別シートを参照するVBAコードです。
「Sheet2!A2:B15」というように「シート名」まで入力するところがポイントです。
Sub TEST8()
With Worksheets("Sheet1")
'VLookup関数で別シートを参照
.Cells(2, "A") = "=VLOOKUP(""B"",Sheet2!A2:B15,2,FALSE)"
.Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
End With
End Sub
別シートを用意しておきます。
では、VBAコードを実行してみます。
別シートを参照してVLookup関数で検索できました。
全シートを参照する
全シートを参照するには、「シート番号」を使ってループする方法と、「For Each」を使う方法があります。
VLookup関数で全シートを参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
「埋め込み数式」で別ブックを参照してみます。
「埋め込み数式」が使うと、別ブックを閉じたまま参照することができるので便利です。
まずは、開いた状態の別ブックを参照してみます。
別ブックを参照する
開いた状態の別ブックを参照するVBAコードです。
「[TEST.xlsx]Sheet1!$A$2:$B$10」というように「ブック名」まで入力するところがポイントです。
Sub TEST9()
'数式埋め込みでVLookup関数を使って、別ブックを参照
With ThisWorkbook.Worksheets("Sheet1")
.Range("A2") = "=VLOOKUP(""D"",[TEST.xlsx]Sheet1!$A$2:$B$10,2,FALSE)"
.Range("A2").Value = .Range("A2").Value '値に変換
End With
End Sub
別ブックを開いておきます。
では、VBAコードを実行してみます。
別ブックを参照してVLookup関数で検索できました。
閉じたまま別ブックを参照する
閉じたまま別ブックを参照してみます。
ポイントは、「'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10」というように「フルパス」で入力するということです。
Sub TEST10()
'数式埋め込みで、別ブックを「フルパス」で参照
With ThisWorkbook.Worksheets("Sheet1")
.Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"
.Range("A2").Value = .Range("A2").Value '値に変換
End With
End Sub
では、VBAコードを実行してみます。
閉じたまま別ブックを参照して値を検索できます。
「閉じたまま」別ブックを参照して値を検索できました。
フルパスの数式を作成する手順
フルパスの数式は手入力でやると大変です。
フルパスの数式で表示させて、コピーして使いましょう。
では、手順です。
Cドライブの「TEST」というフォルダに別ブックを保存しています。
別ブックを「開いた状態」で、数式を入力して別ブックを閉じます。
入力した数式のセル範囲が、「フルパス表示」になるので数式を「コピー」して使います。
VBAコードを実行するとこんな感じで、別ブックを閉じた状態で参照することができます。
別ブックを閉じた状態で参照することができました。
閉じたまま参照すると高速
閉じたままVLookup関数で参照すると高速になります。
「埋め込み数式」を使って閉じたまま参照すると「開いて閉じるを繰り返して取得する方法」より90倍ぐらい速くなります。
時間を計測した結果について、詳細はこちらでまとめています。
エラー回避する
「埋め込み数式」のVLookup関数のエラーを回避する方法です。
「IFERROR関数」を使うとエラーだった場合に、任意の文字を入力することができます。
「IFERROR関数」を使う
「IFERROR関数」を使って、エラー処理をしてみます。
Sub TEST11()
'「IFERROR」を使う
Range("D2") = "=IFERROR(VLOOKUP(""GGGG"",A2:B10,2,FALSE),""エラー"")"
Range("D2").Value = Range("D2").Value '値に変換
End Sub
表にない「GGGG」を検索します。
では、VBAコードを実行してみます。
「IFERROR関数」を使ってエラー時に任意の文字「エラー」を入力できました。
埋め込み数式を一括入力して高速化する
VLookup関数を高速に使いたい場合は、「埋め込み数式を一括入力」が使えます。
大量データで検索してみる
大量データで「埋め込み数式」を使って検索してみます。
埋め込み数式を一括入力する
埋め込み数式を一括入力するVBAコードです。
Sub TEST12()
t = Timer
'埋め込み数式でVLookup関数を使う
Range("B2:B10001") = "=VLOOKUP(A2,$D$2:$E$50001,2,FALSE)"
Range("B2:B10001").Value = Range("B2:B10001").Value '値に変換
Debug.Print Timer - t & " 秒"
End Sub
シンプルですね。
大量データを用意しておきます。
では、VBAコードを実行してみます。
埋め込み数式を一括入力して検索できました。
かかった時間は、「0.47 秒」です。
WorksheetFunctionで繰り返した場合は、「30 秒」程度かかりますので、高速化したい場合は、「埋め込み数式」です。
さらに高速化したい場合は「Dictionary」
「数式埋め込み」を使えば十分高速です。
でも、「もっと高速化したい」という場合は、「Dictionary」を使うことを検討してみるといいです。
「Dictionary」を使ってVLookup関数の機能を高速化する方法について、詳細はこちらでまとめています。
おわりに
この記事では、VBAでVLookup関数を使う方法について、網羅的にご紹介しました。
VBAでVLookup関数を使うには「WorksheetFunction」と「埋め込み数式」を使う方法があります。
取得したデータをVBAで使いたい場合は「WorksheetFunction」を使って、高速に処理したい場合は「埋め込み数式」が使えます。
VLookup関数は、値を検索して取得できるかなり便利な関数です。
VBAでVLookup関数を活用するとさらに便利につかっていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す