【COUNTIF関数】複数の検索条件を設定する1番カンタンな方法

Excel

データの数を数える方法を探していたら、COUNTIFという関数を使えばいいということを知った。
使い方を詳しく知りたいのと、複数条件を指定する方法も知りたい。他にもデータを条件に従って抽出する方法があれば、知りたい。

目次

スポンサードサーチ

1. COUNTIF関数とは?どんなときに使うと便利な関数?

COUNTIF関数は「ある条件に合うセルがいくつあるか?」をカンタンに求められる関数です。
他にも、COUNTやCOUNTAなど似たような関数がありますが、COUNTIFは、条件を指定できるという部分が、特徴になります。

2. COUNTIF関数の基本的な使い方

例えば、
あなたは、会社の上司から忘年会の幹事を任されました。
出席人数を確認するために、社員全員に出欠を取り、下のようにエクセルの表にまとめたとします。

お店の予約のために、出席人数を集計したいと思います。さて、どんな方法を使えば、より早く計算できるでしょうか?

こうように、「ある条件に合うセルがいくつあるか?」を求めたい場合に、COUNTIF関数が活躍します。

2-1. COUNTIF関数で、出席者の数を求める

今回の場合、集計した出欠一覧から、忘年会に出席できる社員が何人いるのか?を求めたいので、出席のセルの数を数えればいいことになりますね。

それでは、実際に計算式を入れてみましょう!

今回は、集計表の横に、出席人数という項目を作り、その横に計算結果を表示してみたいと思います。
計算結果を表示したいセルをクリックして、=COUNTIF()と入力します。

COUNTIFの計算式は、次のような形でつくる必要があります。

今回の場合、
=COUNTIF(出欠が入力されているセル範囲, “出席”)
となります。

実際の計算式に当てはめると、
=COUNTIF(C3:C11,”出席”)となります。

注意点検索条件に入れる文字列は ””(ダブルクォーテーション) で囲む必要があります。これを忘れると、正しい結果が出ません。
検索条件をセルで指定する場合は、囲まなくてOKです。

計算式を入れ、Enterを押すとセルに結果が表示されます。

出席人数をちゃんと計算できてますね!

もう少し練習してみましょう。
次は、欠席の人数も求めてみたいと思います。

2-2. 欠席者の数を求める

COUNTIF関数を使えばカンタンですね!

先ほどの計算式をちょこっといじってあげれば、すぐに求められます。
=COUNTIF(C3:C12,"欠席")

さらにもう1つ、別の方法で欠席者の数を求める方法があります。
counta関数を組み合わせます。

counta関数とは?

counta関数は、文字や数値などが入力されたセルの数を数えてくれる関数です。

counta関数で、出席・欠席を含めた全体の数を求め、先ほど求めた出席者の数を引いてあげれば、欠席の人数もわかりそうですね。

計算式は、=counta(C3:C12)-F2となります。

この方法でも、欠席の人数が求められました。どちらが正解ということは無いので、使いやすい方を使ってもらえればOKです。ただ、いろいろな計算方法を知っておくと、状況に応じて柔軟に対応できるので、知っておいて損はありません!

欠席の人数も求められました。

例えば、気難しい上司が、「部署ごとの出席者数を教えてほしい」と言ってきた場合、どうしたらいいでしょうか?

この場合、設定する条件は以下の2つになりそうです。

  1. 部署 → 営業部、人事部、経理部
  2. 出欠 → 出席

残念なことにCOUNTIF関数は、検索条件を1つしか設定できません。複数の条件に合うセルの数を求めたいときには、他の方法を考えなければいけません。

そこで登場するのが、COUNTIFS関数です!次から詳しく解説していきます。

スポンサードサーチ

3. COUNTIFSで、複数条件を指定する方法

COUNTIFS関数は、COUNTIFの検索条件を2つ以上指定したいときに使うと便利です。

COUNTIFS関数とは?

COUNTIFに、複数形のsがついていることからもわかるように、COUNTIFに複数の条件をつけることができる関数です。「AとBの条件に合うセルがいくつあるか?」を求められます。

COUNTIFSの計算式は、次のような形でつくる必要があります。

実際の計算式に当てはめると、
=COUNTIFS(C3:C11,”営業部”,D3:D11,”出席”)となります。

また、部署名が入力されたセルを、検索条件にすることもできます。
その場合は、文字をダブルクォーテーションで囲む必要はありません。

計算式にすると図のようになります。
=COUNTIFS(C3:C11,F3,D3:D11,"出席")

それぞれの部署ごとに計算式を入力しました。

COUNTIFSを使うと、複数の条件をつけられるので、活用の場面がグッと広がります。

複数の条件は指定したいけど、条件のどれかに一致すればOKというときに使える方法を解説します。

4. 複数条件を、いずれか一致で指定する方法

COUNTIFS関数は、複数の条件に合うセルがいくつあるか?を数える関数ということを解説しました。

もし「AもしくはBの条件に合うセルがいくつあるか?」を求めたい場合には、COUNTIF関数をそのまま使うことができません。

こんなときは、COUNTIF関数を+記号でつなぎます。

例えば下の図のように、誕生月が10~12月の人数を求めたい場合、
=COUNTIF(C3:C11,"10月")+ COUNTIF (C3:C11,"11月")+ COUNTIF (C3:C11,"12月")という計算式になります。

10~12月のセルの数をそれぞれ求めて、足し算するイメージですね。

スポンサードサーチ

5. もっと柔軟に検索条件を指定する方法

COUNTIFの検索条件を、より柔軟に指定する方法を2つご紹介します

具体的には、以下の2つの方法をご紹介します。

  1. ○より大きい、○より小さいなどを条件に指定する方法【比較演算子】
  2. 特定の文字が含まれるセルの数を数える方法【ワイルドカード】

5-1. ○より大きい、○より小さいなどを条件に指定する方法【比較演算子】

下の図を例に解説してきます。

○より大きい、もしくは○より小さいセルの数を求めたいときは、どのような検索条件を設定すればいいでしょうか?

こんなときに便利なのが、比較演算子です。
今回は、基本的な5つの比較演算子をご紹介します。

> 10 10より大きい
<10 10より小さい
<=10 10より大きいか、等しい(10以上)
>=10 10より小さいか、等しい(10以下)
<>10 10以外

5-1-1. 60より大きいセルの数を求めたいとき

数式バーに、計算式を入力します
=COUNTIF(C3:C7,">60")

5-1-2. 45以下のセルの数を求めたいとき

数式バーに、計算式を入力します
=COUNTIF(C3:C7,"<=45")

5-1-3. 65以上、80以下のセルの数を求めたいとき

数式バーに、計算式を入力します
=COUNTIF(C3:C7,">=65")- COUNTIF (C3:C7,">80")

ポイント

  • ○以上○以下という条件にしたいときは、COUNTIF同士を引き算する
  • 65以上のセルの数 ― 82より大きいセルの数を引くと、残りは65以上で82以下のセルの数だけになります。

5-1-4. 76以外のセルの数を求めたいとき

数式バーに、計算式を入力します
=COUNTIF(C3:C7,"<>76")

ポイント

  • ○○以外を検索条件にしたい場合、”<>○○”を使います
  • ○○に入れるのは、文字列でもOKです

5-2. 特定の文字が含まれるセルの数を数える方法【ワイルドカード】

○から始まる、○で終わる、○を含むなど、あいまいな文字列を検索条件にしたいときは、ワイルドカードをという特殊な文字を使います。

ワイルドカードとは

  • あいまい検索と言って、一部不明な文字に置き換えて使う、特殊な文字のことです
  • 文字の検索や置換、フィルタでデータを抽出するときによく使われます

ちょっと分かりづらいと思うので、実際に以下の表を使って解説していきます。

このような表で、トマトという文字が含まれる文字列は5つあると思います。
これらの数を数えるために、=COUNTIF(B3:B5,"トマト")という計算式を入力してみます。

すると結果は、1になってしまいます。COUNTIFの検索条件に指定した文字列は、完全一致のときだけ、数をカウントします。つまり「トマト」以外は認めない!というめっちゃ強気なスタンスなわけです 笑

「そこんとこ、いい感じにやってよ」と言いたくなりますが、パソコンは、人間のようには、融通を利かせてくれません。。

このようなときに、使うのがワイルドカード(あいまい検索)です。

ワイルドカードを使うことで、○を含む文字、○で始まるもしくは○で終わる文字を、検索できるようになります。

一般的には、以下の2つの記号が使われます。

  • *(半角アスタリスク) 任意の文字を表す
  • ?(半角疑問符) 任意の1文字を表す

*トマト???トマトのように、文字の前後にくっつけて使います。

イメージが湧きづらいのと思うので、実際に使ってみましょう。

5-2-1. トマトから始まるセルの数を求めます

つまり、文字の始まりにトマトと入っていれば、後の文字は何でもOKというときに使えます
この場合は、トマト*と入力できます。

計算式にすると、=COUNTIF(B2:B7,"トマト*")と表せます。検索条件は “”(ダブルクォーテーション)で囲むことを忘れないでください。

5-2-2. トマトで終わるセルの数を求めます

文字の最後にトマトが入っているものが対象になります。
この場合は、*トマトと入力できます。
計算式にすると、=COUNTIF(B2:B7,"*トマト")と表せます。

5-2-3. トマトを含むセルの数を求めます

文字の中に、トマトという文字を含むものが対象になります。おそらくこれが1番使いやすいのではないでしょうか?
この場合は、*トマト*と入力できます。
計算式にすると、=COUNTIF(B2:B7,"*トマト*")と表せます。

5-2-4. ○○○トマトと入力されたセルの数を求めます

カットトマトやホールトマトなど、文字数が決まっているときには、?を使います。
この場合は、???トマトと入力できます。
計算式にすると、=COUNTIF(B2:B7,"???トマト")と表せます。

6. COUNTIF関数を使うときの注意点

COUNTIF関数を使うときに、いくつか注意点があります。ここを押さえておかないと、正しい結果が得られないことがあるので、ぜひ目を通していただければと思います。

6-1. 255文字より長い文字列は、正しく計算できません

あまりにも長い文字は、正しく計算されません。
検索範囲に、255文字を超える文字列が無いか注意しましょう。

6-2. 検索条件では、大文字・小文字が区別されません

COUNTIFの検索条件は、大文字・小文字を区別してくれません。つまり検索条件にappleと入力しても、APPLEと入力しても同じことになります。

6-3. 文字や数値の前後に、半角スペースなど余計な文字が入っていると、正しい結果にならないことがあります

これはよくあるトラブルなので、注意が必要です。特に、文字の最後に半角スペースが入っていると、見た目は一緒なので、目視しただけではわかりません。

このような余計な文字が入っていると、正しい結果にならないことがあります。

スポンサードサーチ

7. まとめ

COUNTIF関数や、複数条件を設定できるCOUNTIFS関数を使って、セルの数を集計する方法は、お分かりいただけたでしょうか?

今回のポイントをまとめます

  • COUNTIF関数は1つの条件、COUNTIFS関数は複数の条件に一致するセルの数を求められる
  • 条件がいずれか一致のときは、COUNTIFを足し算するといい
  • 検索条件をざっくり指定したいときは、あいまい検索を使う
  • ○○以上などの数値を条件にしたいときは、比較演算子を使う

今回は、条件に一致するセルの数を数えてくれる、COUNTIF関数、COUNTIFS関数の基本的な使い方から、応用方法まで解説させていただきました。

「どんな結果を出したいのか?」をイメージして、それに合った計算方法を使ってみてください。

検索条件の指定方法は、if関数など他の関数にも応用できるので、この機会にぜひ覚えてみてはいかがでしょうか?