ExcelVBAで検索速度勝負！Findメソッド vs ワークシート関数利用

前回、Findメソッドを使った超高速検索方法について書きましたが、ワークシート関数利用版とどっちが速いか？という質問をもらったので速度勝負してみました。

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

ExcelVBAでFindメソッドを使って超高速検索方法

以前、Excelのシート内検索を高速化させるために関数との組み合わせについて書きました。これが一番速いと思っていたのですが関数との組み合わせという点がちょっと引っかかっていました。ところが100%VBAで同じくらい高速検索ができることがわかり今回紹介します。使ったデータは郵便局サイトの郵便番号ダウンロードページから全国データをもらって使いました。124,500行もの大量データなのでこのようなテストをするときに最適です。 ソースコードソースコードはこちらです。2行目のFindメソッドを使った行が高速検索のコードに...

ワークシート関数利用版というのはこっちの方です。

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

 

3 Users

5 Pockets

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

仕事でExcelマクロはよく使っています。そんな中、一番苦労するのが、シート内の膨大なデータから特定の文字列を検索するときです。例えばデータ数が１万行もあれば、検索にすごく時間がかかってしまいます。でも、仕事ではシート内に列挙された何千行、何万行のデータ行から特定のキーワードを検索するのって、Excelマクロで一番使われるんじゃないでしょうか。データ量が多いほど時間がかかります。１万行もあったりすると結構しんどいです。今回はこれを超高速にする方法を紹介します。まずは一般的なやり方まず、下の図は郵便番号...

使ったデータは上記記事と同じく郵便局サイトの郵便番号ダウンロードページから全国データをもらって使いました。124,500行もの大量データの中から検索させます。

検索する郵便番号はランダムに40個選びました。（北海道は頭に０が入るのですがExcel上では数値として認識され０がなくなってしまうので検索ワードも先頭０なしでやりました。）

これを以下のようにzip配列変数に格納してループで順次検索させました。

Dim zip As Variant
zip = Array(420941, 981821, 9896801, 9998522, 3702461, 2700222, 2208121, 9591851, 9231269, 5030878, _
            4170844, 4430011, 6048004, 6128477, 6712132, 6392212, 7011527, 7911501, 8200067, 8720345, _
            660005, 891330, 101606, 9637858, 3702343, 3400024, 2520173, 9593444, 9203126, 5061317, _
            4380086, 4470866, 5290251, 5406019, 6530877, 6441142, 7390517, 7981354, 8595101, 8780146)
 

それでは速度勝負

Findメソッド版

まずFindメソッド版です。以下のようなコードでやりました。出力はセルだと速度ロスが起きるのでイミディエイトウィンドウに表示させます。

Dim i As Long
Dim d1, d2 As Double
Dim zip As Variant

d1 = Timer()
zip = Array(420941, 981821, 9896801, 9998522, 3702461, 2700222, 2208121, 9591851, 9231269, 5030878, _
            4170844, 4430011, 6048004, 6128477, 6712132, 6392212, 7011527, 7911501, 8200067, 8720345, _
            660005, 891330, 101606, 9637858, 3702343, 3400024, 2520173, 9593444, 9203126, 5061317, _
            4380086, 4470866, 5290251, 5406019, 6530877, 6441142, 7390517, 7981354, 8595101, 8780146)

Dim c As Range
For i = 0 To UBound(zip)
  Set c = Sheets("KEN_ALL").Range("C:C").Find(What:=zip(i), LookAt:=xlWhole)
  If Not c Is Nothing Then
    Debug.Print c(1, 5) & c(1, 6) & c(1, 7)
  Else
    Debug.Print "見つかりません"
  End If
Next
d2 = Timer()
Debug.Print (d2 - d1)
 

結果はご覧の通り。かかった時間は2.296875秒です。

ワークシート関数利用版

次にMatch関数利用版です。セルQ1に郵便番号を入力し、R1, S1, T1セルに、=INDIRECT(ADDESS(MATCH(Q1,C:C,0), 7)))という関数を仕込んでおきます。その結果を読んでイミディエイトウィンドウに出力させます。

Dim i As Long
Dim d1, d2 As Double

d1 = Timer()
For i = 0 To UBound(zip)
  Sheets("KEN_ALL").Range("Q1").Value = zip(i)
  Debug.Print Sheets("KEN_ALL").Range("R1").Value & Sheets("KEN_ALL").Range("S1").Value & Sheets("KEN_ALL").Range("T1").Value
Next

d2 = Timer()

Debug.Print (d2 - d1)
 

結果はこちら。なんと0.453125秒！ めっちゃ速い！

ワークシート関数利用版の方がセルへの読み書きが発生するので絶対に遅いと思ったのですが結果は逆でした。まさかの5倍速。

 - Excel, VBA