【Excel・VBA】VLOOKUP関数の使い方

VLOOKUP関数を使う方法について説明します。

VLOOKUP関数の基本
1. VLOOKUP関数の使い方（エクセル）
2. VLOOKUP関数の呼び出し（VBA）
  1. サンプルコード
VLOOKUP関数の検索範囲の指定方法
1. ワークシート上で指定する
2. 配列を利用する
  1. サンプルコード

VLOOKUP関数の基本

エクセルでのVLOOKUP関数の使い方とVBAでの呼び出し方

VLOOKUP関数の使い方（エクセル）

VLOOKUP関数は、任意の検索値（文字列）を表の一番列から検索し、検索値を含む行の中で指定した列の値を返します。

例として上記表における、Bの身長を検索する場合の関数の引数の指定は次のようになります。

指定したセルにBの身長が出力されました。

VLOOKUP関数の呼び出し（VBA）

今度は、VBAでVLOOKUP関数を呼び出す方法です。

VBAではエクセル関数をApplicationオブジェクトのWorksheetFunctionプロパティを利用して関数を呼び出します。

《VLOOKUP関数の呼び出し》
　Application.WorksheetFunction.VlLookup（検索値, 範囲, 列番号, 検索方法）

例えば、先ほどの表からBの身長を検索し、セル（6,8）に出力するコードは次のようになります。

サンプルコード

Sub sample()
　Cells(6, 8) = Application.WorksheetFunction.VLookup _
　(Cells(6, 7), Range(Cells(6, 3),Cells(11, 5)), 2, False)
End Sub

このコードの説明は次の通りです。

また、エクセル関数の呼び出し方については、 VBA】エクセル関数の使い方でも説明していますのでご参照ください。

VLOOKUP関数の検索範囲の指定方法

ここでは、VLOOKUP関数の引数の一つである検索範囲の指定方法について説明します。なお、ここでの基準は、値を返すシートを基準としています。

ワークシート上で指定する

同一シートのセル範囲を参照する

検索範囲が値を返すセルと同一のシート内に存在する場合は、次のように書くことができます。

《検索範囲と値の出力セルが同一の場合》
　range(cells(○,△) , cells(●,▲))
　※○,△,●,▲はセルを指定する任意の数字

別シートのセル範囲を参照する

次は、検索範囲が値を返すセルと別のシートにある場合です。

《検索範囲が値を出力セルと別シートの場合》　
　range(Worksheets(”シート名”).cells(○,△), _
　　　　Worksheets(”シート名”).cells(○,△))
　※○,△,●,▲はセルを指定する任意の数字

セル指定の前にWorksheets（”シート名”）が追加されます。

またこの書き方は、検索範囲が同一シートの場合でも適用可能です。

別ブックのセル範囲を参照する

今度は、検索範囲が別ブックにある場合です。

《検索範囲が値を出力セルと別ブックの場合》　
range(Workbooks(“ブック名”).Worksheets(”シート名”).cells(○,△), _
Workbooks(“ブック名”). Worksheets(”シート名”).cells(○,△))
　※○,△,●,▲はセルを指定する任意の数字

別ブックの場合は、最初にWorkbooks(“ブック名”)でブックを指定します。注意点としては、ブック名は拡張子を含めて指定します。また、参照するワークブックは事前に開いておく必要があります。

またこの書き方は、検索範囲が別シート及び同一シートの場合でも適用可能です。

配列を利用する

検索範囲の指定を配列を使用して書く方法です。大きな表から繰り返し検索をする場合にお勧めです。

配列を検索範囲に指定する際のポイントは次の通りです。

配列の型は文字列にする。
配列の大きさは動的に指定する。（検索範囲が固定の場合は静的でもOK）
配列の大きさは検索範囲と同じにする。
検索範囲は”配列名”()と書く。

先ほどと同様に下の表から、Bの身長を検索するコードを配列を利用して書いてみます。

サンプルコード

Sub sample()
 Dim cfi As String
 Dim cws As String
 Dim hyou() As String
 Dim tate As Integer
 Dim yoko As Integer
 Dim tg As String
 Dim i As Integer
 Dim ii As Integer
 Dim col As Integer
 Dim ro As Integer

 cfi = ThisWorkbook.Name
 cws = "Sheet1"
　
 With Workbooks(cfi).Worksheets(cws)
  tate = Application.WorksheetFunction.CountA _
  (Range(.Cells(6, 3), .Cells(6, 3).End(xlDown)))
  yoko = Application.WorksheetFunction.CountA _
　(Range(.Cells(6, 3), .Cells(6, 3).End(xlToRight)))
  tg = .Cells(6, 7)
 End With
 
 ReDim hyou(1 To tate, 1 To yoko)

 col = 6
 ro = 3
 For i = 1 To tate
  For ii = 1 To yoko
　 hyou(i, ii) = Workbooks(cfi).Worksheets(cws).Cells _
　 (col, ro + ii - 1)
　Next
  col = col + 1
 Next
 Cells(6, 8) = Application.WorksheetFunction.VLookup _
 (tg, hyou(), 2, False)

End Sub

全体的な流れとしては、①配列の大きさに関する情報を取得②配列を定義③VLOOKUP関数による値の出力です。コードを実行すると、アクティブなシートのセル(6,8)へ値が出力されます。

サンプルコードの大まかな説明は次の通りです。