EXCEL VBA COUNTIFS関数　複数条件に一致するデータのセル数をカウントする。（条件に合うデータの件数集計・ワークシート関数）

2021年11月13日

EXCEL VBA COUNTIFS関数　複数条件に一致するデータのセル数をカウントする。（条件に合うセルデータの件数集計・ワークシート関数）

●はじめに

今回説明するのは、Countifs 関数の利用方法を説明いたします。Countifs関数は、指定したセル範囲内のデータに対して、条件に一致するセル数のカウントを行います。Countifs関数では、複数条件を指定する事ができるので、複雑のデータも一致する条件のセル数（該当）をカウントする事がでます。それでは、サンプルプログラムを交えて順番に説明いたします。

●【SUMIF関数の使い方については、下記の情報を参照して下さい】

EXCEL VBA 条件に合う集計・一致する集計・合計の集計「Sumif・Sumifs」の使い方

●【WorksheetFunction Countifsメソッド (Excel)、下記の参照して下さい】
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.countifs

●書式の説明　WorksheetFunction（ワークシート関数）

●　Countifs関数を利用するには、下記の通りに設定を行います。

【構文】
●　WorksheetFunction.CountIfs( セル範囲1, 条件1)　　：　単一条件
●　WorksheetFunction.CountIfs( セル範囲1, 条件1, セル範囲2, 条件2 ・・・・ )　：　複数条件

・条件の指定は、最大127組のセル範囲と条件を指定する事ができます。

【使用例】
・下記のサンプルプログラムは、Countifs関数により、複数条件に一致する件数を買カウントするサンプルプログラムです。国語・数学・英語と3科目全てが60点以上ならば、合格として合格者数をカウントします。

'
'
Sub Countifs01()  '国語・数学・英語の点数が６０点以上の人数
    
    Dim Man As Long

    Man = WorksheetFunction.CountIfs(Range("B2:B7"), ">=60", Range("C2:C7"), ">=60", Range("D2:D7"), ">=60")
    MsgBox "合格者数　" & Man & " 人"

End Sub
'

Sub Countifs01() '国語・数学・英語の点数が６０点以上の人数

Dim Man As Long

Man = WorksheetFunction.CountIfs(Range("B2:B7"), ">=60", Range("C2:C7"), ">=60", Range("D2:D7"), ">=60")

MsgBox "合格者数　" & Man & " 人"

End Sub

EXCEL VBA　検索条件・複数条件の設定例（以上・以下・未満・超える・ワイルドカード）

●検索条件

検索条件は、様々な方法で該当するデータの件数を把握する事ができます。下記に様々な条件を記述しましたので、参考にして下さい。

【条件の設定例】

条件設定（記述例）	条件説明（抽出内容）
"=100"	100と等しい
"<>100"	100と等しくない
">=100"	100以上
"<=100"	100以下
">100"	100を超える
"<100"	100未満
""	空白のセル（スペースも含まれない）
"<>"	空白のセル以外
”東京”	[東京]が含まれる
"<>東京”	[東京]が含まれない

【ワイルドカードの設定例】

条件設定（記述例）	条件説明（抽出内容）
”東京”	[東京]が含まれる
"<>東京”	[東京]が含まれない
"東京?"	[東京] +任意1文字
"東京＊"	[東京]＋0文字以上の文字列
"?東京"	任意1文字＋[東京]
”??東京???	任意2文字+[東京] +任意3文字
"東京??＊"	[東京]＋2文字以上の文字列

EXCEL VBA COUNTIFS関数　複数条件に一致するデータのセル数をカウントする。（ワイルドカードを使用した条件設定・複数条件）

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、Countifs関数を利用して複数条件に一致するデータのセル数をカウントするサンプルプログラムです。今回のサンプルプログラムでは、社員データからカウント条件を指定して、J列に区市町村ごとに該当人数をカウントします。

【カウント条件】
①　F列：　条件として、F列（区→市→町→村）
②　D列：　35歳以上
③　C列：　男

【プログラムの流れ】
①　I列の最終行を取得します。
②　I列の最終行まで繰り返す（2行目～最終行まで）
③　I列の区市町村を順番に代入します。
④　集計（カウント）条件として、F列（区→市→町→村）・D列（35歳以上）・C列（男）で結果をJ列に代入

【プログラム実行条件】
①　I列に区市町村をセルに入力
②　A列～F列（社員番号・氏名・性別・年齢・郵便番号・住所）を入力します（上記表参照）

'
'
Sub Countifs01A()    '複数条件・ワイルドカード
        
    Dim I, lRow As Long
    Dim add As String
    
    With ActiveSheet
    
        lRow = .Cells(Rows.Count, "I").End(xlUp).Row   'I列の最終行を取得します。
      
        For I = 2 To lRow  'I列の最終行まで繰り返す。（2～最終行）
            
            add = .Cells(I, "I")  'I列の区市町村を順番に代入します。
            .Cells(I, "J") = WorksheetFunction.CountIfs(.Range("F:F"), "*" & add, .Range("D:D"), ">=35", .Range("C:C"), "男")
            '集計（カウント）条件として、F列（区→市→町→村）・D列（35歳以上）・C列（男）で結果をJ列に代入
        Next I

    End With

End Sub
'

Sub Countifs01A() '複数条件・ワイルドカード

Dim I, lRow As Long

Dim add As String

With ActiveSheet

lRow = .Cells(Rows.Count, "I").End(xlUp).Row 'I列の最終行を取得します。

For I = 2 To lRow 'I列の最終行まで繰り返す。（2～最終行）

add = .Cells(I, "I") 'I列の区市町村を順番に代入します。

.Cells(I, "J") = WorksheetFunction.CountIfs(.Range("F:F"), "*" & add, .Range("D:D"), ">=35", .Range("C:C"), "男")

'集計（カウント）条件として、F列（区→市→町→村）・D列（35歳以上）・C列（男）で結果をJ列に代入

Next I

End With

End Sub

●実行前～実行後　※プログラム実行後、指定した複数条件でカウントされた該当人数が結果としてJ列に代入されました。
（画面クリックして拡大）

EXCEL VBA COUNTIFS関数　重複データのセル数をカウントする。

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、Countifs関数を利用して重複データのセル数をカウントするサンプルプログラムです。社員データ内にメールアドレス一覧が表示されています。このメールアドレスに重複データが有るか無いかを調べます。

【カウント条件】
①　D列：　条件として、D列のメールアドレスを順番に重複していないか確認する。

【プログラムの流れ】
①　A列の最終行を取得します。
②　A列の最終行まで繰り返す（2行目～最終行まで）
③　D列のメールアドレスを比較して重複データが1件を超えるか確認します。同じデータが2件以上あると重複している事になります。
④　重複データの場合は、G列に「データが重複」を表示します。

【プログラム実行条件】
①　D列にメールアドレス一覧を作成します。（A列～F列に上記一覧データを作成します。）

'
'
Sub Countifs02()     '登録されているメールアドレスを比較して重複しているか調べます。


    Dim I, lRow As Long
    
    With ActiveSheet
    
        lRow = .Cells(Rows.Count, "A").End(xlUp).Row   'A列の最終行を取得します。
    
            For I = 2 To lRow 'A列の最終行まで繰り返します。
    
                If WorksheetFunction.CountIf(.Range("D:D"), .Cells(I, "D")) > 1 Then
                    'D列のメールアドレス（全て）とにD列のメールアドレスを順番に比較すます。
                    .Cells(I, "G") = "データが重複"
                    '１を超える件数でデータ重複判定
                End If
        Next I
    End With
    
End Sub
'

Sub Countifs02() '登録されているメールアドレスを比較して重複しているか調べます。

Dim I, lRow As Long

With ActiveSheet

lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

For I = 2 To lRow 'A列の最終行まで繰り返します。

If WorksheetFunction.CountIf(.Range("D:D"), .Cells(I, "D")) > 1 Then

'D列のメールアドレス（全て）とにD列のメールアドレスを順番に比較すます。

.Cells(I, "G") = "データが重複"

'１を超える件数でデータ重複判定

End If

Next I

End With

End Sub

●実行前～実行後　※プログラム実行後、D列のメールアドレスを確認して、重複されているメールアドレスに対して、G列に「データが重複」のメッセージが表示されました。
（画面クリックして拡大）

EXCEL VBA COUNTIFS関数　複数セルに対して空白のセル・空白以外のセル（文字列入力セル）をカウントする。

●プログラム説明　(サンプル③)

下記のサンプルプログラムは、Countifs関数を利用して複数セルA列・B列（行単位）に対して、空白のセル・空白以外のセル（文字列入力セル）・A列・B列のいずれかが空白のセルをカウントするサンプルプログラムです。

【カウント条件】
①　A列の【氏名】とB列の【出身地】共に空白のセルをカウントする。　　⇒結果をセルE１
②　A列の【氏名】とB列の【出身地】共に空白以外（文字列入力）のセルをカウントする。　⇒結果をセルE3
③　A列の【氏名】とB列の【出身地】のいずれかの空白セルをカウントする。　⇒結果をセルE２

【プログラムの流れ】
①　A列の最終行を取得します。
②　A列の「氏名」・B列の「出身地」共に空白のセルをカウントし結果をセルE1へ代入
③　A列の「氏名」・B列の「出身地」共に文字列が入力されているセルをカウントし結果をセルE3へ代入
④　A列の「氏名」・B列の「出身地」のいずれかに文字列が入力されているセルをカウントします。
⑤　片側に入力されているカウント数をセルE2へ代入します。

'
'
Sub Countifs03()     '空白セルのカウント及び文字入力されているセルをカウントします。
    
    Dim lRow, Con01, Con02 As Long
    
    With ActiveSheet
    
    lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得
    

        .Cells(1, "E") = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "", .Range("B2:B" & lRow), "")
        'A列の「氏名」・B列の「出身地」共に空白のセルをカウントし結果をセルE1へ代入
        
        .Cells(3, "E") = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "<>", .Range("B2:B" & lRow), "<>")
        'A列の「氏名」・B列の「出身地」共に文字列が入力されているセルをカウントし結果をセルE3へ代入
        
        Con01 = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "", .Range("B2:B" & lRow), "<>")
        Con02 = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "<>", .Range("B2:B" & lRow), "")
         'A列の「氏名」・B列の「出身地」のいずれかに文字列が入力されているセルをカウントします。
           
        .Cells(2, "E") = Con01 + Con02
        '片側に入力されているカウント数をセルE2へ代入します。
    
    End With

End Sub
'

Sub Countifs03() '空白セルのカウント及び文字入力されているセルをカウントします。

Dim lRow, Con01, Con02 As Long

With ActiveSheet

lRow = .Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得

.Cells(1, "E") = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "", .Range("B2:B" & lRow), "")

'A列の「氏名」・B列の「出身地」共に空白のセルをカウントし結果をセルE1へ代入

.Cells(3, "E") = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "<>", .Range("B2:B" & lRow), "<>")

'A列の「氏名」・B列の「出身地」共に文字列が入力されているセルをカウントし結果をセルE3へ代入

Con01 = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "", .Range("B2:B" & lRow), "<>")

Con02 = WorksheetFunction.CountIfs(.Range("A2:A" & lRow), "<>", .Range("B2:B" & lRow), "")

'A列の「氏名」・B列の「出身地」のいずれかに文字列が入力されているセルをカウントします。

.Cells(2, "E") = Con01 + Con02

'片側に入力されているカウント数をセルE2へ代入します。

End With

End Sub

●実行前～実行後　※プログラム実行後、A列【氏名】・B列【出身地】を元に、D列に記載しているカウント条件結果をE列に結果（カウント数）が転記されました。
（画面クリックして拡大）

EXCEL VBA COUNTIFS関数を使いクロス集計表を作成（一覧データからマトリックス表・複数条件）

●プログラム説明　(サンプル③)

下記のサンプルプログラムは、Countifs関数を利用してクロス集計表を作成するサンプルプログラムです。シート「一覧」には、社員データとして、D列に都道府県・E列に区市町村名が登録されています。この都道府県×区市町村のデータを使い、シート「クロス集計」へ該当する都道府県×区市町村のデータを集計（カウント）するサンプルプログラムです。この方法を使う事により、簡単に縦横のクロス集計（カウント）する事ができます。

【カウント条件】
①　シート「一覧」D列の【都道府県】とE列の【区市町村】データを元に、シート「クロス集計」の縦「都道府県」×横「区市町村」毎にカウントする。⇒結果を該当セルに記入する。

【プログラムの流れ】
①　シート「クロス集計」A列の最終行を取得します。（都道府県）
②　シート「クロス集計」1行目の最終列を取得します。（区市町村）
③　シート「クロス集計」A列の最終行まで繰り返す（東京⇒神奈川⇒埼玉・・・）
④　シート「クロス集計」1行名の最終列まで繰り返す（区⇒市⇒町⇒村）
⑤　シート「一覧」のD列「都道府県」からシート「クロス集計」のA列「都道府県」を１列目の「区市町村」に一致するデータをカウントして、値を該当セルに代入します。

'
'
Sub Countifs04()      'データ一覧からクロス集計を作成する。


    Dim ws01, ws02 As Worksheet
    Dim lRow, Lcol As Long
    
    Set ws01 = Worksheets("一覧")
    Set ws02 = Worksheets("クロス集計")
    
    Dim I, L As Long
    
    lRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row  'シート「クロス集計」A列の最終行を取得します。
    Lcol = ws02.Cells(1, Columns.Count).End(xlToLeft).Column  'シート「クロス集計」1行目の最終列を取得します。

    For I = 2 To lRow  'シート「クロス集計」A列の最終行まで繰り返す（東京⇒神奈川⇒埼玉・・・）
        For L = 2 To Lcol 'シート「クロス集計」1行名の最終列まで繰り返す（区⇒市⇒町⇒村）

            Cells(I, L) = WorksheetFunction.CountIfs(ws01.Range("D:D"), ws02.Cells(I, "A"), ws01.Range("E:E"), "*" & ws02.Cells(1, L))
            'シート「一覧」のD列「都道府県」からシート「クロス集計」のA列「都道府県」を１列目の「区市町村」に一致するデータをカウントして、値を該当セルに代入します。
        Next L
    Next I
    
End Sub
'