Excel VBA 検索のやり方（FindとMatch）

VBA
この記事に書いてあること。

１．Findより10倍速いMatch
２．Matchの欠点
３．もう一つのMatch
４．FindとMatchのサンプルコード


１．Findより10倍速いMatch

去年の夏にRangeオブジェクトのメソッド、Findについて書きましたが。

Excel VBA　RangeオブジェクトのFindメソッドの使い方。

はっきり言ってネガティブな意見でした。

実は、使い方に癖がある以外にももう一つ欠点があります。

遅いんです。

と言っても、流石にVBAで1セルずつ見ていくよりは速いでしょうけど。

じゃぁ他にやりようはあるのかと言うと、ワークシート関数のMatchを使うという手があります。

Application.WorksheetFunction.Match

ということですね。

後ほど比較のためのコードも示しますが、Findより10倍くらい速いです。


２．Matchの欠点

ただ、前回の記事の論調と矛盾しますが、その結果の使い勝手で言うとFindの方が上です。

Findは探したい値を持つセルがRangeとして返ってきますので、そのままセルを加工したり、その行の他の列の値を取ってくるなどということが簡単にできます。

一方、Matchは検索範囲の中で何番目に見つかったかが返ってきます。
つまり、検索範囲が1行目からなら良いのですが

スクリーンショット 2022-03-13 063510.jpg

こんなデータのA3:A7を検索範囲として、３を検索したとすると、その結果は３が入っているセルの行５ではなく、検索範囲A3:A7の中で３行目なので、３が返ってきます。

なので、見つかったセルを得るには検索範囲を考慮して自分で計算しないといけません。

また、検索範囲も、FindはA1:B10のように、矩形の範囲を指定できますが、Matchは１行、もしくは１列となる範囲しか指定できません。

と、そんな欠点のあるMatchですが、Findより１０倍速いので、何万件というような大量のデータの中から検索するとか、検索を何度もするというような場合はMatchがおすすめです。


３．もう一つのMatch

で、そのMatchですが、Application.WorksheetFunction.Matchの他に、もう一つあります。

Application.Match

です。
引数はワークシート関数のMatchと同じです。

ワークシート関数のMatchにはもう一つ使い勝手の点で欠点が有って、もし、検索範囲の中に検索対象が無かった場合、エラーとなり、マクロが止まってしまいます。

なので、こんな感じにコーディングする必要があります。

    On Error Resume Next
    FoundRow = WorksheetFunction.Match(5, Range("A1:A10"), 0)
    If Err.Number = 0 Then
    Else
        見つからなかった
    End If
    On Error Goto 0

On Errorで囲むということですね。

その点、Application.Matchの方はもう少し簡単です。
返り値をVariant型の変数で受けるのです。
そして、

    FoundRow = Application.Match(5, Range("A1:A10"), 0)
    If IsError(FoundRow) Then
        見つからなかった。
    End If

こんな感じにするのです。
エラーでない場合は、ワークシート関数のMatch同様、FoundRowには検索範囲で何番目に見つかったかが返ってきます。

ただ、ちょっと不安なのは、Application.Matchで検索してみるとわかりますが、これについて書いた記事は殆どないのです。
で、Microsoftのページに行っても、Application.Matchについては書いていないのです。

これ、使って良いのかなぁ、、と思ってしまいます。
隠し機能みたいなもので、突然使えなくなったりすることも無いとも限りません、、
そのへんは自己責任で、、ｗ


４．FindとMatchのサンプルコード

ま、そのへんはともかく、最後にRange.Find、WorksheetFunction.MatchとApplication.Matchのサンプルコードとして頭書の速度比較のコードを示します。


Sub FindTest()
    Dim i As Integer
    Dim startTime As Double
    Dim TimeFind As Double
    Dim TimeFunc As Double
    Dim TimeMatch As Double
    
    startTime = Timer()
    For i = 1 To 100
        Call findByFind
    Next i
    TimeFind = Timer() - startTime
    
    startTime = Timer()
    For i = 1 To 100
        Call findBySheetFunction
    Next i
    TimeFunc = Timer() - startTime
    
    startTime = Timer()
    For i = 1 To 100
        Call findByMatch
    Next i
    TimeMatch = Timer() - startTime
    
    MsgBox "Find:" & TimeFind & vbCrLf & _
           "Func:" & TimeFunc & vbCrLf & _
           "Match:" & TimeMatch
End Sub

Sub findByFind()
    Dim FirstRange As Range
    Dim FoundRange As Range
    
    Set FirstRange = Range("A1").CurrentRegion.Find(5, lookat:=xlWhole)
    Debug.Print FirstRange.Address
    Set FoundRange = FirstRange
    
    Do
        Set FoundRange = Range("A1").CurrentRegion.FindNext(FoundRange)
        Debug.Print FoundRange.Address
    Loop Until FoundRange.Address = FirstRange.Address
    
End Sub

Sub findBySheetFunction()
    Dim FoundRow As Long
    Dim LastRow As Long
    Dim StartRow As Long
    
    LastRow = Range("A1").CurrentRegion.Rows.Count
    StartRow = 1
    
    Do While StartRow <= LastRow
        On Error Resume Next
        FoundRow = WorksheetFunction.Match(5, Range(Cells(StartRow, 1), Cells(LastRow, 1)), 0)
        If Err.Number = 0 Then
        Else
            Exit Do
        End If
        On Error GoTo 0
        Debug.Print StartRow + FoundRow - 1
        StartRow = StartRow + FoundRow
    Loop
End Sub

Sub findByMatch()
    Dim FoundRow As Variant
    Dim LastRow As Long
    Dim StartRow As Long
    
    LastRow = Range("A1").CurrentRegion.Rows.Count
    StartRow = 1
    
    Do While StartRow <= LastRow
        FoundRow = Application.Match(5, Range(Cells(StartRow, 1), Cells(LastRow, 1)), 0)
        If IsError(FoundRow) Then
            Exit Do
        End If
        Debug.Print StartRow + FoundRow - 1
        StartRow = StartRow + FoundRow
    Loop
End Sub

A1:A10000に乱数で0から10の数字を入れて動かしました。
私のPCで実行した結果は、

Findが152秒
ワークシート関数のMatchが17秒
Application.Matchが15秒

でした。

Application.Matchの方がワークシート関数のMatchより微妙に速いようですね。

ということで、検索するならMatchがおすすめというお話でした。

なお、Microsoft365などの最新のExcelにはXMatchなるワークシート関数があるそうです。
私の手元に試せる環境が無いのと、Microsoftの説明を読んでもよくわからなかったので、これについては他の方におまかせします。

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

この記事へのコメント

コメントを書く

この記事へのトラックバック