VBAでワークシート関数のVLOOKUP関数の使い方についてご説明します。 ワークシート関数の「VLOOKUP」を使用する事で、簡単に検索範囲から検索値に対応する値を取得する事ができます。 Excelで非常に利用頻度の高い馴染みのある関数かと思います。 ExcelVBAの利点はワークシートという高機能なアプリケーションを、最初から使える事です。 そして、最大の利点はExcelに用意されている関数を使える事です。 その関数の一つ、「VLOOKUP関数」の使い方についてご説明します。
1.VLOOKUP関数とはVLOOKUP関数は検索条件に一致するデータを指定した範囲の中から検索して、対応したデータの指定した列のデータを返す関数です。 膨大なデータから対応するデータを目視で探すのは非常に大変な作業ですし、検索したい値が多くなるとさらに大変です。 それらを簡単に自動的に行ってくれる非常に便利な関数です。 VLOOKUP関数の欠点非常に便利な関数ですが、1点注意する事があります。 もし対応するデータが複数あった場合・・・。 一番上の対応するデータを取得してしまうため、それ以下にあるデータを抽出する事ができません。 もし全ての値を抽出する場合はそれなりに工夫が必要になります。 図にすると次のように感じです。 また、検索するデータ量が多くなるとExcel2013以前のバージョンでは処理時間が非常に長くなります。 もしすでに「VLOOKUP関数」をVBAで使いこなしていて、処理時間にお悩みの方は「VLOOKUPの高速化」をご覧ください。 2.VLOOKUP関数の使い方VLOOKUP関数は次のように記述します。 VLOOKUP(検索値, 範囲, 列番号, 検索方法) 「検索値」を「範囲」の 1 列目(一番左の列)のデータと比較し、「検索方法」で検索が一致した一番最初の行の「列番号」で指定した値を取得します。 VBAで利用するには次のように記述します。 Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法) 引数一覧
VBAでVLOOKUP関数を使う注意点基本的にExcelと使い方は同じなのですが、検索値が見つからなかった場合のエラーが発生した時の挙動が異なります。 Excelではセルに「#N/A」と表示されるだけですが、VBAでは処理を組み込まない場合は停止してしまいます。 見つからない場合は意外とよく起こりうることなので、しっかりとエラー処理を組み込む必要があります。 エラー処理の詳細については「「On Error」でエラー処理」をご覧ください。 3.サンプルコードそのまますべて記述する基本的な方法から、検索値や範囲を変数に入れて処理する方法、エラー処理の方法などいくつかのケースのサンプルコードです。 次のようなデータを使用して、C列にB列とE列が一致するF列の価格します。 基本的なサンプルコードまずは「B4」のみ検索してC4に出力するコードです。
コードの説明『Cells(4, 3)= Application.WorksheetFunction.VLookup』の「Cells(4, 3)」はC4を指しており、検索結果を出力します。 「Application.WorksheetFunction.VLookup」でVBAでも「VLOOKUP関数」を使用できるようにしています。 『(Cells(4, 2), Range(Cells(4, 5), Cells(23, 6)), 2, False)』はそれぞれ引数を指定します。 「(Cells(4, 2)」はB4、「Range(Cells(4, 5), Cells(23, 6))」はE4~F23を指定しています。 「2」はE列が1列目、F列が2列目ということになりますので2を指定します。 最後の「False」は完全一致を指定します。 変数を使ったサンプルコード次は変数を使用したサンプルコードです。 変数に入れる事でコードがシンプルになりますし、動的にも対応させやすいです。
コードの説明変数は「SearchWord」は検索値で、「SearchRange」を検索範囲としています。 検索値は文字列でよいのでStringと宣言していて、検索範囲はセル範囲なのでRangeで宣言しています。 『(SearchWord, SearchRange, 2, False)』が先ほどのSample1コードの引数の指定部分になります。 長々と書かれていたコードがシンプルになりました。 ループするサンプルコードせっかく複数のデータサンプルを使用するので、ループさせたサンプルも記載します。 ループには「For~Next」を使用します。 ループについての詳細は「ループの使い方」をご覧ください。 ついでに行数が変動しても対応できるように、最終行も変数に格納してみます。
すべての値が入りましたが、コードが一気に複雑になりました。 コードの説明変数がさらに追加されて全部で5個になりました。 『WordMaxRow = Cells(Rows.Count, 2).End(xlUp).Row』でB列の最終行を取得しています。 『RangeMaxRow = Cells(Rows.Count, 5).End(xlUp).Row』で今度はE列の最終行を取得してします。 「Set SearchRange = Range(Cells(4, 5), Cells(RangeMaxRow, 6)) 」は最終行までの範囲を指定しています。 この変数を使用する事でループする回数と、検索範囲が動的でも常に最終行まで処理されます。 『For i = 4 To WordMaxRow』~『Next i』で検索したい行数分ループします。 ループの中に『SearchWord = Cells(i, 2)』とループするたびに検索値に格納するセルを変更しています。 『Cells(i, 3) = Application.WorksheetFunction.VLookup(SearchWord, SearchRange, 2, False)』も「Cells(i, 3) 」をループに合わせて出力セルを変更しています。 エラー処理のサンプルコードさて、上記の3つのコードで基本的なVLOOKUPは対応できると思いますが、最後にエラー処理のサンプルコードです。 VBAでVLOOKUP関数を使用する場合は、検索値が見つからない場合はエラーで停止してしまいますので、エラー処理の組み込みが必要になります。 On Error Resume Nextのサンプルコード「On Error Resume Next」はエラーをすべて無視します。 この方法が一番手っ取り早いです。 また、「On Error Resume
Next」は解除しないとエラーを無視し続けるため、解除する必要があり、解除するところで「
On Error GoTo ラベル名のサンプルコード「On Error GoTo ラベル名」はエラーが発生したら、ラベル名の位置までスキップするコードです。 スキップ先は「ラベル名:」と記述します。 1点注意があり、エラーが起きなかった場合でもラベル名以降に処理が実行されるため、ラベル名前に「Exit Sub」で離脱する必要があります。 |