[プログラミング: ExcelVBA] 1次元配列から値を検索する方法とその処理速度のまとめ(値の重複有り)

2020年2月5日 13:54

　お疲れ様です (し 'ω' )ノ

　みなさん、VBAでシートに登録されている商品マスタからその商品を特定するような場合、どうしていますか？
　僕は、シートに登録されている情報を一度変数に入れて、その中から検索するタイプです。

　このノートでは変数（配列）に格納した値をどうやったら簡単に早く見つけられるかを考え、実験をした結果をまとめてみました。

＜実験内容＞
　要素数10,000個の1次元配列を作成し、そこから10,000回の検索した場合の処理時間を計測する。
　配列に格納する値は10,000を最大値とするランダムな整数値で、重複した値も含まれるが値が見つかった場合はその値の検索を終える（ループから抜ける）。
　また、検索する値もランダムに生成された値を使う。

＜実験環境＞
・OS：Windows 8.1 Pro 64bit
・Office：Office365 32bit
・CPU：Intel Core i5 3450 @3.1GHz
・RAM：16GB

＜検索方法＞
　(1) ワークシート関数のMatch関数を使う（普段僕が多用している方法）
　(2) 配列を要素数の分だけループさせて、要素一つずつ検索値と同じか
　　どうかを比較する
　(3) (2)の方法をループで比較する部分をFunctionプロシージャ化する
　(4) Collectionに入れて、ItemをForEachループさせて比較する
　(5) (4)のCollectionをForループさせ、Itemをインデックス番号指定で比較する
　(6) Dictionaryに入れて、ForEachループで取得したItemと比較する

＜その他＞
　各検索方法をそれぞれ10回ずつ実行し、その平均速度を取る。

＜実験に使ったコードと実行結果＞
(1) ワークシート関数のMatch関数を使う（普段僕が多用している方法）

Sub Match関数で検索()
'===== 変数宣言 ========================================================
 Dim varValList As Variant
 Dim varSearchVal As Variant
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long
 Dim dblStartTime As Double
'======================================================================
 
 lngMaxVal = 10000       '配列に格納する値の最大値 / 配列の要素数
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
 '----- 計測開始
 dblStartTime = Timer
 
 '----- 検索
 With Application
   For i = 1 To lngMaxVal
     '検索値を作成
     varSearchVal = Int(Rnd() * lngMaxVal + 1)
     
     '検索
     varMtch = .Match(varSearchVal, varList, 0)
   Next
 End With
 
 '---- 計測終了
 Debug.Print _
   "Match関数で検索 " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
End Sub

※　Application.Match() にする理由は、値が見つからなかった場合も変数にエラー値として返してくれるため。WorksheetFunction.Match()だとエラートラップしないとプログラムが止まるので。

　(1)　結果：　平均 7.7125秒

Match関数で検索 10,000件処理時間:7.734秒
Match関数で検索 10,000件処理時間:7.734秒
Match関数で検索 10,000件処理時間:7.719秒
Match関数で検索 10,000件処理時間:7.719秒
Match関数で検索 10,000件処理時間:7.703秒
Match関数で検索 10,000件処理時間:7.703秒
Match関数で検索 10,000件処理時間:7.719秒
Match関数で検索 10,000件処理時間:7.703秒
Match関数で検索 10,000件処理時間:7.688秒
Match関数で検索 10,000件処理時間:7.703秒

(2) 配列を要素数の分だけループさせて、要素一つずつ検索値と同じか
　どうかを比較する

Sub ループで検索()
'===== 変数宣言 =======================================================
 Dim varList As Variant
 Dim varSearchVal As Long
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long, j As Long
 Dim dblStartTime As Double
'====================================================================
 
 lngMaxVal = 10000       '配列に格納する値の最大値 / 配列の要素数
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
  '----- 計測開始
 dblStartTime = Timer
 
 For i = 1 To lngMaxVal
   '検索値を作成
   varSearchVal = Int(Rnd() * lngMaxVal + 1)
   
   '検索
   For j = LBound(varList) To UBound(varList)
     If varSearchVal = varList(j) Then
       Exit For
     End If
   Next
 Next
 
 '----- 計測終了
 Debug.Print _
   "ループで検索 " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
End Sub

(2)　結果：　平均 3.3567秒

ループで検索 10,000件処理時間:3.391秒
ループで検索 10,000件処理時間:3.328秒
ループで検索 10,000件処理時間:3.359秒
ループで検索 10,000件処理時間:3.344秒
ループで検索 10,000件処理時間:3.348秒
ループで検索 10,000件処理時間:3.355秒
ループで検索 10,000件処理時間:3.395秒
ループで検索 10,000件処理時間:3.344秒
ループで検索 10,000件処理時間:3.375秒
ループで検索 10,000件処理時間:3.328秒

(3) (2)の方法をループで比較する部分をFunctionプロシージャ化する

Sub ループで検索2_ループをFunction化()
'===== 変数宣言 =========================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long
 Dim dblStartTime As Double
'======================================================================
 
 lngMaxVal = 10000       '配列に格納する値の最大値 / 配列の要素数
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
 '----- 計測開始
 dblStartTime = Timer
 
 For i = 1 To lngMaxVal
   '検索値を作成
   varSearchVal = Int(Rnd() * lngMaxVal + 1)
   
   '検索
   varMtch = SearchLoop(varList, varSearchVal)
 Next
 
 '----- 計測終了
 Debug.Print _
   "ループで探索2_ループをFunction化 " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
End Sub



'----- Functionプロシージャ
' 引数1   : vlList                    / 検索対象リスト
' 引数2   : vlVal                     / 検索値
' 戻り値  : [Long]                    / ヒットした配列の要素番号
Function SearchLoop(ByVal vlList As Variant, _
                    ByVal vlVal As Variant) As Long
'===== 変数宣言 ==========================================================
 Dim i As Long
'========================================================================
 
 For i = LBound(vlList) To UBound(vlList)
   If vlVal = vlList(i) Then
     SearchLoop = i
     Exit Function
   End If
 Next
 
End Function

(3)　結果：　平均 6.0603秒

ループで探索2_ループをFunction化 10,000件処理時間:6.063秒
ループで探索2_ループをFunction化 10,000件処理時間:6.078秒
ループで探索2_ループをFunction化 10,000件処理時間:6.078秒
ループで探索2_ループをFunction化 10,000件処理時間:6.047秒
ループで探索2_ループをFunction化 10,000件処理時間:6.063秒
ループで探索2_ループをFunction化 10,000件処理時間:6.063秒
ループで探索2_ループをFunction化 10,000件処理時間:6.035秒
ループで探索2_ループをFunction化 10,000件処理時間:6.082秒
ループで探索2_ループをFunction化 10,000件処理時間:6.016秒
ループで探索2_ループをFunction化 10,000件処理時間:6.078秒

(4) Collectionに入れて、ItemをForEachループさせて比較する

Sub Collectionで検索()
'===== 変数宣言 ===========================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long
 Dim varColl As Collection
 
 Dim varTmp As Variant
 Dim dblStartTime As Double
'========================================================================
 
 Set varColl = New Collection
 
 lngMaxVal = 10000       '配列に格納する値の最大値
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
 '----- 計測開始
 dblStartTime = Timer
 
 '対象データをコレクションに格納
 For i = LBound(varList) To UBound(varList)
   varColl.Add varList(i)
 Next
 
 For i = 1 To lngMaxVal
   '検索値を作成
   varSearchVal = Int(Rnd() * lngMaxVal + 1)
   
   'コレクションの値をループで検索
   For Each varTmp In varColl
     If varSearchVal = varTmp Then
       Exit For
     End If
   Next
 Next
 
 '----- 計測終了
 Debug.Print _
   "Collectionで検索 " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
 Set varColl = Nothing
End Sub

※　他の検索方法と同様に検索対象の配列を準備した後から計測しているので、配列からCollectionへ値を格納するところから計測している。

(4)　結果：　平均 3.6172秒

Collectionで検索 10,000件処理時間:3.656秒
Collectionで検索 10,000件処理時間:3.641秒
Collectionで検索 10,000件処理時間:3.641秒
Collectionで検索 10,000件処理時間:3.609秒
Collectionで検索 10,000件処理時間:3.609秒
Collectionで検索 10,000件処理時間:3.641秒
Collectionで検索 10,000件処理時間:3.641秒
Collectionで検索 10,000件処理時間:3.578秒
Collectionで検索 10,000件処理時間:3.578秒
Collectionで検索 10,000件処理時間:3.578秒

(5) (4)のCollectionをForループさせ、Itemをインデックス番号指定で比較する

Sub Collectionで検索2_Index指定()
'===== 変数宣言 ===========================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long, j As Long
 Dim varColl As Collection
 Dim dblStartTime As Double
'========================================================================
 
 Set varColl = New Collection
 
 lngMaxVal = 10000       '配列に格納する値の最大値
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
 '----- 計測開始
 dblStartTime = Timer
 
 '対象データをコレクションに追加
 For i = LBound(varList) To UBound(varList)
   varColl.Add varList(i)
 Next
 
 For i = 1 To lngMaxVal
   '検索値を作成
   varSearchVal = Int(Rnd() * lngMaxVal + 1)
   
   'コレクションをインデックスでループ検索
   For j = 1 To lngMaxVal
     If varSearchVal = varColl.Item(j) Then
       Exit For
     End If
   Next
 Next
 
 '----- 計測終了
 Debug.Print _
   "Collectionで検索_Index指定  " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
 Set varColl = Nothing
End Sub

(5)　結果：　平均 1251.750秒（20分51秒）

Collectionで検索_Index指定 10,000件処理時間:1251.750秒

　※　処理時間が掛かり過ぎたため計測は1度しか行わなかった(；一_一)

(6) Dictionaryに入れてForEachループさせて比較する

Sub Dictionaryで検索()
'===== 変数宣言 ===========================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim lngMaxVal As Long
 Dim varMtch As Variant
 Dim i As Long
 Dim varDict As Object
 Dim varTmp As Variant
 Dim dblStartTime As Double
'========================================================================
 
 Set varDict = CreateObject("Scripting.Dictionary")
 
 lngMaxVal = 10000       '配列に格納する値の最大値
 
 'リストを作成
 ReDim varList(1 To lngMaxVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = Int(Rnd() * lngMaxVal + 1)
 Next
 
 '----- 計測開始
 dblStartTime = Timer
 
 '対象データをコレクションに格納
 For i = LBound(varList) To UBound(varList)
   varDict.Add i, varList(i)
 Next
 
 For i = 1 To lngMaxVal
   '検索値を作成
   varSearchVal = Int(Rnd() * lngMaxVal + 1)
   
   'ディクショナリの値をループで検索
   For Each varTmp In varDict.Items
     If varSearchVal = varTmp Then
       Exit For
     End If
   Next
 Next
 
 '----- 計測終了
 Debug.Print _
   "Dictionaryで検索  " & Format(lngMaxVal, "#,##0件") _
   & " 処理時間:" & Format(Timer - dblStartTime, "0.000秒")
 
 Set varDict = Nothing
End Sub

※　DictionaryはCollection同様、Keyに重複した値を格納できないため、Itemに値を格納した。

(6)　結果：　平均 5.4897秒

Dictionaryで検索 10,000件処理時間:5.453秒
Dictionaryで検索 10,000件処理時間:5.484秒
Dictionaryで検索 10,000件処理時間:5.535秒
Dictionaryで検索 10,000件処理時間:5.484秒
Dictionaryで検索 10,000件処理時間:5.488秒
Dictionaryで検索 10,000件処理時間:5.500秒
Dictionaryで検索 10,000件処理時間:5.516秒
Dictionaryで検索 10,000件処理時間:5.469秒
Dictionaryで検索 10,000件処理時間:5.484秒
Dictionaryで検索 10,000件処理時間:5.484秒

＜結果まとめ＞
(1) ワークシート関数のMatch関数を使う（普段僕が多用している方法）
　　結果：　平均 7.7125秒
-----------------------------------------------------------------------------------
(2) 配列を要素数の分だけループさせて、要素一つずつ検索値と同じかどうかを比較する
　　結果：　平均 3.3567秒
-----------------------------------------------------------------------------------
(3) (2)の方法をループで比較する部分をFunctionプロシージャ化する
　　結果：　平均 6.0603秒
-----------------------------------------------------------------------------------
(4) Collectionに入れて、ItemをForEachループさせて比較する
　　結果：　平均 3.6172秒
-----------------------------------------------------------------------------------
(5) (4)のCollectionをForループさせ、Itemをインデックス番号指定で比較する
　　結果：　平均 1251.750秒（20分51秒）
-----------------------------------------------------------------------------------
(6) Dictionaryに入れて、ForEachループさせて比較する
　　結果：　平均 5.4897秒

結果は、上記の通りとなった。

＜まとめ＞
　何と言っても(5)のCollectionのItemをインデックスを指定して比較する方法が激遅だったことに衝撃を受けました。実は(5)の実験は、コードの途中で無限ループに入ったかCPUも使用していない状態でプロセスが応答なしになったんじゃないかと思って、何回かプロセスを強制終了させていました。だけど、そうじゃなくてただ遅いだけで、処理は続いていました。
　因みに別の環境（ノートPC：Win10Pro64bit, Office365 32bit, i5-6200U）で実行した結果だと27分掛かっていました。

　(2)のただ単純にループさせる方は半分の時間で終わってたので、Match関数を常用している自分からするとそれが意外だった。これを確認できたのが僕としては一番の成果だと思っています。

　あと、重複しない値を格納するのであれば、Dictionary の Key に入れて Exists でキーの存在確認が使えたし、今回は参照設定しないで CreateObject してたので、参照設定すればそこでもう少し健闘したのかなと思います。

　記事を最後まで読んでいただきありがとうございました！

　宜しければ "スキ" をしてもらえると次回の記事へのモチベーションが上がり、大変禿げ上がる思いです。

　どうぞよろしくお願いします！

　それじゃ (し 'ω' )ノ

この記事が気に入ったらサポートをしてみませんか？