セル検索の速度比較 - Find/Match/Dictionary

マクロを作るとかなりの頻度で行うことになる「セルの検索」ですが、
これを実行するには以下の手段が挙げられます。

• 検索範囲の全セルを愚直にFor文で判定
• Range.Findメソッド
• WorksheetFunction.Match
• セル範囲.Formula = "=MATCH"
• 第1セル.Formula2 = "=スピルするMATCH"
• Dictionary(連想配列)オブジェクト
• 自作したバイナリサーチ

この中でどれが早いかを検証してみましょう。

お忙しい方向けに結論を先に述べますと、
1万件からの検索を1万回行った結果がこちらでした。

検索方法	セル直接参照	読取を配列	出力を配列	どちらも配列
愚直にFor文	109.2秒	2.748秒	109.1秒	1.796秒
Range.Find	23.59秒
WF.Match	1.003秒	11.56秒	0.084秒	0.053秒
Formula "MATCH"	0.073秒
Formula "XMATCH"	0.117秒
Formula2 "MATCH"	0.034秒
Dictionary	0.975秒		0.065秒	0.031秒
自作バイナリサーチ				0.047秒

考察としては、

• 速度/記述コスト/汎用性のバランスが良いWorksheetFunction.Match
• 超速かつ低コストだがメンテ性に注意が必要なFormula/Formula2
• コーディング難易度が上がるが超速で汎用性も高いDictionary

これらを状況に応じて使い分けるのがよいという結論になります。

付け加えると、Findメソッドは様々な罠があるにもかかわらず普通に遅いので、
Range.Findは基本的に使ってはいけないというのも覚えておいてください。

以降、検証コードと考察の詳細を述べていきますので、
興味がある方はこのまま読み進めてみてください。

検証の仕様

画像のようなシートで、C列の値をA列から検索し、
発見した行番号をD列に出力します。

簡単に言えば、
10000件から1件を探す検索処理を10000回実行するのにかかる時間
を測定することになります。

なおB列のデータには「95%の確率でA列に存在する」データを用いており、
20データにつき1回ほどA列に存在しないデータが混じります。

計測には以下のコードを用いました。

Dim 開始時刻 As Double, 終了時刻 As Double
開始時刻 = Timer

' ここに各処理を記載する

終了時刻 = Timer
Debug.Print "このマクロの実行時間：" & (終了時刻 - 開始時刻) & "秒"

配列の使用について

今回のマクロは検索方法の違い以外に、
セルの読込/出力に配列を使うかどうかにも左右されます。

よって、

• 画像のシートをまるごと配列に入れて、以降はそれを読み込んで使う
• 結果を一旦配列に入れておき、最後に一括でセルに出力する

この2つの方法を用いた場合の違いも記載しました。

今回は配列を使うことで、
読込では10000回ごとに0.03秒ほど
出力では10000回ごとに0.9秒ほど
を短縮することができました。

この短縮効果は対象ファイルの内容にかなり左右されますのでご注意ください。

今回は発見した行番号だけを読み取っていますが、
そのあとマスタから10列分を読み取れば当然10倍の時間がかかります。

出力についてはもっと差が出る部分で、
重い数式の再計算が入る場合は数百倍、
さらに条件付き書式などがあると数千倍になることもあり得ます。

いずれにせよ読取より出力の方が時間がかかるため、
配列の速度効果は出力処理の方が大きいと認識しておいてください。

それではコードと結果の解説に入ります。

ソースコード

今回の全ソースコードと使用したサンプルデータがこちらになります。

セル検索速度検証ソース(和風スパ).xlsm(ダウンロードリンク)

全パターンで配列のありなしを分けて検証していますが、
本記事に全ソースを貼ると長大で読みづらくなるため、
記事内では「配列なし」のソースだけを掲載します。

配列ありを含めた全ソースコードを確認したり、他の検証をやってみたい方は、
上記のサンプルファイルをダウンロードしてお使いください。

検索範囲の全セルを愚直にFor文で判定

ソースコード

Dim R As Long
For R = 2 To 10001
    
    Dim 検索値 As Long: 検索値 = WSテスト.Cells(R, 3)
    
    Dim R_検索行 As Long
    For R_検索行 = 2 To 10001
    
        If WSテスト.Cells(R_検索行, 1) = 検索値 Then
            WSテスト.Cells(R, 4) = R_検索行
            Exit For
        End If
    
    Next
    
Next

結果

セルを直接参照	109.2秒
読取を配列化	2.748秒
出力を配列化	109.1秒
どちらも配列化	2.729秒
描画を停止	109.2秒

考察

特筆すべきは「読取を配列化しないときの遅さ」で100秒以上かかっています。

これは「愚直にFor文を使うと1検索ごとに平均5000回読み取る」ことが原因ですね。

通常は

• 出力は配列化するとかなり高速化する
• 読取は配列化してもそこまで高速化しない

ことが多いですが、流石に膨大な読取を行う場合は話が変わるということです。

この方法は他の方法と比較する基準としてやってみましたが、
普通に遅いのでわざわざこの方法で実装する必要はありません。

ちなみに

Application.ScreenUpdating = False

も試してみましたが、ほとんど効果はなかったです。

これはSelectを使ったマクロ記録マクロを高速化できるだけで、
検索コードの高速化にはあまり役に立たないというのも知っておいてください。

Range.Findメソッド

ソースコード

Dim 検索範囲 As Range
Set 検索範囲 = WSテスト.Range("A2:A10001")

Dim R As Long
For R = 2 To 10001
    
    Dim 検索値 As Long: 検索値 = WSテスト.Cells(R, 3)
    
    Dim 発見セル As Range
    Set 発見セル = 検索範囲.Find(検索値, lookat:=xlWhole)
    
    If Not 発見セル Is Nothing Then
    
        WSテスト.Cells(R, 4) = 発見セル.Row
    
    End If
    
Next

結果

セルを直接参照

23.59秒

考察

For文で全セルを見る処理の10倍時間がかかるくらい相当遅いです。

配列を使わないWorksheetFunction.MatchですらFindの20倍速ですので、
他の方法とは比較できないレベルで遅いですね。

しかもFindメソッドには、

• オートフィルターによる非表示セルを見つけられない
• ユーザーと設定(完全/部分一致など)を共有するため、
• 直前にユーザーが行った操作によって挙動が変わる

という厄介な罠使用を持っています。

割と有名で使われているマクロもよく見るFindメソッドですが、
遅くて不安定でユーザー操作に影響されるという三重苦メソッドですので、
基本的には使用しないものだと思ってください。

WorksheetFunction.Match

ソースコード

Dim 検索範囲 As Range
Set 検索範囲 = WSテスト.Range("A2:A10001")

Dim R As Long
For R = 2 To 10001
    
    Dim 検索値 As Long: 検索値 = WSテスト.Cells(R, 3)
    
    On Error Resume Next
    Dim 発見行 As Long: 発見行 = 0
    発見行 = WorksheetFunction.Match(検索値, 検索範囲)
    On Error GoTo 0
    
    If 発見行 > 0 Then
        WSテスト.Cells(R, 4) = 発見行
    End If

Next

結果

セル直接参照	1.003秒
読取を配列	11.56秒
出力を配列	0.084秒
どちらも配列	0.053秒

考察

ExcelVBAにおけるセル検索の本命はこれだと思います。

10000件で1秒ほど、配列を使えば0.1秒かかりません。

しかも見つかった行番号をそのままLong型として取得できるため、
そのあとのコーディングもやりやすく、単体実行にも向いています。

OnError処理を使用する必要があるところが欠点ですが、
速度・汎用性・コーディングコストどれも高レベルでバランスがよいです。

基本的にこの方法を使っておけば間違いはないと思います。

面白いのは「読取を配列化すると11秒とかなり遅くなる」点で、
シート関数はRangeに対して実行した方が性能が良いみたいですね。

なので「検索範囲はRangeのまま、検索値だけを配列に」するのが最速でした。

' ↓このコードは検索値を配列からとると高速化するが検索範囲を配列化すると遅くなる
発見行 = WorksheetFunction.Match(検索値, 検索範囲)

「どちらも配列」が早くなっているのはこの検索値だけを配列化したためです。

この仕様は覚えておきましょう。

なお、Match関数を使う方法にはWorksheetFunctionの他に、
Applicationから直接呼ぶ裏技的な方法があります。

Dim 検索値 As Long: 検索値 = WSテスト.Cells(R, 3)

' On Error Resume Nextは不要
Dim 発見行 As Variant ' ←ここがVariantになる
発見行 = Application.Match(検索値, 検索範囲)

If IsError(発見行) > 0 Then ' ← IsError関数が使える
    WSテスト.Cells(R, 4) = 発見行
End If

　
WorksheetFunctionとの違いは「エラー値が返る」ところで、
受ける変数をVariantにしておくことで実行時エラーを発生させず、
発生したエラーをエラー値として変数に格納することができます。

このエラーはIsError関数で判定することができるため、
On Error Resume Nextによるエラー処理が不要になります。

この方法でも速度を測定してみましたが、
ほとんど変わらない実行時間になりました。

Application.Matchも問題なく使用できますので、
お好きな方をお使いください。

セル範囲.Formula = "=MATCH"

ソースコード

Dim 計算範囲 As Range
Set 計算範囲 = WSテスト.Range("D2:D10001")

計算範囲.Formula = "=MATCH(C2,A:A,0)"
計算範囲.Value = 計算範囲.Value

結果

Formula"MATCH"	0.073秒
Formula"XMATCH"	0.117秒

考察

続いても非常に強力な手法、Formulaプロパティによるシート上での処理です。

なんといってもコーディングコストの低さが素晴らしく、
たったこれだけのコードで実装ができてしまいます。

For文すら書かなくてもよい簡単さに加え、
Rangeのアクセス回数が数回なため配列を使う必要もありません。

この「配列による高速化の知識がなくても実装できる」のも強みですね。

加えて強力なのが「テストが容易」な点で、
マクロを実行しなくても、数式を実際にセルに入れてみることでテストできます。

最後の.Value = .Valueをコメントアウトすることでもテストができますので、
この手法はプログラムテストとの相性がとても良いのが強みですね。

それでいて速度も超速で、
配列使用のWorksheetFunctionとほぼ同速のスピードが出ます。

簡単かつ超速と、相当強力な手法になります。

是非とも使っていきましょう。

欠点は「定数と相性が悪いこと」で、

計算範囲.Formula = "=MATCH(C2,A:A,0)"

このコードのマジックナンバー「C2」や「A:A」を定数化するのが困難です。

これをわざわざ定数化するとせっかくのコーディングコストが台無しになりますので、

Enum 列_売上データ
    購入日 = 1
    単価
    個数
    金額
End Enum
' マジックナンバー数式あり(※1)

計算範囲.Formula = "=MATCH(C2,A:A,0)" ' (※1)

こんな風に宣言部に定数化できていない数式がある旨をコメントしておき、
改修時は数式を1から書き直す運用にするのがおすすめです。

もう一つの欠点は「内部計算に使えないこと」で、
結果をセルではなく変数に一旦ほしい場合は使えません。

この場合はどうしようもありませんので、
素直にWorksheetFunction.Matchの方で実装しましょう。

余談ですが、意外にもXMATCH関数に変えると遅くなるようです。

高機能な分単純な検索は微妙に遅くなっているということなのでしょうか。

互換性の面含め、MATCH関数を使っておけばいいみたいですね。

第1セル.Formula2 = "=スピルするMATCH"

ソースコード

WSテスト.Range("D2").Formula2 = "=MATCH(C2:C10001,A:A,0)"
WSテスト.Range("D2#").Value = WSテスト.Range("D2#").Value

結果

セル直接参照

0.034秒

考察

セル範囲全体のFormulaプロパティに数式を代入するのではなく、
第1セルのFormula2プロパティにスピルする数式を代入する方法です。

Excelには同一数式が高速計算される特長がありますが、
スピル式の場合はその効果がより高まるのか、Formulaより高速でした。

この方法がスピード以上に強力なのは、

WSテスト.Range("D2#")

このコードで動的なスピル範囲を取得できることですね。

UNIQUEやFILTERなど返り値の大きさが動的な処理を行う場合は、
MATCH以上にコーディングコストの削減効果が大きいと思います。

欠点は当然ながらスピル対応でないバージョンのExcelでは動かないことです。

この点に注意しつつ積極的に使用していきましょう。

Dictionary(連想配列)オブジェクト

ソースコード

Dim Dic検索リスト As New Dictionary

Dim R As Long
For R = 2 To 10001
    
    Dim key As Long: key = WSテスト.Cells(R, 1)
    If Dic検索リスト.Exists(key) = False Then
        Dic検索リスト.Add key, R
    End If

Next

For R = 2 To 10001
    
    WSテスト.Cells(R, 4) = Dic検索リスト(WSテスト.Cells(R, 3).Value)

Next

結果

セル直接参照	0.975秒
出力を配列	0.065秒
どちらも配列	0.031秒

考察

今回の検証で最速だったのはこのDictionaryでした。

やはりDictionaryはとんでもなく高速ですね。

今回は「Keyに検索値、Itemに行番号」を入れていますが、
実際のマクロではItemに配列を入れるなどさらに応用が利くのも強みです。

欠点としては、まずコーディング難易度がやや高いことが挙げられます。

学習が必要ですし、読む際も仕様をしっかり読み解く必要があります。

とはいえコード量的にはそんなでもありませんので、
習得してしまえばコーディングコストは高くないのはいい点ですね。

もう一つの欠点として、単独実行には向かない点も挙げられます。

Dictionaryを使って検索をする場合は、

1. 全データをまずはDictionaryに格納
2. Dictionary内を検索(厳密には検索ではないけれど)

という手順を踏むため、2だけを随時実行するのが少々難しいです。

今回のように「全データを一括で検索する」処理には向いていますが、
マクロの実行フローの中で適宜検索をするような処理には向いていません。

もちろんマクロの冒頭でDictionaryをまず生成しておき、
以降の随時検索にはそのDictionaryを使うということはできますけどね。

このあたりはマクロの仕様と相談しながら実装方法を決定してください。

自作したバイナリサーチ

ソースコード

Dim Arr読込配列
Arr読込配列 = WSテスト.Range("A2:C10001").Value

Dim Arr出力配列()
ReDim Arr出力配列(1 To 10000, 1 To 1)

Dim R As Long
For R = 1 To 10000
    
    Dim 検索値 As Long: 検索値 = Arr読込配列(R, 3)
    
    Dim R_上限値 As Long: R_上限値 = 10000
    Dim R_下限値 As Long: R_下限値 = 1
    Dim R_中央値 As Long
    
    Do
        R_中央値 = Int((R_上限値 + R_下限値) / 2)
        
        If Arr読込配列(R_中央値, 1) = 検索値 Then
            Arr出力配列(R, 1) = R_中央値
            Exit Do
        End If
        
        If Arr読込配列(R_上限値, 1) = 検索値 Then
            Arr出力配列(R, 1) = R_上限値
            Exit Do
        End If
        
        If Arr読込配列(R_中央値, 1) > 検索値 Then
            R_上限値 = R_中央値
        Else
            R_下限値 = R_中央値
        End If
        
        If R_上限値 - R_下限値 = 1 Then Exit Do
        
    Loop
        
Next

WSテスト.Range("D2:D10001").Value = Arr出力配列

結果

どちらも配列

0.047秒

考察

最後はネタというか、興味本位でやってみました。

バイナリサーチを自作してみて、それで全セルを検索しています。

意外にもMATCH系列とほとんど同速で終わる結果となっており、
実際は同じような実装になっているのかもしれません。

もちろんわざわざ検索コードを自作する必要は全くありませんので、
素直に組込メソッドやオブジェクトを使っていきましょう。

まとめ

以上で検証と考察を終わります。

結果・結論を再掲しますと、

検索方法	セル直接参照	読取を配列	出力を配列	どちらも配列
愚直にFor文	109.2秒	2.748秒	109.1秒	1.796秒
Range.Find	23.59秒
WF.Match	1.003秒	11.56秒	0.084秒	0.053秒
Formula "MATCH"	0.073秒
Formula "XMATCH"	0.117秒
Formula2 "MATCH"	0.034秒
Dictionary	0.975秒		0.065秒	0.031秒
自作バイナリサーチ				0.047秒

• 速度/記述コスト/汎用性のバランスが良いWorksheetFunction.Match
• 超速かつ低コストだがメンテ性に注意が必要なFormula/Formula2
• コーディング難易度が上がるが超速で汎用性も高いDictionary

これらを状況に応じて使い分けるのがよいという結論になります。

セルの検索はかなりの頻度で実装する処理ですので、
本記事を参考に、自分なりのベストプラクティスを研究してみてください。