このページではある値の検索を行う Match、VLookup 関数について説明します。ともに指定されたセル範囲から任意の値を探すを関数ですが、マッチ時に返却されるものが異なります。
- Match 関数 ⇒ マッチしたセルの相対行を返却
- VLookup 関数 ⇒ マッチしたセルと同じ行で異なる列の値を返却
主な使用用途としては、Match 関数はある値が存在している位置の取得や、値が存在しているかどうかの存在チェック、Vlookup 関数はキーに対応する値を取得する連想配列としての使い方が挙げられます。これらは様々な場面で要求される処理ですので、ぜひ使い方をマスターしておきたいところです。
使用時の注意点
検索関数を使用する際、注意すべき点がいくつかあります。
- 検索時に大文字、小文字は区別されません。”abc”と”ABC”は同じものとみなされ、マッチしてしまいます。
- 検索タイプを”近似値検索”にした場合、検査範囲を昇順または降順でソートしておく必要があります。
- 検索タイプを”完全一致”にした場合、検索する値が存在しないと以下のようなエラーが発生し、処理が停止してしまいます。
1004 WorksheetFunction クラスの ＊＊＊ プロパティを取得できません。
（＊＊＊には関数名が入ります。）
この状況を回避する方法として、 On Errorステートメントを使用する方法があります。これは、検索関数実行後にエラーコードをチェックし、上記”1004″のエラーが発生していた場合は値が見つからなかったとみなして、処理を継続する方法です。
（詳細は後述の使用例1、使用例2を参照してください。）
上記の点に注意しながら、うまく使用してください。
Match関数
Match関数は、検査範囲内で見つかった検査値の相対位置を返します。第3引数により、検索タイプ（”完全一致検索”、”近似値検索”）を選択することができます。
WorksheetFunction.Match(検査値 , 検査範囲 [, 照合の型]) 【戻り値】 倍精度浮動小数点型 (Double)
| 引数名 | 省略 | 説明 |
|---|---|---|
| 検査値 | × | 検索する値を指定します。 |
| 検査範囲 | × | 検索対象のセル範囲（レンジオブジェクト）または配列を指定します。 |
| 照合の型 | ○ | -1：近似値（検査値以上の最小値）を検索。検査範囲を降順にソートしておく必要あり。 0：完全に一致する値のみを検索。検査範囲をソートしておく必要なし。 1：近似値（検査値以下の最大値）を検索。検査範囲を昇順にソートしておく必要あり。 （省略時は 1 ） |
Match関数の使用例（完全一致検索）
以下のマクロは、インプットボックスより入力された店舗番号について、売上表の店舗番号列に存在するかどうか Match 関数で完全一致検索を行う例です。店舗番号が存在する場合はその相対位置と行を表示します。
Sub sample_wf016_01()
Dim mise As String
Dim sRng As Range
Dim str As String
Dim i As Long
Dim r As Long
'検査値の入力受付
mise = ""
Do Until mise <> ""
mise = InputBox("店舗番号を入力してください。" & vbLf & _
"※大文字と小文字は区別されません。")
'キャンセルのチェック
If StrPtr(mise) = 0 Then Exit Sub
Loop
'検査範囲
Set sRng = Range("A4:A13")
'エラーが発生しても処理を続行する
On Error Resume Next
'完全一致検索
i = WorksheetFunction.Match(mise, sRng, 0)
'エラー発生時の処置
If Err.Number = 1004 Then
'一致する値がない場合
i = 0
Err.Clear 'エラーをクリアして処理続行
ElseIf Err.Number > 0 Then
'上記以外のエラーが発生した場合
MsgBox "エラー発生" & vbLf & _
Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End If
str = "店舗番号『" & mise & "』"
If i = 0 Then
MsgBox str & "は登録されていません。", vbExclamation
Else
r = sRng.Row + i - 1 '相対位置から行を計算
MsgBox str & i & "は番目(" & r & "行目)に登録されています。", _
vbInformation
End If
End Sub
Setステートメント
Do…Loop
On Errorステートメント
InputBox関数
MsgBox関数
下図はテスト用の売上表です。
存在する店舗番号を入力すると、その場所がメッセージボックスで表示されます。
存在しない店舗番号を入力すると、その旨がメッセージで表示されます。
VLookup関数
VLookup 関数は、検査範囲内で見つかった検査値と同じ行にある値を返します。検査範囲内の左端列が検索の対象となり、返却する値の列はこの左端列からの相対位置で指定します。第3引数により、検索タイプ（”完全一致検索“、”近似値検索“）を選択することができます。
第3引数を省略すると”近似値検索”になります。”完全一致検索”を行う場合は第3引数を設定し忘れないように、十分注意してください。
WorksheetFunction.VLookup(検査値 , 検査範囲 , 列番号 [, 検査の型]) 【戻り値】 バリアント型 (Variant)
| 引数名 | 省略 | 説明 |
|---|---|---|
| 検査値 | × | 検索する値を指定します。 |
| 検査範囲 | × | 検索対象のセル範囲（レンジオブジェクト）を指定します。値を取得するための列が必要になるので、最低2列以上の範囲を指定する必要があります。 |
| 列番号 | × | 検査値が見つかった場合に値を取得するめの列番号を指定します。列番号は検査範囲内の左端列を1とした相対値で指定します。 |
| 検査の型 | ○ | True：近似値（検査値以下の最大値）を検索。検査範囲を昇順にソートしておく必要あり。 False：完全に一致する値のみを検索。検査範囲をソートしておく必要なし。 （省略時は True ） |
VLookup関数の使用例（完全一致検索）
以下のマクロは、インプットボックスより入力された店舗番号の3月分売上を VLookup 関数で取得して表示する例です。
Sub sample_wf016_02()
Dim mise As String
Dim sRng As Range
Dim str As String
Dim uri As Long
'検査値の入力受付
mise = ""
Do Until mise <> ""
mise = InputBox("店舗番号を入力してください。" & vbLf & _
"※大文字と小文字は区別されません。")
'キャンセルのチェック
If StrPtr(mise) = 0 Then Exit Sub
Loop
'検査範囲
Set sRng = Range("A4:D13")
'エラーが発生しても処理を続行する
On Error Resume Next
'完全一致検索
uri = WorksheetFunction.VLookup(mise, sRng, 4, False)
'エラー発生時の処置
If Err.Number = 1004 Then
'一致する値がない場合
uri = 0
Err.Clear 'エラーをクリアして処理続行
ElseIf Err.Number > 0 Then
'上記以外のエラーが発生した場合
MsgBox "エラー発生" & vbLf & _
Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End If
str = "店舗番号『" & mise & "』"
If uri = 0 Then
MsgBox str & "は登録されていません。", vbExclamation
Else
MsgBox str & "の3月分の売上は" & uri & "万円です。", _
vbInformation
End If
End Sub
Setステートメント
Do…Loop
On Errorステートメント
InputBox関数
MsgBox関数
前の sample_wf016_02 で使用した売上表と同じものです。
存在する店舗番号を入力すると、その店舗の売上が表示されます。
存在しない店舗番号を入力すると、その旨がメッセージで表示されます。
VLookup関数の使用例（近似値検索）
以下のマクロは、商品リストからある日付時点における商品価格を VLookup 関数により取得する例です。
Sub sample_wf016_03()
Dim sCode As String
Dim sDate As String
Dim sKey As String
Dim var As Variant
Dim sRng As Range
Dim str As String
Dim code As String
Dim price As Long
'検査値の入力受付
sCode = ""
sDate = ""
Do Until sCode <> "" And IsDate(sDate)
sCode = InputBox("商品コードと日付を" & _
"カンマ区切りで入力してください。" & _
vbLf & _
"※大文字と小文字は区別されません。")
'キャンセルのチェック
If StrPtr(sCode) = 0 Then Exit Sub
'入力値をカンマで分割
var = Split(sCode, ",")
If UBound(var) = 1 Then
sCode = Trim(var(0))
sDate = Trim(var(1))
End If
Loop
'検索用キー
sKey = sCode & "-" & Format(CDate(sDate), "yyyymmdd")
'検査範囲
Set sRng = Range("A3:D8")
'エラーが発生しても処理を続行する
On Error Resume Next
'近似値検索では入力値と異なる商品コードの行と取得してしまう
'可能性があるため、まずは商品コードをチェック
code = WorksheetFunction.VLookup(sKey, sRng, 2, True)
If StrComp(code, sCode, vbTextCompare) = 0 Then
'商品コードがマッチしていたら近似値検索で価格を取得
price = WorksheetFunction.VLookup(sKey, sRng, 4, True)
Else
'商品コードまたは適用開始日が登録されていない
price = 0
End If
'エラー発生時の処置
If Err.Number = 1004 Then
'一致する値がない場合
price = 0
Err.Clear 'エラーをクリアして処理続行
ElseIf Err.Number > 0 Then
'上記以外のエラーが発生した場合
MsgBox "エラー発生" & vbLf & _
Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End If
If price = 0 Then
MsgBox "『" & sCode & "』の" & sDate & _
"時点における価格は登録されていません。", _
vbExclamation
Else
MsgBox "『" & sCode & "』の" & sDate & _
"時点における価格は" & price & "円です。", _
vbInformation
End If
End Sub
Setステートメント
Do…Loop
On Errorステートメント
InputBox関数
MsgBox関数
Split関数
LBound、UBound関数
＜テストデータ＞
商品リストには商品コード（B列）とその価格（D列）、そして価格の適用開始日（C列）があります。同一商品コードに複数の適用開始日がある場合、価格は同一行の適用開始日から次行の適用開始日前日まで有効とします。
VLookup 関数による”近似値検索”を行うため、商品コードと価格の適用開始日を数式で結合したもの（日付はフォーマット変換しています）を検索キー（A列）とし、商品リストはこのキーの昇順に並べられています。
商品コード『S010』、日付『2015/1/1』を入力した場合、完全一致した行の価格が表示されます。
商品コード『S010』、日付『2014/12/31』を入力した場合、検索値以下の最大値『2014/6/10』に対応する価格『180』円が表示されます。
商品コード『S001』、適用開始前の日付『2000/9/30』を入力した場合、価格が未登録である旨のメッセージが表示されます。
存在しない商品コード『S100』を入力した場合は、その旨がメッセージで表示されます。
