【VBA】セル範囲内で指定した要素がどこにあるか検索する

投稿者 投稿日:

Excelでセルの範囲内で指定した要素の位置を検索する方法として、数式の場合はMATCH関数を使用します。ではVBAで同様な処理を行うにはどうしたら良いでしょうか？

今回はVBAで指定した要素の位置を検索する方法を検索する方法について紹介します。

目次
  1. 使用するデータ
  2. 方法1 セル範囲をループする
  3. 方法2 配列を使用する
  4. 方法3 Dictionaryを使用する
  5. まとめ

使用するデータ

以下の画像のように、番号と価格が並んでいるデータが50万件あるExcelで、A列の値が I40LZUVZDTER4a1ia のデータがどこにあるか、VBAで検索する方法について考えてみます。

今回は重複する番号のデータは存在しない前提とします。

方法1 セル範囲をループする

まず最初に思いつきそうなのは、セル範囲をループすることで検索する方法です。例えば以下のようなコードです。

コード

'セル範囲をループして検索
Sub sample1()
    
    Dim r As Range '検索セル
    Dim searchValue As Variant '検索する番号
    Dim rownum As Long
    '指定した要素の位置
    
    searchValue = "I40LZUVZDTER4a1ia"
    
    ' セル範囲をループ
    For Each r In Range("A1:A500000")
        If r.Value = searchValue Then
            rownum = r.Row
        End If
    Next
    
    If rownum <> 0 Then
        Debug.Print searchValue & "の行番号は" & rownum & "です。"
    End If
End Sub

上記のコードで、セルの範囲内で指定した要素の位置を検索するという目的は達成できますが、このコードには問題があります。

それはなにか？

1回検索、10回検索、100回検索、1000回検索とそれぞれの実行時間を測定してみるとわかります。

実行結果

1回検索10回検索100回検索1000回検索
0.594秒5.891秒60.641秒710秒

実行結果を見ると、検索回数が増えるごとに線形的に実行時間が増えていき、1000回検索では約12分も待たなくてはいけません。また、1回の実行に約0.6秒かかっているのは一見そこまで遅くなさそうに見えますが、実際に実行して1秒弱待たないと行けないのはかなり遅いです。

では、もっと早くする方法はあるのでしょうか？それは配列を使うことです。

方法2 配列を使用する

方法1が遅い原因の1つとして、r.Value = searchValueのようにセルを参照する処理が、何十万回と実行されていることが挙げられます。セルを参照する処理が多いとVBAの実行時間がとても遅くなります。

そのため、セル範囲の値を配列に格納し配列をループすることで、セル参照を1回に抑える事ができます。

コード

'セル範囲を配列に入れて、ループして検索
Sub sample2()

    Dim i As Long 'ループカウンタ
    Dim r As Range '検索セル
    Dim searchValue As Variant '検索する番号
    Dim rownum As Long '指定した要素の位置
    Dim arr As Variant 'セル範囲を格納する配列
    
    searchValue = "I40LZUVZDTER4a1ia"
    
    ' セル範囲を配列に格納
    arr = Range("A1:A500000").Value
    
    ' 配列の要素数分ループ
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = searchValue Then
            rownum = i
        End If
    Next
    
    If rownum <> 0 Then
        Debug.Print searchValue & "の行番号は" & rownum & "です。"
    End If
End Sub

上記のコードでは、セル範囲の値を配列arrに格納し、配列をループして検索したい番号と一致した、ループカウンタの値（＝セル範囲での位置）を表示します。

方法1と同様に1回検索、10回検索、100回検索、1000回検索それぞれの実行時間を測定してみます。

実行結果

1回検索10回検索100回検索1000回検索
0.16秒0.77秒6.18秒57.91秒

実行結果を見ると、配列を使用した場合に100回実行して約6秒と、方法1と比べて大幅に実行時間が短くなっていることがわかります。

さて、配列を用いると短い時間で検索できるとわかりましたが、繰り返し何回も検索すると線形的に時間がかかり、長い時間待たないと行けない問題は解決していません。

この問題はDictionaryを使用すると解決します。

少し余談

方法2のコードでは、要素の検索として配列の要素を1つずつ繰り返し調べていますが、他の方法としてワークシート関数のMATCHを使う方法があります。

参考：Microsoft公式ドキュメント WorksheetFunction.Match メソッド (Excel)

'Matchで検索
Sub sample2_match()

    Dim r As Range '検索セル
    Dim searchValue As Variant '検索する番号
    Dim rownum As Long '指定した要素の位置
    Dim arr As Variant 'セル範囲を格納する配列
    
    searchValue = "I40LZUVZDTER4a1ia"
    
    ' セル範囲を配列に格納
    arr = Range("A1:A500000").Value
    
    On Error GoTo catch_error
    ' MATCHで位置を検索
    rownum = Application.WorksheetFunction.Match(searchValue, arr, 0)
    Debug.Print searchValue & "の行番号は" & rownum & "です。"
catch_error:
End Sub

ただし、Matchを使用した場合、配列の要素を1つずつ繰り返し調べた場合に比べて、約2倍ぐらい実行時間がかかるので基本的には配列を使用するのが良いと思います。

方法3 Dictionaryを使用する

Dictionary（連想配列）は、キーとアイテムをセットで格納するオブジェクトです。配列ではアイテムのみ格納していたのに対してDictionaryはキーも格納しているため、キーを用いて高速に検索することができます。

参考：Microsoft公式ドキュメント Dictionary オブジェクト

コード

'配列をDictionaryに入れて検索
Sub sample3()

    Dim i As Long 'ループカウンタ
    Dim r As Range '検索セル
    Dim searchValue As Variant '検索する番号
    Dim rownum As Long '指定した要素の位置
    Dim arr As Variant 'セル範囲を格納する配列
    Dim dic As Object 'セル範囲を格納するdictionary
    
    ' Dictionaryオブジェクトを作成
    Set dic = CreateObject("Scripting.Dictionary")
    
    searchValue = "I40LZUVZDTER4a1ia"
    
    ' Dictionaryオブジェクトにキーとアイテムをセット
    arr = Range("A1:A500000").Value
    For i = 1 To UBound(arr)
        dic.Add arr(i, 1), i
    Next
    
    ' キーを用いてDictionaryオブジェクトからアイテムを取得
    rownum = dic(searchValue)
    
    If rownum <> 0 Then
        Debug.Print searchValue & "の行番号は" & rownum & "です。"
    End If
End Sub

上記のコードでは一度セル範囲の値を配列に入れたあと、Dictionaryにキー＝配列の値、アイテム＝ループカウンタの値（＝セル範囲での位置）を格納しています。そして、Dictionaryを使用して検索を行っています。

※Dictionaryは同名のキーを格納することができないため、業務等で使用する場合は格納する前に、Existsメソッドを用いて同名のキーがないか確認するのが望ましいです。今回は、重複するデータが存在しない前提のため省いています。

では、1回検索、10回検索、100回検索、1000回検索それぞれの実行時間を測定してみます。

実行結果

1回検索10回検索100回検索1000回検索
14.914秒14.977秒14.918秒15.816秒

実行結果を見ると、100回程度の検索では方法2の配列を使用したときよりも遅いことがわかります。これは、検索の前にDictionaryに50万件ほどのデータを格納する処理があるためです。

ですが、Dictionaryの場合1回検索でも1000回検索でも実行時間がほとんど変わらず、1000回検索時は約1/3の実行時間になっています。

よって、検索回数（ループ回数）が比較的少ない場合は配列を使用し、何回も繰り返し検索する必要がある場合は、Dictionaryを使用するとかなり効果的であると言えます。

まとめ

今回は、セル範囲内で指定した要素がどこにあるか検索する方法として、3つの方法を紹介しました。VBAではやりたいことに対して、方法が複数ある事がよくあります。

方法1と方法2のように明らかに、優れている方法がある場合はその方法を覚えれば良いですが、方法2と方法3のようにその時の状況によって一長一短ある場合は、両方覚えて使い分けて行きたいですね。

ExcelⓇ 業務をもっとカンタンに

投稿者: komatsu