非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

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

      2017/02/15

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

[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

 

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

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

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

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

 

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

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

処理時間(秒) 処理回数(回)
0.05 3,792

 

比較すると以下の通りです。99.7%も処理時間が短縮できています。

マクロ名 処理時間(秒) 処理回数(回)
Test1 18.88 1,798,356
Test2 0.05 3,792

 

Excelはループ内にセルの参照があると極端に速度が落ちます。そのため色々な手段を使って速度アップさせる工夫が必要になります。今回の例が使えるかどうかはケースバイケースですが、このようなテクニックもあるということを覚えておくとよいでしょう。

参考までにこちらの記事もどうぞ

[ad#ad-1]

スポンサーリンク

 - Excel, VBA