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

2021/11/30 2021/12/02

以前、Excelのシート内検索を高速化させるために関数との組み合わせについて書きました。これが一番速いと思っていたのですが関数との組み合わせという点がちょっと引っかかっていました。

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

ところが100%VBAで同じくらい高速検索ができることがわかり今回紹介します。

使ったデータは郵便局サイトの郵便番号ダウンロードページから全国データをもらって使いました。124,500行もの大量データなのでこのようなテストをするときに最適です。

1 ソースコード
2 実行結果
3 解説

ソースコード

ソースコードはこちらです。2行目のFindメソッドを使った行が高速検索のコードになります。C列の郵便番号から7320009を検索します。詳細の解説は後述します。

Dim c As Range
Set c = Range("C:C").Find(What:="7320009", LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox c(1, 5) & c(1, 6) & c(1, 7)
  MsgBox c.Offset(0, 4) & c.Offset(0, 5) & c.Offset(0, 6)
  c.EntireRow.Select
Else
  MsgBox "見つかりません"
End If

実行結果

上記を実行すると、まず以下のようなメッセージボクスが２回現れます。郵便番号7320009の住所ですね。ちゃんと検索されたことが確認できるかと思います。なぜ２回メッセージボックスを出したかと言うと２通りのやり方があるからです。

最後にその箇所を選択するようにもしました。

解説

それでは解説です。

まずFindメソッドを使った検索は以下のとおりです。Whatで検索キーワードを指定し、LookAt=xlWholeで完全一致を示しています。ちなみに部分一致で検索したい場合はxlPartを指定します。

Set c = Range("C:C").Find(What:="7320009", LookAt:=xlWhole)

この行で検索ヒットしかたどうかを判別します。

If Not c Is Nothing Then

次にメッセージボックスですが、以下の１行目はC列を1列目としてカウントするのでG,H,I列を連結した文字列を表示させます。2行目のOffsetはC列を0としているので同じくG,H,I列を連結した文字列となります。どちらか好きな方を使えばよろしいかと。

MsgBox c(1, 5) & c(1, 6) & c(1, 7)
MsgBox c.Offset(0, 4) & c.Offset(0, 5) & c.Offset(0, 6)

最後に行を選択状態にする方法は以下の通りです。EntireRowは行全体を示します。ちなみにc.EntireRow.Deleteとやると行ごと削除されます。

c.EntireRow.Select

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

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

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

前回、Findメソッドを使った超高速検索方法について書きましたが、ワークシート関数利用版とどっちが速いか？という質問をもらったので速度勝負してみました。ワークシート関数利用版というのはこっちの方です。使ったデータは上記記事と同じく郵便局サイトの郵便番号ダウンロードページから全国データをもらって使いました。124,500行もの大量データの中から検索させます。検索する郵便番号はランダムに40個選びました。（北海道は頭に０が入るのですがExcel上では数値として認識され０がなくなってしまうので検索ワード...

- Excel, VBA