Excelの表でコードや区分の横に対応する名称を出すときなど、VLOOKUP関数を使います。
しかし、初心者にとっては
そこで、本記事では、
といった基本的な内容から、
という応用的な内容までお伝えします。
VBAのVLOOKUP関数についてわかりやすく解説していますので、ぜひ参考にしてください！
本記事を読み終える頃には、表においてVLOOKUP関数を使えるだけでなく、VBAからも自在に使えるようになっていますよ！
VLOOKUP関数とは
最初に、そもそもVLOOKUP関数とは何なのかを解説します。冒頭で触れたとおり、コードや区分から対応する値を取得するものです。
伝票を例にとると、商品コードから商品名や単価を取得すると考えてください。その具体例は以下です。
以下の例では、商品コードと数量のみ手入力で、商品名と単価はVLOOKUP関数を使用して取得しています。金額は単価と数量の掛け算、合計はSUM関数を使って自動で計算結果を表示しています。
[書式]
=VLOOKUP(検索値, 範囲, 列番号, 検索方法の指定)
検索値は検索したい値、範囲は対応する名称がある範囲、列番号は何列目に欲しい値があるかの指定です。
検索方法は完全一致かあいまい検索を指定しますが、通常は完全一致で使用するため、FALSEを指定すればよいでしょう。
[使用例]
商品名の取得を文章で説明しましょう。
結果、図のオレンジ色の枠にある式ができます。となりのD3セルに単価も取得して設定してみました。オレンジ色と青色の枠内の式を比較すると、動作イメージが明確になるでしょう。
これが、Excelの表におけるVLOOKUP関数です。
VBAでVLookupを使う方法
ExcelVBAにおいて、コードや区分から値を取得する場合は以下のようにします。
[書式]
WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法の指定)
次の例では、B3セルに入力した商品コードに対応する商品名を、表から取得してC3セルに表示しています。
[使用例]
Sub vlookup_sample1()
Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
End Sub
[実行結果]
いかがでしょうか？
キーとなる値からそれに対応する値を取り出す、といった関係データベース的な使い方ができますね。
エラーへの対処法
VBAでVLOOKUP関数を使ったコーディングをするのは、今まで解説してきたことで十分可能です。実は、それよりもエラーとその対処方法のほうが難しいのです。
「WorksheetFunction クラスの VLookup プロパティを取得できません。」このエラーは特によく起こります。
よくある理由を順に解説していきます。
参照と型が異なる場合
検索したい値のあるセルと、範囲で指定するセルの型が異なるときに起こります。
以下の例では、B3セルを文字列にして型を合わせるとエラーは解消します。
参照範囲に値が存在しない
検索範囲に値が存在しないときにエラーが発生します。
VBAの一般的なエラーの対処法を解説します。
次のサンプルコードでは、実行時エラー1004が発生した場合に、C3セルに「値取得不可！」と入れています。
Sub vlookup_sample2()
On Error GoTo err:
Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
Exit Sub
err:
If err.Number = 1004 Then
Range("C3") = "値取得不可！"
End If
End Sub
最初の方にある「On Error Goto err:」は、エラーが発生したら下の方の「err:」というラベルまでジャンプせよという意味です。
ただしエラーは1004以外にもありえるので、1004の場合のみ「値取得不可！」と返すようにしているのが「err:」の後のIf文です。
実行時エラー1004だけでも「範囲に値が存在しない」以外にもさまざまな原因があるので、あえてザックリと「値取得不可！」としています。
VBAからセルに記述してデバッグする方法
以下のコードを実行してみましょう。
Sub vlookup_sample3()
Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 4, False)
End Sub
1004エラーが発生します。(環境によっては、実行時にエラーが出るケースもあります。)
検証方法を解説します。コード中のVLOOKUP関数の部分を、セルに式で記述してみましょう。
商品名は「#REF!」と表示されており、値が取得できていません。これはVLOOKUP関数の第3引数である列番号が4となっているのが原因です。
範囲はE3セルからG8セルまで3列しかないのに、4列目を指定してしまっています。ここでは商品名がほしいので、列番号を2と修正すれば問題は解消できます。
想定する最終形で実際に式として記述してみる、これはとても役に立つデバッグ方法です。そして、このデバッグ方法をもう一歩さらに進めてみましょう。
ここまでは、ExcelシートのセルにVLOOKUP関数を直接入力していましたが、セルに入力せずにVBAからVLOOKUP関数をセルに入力することもできます。
それはRangeオブジェクトのFormulaプロパティを使う方法です。
Rangeオブジェクト.Formula = “=数式”
これで指定したRangeオブジェクトにVBAから関数式を入力することができます。
Sub vlookup_sample4()
Range("C3").Formula = "=VLOOKUP(B3, E3:G8, 4, False)"
End Sub
実行結果：
これでExcelシートとVBAの開発環境(VBE)を行ったり来たりせずに、VBAの開発環境だけでデバッグをすることが可能です。
ぜひ覚えてください。
Formulaプロパティを使ってVBAからセルに直接関数を入力する方法については、こちらで詳しく解説しています。ぜひ参考にしてください。
別シート、別ブックを参照する方法
ここまでは同じシート内の参照範囲を検索してきました。参照範囲が大きくなったり、勝手に変更されたくない場合などは検索結果と参照範囲を別シートや別ブックに分ける場合も考えられます。
そんな場合は、参照範囲の指定を以下のように記述することで対応できます。
Workbooks("ブック名.xlsx").Worksheets(“シート名”).Range((参照範囲)
参照先が同じブック内の場合はWorkbooks(“ブック名.xlsx”).は省略可能です。
これは参照先のブックが開いていることが前提になります。
Sub vlookup_sample5()
With Worksheets("入力")
.Range("C3") = WorksheetFunction.VLookup(.Range("B3"), Worksheets("データ").Range("A3:C8"), 2, False)
End With
End Sub
実行結果：
参照範囲を配列で置き換える
これまでは参照範囲がExcelシート上のあるセル範囲でした。VBAを使っていると、Excelシート上のセル範囲ではなく、配列を参照範囲に指定したい場合も出てきます。
検索する参照範囲を配列にした場合についてみてみましょう。
Sub vlookup_sample6()
Dim arr() As Variant
arr = Range("E3:G8")
'参照範囲を配列に変更
Range("C3") = WorksheetFunction.VLookup(Range("B3"), arr, 2, False)
End Sub
実行結果：
まず、配列arrにセル範囲”E3:G8″の値を格納しています。VLookup関数の参照範囲に配列arrを指定しています。
なお、配列とセル範囲の値を共有する方法については、こちらで詳しく解説していますので、ぜひ参考にしてください。
VLookupの高速化
これまでは、比較的小さいデータをVLookupで参照して検索してきました。
しかし、例えば1万件や10万件を超える大きなデータから検索を数千回繰り返したい場合もあります。そんな場合はVLookup関数の処理速度も考慮しなければ、時間がかかりすぎてしまうことになります。
時間がかかりすぎてしまえば、代わりの手段も考えなければなりません。
ExcelではVLOOKUP関数の代わりに、INDEX関数とMATCH関数を組み合わせて使わうこともよくあります。
INDEX関数は
=INDEX(範囲,縦位置)
“縦位置”に”範囲”内の位置を数値で入力するとその値を返します。配列のインデックス番号を指定したら、要素の値が返ってくるのと使い方が似ていますね。
MATCH関数は
=MATCH(検索値,範囲,一致か近似値か)
”検索値”に値を入力すると”範囲”の中の位置を数値で返します。
MATCH関数で検索値の位置を出力して、その位置を使ってある範囲内の値を抽出します。これでVLOOKUP関数と同じ結果が得られるようになります。
Sub vlookup_sample7()
Range("C3").Formula = "=INDEX(F3:F8, MATCH(B3, E3:E8))"
End Sub
実行結果：
また、VBAでは検索にはFindメソッドをよく使います。
これらで処理速度を比較してみましょう。使うデータは以下のVBAを使って作成しています。
Sub vlookup_sample9()
Const num = 10000
Dim i As Long
For i = 1 To num
Range("B" & i + 2) = "C" & Format(CStr(Round(num * Rnd) + 1), "00000")
Range("E" & i + 2) = "C" & Format(CStr(i), "00000")
Range("F" & i + 2) = "商品" & Format(CStr(i), "00000")
Next i
End Sub
1万個のデータリストから商品名を抽出する作業を1万回繰り返す時間で比較します。
Declare Function GetTickCount Lib "KERNEL32.DLL" () As Long
Sub vlookup_sample8()
Const num = 10000
Dim newTimer As Long
Dim t1 As Long, t2 As Long, t3 As Long
'VLookup関数
newTimer = GetTickCount '計測開始
Dim i As Long
For i = 1 To num
Range("C" & i + 2) = WorksheetFunction.VLookup(Range("B" & i + 2), Range("E3:F" & num + 2), 2, False)
Next i
t1 = GetTickCount - newTimer '処理時間算出
'Index関数 & Match関数
newTimer = GetTickCount '計測開始
For i = 1 To num
Range("C" & i + 2) = WorksheetFunction.Index(Range("F3:F" & num + 2), _
WorksheetFunction.Match(Range("B" & i + 2), Range("E3:E" & num + 2)))
Next i
t2 = GetTickCount - newTimer '処理時間算出
'Findメソッド
newTimer = GetTickCount '計測開始
Dim myObj As Range
For i = 1 To num
Set myObj = Range("E3:E" & num + 2).Find(Range("B" & i + 2))
Range("C" & i + 2) = Range("F" & myObj.Row)
Next i
t3 = GetTickCount - newTimer '処理時間算出
MsgBox "VLookup関数： " & t1 & "ミリ秒" & vbCrLf & _
"Index関数 & Match関数： " & t2 & "ミリ秒" & vbCrLf & _
"Findメソッド： " & t3 & "ミリ秒"
End Sub
実行結果：
実行結果では
という結果になりました。この結果からは、VLookup関数よりも代わりにIndex関数とMatch関数を使った方が処理時間を短縮できます。
数千や数万個のデータリストから数千個のデータを検索して抽出する場合は、Index関数とMatch関数の組み合わせを使用することをオススメします。
ちなみに、Findメソッドは処理時間がかなり遅い結果となりました。Findメソッドの使い方については、こちらのサイトで詳しく解説していますので、ぜひ参考にしてください。
まとめ
本記事では、VLOOKUP関数について解説しました。
セルに式で記述する、VBAでも実装できる、この両方をマスターするといろんなことができるようになります。
ぜひ、頑張ってマスターしてくださいね！
