Excel VBA Findで膨大なデータを高速で検索する方法と速度検証

高速化

今回は高速で文字列を検索する方法をご説明します。

結論から言いますと、検索する回数で高速の方法が異なります。

また、完全一致の場合に限りますので部分一致の検索方法等ではご使用できません。

一般的に使用される検索方法は「Findメソッド」ではないでしょうか。

そしてFindの高速として挙げられるのが 「Match関数」や「配列」かと思います。

今回は全ての検索結果を取得したいため「Match関数」は検証に含めません。

今回紹介する方法は、データ量が多い場合に「配列」を大幅に超える高速方法です

検証はいいから、結果と方法だけ知りたいという方は、『4.1万回検索してみる「超高速の方法で10000回検索してみる」』のサンプルコードをご覧ください。

Findメソッド」の詳細については「指定の文字列をFind、FindNextで検索する方法」をご覧ください。

1.検証に使うサンプルデータ

まずはそれぞれの高速検索方法の検証に使うサンプルデータを用意しました。

A列に商品名「商品100001」~「商品110000」までの1万行あります。

B列は検索結果を出力する列です。

D列には上記A列のデータを含む20万行のデータです。(※含まないデータもあります。)

2.1つの検索ワードを高速で検索する方法

まずは1ワードを高速に検索する方法です。

20万行のデータから「商品100001」というワードを検索する検証です。

サンプルコードの説明はコード内のコメントをご覧ください。

配列で検索する

データ範囲を配列に入れて、検索ワードと一致した場合に行数を取得します。

この方法が一番高速化と思います。

Sub Sample1()

Dim SearchRange As Range '検索範囲格納
Dim KeyItem     As String '検索ワード
Dim MyAray      As Variant '検索範囲の配列
Dim i           As Long
Dim MsgStr      As String

KeyItem = "商品100001" '検索ワード

Set SearchRange = Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)) '検索範囲

MyAray = SearchRange '配列格納

For i = LBound(MyAray) To UBound(MyAray) '格納した配列をループ

    If MyAray(i, 1) = KeyItem Then '検索ワードに一致したら取得
    
        MsgStr = MsgStr & i & "行目" & vbCrLf '文字列に格納
    
    End If

Next i

MsgBox MsgStr
End Sub

検索結果は「0.11秒」でした。

Findメソッドで検索する

データ範囲を配列に入れて、検索ワードを「Findメソッド」で検索します。

一致した場合に行数を取得します。

Sub Sample2()

Dim SearchRange As Range '検索範囲格納
Dim ResultRange As Range '検索結果格納
Dim StartRange  As Range '検索行格納
Dim KeyItem     As String '検索ワード
Dim MsgStr      As String
Dim i           As Long

Set SearchRange = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp)) '検索範囲

KeyItem = "商品100001" '検索ワード

Set ResultRange = SearchRange.Find(KeyItem, LookAt:=xlWhole) '最初に一致したRangeを取得

If ResultRange Is Nothing Then '検索結果を判定

    MsgBox "検索文字列はありませんでした"
    
    Exit Sub

Else

    Set StartRange = ResultRange '最初に見つかったセルを格納しておく
    MsgStr = ResultRange.Row & "行目" & vbCrLf '文字列に格納
    
End If

Do

    Set ResultRange = SearchRange.FindNext(ResultRange) '次の検索セルを指定する
    
    If ResultRange.Address = StartRange.Address Then '見つかったセルが最初のセルか判定
    
        Exit Do '同じ場合はループを離脱
    
    Else
    
        MsgStr = MsgStr & ResultRange.Row & "行目" & vbCrLf '文字列に格納
    
    End If

Loop

MsgBox MsgStr

End Sub

遅いといわれる「Find」メソッドですが、検索結果は「0.17秒」でした。

配列ほどではありませんが、そこまで遅くありません。

膨大なデータを超高速で検索する方法

次は今回紹介する「膨大なデータを超高速で検索する方法」です。

Sub Sample3()

Dim SearchArray As Variant '検索範囲格納
Dim KeyItem     As String '検索ワード
Dim myDic       As Object 'Dictionary
Dim MaxRow      As Long
Dim Keyval      As String '検索Key格納
Dim Itemval     As Long '検索Item(行数)格納
Dim MsgStr      As String
Dim i           As Long

KeyItem = "商品100001" '検索ワード

MaxRow = Cells(Rows.Count, 4).End(xlUp).Row '最終行を取得

SearchArray = Range(Cells(2, 4), Cells(MaxRow, 4)) '検索範囲

Set myDic = CreateObject("Scripting.Dictionary") 'Dictionary
    
For i = 1 To UBound(SearchArray) '配列ループ

    Keyval = SearchArray(i, 1) 'Key格納
    Itemval = i + 1 'Item(行数)格納
    
    If Not myDic.Exists(Keyval) Then 'Dictionary格納
        
        myDic.Add Keyval, Itemval
    
    Else
    
        myDic(Keyval) = myDic(Keyval) & "/"  '登録済みの場合は行数をItemに連結する
    
    End If
    
Next i

temp = Split(myDic(KeyItem), "/") 'Itemを"/"で分解する

For i = 0 To UBound(temp)

    MsgStr = MsgStr & temp(i) & "行目" & vbCrLf '要素数分文字列に格納

Next i

MsgBox MsgStr

End Sub

検索結果は「1.62秒」です。

他の配列「0.11秒」、Find「0.17」と比較すると10倍以上遅いですね。

最初に記載した通り、「データ量が多い場合に」に効果を発揮します。

では、次の検証では複数回ループさせます。

3.複数回検索する方法と検証結果

次は複数回検索する方法で検証したいと思います。

サンプルデータをA列に1万行用意しましたが、まずは100個の商品名を検索したいと思います。

取得した行数を「/」で繋げて、B列に出力します

取得した行数をRangeやRowに格納する事でセル、行を選択する事も可能です。

配列で複数回検索する

データ範囲を配列に入れて、検索ワードと一致した場合に行数を取得する処理を100回繰り返します。

1ワードでは圧倒的に早かったですが、今回はどうでしょう。

Sub Sample4()

Dim SearchArray As Variant '検索範囲格納
Dim OutArray()  As Variant '出力用配列
Dim i           As Long
Dim n           As Long
Dim KeyItem         As String
    
ReDim OutArray(0 To 99, 0 To 0) '出力用配列

SearchArray = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp)) '検索範囲
    
For i = 2 To 101 '100回分検索

    KeyItem = Cells(i, 1) '検索ワード
    
    For n = LBound(SearchArray) To UBound(SearchArray) '配列分ループ
    
        If SearchArray(n, 1) = KeyItem Then '一致したか判定
        
            OutArray(i - 2, 0) = OutArray(i - 2, 0) & n + 1 & "/" '一致した場合は行数を配列に格納
        
        End If
    
    Next n

Next i

Range(Cells(2, 2), Cells(101, 2)) = OutArray '配列を出力

End Sub

検索結果は「5.03秒」でした。

100回で約5秒ではこれが1万回等になった場合は500秒かかる計算になります。

Findメソッドで複数回検索する

データ範囲を配列に入れて、検索ワードを「Findメソッド」で検索した結果を取得する処理を100回繰り返します。

Sub Sample5()

Dim SearchRange As Range '検索範囲格納
Dim ResultRange As Range '検索結果格納
Dim StartRange  As Range '検索行格納
Dim KeyItem     As String
Dim i           As Long

Set SearchRange = Range("D1:D200001") '検索したいデータ範囲

ReDim OutArray(0 To 99, 0 To 0) '出力用配列

For i = 2 To 101 '100回分検索

    KeyItem = Cells(i, 1) '検索ワード
    
    Set ResultRange = SearchRange.Find(KeyItem, LookAt:=xlWhole) '最初に一致したRangeを取得
    
    If ResultRange Is Nothing Then '検索結果を判定
    
        MsgBox "検索文字列はありませんでした"
        
        Exit Sub
    
    Else
    
        Set StartRange = ResultRange '最初に見つかったセルを格納しておく
        
        OutArray(i - 2, 0) = ResultRange.Row '文字列に格納
    
    End If
    
    Do
    
        Set ResultRange = SearchRange.FindNext(ResultRange) '次の検索セルを指定する
        
        If ResultRange.Address = StartRange.Address Then '見つかったセルが最初のセルか判定
        
            Exit Do '同じ場合はループを離脱
        
        Else
        
            OutArray(i - 2, 0) = OutArray(i - 2, 0) & "/" & ResultRange.Row '文字列を連結して格納
        
        End If
    
    Loop
    
Next i

Range(Cells(2, 2), Cells(101, 2)) = OutArray '配列を出力

End Sub

検索結果は「15.0秒」でした。

100回でかなり時間がかかるようになりました。

配列の3倍くらい遅いですね。

膨大なデータを超高速で複数回検索する方法

次はいよいよ、紹介する検索方法の速度が発揮されます。

100回の検索で配列は5秒、Findは15秒でした。

Sub Sample6()

Dim SearchArray As Variant
Dim OutArray()  As Variant
Dim KeyItem     As String
Dim MaxRow      As Long
Dim myDic       As Object
Dim Keyval      As String
Dim Itemval     As Long
Dim i           As Long
Dim n           As Long
 
MaxRow = Cells(Rows.Count, 4).End(xlUp).Row '最終行を取得

SearchArray = Range(Cells(2, 4), Cells(MaxRow, 4)) '検索したいデータ範囲

Set myDic = CreateObject("Scripting.Dictionary") 'Dictionary
    
For i = 1 To UBound(SearchArray) '配列ループ

    Keyval = SearchArray(i, 1) 'Key格納
    Itemval = i + 1 'Item(行数)格納
    
    If Not myDic.Exists(Keyval) Then 'Dictionary格納
        
        myDic.Add Keyval, Itemval
    
    Else
    
        myDic(Keyval) = myDic(Keyval) & "/" & Itemval '登録済みの場合は行数をItemに連結する
    
    End If
    
Next i

ReDim OutArray(0 To 99, 0 To 0)

For n = 2 To 101

    KeyItem = Cells(n, 1)
    
    OutArray(n - 2, 0) = myDic(KeyItem)

Next n

Range(Cells(2, 2), Cells(101, 2)) = OutArray '配列を出力

End Sub

検索結果は「1.60秒」でした。

1ワード検索の時には配列やFindと比較すると、非常に遅く感じましたが100回の検索で配列「5秒」、Find「15秒」を大きく上回り圧倒的に高速という結果になりました

4.1万回検索してみる

Findメソッドは時間がかかりすぎるので検証しませんが、100回で15秒ということは1万回では約「1500秒」ということが想定されます。

配列でも100回で秒という事なので「500秒」は想定していますので、1000回で検証します。

配列で1000回検索してみる

Sub Sample7()

Dim SearchArray As Variant '検索範囲格納
Dim OutArray()  As Variant '出力用配列
Dim i           As Long
Dim n           As Long
Dim KeyItem     As String
    
ReDim OutArray(0 To 999, 0 To 0) '出力用配列

SearchArray = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp)) '検索範囲
    
For i = 2 To 1001 '100回分検索

    KeyItem = Cells(i, 1) '検索ワード
    
    For n = LBound(SearchArray) To UBound(SearchArray) '配列分ループ
    
        If SearchArray(n, 1) = KeyItem Then '一致したか判定
        
            OutArray(i - 2, 0) = OutArray(i - 2, 0) & n + 1 & "/" '一致した場合は行数を配列に格納
        
        End If
    
    Next n

Next i

Range(Cells(2, 2), Cells(1001, 2)) = OutArray '配列を出力

End Sub

想定通り50秒なので、1000回で500秒といったところです

超高速の方法で10000回検索してみる

サンプルデータに用意した1万行をすべて検索してみます。

Sub Sample8()

Dim SearchArray As Variant
Dim OutArray()  As Variant
Dim KeyItem     As String
Dim MaxRow      As Long
Dim myDic       As Object
Dim Keyval      As String
Dim Itemval     As Long
Dim i           As Long
Dim n           As Long
 
'開始時間取得
startTime = Timer

MaxRow = Cells(Rows.Count, 4).End(xlUp).Row '最終行を取得

SearchArray = Range(Cells(2, 4), Cells(MaxRow, 4)) '検索したいデータ範囲

Set myDic = CreateObject("Scripting.Dictionary") 'Dictionary
    
For i = 1 To UBound(SearchArray) '配列ループ

    Keyval = SearchArray(i, 1) 'Key格納
    Itemval = i + 1 'Item(行数)格納
    
    If Not myDic.Exists(Keyval) Then 'Dictionary格納
        
        myDic.Add Keyval, Itemval
    
    Else
    
        myDic(Keyval) = myDic(Keyval) & "/" & Itemval '登録済みの場合は行数をItemに連結する
    
    End If
    
Next i

ReDim OutArray(0 To 9999, 0 To 0)

For n = 2 To 10001

    KeyItem = Cells(n, 1)
    
    OutArray(n - 2, 0) = myDic(KeyItem)

Next n

Range(Cells(2, 2), Cells(10001, 2)) = OutArray '配列を出力

End Sub

1万行でもほぼ変わらない「1.76秒」です。

10万行であってもそこまで変わらないと思います。

取得した行を個別に取得したい場合は「Sub Sample3()」のような処理を加えます。

「/」で文字区切りをすることで、「/」で結合された文字列を分解して取得する事が可能です。

temp = Split(myDic(KeyItem), "/") 'Itemを"/"で分解する

For i = 0 To UBound(temp)

    MsgStr = MsgStr & temp(i) & "行目" & vbCrLf '要素数分文字列に格納

Next i

5.まとめ

今回の検証で「膨大なデータで超高速」をいうのは変数を使う方法や、Findを使う方法と大きな違いがあります。

それはループ回数です。

配列やFindは回数が増えるたびに比例して、指定したデータ範囲を都度ループするため、倍々でループ回数が膨大に増えてしまいます。

「膨大なデータで超高速」の方法は検索文字列が増えてもループ回数が検索文字列分しか増加しないために、検索文字列が増えてもほとんど変化しませんでした。

タイトルとURLをコピーしました