COUNTIFS関数の検索条件をセット化して一括で切り換える
COUNTIFS関数は、INDEX＋MATCH関数と組み合わせると、「セット化した検索条件を簡単に切り替える」ことができるようになり、とても便利です。
始めに組み合わせて使うメリットを解説し、その後組み合わせ方を説明します。
これでCOUNTIFS関数がより使いやすくなりますよ。
１．INDEX・MATCH関数と組み合わせるメリット
始めに、COUNTIFS関数とINDEX・MATCH関数を組み合わせるとどんなメリットがあるのかから確認してみましょう。
１－１．セット化した値の異なる複数条件を簡単に切り替えられる
組み合わせのメリットは「セット化した値の異なる複数条件を簡単に切り替えられる」ところです。
例を見てみます。
上の表は10人の3教科のテスト結果です。
まずは、この表から「国数英のどれも25点以上の人」の数をカウントしてみます。
セルG6にCOUNTIFS関数を使った数式を入れ、条件値はセルH3～J3に入れています。
ちなみに、カウント結果は「10」でした。
次に、セルH3～J3の条件を変え、「国数英のどれも50点以上の人」という条件に切り換えました。
このように、条件の切り換えはセルの値を変えることで可能ですが、その都度手入力するのは正直面倒です。
また、手入力だと間違った値を入れてしまうかもしれません。
そこで、便利なのが「条件のセット化」です。
上の表のような「セット用の表」を作成します。
セット1は「国語25点、数学25点、英語25点」、セット2は「国語50点、数学50点、英語50点」といったような形です。
ちなみに、点数が3教科ともバラバラでももちろん構いません。
次に、選んだセットをINDEX＋MATCH関数で表示させます。
そして、COUNTIFS関数で表示された条件値のカウントを行うのです。
こうすれば、条件値の切り換えが簡単にできるようになります。
これは、よく使う条件値の組み合わせがある時に便利です。
２．INDEX・MATCH関数との組み合わせ方
では、実際に数式を作ってみましょう。
例は、先ほどと同じものを使います。
以下手順です。
（１）条件値のセットを表にする
（２）カウント結果表示用のシートに、選択した条件値を表示するための枠を作成する
（３）条件値を表示させるセルの一番左に「=INDEX(」と入力し数式バーのfxボタンを押す
（４）COUNTIFS関数の引数「配列」に条件表の見出しを除くすべての範囲を選択後、「行番号」に「MATCH(」と入力後、再度fxボタンを押す
（５）MATCH関数の引数「検査値」にセット番号を入れるセルを、「検査範囲」にセット表の№の範囲を、「照合の種類」に「0」を入力後、数式バー内のINDEXをクリックする
（６）COUNTIFS関数の引数「列番号」に「COLUMN(B1)」と入力後OKボタンを押す
（７）入力した数式を他の右側のセルにコピーする
（８）カウント結果を表示させるセルを準備する
（９）カウント結果を表示させるセルに「=COUNTIFS(」と入力後、fxボタンを押す
（10）各カウント条件を、引数「検索条件」に先ほど設定したカウント条件のセルを活用しながら指定する
（11）完了
これでセット化した条件値を使ってカウントすることができるようになりました。
数式でいくつか「なぜこう入力するんだろう？」というポイントがあったかと思うので補足します。
まず、MATCH関数の引数「照合の種類」に「0」と入力しましたが、これは「検索は完全一致でないとだめですよ」という意味です。
他に「1」や「-1」が入力できますが、これらは検索値に近い値を持ってきますので、場合によって正しく計算してくれません。
また、INDEX関数の引数「列番号」に「COLUMN(B1)」と入力しましたが、これはこの後数式を右側にコピーした時に列番号も1ずつ増えるようにするためです。
COLUMN関数は指定したセルの列番号を返す関数で、B1を指定するとB列は2番目の列なので「2」が返ってきます。
これが、数式のコピーで「3」、「4」と変わっていくのです。
これで、その都度引数を手で直さなくて済みます。
ただし、もし分かりづらい場合には数値を手入力で入れても構いません。
以上参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
COUNTIFS関数に関連したページは、次の通りです。。
- COUNTIFS関数 使いこなすためのポイントはこちら