Excel（エクセル）の COUNTIFS 関数は「複数の条件と一致するデータの数を数える関数」です。
他の関数と組み合わせて使うことで大きく効果を発揮します。
基本的な使い方を紹介した後、経理職や会計事務所での業務での活用例を紹介します。
1、COUNTIFS 関数の使い方
まず、COUNTIFS 関数の使い方を説明します。
COUNTIFS 関数は
のように記述して使います。
対象となる範囲を一行ずつ調べていき、「範囲1」〜「範囲n」がそれぞれ「条件1」〜「条件n」を満足する場合に「1」カウントし、合計数を計算します。
「範囲1」〜「範囲n」は行番号を揃えなければなりません。
次の表を例にあげて説明しますね。
AAA～JJJ 10人の国語、数学、英語のテストの点数を記録してあるシートです。
さて、この AAA ～ JJJ の中で3教科全てで 90点以上取っている人の人数を数える場合を考えてみます。
セル「I5」に人数を表示します。
「=COUNTIFS(C5:C14,”>=90″,D5:D14,”>=90″,E5:E14,”>=90″)」
と入力しましょう。
このケースでは範囲と条件は3つになります。
●範囲1は「C5:C14」
●範囲2は「D5：D14」
●範囲3は「E5:E14」
全ての範囲の行番号を合わせなければなりません。
今回の場合、行番号の下は「5」上は「14」となっています。
●条件1～条件3は「”>=90″」
＊条件を記述する際には” “で囲うことを忘れないようにしましょう。数値を入れる場合には「”=90″」「”>=90″」のように等号不等号を使います。文字の場合は「”テスト”」のように等号を使わずに入力します。
この COUNTIFS 関数がやっていることのイメージは、5行目～14行目まで一行ずつ条件と一致するか見ていき一致する場合に1ずつ加算していくということです。
計算結果を確認すると「3」となります。
国語、数学、英語がそれぞれ90点以上となる人は DDD、FFF、JJJ の3人ですから正しく計算できてますね！
2、毎月の売上高、費用のデータの平均値を求めるシート
さて、冒頭で他の関数と組み合わせることで大きく効果を発揮すると書きました。
ということで他の関数と組み合わせて使う例をあげたいと思います。
毎月の売上高、費用のデータの平均値を求めるシートのシート設計
次の表に会社の1年間の「売上高」「変動費」「固定費」のデータを毎月入力していき「経常損益」を計算しています（経常損益 = 売上高 － 変動費 － 固定費）。
「経常損益」を計算する F列には計算式を最初に12ヶ月分入力してあります。
この表に1つの月が終わるごとに1行ずつデータを入力していき、それらの入力した「売上高」「変動費」「固定費」の平均値を計算したいと思います。
平均値を計算する上での課題
さて、この平均値の計算に一工夫必要なのです。
単純に 12ヶ月分のデータを平均するとしたら、売上高であれば19行目に「=SUM(C5:C16)/12」のように分母を「12」とした数式を埋め込んでおけば計算できます。
ただ、これだとまだデータを入力していない月も含めて平均を計算してしまっていますよね。
上の表で言えば9ヶ月分しかデータを入力していないので、正しくは分母を「9」にしなければなりません。
しかし、毎月データが1行ずつ増えていくので、入力済の月数分の平均を常に19行目に表示させようと思うと、上記計算式の分母をいちいち変更しなければならなくなってしまいます。
●3ヶ月分のデータを入力済の場合「=SUM(C5:C16)/3」
●5ヶ月分のデータを入力済の場合「=SUM(C5:C16)/5」
●9ヶ月分のデータを入力済の場合「=SUM(C5:C16)/9」
毎月平均を把握しようとすると、これではとても面倒で非効率ですよね。。
COUNTIFS 関数の活用
ここで COUNTIFS 関数の出番なのです！
平均値を計算する行の数式の分母が、入力したデータの行数によって変化するように COUNTIFS 関数を使います。
次のように、18行目に「合計」を計算し、19行目で平均を計算したいと思います。
なお、セル「C21」では「平均」の計算に使う「データの数」を計算しています。
18行目に埋め込んである数式は、売上高の合計を計算しているセル「C18」であれば「=SUM(C5:C16)」としてあり、D列、E列も同様に計算しています。
続いてポイントとなるデータ数の計算です。
セル「C21」には「=COUNTIFS(C5:C16,”<>0″,C5:C16,”<>”)」と入力してあります。
COUNTIFS 関数の使い方↓
に当てはめた場合、範囲と条件は2つになります。
●範囲1は「C5:C16」、条件1は「”<>0″」（数値の0ではない）
●範囲2は「C5:C16」、条件2は「”<>”」（空白ではない）
記号の <> はノットイコールを意味し、「”<>0″」とすれば「セルの数値が0ではない」という意味になります。
また、「”<>”」とした場合には「セルが空白ではない」ことを意味します。
今回入力した数式により、売上高の範囲「C5～C16」で数値の0ではなく、かつ空白でもないのものをカウントして合計していることになりす。
9ヶ月分入力してあれば「9」となるわけですね。
このように常に入力済の月数の数が、データ数として「C21」に表示されるようになっているのです。
次に平均を計算している19行目について、売上高の合計を計算しているセル「C19」であれば「=C18/$C$21」としてあり、D列、E列も同様に計算しています。
全部の月の合計額を先ほど説明したセル「C21」のデータ数で割ることで、常にデータ入力が済んだ月数分の平均値を計算できるようにしてあるのです。
いきなり平均を計算することも可能
さて、先ほどまでの説明では「合計」と「データ数」を計算した後にそれらの計算結果を使って「平均」を計算するイメージになっていましたが、いきなり「平均」を計算することだって可能です。
売上高の平均をセル「C19」に「=SUM(C5:C16)/COUNTIFS(C5:C16,”<>0″,C5:C16,”<>”)」と入力して計算してみましょう。D列、E列にも同様の数式を入力します。
まず、分子の「SUM(C5:C16)」により売上高の合計を計算しています。
そして、分母の「COUNTIFS(C5:C16,”<>0″,C5:C16,”<>”)」は先ほど説明したデータ数を計算したものです。
これらを使って一気に割り算を行って平均を計算しているのです。
SUM 関数とCOUNTIFS 関数を組み合わせて使ってますよね。
これが、冒頭で他の関数と組み合わせて使うことで大きく効果を発揮すると書いた意味を表す一例です。
3、まとめ
COUNTIFS 関数の基本的な使い方と、他の関数と組み合わせて活用する方法を説明しました。
他の関数と組み合わせて使えばアイデア次第で色々と応用がきくようになりますので、是非使ってみて頂きたいです。