自分流のVLOOKUP関数を作る
VLOOKUP関数は、VBAを使用しない人も良く使う関数です。
他の関数と違って検索したい値に対応した値を拾ってきてくれるのでとても便利です。
わざわざ再開発するからには、便利で使い勝手の良いユーザー定義関数に仕上げたいところです。
具体的には、データが空白の状態でもエラー表示にならず、引数も検索値と抽出する列番号のみにして、データ範囲に関しては、ユーザー定義関数内に埋め込むようにしてみました。
第4引数の「検索の型」については完全一致のみとします。
このような変更で、VLOOKUP関数の汎用性は失われますが、ユーザー固有の仕様にすることで使い勝手を向上させてみようと思います。
VLOOKUP関数の復習
まずはVLOOKUP関数をおさらいしましょう。
4つの引数を指定します。
=VLOOKUP ( 検索値, 検索範囲, 列番号, [検索方法] )
検索値： 検索する値を指定します。
検索範囲： 検索対象となる範囲を指定します。
列番号： 検索にヒットした時、抽出する列を指定します。
[検索方法]： 完全一致（FALSE）か近似一致（TRUE）を指定します。
検索方法は省略できますが、省略すると近似一致（TRUE）となるため実際にはFALSEを指定して完全一致とする場合が殆どです。
第３引数の「列番号」は、シート上の列ではなく、検索範囲での列になりますので注意してください。
上に示したのが実際の使用例です。上の例では同じシートに検索範囲を指定していますが、別のシートを指定してもOKです。
例では、If文を使用して、値が無い場合は空白となるようにしています。
商品IDが見つからない場合はエラーとなります。
これを防止するために、商品IDの列（F列）は入力規則のリストを設定して、左側のリストから選択できるようにしています。
独自のVLOOKUP関数を作る
VLOOKUP関数は、機能性と汎用性を兼ね備えているため引数も４つあります。また、IF文を使わないとエラー表示となってしまうため、この辺を簡単に書けるようにしてみましょう。
オリジナルのVLOOKUP関数は汎用性を欠きますが、その代わり自分の環境に合わせてプログラムを書く事ができるようになります。
プログラムはユーザ定義関数として、標準モジュールに書いていきます。
Function myVlookup(検索値 As String, 列番号 As Long) As Variant ’①
Dim i As Long
Dim v As Variant
v = ""
With Worksheets("商品リスト") ’②
For i = 3 To .Cells(Rows.Count, 1).End(xlUp).Row ’③
If .Cells(i, "A") = 検索値 Then
v = .Cells(i, "A").Offset(0, 列番号 - 1) '④
Exit For
End If
Next i
End With
myVlookup = v '⑤
End Function 引数は分かり易いように日本語にしてみました。
第一引数はVLOOKUP関数と同じ検索値を指定します。
第二引数は、列番号でヒットした時の検索範囲の列番号を指定します。
検索範囲は、関数の中に埋め込んでいるので汎用性は欠く事になります。
ただ、別のプログラムで検索範囲が変更になっても、セルの数式を変更する必要がなくなります。
また、ヒットしなかった場合は、空白を返すのでIF文も不要です。
プログラムを解説します。
まず、返り値はVariant型にしています。①
これは、検索範囲のセル値を受け取って返すからです。
②では検索範囲のシートとして「商品リスト」シートを指定しています。
A列の３行目から最後までが検索対象となります。③
IF文で検索値と同じ値を見つけると、
.Cells(i, “A”).Offset(0, 列番号 – 1)
として、A列から相対的な列の位置で値を返します。
列番号 – 1 としているのは、VLOOKUP関数と同じような指定となるようにするためです。
最後に⑤で値を返しますが、検索値がヒットしなかった場合は、初期値で指定した空白が返る事になります。
まとめ
VLOOKUP関数をそのままユーザー定義関数として書くのは、プログラムの勉強になるかもしれませんがあまり意味がないでしょう。
VBAでプログラミングをするのであれば、自分の環境に合ったきめ細やかな機能を持たせる事こそ、その醍醐味でしょう。
そのことは機能追加だけでなく、面倒な部分を省略して使いやすくするという点でも活かせると思います。
コメント