VBAでMATCH関数の使い方と、検索結果から位置を取得する方法をご説明します。
また、近似検索を行う場合は並び替えが必要となりますので、並び替えを行って近似検索するコードも紹介します。
MATCH関数を使用する事で、指定した範囲の検索結果の相対的な位置を簡単に取得出来ます。
よくVLOOKUP関数の高速版としてINDEX関数と併用されることが多いですが、MATCH関数は高速で検索結果の位置を取得出来ます。
ExcelVBAの利点はワークシートという高機能なアプリケーションを、最初から使える事です。
そして、最大の利点はExcelに用意されている関数を使える事です。
そのMATCH関数をVBAで使う方法についてご説明したいと思います。
1.MATCH関数とは
MATCH関数は指定した範囲の中から、指定した値を検索した結果の相対的な位置を返します。
検索方法を指定する事で、「一致」「最小」「最大」を検索することが可能です。
また、一致以外の値を取得するためには、昇順、降順の並び替えが必要となります。
並び替え方法については下記記事をご覧ください。
MATCH関数の欠点
上記でも記載していますが、検索方法を指定する事で、「一致」「最小」「最大」を検索することが可能ですが、最大限に活用するためには並び替えの知識が求められます。
予め並び替えを行っているデータを使用してVBAでMATCH関数を使用しても良いかと思いますが、その部分が手動となってしまうため、ぜひ並び替え方法についても覚えてみてください。
また、VBAで使用する場合に検索した値が見つからなかった場合にエラーとなり、実行が停止してしまいます。
そのため、エラーを回避するコードを組み込む必要があります。
2.MATCH関数の使い方
MATCH関数は次のように記述します。
MATCH(検索値, 検索範囲, 検索方法)
検索値には検索したい値やセルを指定します。
検索範囲には検索値を検索したいセル範囲を指定します。
検索方法は完全一致で検索するか、最大値を検索するか、最小値を検索するか指定します。
検索方法の説明
| 検索方法 | 値 | 説明 |
| 完全一致 | 0 | 検索値に一致する値を検索します。 この場合はデータを並び替えする必要はありません。 |
| 最大値 | 1 | 検索値以下の最大値を検索します。 この場合はデータを昇順に並び替えておく必要があります。 |
| 最小値 | -1 | 検索値以上の最小値を検索します。 この場合はデータを降順に並び替えしておく必要があります。 |
※注意点として、1の時は検索値以下、-1の時は検索値以上となっています。
2.サンプルコード
基本的なコードから、並び替えをして検索するコード、エラー処理を組み込んだコードなどいくつか記載したいと思います。
データは次のようなA列に値のあるシンプルなデータを使用します。
基本的なコード
上記データから商品コードが「D1004」を検索して、完全一致の検索結果を取得します。
Sub Sample1()
MsgBox Application.WorksheetFunction.Match("D1004", Range(Cells(2, 1), Cells(8, 1)), 0)
End Sub
並び替えをして最小値を取得するコード
A列を降順に並び替えして、検索値「10039」を検索して検索値以上の最小値を取得します。
並び替え部分が追加となるため、コードが長くなります。
Sub Sample2()
With ActiveSheet
.Sort.SortFields.Clear '並び替え条件クリア
'昇順：xlAscending、降順：xlDescending
.Sort.SortFields.Add _
Key:=ActiveSheet.Cells(1, 1), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
With .Sort
.SetRange Range(Cells(1, 1), Cells(8, 1)) '並び替え範囲を指定
.Header = xlYes '項目ありを指定
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
MsgBox Application.WorksheetFunction.Match("D10039", Range(Cells(2, 1), Cells(8, 1)), -1)
End Sub
3.エラー処理のコード
VBAでMATCH関数を使用する場合は、検索値が見つからなかった場合はエラーとなるためエラー処理を組み込む必要があります。
エラー処理については「「On Error」でエラー処理」をご覧ください。
On Error Resume Nextのサンプルコード
「On Error Resume Next」はエラーをすべて無視します。
この方法が一番手っ取り早いです。
また、「On Error Resume Next」は解除しないとエラーを無視し続けるため、解除する必要があり、解除するところで「On Error GoTo 0」と記述します。
下記コードは完全一致で検索していて、「D10039」が存在しないため、エラーとなりますが無視されるためメッセージボックスが表示されません。
Sub Sample3()
On Error Resume Next
MsgBox Application.WorksheetFunction.Match("D10039", Range(Cells(2, 1), Cells(8, 1)), 0)
On Error GoTo 0 'エラー無視解除
End SubOn Error GoTo ラベル名のサンプルコード
「On Error GoTo ラベル名」はエラーが発生したら、ラベル名の位置までスキップするコードです。
スキップ先は「ラベル名：」と記述します。
1点注意があり、エラーが起きなかった場合でもラベル名以降に処理が実行されるため、ラベル名前に「Exit Sub」で離脱する必要があります。
以下コードは検索値「D10039」が存在しないので、「ErrLabel」のラベルにスキップします。
Sub Sample4()
On Error GoTo ErrLabel 'エラー発生時のラベル名指定
MsgBox Application.WorksheetFunction.Match("D10040", Range(Cells(2, 1), Cells(8, 1)), 0)
Exit Sub 'エラーが発生しなかった場合、ここで離脱
ErrLabel: 'エラー発生時にここまでスキップ
MsgBox "検索値が見つかりませんでした。"
End Sub
