大体でIT

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

大体でIT

Excel VBAで、SumIfs関数の機能を高速化する方法について、ご紹介します。できるだけ高速化したい場合は「Dictionary」が使えます。そこそこの速度で、VBAコードは「シンプル」がいいという場合は「数式埋め込み」でSumIfs関数を使う方法が簡単です。状況に応じて使い分けていきましょう。

はじめに

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

次の4つの方法で、複数条件に一致する値の合計を計算してみます。

  • WorksheetFunctionでSumIfs関数を使う
  • 数式埋め込みでSumIfs関数を使う ←そこそこ高速でシンプル
  • ForとIfでSumIfs関数の機能を作成
  • DictionaryでSumIfs関数の機能を作成 ←かなり高速

結論から言うと、「Dictionary」を使う方法が一番速いという結果です。

「数式埋め込み」でSumIfs関数を使う方法は、「そこそこ速くて」VBAコードが「シンプル」です。

できるだけ高速化したい場合は「Dictionary」を使って、簡単にそこそこの速さがいいという場合は「数式埋め込み」でSumIfs関数を使うといいです。

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

この記事で紹介すること

  • VBAでSumIfs関数を高速化する方法

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

目次

複数条件に一致する合計値を算出する

複数条件に一致する合計値を算出してみます。

Excel関数で、「SumIfs関数」がありますけど、遅いです。

「SumIfs関数」を使う方法と「ForとIf」でVBAコードを作ってやってみます。

やりたいこと

やりたいことは、複数条件に一致する値の合計値を計算したい、ということです。

大量のデータで確認して、どれぐらい遅いのかを確認してみます。

大量データ

大量データ

大量データを用意しました。

参照元が「10,000」件で、参照先が「50,000」件となっています。

「複数条件」に一致した値の「合計値」を算出

「複数条件」に一致した値の「合計値」を算出

「複数条件」に一致した値の「合計値」を算出します。

遅い例として、

  • WorksheetFunctionでSumIfs関数を使う
  • 数式埋め込みでSumIfs関数を使う
  • ForとIfでSumIfs関数を作る

の3パターンで実行速度を計測してみます。

WorksheetFunctionでSumIfs関数を使ってみる

まずは、WorksheetFunctionでSumIfs関数を使ってみます。

セルへの入力と、セルの参照は配列を使って、できるだけ高速化します。

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

参照元が「10,000」件で、参照先が「50,000」件のデータを用意しておきます。

大量データを用意

大量データを用意

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

複数条件に一致するセルの合計を算出

複数条件に一致するセルの合計を算出

複数条件に一致するセルの合計を算出できました。

かかった時間

かかった時間は、「0.23 秒」です。

これまでで一番速かったのが「数式埋め込み」で「17.72 秒」でしたので、かなり高速です。

ただ、VBAコードを書くのがめんどくさいではあります。

一番いいのは、VLookup関数みたいにSumIfs関数が高速になってくれたらいいですけどね。

とりあえず、現時点では、SumIfs関数をできるだけ高速にしたい場合は、「Dictionary」を使うことになります。

おわりに

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

次の4つの方法で、複数条件に一致する値の合計を計算してみました。

  • WorksheetFunctionでSumIfs関数を使う
  • 数式埋め込みでSumIfs関数を使う ←そこそこ高速でシンプル
  • ForとIfでSumIfs関数の機能を作成
  • DictionaryでSumIfs関数の機能を作成 ←かなり高速

結果は、「Dictionary」を使う方法が一番速いという結果です。

「数式埋め込み」でSumIfs関数を使う方法は、「そこそこ速くて」VBAコードが「シンプル」です。

できるだけ高速化したい場合は「Dictionary」を使って、簡単にそこそこの速さがいいという場合は「数式埋め込み」でSumIfs関数を使うといいです。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す