VBA COUNT

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

VBAのCOUNT関数は、ワークシートの中で値を持つセルの数を数えるために使用します。VBAのWorksheetFunctionメソッドを使用してアクセスします。

COUNT WorksheetFunction

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

Sub TestCountFunction
  Range("D33") = Application.WorksheetFunction.Count(Range("D1:D32"))
End Sub

COUNT関数には、最大30個の引数を指定することができます。各引数は、セルの範囲を参照する必要があります。 以下の例では、セルD1～D9の値がいくつのセルに格納されているかをカウントします。

Sub TestCount()   
   Range("D10") = Application.WorksheetFunction.Count(Range("D1:D9"))
End Sub

以下の例では、G列の範囲とH列の範囲にいくつ値があるかを数えます。Applicationオブジェクトは省略することができます。

Sub TestCountMultiple()
   Range("G8") = WorksheetFunction.Count(Range("G2:G7"), Range("H2:H7"))
End Sub

カウントした結果を変数に代入する

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

Sub AssignCount()
   Dim result As Integer
'変数に代入する   
   result = WorksheetFunction.Count(Range("H2:H11"))
'結果を表示する
   MsgBox "値が入力されたセルの数は" & result
End Sub

Rangeオブジェクトを使ったCOUNT

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

Sub TestCountRange()
   Dim rng As Range
'セルの範囲を代入する
   Set rng = Range("G2:G7")
'数式で範囲を使用する
   Range("G8") = WorksheetFunction.Count(rng)
'範囲オブジェクトを解放する
   Set rng = Nothing
End Sub

複数のRangeオブジェクトのCOUNT

同様に、複数のRangeオブジェクトに値が入力されているセルの数を数えることができます。

Sub TestCountMultipleRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'セルの範囲を代入する 
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'数式で範囲を使用する 
   Range("E11") = WorksheetFunction.Count(rngA, rngB)
'範囲オブジェクトを解放する
   Set rngA = Nothing 
   Set rngB = Nothing
End Sub

COUNTAの使用

COUNT関数はセル内の数値のみをカウントし、セルにテキストがある場合はカウントされません。何らかのデータが入力されているセルをカウントするには、COUNTA関数を使用する必要があります。

Sub TestCountA()
   Range("B8") = Application.WorksheetFunction.CountA(Range("B1:B6")) 
End Sub

以下の例では、COUNT関数はB列に値がないため0を返し、C列には4を返します。しかし、COUNTA関数は、テキストが入っているセルを数えて、B列では5を返し、C列では4を返します。

COUNTBLANKSの使用

COUNTBLANKS関数は、セル範囲内の空白セル、つまりデータが全く入っていないセルだけを数えます。

Sub TestCountBlank()   
   Range("B8") = Application.WorksheetFunction.CountBlanks(Range("B1:B6")) 
End Sub

以下の例では、B列に空白セルがなく、C列に空白セルが1つあります。

COUNTIF関数の使用

もうひとつ、ワークシートの関数として、COUNTIF関数があります。

Sub TestCountIf()
   Range("H14") = WorksheetFunction.CountIf(Range("H2:H10"), ">0")
   Range("H15") = WorksheetFunction.CountIf(Range("H2:H10"), ">100")
   Range("H16") = WorksheetFunction.CountIf(Range("H2:H10"), ">1000")
   Range("H17") = WorksheetFunction.CountIf(Range("H2:H10"), ">10000")
End Sub

上記のプロシージャは、0より大きい、100より大きい、1000より大きい、10000より大きいという条件に一致する場合のみ、値のあるセルを数えます。 この式が正しく動作するためには、条件を引用符で囲む必要があります。

WorksheetFunctionのデメリット

WorksheetFunction を使用してワークシートの範囲内の値を数える場合、柔軟な数式ではなく、静的な値が返されます。つまり、Excelの数値が変わっても、WorksheetFunctionで返された値は変わりません。

上の例では、TestCountプロシージャがH列で値が存在するセルをカウントアップしています。 数式バーを見ればわかるように、この結果は数式ではなく数値です。

したがって、Range(H2:H12)内のいずれかの値が変化しても、H14の結果は変化しません。

WorksheetFunction.Countを使う代わりに、VBAでFormulaや FormulaR1C1メソッドを使って、セルにカウント関数を適用することができます。

Formulaメソッドの使用

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

Sub TestCountFormula
  Range("H14").Formula = "=Count(H2:H12)"
End Sub

FormulaR1C1 メソッドの使用

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

Sub TestCountFormula()
   Range("H14").Formula = "=Count(R[-9]C:R[-1]C)"
End Sub

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

Sub TestCountFormula() 
   ActiveCell.FormulaR1C1 = "=Count(R[-11]C:R[-1]C) "
End Sub

ワークシートのどこにあっても、数式はその真上の12個のセルの値を数えて、その答えをアクティブセルに入れます。COUNT関数内のRangeは、Row（R）とColumn（C）の構文で参照する必要があります。

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

こうして、H14には値の代わりに数式が入りました。