気づいたらAccessのデータベースが16万件を超えていた
AccessからテーブルをExcelに読み込んで、集計したり、テーブルの重複を判定していました。扱うデータの件数が10万件超えると For ~ Next のループだと処理速度が10分を超えてしまいます。
処理速度を上げるため、連想配列について調べました。
Sub test()
Dim i As Long
Dim dData As New Dictionary
Dim data As Variant
Dim shDB, shSUM As Worksheet
Set shDB = ThisWorkbook.Sheets("データベース")
Set shSUM = ThisWorkbook.Sheets("集計")
data = shDB.Range("A1").CurrentRegion
For i = 1 To UBound(data)
If dData.Exists(data(i, 1)) Then
dData(data(i, 1)) = dData(data(i, 1)) + data(i, 2)
Else
dData.Add data(i, 1), data(i, 2)
End If
Next i
Dim keys As Variant
keys = dData.keys
For i = 1 To UBound(keys)
shSUM.Cells(1 + i, "A").Value = keys(i)
shSUM.Cells(1 + i, "B").Value = dData(keys(i))
Next i
End Sub
実行すると、集計シートに名前ごとのスコアが集計された結果が表示されます。
集計シートのC列には、確認のためSUMIFが入力されています。
なぜこれで動くのかわからない、ウォッチウインドウで配列の中身を確認してみた
dDataにどのタイミングでデータがキーとデータが入るのかを確認するため、
For i = 1 To UBound(data) の行をブレークポイントにして、ウォッチウインドウを準備します。
ステップイン
data = shDB.Range("A1").CurrentRegion 実行直後
dDataはEmpty、dataにはデータベースシートのA列B列の値が格納されています。
If dData.Exists(data(i, 1)) Then
dData(data(i, 1)) = dData(data(i, 1)) + data(i, 2) 実行直後
dDataに異変が
さらに実行
dData.Add data(i, 1), data(i, 2) でひとつずつ格納されていることが理解できました。
言葉で説明されても理解できないので、自分で検証してみました。