【エクセル・VBA】VLOOKUP関数の使い方

Excel

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

VLOOKUP関数の基本

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

VLOOKUP関数の使い方(エクセル)

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)へ値が出力されます。

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

コードの流れ

VLOOKUP関数についてはここまでです。

コメント

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