findメソッド

【VBA】Findメソッドよりも簡単、早い!高速でセル位置を検索、取得する方法。完全一致、部分一致対応。複数セル取得対応。

記事内に商品プロモーションを含む場合があります

こんにちは、hokkyokunです。

VBAでシートのセル範囲から特定の値(文字列や数値)を検索し、
その位置を取得する方法をご紹介します。

Findメソッドではありません

特徴は

  • Findメソッドよりも高速
  • Findメソッドよりもエラーに強い
  • Findメソッドよりも簡単に扱える
  • Findメソッドは非表示セルを検索できないが、
    紹介する方法は検索可能
  • 完全一致、部分一致対応可能

オリジナル関数です。
VBAは結構不親切なところがあり、
色々と関数を作ることで機能を補っています。

配列を便利に使いやすくする関数を作っています。

Findメソッドに関して他にも記事を書いているので
よかったら見てやってください。

Findメソッドも便利なのですが、
操作が難しく、またエラーが発生しやすい、そして遅いという特徴があります。

紹介する関数は
使いやすいのでぜひ見ていってください。

Findメソッドをおすすめしない理由

Findメソッドをおすすめしない理由は以下の通りです。

  • 引数設定が難しい
  • 引数の設定が前回の検索条件に引っ張られる
  • 遅い
  • 非表示範囲を検索しない

これらを解説すると長くなるので
こちらをご確認ください。

【VBA Findメソッド】あるのに見つからない場合に試すこと4選。エラー回避と代替手段 このページでわかること Findメソッドは見つからない場合、続けて処理するとエラーが生じる可能性がある。→エラー回避方法を紹介 ...

関数名

  • 関数名
    Search ( Rng , keyWord , Whole )
  • 引数
    ① Rng : 検索範囲(データ型はRangeオブジェクト)
    ② KeyWord : 検索する値(データ型はVariant)
    ③ Whole : 完全一致か部分一致か選択
     完全一致⇒True 部分一致⇒False
  • 戻り値
    Rangeオブジェクト 見つからなかった場合は「Nothing」

コード

コピペして使ってみて下さい。

Function Search(ByVal Rng As Range, ByVal keyWord As Variant, ByVal Whole As Boolean)
'   引数:
'       Rng:検索範囲 (例) ActiveSheet.Range ("A1:Z500")
'       KeyWord:検索する値 (例) "リンゴ",10,7.85
'       Whole:完全一致→True 部分一致→False (例)True
'   戻り値
'       Rangeオブジェクト 見つからなかった場合は「Nothing」

'   検索範囲内の「KeyWord」を検索する関数です。
'   検索範囲を一つずつ判定し、最初に一致したセル範囲を返します。
'   完全一致と部分一致対応できます。
'   非表示のセルも検索かけます
'   検索結果はセル範囲一つだけです。複数の場合が良ければ「search_List」関数を使ってください


    Dim r As Range
    
    '完全一致
    If Whole Then
        For Each r In Rng
            If r.Value = keyWord Then
                Set Search = r
                Exit Function
            End If
        Next
        
    '部分一致
    Else
        For Each r In Rng
            If InStr(r.Value, keyWord) > 0 Then
                Set Search = r
                Exit Function
            End If
        Next
    End If
    
    '見つからなかった時の処理
    '適宜変更してもらって構いません
    Set Search = Nothing
            
End Function

使い方

使ってみます。
下記のように入力してあり、
keyWord=”リン”、部分一致で検索してみます。

Sub test_search()

    Dim Rng As Range
    Dim result As Range
    
    Set Rng = ThisWorkbook.Worksheets(1).Range("A1:Z5000")
    
    Set result = Search(Rng, "リン", False)
    
    If Not result Is Nothing Then
        Debug.Print result.Address
    Else
        Debug.Print "Nothing"
    End If
    
    '>>$A$1
End Sub

A1から検索かけるので、
最初にヒットしたA1セル(リンゴと書かれている)を返してくれました。

セル範囲から一致するセルを全て取得する関数

関数名

  • 関数名
    Search_List ( Rng , keyWord , Whole )
  • 引数
    ① Rng : 検索範囲(データ型はRangeオブジェクト)
    ② KeyWord : 検索する値(データ型はVariant)
    ③ Whole : 完全一致か部分一致か選択
     完全一致⇒True 部分一致⇒False
  • 戻り値
    Rangeオブジェクト 見つからなかった場合は「Nothing」

コード

コピペして使ってみて下さい。

ただし、注意書きは必ず見てください。

Function search_List(ByVal Rng As Range, ByVal keyWord As Variant, ByVal Whole As Boolean)
'   引数:
'       Rng:検索範囲 (例) ActiveSheet.Range ("A1:Z500")
'       KeyWord:検索する値 (例) "リンゴ",10,7.85
'       Whole:完全一致→True 部分一致→False (例)True
'   戻り値
'       配列 見つからなかった場合は「空の配列」

'   検索範囲内の「KeyWord」を検索する関数です。
'   検索範囲を一つずつ判定し、一致したセル範囲を全て配列に含めて返します。
'   完全一致と部分一致対応できます。
'   非表示のセルも検索かけます
'   配列に追加する関数「set_add_Elm」関数、「Is_correct_array」関数を使っています。
'   ↑の関数は当ブログで解説していますので良かった見てください。


    Dim r As Range
    
    '完全一致
    If Whole Then
        For Each r In Rng
            If r.Value = keyWord Then
                Call set_add_Elm(search_List, r)
            End If
        Next
        
    '部分一致
    Else
        For Each r In Rng
            If InStr(r.Value, keyWord) > 0 Then
                Call set_add_Elm(search_List, r)
            End If
        Next
    End If
    
    '見つからなかった時の処理
    '適宜変更してもらって構いません
    If Not Is_correct_array(search_List) Then
        search_List = Array()
    End If
    
            
End Function

注意書き

Search_List関数を使うためには準備が必要です。

このプログラムは複雑な仕様となっているため
二つのサブ関数を使用しています。

  • これらの関数はオリジナルで作っています。
  • これらのコードもコピーしないと動きませんので、
  • 下記に紹介しているコードを合わせてコピーしてください。

Is_correct_array関数

Is_correct_array関数は
配列がエラーを発生させる空の状態かどうかを判断する関数です。

コードはこちらです。

Function Is_correct_array(ByVal arrs As Variant)
    Dim a As Long
    
    'なんでもいいが、エラーを生じさせる
    On Error GoTo err
    a = UBound(arrs)

    'エラーが生じたときエラー番号で9か13の場合はFalse
err:
    If err.number = 9 Or err.number = 13 Then
        Is_correct_array = False
    Else
        Is_correct_array = True
    End If
    
End Function

詳しい使い方はこちらにありますので、
よかったら見てください。

【VBA】配列が空かどうかを判定する関数とエラー回避する関数 わかること 配列が空の状態は2種類①エラーが発生する状態②エラーが出ずEmptyが要素として入った状態 配列の状態を把握するこ...

set_add_Elm関数

set_add_Elm関数は
配列にオブジェクトを追加する関数です。

Function set_add_Elm(ByRef arrs As Variant, ByVal elm As Object)
    Dim num As Long
    
    'Is_correct_array関数で配列がエラーを起こす空の状態かどうか判定
    
    'エラーを起こす空の状態
    If Not Is_correct_array(arrs) Then
    
        '要素数が一つ=「0」で宣言
        ReDim arrs(0)
        Set arrs(0) = elm
    
    'エラーを起こす空の状態ではない
    Else
        num = UBound(arrs)
        ReDim Preserve arrs(num + 1)
        Set arrs(num + 1) = elm
    End If
End Function

基本的には配列に要素を追加する
add_Elm関数と同様ですが、オブジェクトに対応するよう
少し変えています。

add_Elm関数については
こちらに詳しく書いていますので見てください。

【VBA】配列に要素を追加する関数(末尾、任意の場所、コピペで使える) わかること 以下の二つの関数を紹介します。 シンプルな末尾追加関数 任意の位置に挿入する関数 エラー回避関数を組み...

使い方

Search関数と同じ例でいきますが、
下記のように入力されているシートに対し
KeyWord=「リン」、部分一致で全検索します。

Sub test_search_list()
    Dim Rng As Range
    Dim results As Variant
    
    Set Rng = ThisWorkbook.Worksheets(1).Range("A1:Z5000")
    
    results = search_List(Rng, "リン", False)
    
    Dim result As Variant
    For Each result In results
        Debug.Print result.Address & " " & result.Value
    Next
    
End Sub

速度を比べてみました。

最後にFindメソッドと今回作ったSearch関数の
どちらが処理能力が早いか計測してみました。

ルール

下記のように「リンゴ、バナナ、メロン、イチゴ、スイカ」と
あらかじめ入力されている表を用意します。

  1. 最初はリンゴを表(A1~A5)の範囲で検索していきます。
  2. 検索したセルの隣(B列)に数値
    (リンゴなら1、バナナなら2、…、スイカは5)
    を入力していきます。
  3. リンゴの次はバナナ、次はメロン、とスイカまで順々に検索していきます。
  4. スイカまで検索したら、B列をクリアします。
  5. またリンゴを検索し…、これを5000回繰り返します。

Search関数はFindメソッドの約3倍の速度

このような単純処理を
Findメソッドと
オリジナル関数Search関数の二種類の検索方法で比較してみます。

計測はそれぞれ5回行いました
プログラムはこんな感じです。

Sub speed_Test_findmethod()

Debug.Print "これはFindメソッドです"
Dim start, end_ As Date

Dim j As Long

For j = 1 To 5
    start = Now
    
    Dim Rng As Range
    Set Rng = ActiveSheet.Range("A1:A5")
    
    Dim arrs, arr As Variant
    arrs = Array("リンゴ", "バナナ", "メロン", "イチゴ", "スイカ")
    
    
    Dim r As Range
    Dim i As Long
    
    For i = 1 To 5000
        For Each arr In arrs
            Set r = Rng.Find(arr)
            
            Select Case r.Value
                Case "リンゴ": r.Offset(, 1).Value = 1
                Case "バナナ": r.Offset(, 1).Value = 2
                Case "メロン": r.Offset(, 1).Value = 3
                Case "イチゴ": r.Offset(, 1).Value = 4
                Case "スイカ": r.Offset(, 1).Value = 5
            End Select
            Set r = Nothing
        Next
        
        ActiveSheet.Columns("B").ClearContents
    Next i
    
    end_ = Now
    
    Debug.Print Format((end_ - start), "hh:nn:ss")
Next j
End Sub

ちなみに当たり前ですが、Search関数を試すときも同様のプログラムです
(debugで表示する文字やFindメソッドをSearch関数に置き換えるなど一部変更)
同じなので、省略します。

Findメソッドの結果です。

Search関数の結果です。

Search関数の方が3倍近く速いことがわかりました。

Match関数を使うと早い?

もっと早い方法ないの?

みんなが大好きなMatch関数を検討してみました。

Match関数はエクセルでよく使われる関数で
特定の値が入っている行を取得してくる関数です。

Match関数を使った処理関数

  1. 列ごとにFor文を回す
  2. それぞれの列に対し、Match関数を使って該当の値のある行を取得
  3. ①の列と②で取得した行を組み合わせて該当のセル位置を特定する

上記のようなロジックでコーディングしました。

Function Search2(ByVal rng As Range, ByVal keyWord As Variant, ByVal Whole As Boolean)
'   引数:
'       Rng:検索範囲 (例) ActiveSheet.Range ("A1:Z500")
'       KeyWord:検索する値 (例) "リンゴ",10,7.85
'       Whole:完全一致→True 部分一致→False (例)True
'   戻り値
'       Rangeオブジェクト 見つからなかった場合は「Nothing」

'   検索範囲内の「KeyWord」を検索する関数です。
'   検索範囲を一つずつ判定し、最初に一致したセル範囲を返します。
'   完全一致と部分一致対応できます。
'   非表示のセルも検索かけます
'   検索結果はセル範囲一つだけです。複数の場合が良ければ「search_List」関数を使ってください


    Dim r As Range
    Dim s_Row, e_Row As Long
    Dim s_Col, e_Col As Long
    Dim col_, row_ As Long
    Dim ws As Worksheet
    
    '指定された範囲の左上のセルと右上のセルから
    '行と列の範囲を取得
    s_Row = rng(1).Row
    e_Row = rng(rng.Count).Row
    
    s_Col = rng(1).Column
    e_Col = rng(rng.Count).Column
    
    '指定された範囲のシートを取得
    Set ws = rng.Parent
    
    '不完全一致の場合はワイルドカードを付ける
    If Not Whole Then
        keyWord = "*" & keyWord & "*"
    End If
    
    Dim tgtRow As Long  'Match関数で該当のセルの行を取得する
    
    For col_ = s_Col To e_Col
        On Error GoTo err
        tgtRow = WorksheetFunction.Match(keyWord, ws.Range(ws.Cells(s_Row, col_), ws.Cells(e_Row, col_)), 0)
        If tgtRow <> 0 Then
            Set Search2 = ws.Cells(tgtRow, col_)
            Exit Function
        End If

err:
    Resume Next
    Next col_
    
    Set Search2 = Nothing
            
End Function

上記の関数を使って計測してみます。

…あれ?

意外に遅い…
おそらくMatch関数って一つの列に対し適合する行を取得することに
特化しているのだと思います。

今回のケースだとA1~A5の短い範囲をぐるぐる確認させたので
差が生じなかったのだと思います。

例えばA列全体を検索すると速度はかなり変わってくるのだと思います。

WorkSheet関数はあまりおすすめしない

個人的に、Worksheet関数をVBAで使うのはお勧めしません

わかります。
批判されると思います…
お前が使いこなせてないだけだろと…

ただ、Match関数を使ってみて思ったことなのですが、

使い方の範囲せまくない?

なんというか、汎用的でないというか…

例えば、複数の離れた列や[A1:A5,D7:D15,H8:J12]みたいにとびとびの範囲から
検索するとき、かなり複雑な処理を書かなきゃいけません。
(これをしなきゃいけない使い方がいいか悪いはここでは不問とします)

WorkSheetFunctionで呼び出せる関数(VLOOKUP等)は
エクセルを手動で動かすうえで使うべきで
プログラミング脳とは相容れないと考えます

プログラミング脳を身につけよう

プログラミングは分岐と反復が基本です

指定されたセル範囲を一つ一つ検査して処理
という考えの方がはるかに応用が利きます。

そもそもFindメソッドを遅い遅いと煽りましたが、
業務に支障をきたすレベルでありません。

そのFindメソッドの三倍速いので
汎用性を考えればSearch関数が実用的と考えます。

まとめ

まとめ
  • Findメソッドよりも
    高速、操作が簡単、エラーにも強い関数を紹介
  • 完全一致」「部分一致」も引数で対応可能
  • Search関数
    セル範囲から一致するセルを一つ取得する関数
  • Search_List関数
    セル範囲から一致するセルを全て取得する関数
  • Search関数はFindメソッドの約3倍速い

VBAの学習方法をまとめました。

VBA(マクロ)のおすすめの学習方法 こんにちはhokkyokunです。 VBAを学ぶことで確実に業務は効率化し、余裕をもって仕事をすることができるようになります。 ...

VBAを高コスパで、短期間で学ぶにはUdemyがおすすめです。
Udemyは良質の学習プラットフォームですが、
動画数が多すぎてどれを見ればよいか迷います。

おすすめの講師をまとめました。

【Udemyは講師で選べ!】UdemyがVBA学習に最適な理由とおすすめのVBA講師 こんにちはhokkyokunです。 巨大学習プラットホームUdemyの中からVBAに関する動画について講師に焦点を当ててまとめま...

Findメソッドの他の記事です。

ブログ村ランキング参加中です。よかったらフォローお願いします!!

PVアクセスランキング にほんブログ村