Sub TEST1()
t = Timer
'検索元のデータを配列に入力
Dim A
A = Range("A2:C10001")
'配列をループ
For i = 1 To UBound(A, 1)
'複数条件に一致した値の合計値を計算
A(i, 3) = WorksheetFunction.SumIfs(Range("G:G"), Range("E:E"), A(i, 1), Range("F:F"), A(i, 2))
Next
'結果をセルに入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
では、VBAコードを実行してみます。
合計値を算出した結果
複数条件に一致する合計値を算出できました。
かかった時間
かかった時間は、「68.90 秒」となりました。
埋め込み数式でSumIfs関数を使ってみる
次は、「埋め込み数式」でSumIfs関数を使ってみます。
Sub TEST2()
t = Timer
'SumIfs関数を数式で埋め込む
Range("C2:C10001") = "=SUMIFS(G:G,E:E,A2,F:F,B2)"
Range("C2:C10001").Value = Range("C2:C10001").Value '値に変換
Debug.Print Timer - t & " 秒"
End Sub
では、VBAコードを実行してみます。
合計値を算出した結果
複数条件に一致した合計値を算出できました。
かかった時間
かかった時間は、「17.72 秒」となりました。
WorksheetFunctionよりは速くなりました。
VBAコードがシンプルなので、なかなか使い勝手がいいです。
ForとIfを組み合わせて複数条件に一致する合計値を算出
次は、「ForとIfを組み合わせて」複数条件に一致する合計値を算出してみます。
この方法が一番遅いです。
実務でこれは使わない方がいいですね。
一応、セルへの入力と参照は配列を使って高速化しています。
Sub TEST3()
t = Timer
Dim A, B
A = Range("A2:C10001") '参照元の値を配列に入力
B = Range("E2:G50001") '参照先の値を配列に入力'参照元の値をループ
For i = 1 To UBound(A, 1)
'参照先の値をループ
For j = 1 To UBound(B, 1)
'「商品」が一致
If A(i, 1) = B(j, 1) Then
'「型番」が一致
If A(i, 2) = B(j, 2) Then
'合計値を加算する
A(i, 3) = A(i, 3) + B(j, 3)
End If
End If
Next
Next
'セルに配列を入力
Range("A2").Resize(UBound(A, 1), UBound(A, 2)) = A
Debug.Print Timer - t & " 秒"
End Sub
では、VBAコードを実行してみます。
合計値を算出した結果
複数条件に一致した値の、合計値を算出できました。
かかった時間
かかった時間は、「119.13 秒」となりました。
VBAコードも長いですし遅いので、実務では使わない方がいいですね。
Dictionaryを使ってSumIfs関数を高速化する
「Dictionary」を使って、SumIfs関数を高速化してみます。
かなり高速に合計値を算出できます。
VBAコード
「Dictionary」を使って、複数条件に一致した値の合計値を算出してみます。
「Dictionary」を使う流れは、
参照元を辞書に登録（区切り文字を使って結合）
参照先をループして辞書に合計値を加算していく
セルに合計値の結果を入力
という感じです。
「Dictionary」で複数条件に一致した合計値を算出
Sub TEST4()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
'辞書に登録する
For i = 2 To 4
'「商品」と「支店」を「"/"」でくっつけて、「キー」に登録
A.Add Cells(i, "A").Value & "/" & Cells(i, "B").Value, 0
Next
'条件一致の合計値を計算
For i = 2 To 10
'既に登録されている場合
If A.exists(Cells(i, "E").Value & "/" & Cells(i, "F").Value) = True Then
'合計値を加算する
A(Cells(i, "E").Value & "/" & Cells(i, "F").Value) = A(Cells(i, "E").Value & "/" & Cells(i, "F").Value) + Cells(i, "G").Value
End If
Next
'セルに配列を入力
Range("C2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
セルへの入力と参照を配列を使ってさらに高速化します。
「配列」を使ってさらに高速化
Sub TEST5()
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B
B = Range("A2:B4") '参照元のデータ'辞書に登録していく
For i = 1 To UBound(B, 1)
'「商品」と「支店」を「"/"」で区切って登録
A.Add B(i, 1) & "/" & B(i, 2), 0
Next
Dim C
C = Range("E2:G10") '参照先のデータ'参照先のループ
For i = 1 To UBound(C, 1)
'既に登録されている場合
If A.exists(C(i, 1) & "/" & C(i, 2)) = True Then
'合計値を加算していく
A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
End If
Next
'セルに配列を入力
Range("C2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
End Sub
最初は、簡単な表で動きを確認してみます。
簡易な表を用意
では、流れをみてみます。
「Dictionary」を使う流れ
最初は、文字列を区切って辞書に登録します。
辞書には、「キー」となる値を1つだけ登録できます。
今回は「複数条件」で一致するかを確認したいので、「文字列を結合」して辞書に登録します。
区切り文字を使わない場合、同じ文字列であると「間違って」認識してしまう可能性があります。
「複数条件」を正確に判定できるように、「区切り文字」を使って結合します。
参照先をループして、「複数の条件」に一致する値を合計値に「加算」していきます。
すべてループしたら、辞書の合計値を配列に入力します。
これで、複数条件に一致する値の合計を算出できます。
結果をみてみます。
複数条件に一致したセルの合計値を算出した結果
「複数条件」に一致したセルの「合計値」を算出できました。
大量データで計測してみる
先ほど作成したVBAコードを、「大量データ」で計測してみます。
Sub TEST6()
t = Timer
'辞書を作成
Dim A
Set A = CreateObject("Scripting.Dictionary")
Dim B
B = Range("A2:B10001") '参照元のデータ'辞書に登録していく
For i = 1 To UBound(B, 1)
'「商品」と「支店」を「"/"」で区切って登録
A.Add B(i, 1) & "/" & B(i, 2), 0
Next
Dim C
C = Range("E2:G50001") '参照先のデータ'参照先のループ
For i = 1 To UBound(C, 1)
'既に登録されている場合
If A.exists(C(i, 1) & "/" & C(i, 2)) = True Then
'合計値を加算していく
A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + C(i, 3)
End If
Next
'セルに配列を入力
Range("C2").Resize(UBound(A.items) + 1) = WorksheetFunction.Transpose(A.items)
Debug.Print Timer - t & " 秒"
End Sub
