Sub TEST1()
t = Timer
Dim A
A = Range("A2:B10001") '検索値を取得'検索値をループする
For i = 1 To UBound(A, 1)
'WorksheetFunctionでVLookup関数を使って値を取得
A(i, 2) = WorksheetFunction.VLookup(A(i, 1), Range("D2:E50001"), 2, False)
Next
'セルに結果を入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
大量データを用意しておきます。
大量データを用意
では、VBAコードを実行してみます。
「WorksheetFunction」でVLookup関数を使う
「WorksheetFunction」でVLookup関数を使って検索できました。
かかった時間
かかった時間は、「27.71 秒」となりました。
まぁちょっと遅いかなという感じです。
ForとIfを組み合わせて値を取得
「ForとIfを組み合わせて」値を検索して取得してみます。
力技でやるという感じです。
Sub TEST2()
t = Timer
Dim A, B
A = Range("A2:B10001") '検索値を取得
B = Range("D2:E50001") 'データベースを取得'検索値をループ
For i = 1 To UBound(A, 1)
'データベースをループ
For j = 1 To UBound(B, 1)
'値が一致した場合
If A(i, 1) = B(j, 1) Then
A(i, 2) = B(j, 2) '検索結果を取得
Exit For 'ループを終了
End If
Next
Next
'セルに結果を入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
大量データを用意しておきます。
大量データを用意
では、VBAコードを実行してみます。
「ForとIfを組み合わせて」値を取得
「ForとIfを組み合わせて」値を取得できました。
かかった時間
かかった時間は、「62.10 秒」です。
力技は一番やっちゃいけないですね。
埋め込み数式でVLookup関数を使う
「埋め込み数式」でVLookup関数を使ってみます。
この「埋め込み数式」がかなり高速で、かつVBAコードもシンプルです。
実務ではこの方法を使った方がいいですね。
Sub TEST3()
t = Timer
'埋め込み数式でVLookup関数を使う
Range("B2:B10001") = "=VLOOKUP(A2,$D$2:$E$50001,2,FALSE)"
Range("B2:B10001").Value = Range("B2:B10001").Value '値に変換
Debug.Print Timer - t & " 秒"
End Sub
Sub TEST4()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
'データベースを取得
Dim B
B = Range("D2:E10")
'データベースを辞書に登録する
For i = 1 To UBound(B, 1)
A.Add B(i, 1), B(i, 2)
Next
'検索値を取得
Dim C
C = Range("A2:B4")
'辞書を検索して価格を取得
For i = 1 To UBound(C, 1)
C(i, 2) = A(C(i, 1))
Next
'セルに結果を入力
Range("A2").Resize(UBound(C, 1), UBound(C, 2)) = C
End Sub
Dictionaryはちょっとややこしいので簡単な表を使って、実行手順をみてみます。
簡単な表を用意します。
データベースを配列に入力して「Dictionary」に登録します。
検索値を配列に入力します。
検索値を「Dictionary」から検索してアイテムを取得していきます。
配列をセルに入力します。
これで、Dictionaryを使って値を取得できます。
Dictionaryで値を取得できました。
大量データで測定してみる
大量データで測定してみます。
Sub TEST5()
t = Timer
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
'データベースを取得
Dim B
B = Range("D2:E50001")
'データベースを辞書に登録する
For i = 1 To UBound(B, 1)
A.Add B(i, 1), B(i, 2)
Next
'検索値を取得
Dim C
C = Range("A2:B10001")
'辞書を検索して価格を取得
For i = 1 To UBound(C, 1)
C(i, 2) = A(C(i, 1))
Next
'セルに結果を入力
Range("A2").Resize(UBound(C, 1), UBound(C, 2)) = C
Debug.Print Timer - t & " 秒"
End Sub
