マクロVLOOKUPの高速化

質問

• マクロVLOOKUPを使用したシート１とシート区分マスターの検索にかかる時間を短縮したい。
• シート１のデータ行数は常に変化しており、シート区分マスターの行数は固定されている。
• 現在の検索処理では５分以上かかっており、もっと早く処理する方法はないか検討したい。

回答No.3

end-u

VLookup自体が重いので、劇的に改善しようと思えば
dictionaryオブジェクトを使ったりする事になります。

Sub 区分検索02()を少しでも改善するなら
Sub test1()
　　Dim r　As Range
　　Dim ri As Range
　　Dim t As Single

　　t = Timer
　　Application.ScreenUpdating = False
　　Application.Calculation = xlCalculationManual

　　With Sheets("シート１")
　　　　Set r = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 2)
　　End With
　　r.ClearContents
　　On Error Resume Next
　　For Each ri In r
　　　　ri.Value = WorksheetFunction.VLookup(ri.Offset(, -2), Worksheets("区分マスター").Range("A1:E60000"), 5, 0)
　　Next
　　On Error GoTo 0
　　r.Replace "", "無", xlWhole

　　Application.Calculation = xlCalculationAutomatic
　　Application.ScreenUpdating = True
　　Debug.Print Timer - t
End Sub

また、ApplicationレベルでVLookupを使って配列に対して処理をすると
環境によっては速く処理できます。
#Excel2000では配列制限があるためこのままでは使えないです。
Sub test2()
　　Dim v
　　Dim t As Single

　　t = Timer
　　With Sheets("シート１")
　　　　With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
　　　　　　v = Application.VLookup(.Value, Worksheets("区分マスター").Range("A1:E60000").Value, 5, 0)
　　　　　　With .Offset(, 2)
　　　　　　　　.ClearContents
　　　　　　　　.Value = v
　　　　　　　　.Replace "#N/A", "無", xlWhole
　　　　　　End With
　　　　End With
　　End With
　　Debug.Print Timer - t
End Sub

お礼

ありがとうございます。
かなり早くなりました。
行数を倍にしても大丈夫です。

１．ここで教えていただいた表示と式停止の場合
　　13,500行＝1:49
　　27,000行＝3:35

２．質問後自作のLOOP文（記述は最後尾に）
　　13,500行＝1:00
　　27,000行＝1:58

３．今回教えていただいたSub test1()
　　13,500行＝0:58
　　27,000行＝1:54　

４．今回教えていただいたSub test2()
　　13,500行＝0:49
　　27,000行＝1:36　

質問では式を貼付して出た値を値貼付していた
↓
質問後自分で考えたloop文

Sub 区分検索02()

'2010 年11月18日
'シート１のＡ列を検索値として
'シート区分マスターのＡ列を検索しヒットしたら
'シート区分マスターの該当行のＥ列をシート１のＣ列に転記
'データはそれぞれのシートともに２列目からである
'ヒットしない場合はシート１のＣ列は空白にする

'検索値があるシート選択する
Sheets("シート１").Select
'そのシートの検索開始の行数を選択。2行目から開始。
Line = 2
'そのシートの検索値の列を指定1=A列。
'その値がなくなったら検索を終了させる。
Do Until Cells(Line, 1).Value = ""
'エラーとなっても次に進む
On Error Resume Next
'検索結果を転記する列を指定。Line3=C列
'検索するシートと列(1=A列）とヒットした場合のその行の返す値の列(5=E列）指定
Cells(Line, 3).Value = Application.WorksheetFunction.VLookup(Cells(Line, 1).Value, Worksheets("区分マスター").Range("A1:E60000"), 5, 0)
'VLOOKUP関数が終了又はエラーが発生したら止まる
On Error GoTo 0
'検索されなかったときの処理。
If Cells(Line, 3).Value = "" Then
Cells(Line, 3).Value = "無"
End If
'2行目から開始なので次の行の値を検索値とする
Line = Line + 1
'検索する値がなくなるまで繰返す
Loop
End Sub

補足

２０１０年１１月１９日　１２：００

申し訳ありません。
回答Ａ－ＮＯ．４の補足で間違いです。

＞VLOOKUPで行った時と
＞この記述で行った時で
＞返ってきた値が相違する行が２，０００行ほどあります。
＞原因が分かりません。

＞多分と思われるのが
＞シート１の検索値（シート１のＡ列）に重複レコードがうじゃうじゃランダムに存在するという事くらいですが
＞重複していない1行のみしかいない行であっても
＞返ってきた値が相違しています。

＞投稿日時 - 2010-11-19 11:51:18

重複レコードはありませんでした。
ですが
VLOOKUPで行った時と
この記述で行った時で
返ってきた値が相違する行が２，０００行ほどあります。

申し訳ありません。