【COUNTIF関数】複数の検索条件を設定する1番カンタンな方法
データの数を数える方法を探していたら、COUNTIFという関数を使えばいいということを知った。
使い方を詳しく知りたいのと、複数条件を指定する方法も知りたい。他にもデータを条件に従って抽出する方法があれば、知りたい。
目次
- １. COUNTIF関数とは？どんなときに使うと便利な関数？
- ２. COUNTIF関数の基本的な使い方
- ３. COUNTIFSで、複数条件を指定する方法
- ４. 複数条件を、いずれか一致で指定する方法
- ５. もっと柔軟に検索条件を指定する方法
- ６. COUNTIF関数を使うときの注意点
- ７. まとめ
スポンサードサーチ
１. COUNTIF関数とは？どんなときに使うと便利な関数？
COUNTIF関数は「ある条件に合うセルがいくつあるか？」をカンタンに求められる関数です。
他にも、COUNTやCOUNTAなど似たような関数がありますが、COUNTIFは、条件を指定できるという部分が、特徴になります。
２. COUNTIF関数の基本的な使い方
例えば、
あなたは、会社の上司から忘年会の幹事を任されました。
出席人数を確認するために、社員全員に出欠を取り、下のようにエクセルの表にまとめたとします。
お店の予約のために、出席人数を集計したいと思います。さて、どんな方法を使えば、より早く計算できるでしょうか？
こうように、「ある条件に合うセルがいくつあるか？」を求めたい場合に、COUNTIF関数が活躍します。
２-１. COUNTIF関数で、出席者の数を求める
今回の場合、集計した出欠一覧から、忘年会に出席できる社員が何人いるのか？を求めたいので、出席のセルの数を数えればいいことになりますね。
それでは、実際に計算式を入れてみましょう！
今回は、集計表の横に、出席人数という項目を作り、その横に計算結果を表示してみたいと思います。
計算結果を表示したいセルをクリックして、=COUNTIF()と入力します。
COUNTIFの計算式は、次のような形でつくる必要があります。
今回の場合、
=COUNTIF(出欠が入力されているセル範囲, “出席”)
となります。
実際の計算式に当てはめると、
=COUNTIF(C3:C11,”出席”)となります。
検索条件をセルで指定する場合は、囲まなくてOKです。
計算式を入れ、Enterを押すとセルに結果が表示されます。
出席人数をちゃんと計算できてますね！
もう少し練習してみましょう。
次は、欠席の人数も求めてみたいと思います。
２-２. 欠席者の数を求める
COUNTIF関数を使えばカンタンですね！
先ほどの計算式をちょこっといじってあげれば、すぐに求められます。
=COUNTIF(C3:C12,"欠席")
さらにもう１つ、別の方法で欠席者の数を求める方法があります。
counta関数を組み合わせます。
counta関数とは？
counta関数は、文字や数値などが入力されたセルの数を数えてくれる関数です。
counta関数で、出席・欠席を含めた全体の数を求め、先ほど求めた出席者の数を引いてあげれば、欠席の人数もわかりそうですね。
計算式は、=counta(C3:C12)-F2となります。
この方法でも、欠席の人数が求められました。どちらが正解ということは無いので、使いやすい方を使ってもらえればOKです。ただ、いろいろな計算方法を知っておくと、状況に応じて柔軟に対応できるので、知っておいて損はありません！
欠席の人数も求められました。
例えば、気難しい上司が、「部署ごとの出席者数を教えてほしい」と言ってきた場合、どうしたらいいでしょうか？
この場合、設定する条件は以下の２つになりそうです。
- 部署 → 営業部、人事部、経理部
- 出欠 → 出席
残念なことにCOUNTIF関数は、検索条件を１つしか設定できません。複数の条件に合うセルの数を求めたいときには、他の方法を考えなければいけません。
そこで登場するのが、COUNTIFS関数です！次から詳しく解説していきます。
スポンサードサーチ
３. COUNTIFSで、複数条件を指定する方法
COUNTIFS関数は、COUNTIFの検索条件を2つ以上指定したいときに使うと便利です。
COUNTIFS関数とは？
COUNTIFに、複数形のsがついていることからもわかるように、COUNTIFに複数の条件をつけることができる関数です。「AとBの条件に合うセルがいくつあるか？」を求められます。
COUNTIFSの計算式は、次のような形でつくる必要があります。
実際の計算式に当てはめると、
=COUNTIFS(C3:C11,”営業部”,D3:D11,”出席”)となります。
また、部署名が入力されたセルを、検索条件にすることもできます。
その場合は、文字をダブルクォーテーションで囲む必要はありません。
計算式にすると図のようになります。
=COUNTIFS(C3:C11,F3,D3:D11,"出席")
それぞれの部署ごとに計算式を入力しました。
COUNTIFSを使うと、複数の条件をつけられるので、活用の場面がグッと広がります。
複数の条件は指定したいけど、条件のどれかに一致すればOKというときに使える方法を解説します。
４. 複数条件を、いずれか一致で指定する方法
COUNTIFS関数は、複数の条件に合うセルがいくつあるか？を数える関数ということを解説しました。
もし「AもしくはBの条件に合うセルがいくつあるか？」を求めたい場合には、COUNTIF関数をそのまま使うことができません。
こんなときは、COUNTIF関数を＋記号でつなぎます。
例えば下の図のように、誕生月が10～12月の人数を求めたい場合、
=COUNTIF(C3:C11,"10月")+ COUNTIF (C3:C11,"11月")+ COUNTIF (C3:C11,"12月")という計算式になります。
10～12月のセルの数をそれぞれ求めて、足し算するイメージですね。
スポンサードサーチ
５. もっと柔軟に検索条件を指定する方法
COUNTIFの検索条件を、より柔軟に指定する方法を２つご紹介します
具体的には、以下の2つの方法をご紹介します。
- ○より大きい、○より小さいなどを条件に指定する方法【比較演算子】
- 特定の文字が含まれるセルの数を数える方法【ワイルドカード】
５-１. ○より大きい、○より小さいなどを条件に指定する方法【比較演算子】
下の図を例に解説してきます。
○より大きい、もしくは○より小さいセルの数を求めたいときは、どのような検索条件を設定すればいいでしょうか？
こんなときに便利なのが、比較演算子です。
今回は、基本的な5つの比較演算子をご紹介します。
| > 10 | 10より大きい |
| <10 | 10より小さい |
| <=10 | 10より大きいか、等しい（10以上） |
| >=10 | 10より小さいか、等しい（10以下） |
| <>10 | 10以外 |
５-１-１. 60より大きいセルの数を求めたいとき
数式バーに、計算式を入力します
=COUNTIF(C3:C7,">60")
５-１-２. 45以下のセルの数を求めたいとき
数式バーに、計算式を入力します
=COUNTIF(C3:C7,"<=45")
５-１-３. 65以上、80以下のセルの数を求めたいとき
数式バーに、計算式を入力します
=COUNTIF(C3:C7,">=65")- COUNTIF (C3:C7,">80")
ポイント
- ○以上○以下という条件にしたいときは、COUNTIF同士を引き算する
- 65以上のセルの数 ― 82より大きいセルの数を引くと、残りは65以上で82以下のセルの数だけになります。
５-１-４. 76以外のセルの数を求めたいとき
数式バーに、計算式を入力します
=COUNTIF(C3:C7,"<>76")
ポイント
- ○○以外を検索条件にしたい場合、”<>○○”を使います
- ○○に入れるのは、文字列でもOKです
５-２. 特定の文字が含まれるセルの数を数える方法【ワイルドカード】
○から始まる、○で終わる、○を含むなど、あいまいな文字列を検索条件にしたいときは、ワイルドカードをという特殊な文字を使います。
ワイルドカードとは
- あいまい検索と言って、一部不明な文字に置き換えて使う、特殊な文字のことです
- 文字の検索や置換、フィルタでデータを抽出するときによく使われます
ちょっと分かりづらいと思うので、実際に以下の表を使って解説していきます。
このような表で、トマトという文字が含まれる文字列は5つあると思います。
これらの数を数えるために、=COUNTIF(B3:B5,"トマト")という計算式を入力してみます。
すると結果は、１になってしまいます。COUNTIFの検索条件に指定した文字列は、完全一致のときだけ、数をカウントします。つまり「トマト」以外は認めない！というめっちゃ強気なスタンスなわけです 笑
「そこんとこ、いい感じにやってよ」と言いたくなりますが、パソコンは、人間のようには、融通を利かせてくれません。。
このようなときに、使うのがワイルドカード（あいまい検索）です。
ワイルドカードを使うことで、○を含む文字、○で始まるもしくは○で終わる文字を、検索できるようになります。
一般的には、以下の２つの記号が使われます。
- *（半角アスタリスク） 任意の文字を表す
- ?（半角疑問符） 任意の１文字を表す
*トマトや???トマトのように、文字の前後にくっつけて使います。
イメージが湧きづらいのと思うので、実際に使ってみましょう。
５-２-１. トマトから始まるセルの数を求めます
つまり、文字の始まりにトマトと入っていれば、後の文字は何でもOKというときに使えます
この場合は、トマト*と入力できます。
計算式にすると、=COUNTIF(B2:B7,"トマト*")と表せます。検索条件は “”（ダブルクォーテーション）で囲むことを忘れないでください。
５-２-２. トマトで終わるセルの数を求めます
文字の最後にトマトが入っているものが対象になります。
この場合は、*トマトと入力できます。
計算式にすると、=COUNTIF(B2:B7,"*トマト")と表せます。
５-２-３. トマトを含むセルの数を求めます
文字の中に、トマトという文字を含むものが対象になります。おそらくこれが1番使いやすいのではないでしょうか？
この場合は、*トマト*と入力できます。
計算式にすると、=COUNTIF(B2:B7,"*トマト*")と表せます。
５-２-４. ○○○トマトと入力されたセルの数を求めます
カットトマトやホールトマトなど、文字数が決まっているときには、？を使います。
この場合は、???トマトと入力できます。
計算式にすると、=COUNTIF(B2:B7,"???トマト")と表せます。
６. COUNTIF関数を使うときの注意点
COUNTIF関数を使うときに、いくつか注意点があります。ここを押さえておかないと、正しい結果が得られないことがあるので、ぜひ目を通していただければと思います。
６-１. 255文字より長い文字列は、正しく計算できません
あまりにも長い文字は、正しく計算されません。
検索範囲に、255文字を超える文字列が無いか注意しましょう。
６-２. 検索条件では、大文字・小文字が区別されません
COUNTIFの検索条件は、大文字・小文字を区別してくれません。つまり検索条件にappleと入力しても、APPLEと入力しても同じことになります。
６-３. 文字や数値の前後に、半角スペースなど余計な文字が入っていると、正しい結果にならないことがあります
これはよくあるトラブルなので、注意が必要です。特に、文字の最後に半角スペースが入っていると、見た目は一緒なので、目視しただけではわかりません。
このような余計な文字が入っていると、正しい結果にならないことがあります。
スポンサードサーチ
７. まとめ
COUNTIF関数や、複数条件を設定できるCOUNTIFS関数を使って、セルの数を集計する方法は、お分かりいただけたでしょうか？
今回のポイントをまとめます
- COUNTIF関数は１つの条件、COUNTIFS関数は複数の条件に一致するセルの数を求められる
- 条件がいずれか一致のときは、COUNTIFを足し算するといい
- 検索条件をざっくり指定したいときは、あいまい検索を使う
- ○○以上などの数値を条件にしたいときは、比較演算子を使う
今回は、条件に一致するセルの数を数えてくれる、COUNTIF関数、COUNTIFS関数の基本的な使い方から、応用方法まで解説させていただきました。
「どんな結果を出したいのか？」をイメージして、それに合った計算方法を使ってみてください。
検索条件の指定方法は、if関数など他の関数にも応用できるので、この機会にぜひ覚えてみてはいかがでしょうか？