1次元配列を検索する方法4選!
VBAといえば、エクセル。エクセルといえば、セル。
『どのセルを操作するか』を判断することは、自動化を目的としたプログラムの性能を上げるために、最も重要視すべき点のひとつであるといえます。
どのセルかを判断するには、『検索』をします。
検索の方法は、大きく分けて、以下の4つの方法があります。
- セルをループさせる(For)
- セルをループさせる(For Each)
- Findメソッドを使用する
- ワークシート関数(VLookup関数、HLOOKUP関数)を使用する
それでは、これら4つの方法について、性能の検証をしてみましょう。
セルのループ(For) サンプルプログラム
Exit For
Range.Cellsを、決められた回数分ループする方法です。
VBAプログラムの中でも、最も基本的なロジックのひとつです。
似た構文にDo Loop構文がありますが、やっていることは同じなので、ここでは割愛します。
セルのループ(For Each) サンプルプログラム
Exit For
Rangeオブジェクトの数だけループする方法です。
配列の要素数だけループし、セルの場合、列行の順でループをします。
例:Range("A1:B2")の場合
1回目のループ:A1を参照する
2回目のループ:B1を参照する
3回目のループ:A2を参照する
4回目のループ:B2を参照する
Findメソッド サンプルプログラム
Range.Findメソッドを使用した検索方法です。
セル範囲のうち、Findメソッドの引数に指定した条件のセルを返却します。
検索条件に合致したセルが存在しない場合は、Nothingを返却します。
VLOOKUP関数、HLOOKUP関数 サンプルプログラム
ワークシート関数であるVLOOKUP関数またはHLOOKUP関数を使用した検索方法です。
WorksheetFunctionオブジェクトのメンバで、引数は数式のそれと同様です。
ここでは、VLOOKUP関数のサンプルを提示します。
検証
今回の検証は、セル範囲[A:A]に1~65536を入力し、検索する文字は乱数を発生させ、
セルを検索するものとします。それぞれ4つの方法についての実行結果は、以下の通りです。
回数 | For | For Each | Findメソッド | ワークシート関数 |
---|---|---|---|---|
1回目 | 0.359 | 0.219 | 0.630 | 0.000 |
2回目 | 0.391 | 0.375 | 0.470 | 0.000 |
3回目 | 0.203 | 0.359 | 0.000 | 0.160 |
4回目 | 0.203 | 0.172 | 0.160 | 0.150 |
5回目 | 0.610 | 0.422 | 0.470 | 0.000 |
6回目 | 0.016 | 0.468 | 0.470 | 0.000 |
7回目 | 0.516 | 0.015 | 0.160 | 0.000 |
8回目 | 0.641 | 0.516 | 0.160 | 0.000 |
9回目 | 0.468 | 0.172 | 0.620 | 0.000 |
10回目 | 0.031 | 0.234 | 0.620 | 0.000 |
平均 | 0.344 | 0.295 | 0.376 | 0.031 |
※単位は全て『秒』
この結果より、VLOOKUP関数が圧倒的に速いことがわかります。
ただ、1点注意しなければならないのが、VLOOKUP関数の使用条件に気をつけなければいけないことです。
VLOOKUP関数はもともとワークシート関数、つまりVBAのためではなくエクセルのためにある機能です。
あくまで、セルの値を返す関数であり、セルのアドレスを返す関数ではないため、使い勝手を良くするためには一工夫必要です。
しかもデータが昇順で並んでいないと検索が不安定であることより、ある特定の条件のもと効力を発揮する検索方法であると言えます。
さらに、これが1次元配列の場合であるということに注意しなければなりません。
2次元配列の場合は、結果が大きく変わります。
セルは、行×列の2次元配列ですので、2次元配列での検証をする必要があります。
2次元配列での検証結果は、こちらを参照ください。