VBAで速度アップテクニック～2重ループで文字検索する場合～

2017/02/14 2017/02/15

Excelマクロで、複数行のデータを別の複数行データから同じ文字列を検索するマクロがあったとします。この場合、よく使う手として２重ループを使って検索するのですが、２重ループのため時間がかかってしまいます。これを速度アップさせるテクニックを紹介します。

[ad#top-1]

例えば、下の図のようにA列に県市町村名が郵便番号順に並んでいて、B列に同じく県市町村名がランダムに並んだものを用意します。行数はA列、B列ともに1896行あります。

で、A列のすべての行に対して、B列の中に存在するかを調べるプログラムを組んだとします。

その場合、単純に考えると以下のようなプログラムになります。D1セルに処理時間、D2セルに処理回数を表示するようにしました。（TimerはNowと違ってミリ秒まで現れるので便利です）

Sub Test1()
   dt1 = CDbl(Timer)

   cnt = 0
   For m = 1 To 1896
      For n = 1 To 1896
         cnt = cnt + 1
         If Cells(m, 1) = Cells(n, 2) Then
            'ここで何らかの処理
            Exit For
         End If
      Next n
    Next m

   dt2 = CDbl(Timer)
   Range("D1") = cnt
   Range("D2") = dt2 - dt1
End Sub

実行すると処理時間と処理回数は以下の通りとなりました。19秒近くかかって実用上はちょっと耐えられません。

処理時間(秒)	処理回数(回)
18.88	1,798,356

これをどうやって速度アップするかというと、片方のデータ行を１つの文字列に連結してしまって、その中に検索文字が含まれているか？、という方法を使います。

Sub TEST2()
   dt1 = CDbl(Timer)
   word = ""
   cnt = 0
   For n = 1 To 1896
      word = word &amp; Cells(n, 2) &amp; "," 'カンマが重要
      cnt = cnt + 1
   Next n

   For m = 1 To 1896
      cnt = cnt + 1
      If InStr(word, Cells(m, 1) &amp; ",") &gt; 0 Then
         'ここで何らかの処理
      End If
   Next m

   dt2 = CDbl(Timer)
   Range("E1") = cnt
   Range("E2") = dt2 - dt1
End Sub

まず、最初にB列のデータを１つの文字列としてに連結しています。あとはA列のみをループして連結文字の中に存在するかどうかを確認するわけです。

結果は以下の通り、Test1の時よりも大幅に時間短縮できました。0.05秒ならまばたきするうちに終わります。