EXCELでVBAを使わずにシート内を高速検索する方法

以前、Excelマクロでシート内検索を超高速にする方法として、VBAにMATCH関数を組み合わせることで検索速度が爆速になることを書きました。

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

 

3 Users

5 Pockets

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

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

この高速検索を可能にしていのるがMATCH関数なのですが、このMATCH関数を含めた３つの関数だけで高速検索する方法について紹介したいと思います。

なので、今回はVBAは使いません。

[ad#top-1]

まずはMATCH関数のおさらい

前回の高速検索に登場したMATCH関数ですが、以下の住所録の例の通り、A2セルに書かれている郵便番号をキーに12万データのどこに存在するか一瞬で探すことができます。

解説は以下の通りで、第一引数のA2は検索値、探したいキーワードです。第二引数が検索範囲で、今回はA4～A124118までが探す範囲となります。第三引数の0は完全一致を意味します。

返り値は、行番号ではなく「A4から何個目か」が返ってくるので、それを行番号にするために最後に下駄分として３を足しています。

この式をA3セルに入力すれば、A2に入力した郵便番号が何行目に存在するかが表示されるわけです。

行列番号からセルの参照アドレスを返すADDRESS関数

次にADDRESS関数です。

こちらは単純な関数で、行番号と列番号から、セルの参照アドレスを返す関数です。以下の例の通り、３行目、２列目と指定すれば、「$B$4」と帰ってくるわけです。

これを前出のMATCH関数と組み合わせると、こんな感じです。

これを先ほどのB2セルに入れると以下の通りです。検索した行番号からB列のセル参照アドレスが表示されました。

セルの参照アドレスからセルの値を返すINDIRECT関数

最後にINDIRECT関数です。

これは引数の示すセル参照アドレスから、そのセルに入力されている値を返します。

もし、B4セルに「TEST」という文字列が入っていたら、そのまま「TEST」が表示されるだけです。「=B4」と結果は同じです。

こいつを前出のADDRSS関数と組み合わせるとこうなります。

これを先ほどのB2セルに入れると以下の通りです。A2に示した郵便番号から、その郵便番号に該当する県名（B列）が表示されるようになりました。

これをC列（市町村名）、D列（住所）についても設定すれば以下のように、郵便番号をA2列に入力するだけで、該当する県名、市町村名、住所が一瞬で現れるようになります。

VBAを使うよりシンプルで速いです。ケースバイケースですが、関数のみで解決できるものは関数で処理した方が基本的に高速です。

[ad#ad-1]

 - Excel