Excel関数の中では比較的よく使うVLOOKUP関数ですが、そのVLOOKUP関数をExcelVBAで書く基本と処理を高速化する方法を初心者向けにやさしく解説します。
ExcelのVLOOKUP関数とはどのような機能ですか？
それではまず、ExcelのVLOOKUP関数についておさらいしましょう。
VLOOKUP関数とは？
指定したキーワードを表形式のデータから縦方向(Vertical)に検索し(Lookup)、一致した値と同じ行にあるデータを返す関数のこと。
基本構文
=VLOOUP(検索値,範囲,列番号,検索の型)
- 検索値：検索するキーワード
- 範囲：検索する対象の表形式のデータ
- 列番号：求めるデータの対象の列
- 検索の型：完全一致はFALSE、近似一致はTRUE
ExcelVBAでVLOOKUP関数を書く方法は、大まかに言ってWorksheetFunctionのVLookupメソッドを使用する方法とセルに数式を直接記述する方法の二通りがあります。
主な使い分けとしては、変数やif文などを組み合わせてプログラムを作ったり、計算結果などをその後の処理に活用するような場合には、WorksheetFunctionを使う方がコードが直感的で可読性がよく、データ量が多く処理を高速にしたい場合はセルに直接記述することが多いです。
ここがVLOOKUP関数をExcelVBAで書くメリットなのですね。
WorksheetFunctionを使う方法
実際に上記で使用したエクセルシートの関数をExcelVBAで書いてみます。
Sub sample()
Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("B9:D18"), 2, False)
Range("D3") = WorksheetFunction.VLookup(Range("B3"), Range("B9:D18"), 3, False)
End Subこのコードは先ほどの表の関数と同じ結果が返ってきます。ただ、注意点としてはVLookupメソッドの第一引数と第二引数には座標文字列ではなくRangeオブジェクトを入れることです。
WorksheetFunctionの繰り返し処理
WorksheetFunctionの使い方はわかりましたが、実務では処理が単一のセルのみで終わることはほとんどありません。処理が複数行にわたる場合などはFor文などを使って繰り返し処理を行います。
Sub for文()
Dim i As Integer
For i = 3 To 20 '販売実績の3行目から20行目までを繰り返す
Cells(i, 4) = WorksheetFunction.VLookup(Cells(i, 2), Range("F3:H12"), 3, False)
Next i
End Sub
ただし、WorksheetFunctionは、検索するキーワードにあたるセルが空白であったり、参照する表の範囲内にキーワードがなかったりするとエラーになってしまう為、あらかじめエラーを回避する為の処理を書いておく必要があります。
エラー回避処理
Sub for文エラー処理()
Dim i As Integer
On Error Resume Next 'エラーになっても処理を継続する
For i = 3 To 20
Cells(i, 4) = WorksheetFunction.VLookup(Cells(i, 2), Range("F3:H12"), 3, False)
Next i
'エラー情報をクリアして、’On Error Resume Next’の設定を解除する
On Error GoTo 0
End Subこのコードはエラーになった場合にただ処理をスキップしていくだけですが、エラーになったセルに処理を加えたい場合には次のように書くこともできます。
Sub for文エラー処理()
Dim i As Integer
For i = 3 To 20
On Error Resume Next
Cells(i, 4) = WorksheetFunction.VLookup(Cells(i, 2), Range("F3:H12"), 3, False)
'エラーだった時はセルを空白にする。
If Err.Number <> 0 Then 'Err.Numberが0以外の時。つまりエラーだった時。
Cells(i, 4) = ""
On Error GoTo 0 'エラー情報をクリアにする。
End If
Next i
End Sub次はセルに直接記述する方法を書いてみます。
👉 【最大10,000円割引！】 XServer レンタルサーバー招待リンク
セルに直接記述する方法
WorksheetFunctionのところで書いた一番始めのエクセルシートと同じ結果が返ってくるコードです。
Sub 直接記述()
Range("C3") = "=VLOOKUP(B3,B9:D18,2,FALSE)"
Range("D3") = "=VLOOKUP(B3,B9:D18,3,FALSE)"
'処理の結果を値に変換します。変換しないと計算式がセルに残ります。
Range("C3").Value = Range("C3").Value
Range("D3").Value = Range("D3").Value
End Subその名の通りセルに直接関数を入れていくようなニュアンスでわかりやすいですが、こんどはエクセルの入力行が3万行あるとして、直接記述のVLOOKUＰ関数の書き方と処理にかかる時間を計測します。同時にエラー処理も設定します。
Sub 連続直接記述()
Dim startTime As Double
Dim stopTime As Double
startTime = Timer '処理前
ダブルクォート４つ
Range("D3:D30003") = "(VLOOKUP(,$F$3:$H$12,3,FALSE),)"
エラー処理 直接記述する最初のセル
Range("D3:D30003").Value = Range("D3:D30003").Value
stopTime = Timer '処理後
MsgBox stopTime - startTime & "秒" 'メッセージボックスに表示
End Subエラーになったセルに文字などを入れたい場合には、ダブルクォートのところに “”エラーです”” のように真ん中に文字などを挿入して下さい。ちなみに時間を計測した結果は次のようになりました。
WorksheetFunctionで同じ条件で処理にかかる時間を計ったところ、およそ34秒ほどかかったのでセルに直接記述する書き方の方が高速であることがわかりました。
別のExcelファイルのデータを参照する
次は別のExcelファイルにあるデータを参照してVBAでVLOOKUP関数を使用する場合の書き方を確認します。このやり方はExcelファイルを閉じたまま参照することができます。
下記のフォルダ内にふたつのファイルがあります。
sample.xlsx
参照するデータを記述しているExcelファイルです。
vlookup.xlsm
こちらのExcelファイルから sample.xlsx のデータを参照します。
検索キーワードの商品idから商品名と商品単価を取得します。
Sub vlookup()
' vlookup.xlsmファイルの Sheet1
With ThisWorkbook.Worksheets("Sheet1")
.Range("C3") = "=VLOOKUP(B3,'C:\Users\Excel-vlookup\[sample.xlsx]Sheet1'!$A$3:$C$12,2,FALSE)"
.Range("D3") = "=VLOOKUP(B3,'C:\Users\Excel-vlookup\[sample.xlsx]Sheet1'!$A$3:$C$12,3,FALSE)"
.Range("C3").Value = .Range("C3").Value
.Range("D3").Value = .Range("D3").Value
End With
End Sub
ちなみにこちらのやり方は、もちろんVBAを使わずにセルに直接計算式を記述しても同様の結果が得られます。
また、こちらの例題ではファイルのあるパスを省略して書いていますが、実際のコードは絶対パス（フルパス）で正確に記述する必要があります。
おわりに
VLOOKUP関数をExcelVBAで書くとエクセルシートのセルに何万行も計算式を入れる場合などはエクセルファイルの容量が大幅に少なくて済み、再計算もなく任意のタイミングでの実行になるのでエクセル自体の動作が軽くなるというメリットがあります。
まだまだ活用方法はたくさんありますが今回はここまでで。
最後までご覧頂きありがとうございました。
自分のサーバーを持ってみたいと思いませんか？
👇 本サイトからの申し込みならエックスサーバー【最大10,000円割引】！
キャンペーンと併用してさらにお得！
