Excelマクロでシート内検索を超高速にする方法

仕事でExcelマクロはよく使っています。

そんな中、一番苦労するのが、シート内の膨大なデータから特定の文字列を検索するときです。例えばデータ数が１万行もあれば、検索にすごく時間がかかってしまいます。

でも、仕事ではシート内に列挙された何千行、何万行のデータ行から特定のキーワードを検索するのって、Excelマクロで一番使われるんじゃないでしょうか。データ量が多いほど時間がかかります。１万行もあったりすると結構しんどいです。

今回はこれを超高速にする方法を紹介します。

まずは一般的なやり方

まず、下の図は郵便番号を検索するためのExcelシートですが、上段の表が検索したい郵便番号。とりあえず10個用意しました。下段の表は全国の郵便番号と住所の対応表です。郵便番号と住所の全データは郵便局のWebページからダウンロードできます。全国データは124,115データもあるので、検索には時間がかかります。

 

 

任意の10データを下段の一覧表から検索する場合、一般的にはループを使います。以下がその例です。最後にC1セルに経過時間を記録するようになっています。

Sub 検索1()
  t1 = Timer()

  For r = 4 To 10
    r2 = 14
    Do While Cells(r2, 1) <> ""
      If Cells(r2, 1) = Cells(r, 1) Then
        Cells(r, 2) = Cells(r2, 2)
        Cells(r, 3) = Cells(r2, 3)
        Cells(r, 4) = Cells(r2, 4)
        Exit Do
      End If
      r2 = r2 + 1
    Loop
  Next r

  t2 = Timer()
  Range("C1") = t2 - t1
End Sub
 

 

これを実行するとどれくらい時間がかかるかというと、16.5秒かかりました。処理中、もしかしてフリーズしちゃったかな？って勘違いするレベルです。

 

高速化するコツは関数との組み合わせ

これをどうやって高速化するかと言うと、Excel関数と組み合わせると効果的なんです。Excel関数に便利なMatch関数というものがありまして、これを使うと一瞬で一致した先の行No.を表示してくれます。

これが、Match関数を使ったシートの例です。C16セルに検索したい郵便番号を入力すると、D16セルにその郵便番号が書かれた行No.を表示してくれるようになっています。

D16セルの計算式は「=MATCH(C12,A14:A124128,0)+13」と入っています。

C12は検索したい郵便番号の場所です。A14:A124128は検索範囲。これだけだと、行Noではなく検索範囲内での相対位置（つまり、A14から何行目か）が表示されますので、検索範囲は13行目からスタートしていますから、ゲタとして13を足し算します。これで対象の郵便番号が記載されている行No.がゲットできます。

要はマクロの中で、C16に郵便番号を入力し、その後D16の値をゲットすれば高速で住所を検索できるってわけです。これを利用したプログラム例がこちら。

Sub 検索2()
  t1 = Timer()

  For r = 4 To 10
    Range("C12") = Cells(r, 1)
    r2 = Range("D12")
    Cells(r, 2) = Cells(r2, 2)
    Cells(r, 3) = Cells(r2, 3)
    Cells(r, 4) = Cells(r2, 4)
  Next r

  t2 = Timer()
  Range("C1") = t2 - t1
End Sub
 

 

このプログラムを走らせると、なんと0.04秒で完了。超早です。

 

今回に限らず、Excel関数とマクロとを組み合わせることによって高速化できる可能性があります。ただ、欠点として、不特定多数の人間で使用するようなケースだと、セル内の計算式を消されてしまう危険があるという点です。シートの保護機能を使って編集できないようにすることもできますが、僕はプログラムの最初に計算式を入力するようにしています。

今回の例だと、プログラムの最初に、

Range(“D12”) = “=MATCH(C12,A14:A124128,0)+13”

を入れておけば、消されても何度も復活します。後になってどんな計算式か忘れてしまうものなので、それを記録しておくという意味でも、この方法を使っています。

 - VBA