■ 様々な検索方法をマスターしよう！
業務システムを開発する上で、必須機能となるのがデータの検索です。
データーベース利用を前提にするなら、SQL文で検索すれば事足りるのですが、ExcelVBAの場合、ワークシート上のデータから検索したいこともよくある話です。
今回は、代表的な検索方法から、ちょっと変ったものまでいくつかご紹介したいと思います。
開発内容に応じて、使い分けをしてもらえればと思います。
■Findメソッドによる検索
エクセル検索の代表的なものは、このFindメソッドによる検索でしょう。ワークシート上から欲しいデータをあっという間に検索します。
（例）
Public Sub test()
Debug.Print Columns("a").Find(What:="excel").Offset(0, 1)
End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
ただしこの例では、検索値が見つからなかった場合にエラーが発生します。検索値が見つからなかった場合、FindメソッドはNothingを返します。NothingはRangeオブジェクトではないため、Offsetプロパティで値を取得することができません。
検索値が見つからなくてもエラーが発生しないようにするには、
（例）
Public Sub test()
Dim MyRange As Range
Set MyRange = Columns("a").Find(What:="excel")
If MyRange Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print MyRange.Offset(0, 1)
End If
End Sub
このようにします。
Findメソッドの返り値をオブジェクト変数にセットし、セットしたオブジェクト変数がNothingかどうかを判定させ、処理を分岐させるようにします。
Findメソッドには、たくさんの引数を指定できます。指定可能な引数は以下の通りです。
|
引数 |
定数 |
内容 |
省略 |
|
What |
|
検索するデータを指定します |
不可 |
|
After |
|
検索を開始するセルを指定します
省略すると検索範囲の左上端から検索します |
可 |
|
LookIn |
|
|
可 |
| |
xlFormulas |
数式を検索対象に指定します |
|
| xlValues |
値を検索対象に指定します |
|
| xlComents |
コメント文を検索対象に指定します |
|
|
LookAt |
|
|
可 |
| |
xlPart |
一部が一致するセルを検索します |
|
| xlWhole |
全てが一致するセルを検索します |
|
|
SearchOrder |
|
|
可 |
| |
xlByRows |
列方向に検索します |
|
| xlByColumns |
行方向に検索します |
|
|
SearchDirection |
|
|
可 |
| |
xlNext |
順方向に検索します(規定値) |
|
| xlPrevious |
逆方向に検索します |
|
|
MatchCase |
|
|
可 |
| |
True |
大文字と小文字を区別します |
|
| False |
大文字と小文字を区別しません(規定値) |
|
|
MatchByte |
|
|
可 |
| |
True |
半角と全角を区別します |
|
| False |
半角と全角を区別しない(規定値) |
|
|
SearchFormat |
|
|
可 |
| |
True |
検索に書式を含めます |
|
| False |
検索に書式を含めません |
|
LookIn、LookAt、SearchOrder、MatchByteの引数を省略した場合、前回検索したときのプロパティが使用されます。
SearchFormat引数はExcel2002以降で使用可能です。
■Vlookup関数による検索
ワークシート関数のVlookup関数ですが、当然VBAからでも利用できます。
（例）
Public Sub test()
Debug.Print WorksheetFunction.VLookup("excel", Range("A:B"), 2, False)
End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合に実行時エラーが発生します。
検索値が見つからなくても実行時エラーが発生しないようにするには、
（例）
Public Sub test()
Dim MyVariant As Variant
MyVariant = Application.VLookup("excel", Range("A:B"), 2, False)
If IsError(MyVariant) Then
Debug.Print "Not Found"
Else
Debug.Print MyVariant
End If
End Sub
このようにします。
Application.WorksheetFunction.VLookupをApplication.VLookupにすることで、エラー値を取得することが可能になります。（WorksheetFunctionプロパティを使用する場合はエラートラップが必要になります）
■セル範囲を配列に取得して検索します
セル範囲をいったん配列に格納してから検索させてみましょう。
（例）
Public Sub test()
Dim MyVariant As Variant
Dim i As Long
MyVariant = Range("A:B")
For i = 1 To UBound(MyVariant)
If MyVariant(i, 1) = "excel" Then
Debug.Print MyVariant(i, 2)
Exit Sub
End If
Next i
Debug.Print "Not Found"
End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合"Not Found"を出力します。
■ADOレコードセットを取得して検索します
ADOのレコードセットオブジェクトには、Findメソッドが用意されています。
ワークシートをレコードセットに格納したのち、Findメソッドで検索させてみます。
※ 参照設定で「Microsoft ActiveX Data ObjectX.X Library」にチェックを入れる必要があります。（X.Xはバージョン名）
（例）
Public Sub test()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Excel 8.0"
CN.Open ThisWorkbook.FullName
SQL = "SELECT * FROM [" & ActiveSheet.Name & "$]"
Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly
RS.Find RS.Fields(0) & "='excel'"
If RS.EOF Then
Debug.Print "Not Found"
Else
Debug.Print RS.Fields(1)
End If
End Sub
この例では、アクティブシートのA列を検索し、"excel"の文字列が見つかったセルの右隣りのセルの値を出力します。
またこの例では、検索値が見つからなかった場合"Not Found"を出力します。
速度は遅いですがこの方法の場合、閉じているブックに対して検索がかけられるというメリットがあります。
※ その場合、ThisWorkbook.FullNameの部分を対象のブックのフルパスに、ActiveSheet.Nameの部分を検索対象のシート名に変更する必要があります。
フィールド名がある（シートの1行目に項目名がある）場合、Select文のWhere句を用いてデータを検索することも可能です。
（例）
Public Sub test()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL As String
Set CN = New ADODB.Connection
CN.Provider = "Microsoft.Jet.OLEDB.4.0"
CN.Properties("Extended Properties") = "Excel 8.0"
CN.Open ThisWorkbook.Path & "\test.xls"
SQL = "SELECT * FROM [Sheet1$] WHERE DATA='excel'"
Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly
If RS.EOF Then
Debug.Print "Not Found"
Else
Debug.Print RS.Fields("NO")
End If
End Sub
この例では、マクロを実行するブックと同じフォルダにあるtest.xlsブックのワークシート"Sheet1"から、項目名"DATA"の列を検索し、"excel"の文字列が見つかったとき、同じ行の"NO"の列の値を出力します。
また、この場合もtest.xlsブックが開いている必要はありません。
それではせっかくですので、最後に各方法による検索速度を比較してみましょう。
速度比較は、ワークシート上にある5万件のデータから検索する処理を10回繰り返した時間で比較しています。
| 検索方法（単位ミリ秒） |
計測1回目 |
計測2回目 |
計測3回目 |
| Findメソッドによる検索 |
125 |
125 |
140 |
| Vlookup関数による検索 |
31 |
47 |
47 |
| 配列を使用した検索 |
203 |
219 |
218 |
| ADOのFindメソッドによる検索 |
2562 |
2532 |
2532 |
| SQLのWHERE句による検索 |
532 |
541 |
547 |
| ※ADOによる検索は両方とも、閉じたブックに対して行った結果です。 |
こうして比較してみると、Vlookup関数とFindメソッドを利用した検索の速さが際立ちます。並び替え（Sort）のときも触れましたが、Excel自体が持つ検索機能がいかに優れているかがよくわかる結果となりました。
ADOによる検索も速度は遅いものの閉じたブックに対して有効であるなど、それぞれに長所があります。
開発するシステムに応じて、適材適所、使い分けてください。
|
