【VBA】CountIfとCountIfs関数を使いこなす【実務で使えるレベルになります】
Excel VBAで、「CountIf」や「CountIfs」を使う方法について、網羅的に解説していきます。CountIfやCountIfs関数は、条件一致したセルをカウントできる便利な関数です。「WorksheetFunction」や「数式埋め込み」を使って、使いこなしていきましょう。
はじめに
この記事では、VBAで「CountIf関数」と「CountIfs関数」を使う方法について、ご紹介します。
CountIfやCountIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。
カウントした結果をVBAで使いたい場合は、「WorksheetFunction」が便利です。
セルにカウントした結果をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。
VBAで「CountIf」や「CountIfs」を使いこなしていきましょう。
では、VBAで「CountIf関数」と「CountIfs関数」を使う方法について、解説していきます。
この記事で紹介すること
- VBAで「CountIf関数」と「CountIfs関数」を使う方法
目次
- WorksheetFunctionでCountIfやCountIfs関数を使う
- 条件に一致した値のカウント（CountIf関数）
- 複数条件に一致した値のカウント（CountIfs関数）
- ワイルドカードを使う
- 比較演算子を使う
- 最終行まで繰り返し使う
- 別シートを参照する
- 別ブックを参照する
- 埋め込み数式でCountIfやCountifs関数を使う
- 条件に一致した値のカウント（CountIf関数）
- 複数条件に一致した値のカウント（CountIfs関数）
- ワイルドカードを使う
- 比較演算子を使う
- 最終行までの条件一致したセルをカウント
- 別シートを参照する
- 別ブックを参照する
- Dictionaryで高速化する
- おわりに
WorksheetFunctionでCountIfやCountIfs関数を使う
「WorksheetFunction」でCountIfやCountIfs関数を使ってみます。
条件に一致した値のカウント（CountIf関数）
「CountIf関数」は、条件に一致した値のカウントをすることができます。
CountIf関数に入力する引数は、関数の場合と同じで次のようになります。
CountIf関数に入力する引数
'CountIfの引数
A = WorksheetFunction.CountIf(セル範囲, 条件)
という感じです。
では、CountIf関数をVBAで使ってみます。
CountIf関数を使ってみる
Sub TEST1()
'「"B"」をカウント
Range("C2") = WorksheetFunction.CountIf(Range("A:A"), "B")
End Sub
「"B"」を含むセルをカウントします。
表を用意しておきます。
では、VBAコードを実行してみます。
CountIf関数を使って、条件が一致したセルをカウントできました。
「CountIf関数」で「変数」や「セルの値」を参照する方法
「CountIf関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。
実務では、変数を使ってセルの値を参照することが多いです。
複数条件に一致した値のカウント（CountIfs関数）
「CountIfs関数」は、複数条件に一致した値のカウントすることができます。
CountIfs関数に入力する引数は、こんな感じです。
CountIfs関数に入力する引数
'CountIfs関数を使う
A = WorksheetFunction.CountIfs(条件範囲1, 条件1, 条件範囲2, 条件2)
条件は、2つだけでなく自由に増やすことができます。
VBAで、CountIfs関数を使ってみます。
CountIfs関数を使ってみる
Sub TEST2()
'商品が「B」で、支店が「東京」をカウント
Range("D2") = WorksheetFunction.CountIfs(Range("A:A"), "B", Range("B:B"), "東京")
End Sub
A列が「"B"」で、B列が「"東京"」のセルをカウントします。
表を用意しておきます。
では、VBAコードを実行してみます。
CountIfs関数を使って、複数条件に一致したセルをカウントできました。
「CountIfs関数」で「変数」や「セルの値」を参照する方法
「CountIfs関数」で「変数」や「セルの値」を参照する方法について、詳細はこちらでまとめています。
実務では、変数を使ってセルの値を参照することが多いです。うまく使いこなしましょう。
ワイルドカードを使う
CountIfやCountIfs関数は、ワイルドカードを使うことができます。
ワイルドカードの種類は、2つあります。
ワイルドカードの種類
- 「*」：すべての値
- 「?」：すべての値。ただし1文字
「CountIf関数」でワイルドカードを使ってみます。
「CountIf関数」でワイルドカードを使う
「"B"を含む」セルをカウントしてみます。
Sub TEST3()
'「"B"」を含むセルをカウント
Range("C2") = WorksheetFunction.CountIf(Range("A:A"), "*B*")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIf関数」でワイルドカードを使ってセルをカウントできました。
「CountIfs関数」でワイルドカードを使う
「CountIfs関数」でワイルドカードを使ってみます。
商品が「Bを含む」値で、支店が「東京」をカウントします。
Sub TEST4()
'商品が「Bを含む」値で、支店が「東京」をカウント
Range("D2") = WorksheetFunction.CountIfs(Range("A:A"), "B*", Range("B:B"), "東京")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIfs関数」でワイルドカードを使ってセルをカウントできました。
比較演算子を使う
CountIfとCountIfs関数は、「比較演算子」も使えます。
比較演算子の種類は、次のとおりです。
比較演算子の種類
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
「CountIf関数」で比較演算子を使ってみます。
「CountIf関数」で比較演算子を使う
「5以上」のセルをカウントしてみます。
Sub TEST5()
'「5以上」のセルをカウント
Range("C2") = WorksheetFunction.CountIf(Range("A:A"), ">=5")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIf関数」で比較演算子を使ってセルをカウントできました。
「CountIfs関数」で比較演算子を使う
「CountIfs関数」で比較演算子を使ってセルをカウントしてみます。
「2021/8/1」～「2021/9/3」であるセルをカウントします。
Sub TEST6()
'「2021/8/1」～「2021/9/3」をカウント
Range("C2") = WorksheetFunction.CountIfs(Range("A:A"), ">=2021/8/1", Range("A:A"), "<=2021/9/3")
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIfs関数」で比較演算子を使ってセルをカウントできました。
最終行まで繰り返し使う
「WorksheetFunction」で最終行まで繰り返し「CountIf関数」を使ってみます。
最終行まで繰り返し使う
Sub TEST7()
'最終行までループする
For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
'カウントする
With Cells(i, "C")
.Offset(0, 1) = WorksheetFunction.CountIf(Range("A:A"), .Value)
End With
Next
End Sub
表を用意しておきます。
「End」を使って最終行を取得できます。
では、VBAコードを実行した結果をみてみます。
最終行まで繰り返し使って、カウントできました。
最終行までループする方法の詳細
「CountIf関数」を使って最終行までループする方法について、詳細な手順はこちらでまとめています。
別シートを参照する
「WorksheetFunction」で「CountIf関数」を使って「別シート」を参照してみます。
別シートを参照する
別シートの参照は「Worksheets」を使います。
Sub TEST8()
'別シートの合計値を算出
With Worksheets("Sheet2")
Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
End With
End Sub
別シートを用意しておきます。
では、VBAコードを実行してみます。
別シートを参照して、条件一致のセルをカウントできました。
すべての別シートをループする
「For Each」やシート数を「Worksheets.Count」で取得すると、すべての別シートを参照することができます。
すべての別シートを「CountIf関数」で参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
「WorksheetFunction」で「CountIf関数」を使って「別ブック」を参照してみます。
別ブックを参照する
「別ブック」を参照するには、「Workbooks」を使います。
Sub TEST9()
'「別ブック」を参照して条件一致の数を算出
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
End With
End Sub
フォルダ構成は、こんな感じで、同じフォルダ内に、「別ブック」を保存しておきます。
別ブックを開いておく、というのがポイントです。
では、VBAコードを実行してみます。
別ブックを参照して、条件一致のセルをカウントできました。
別ブックを「開いて」参照する方法
別ブックを参照する場合は、「別ブックを開く」VBAコードを追加して、参照すると便利です。
別ブックを「開いて」参照する方法について、詳細はこちらでまとめています。
埋め込み数式でCountIfやCountifs関数を使う
「埋め込み数式」でCountIfやCountifs関数を使ってみます。
数式埋め込みは、入力する内容が直感的にわかるので、扱いやすいです。
条件に一致した値のカウント（CountIf関数）
「数式埋め込み」で「CountIf関数」を使って条件に一致した値のカウントしてみます。
埋め込み数式で「CountIf関数」を使う
埋め込み数式で「CountIf関数」を使ってみます。
「"B"」のような文字列は、「"」で囲ってあげて「""B""」というようにするのがポイントです。
Sub TEST10()
'「"B"」をカウント
Range("C2") = "=COUNTIF(A:A,""B"")"
Range("C2").Value = Range("C2").Value '値に変換
End Sub
数式ではなく「値」にしたいので、最後に値に変換しています。
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込み数式で「CountIf関数」を使って条件一致のセルをカウントできました。
埋め込み数式を使う場合は、セルに入力した数式を「コピー」して使うと簡単です。
「CountIf関数」で「変数」や「セル」の値を使って参照する
実務では、「変数」や「セル」の値を参照して、「CountIf関数」を使うことが多いです。
「変数」や「セル」の値を使って、「CountIf関数」を使う方法について、詳細はこちらでまとめています。
複数条件に一致した値のカウント（CountIfs関数）
埋め込み数式で、「CountIfs関数」を使って、複数条件に一致した値のカウントしてみます。
埋め込み数式で「CountIfs関数」を使う
埋め込み数式で「CountIfs関数」を使ってみます。
商品が「B」で、支店が「東京」をカウントします。
Sub TEST11()
'商品が「B」で、支店が「東京」をカウント
Range("D2") = "=COUNTIFS(A:A,""B"",B:B,""東京"")"
Range("D2").Value = Range("D2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
埋め込み数式で「CountIfs関数」を使って、複数条件に一致したセルのカウントができました。
埋め込み数式を使う場合は、セルに入力した数式を「コピー」して使うと簡単です。
「CountIfs関数」で「変数」や「セル」の値を使って参照する
実務では、「変数」や「セル」の値を参照して、「CountIfs関数」を使うことが多いです。
「変数」や「セル」の値を使って、「CountIfs関数」を使う方法について、詳細はこちらでまとめています。
ワイルドカードを使う
CountIfやCountIfs関数は、ワイルドカードを使うことができます。
ワイルドカードの種類は、2つあります。
ワイルドカードの種類
- 「*」：すべての値
- 「?」：すべての値。ただし1文字
という感じです。
「CountIf関数」でワイルドカードを使ってみます。
「CountIf関数」でワイルドカードを使う
数式埋め込みを使って「"B"」を含むセルをカウントしてみます。
Sub TEST12()
'「"B"」を含むセルをカウント
Range("C2") = "=COUNTIF(A:A,""*B*"")"
Range("C2").Value = Range("C2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIf関数」でワイルドカードを使って、セルをカウントできました。
「CountIfs関数」でワイルドカードを使う
「CountIfs関数」でワイルドカードを使ってみます。
商品が「Bを含む」値で、支店が「大阪」の数をカウントしてみます。
Sub TEST13()
'商品が「Bを含む」値で、支店が「大阪」の数をカウント
Range("D2") = "=COUNTIFS(A:A,""B*"",B:B,""東京"")"
Range("D2").Value = Range("D2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIfs関数」でワイルドカードを使って、セルをカウントできました。
比較演算子を使う
CountIfとCountIfs関数は、「比較演算子」も使えます。
比較演算子の種類は、次のとおりです。
比較演算子の種類
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
という感じです。
「CountIf関数」で比較演算子を使う
数式埋め込みで「5以上」のセルをカウントしてみます。
Sub TEST14()
'「5以上」のセルをカウント
Range("C2") = "=COUNTIF(A:A,"">=5"")"
Range("C2").Value = Range("C2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIf関数」で比較演算子を使って、セルをカウントできました。
「CountIfs関数」で比較演算子を使う
「CountIfs関数」で比較演算子を使ってみます。
数式を埋め込んで、「2021/8/1」～「2021/9/3」のセルをカウントしてみます。
Sub TEST15()
'「2021/8/1」～「2021/9/3」をカウント
Range("C2") = "=COUNTIFS(A:A,"">=2021/8/1"",A:A,""<=2021/9/3"")"
Range("C2").Value = Range("C2").Value '値に変換
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「CountIfs関数」で比較演算子を使って、セルをカウントできました。
最終行までの条件一致したセルをカウント
最終行までの条件一致したセルをカウントしてみます。
最終行までの条件一致したセルをカウント
数式埋め込みの場合は、最終行までの「セル範囲」を指定して入力します。
Sub TEST16()
'最終行を取得
A = Cells(Rows.Count, "C").End(xlUp).Row
'A～Cの項目をカウントする
Range("D2:D" & A) = "=COUNTIF(A:A,C2)"
Range("D2:D" & A).Value = Range("D2:D" & A).Value '値に変換
End Sub
表を用意しておきます。
「End」を使えば「最終行」を取得できます。
では、VBAコードを実行してみます。
最終行までの条件一致したセルをカウントできました。
数式埋め込みだとループすることなく、最終行までカウントすることができます。
最終行までのセルをカウントする方法の手順
最終行までのセルをカウントする方法の手順について、詳細はこちらでまとめています。
順を追って解説しています。
別シートを参照する
数式埋め込みの方法で、「別シート」を参照してみます。
別シートを参照する
別シートを参照するには、「Sheet2!A:A」というように「シート名」まで入力することでできます。
Sub TEST17()
'別シートの合計値を算出
Worksheets("Sheet1").Cells(2, "A") = "=COUNTIF(Sheet2!A:A,""B"")"
Worksheets("Sheet1").Cells(2, "A").Value = Worksheets("Sheet1").Cells(2, "A").Value '値に変換
End Sub
別シートを用意しておきます。
では、VBAコードを実行してみます。
別シートを参照して、セルをカウントできました。
すべてのシートを参照する
実務では1つのシートだけではなく、複数のシートを参照することが多いです。
CountIf関数の埋め込み数式で、すべてのシートをループして参照することができます。
埋め込み数式ですべてのシートをループして参照する方法について、詳細はこちらでまとめています。
別ブックを参照する
数式を埋め込む方法で、「別ブック」を参照してみます。
別ブックを参照する
別ブックを参照するには、「[TEST.xlsx]Sheet1!A:A」というように「ブック名」まで入力することでできます。
Sub TEST18()
'「別ブック」を参照して条件一致の数を算出
With ThisWorkbook.Worksheets("Sheet1")
.Cells(2, "A") = "=COUNTIF([TEST.xlsx]Sheet1!A:A,""B"")"
.Cells(2, "A").Value = .Cells(2, "A").Value '値に変換
End With
End Sub
フォルダ構成は、こんな感じで同フォルダに「別ブック」を保存しています。
ポイントは、「別ブックを開いておく」ということです。
では、VBAコードを実行してみます。
別ブックを参照して、セルをカウントできました。
別ブックを「開いて」参照する方法と「閉じたまま」参照する
別ブックを参照する際は、「ブックを開く」VBAコードを追加すると便利です。
「COUNT関数」と「If関数」を組み合わせると閉じたまま参照することもできます。
別ブックを「開いて」参照する方法と「閉じたまま」参照する方法について、詳細はこちらでまとめています。
Dictionaryで高速化する
「Dictionary」で「CountIf関数」と「CountIfs関数」を高速化してみます。
CountIf関数を高速化する
CountIf関数を「Dictionary」で高速化してみます。
「Dictionary」でCountIf関数を高速化
手順としては、
- 「カウント元」と「カウント先」の値を配列に入力
- 「カウント元」の値を「Dictionary」に登録
- 「カウント先」をループして「キー」で検索して「アイテム」をカウントアップ
- 「アイテム」をセルに入力
という感じです。
Sub TEST19()
t = Timer
Dim A
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
Dim B, C
B = Range("A2:A10001") 'カウント元の値を取得
C = Range("D2:D50001") 'カウント先の値を取得
'カウント元をループ
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)) Then
A(C(i, 1)) = A(C(i, 1)) + 1 'カウントアップ
End If
Next
'配列をセルに入力
Range("B2").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
Debug.Print Timer - t & " 秒"
End Sub
大量データを用意しておきます。
では、VBAコードを実行してみます。
「Dictionary」でCountIf関数を作成できました。
かかった時間は、「0.20 秒」です。
かなり高速ですね。
数式埋め込みの場合は「21.88281 秒」かかるので、「Dictionary」を使うとかなり高速にカウントできていることがわかります。
「Dictionary」でCountIfを高速化する手順の解説
「Dictionary」を使ってCountIfの機能を高速化する手順について、詳細はこちらで解説しています。
CountIfs関数を高速化する
次は、「CountIfs関数」を「Dictionary」で高速化してみます。
「Dictionary」でCountIfs関数を高速化
手順としては、
- 「カウント元」と「カウント先」の値を配列に入力
- 「カウント元」の値を「文字列結合」して「Dictionary」に登録
- 「カウント先」をループして「キー」で検索して「アイテム」をカウントアップ
- 「アイテム」をセルに入力
という感じです。
検索する複数の条件を「文字列結合」するところがポイントです。
Sub TEST20()
t = Timer
Dim A
'辞書を作成
Set A = CreateObject("Scripting.Dictionary")
Dim B, C
B = Range("A2:B10001") 'カウント元の値を取得
C = Range("E2:F50001") 'カウント先の値を取得
'カウント元をループ
For i = 1 To UBound(B, 1)
'カウント元を辞書に登録
A.Add B(i, 1) & "/" & B(i, 2), 0 '←文字列を結合するのがポイント
Next
'カウント先をループ
For i = 1 To UBound(C, 1)
'辞書に登録されている場合
If A.exists(C(i, 1) & "/" & C(i, 2)) Then '←2つの文字を結合するのがポイント
A(C(i, 1) & "/" & C(i, 2)) = A(C(i, 1) & "/" & C(i, 2)) + 1 'カウントアップ
End If
Next
'配列をセルに入力
Range("C2").Resize(A.Count) = WorksheetFunction.Transpose(A.items)
Debug.Print Timer - t & " 秒"
End Sub
大量データを用意しておきます。
では、VBAコードを実行してみます。
「Dictionary」でCountIfs関数を作成できました。
かかった時間は、「0.23 秒」です。
こちらもかなり高速です。
数式埋め込みの場合は「66 秒」かかるので、「Dictionary」を使えばかなり高速でカウントできることがわかります。
「Dictionary」でCountIfsを高速化する手順の解説
「Dictionary」を使ってCountIfsの機能を高速化する手順について、詳細はこちらで解説しています。
おわりに
この記事では、VBAで「CountIf関数」と「CountIfs関数」を使う方法について、ご紹介しました。
CountIfやCountIfs関数を使う方法は、「WorksheetFunction」と「数式を埋め込む方法」があります。
カウントした結果をVBAで使いたい場合は、「WorksheetFunction」が便利です。
セルにカウントした結果をそのまま入力したいのであれば、「数式を埋め込む方法」が簡単です。
VBAで「CountIf」や「CountIfs」を使いこなしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す