本記事ではExcelのCOUNTIFS関数の使用方法を説明します。
以下の記事で紹介したCOUNTIF関数では1つの条件しか指定ができませんが、COUNTIFS関数は複数の条件を指定して、その条件に一致する件数を数えることができます。
- COUNTIFS関数とは。COUNTIFS関数の書式
- COUNTIFS関数のセル範囲を指定するポイント
- 複数の検索条件に一致するデータの存在を確認する方法
- 複数の検索条件に一致するデータの重複を確認する方法
- COUNTIFS関数の検索条件に使える『比較演算子』・『ワイルドカード』
COUNTIFS関数とは。COUNTIFS関数の書式
COUNTIFS関数は、指定した範囲の中で検索条件に一致するデータの件数を数える関数です。(検索条件に一致するデータがなければ0を返します)
COUNTIF関数が1つの条件に一致する件数を数える関数なのに対して、COUNTIFS関数は2つ以上の条件に一致する件数を数える関数です。
検索条件には、比較演算子やワイルドカードを使うことができます。
COUNTIFS関数の書式は以下の通りです。
COUNTIFS関数の書式
=COUNTIFS(範囲1,検索条件1,範囲2,検索条件2,...)
範囲1、検索条件1、範囲2、検索条件2は必須となります。範囲3、検索条件3以降は省略可能です。
以下画像では、COUNTIFS関数を使って、住所が兵庫県神戸市の男性、女性の件数を数えています。
性別が男性かつ住所が兵庫県神戸市のデータ件数は『=COUNTIFS($C$3:$C$17,"男",$E$3:$E$17,"兵庫県神戸市")』で求めています。
範囲1、検索条件1には、『$C$3:$C$17,"男"』、範囲2、検索条件2には『$E$3:$E$17,"兵庫県神戸市"』を指定して、性別が男性かつ住所が兵庫県神戸市のデータ件数を数えています。
性別が女性かつ住所が兵庫県神戸市のデータ件数は『=COUNTIFS($C$3:$C$17,"女",$E$3:$E$17,"兵庫県神戸市")』で数えています。
COUNTIFS関数のセル範囲を指定するポイント
COUNTIFS関数で範囲を指定する際は、以下のポイントを意識します。
- 範囲1、範囲2...のセル範囲は、検索条件に一致するデータがある1つの列で範囲を指定する (複数の列にまたがる範囲を指定しない)
- 範囲1、範囲2...のセル範囲は、開始行、終了行をあわせる。
上記画像の場合は、「性別が男性かつ住所が兵庫県神戸市かつ購入回数が10回以上」という3つの範囲、検索条件で件数を数えています。
範囲1～3はそれぞれ、検索条件に一致するデータがある該当の1列を指定しています。
範囲1～3は開始行3、終了行17を指定しており、範囲1「$C$3:$C$17」、範囲2「$E$3:$E$17」、範囲3「$H$3:$H$17」をして指定ます。
複数の検索条件に一致するデータの存在を確認する方法
COUNTIFS関数でデータ群の中に複数の検索対象に一致するデータが存在するかどうかを確認することができます。COUNTIFSで0を返すか、1以上を返すかで存在確認をします。
上記画像では、セルB3：D17の部品一覧の中にセルF3：H9のデータが何件存在しているかどうかを、COUNTIFS関数を使って数えて、I列に件数を返しています。
I列の数値が0のデータは、部品一覧に存在しないことを表しています。
※部品一覧は、部署コード、部品コード、仕入先コードの組み合わせで一意となります。
セルI3の数式は以下のようになっています。
=COUNTIFS($B$3:$B$17,F3,$C$3:$C$17,G3,$D$3:$D$17,H3)
範囲1「$B$3:$B$17」で検索条件「F3」かつ、範囲2「$C$3:$C$17」で検索条件「G3」かつ、範囲3「$D$3:$D$17」で検索条件「H3」に一致するデータが何件あるかをCOUNTIFS関数で数えています。
I列が「0」の場合はデータが存在いないことを表しています。I列が「1」以上の場合はその数値分のデータが存在することを表しています。
複数の検索条件に一致するデータの重複を確認する方法
COUNTIFS関数で、データ群の中で重複しているデータを探すことができます。COUNTIFS関数は条件を複数指定できるため、複数の条件で一意になるデータが重複しているかどうかを調べることができます。
COUNTIFS関数で2以上の値が返ってきたら重複している状態です。
上記画像では、部署コード、部品コード、仕入先コードで一意となる部品一覧で、部署コード、部品コード、仕入先コードを組み合わせたデータが、一覧の中で重複しているかどうかを確認しています。
セルE3では以下の数式で、部署コード(B3)、部品コード(C3)、仕入先コード(D3)を組み合わせたデータが部品一覧で重複しているかどうかを調べています。
=COUNTIFS($B$3:$B$25,B3,$C$3:$C$25,C3,$D$3:$D$25,D3)
範囲1「$B$3:$B$25」検索条件1「B3」、範囲2「$C$3:$C$25」、検索条件2「C3」、範囲3「$D$3:$D$25」、検索条件3「D3」に一致するデータの件数を返しています。
E列の値が「0」の場合はそのデータ存在しない。「1」の場合は重複せずに1件のみ存在している。「2」以上の場合はその数値だけ重複していることを表しています。
ポイントは、数式をコピーして利用するので検索範囲を絶対参照、検索対象を相対参照とすることです。
COUNTIFS関数の検索条件に使える『比較演算子』・『ワイルドカード』
COUNTIFS関数の引数の『検索条件』には、以下の比較演算子・ワイルドカードを使うことができます。比較演算子・ワイルドカードを使用する場合は検索条件とあわせて『"(ダブルクォーテーション)』で囲みます。
| 比較演算子 | 説明 | 使用例 |
|---|---|---|
| = | 等しい | "=10" |
| <> | 等しくない | "<>男" |
| ＞ | より大きい | ">19" |
| < | より小さい | "<20" |
| >= | 以上 | ">=20" |
| ＜＝ | 以下 | ”＜＝19" |
| ワイルドカード | 説明 | 検索条件の使用例 |
|---|---|---|
| * | 任意の文字列 | "A*" |
| ? | 任意の1文字 | "?田" |
比較演算子、ワイルドカードの使い方については、以下のCOUNTIF関数の記事で説明していますので、ご参照ください。
auroralights.jp
