COUNTIFS関数を使ってクロス集計表

COUNTIFSの基本構成と使い方

条件で検索したデータの個数を数えるのがCOUNTIF（カウント・イフ）。COUNTIFS（カウント・イフ・エス）は複数の条件で検索したデータの個数を数えます。

構成を見るために、引数のダイアログを開きましょう。 「数式」タブ「関数ライブラリ」→「その他の関数▼」→「統計▶」から「COUNTIFS」をクリックします。

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

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

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

ダイアログの１番めの検索条件を指定してみましょう。
「検索条件範囲1」には「性別」の列範囲を指定します。列全体「G:G」を指定しても、入力値が入ったセル範囲を指定してもどちらでもOK。ただ、セル範囲を指定するときはF4キーを押して絶対参照にしておきましょう。
「検索条件1」には文字列で「"男"」を指定します。セル参照でもOK。ただ、後々データの追加や入れ替えなどで入力値が変わる恐れがあるなら、文字列指定が安全です。

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

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

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

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

COUNTIFSで0が返されるときのチェックポイント

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

「<>」を「空白以外」として使う

不等号（比較演算子）の中で「～と等しくない」を表す「<>」は、「<>20」とすれば「20以外」を表しますが、単独だと「空白ではない＝空白以外」が転じて「全ての値」を意味します。
これを応用して、最初の章で使ったクロス集計表で数式をフィルコピーした際に「総計」までを表示させてみます。

年代を表す数字の入った列の最終セルに「<>」を入力します。（「20代」の男性・女性セルには下図のようにそれぞれの数式が挿入されています）

20代の２つのセルを選択して、フィルハンドルをダブルクリックするか、「総計」行まで引っ張ってオートフィルコピーします。

「総計」のセルに入った数式はこうなります。
=COUNTIFS($G$3:$G$42,"男",$E$3:$E$42,$I7)
「$I7」には「<>」が入っているので、「全ての年代」を指定したことになります。「性別の範囲から男を探し、年代からは全てをカウントする」

▶検索して数えるCOUNTIF関数