高速で文字列を検索する方法と速度検証結果をご説明します。
1回の検索であれば関数でも配列でも、Findでも正直どれも速いです。
でも1回の検索が早くてもそれを1万回、10万回と繰り返すと数分、数10分とかかってしまうケースがあります。
結論から言いますと、検索する回数で高速の方法が異なります。
また、前提として紹介する検索方法は完全一致の場合に限りますので、部分一致の検索方法等ではご使用できません。
検索するケースに合わせた高速な検索方法をご紹介したいと思います。
「Findメソッド」、「配列」、「Dictionary」の3つを使用した検索方法を、それぞれの検索するケースで検証します。
特に「Dictionary」を使用した検索方法の速さに着目して頂きたいと思います。。
「Findメソッド」、「配列」、「Dictionary」の詳細については下記記事をご覧ください。
1.検証に使うサンプルデータ
まずは、それぞれの高速検索方法の検証に使うサンプルデータを用意しました。
A列に商品名「商品100001」~「商品110000」までの1万行あります。
B列は検索結果を出力する列です。
D列には上記A列のデータを含む20万行のデータです。(※含まないデータもあります。)
2.1つの検索文字列を検索する方法
まずは1つの文字列を高速に検索する方法です。
20万行のデータから「商品100001」という文字列を検索する検証です。
検証結果
1回の検索では配列に格納して、配列内で一致した結果を取得するのが1番早かったです。
着目してほしい「Dictionary」はとても遅いですね・・・。
それではそれぞれのサンプルコードを紹介します。
| 検索方法 | 検索速度 |
| Findメソッド | 0.17秒 |
| 配列 | 0.11秒 |
| Dictionary | 1.62秒 |
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 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秒」でした。
早いですね。
Dictionaryで検索する方法
次は「Dictionary」を使用した検索方法です。
「Dictionary」自体はそもそも検索するためのオブジェクトではないのですが、少し工夫することで検索することにも使用できます。
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回検索をした場合は「Find」と「配列」を抜いて「Dictionary」が一番早い結果になりました。
この結果は実は100回を1000回、1万回と増やせば増やすほど「Find」と「配列」はループ回数が増えて処理速度が増え続けます。
それに比べて「Dictionary」は最初にリストを格納してしまえばループ回数は増えません。
そのため変わらないんですね。
それぞれのサンプルコードをご紹介したいと思います。
| 検索方法 | 検索速度 |
| Findメソッド | 15.0秒 |
| 配列 | 5.03秒 |
| Dictionary | 1.60秒 |
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回でかなり時間がかかるようになりました。
1000回で150秒、1万回で1500秒くらいかかる想定になります。
配列で複数回検索する
データ範囲を配列に入れて、検索ワードと一致した場合に行数を取得する処理を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秒かかる計算になります。
Dictionaryで複数回検索する方法
次はいよいよ、紹介する検索方法の速度が発揮されます。
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秒」ということが想定されます。
検証結果
配列を1000回、Dictionaryを10000回で検索してみました。
圧倒的に差がでました。
それぞれのサンプルコードをご紹介したいと思います。
| 検索方法 | 検索回数 | 検索速度 |
| Findメソッド | なし | 測定無し |
| 配列 | 1000回 | 50秒 |
| Dictionary | 10000回 | 1.72秒 |
配列で10000回検索してみる
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 9999, 0 To 0) '出力用配列
SearchArray = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp)) '検索範囲
For i = 2 To 1001 '1000回分検索
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秒なので、10000回で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 i5.まとめ
今回の検証で「膨大なデータで高速」をいうのは変数を使う方法や、Findを使う方法と大きな違いがあります。
それはループ回数です。
配列やFindは回数が増えるたびに比例して、指定したデータ範囲を都度ループするため、倍々でループ回数が膨大に増えてしまいます。
「膨大なデータで超高速」の方法は検索文字列が増えてもループ回数が検索文字列分しか増加しないために、検索文字列が増えてもほとんど変化しませんでした。
