[プログラミング: ExcelVBA] Match関数で配列を検索したらどれぐらいの要素数まで検索できるか？

2020年2月21日 23:02

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

　他のnote記事でも書いていますが、僕は配列から値を検索するときにワークシート関数のMatch関数を使うタイプです。
　恐らく僕以外にもMatch関数を使って検索する人がいるのではないかと思います。

　そこで、Match関数を使って1次元配列と2次元配列の要素をどこまで検索できるのか実験しました。

　Match関数を使ってる人からすれば「そもそもシートが2次元配列（セル範囲をVariant型変数に入れると2次元配列が形成される）なんだから、1次元配列じゃ使えないだろ。」と言うかもしれませんが、使えなくはないんです。
　ただ、1次元配列も2次元配列もどこまで（どれぐらいの要素数まで）使えるのわからなかったので、実験してみました。

＜実験方法＞

・1次元配列と2次元配列を用意し、要素には要素番号と同じ 1～n までを順に格納する
　＜例＞　配列(1) = 1
　　　　　配列(2) = 2
　　　　　配列(3) = 3
　　　　　…
　　　　　配列(n) = n

・配列の要素数は 65,536, 100,000, 500,000, 1,048,576, 2,000,000 の5パターンとする
　※ 65,536は.xls形式のシート最大行数、1,048,576 は.xlsx形式のシート最大行数であるため、シート上で表現できる行数に合わせた要素数を検索できるのか確認する目的で含めている

・要素番号1つずつ指定すると処理に時間が掛かるので、Match関数の第1引数(検索値)には、1～nまでの数を1,000単位で与える
　※ 以前行った実験 ”1次元配列から値を検索する方法とその処理速度のまとめ(値の重複有り)” https://note.com/skeccho/n/n7298499a265f から、要素数が増えるにつれ処理に大幅な時間が掛かることから
　※ ただし、テストする65,536等の1000で割り切れない要素数については、65,001からは1ずつ加算していく

・1,000単位で検索を行い、エラーが発生したら検索できる閾値を探すために値を1,000戻し、そこから1ずつ加算して検索する
　＜例＞　1,000　←検索出来た
　　　　　　↓
　　　　　2,000　←検索出来た
　　　　　　↓
　　　　　3,000　←エラー
　　　　　　↓
　　　　　2,001　←検索出来た
　　　　　　↓
　　　　　2,002　←検索出来た
　　　　　　↓
　　　　　2,003　←エラー（2,002まで検索できた！）

・実験は要素数毎に分け、各要素数を5回ずつ実行する

＜コードの準備＞

Option Explicit

Sub ここから始める()
'===== 変数宣言 =============================================================
 Dim i As Long
 Dim varLoopVal As Variant
 Dim varTmpVal As Variant
'==========================================================================
 
 varLoopVal = Array(65536, 100000, 500000, 1048576, 2000000)

 '---- 1次元配列を検索
 For Each varTmpVal In varLoopVal
   For i = 1 To 5
     Call mdlExperiment2.Match関数で検索_1次元配列(varTmpVal, 1000)
     DoEvents
   Next
 Next

 '---- 2次元配列を検索
 For Each varTmpVal In varLoopVal
   For i = 1 To 5
     Call mdlExperiment2.Match関数で検索_2次元配列(varTmpVal, 1000)
     DoEvents
   Next
 Next

End Sub

＜1次元配列　検索用コード＞


Sub Match関数で検索_1次元配列(ByVal vlLoopVal As Long, _
                             ByVal vlStepVal As Long)
'===== 変数宣言 =============================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim varMtch As Variant
 Dim i As Long, j As Long
 
 Dim blnExecFlg As Boolean
 Dim lngTmpVal As Long
 Dim lngThreshold As Long
'==========================================================================
 
 'リストを作成
 ReDim varList(1 To vlLoopVal)
 For i = LBound(varList) To UBound(varList)
   varList(i) = i
 Next
 
 lngThreshold = Int(vlLoopVal / vlStepVal) * vlStepVal
 
 '----- 検索
 With Application
   For i = 1 To vlLoopVal
     blnExecFlg = False
     
     If i Mod vlStepVal = 0 Then
       blnExecFlg = True
     Else
       If i > lngThreshold Then
         blnExecFlg = True
       End If
     End If
     
     If blnExecFlg Then
       
       '検索
       varMtch = .Match(i, varList, 0)
       
       If IsError(varMtch) Then
         lngTmpVal = Int((i - 1) / vlStepVal) * vlStepVal
         For j = (lngTmpVal + 1) To (i - 1)
           varMtch = .Match(j, varList, 0)
           
           If IsError(varMtch) Then
             i = j
             GoTo EndLine
           End If
         Next
       End If
     End If
   Next
 End With
 
EndLine:

End Sub

＜2次元配列　検索用コード＞

　※配列以外は1次元配列検索用コードと一緒。

Sub Match関数で検索_2次元配列(ByVal vlLoopVal As Long, _
                             ByVal vlStepVal As Long)
'===== 変数宣言 =============================================================
 Dim varList As Variant
 Dim varSearchVal As Variant
 Dim varMtch As Variant
 Dim i As Long, j As Long
 
 Dim blnExecFlg As Boolean
 Dim lngTmpVal As String
 Dim lngThreshold As Long
'==========================================================================
 
 'リストを作成
 ReDim varList(1 To vlLoopVal, 1 To 1)
 For i = LBound(varList) To UBound(varList)
   varList(i, 1) = i
 Next
 
 lngThreshold = Int(vlLoopVal / vlStepVal) * vlStepVal
 
 '----- 検索
 With Application
   For i = 1 To vlLoopVal
     blnExecFlg = False
     
     If i Mod vlStepVal = 0 Then
       blnExecFlg = True
     Else
       If i > lngThreshold Then
         blnExecFlg = True
       End If
     End If
     
     If blnExecFlg Then
       
       '検索
       varMtch = .Match(i, varList, 0)
       If IsError(varMtch) Then
         lngTmpVal = Int((i - 1) / vlStepVal) * vlStepVal
         For j = (lngTmpVal + 1) To (i - 1)
           varMtch = Application.Match(j, varList, 0)
           If IsError(varMtch) Then
             i = j
             GoTo EndLine
           End If
         Next
       End If
     End If
   Next
 End With
 
EndLine:

End Sub

　コードの説明ですが、1次元配列から検索するコードも2次元配列から検索するコードも配列が1次元か2次元かの違いだけで、後は同じ仕様です。
　
　一番上に示したコード ”ここから始める()” は、処理が終わるまで時間が掛かるであろう、各要素数と次元数のそれぞれのパターンを連続して処理したかったので、作成しています。

　ちなみになぜ、For...Next 文の Stepを使わないのかというと、、、
例えば、" For i = 1 To 10000 Step 1000 " とした場合にループ内で処理されるのが、i=1, 1001, 2001, 2001, .... 8001, 9001 の時になってしまうからです。
　その為、上記コードのように少し面倒なコードを書かなければなりません。
　また、可読性が悪くなるため上のコードからは Debug.Print などのログ出力部分は取り除いています。

＜実行結果＞

　実行結果をシートの表にまとめました。

　結論から言うと、要素数が2,000,000個でも2次元配列の場合は検索できましたが、1次元配列は100,000個でも34,465個目の要素は検索できませんでした。
　なお、1次元配列では100,000個中、34,465個目でエラーが発生していますが、別のPCでテスト実行したときは34,464個目でエラーが発生していたので、環境により少し変動すると思います。
　あと、よくわからない結果として、1次元配列では10万個の要素だと34,465個目でエラーが発生していますが、1,000,000個では65,537個目でエラーが発生しており、配列の要素数が多いからと言って検索できる要素数が少なくなるわけでも無いようです。

＜結論＞

　結論としては、Match関数で1次元配列を検索するときは6万5千個ぐらいの要素数までにしたほうが良い。2次元配列であれば2百万個でも問題ないが、処理に時間が掛かりすぎるためAccess等のDBでの検索に切り替えたほうが良い。
・・・ということですかね。

　Match関数は2次元配列で程々に使いましょう(笑)

　恐らく、他のVlookup関数とかも同じような結果になりそうですね。次回以降、時間があればやってみようと思います。

　最後まで見ていただいてありがとうございます。

　よろしければ、最後に ”スキ” をしてくれると僕のモチベーションが上がりますので、ぜひよろしくお願いします！

　それじゃ ('ω')ノ

[プログラミング: ExcelVBA] Match関数で配列を検索したらどれぐらいの要素数まで検索できるか？

＜ 実験方法 ＞

＜ コードの準備 ＞

＜ 実行結果 ＞

＜ 結論 ＞

いいなと思ったら応援しよう！

＜実験方法＞

＜コードの準備＞

＜実行結果＞

＜結論＞