VBAでワークシート関数のHLOOKUP関数の使い方についてご説明します。
ワークシート関数の「HLOOKUP」を使用する事で、簡単に検索範囲から検索値に対応する値を取得する事ができます。
「VLOOKUP関数」に非常によく似ている名前ですが、「VLOOKUP関数」は垂直方向(行)で検索するのに対して、「HLOOKUP関数」は水平方向(列)に対して検索をする関数です。
ExcelVBAの利点はワークシートという高機能なアプリケーションを、最初から使える事です。
そして、最大の利点はExcelに用意されている関数を使える事です。
その関数の一つ、「HLOOKUP関数」の使い方についてご説明します。
1.HLOOKUP関数とは
HLOOKUP関数は検索条件に一致するデータを指定した範囲の中から検索して、対応したデータの指定した行のデータを返す関数です。
VLOOKUP関数を列行を入れ替えた関数と説明するとわかりやすかもしれません。
膨大なデータから対応するデータを目視で探すのは非常に大変な作業ですし、検索したい値が多くなるとさらに大変です。
それらを簡単に自動的に行ってくれる非常に便利な関数です。
HLOOKUP関数の欠点
非常に便利な関数ですが、1点注意する事があります。
もし対応するデータが複数あった場合・・・。
一番左から検索して、最初に検索した列の対応するデータを取得してしまうため、それより右にあるデータを抽出する事ができません。
もし全ての値を抽出する場合はそれなりに工夫が必要になります。
図にすると次のように感じです。
若干不自然なデータですが、「2019/9/1～2019/9/5」のあとにも同じ日付があった場合に、後ろの「2019/9/2」の売上は抽出できません。
行と比べると列数自体が少ないので、そこまで膨大な列のデータを扱うこと自体が稀ではあるかと思いますが、検索するデータ量が多くなると処理時間が非常に長くなります。
2.HLOOKUP関数の使い方
HLOOKUP関数は次のように記述します。
HLOOKUP(検索値, 範囲, 行番号, 検索方法)
「検索値」を「範囲」の 1行目(一番上の行)のデータと比較し、「検索方法」で指定した検索方法(完全一致/部分一致)で、検索が一致した一番最初の列の「行番号」で指定した値を取得します。
VBAで利用するには次のように記述します。
Application.WorksheetFunction.HLookup(検索値, 範囲, 行番号, 検索方法)
引数一覧
| 引数 | 省略 | 説明 |
| 検索値 | 必須 | 検索する値を、数値、文字列、セル参照、関数などで指定します。 引数「検索方法」を FALSEに指定した場合は、ワイルドカードを使用できます。 |
| 範囲 | 必須 | 検索対象となるデータと取得するデータが含まれたセルの範囲を指定します。 この範囲の一番上の行を検索します。 |
| 行番号 | 必須 | 取得する行番号を指定します。 引数「範囲」の1番上の行が1行目になります。 上から 2 番目の行が2行目になります。 |
| 検索の型 | 省略可能 | TRUEもしくは「1」または省略した場合は、検索する値に1番近い小さい値を取得します。 FALSEもしくは「0」を指定した場合は、検索する値に完全一致する値を取得します。 |
VBAでHLOOKUP関数を使う注意点
基本的にExcelと使い方は同じなのですが、検索値が見つからなかった場合のエラーが発生した時の挙動が異なります。
Excelではセルに「#N/A」と表示されるだけですが、VBAでは処理を組み込まない場合は停止してしまいます。
見つからない場合は意外とよく起こりうることなので、しっかりとエラー処理を組み込む必要があります。
エラー処理の詳細については「「On Error」でエラー処理」をご覧ください。
3.サンプルコード
そのまますべて記述する基本的な方法から、検索値や範囲を変数に入れて処理する方法、エラー処理の方法などいくつかのケースのサンプルコードです。
次のようなデータを使用して、2行目を検索値として6行目で一致する7行目を3行目に価格を抽出します。
基本的なサンプルコード
まずは「C2」のみ検索してC3に出力するコードです。
Sub Sample1()
Cells(3, 3) = Application.WorksheetFunction.HLookup _
(Cells(2, 3), Range(Cells(6, 3), Cells(7, 13)), 2, False)
End Subコードの説明
『Cells(3, 3) = Application.WorksheetFunction.HLookup』の「Cells(3, 3)」はC3を指しており、検索結果を出力します。
「Application.WorksheetFunction.HLookup」でVBAでも「HLOOKUP関数」を使用できるようにしています。
『(Cells(2, 3), Range(Cells(6, 3), Cells(7, 13)), 2, False)』はそれぞれ引数を指定します。
「(Cells(2, 3)」はC2、「Range(Cells(6, 3), Cells(7, 13))」はC6～M7を指定しています。
「2」は6行目が1行目、7行目が2行目ということになりますので2を指定します。
最後の「False」は完全一致を指定します。
変数を使ったサンプルコード
次は変数を使用したサンプルコードです。
変数に入れる事でコードがシンプルになりますし、動的にも対応させやすいです。
Sub Sample2()
Dim SearchWord As String '検索値
Dim SearchRange As Range '検索範囲
SearchWord = Cells(2, 3)
Set SearchRange = Range(Cells(6, 3), Cells(7, 13))
Cells(3, 3) = Application.WorksheetFunction.HLookup _
(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 WordMaxCol As Long '検索値最終列
Dim RangeMaxCol As Long '検索範囲最終列
Dim i As Long
WordMaxCol = Cells(2, Columns.Count).End(xlToLeft).Column '検索値最終列
RangeMaxCol = Cells(6, Columns.Count).End(xlToLeft).Column '検索範囲最終列
Set SearchRange = Range(Cells(6, 3), Cells(7, RangeMaxCol)) '検索範囲格納
For i = 3 To WordMaxCol '検索値数分ループ
SearchWord = Cells(2, i) '検索値格納
Cells(3, i) = Application.WorksheetFunction.HLookup _
(SearchWord, SearchRange, 2, False) '検索
Next i
End Sub
すべての値が入りましたが、コードが一気に複雑になりました。
コードの説明
変数がさらに追加されて全部で5個になりました。
『WordMaxCol = Cells(2, Columns.Count).End(xlToLeft).Column』で2行目の最終列を取得しています。
『RangeMaxCol = Cells(6, Columns.Count).End(xlToLeft).Column』で今度は6行目の最終列を取得してします。
『Set SearchRange = Range(Cells(6, 3), Cells(7, RangeMaxCol))』は最終列までの範囲を指定しています。
この変数を使用する事でループする回数と、検索範囲が動的でも常に最終行まで処理されます。
『For i = 3 To WordMaxCol』～『Next i』で検索したい列数分ループします。
ループの中に『SearchWord = Cells(2, i)』とループするたびに検索値に格納するセルを変更しています。
『
Cells(3, i) = Application.WorksheetFunction.HLookup (SearchWord, SearchRange, 2, False)』も「Cells(3, i)」をループに合わせて出力セルを変更しています。
エラー処理のサンプルコード
さて、上記の3つのコードで基本的なHLOOKUPは対応できると思いますが、最後にエラー処理のサンプルコードです。
VBAでHLOOKUP関数を使用する場合は、検索値が見つからない場合はエラーで停止してしまいますので、エラー処理の組み込みが必要になります。
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 WordMaxCol As Long '検索値最終列
Dim RangeMaxCol As Long '検索範囲最終列
Dim i As Long
WordMaxCol = Cells(2, Columns.Count).End(xlToLeft).Column '検索値最終列
RangeMaxCol = Cells(6, Columns.Count).End(xlToLeft).Column '検索範囲最終列
Set SearchRange = Range(Cells(6, 3), Cells(7, RangeMaxCol)) '検索範囲格納
On Error Resume Next
For i = 3 To WordMaxCol '検索値数分ループ
SearchWord = Cells(2, i) '検索値格納
Cells(3, i) = Application.WorksheetFunction.HLookup _
(SearchWord, SearchRange, 2, False) '検索
Next i
On Error GoTo 0
End SubOn Error GoTo ラベル名のサンプルコード
「On Error GoTo ラベル名」はエラーが発生したら、ラベル名の位置までスキップするコードです。
スキップ先は「ラベル名：」と記述します。
1点注意があり、エラーが起きなかった場合でもラベル名以降に処理が実行されるため、ラベル名前に「Exit Sub」で離脱する必要があります。
Sub Sample5()
Dim SearchWord As String '検索値
Dim SearchRange As Range '検索範囲
Dim WordMaxCol As Long '検索値最終列
Dim RangeMaxCol As Long '検索範囲最終列
Dim i As Long
WordMaxCol = Cells(2, Columns.Count).End(xlToLeft).Column '検索値最終列
RangeMaxCol = Cells(6, Columns.Count).End(xlToLeft).Column '検索範囲最終列
Set SearchRange = Range(Cells(6, 3), Cells(7, RangeMaxCol)) '検索範囲格納
On Error GoTo ErrLabel 'エラー発生時のラベル名指定
For i = 3 To WordMaxCol '検索値数分ループ
SearchWord = Cells(2, i) '検索値格納
Cells(3, i) = Application.WorksheetFunction.HLookup _
(SearchWord, SearchRange, 2, False) '検索
Next i
Exit Sub 'エラーが発生しなかった場合、ここで離脱
ErrLabel: 'エラー発生時にここまでスキップ
MsgBox i & "列目でエラーが発生しました。"
End Sub
