スポンサーリンク

複数条件に当てはまる件数をかぞえる

関数の応用

 

複数の条件を満たしている件数をかぞえる方法の紹介。

色々な角度から攻めていきます。

今回のお題

現在のランクと、今期の評価が載っている名簿があります。

この名簿の中から、ランクが2か3で、評価がAの人が何人いるか数えてみましょう。

答えは4です。(オタリア三郎、オタリア五郎、セイウチ三郎、セイウチ五郎)

この答えを導き出すための、様々な手法を紹介していきます。

フィルターを使う

フィルター機能を使って、ランクを2と3に絞ります。

続けて、評価がAだけに絞ります。

最後に、キーになる列(空欄がない列)を選択すると、エクセル画面の下のほうにあるステータスバーに、データの個数が表示されます。

データの個数ではなく、合計とか平均値のみが表示される場合は、ステータスバーを右クリックして、表示させたい項目を調整しましょう。

フィルターとCOUNTA関数を使う

空欄ではないセルがいくつあるか数える、COUNTA関数を使います。

フィルターでランク2,3、評価Aで絞ったあと、余白の列に目印をつけます。

今回は対象という文字を、目印として入力します。

今回はD列に目印をつけたので、下記の式を使って件数をかぞえます。

=COUNTA(D:D)

D列に、空欄ではないセルがいくつあるかを数える。という式です。

今回、目印を文字にしたのでCOUNTA関数を使っていますが、目印が数値ならばCOUNT関数でも問題ありません。

COUNTIF関数を使う

指定した条件に一致した件数を数える、COUNTIF関数を使います。

まず、余白の列に、検索用のキーワードを作りだします。

今回はD列を作業スペースとして、D2セルにキーワード作成するための式を入れ、オートフィルで最終行まで反映させます。

キーワード作成の用の式は、=B2 & C2 を使います。

あとはCOUNTIF関数を使って、D列の中から、2Aの件数と、3Aの件数を探し出し、足し算します。

=COUNTIF(D:D,”2A”) + COUNTIF(D:D,”3A”)

COUNTIFS関数を使う

複数の条件が指定できる、COUNTIFS関数を使います。

=COUNTIFS(B:B,”>=2″,B:B,”<=3″,C:C,”A”)

B列が2以上かつ3以下で、C列がAの件数を数えるという式です。

COUNTIFS関数の条件はAND扱いになるので、=COUNTIFS(B:B,”2″,B:B,”3″,C:C,”A”)という式にしてしまうと、0件となります。

B列が2であり、3でもある。なんて式は今回の場合成り立ちませんので。

SUBTOTAL関数を使う

様々な集計ができる、SUBTOTAL関数を使います。

=SUBTOTAL(3,A:A)-1

A列から、空欄ではない件数を数えるという式です。

最初の3というのは、空欄ではない件数を数えるという指定(COUNTAと同じ)です。

最後の-1は、タイトル行の分を引き算しています。

これだけでは、単純にすべてカウントされ、20という結果になっていますので、ランクが2,3のみ、評価がAのみというフィルターをかけます。

SUBTOTALはフィルターで隠れた件数を除いて集計してくれるので、結果が4に変わりました。

SUM関数を使う

指定範囲の合計値を出すSUM関数を使います。

まずフィルター機能を使って、ランクを2と3、評価Aのみに絞ります。

そして余白の列に1を入力します。

あとはD列の合計値を計算するだけです。

=SUM(D:D)

SUMIF関数を使う

条件に一致した行でのみ合算する、SUMIF関数を使います。

まず、余白の列に、検索用のキーワードを作りだします。

今回はD列を作業スペースとして、D2セルにキーワード作成するための式を入れ、オートフィルで最終行まで反映させます。

キーワード作成の用の式は、=B2 & C2 を使います。

さらに別の余白列に、1を入力します。

SUMIF関数で、D列が2Aの場合のみE列を合算と、D列が3Aの場合のみE列を合算の結果を足します。

SUMIFS関数を使う

SUMIFの、条件が複数指定できる版である、SUMIFS関数を使います。

まずは余白列に1を入力します。

そしてSUMIFS関数を使って式を作ります。

=SUMIFS(D:D,B:B,”>=2″,B:B,”<=3″,C:C,”A”)

B列が2以上かつ3以下で、C列がAであるという条件に一致した行の分のみ、D列を合算するという式です。

今日はこのくらいにしといたるわ

その気になればまだまだやり方は出てくるのですが、これ以上は正直あまり実用性がないかなと思い、ここで打ち止めとします。

以上、条件に一致した件数を数えると言っても、これだけ色々なやり方があるんだぞっていうお話でした。

コメント

タイトルとURLをコピーしました