VBA COUNTIF関数とCOUNTIFS関数

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

VBAには、ExcelのCOUNTIF関数やCOUNTIFS関数に相当するものはありません。その代わり、Excelの関数を呼び出すには WorksheetFunctionオブジェクトを作成します。

COUNTIF WorksheetFunction

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

Sub TestCountIf()
   Range("D10") = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
End Sub

上記のプロシージャは、Range(D2:D9)のセルが5以上の値である場合のみカウントします。 不等号（大なり記号）を使っているので、5より大きいという条件を記述する場合、引用符で囲む必要があることに注意してください。

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

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

Sub AssignSumIfVariable()
   Dim result as Double
'変数を代入する
   result = Application.WorksheetFunction.CountIf(Range("D2:D9"), ">5")
'結果を表示する
  MsgBox "5より大きい値を持つセルのカウントは" & result
End Sub

COUNTIFSの使用

COUNTIFS関数は、COUNTIF WorksheetFunctionと似ていますが、複数の条件をチェックすることができます。以下の例では、D2 から D9 において、販売価格が 6 より大きく、かつ仕入価格が 5 より大きいセルの数をカウントアップしています。

Sub UsingCountIfs()
   Range("D10") = WorksheetFunction.CountIfs(Range("C2:C9"), ">6", Range("E2:E9"), ">5")
End Sub

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

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

Sub TestCountIFRange()
   Dim rngCount as Range
'セル範囲を代入する
   Set rngCount = Range("D2:D9")
'数式で範囲を使用する
   Range("D10") = WorksheetFunction.SUMIF(rngCount, ">5")
'範囲オブジェクトを解放する
  Set rngCount = Nothing
End Sub

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

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

Sub TestCountMultipleRanges() 
   Dim rngCriteria1 As Range 
   Dim rngCriteria2 as Range
'セルの範囲を割り当てる 
   Set rngCriteria1= Range("D2:D9")
   Set rngCriteria2 = Range("E2:E9")
'数式で範囲を使用する 
   Range("D10") = WorksheetFunction.CountIfs(rngCriteria1, ">6", rngCriteria2, ">5")
'レンジオブジェクトを解放する
   Set rngCriteria1 = Nothing 
   Set rngCriteria2 = Nothing
End Sub

COUNTIF式

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

上の例では、Range(D2:D9)の中で販売価格が6より大きい値を持つセルの数を数え、その結果をD10に書き込んでいます。数式バーを見ればわかるように、この結果は数式ではなく値です。

Range(D2:D9)のいずれかの値が変更されても、D10の結果は変更されません。

WorksheetFunction.SumIfを使う代わりにVBAでCOUNTIF関数をセルに適用するには、以下のようにします。 Formulaまたは FormulaR1C1 メソッドを使用します。

Formulaメソッド

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

Sub TestCountIf()
  Range("D10").FormulaR1C1 ="=COUNTIF(D2:D9, "">5"")".
Sub Sub

FormulaR1C1メソッド

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

Sub TestCountIf()
   Range("D10").FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

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

Sub TestCountIf() 
   ActiveCell.FormulaR1C1 = "=COUNTIF(R[-8]C:R[-1]C,"">5"")"
End Sub

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

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

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