Sub TEST1()
Dim A, B
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
B = Range("A2:B7") '配列に格納'辞書に登録
For i = 1 To UBound(B)
A.Add B(i, 1), B(i, 2)
Next
Range("E2") = A(Range("D2").Value) '価格を取得
End Sub
Dictionaryで値を検索してみます。
Dictionaryで値を検索
商品の「B」で検索して、価格を取得してみます。
Dictionaryで検索して、価格を取得できます。
というような感じで、Dictionaryを使って値を検索できます。
大量データから高速で値を検索
では、大量データを使って値を検索してみます。
次の2つの方法で、値を検索する時間を比較してみます。
ForとIfと配列
Dictionary
では、やってみます。
ForとIfと配列を使って値を検索してみる
まずは、ForとIfと配列を使って値を検索してみます。
検索する値をループ
データベースをループ
一致する場合に値を取得
配列をセルに入力
という流れになります。
Sub TEST2()
t = Timer
Dim A, B
A = Range("A2:B17577") 'データベースを配列に格納
B = Range("D2:E2001") '検索する値を配列に格納
For i = 1 To UBound(B)
For j = 1 To UBound(A)
'値が一致した場合
If B(i, 1) = A(j, 1) Then
B(i, 2) = A(j, 2) '価格を取得
Exit For
End If
Next
Next
'配列をセルに入力
Range("D2").Resize(UBound(B, 1), UBound(B, 2)) = B
Debug.Print Timer - t & " 秒"
End Sub
配列を使っているので、そこそこ高速になります。
大量データで値を検索
大量データを用意しました。
データベースが「17,577行」で、検索する値は「2,000行分」となります。
実行すると、大量データから値を検索できます。
大量データから値を検索できました。
では、かかった時間をみてみます。
かかった時間
「約2秒」ですね。
配列を使って、ある程度は高速化しているんですけど、ちょっと遅いという感じです。
Dictionaryを使って高速で値を検索
次は、Dictionaryを使って高速で値を検索してみます。
Dictionaryを作成
Dictionaryにデータベースを登録
検索値でDictionaryを検索して値を取得
配列をセルに入力
という感じです。
VBAコードは、次のようになります。
Sub TEST3()
t = Timer
Dim A, B, C
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
B = Range("A2:B17577") 'データベースを配列に格納
C = Range("D2:E1001") '検索する値を配列に格納'辞書に登録
For i = 1 To UBound(B)
A.Add B(i, 1), B(i, 2)
Next
For i = 1 To UBound(C)
C(i, 2) = A(C(i, 1)) '価格を取得
Next
'配列をセルに入力
Range("D2").Resize(UBound(C, 1), UBound(C, 2)) = C
Debug.Print Timer - t & " 秒"
End Sub