知らずに落ちると抜け出せなくなるVBAの落とし穴です。
- Findメソッドがうまくセルを見つけてくれない
- Findメソッドの検索オプションが勝手に変わる
- Findメソッドを使用したマクロが遅い
あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。
セル範囲の検索に用いられることの多いFindメソッドさんですが、
実は「危ない＆遅い」の2大プログラミング罠要素を兼ね添えた、
結構な地雷メソッドです。
罠の内容を簡単に説明すると、
- オートフィルターによる非表示セルを見つけられない
- ユーザーと設定(完全/部分一致など)を共有するため、
直前にユーザーが行った操作によって挙動が変わる - MATCH関数やVLOOKUP関数より十数倍レベルで遅い
ざっとこんな感じで、1個でも困る罠要素をなんと3つも持っています。
罠が多すぎて記事タイトルの文字数制限にかかってしまったので、
メソッドを消して呼び捨てにしました。
先に解決策だけ記しておきますと、
そもそも「ひとつの列を検索する」のにFindメソッドを使用してはいけません。
この表のA列からぶどうを探すときなどですね↓
このような表形式のデータで検索を行う場合は、MATCHやVLOOKUPなど、
専用のシート関数がはるかに高速でしかも安全に検索を行ってくれます。
Findメソッドは、
ここからレモンを探すときなど、行/列データでないセル範囲からの検索を、
さっと書きたいときにのみ使用するものです。
それを踏まえたうえで、罠の説明を読み進めてください。
Findメソッドはオートフィルターによる非表示セルを見つけられない
まず最初の罠はこちらです。
オートフィルターによって非表示になっている行を、
Findメソッドは見つけることができません。
「オートフィルターによって」というのがミソで、
ただ非表示にしているセルは発見してくれます。
Findメソッドは「シート上でCtrl+Fで開く検索ダイアログ」ですので、
試しにやってみてください。
オートフィルターに弱いというのは曲者で、
- マクロを書いているときは正常に動く
- マクロを渡した相手がフィルターを使った後実行するとバグる
ということが往々にしてあります。
オートフィルターに弱いコードは、マクロ作成時には気づきにくいバグを生みやすいです。
同じくオートフィルターの罠にかかりやすい、
Endプロパティによる最終行取得とセットで気を付けましょう。
Findメソッドはユーザーと検索オプションを共有する
次の罠がこちらです。
前述の通り、Findメソッドは「検索ダイアログボックス」ですので、
↑これのことです。
この検索と置換ダイアログボックスは、「前回のオプション設定を引き継ぐ」という特徴があるのですが、この特徴はマクロ・ユーザー操作で共有しています。
よって、オプションの指定を全く行わない、
Set 発見セル = セル範囲.Find("みかん")
このコードは、最後にユーザーが行った検索に依存した動きをする、
完全一致/部分一致でさえどちらで実行されるかわからないコードです。
なので、本当に頑強なコードを書こうと思ったら、
この長ーい引数たちを軒並み指定してあげないといけません。
うわめんどくさっ
それはさすがに面倒なので、LookAtの部分一致/完全一致だけ指定して、
使い捨てマクロでさっと書くことはあります。
が、それをユーザーに配布する場合はいつか何か起きるものと覚悟してください。
とくにLookInも曲者で、これは検索対象の「値/数式」を決めるオプションですが、
値にすると、値と言っているのに表示テキストを検索しやがります。
「1,000」という値が1000のセルを、Find(1000)が見つけてくれなくなります。
LookIn:=xlValues というのは大嘘で、実際は LookIn:=xlTexts みたいなもの。
ということも立派な罠なので覚えておきましょう。
FindメソッドはMATCH・VLOOKUP関数より十数倍遅い
最後の罠ですが、Findメソッドは対抗馬のMATCH関数と比べて十数倍遅いです。
状況によっては数十倍以上の差が付きます。
ランダムに生成した10000個の数値(10000～99999)から、
同じくランダムに生成した10000個の数値の位置を検索してみましょう。
↓こんなシートでD列を計算させます。
コードはこんな感じで、コメントアウト部分以外は全部一緒↓
Sub FindメソッドvsMATCH関数() Application.ScreenUpdating = False Dim 開始 As Double: 開始 = Timer Dim R As Long For R = 2 To 10001 On Error Resume Next ' ↓どちらかのコメントを解いて実行 ' Cells(R, 4) = WorksheetFunction.Match(Cells(R, 3), Columns(1), 0) ' Cells(R, 4) = Columns(1).Find(Cells(R, 3), lookat:=xlWhole).Row Next Debug.Print Timer - 開始 Application.ScreenUpdating = True End Sub
コメントアウトを片方ずつ解いて勝負すると・・・
| 処理方法 | 所要時間 |
|---|---|
| MATCH関数 | 1.13秒 |
| Findメソッド | 32.52秒 |
MATCH速っ！！
これでは勝負になりませんね。
VLOOKUP関数でも大体同じ結果になります。
シート関数はExcelのバージョンが上がるほど高速なので、
最新のExcelだともっと差がつくかもしれません。
逆にExcel2010とかだともう少し縮まるかもしれませんが、
それでも10倍は軽く差が出てしまうと思われます。
ここまで差があると、比較してどうのという問題ではありません。
Findメソッドが安全だったとしても使用に値しないレベルです。
そしてもっと重要なのが、
MATCH関数はWorksheetFunctionで呼ぶよりも、
Formulaプロパティを使ってセルに直接入れた方が速い
ということです。
コードはこんな感じ↓
Sub 最強はMATCH関数をFormulaで() Application.ScreenUpdating = False Dim 開始 As Double: 開始 = Timer Range("D2:D10001").Formula = "=IFERROR(MATCH(C2,A:A,0),"""")" Range("D2:D10001").Value = Range("D2:D10001").Value Debug.Print Timer - 開始 Application.ScreenUpdating = True End Sub
普通にD列に数式を入れて、シート上で計算しているコードです。
IFERRORでエラーを分岐し、
計算が終わったあとは値貼り付け
というハンデを背負っているように見えますが、、
| 処理方法 | 所要時間 |
|---|---|
| .Formula = "=MATCH" | 0.12秒 |
| WorksheetFunction.Match | 1.13秒 |
| Findメソッド | 32.52秒 |
Formula速っ！！
Findメソッドとは300倍とかいう馬鹿げた差になり、
WorksheetFunctionすら鼻で笑える10倍です。
しかもコードが一番短くて簡単というおまけつき。
セルに普通に数式を入力して、それをコピペしてくればOKだし、
なんならセルに数式打った時点で、第1レコードでテストができちゃっています。
このFormulaとWorksheetFunctionの速度差は、
計算速度というよりはセルへの入力回数差です。
For R = 2 To 10001 Cells(R, 1) = 1 Next Range("A2:A10001") = 1
この2つのコードでも同様に発生する差です。
セルへのアクセスは規模より回数が大事なので、
Range("D2:D10001").Formula = "=IFERROR(MATCH(C2,A:A,0),"""")"
と、Rangeオブジェクトへのアクセスが1回で済むことで超速になっています。
この「Rangeオブジェクトへのアクセスを1回で済ます」というのは、
「配列を使った高速化」と同じ理屈です。
この手法では、配列と同等の速度が得られます。
※ シート関数にはさらに「広範囲を同時に計算したときの高速化」が入るので、
配列＋WorksheetFunction で処理したときよりさらに高速なことも多いです。
これはもちろんMATCH関数だけの話ではなく、
四則演算だろうとWorksheetFunctionよりもFormulaの方が超速です。
配列クラスの高速化ができる上に、
小難しい配列とはケタ違いに簡単なコードで実装できます。
Findメソッドの考察なんかもはやどうでもいいので、
今日このページからは「Formulaプロパティは超速だ」という情報を持ち帰ってください。
詳しくはこちらの記事をどうぞ
まとめ
以上でFindメソッドの罠の解説を終わります。
- オートフィルターによる非表示セルを見つけられない
- ユーザーと設定(完全/部分一致など)を共有するため、
直前にユーザーが行った操作によって挙動が変わる - MATCH関数やVLOOKUP関数より十数倍レベルで遅い
この3つが気を付けるべき罠でしたね。
Findメソッドは基本使用しないメソッドと思っておいて問題ありません。
検索をマクロ記録するとFindメソッドが記録されてしまうためか、
Findメソッドさんは無駄に有名で、既存コードに多数組み込まれています。
私もこの事実を知るまではFind使いまくりでした。
そんなコード見つけたら、そっとMATCHに直してあげてください。
そんなことより、このMATCH関数(やVLOOKUP関数)を使用する際は、
WorksheetFunctionではなくFormulaで実装すると超速だということの方が情報としては重要です。
WorksheetFunctionとは数倍～数十倍、
Findメソッドとは100倍以上の差が出ますので、
是非とも活用してください。
