ExcelのCOUNTIFS関数の使い方|複数条件に一致するデータを数える
- Release
- Update
ExcelのCOUNTIFS関数は、指定した範囲の中で複数の検索条件に一致するセルがいくつあるかを求める関数です。
COUNTIFS関数を使えば特定の文字列や日付を含むセルの個数を数えたり、空白以外のセルの個数を数えたりできます。
以下の記事では、COUNTIFS関数の基本的な使い方をご紹介していきます。
COUNTIFS関数とは?
COUNTIFS関数は、指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。
読み方
カウントイフズ/カウントイフス/カウントイフエス
Excelの対応バージョン
Excel 2007以降のバージョンで使用できます。
※Excel 2003以前では使用できません。
COUNTIFS関数の書式
まずは、COUNTIFS関数の書式を確認していきましょう。
![COUNTIFS関数の書式](../../wp-content/uploads/countifs01.png)
COUNTIFS関数の書式は「=COUNTIFS(条件範囲1, 検索条件1, [条件範囲2, 検索条件2], …)」のように記述します。
COUNTIFS関数では「条件範囲」と「検索条件」の組み合わせを最大127個まで指定することができます。
引数名 | 説明 |
---|---|
条件範囲1 (必須) | 検索の対象とするセルやセルの範囲を指定します。数値や配列、名前付き範囲、参照が指定できます。 |
検索条件1(必須) |
「条件範囲1」の中からセルを検索するための条件を、数値、式、セル範囲、または文字列で指定します。文字列を指定する場合は、「"(ダブルクォーテーション)」で囲みます。 |
条件範囲2 |
追加したい検索の対象とするセルやセルの範囲を指定します。 |
検索条件2 |
「条件範囲2」の中からセルを検索するための条件を、数値、式、セル範囲、または文字列で指定します。 |
第1引数(条件範囲1)
![COUNTIFS関数の条件範囲1](../../wp-content/uploads/countifs02.png)
1番目の引数は「条件範囲1」です。この引数は必須です。
検索の対象とするセルやセルの範囲を指定します。数値や配列、名前付き範囲、参照が指定できます。
第2引数(検索条件1)
![COUNTIFS関数の検索条件1](../../wp-content/uploads/countifs03.png)
2番目の引数は「検索条件1」です。この引数は必須です。引数「条件範囲1」の中からセルを検索するための条件を数値、式、セル範囲、または文字列で指定します。
文字列を指定する場合は、「"(ダブルクォーテーション)」で囲みます。
検索条件で式を指定する際に比較演算子を使用することができます。検索条件で使用できる比較演算子は下記の表をご覧ください。
条件式 | 意味 | 読み方 |
---|---|---|
> 1 | 1よりも大きい | 大なり(だいなり) |
>= 1 | 1以上 | 大なりイコール |
< 1 | 1よりも小さい | 小なり(しょうなり) |
<= 1 | 1以下 | 小なりイコール |
= 1 | 1と等しい(1と同じ) | 等号、イコール |
<> 1 | 1と等しくない(1以外) | 山カッコ、大なり小なり |
検索条件ではワイルドカードも使用することが可能です。検索条件で使用できるワイルドカードは下記の表をご覧ください。
パターン | 意味 | 使用例 |
---|---|---|
* (アスタリスク) |
1文字以上の任意の文字列 | "A*"・・・「A」で始まる文字列 "*A*"・・・「A」を含む文字列 "*A"・・・「A」で終わる文字列 |
? (疑問符) |
任意の1文字 | "A?"・・・「A」で始まる2文字 "?A?"・・・「A」を含む3文字 "?A"・・・「A」で終わる2文字 |
~ (チルダ) |
ワイルドカードの「*」や「?」を検索する | "A~?"・・・「A?」 "A~*"・・・「A*」 |
COUNTIFS関数の使い方(実践)
実際にCOUNTIFS関数を使用する例をご紹介します。引数をどのように使用するか試してみましょう。
作業時間:3分
関数名を入力する
男性かつ東京出身の人数を求めます。COUNTIFS関数を入力したい【セル(例ではE3セル)】を選択し、『=COUNTIFS(』と入力します。
条件範囲1を指定する
1つ目の条件範囲としてB列の性別を指定します。「=COUNTIFS(」に続いて『B3:B12,』を入力します。マウスでドラッグして指定することもできます。
検索条件1を指定する
条件範囲1から「男」を検索条件として指定します。「=COUNTIFS(B3:B12,」に続いて『"男",』を入力します。
条件範囲2を指定する
2つ目の条件範囲としてC列の出身を指定します。「=COUNTIFS(B3:B12,"男",」に続いて『C3:C12,』を入力します。
検索条件2を指定する
条件範囲2から「東京」を検索条件として指定します。「=COUNTIFS(B3:B12,"男",C3:C12,」に続いて『"東京")』を入力し、【Enter】キーを押します。
COUNTIFS関数の結果
COUTIFS関数によって指定された範囲の中からすべての条件を満たすセルの個数が表示されました。男性かつ東京出身は2名となりました。
このページを見ている人におすすめの商品
さまざまな検索条件の指定方法
よく使用される検索条件を中心にご紹介していきます。
ワイルドカードを使って特定の文字列を含むセルを数える
特定の文字列を含むという検索条件を指定するには、ワイルドカードの「*(アスタリスク)」を使用します。
![ワイルドカードの使用例](../../wp-content/uploads/countifs10.png)
例えば、例のように種類の列から「り」を含むセルの個数を求めるとします。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C9,"*り*")』と入力します。
検索条件で「"*り*"」を指定しましたが、対象の文字列「り」の前後に「*(アスタリスク)」を記述することで「り」を含む文字列を検索します。
![ワイルドカードを使用した例](../../wp-content/uploads/countifs11.png)
検索条件の「り」を含む文字列であるセルの個数として「2」が求められました。
比較演算子を使って特定の文字列以外のセルを数える
特定の文字列以外という検索条件を指定するには、比較演算子の「<>(等しくない)」を使用します。
![COUNTIFS関数の否定条件](../../wp-content/uploads/countifs12.png)
上図の種類から「バナナ」以外のセルの個数を求めるとします。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C9,"<>バナナ")』と入力します。
検索条件で「"<>バナナ"」を指定しましたが、対象の文字列「バナナ」の前に「<>(等しくない)」を記述することで「バナナ」以外の文字列を検索します。
![COUNTIFS関数の否定条件の結果](../../wp-content/uploads/countifs13.png)
検索条件の「バナナ」以外の文字列であるセルの個数として「5」が求められました。
空白以外のセルを数える
空白以外のセルを数えて未入力のセルがないかをチェックする場合、比較演算子の「<>(等しくない)」を使用します。
![COUNTIFS関数で空白以外を数える](../../wp-content/uploads/countifs14.png)
上図の種類から空白以外のセルの個数を求めるとします。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C9,"<>")』と入力します。
検索条件で「<>」を指定しましたが、省略せずに記載すると「<>&""」になります。空白「""」を比較演算子「<>」と文字列結合することで空白以外として検索条件にできます。
![COUNTIFS関数で空白以外を数えた結果](../../wp-content/uploads/countifs15.png)
検索条件である空白以外のセルの個数として「6」が求められました。
日付や期間を数える
指定の日付以前をカウントする
![指定の日付以前を数える](../../wp-content/uploads/countifs16.png)
指定の日付以前のセルを数える場合、検索条件に比較演算子「<=」を使用します。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C12,"<=2019/1/7")』と入力します。
![指定の日付以前を数える結果](../../wp-content/uploads/countifs17.png)
検索条件である「2019/1/7」以前のセルの個数として「7」が求められました。
指定の日付以降をカウントする
![○○以降の日付をカウント](../../wp-content/uploads/countifs18.png)
指定の日付以降のセルを数える場合、検索条件に比較演算子「>=」を使用します。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C12,">=2019/1/7")』と入力します。
![>○○以降の日付をカウント結果](../../wp-content/uploads/countifs19.png)
検索条件である「2019/1/7」以降のセルの個数として「6」が求められました。
指定の期間のセルをカウントする
![指定の期間のセルをカウントする](../../wp-content/uploads/countifs20.png)
指定の期間のセルを数える場合、検索条件に比較演算子「>=」を使って期間の開始日を、「<=」を使って期間の終了日を指定します。
2019年1月に入社した人数を求めます。①F3セルを選択し、②数式バーに『=COUNTIFS(C3:C12,">=2019/1/1",C3:C12,"<=2019/1/31")』と入力します。
![指定の期間のセルをカウントする](../../wp-content/uploads/countifs21.png)
検索条件である「2019年1月入社」のセルの個数として「5」が求められました。
「以上」「以下」を数える
○○以上や○○以下のセルの個数を数えたい場合、比較演算子を使えば簡単に検索条件に指定することができます。
以下の記事では「○○以上○○未満」といった指定方法もお伝えてしていますのでご参考ください。
連続しない複数範囲のセルを数える
COUNTIFS関数で連続しない複数範囲ごとに条件を満たすセルの個数を求めるには範囲ごとにCOUNTIFS関数を作って加算(足す)します。
以下の記事で複数範囲のセルをカウントする方法を説明しています。
複数条件をOR(または)条件で指定する
COUNTIFS関数を使ってOR(または)条件を指定したい場合、COUNTIFS関数同士を足すことで複数条件をOR条件で指定することができます。
以下の記事では同じ列、複数の列の場合に分けてOR条件で指定する方法を説明しています。
COUNTIF関数とCOUNTIFS関数の違い
COUNTIF関数とCOUNTIFS関数を比較してみましょう。
![COUNTIF関数とCOUNTIFS関数の違い](../../wp-content/uploads/countifs22.png)
COUNTIF関数は1つの条件のみ指定することできます。一方、COUNTIFS関数は複数の条件を指定することが可能です。
またCOUNTIF関数は数式を加算することでOR条件に対応することが可能ですが、AND条件を指定することができません。COUNTIFS関数はOR条件、AND条件どちらにも対応することが可能です。
COUNTIFS関数のよくあるエラーとその対処方法
#VALUE!エラーになる場合
閉じた別ブックを参照している
COUNTIFS関数の条件範囲で別ブックのセルまたは範囲を指定している場合、その別ブックを閉じてしまうと仕様上参照することができません。
別ブックを開くか、別ブックを参照しないようにしましょう。
条件範囲が異なっている
COUNTIFS関数では、複数の「条件範囲」と「検索条件」を指定できます。ただし条件範囲の行の範囲が一致していないと#VALUE!エラーになります。もし条件範囲に「B3:B12」と「C3:C10」を指定している場合、行数が違うのでエラーになります。
条件範囲の行数を揃えましょう。
文字列が255文字より長い
検索条件には文字列を指定できますが、仕様上255文字より長いと#VALUE!エラーになります。
CONCATENATE関数または文字列結合「&」を使いましょう。
COUNTIFS関数で0を表示しない
COUNTIFS関数では検索条件に合致するものがなければ0を返します。
![0が表示される場合](../../wp-content/uploads/countifs23.png)
上図では出身列に「山梨」がないのでE3セルに「0」と表示されています。
![0を表示しない](../../wp-content/uploads/countifs24.png)
0を表示させない場合、IF関数と組み合わせます。
上図にはE3セルに『=IF(COUNTIFS(B3:B12,"男",C3:C12,"山梨")=0,"",COUNTIFS(B3:B12,"男",C3:C12,"山梨"))』を入力しています。
IF関数で0の場合は空白と指定しています。
VBAでCOUNTIFS関数を使用するには
ExcelではCOUNTIFS関数を使用することで複数の条件に一致するセルの個数を求めることができますが、VBA(マクロ)でもCountIfsを使用すれば同じように複数の条件を満たすセルの個数を求めることができます。
VBAのCountIfsはワークシート関数(worksheetfunction)で使用できるメソッドの1つです。指定したセル範囲内で、複数の検索条件を満たすセルの個数を求めます。
構文
式としては「WorksheetFunction.CountIfs(条件範囲1,検索条件1,条件範囲2,検索条件2)」のように記述します。
使用例
VBAのコードの中で「CountIfs」がどのように使用されるのか使い方をご紹介します。
![CountIfsの使用例](../../wp-content/uploads/countifs25.png)
上図は性別と出身が入力されています。B3~B12のセル範囲から「男」に一致し、かつC3~C12のセル範囲から「東京」に一致した場合のセルの個数をE3セルに出力させてみましょう。
Sub sample()
Cells(3, 5) = WorksheetFunction.CountIfs(Range("B3:B12"), "男", Range("C3:C12"), "東京")
End Sub
VBAで「CountIfs」を使ったサンプルになります。
- 2行目:「Range("B3:B12")」が条件範囲1です。
- 2行目:「Range("男")」が検索条件1です。
- 2行目:「Range("C3:C12")」が条件範囲2です。
- 2行目:「Range("東京")」が検索条件2です。
他の関数も合わせてチェック!
Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。
この記事はお役に立ちましたか?
- はい
- いいえ