大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、SumIf関数の機能を高速化する方法について、ご紹介します。結果から言うと「Dictionary」を使うと、かなり高速に合計値を計算できます。そこそこの速度で、VBAコードを「シンプル」にしたい場合は「SumIf関数を埋め込む」方法が使えます。

はじめに

この記事では、SumIf関数の機能を高速化する方法について、ご紹介します。

次の4パターンで、比較してみます。

  • WorkhseetFunctionでSumIf関数を使う
  • 数式埋め込みでSumIf関数を使う ←そこそこ速くてシンプル
  • ForとIfで合計値を算出する
  • Dictionaryで合計値を算出する ←かなり高速

結果として、この中で「Dictionary」が一番速くて、かなり高速です。

「数式埋め込み」はVBAコードとしてはシンプルで、そこそこ速いです。

そこそこの速度でいいのであれば「数式埋め込みでSumIf関数」を使って、できるだけ高速化したい場合は「Dictionary」を検討してみましょう。

では、SumIf関数の機能を高速化する方法について、解説していきます。

この記事で紹介すること

  • VBAでSumIf関数の機能を高速化する方法

目次から見たい項目へ移動すると便利ですよ。

目次

WorksheetFunctionでSumIf関数を使ってみる

WorksheetFunctionでSumIf関数を使う方法で、やってみます。

VBAコード

WorksheetFunctionでSumIf関数を使う方法は、こんな感じになります。

WorksheetFunctionでSumIf関数を使う

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のデータ

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のデータ

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

では、参照元が「10,000」件で、参照先が「50,000」件のデータで試してみます。

10,000×50,000のデータ

10,000×50,000のデータ

では、VBAコードをを実行してみます。

条件一致の合計値を算出

条件一致の合計値を算出

条件一致した合計値を算出できました。

かかった時間

実行するのにかかった時間は、「111.02 秒」となりました。

これは、実務では使えないですね。

「ForとIf」でやるよりは「SumIf関数の埋め込み」がいいですね。

Dictionaryを使って条件一致の合計値を算出

「Dictionary」を使って、条件一致の合計値を算出してみます。

この「Dictionary」を使う方法が一番高速です。

VBAコード

Dictionaryは、一意のデータをキーとして登録して、それのペアのデータをアイテムとして登録できます。

Dictionaryを使うメリットは、「登録データの検索」がかなり高速ということです。

「Dictionary」を使って、条件一致の合計値を計算するVBAコードは、こんな感じになります。

Dictionaryを使う

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

セルを操作すると時間がかかってしまうので、セルのデータを「配列」に格納して、可能な限り高速化します。

配列に登録しておいてDictionaryを使う

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

では、「10,000×50,000」で確認してみます。

10,000×50,000のデータ

10,000×50,000のデータ

VBAコードを実行してみます。

条件一致の合計値を算出

条件一致の合計値を算出

条件一致した合計値を算出できました。

かかった時間

実行するのにかかった時間は、「0.19 秒」となりました。

かなり速いです。

これまで一番速かったのが、「SumIf関数を埋め込む」方法で「22.02 秒」でしたので、圧倒的に「Dictionary」が速いです。

ただ、VBAコードがややこしいではあります。

VLookUp関数みたいにSumIf関数も高速になれば一番いいですけどね。

とりあえず現時点では、SumIf関数の機能を高速化したいのであれば、「Dictionary」を使うことになります。

おわりに

この記事では、SumIf関数の機能を高速化する方法について、ご紹介しました。

比較してみた方法は、

  • WorkhseetFunctionでSumIf関数を使う
  • 数式埋め込みでSumIf関数を使う ←そこそこ速くてシンプル
  • ForとIfで合計値を算出する
  • Dictionaryで合計値を算出する ←かなり高速

という4つの方法です。

結果として、「Dictionary」を使えば、SumIf関数の機能を、かなり高速化することができます。

「数式埋め込み」がVBAコードとしてはシンプルで、そこそこ速いです。

そこそこの速度でいいのであれば「数式埋め込みでSumIf関数」を使って、できるだけ高速化したい場合は「Dictionary」を検討してみましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す