2010年10月27日
特定のセル範囲からデータを探す方法を比較する。
いろいろな方法が考えられるが、ここでは、ワークシート関数のVLOOKUP、MATCHを使う方法と、Findメソッドを使う方法および配列の中から探す方法を比較してみる。
テストプログラムは下記の通りです。
VBEの標準モジュールに貼りつけて実行してみてください。
Public Sub test3()
Dim tm As Variant
Dim aryRnd(0 To 60000 - 1, 0 To 1) As Variant
Dim idx As Long
Dim searchVal As Variant
Dim hitline As Variant
Dim hitrng As Range
Dim aryRange As Variant
Dim found As Boolean
'10000-99999の範囲の数値を生成してシートに配置
Randomize
For idx = 1 To 60000
aryRnd(idx - 1, 0) = Int((99999 - 10000 + 1) * Rnd + 10000)
aryRnd(idx - 1, 1) = idx '行番号
Next idx
'配列をセル範囲に出力します
With Worksheets("test1")
.Cells.ClearContents
Range(.Cells(1, 1), .Cells(60000, 2)) = aryRnd
'検索する数値
searchVal = Int((99999 - 10000 + 1) * Rnd + 10000)
End With
With Worksheets("test1")
'Vlookup関数で検索
'2列目の値を返す
On Error Resume Next '見つからない場合はエラーが返されるのでトラップする
tm = Timer
hitline = Application.WorksheetFunction.VLookup(searchVal, .Range("A1:B60000"), 2, False)
'処理時間を出力します
Debug.Print "Vlookup関数", Format(Timer - tm, "0.0######")
If Err.Number 0 Then
MsgBox "見つかりません", vbOKOnly + vbExclamation, "VLookupによる検索"
End If
On Error GoTo 0
'match関数で検索
On Error Resume Next '見つからない場合はエラーが返されるのでトラップする
tm = Timer
hitline = Application.WorksheetFunction.Match(searchVal, .Range("A1:A60000"), 0)
'処理時間を出力します
Debug.Print "match関数", Format(Timer - tm, "0.0######")
If Err.Number 0 Then
MsgBox "見つかりません", vbOKOnly + vbExclamation, "matchによる検索"
End If
On Error GoTo 0
'find関数で検索
tm = Timer
Set hitrng = .Range("A1:A60000").Find(what:=searchVal, LookIn:=xlValues, lookat:=xlWhole)
'処理時間を出力します
Debug.Print "find関数", Format(Timer - tm, "0.0######")
If hitrng Is Nothing Then
MsgBox "見つかりません", vbOKOnly + vbExclamation, "findによる検索"
End If
Set hitrng = Nothing
'配列から探す
tm = Timer
aryRange = .Range(.Cells(1, 1), .Cells(60000, 2))
found = False
For idx = 1 To UBound(aryRange, 1)
If aryRange(idx, 1) = searchVal Then
found = True
Exit For
End If
Next idx
'処理時間を出力します
Debug.Print "配列から", Format(Timer - tm, "0.0######")
If Not found Then
MsgBox "見つかりません", vbOKOnly + vbExclamation, "配列から検索"
End If
End With
End Sub
このプログラムで5回実行 してみた結果は下記の通り。ただし、PCの性能により絶対値は変わってくると思いますので、各方法の差のみに注目してください。
これからわかるように、検索値が範囲内のどこにあるかでそれぞれの実行結果は異なりますが、おおむねワークシート関数を使う方法が最も高速のようです。
findや配列からさがす方法もそれなりに高速ではあるので、どれを使うかは「好み」の問題かもしれません。