ビジネスではあらゆるデータを統計し数量的に把握することで、経営状況を見極め、経営戦略立案に役立てたりしています。
Excelには様々な統計関数がありますが、今回は、条件を指定して必要な値をカウントする方法についてお話しします。
遅刻はしないことですね。
カウント方法は何通りかありますが、より便利な方法を見つけ出しましょう。
最も簡単なカウント方法 「フィルター」＋「SUBTOTAL関数」
以下の表は、1組～5組までの男女100人、国語、数学、英語テストの点数一覧です。
クラス、性別、各教科の点数を条件に人数をカウントできるようにしたいと思います。
次の条件に当てはまる人数をカウントするためには、どのような方法がありそうでしょうか？
＜条件＞
クラス：1組
性別：女子
国語：20点以上
数学：30点以上
英語：40点以上
まず2行目の項目列にフィルターをかけて…
今日はジブンが先生になった気分です！
素晴らしい！
完璧ですね。
SUBTOTAL関数は、フィルターをかけた時に表示されているデータに関して、いろいろな集計方法で値を求める関数です。
この場合、集計方法を「103」としましたね。
これはデータが入力されているセルをカウントする方法です。
他によく利用されるのは、集計方法「109」で合計を求めるケースではないでしょうか。
悩さんが提案してくれたこの方法は、最も簡単にカウントできる方法と言ってよいでしょう。
しかし問題は、条件が変わった場合です。
条件が変わるごとに、その都度フィルターを掛け直すのはちょっと面倒ではありませんか？
仮にテスト教科がもっと多かったら、かなりの手間になりますね。
実は、フィルターをかけなくてもカウントできる方法があるんです。
「チャンスは最大限に生かす、それが私の主義だ。」と、Excelが言ってますよ。
フィルターをかけずにカウントする方法 「COUNTIFS関数」
では2番目の方法を紹介します。
上記の「フィルター」＋「SUBTOTAL関数」で行ったカウントは、「COUNTIFS関数」で同じ結果を得ることができます。
従来よりCOUNTIF関数はありましたが、Excel2007から「COUNTIFS」と言う関数ができました。
COUNTIF関数は、一つの条件によってカウントしましたが、COUNTIFS関数は複数の条件を指定できます。
使い方はこんな感じです。
= COUNTIFS(条件範囲1,”条件1″,条件範囲2,”条件2″,条件範囲3,”条件3″…）
条件範囲と条件のセットを127個まで指定できます。
では、セルH2 に先ほどの条件でカウント結果が得られるように関数を入力してみたいと思います。
関数のダイアログボックスで指定してみましょう。
まずは、関数挿入ボタンをクリックし、COUNTIFS関数を選択し、ダイアログボックスを開きます。
条件1から順に、検索範囲と検索条件を指定し、スクロールバーで移動しながら条件5まで追加します。
これが完了すると、以下のような関数が入力されます。
= COUNTIFS(B3:B102,”1″,C3:C102,”女”,D3:D102,”>=20″,E3:E102,”>=30″,F3:F102,”>=40″）
条件は「”」でくくります。
〇〇点以上は、”>=点数” と入力してくださいね。
セルH1 に指定した条件の人数がカウントされましたね。
なるほどー。
ん？でも、条件が変わった時はどうするんですか？
ダイアログボックスを開いて、また関数をぬ入力するのは面倒ですよ。
ってことで、今回はジブンの提案した「フィルター」＋「SUBTOTAL関数」でも良いのではないでしょうか？
関数固定でより便利に！ COUNTIFS関数でセルを参照する方法
悩さんにしては鋭い指摘ですね。
確かに、手間を考えると関数は固定したいところです。
ということで、3つ目の対処法として、関数を固定するために条件を入力するセルを作って、COUNTIFS関数にそのセル内容を条件として参照してもらう便利な方法をお伝えしますね。
完成イメージは、セルH3からH7に条件を入力してセルH1にカウント結果を表示させる、という具合です。
そりゃすごいな。
やり方は簡単で、下記のように引数に指定していた条件を、セルで参照させます。
つまり、四角で囲まれた部分を以下のセル情報に置き換えます。
条件1：H3
条件2：H4
条件3：”>=”&H5
条件4：”>=”&H6
条件5：”>=”&H7
「”」でくくっていた条件は文字列を意味するので、条件3～5については参照セル番地を「”」の外に出して「&」で繋げてあげます。
少しわかりにくいと思いますが、入力する関数は、
=COUNTIFS(B3:B102,H3,C3:C102,H4,D3:D102,”>=”&H5,E3:E102,”>=”&H6,F3:F102,”>=”&H7)
となります。
条件3～5の置き場所に注意してくださいね。
関数の入力が終わったら、実際にセルH3～H7の条件を以下のように変えてみましょう。
＜条件＞
クラス：5組
性別：男
国語：50点以上
数学：60点以上
英語：70点以上
入力した条件によって得られたカウント数がH2に表示されましたか？
Excelは常に進化し続けていますね～。
まとめ
COUNTIFS関数をセル参照させるプロセルをお伝えするため、便利なカウント方法を順々に説明してきました。
自分の知らない便利な関数って意外にあると思いますので、新たな関数に出会ったら一度試してみると面白い発見があると思いますよ。
しかしまぁ、Excelを作った人って本当にすごい人ですよね。
【厳選】お勧めのエクセル関連書籍のご紹介
私が今まで読んできた中で、とても役に立ったエクセル書籍をご紹介します。
エクセルは業務効率のために開発されたソフトであるため、使いこなせないとその恩恵を十分に享受することができません。
少しでも業務効率を上げたい方は、是非ご覧になってみてください。
ビジネスエリートの「これはすごい！」を集めた 外資系投資銀行のエクセル仕事術－－－数字力が一気に高まる基本スキル
私の職場ではコンサル出身の上司がいたこともあり、この本は非常に役立ちました。
「仕事は早く、完璧に」という永遠の課題に取り組む上で、読んでおいて損はないと思います。
中級者以上の方にお勧めですね。
こちらはエクセルのコツを学びながら、数字力を磨くことができる良書。
エクセルの基本操作がカバーされているので、初心者でも飽きずに読破することができると思います。
ちょっと他の本と趣向が異なるので、お勧めです。
関数が苦手、という方にうってつけの書籍です。
関数は食わず嫌いの人が多いですが、すぐに使いこなせなくても、「どういうことができるのか」ということを知っておくだけでも大きな価値があります。
エクセルの場合、その方法を知っているかどうかで業務時間に雲泥の差が生まれることがよくあるので、是非読んでいただきたいですね。
自己申告じゃダメですかね？