COUNTIFS関数の使い方|複数条件で検索して数える

COUNTIFS関数の使い方

Excel(エクセル)のCOUNTIFS関数の使い方|複数条件で検索して数える

使用関数:COUNTIFS

COUNTIFS(カウントイフエス)関数とは?

COUNTIFSは「カウントイフズ」や「カウントイフス」と読んでもOKです。COUNTIFS関数は複数の条件に合うセルを抽出して数えます。COUNTIFを複数条件対応に拡張した関数です。その基本構成と使い方、不等号(比較演算子)を使った条件の指定方法などを初心者にもできるだけ分かりやすく解説していきます。

AND条件(AかつB)での指定とOR条件(AまたはB)の指定

COUNTIFSで指定する複数条件はAND条件です。Aの条件、かつ、Bの条件という指定ですね。「且つ(かつ)」には並立させるという意味があり、前後の条件を同時に並び立させる、両方とも満たすということです。

複数条件をOR条件(Aの条件、または、Bの条件)で指定してカウントしたいなら、DCOUNT・DCOUNTA関数を利用しましょう。

DCOUNT関数の使い方|複数の条件を満たす数値をカウントする
DCOUNT関数の使い方を初心者にも分かりやすく解説。データベース関数の引数の指定方法、フィールドを省略して条件を満たすレコード(行)データをカウントする方法、複数の条件をANDかOR、またはAND・ORを組み合わせて指定する方法など。
DCOUNTA関数の使い方|複数の条件を満たす空白以外のセルを数える
DCOUNTA関数の使い方を初心者にも分かりやすく解説。データベース関数DCOUNTA関数の基本と引数の指定方法、複数の条件をANDかOR、またはAND・ORを組み合わせて指定する方法など。

COUNTIFS関数の引数と書式

「数式」→「関数ライブラリ」→「その他の関数」→「統計」からCOUNTIFSをクリックします。
「関数ライブラリ」→「その他の関数▼」→「統計」→COUNTIFSをクリック
COUNTIFSの引数は「検索条件範囲」と「検索条件」の2つがセットになっていて、これを「1」から番号順に必要なだけ指定していきます。

ダイアログを開くと最初は1のセットだけ。Tabキーを押してカーソルを次に移動させる度に、引数のボックスが増えていきます。途中でスクロールバーが出るので、スライドさせることもできます。
COUNTIFSの引数は「検索条件範囲」と「検索条件」の2つがセット

書式の構成はこうなります。
COUNTIFS関数の書式の構成

COUNTIFS関数で個数をカウント

実際にCOUNTIFSで条件を満たすセルを数えてみましょう。

下図の名簿から男女別、年代別に人数を表示させたいとします。
名簿から男女別、年代別の人数を表示

上図の右側にある赤線で囲ったクロス集計表を作るわけですが、このような場合、まず表の最初のセル(最上左端)に入れる数式を考えます。
上図では「男性」と「20代」がクロスした「K3」セルですね。このセルに「男性」を検索する条件と、「20代」を検索する条件の2つをCOUNTIFS関数で組み立てた数式を入れればいいということです。

第一引数「検索条件範囲1」と第二引数「検索条件1」

この例では「検索条件範囲1」に「性別」の列範囲を指定します。列全体「G:G」を指定しても、入力値が入ったセル範囲を指定してもどちらでもOK。ただ、セル範囲を指定するときはF4キーを押して絶対参照にしておきましょう。

「検索条件1」には文字列で「"男"」を指定します。セル参照でもOK。ただ、後々データの追加や入れ替えなどで入力値が変わる恐れがあるなら、文字列指定が安全です。
検索条件範囲1に性別の範囲、検索条件1に"男"を指定

第三引数「検索条件範囲2」と第四引数「検索条件2」

2番めの条件を指定します。
「検索条件範囲2」には「年代」の列範囲を指定します。列全体「E:E」でも、セル範囲でもOK。ここも、セル範囲を指定するときはF4キーを押して絶対参照にしておきます。

「検索条件2」にはクロス集計表の「20」と入ったセル「I3」をクリックして指定します。F4キーを3回押して絶対列参照「$I3」にしておきます。後で数式をフィルコピーするために列だけを固定させたわけです。
検索条件範囲2に年代の範囲、検索条件2に20年代を指定

数式は「=COUNTIFS($G$3:$G$42,"男",$E$3:$E$42,$I3)
性別の範囲から男を探し更にその中から20代を探してカウントする

20代男性の人数」が表示されたセルのフィルハンドルをダブルクリックするか、下まで引っ張って、この数式をオートフィルコピーします。
数式をオートフィルコピー

また、「女性」「20代」のセルに数式をコピペして、「検索条件1」の「"男"」を「"女"」に変えてから、同じように下までフィルコピーして完了です。
COUNTIFSでの集計が完了

COUNTIFS関数で検索範囲・検索条件を指定するルール

COUNTIFSでは幾つかの外せない条件指定ルールがあります。これを守らないと、多くの場合「0」が返ってきます。

COUNTIFSの「検索の範囲」は行(列)数を揃える

複数の条件を指定する際は、「検索条件範囲」の全てで行(列)数が同じである必要があります。
「検索条件範囲」は行(列)数を揃える

不等号(比較演算子)を使った「~以上・~以下・~未満」の条件は「"」で括る

文字列の指定を「"」で括るのは周知ですが、不等号を使った数式も同様に「"」で括る必要があります。「25歳以上45歳未満」を「">=25"」「"<45"」と指定するということです。文字列と同様、数値と明確に区別するためのルールです。
不等号(比較演算子)を使った「~以上・~以下・~未満」の条件は「"」で括る

セル番地と不等号(比較演算子)は「&」でつなげる

不等号(比較演算子)を使った数式にセル参照を組み込む場合は「"」の他に「&」を入れます。
例えば「30以上」という条件を比較演算子とセル参照で指定するなら、まず比較演算子を「">="」と囲い、続けて「&」を入力、その後にセルをクリックして指定します。「">="&I4
セル番地と不等号(比較演算子)は「&」でつなげる
&」の位置に注意して下さい。
&の正しい位置と誤った位置

COUNTIFSで「<>」を「空白以外」として応用する

不等号(比較演算子)の中で「~と等しくない」を表す「<>」は、「<>20」とすれば「20以外」を表しますが、単独だと「空白ではない=空白以外」が転じて「全ての値」を意味します。

これを応用して、最初の章で使ったクロス集計表で数式をフィルコピーした際に「総計」までを表示させてみます。

年代を表す数字の入った列の最終セルに「<>」を入力します。(「20代」の男性・女性セルには下図のようにそれぞれの数式が挿入されています)
年代を表す数字の入った列の最終セルに<>を入力
20代の2つのセルを選択して、フィルハンドルをダブルクリックするか、「総計」行まで引っ張ってオートフィルコピーします。
「総計」行までオートフィルコピー

「総計」のセルに入った数式はこうなります。
=COUNTIFS($G$3:$G$42,"男",$E$3:$E$42,$I7)

$I7」には「<>」が入っているので、「全ての年代」を指定したことになります。
性別の範囲から男を探し、年代からは全てをカウントする

検索して数えるCOUNTIF関数

COUNTIFS関数のまとめ
  • COUNTIFS関数は複数の条件に合うセルを抽出して、その個数を数えます
  • COUNTIFS関数の引数はCOUNTIFの引数2つを複数指定していくことができます
  • COUNTIFS関数の「検索条件範囲」は行(列)数を揃える必要があります
  • COUNTIFS関数の不等号(比較演算子)を使った「~以上・~以下・~未満」の検索条件は「"」で括ります
  • COUNTIFS関数の検索条件でセル番地と不等号(比較演算子)は「&」でつなげます
  • COUNTIFS関数で「<>」を「空白以外」として応用すると、空白でない全てのセルをカウントできます
タイトルとURLをコピーしました