ワークシート関数VLookupをエクセルVBAで使います。非常にベーシックな検索用途の関数です。
それゆえの利用法と注意点、特に検索対象列の限定制限は覚えておかなければなりません。
こんにちは、じゅんぱ店長(@junpa33)です。
今回も、エクセルVBAで利用できるワークシート関数についてがテーマです。
この記事では、データナンバーなどから、その必要な値や数値を抽出する「Vloolup関数」について使い方を説明します。
「VLookup関数」については他にもその動作がよく似た関数があります。それぞれに固有の特徴がありますが、特に、
関数が返してくる値に注目してください。
- Match
検索対象の値が、指定した範囲内でどの位置にあるかを相対的に位置数値(行または列番号)として返します。
VBAで使うMatch関数 活用度アップでテッパン関数に!- Index
テーブルまたはセル範囲にある値、またはその値のセル参照を返します。
VBA Index関数 の使い方。行と列の指定でセル値の抽出- Vlookup
選択範囲の1列目で指定した値を検索し、その選択範囲内で取出指定した別の列の同じ行にある値を返します。
- Find・FindNext
ワークシートで特定の情報をRangeオブジェクトを対象として検索しセルを表すRangeオブジェクトとして返します。
Findメソッドでの検索の方法とコード組み立てのコツコンテンツ
関数VLookupをVBAで使う。利用法と注意点
VLookup関数は、対象範囲の第1列目を検索範囲として、その合致する値のある行の何れか(指定の)列番号目にあるデータを抽出表示するという特徴の関数になります。
その1列目に検索値が無い場合は、エラーを返します。(VBAのコード進行が止まります。)
VLookup関数の構文は
構文(記述方法)は
WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法)
です。
検索値
指定範囲の第1列にある値で検索します。
検索値には、数値、文字列、理論値や値に対するセル参照を指定します。
もちろん定数だけでなく変数も指定できます。
範囲
これは検索範囲ではなく、抽出したいデータも含めた、「データエリア全体」の範囲になります。
一塊のデータ表であれば、「Current.Regionプロパティ」を使いましょう。
CurrentRegionは連続データをまとめて掴む列番号
抽出したいデータのある列番号を指定します。
検索方法
「True」と「False」のどちらかを指定します。
「検索値と同値」を検索というのが殆どかと思いますので、その場合は「False」を指定することになります。
「True」は近似値の検索になります。
例題の紹介
今回も先回と同じ雑誌リストを使います。
同じサンプルですので、関数による使い方の差もわかるかと思います。
例題テストのコード
今回も、検索条件を入力して結果を見てみます。
Option Explicit
Sub VLookup関数Test1()
Dim sKa As Variant, sKb As Long
Dim MVal As Variant
Dim DataRange As Range
Worksheets("Sheet1").Select
'変数を初期化
sKa = 0
sKb = 0
MVal = ""
'検索範囲をセットする
Set DataRange = Range("B6").CurrentRegion
'検索値の入力
sKa = Range("L1").Value
'戻り値の列の入力
sKb = Range("L2").Value
'エラーストップを無視するコード
On Error Resume Next
'コード項目列で検索する
MVal = Application.WorksheetFunction. _
VLookup(sKa, DataRange, sKb, False)
Range("L3") = MVal
'エラー無視を解除する
On Error GoTo 0
End Sub
実行結果
検索値指定を「58」に、戻り値の列を「6」列目に指定しました。
結果戻り値として、「メンズノンノ」というデータが返されました。
VLookup関数の注意点
VLookup関数の注意点としては、データ選択範囲をどこで設定するかで、
検索対象列が変わってしまうということです。
あくまでも、「検索対象列はデータ選択範囲の一番左端の列」というルールは固定されていますので、
下手にデータ選択範囲を指定してしまうと、思わぬ検索値を指定しなければならなくなります。
それぞれのデータが持つ固有値を検索値にできる位置で検索対象列をセットするようにしましょう。
ワークシート関数VLookupをエクセルVBAで使うまとめ
このVLookup関数は、エクセルを日頃から使っている人には特にポピュラーな関数だと思います。
非常にベーシックである分、使いやすくなっていますが、その分柔軟性に欠けるところもあります。
特に、「検索対象列を変化させれない!」というのは、VBAコード組み立てでは、結構面倒くさいことになる場合もあります。
ですので、
個人的には、検索対象行も変化させれて、戻り値も「行番号」で返してくれる、「Match関数」のイチオシになってしまっています。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です今回の記事はここまでです。 最後までご覧いただき有難うございました。
<記事内容についての告知>
VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。