Sub TEST1()
'A～Cをループする
For i = 2 To 4
'SumIfで条件一致したセルの合計値を算出
Cells(i, "B") = WorksheetFunction.SumIf(Range("D:D"), Cells(i, "A"), Range("E:E"))
Next
End Sub
WorksheetFunctionで、SumIfをループして使うという感じです。
セルを操作してしまうと、時間がかかってしまうので、配列を使って少し高速化します。
配列で入力する
Sub TEST2()
Dim A
A = Range("A2:B4") 'A～Cを配列に入力'配列分だけループ
For i = 1 To UBound(A, 1)
'SumIfで条件一致したセルの合計値を算出
A(i, 2) = WorksheetFunction.SumIf(Range("D:D"), A(i, 1), Range("E:E"))
Next
'配列をセルに入力する
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub
先に、A～Cのデータを配列に入力しておきます。
Sumifで条件として入力するのは、「A(i, 1)」のように配列を使います。
入力する際も、配列を使って入力します。
では、実際に実行してみます。
データを用意
では、VBAコードを実行してみます。
条件一致の合計値を算出
条件一致したセルの合計値を算出できました。
では、大量のデータで計測してみます。
大量データで計測してみる
先ほどのVBAコードを、大量データで計測してみます。
先ほどのVBAコードで、参照する範囲を大きくします。
Sub TEST3()
t = Timer
Dim A
A = Range("A2:B10001") '検索元を配列に入力'配列分だけループする
For i = 1 To UBound(A, 1)
'SumIfで条件一致したセルの合計値を算出
A(i, 2) = WorksheetFunction.SumIf(Range("D:D"), A(i, 1), Range("E:E"))
Next
'配列をセルに入力する
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
使うデータは、参照元データが「10,000」件で、参照先のデータが「50,000」件になります。
10,000×50,000のデータ
では、VBAコードを実行してみます。
条件一致の合計値を算出
条件一致した合計値を算出できました。
かかった時間
実行するのにかかった時間は、「58.14 秒」となりました。
ちょっと遅いという感じです。
他の方法も試してみます。
埋め込み数式を使ってSumIf関数を使ってみる
次は、「埋め込み数式」を使ってSumIf関数を使ってみます。
VBAコードとしては、一番シンプルになります。
VBAコード
「埋め込み数式」を使ってSumIf関数を使うVBAコードは、こんな感じになります。
Sub TEST4()
'SumIf関数を埋め込む
Range("B2:B4") = "=SUMIF(D:D,A2,E:E)"
Range("B2:B4").Value = Range("B2:B4").Value '値に変換
End Sub
数式ではなく値がほしいので、最後に値に変換しています。
簡単なデータで動きを確認してみます。
データを用意
では、VBAコードを実行してみます。
条件一致の合計値を算出
条件一致した合計値を算出できました。
では、大量のデータで計測してみます。
大量データで計測してみる
先ほどのVBAコードで、セル範囲を変更して、大量データでSumIf関数を使います。
Sub TEST5()
t = Timer
'SumIf関数を埋め込む
Range("B2:B10001") = "=SUMIF(D:D,A2,E:E)"
Range("B2:B10001").Value = Range("B2:B10001").Value '値に変換
Debug.Print Timer - t & " 秒"
End Sub
参照元データが「10,000」件で、参照先データが「50,000」件のデータで試してみます。
10,000×50,000のデータ
では、VBAコードを実行してみます。
条件一致の合計値を算出
条件一致した合計値を算出できました。
かかった時間
実行するのにかかった時間は、「22.20 秒」となりました。
WorksheetFunctionを使ったときより、少し速くなりました。
ただ、大量データを扱うのには、ちょっと実用的ではないですね。
ForとIfを組み合わせて条件一致の合計値を算出
SumIf関数を使わないで、「ForとIfを組み合わせて」条件一致の合計値を算出してみます。
VBAコード
「ForとIfを組み合わせて」、条件一致の合計値を算出するVBAコードは、こんな感じです。
ForとIfの組み合わせ
Sub TEST6()
'A～Cをループ
For i = 2 To 4
'検索先データをループ
For j = 2 To 10
'検索値に一致した場合
If Cells(i, "A") = Cells(j, "D") Then
'合計値を算出
Cells(i, "B") = Cells(i, "B") + Cells(j, "E")
End If
Next
Next
End Sub
特にセルを入力する際に、時間がかかってしまうので、全体的に配列を使ってVBAコードを修正します。
配列で入力する
Sub TEST7()
Dim A, B
A = Range("A2:B4") '検索元の値を配列に入力
B = Range("D2:E10") '検索先の値を配列に入力'検索元をループ
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) = A(i, 2) + B(j, 2)
End If
Next
Next
'セルに結果を入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
End Sub
流れとしては、
検索元と検索先の値を配列に入力
「ForとIfを組み合わせて」条件一致の合計値を算出
配列をセルに入力
という感じで実行します。
では、簡単なデータデータで確認してみます。
データを用意
VBAコードを実行してみます。
条件一致の合計値を算出
条件一致した合計値を算出できました。
大量データで計測してみます。
大量データで計測してみる
取得するセル範囲を変更しています。
Sub TEST8()
t = Timer
Dim A, B, C
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) = A(i, 2) + B(j, 2)
End If
Next
Next
'セルに配列を入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
Sub TEST9()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
'検索元をループ
For i = 2 To 4
'辞書に登録する
A.Add Cells(i, "A").Value, 0
Next
'検索先をループ
For i = 2 To 10
'辞書に登録されている場合
If A.exists(Cells(i, "D").Value) = True Then
'合計値を算出
A(Cells(i, "D").Value) = A(Cells(i, "D").Value) + Cells(i, "E")
End If
Next
'セルに結果を入力
Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
Sub TEST10()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B, C
B = Range("A2:A4") '検索元の値を配列に入力
C = Range("D2:E10") '検索先の値を配列に入力'検索元をループ
For i = 1 To UBound(B, 1)
'辞書に登録する
A.Add B(i, 1), 0
Next
'検索先をループ
For i = 1 To UBound(C, 1)
'辞書に登録されている場合
If A.exists(C(i, 1)) = True Then
'合計値を算出
A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
End If
Next
'セルに配列を入力
Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
流れとしては、
検索元と検索先の値を配列に入力
検索元データから辞書を作成する
検索先データをループして辞書に値を足していく
セルに結果を入力
という流れです。
では、簡単なデータで確認してみます。
データを用意
VBAコードを実行して、実行する過程をみてみます。
合計値を算出する流れ
最初に、検索元データを辞書に登録します。
実際は配列から辞書に登録しています。
キーを使って辞書のデータを検索して、アイテムに足していきます。
合計値を計算した辞書のデータを、セルに入力します。
これで、条件一致した合計値を算出することができます。
では、VBAコードを実行した結果をみてみます。
条件一致の合計値を算出
条件一致した合計値を算出できました。
大量データで実行して、時間を計測してみます。
大量データで計測してみる
先ほどのVBAコードから、参照元と参照先のセル範囲を変更します。
Sub TEST11()
t = Timer
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B, C
B = Range("A2:A10001") '検索元の値を配列に入力
C = Range("D2:E50001") '検索先の値を配列に入力'検索元をループ
For i = 1 To UBound(B, 1)
'辞書に登録する
A.Add B(i, 1), 0
Next
'検索先をループ
For i = 1 To UBound(C, 1)
'辞書に登録されている場合
If A.exists(C(i, 1)) = True Then
'合計値を算出
A(C(i, 1)) = A(C(i, 1)) + C(i, 2)
End If
Next
'セルに配列を入力
Range("B2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
Debug.Print Timer - t & " 秒"
End Sub
