- Findメソッドよりも
高速で操作が簡単、エラーにも強い方法を紹介します。 - オリジナル関数ですが、コピペで使えます。
- 検索対象に対し、「完全一致」「部分一致」引数一つで対応できます。
- オリジナル関数は2種類です。
検索範囲から一つだけセルを返す関数と
検索範囲から一致するセルを全て返す関数です
こんにちは、hokkyokunです。
VBAでシートのセル範囲から特定の値(文字列や数値)を検索し、
その位置を取得する方法をご紹介します。
Findメソッドではありません
特徴は
- Findメソッドよりも高速
- Findメソッドよりもエラーに強い
- Findメソッドよりも簡単に扱える
- Findメソッドは非表示セルを検索できないが、
紹介する方法は検索可能 - 完全一致、部分一致対応可能
オリジナル関数です。
VBAは結構不親切なところがあり、
色々と関数を作ることで機能を補っています。
配列を便利に使いやすくする関数を作っています。
Findメソッドに関して他にも記事を書いているので
よかったら見てやってください。
Findメソッドも便利なのですが、
操作が難しく、またエラーが発生しやすい、そして遅いという特徴があります。
紹介する関数は
使いやすいのでぜひ見ていってください。
Findメソッドをおすすめしない理由
Findメソッドをおすすめしない理由は以下の通りです。
これらを解説すると長くなるので
こちらをご確認ください。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/sad-2635043_640.jpg)
セル範囲から一致するセルを一つ取得する関数
関数名
コード
コピペして使ってみて下さい。
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=”リン”、部分一致で検索してみます。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-28.png)
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セル(リンゴと書かれている)を返してくれました。
セル範囲から一致するセルを全て取得する関数
関数名
コード
コピペして使ってみて下さい。
ただし、注意書きは必ず見てください。
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
詳しい使い方はこちらにありますので、
よかったら見てください。
![](https://www.hokkyokun.com/wp-content/themes/jin/img/noimg320.png)
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関数については
こちらに詳しく書いていますので見てください。
![](https://www.hokkyokun.com/wp-content/themes/jin/img/noimg320.png)
使い方
Search関数と同じ例でいきますが、
下記のように入力されているシートに対し
KeyWord=「リン」、部分一致で全検索します。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-28.png)
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
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-29.png)
速度を比べてみました。
最後にFindメソッドと今回作ったSearch関数の
どちらが処理能力が早いか計測してみました。
ルール
下記のように「リンゴ、バナナ、メロン、イチゴ、スイカ」と
あらかじめ入力されている表を用意します。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-25.png)
- 最初はリンゴを表(A1~A5)の範囲で検索していきます。
- 検索したセルの隣(B列)に数値
(リンゴなら1、バナナなら2、…、スイカは5)
を入力していきます。 - リンゴの次はバナナ、次はメロン、とスイカまで順々に検索していきます。
- スイカまで検索したら、B列をクリアします。
- またリンゴを検索し…、これを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メソッドの結果です。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-26.png)
Search関数の結果です。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-30.png)
Search関数の方が3倍近く速いことがわかりました。
Match関数を使うと早い?
もっと早い方法ないの?
みんなが大好きなMatch関数を検討してみました。
Match関数はエクセルでよく使われる関数で
特定の値が入っている行を取得してくる関数です。
Match関数を使った処理関数
- 列ごとにFor文を回す
- それぞれの列に対し、Match関数を使って該当の値のある行を取得
- ①の列と②で取得した行を組み合わせて該当のセル位置を特定する
上記のようなロジックでコーディングしました。
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
上記の関数を使って計測してみます。
![](https://www.hokkyokun.com/wp-content/uploads/2023/04/image-56.png)
…あれ?
意外に遅い…
おそらく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の学習方法をまとめました。
![](https://www.hokkyokun.com/wp-content/uploads/2023/03/Title-Page-320x180.png)
VBAを高コスパで、短期間で学ぶにはUdemyがおすすめです。
Udemyは良質の学習プラットフォームですが、
動画数が多すぎてどれを見ればよいか迷います。
おすすめの講師をまとめました。
![](https://www.hokkyokun.com/wp-content/uploads/2023/03/Title-Page-1-320x180.png)
Findメソッドの他の記事です。
ブログ村ランキング参加中です。よかったらフォローお願いします!!
![PVアクセスランキング にほんブログ村](https://blogparts.blogmura.com/parts_image/user/pv11165663.gif)