エクセルVBAでよく使うワークシート関数としてMatch関数があります。
この関数は、検索値に該当する値の行番号や列番号を取り出せます。
非常に便利です。これは活用度アップで是非テッパン関数にしましょう。
エクセルVBAで利用できる関数についてがテーマです。
今回は、データリストから必要な値や数値を取り出してくる「Match関数」について、使い方を説明します。
こんにちは、じゅんぱ店長(@junpa33)です。
エクセルVBAでデータ検索を行いたいことは、かなりの頻度で有ります。
For~Nextで1行ずつ値を参照して目的のデータを取り出す方法( ” 力わざ ” と言います)もありますが、
オシャレにサクッ!と必要データを取り出せれば、人気者にもなります。(誰も見てないか。)
こういった、VBAで検索する方法として有名どころの関数がありますが、これはいずれも、ワークシート上で関数式として使う関数です。
エクセルVBA上では、WorksheetFunctionオブジェクトの中で「メソッド」として使用することが出来るのです。
関数が返してくる値に注目してください。
- Match
検索対象の値が、指定した範囲内でどの位置にあるかを相対的に位置数値(行または列番号)として返します。 - Index
テーブルまたはセル範囲にある値、またはその値のセル参照を返します。
エクセルVBA Index関数 の使い方。行と列の指定で簡単にセル値の抽出
- Vlookup
選択範囲の1列目で指定した値を検索し、その選択範囲内で取出指定した別の列の同じ行にある値を返します。
ワークシート関数VLookupをエクセルVBAで使う。利用法と注意点
- Find・FindNext
ワークシートで特定の情報をRangeオブジェクトを対象として検索しセルを表すRangeオブジェクトとして返します。
Findメソッドでの検索の方法とコード組み立てのコツ
それぞれの関数によって、
導く値やセットするルールも特徴がありますので、その場に合った使い分けが必要になります。
というか、自分の場合は、得意の関数が使える方向にコードを導いていきますが・・・
いつも使いのご贔屓関数を是非1つは作っておいた方が吉かと思います。
コンテンツ
Match関数 活用度アップでテッパン関数にする
Match関数の構文は
構文(記述方法)は
WorksheetFunction.Match( 検索値 , 検索範囲 , 検索方法)
となります。
検索値
検索値には、数値、文字列、理論値や値に対するセル参照を指定します。
もちろん定数だけでなく変数も指定できます。
検索範囲
検索したいセル範囲を、1行または1列の範囲で指定します。
検索方法
検索方法は「完全一致」「検索値以下の最大値」「検索値以上の最大値」を選択することが出来ます。
「完全一致」・・・・・・・値は「0」
「検索値以下の最大値」・・値は「1」
「検索値以上の最大値」・・値は「-1」
例題の提示
雑誌リストの一部分を使って例題にしたいと思います。
青字の入力セルに検索値を入力します。
「MvalR」はデータ表の中での行数
「MvalC」はデータ表の中での列数
「Mval2C」はデータ表の中での列数

例題のコード テスト1
それぞれ行・列2方向でのテストをします。
列を特定(E列)してその中での検索値を調べます。(行番号をMvalRに表示します。)
行を特定(16行目)してその中での検索値を調べます。(列番号をMvalCに表示します。)
今回は、データ表の行・列の大きさは「CurrentRegion」を使って調べます。
「Match」は検索範囲に検索値が得られないとエラーになります。
ですので、コードを進行させるためエラー回避コードの記述は必須になります。(エラーを発生させるコードの前に設置します。)
コード入力1に「29674」、雑誌名入力1に「SENSE」と入力します。
Sub Match関数Test1() Dim i As Long Dim Arow As Long, Acol As Long Dim sKa As Long, sKb As String Dim MValR As Variant, MValC As Variant Worksheets("Sheet1").Select 'データ表の行数を調べます。 Arow = Cells(6, 2).CurrentRegion.Rows.Count 'データ表の列数を調べます。 Acol = Cells(6, 2).CurrentRegion.Columns.Count '検索値の入力 sKa = Range("L1") sKb = Range("L2") 'エラーストップを無視するコード On Error Resume Next 'コード項目列で検索する MValR = WorksheetFunction.Match(sKa, Range(Cells(6, 5), Cells(Arow + 6, 5)), 0) Range("L3") = MValR 'データNO57行で検索する MValC = WorksheetFunction.Match(sKb, Range(Cells(16, 2), Cells(16, Acol + 1)), 0) Range("L4") = MValC 'エラー無視を解除する On Error GoTo 0 End Sub
実行結果
MvalRは「7」行目、MvalCは「6」列目と表示されました。
これはあくまでもデータ表内の位置関係です。
エクセルシート全体から見れば「12行目」と「7列目」の位置関係になります。
この位置関係の差は、コード組立上で反映させていかないと、正確な値が表示されないという事が発生します。
CurrentRegionプロパティについてはこちらを参考にしてください。

データ入力済セルの最終行番号取得についてはこちらを参考にしてください

例題のコード テスト2
テスト1では、選択範囲が1列または1行のみでのコードでした。
これを行列ともに可変にしたらどうなるかのテストをしてみます。
「データ表内のすべてのセル値を検索したら?」
というコードになります。
1行ごとに検索値を調べていきます。それをデータ表の全行について行います。
検索値が見つかった時、Matchの返す値が0より大きくなるので、If条件式によって、その時の「本体価格」と「税込価格」を取り出すようにします。
今回のデータ表には同じ値のセル値が含まれているので、最初に検索値がマッチしたらループを抜け出して終了させます。
検索名入力2に「集英社」と入力します。
MVal2Cはデータ表内の検索値の列番号になります。
Sub Match関数Test2() Dim i As Long, R As Long Dim Arow As Long, Acol As Long Dim sKc As String Dim MVal2C As Variant Worksheets("Sheet1").Select 'データ表の行数を調べます。 Arow = Cells(6, 2).CurrentRegion.Rows.Count 'データ表の列数を調べます。 Acol = Cells(6, 2).CurrentRegion.Columns.Count '検索値の入力 sKc = Range("L29") 'エラーストップを無視するコード On Error Resume Next 'データ表を1行ずつ作業ループする For i = 1 To Arow 'データを行毎に検索する MVal2C = WorksheetFunction.Match(sKc, Range(Cells(i + 5, 2), Cells(i + 5, Acol + 1)), 0) '列数が返されたときに作業するコード If MVal2C > 0 Then Range("L30") = MVal2C R = i + 5 Range("L31") = Cells(R, Acol).Value Range("L32") = Cells(R, Acol + 1).Value '最初に検索値を見つけたあとループを抜ける Exit For End If Next i 'エラー無視を解除する On Error GoTo 0 End Sub
実行結果
結果はこのようになりました。
Match関数のこの使い方は、コードが少々面倒くさそうな組み方になり、あまりお勧めではない、お遊び的なコードになりました。
エクセルVBAで使うMatch関数 まとめ
Match関数は、コードの書き方(構文)が分かりやすいので、すぐに使えると思います。
基本は検索範囲に列を指定して行番号を取り出すというのが利便性が高いと思います。
そして忘れてはいけないのが、必ず「エラー回避のコードを記述しておく」ということです。
実用場面では、検索値を「定数」にすることはなく「変数」とすることが普通かと思いますが、
その「変数」を検索値に設定する方法も例題テストのコードのようにセットすることも出来ます。
結構使える関数だと思います。
エクセルVBAの学び方、独習の始め方
まずは出来るとこから取り組みましょう。
ガイダンスとなる記事はこちらをご覧ください。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。


電子書籍版「改訂新版 てっとり早く確実にマスターできるExcel VBAの教科書」をamazonで見てみる
(著者)大村あつし(出版社)技術評論社
(税込価格)2,508円(本体2,280円+税)
30冊を超えるExcelのマクロやVBAの解説書を執筆してきた著者による考え抜かれた本書の内容と構成。
独創的な解説手法で必ずExcel VBAが理解できます!
初級からの参考書ですが、より実践的切り口での解説をしています。
QRコードから操作の流れを動画(無音です)で確認することもできるようになりました。
文章解説と動画との関係性は、主は文章での解説、サポートが動画になります。
このサイトでよく使うVBAのコードのトピックを纏めています。
今回の記事はここまでです。 最後までご覧いただき有難うございました。