VBA sumif関数とsumifs関数

このチュートリアルでは、VBAでExcelのSUMIF関数とSUMIFS関数を使用する方法について説明します。

VBAにはSUMIF関数やSUMIFS関数に相当するものはなく、ユーザーはVBAで ワークシート関数オブジェクトを作成します。

SUMIF WorksheetFunction

WorksheetFunctionオブジェクトは、Excelの「関数の挿入」ダイアログボックス内で利用可能なほとんどのExcel関数を呼び出すために使用することができます。SUMIF関数もその一つです。

Sub TestSumIf()
Range("D10") = Application.WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
End Sub

上記のプロシージャは、C列の対応するセルが150である場合にのみ、Range(D2:D9)のセルを足し算するものです。

SUMIFの結果を変数に代入する

計算式の結果を直接Excelの範囲に書き戻すのではなく、コード内の他の場所で使いたい場合があります。そのような場合は、結果を変数に代入して、後でコードの中で使用することができます。

Sub AssignSumIfVariable()
   Dim result as Double
'変数に代入する
   result = WorksheetFunction.SumIf(Range("C2:C9"), 150, Range("D2:D9"))
'結果を表示する
  MsgBox "販売コード150に一致する結果の合計は" & result
End Sub

SUMIFSの使用

SUMIFS関数は、SUMIF WorksheetFunctionと似ていますが、複数の条件をチェックすることができます。この式では、加算するセル範囲が条件の前にあるのに対し、SUMIF関数では後ろにあることに注意してください。

Sub MultipleSumIfs()
   Range("D10") = WorksheetFunction.SumIfs(Range("D2:D9"), Range("C2:C9"), 150, Range("E2:E9"), ">2")
End Sub

RangeオブジェクトでSUMIFを使用する

Rangeオブジェクトにセル群を代入し、そのRangeオブジェクトをWorksheetFunctionオブジェクトで使用することができます。

Sub TestSumIFRange()
   Dim rngCriteria As Range
   Dim rngSum as Range
'セル範囲を代入する
   Set rngCriteria = Range("C2:C9")
   Set rngSum = Range("D2:D9")
'数式で範囲を使用する
   Range("D10") = WorksheetFunction.SumIf(rngCriteria, 150, rngSum)
'範囲オブジェクトを解放する
  Set rngCriteria = Nothing
  Set rngSum = Nothing
End Sub

複数のレンジオブジェクトでSUMIFSを使用する

同様に、複数のRange Objectに対してSUMIFSを使用することができます。

Sub TestSumMultipleRanges() 
   Dim rngCriteria1 As Range 
   Dim rngCriteria2 as Range
   Dim rngSum as Range
'セル範囲の割り当て 
   Set rngCriteria1= Range("C2:C9")
   Set rngCriteria2 = Range("E2:E9")   
   Set rngSum = Range("D2:D9")
'計算式に範囲を使用する 
Range("D10") = WorksheetFunction.SumIfs(rngSum, rngCriteria1, 150, rngCriteria2, ">2")
'レンジオブジェクトを解放する
  Set rngCriteria1 = Nothing 
  Set rngCriteria2 = Nothing
  Set rngSum = Nothing
End Sub

不等号（大なり記号）を使用して2より大きいという条件を記述する場合、引用符で囲む必要があることに注意してください。

SUMIF 式

WorksheetFunction.SUMIFを 使用してワークシートの範囲の合計を追加すると、数式ではなく、値が入力されます。つまり、Excelの表中の数値が変わっても、WorksheetFunctionが返した値は変わりません。

上の例では、C列にあるSaleCodeが150のRange(D2:D9)を加算し、その結果をD10に配置しています。数式バーを見ればわかるように、この結果は数式ではなく、数字です。 Range(D2:D9)またはRange(C2:D9)のいずれかの値が変更されても、D10の結果は変更されません。 WorksheetFunction.SumIfを使う代わりに、VBAでSUMIF関数をセルに適用するには、以下のようにFormulaまたはFormulaR1C1メソッドを使用します。

Formulaメソッド

以下のように、D2:D10 などのセル範囲を指定することができます。

Sub TestSumIf()
  Range("D10").Formula = "=SUMIF(C2:C9,150,D2:D9)"
End Sub

FormulaR1C1メソッド

FormulaR1C1メソッドは、決められたセル範囲に制限されないという点で、より柔軟性があります。以下の例では、上の例と同じ答えが得られます。

Sub TestSumIf()
   Range("D10").FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

しかし、より柔軟な計算式にするために、次のようなコードに修正することができます。

Sub TestSumIf() 
   ActiveCell.FormulaR1C1 = "=SUMIF(R[-8]C[-1]:R[-1]C[-1],150,R[-8]C:R[-1]C)"
End Sub

ワークシートのどこにいても、数式はその真上の基準を満たすセルを合計し、その答えをActiveCellに配置します。SUMIF関数内のRangeは、Row（R）とColumn（C）の構文で参照する必要があります。

これらの方法は、VBAの中でダイナミックなExcelの数式を使用することを可能にします。

D10には、値の代わりに数式が入ります。