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]
スポンサーリンク