Excel VBA VLOOKUP関数の使い方と、エラー処理やループの方法

ExcelVBA-基礎編

VBAでワークシート関数のVLOOKUP関数の使い方についてご説明します。

ワークシート関数の「VLOOKUP」を使用する事で、簡単に検索範囲から検索値に対応する値を取得する事ができます。

Excelで非常に利用頻度の高い馴染みのある関数かと思います。

ExcelVBAの利点はワークシートという高機能なアプリケーションを、最初から使える事です。

そして、最大の利点はExcelに用意されている関数を使える事です。

その関数の一つ、「VLOOKUP関数」の使い方についてご説明します。

1.VLOOKUP関数とは

VLOOKUP関数は検索条件に一致するデータを指定した範囲の中から検索して、対応したデータの指定した列のデータを返す関数です。

膨大なデータから対応するデータを目視で探すのは非常に大変な作業ですし、検索したい値が多くなるとさらに大変です。

それらを簡単に自動的に行ってくれる非常に便利な関数です。

VLOOKUP関数の欠点

非常に便利な関数ですが、1点注意する事があります。

もし対応するデータが複数あった場合・・・。

一番上の対応するデータを取得してしまうため、それ以下にあるデータを抽出する事ができません。

もし全ての値を抽出する場合はそれなりに工夫が必要になります。

図にすると次のように感じです。

また、検索するデータ量が多くなるとExcel2013以前のバージョンでは処理時間が非常に長くなります。

もしすでに「VLOOKUP関数」をVBAで使いこなしていて、処理時間にお悩みの方は「VLOOKUPの高速化」をご覧ください。

2.VLOOKUP関数の使い方

VLOOKUP関数は次のように記述します。

VLOOKUP(検索値, 範囲, 列番号, 検索方法)

「検索値」を「範囲」の 1 列目(一番左の列)のデータと比較し、「検索方法」で検索が一致した一番最初の行の「列番号」で指定した値を取得します。

VBAで利用するには次のように記述します。

Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法)

引数一覧
引数省略説明
検索値必須検索する値を、数値、文字列、セル参照、関数などで指定します。
引数「検索方法」を FALSEに指定した場合は、ワイルドカードを使用できます。
範囲必須検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。
この範囲の一番左の列を検索します。引数「検索の型」に TRUE を指定するときは、
一番左の列が昇順に並んでいる必要があります。
列番号必須取得する列番号を指定します。
引数「範囲」の1番左の列が1列目になります。
左から 2 番目の列が2列目になります。
検索の型省略可能TRUEもしくは「1」または省略した場合は、検索する値に1番近い小さい値を取得します。
FALSEもしくは「0」を指定した場合は、検索する値に完全一致する値を取得します。
VBAでVLOOKUP関数を使う注意点

基本的にExcelと使い方は同じなのですが、検索値が見つからなかった場合のエラーが発生した時の挙動が異なります。

Excelではセルに「#N/A」と表示されるだけですが、VBAでは処理を組み込まない場合は停止してしまいます。

見つからない場合は意外とよく起こりうることなので、しっかりとエラー処理を組み込む必要があります。

エラー処理の詳細については「「On Error」でエラー処理」をご覧ください。

3.サンプルコード

そのまますべて記述する基本的な方法から、検索値や範囲を変数に入れて処理する方法、エラー処理の方法などいくつかのケースのサンプルコードです。

次のようなデータを使用して、C列にB列とE列が一致するF列の価格します。

基本的なサンプルコード

まずは「B4」のみ検索してC4に出力するコードです。

Sub Sample1()

Cells(4, 3) = Application.WorksheetFunction.VLookup _
            (Cells(4, 2), Range(Cells(4, 5), Cells(23, 6)), 2, False)

End Sub
コードの説明

『Cells(4, 3)= Application.WorksheetFunction.VLookup』の「Cells(4, 3)」はC4を指しており、検索結果を出力します。

「Application.WorksheetFunction.VLookup」でVBAでも「VLOOKUP関数」を使用できるようにしています。

『(Cells(4, 2), Range(Cells(4, 5), Cells(23, 6)), 2, False)』はそれぞれ引数を指定します。

「(Cells(4, 2)」はB4、「Range(Cells(4, 5), Cells(23, 6))」はE4～F23を指定しています。

「2」はE列が1列目、F列が2列目ということになりますので2を指定します。

最後の「False」は完全一致を指定します。

変数を使ったサンプルコード

次は変数を使用したサンプルコードです。

変数に入れる事でコードがシンプルになりますし、動的にも対応させやすいです。

Sub Sample2()

Dim SearchWord  As String '検索値
Dim SearchRange As Range '検索範囲

SearchWord = Cells(4, 2)
Set SearchRange = Range(Cells(4, 5), Cells(23, 6))

Cells(4, 3) = Application.WorksheetFunction.VLookup _
            (SearchWord, SearchRange, 2, False)

End Sub
コードの説明

変数は「SearchWord」は検索値で、「SearchRange」を検索範囲としています。

検索値は文字列でよいのでStringと宣言していて、検索範囲はセル範囲なのでRangeで宣言しています。

『(SearchWord, SearchRange, 2, False)』が先ほどのSample1コードの引数の指定部分になります。

長々と書かれていたコードがシンプルになりました。

ループするサンプルコード

せっかく複数のデータサンプルを使用するので、ループさせたサンプルも記載します。

ループには「For~Next」を使用します。

ループについての詳細は「ループの使い方」をご覧ください。

ついでに行数が変動しても対応できるように、最終行も変数に格納してみます。

Sub Sample3()

Dim SearchWord  As String '検索値
Dim SearchRange As Range '検索範囲
Dim WordMaxRow  As Long '検索値最終行
Dim RangeMaxRow As Long '検索範囲最終行
Dim i           As Long

WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row '検索値最終行
RangeMaxRow = Cells(Rows.Count, 5).End(xlUp).Row '検索範囲最終行

Set SearchRange = Range(Cells(4, 5), Cells(RangeMaxRow, 6)) '検索範囲格納

For i = 4 To WordMaxRow '検索値数分ループ

    SearchWord = Cells(i, 2) '検索値格納
    
    Cells(i, 3) = Application.WorksheetFunction.VLookup _
                (SearchWord, SearchRange, 2, False) '検索
                
Next i

End Sub

すべての値が入りましたが、コードが一気に複雑になりました。

コードの説明

変数がさらに追加されて全部で5個になりました。

『WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row』でB列の最終行を取得しています。

『RangeMaxRow = Cells(Rows.Count, 5).End(xlUp).Row』で今度はE列の最終行を取得してします。

「Set SearchRange = Range(Cells(4, 5), Cells(RangeMaxRow, 6)) 」は最終行までの範囲を指定しています。

この変数を使用する事でループする回数と、検索範囲が動的でも常に最終行まで処理されます。

『For i = 4 To WordMaxRow』～『Next i』で検索したい行数分ループします。

ループの中に『SearchWord = Cells(i, 2)』とループするたびに検索値に格納するセルを変更しています。

『Cells(i, 3) = Application.WorksheetFunction.VLookup(SearchWord, SearchRange, 2, False)』も「Cells(i, 3) 」をループに合わせて出力セルを変更しています。

エラー処理のサンプルコード

さて、上記の3つのコードで基本的なVLOOKUPは対応できると思いますが、最後にエラー処理のサンプルコードです。

VBAでVLOOKUP関数を使用する場合は、検索値が見つからない場合はエラーで停止してしまいますので、エラー処理の組み込みが必要になります。

On Error Resume Nextのサンプルコード

「On Error Resume Next」はエラーをすべて無視します。

この方法が一番手っ取り早いです。

また、「On Error Resume Next」は解除しないとエラーを無視し続けるため、解除する必要があり、解除するところで「On Error GoTo 0」と記述します。

Sub Sample4()

Dim SearchWord  As String '検索値
Dim SearchRange As Range '検索範囲
Dim WordMaxRow  As Long '検索値最終行
Dim RangeMaxRow As Long '検索範囲最終行
Dim i           As Long

WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row '検索値最終行
RangeMaxRow = Cells(Rows.Count, 5).End(xlUp).Row '検索範囲最終行

Set SearchRange = Range(Cells(4, 5), Cells(RangeMaxRow, 6)) '検索範囲格納

On Error Resume Next

For i = 4 To WordMaxRow '検索値数分ループ

    SearchWord = Cells(i, 2) '検索値格納
    
    Cells(i, 3) = Application.WorksheetFunction.VLookup _
                (SearchWord, SearchRange, 2, False) '検索
                
Next i

On Error GoTo 0'エラー無視解除

End Sub
On Error GoTo ラベル名のサンプルコード

「On Error GoTo ラベル名」はエラーが発生したら、ラベル名の位置までスキップするコードです。

スキップ先は「ラベル名：」と記述します。

1点注意があり、エラーが起きなかった場合でもラベル名以降に処理が実行されるため、ラベル名前に「Exit Sub」で離脱する必要があります。

Sub Sample5()

Dim SearchWord  As String '検索値
Dim SearchRange As Range '検索範囲
Dim WordMaxRow  As Long '検索値最終行
Dim RangeMaxRow As Long '検索範囲最終行
Dim i           As Long

WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row '検索値最終行
RangeMaxRow = Cells(Rows.Count, 5).End(xlUp).Row '検索範囲最終行

Set SearchRange = Range(Cells(4, 5), Cells(RangeMaxRow, 6)) '検索範囲格納

On Error GoTo ErrLabel 'エラー発生時のラベル名指定

For i = 4 To WordMaxRow '検索値数分ループ

    SearchWord = Cells(i, 2) '検索値格納
    
    Cells(i, 3) = Application.WorksheetFunction.VLookup _
                (SearchWord, SearchRange, 2, False) '検索
                
Next i

Exit Sub 'エラーが発生しなかった場合、ここで離脱

ErrLabel: 'エラー発生時にここまでスキップ

MsgBox i & "回目でエラーが発生しました。"

End Sub
スポンサーリンク
タイトルとURLをコピーしました