エクセルVBAで使うMatch関数 活用度アップでテッパン関数に!

vbamatcheyecatch001

エクセルVBAでよく使うワークシート関数としてMatch関数があります。

この関数は、検索値に該当する値の行番号や列番号を取り出せます。

非常に便利です。これは活用度アップで是非テッパン関数にしましょう。

エクセルVBAで利用できる関数についてがテーマです。

今回は、データリストから必要な値や数値を取り出してくる「Match関数」について、使い方を説明します。

こんにちは、じゅんぱ店長(@junpa33)です。

エクセルVBAでデータ検索を行いたいことは、かなりの頻度で有ります。

For~Nextで1行ずつ値を参照して目的のデータを取り出す方法( ” 力わざ ” と言います)もありますが、

オシャレにサクッ!と必要データを取り出せれば、人気者にもなります。(誰も見てないか。)

こういった、VBAで検索する方法として有名どころの関数がありますが、これはいずれも、ワークシート上で関数式として使う関数です。

エクセルVBA上では、WorksheetFunctionオブジェクトの中で「メソッド」として使用することが出来るのです。

関数が返してくる値に注目してください。

それぞれの関数によって、

導く値やセットするルールも特徴がありますので、その場に合った使い分けが必要になります。

というか、自分の場合は、得意の関数が使える方向にコードを導いていきますが・・・

いつも使いのご贔屓関数を是非1つは作っておいた方が吉かと思います。

「関数リファレンス」を調べる

Match関数 活用度アップでテッパン関数にする

Match関数の構文は

構文(記述方法)は

WorksheetFunction.Match( 検索値 , 検索範囲 , 検索方法)

となります。

検索値

検索値には、数値、文字列、理論値や値に対するセル参照を指定します。

もちろん定数だけでなく変数も指定できます。

検索範囲

検索したいセル範囲を、1行または1列の範囲で指定します。

検索方法

検索方法は「完全一致」「検索値以下の最大値」「検索値以上の最大値」を選択することが出来ます。

「完全一致」・・・・・・・値は「0」

「検索値以下の最大値」・・値は「1」

「検索値以上の最大値」・・値は「-1」

例題の提示

雑誌リストの一部分を使って例題にしたいと思います。

青字の入力セルに検索値を入力します。

「MvalR」はデータ表の中での行数

「MvalC」はデータ表の中での列数

「Mval2C」はデータ表の中での列数

vbamatchsetumei001

例題のコード テスト1

それぞれ行・列2方向でのテストをします。

列を特定(E列)してその中での検索値を調べます。(行番号をMvalRに表示します。)

行を特定(16行目)してその中での検索値を調べます。(列番号をMvalCに表示します。)

テスト1

今回は、データ表の行・列の大きさは「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列目」の位置関係になります。

この位置関係の差は、コード組立上で反映させていかないと、正確な値が表示されないという事が発生します。

vbamatchsetumei002

MEMO

CurrentRegionプロパティについてはこちらを参考にしてください。

vbacurrentregioneyecatch CurrentRegionは連続データをまとめて掴む

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

vbalastcelleyecatch データ入力済セルの最終行番号を取得する

例題のコード テスト2

テスト1では、選択範囲が1列または1行のみでのコードでした。

これを行列ともに可変にしたらどうなるかのテストをしてみます。

「データ表内のすべてのセル値を検索したら?」

というコードになります。

テスト2

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

実行結果

結果はこのようになりました。

vbamatchsetumei003

Match関数のこの使い方は、コードが少々面倒くさそうな組み方になり、あまりお勧めではない、お遊び的なコードになりました。

エクセルVBAで使うMatch関数 まとめ

Match関数は、コードの書き方(構文)が分かりやすいので、すぐに使えると思います。

基本は検索範囲に列を指定して行番号を取り出すというのが利便性が高いと思います。

そして忘れてはいけないのが、必ず「エラー回避のコードを記述しておく」ということです。

実用場面では、検索値を「定数」にすることはなく「変数」とすることが普通かと思いますが、

その「変数」を検索値に設定する方法も例題テストのコードのようにセットすることも出来ます。

結構使える関数だと思います。

エクセルVBAの学び方、独習の始め方

まずは出来るとこから取り組みましょう。

ガイダンスとなる記事はこちらをご覧ください。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。

Udemy1eyecatch エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

改訂新版 てっとり早く確実にマスターできるExcel VBAの教科書
定番参考書の改定新版が、動画付きになりもっと分かり易くなった
vbastudy022a
vbastudy023a

電子書籍版「改訂新版 てっとり早く確実にマスターできるExcel VBAの教科書」をamazonで見てみる

(著者)大村あつし
(出版社)技術評論社
(税込価格)2,508円(本体2,280円+税)

30冊を超えるExcelのマクロやVBAの解説書を執筆してきた著者による考え抜かれた本書の内容と構成。
独創的な解説手法で必ずExcel VBAが理解できます!
初級からの参考書ですが、より実践的切り口での解説をしています。
QRコードから操作の流れを動画(無音です)で確認することもできるようになりました。
文章解説と動画との関係性は、主は文章での解説、サポートが動画になります。

このサイトでよく使うVBAのコードのトピックを纏めています。

エクセルVBA最速理解で必要な知識を集めよう!

今回の記事はここまでです。   最後までご覧いただき有難うございました。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。