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

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

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

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

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

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

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

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

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

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

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

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

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

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

ワークシート関数の記事一覧

2020年12月9日SUM関数で合計計算！実務で使えるVBAコード作成 2020年12月17日ワークシート関数VLookupをエクセルVBAで使う。利用法と注意点 2020年12月15日エクセルVBAで使うMatch関数 活用度アップでテッパン関数に！ 2020年12月16日エクセルVBA Index関数 の使い方。行と列の指定で簡単にセル値の抽出 2021年8月13日Round関数の罠。VBAで数値を四捨五入する時の注意点 2021年8月14日RoundUpとRoundDown関数の使い方。数値の切り方に注意が必要です 2021年8月11日Max・Min・Averageワークシート関数で最大値・最小値・平均を求める

関数リファレンスの記事一覧

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

Match関数の構文は

構文（記述方法）は

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

となります。

検索値

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

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

検索範囲

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

検索方法

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

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

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

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

例題の提示

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

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

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

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

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

例題のコード　テスト1

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

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

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

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

MEMO

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

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

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

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

例題のコード　テスト2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2021年3月18日 エクセルVBAを独学で習得する！ために大切な7つのポイントを解説します

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

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

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

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

改訂新版 てっとり早く確実にマスターできるＥｘｃｅｌ ＶＢＡの教科書

定番参考書の改定新版が、動画付きになりもっと分かり易くなった

電子書籍版「改訂新版 てっとり早く確実にマスターできるＥｘｃｅｌ ＶＢＡの教科書」をamazonで見てみる

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

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

てっとり早く確実にマスターできるＥｘｃｅｌ　ＶＢＡの教科書 改訂新版/技術評論社/大村あつし

posted with カエレバ

楽天市場

Amazon

Yahooショッピング

7net

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

「エクセルVBA最速理解」の記事一覧を開く

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