エクセル上級者直伝!COUNTIF関数の基本と知っていると便利な応用術

エクセル countif エクセル
「COUNTIF関数ってどうやって使うの?」
「文字を条件に指定したいけどどうするの?」
「条件付き書式でもCOUNTIF関数を使えるのかな」

エクセルで指定条件に当てはまるデータの個数を数えるCOUNTIF関数は、便利で使い勝手もいいためよく使われる関数です。
この関数を知っているだけで、目視でカウントしていた作業は格段に効率よく行えるでしょう。

ここではCOUNTIF関数の基本から複数条件の指定方法などを解説しています。
あいまい検索できるワイルドカードや条件付き書式での使い方も紹介していますよ。

COUNTIF関数を覚え作業の能率アップをするために、ぜひこの記事を役立ててください!

1.COUNTIF関数を覚えよう

「この列の10以下の数値だけ数えたい」や「表の中の”エクセル”という文字が入ったセルを数えたい」というような、ある条件に当てはまるデータをカウントしたいケースは、ビジネスシーンではよくあることです。

COUNTIF関数はそんなときに使える「指定した条件のデータを数える関数」です。
データの個数を数えるCOUNT関数と条件を指定して結果を返すIF関数の両機能を併せ持つ関数で、表計算ではよく使われる関数です。

この関数を使いこなせば、面倒なカウント作業もすぐに終わりますよ!
まずは関数の書き方から覚えていってくださいね。

1-1.COUNTIF関数の基本

COUNTIF関数は「指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返す」機能を持ちます。
COUNTIF関数は以下のように書きます。

=COUNTIF(参照範囲, 検索条件)

実際に使ってみましょう。

下図を見てください。
C5に数式バーに表示されている式が入っており、その結果が「3」として表示されています。

エクセル countif

数式の「=COUNTIF(A1:A6, “>3”)」は、「A1:A6の範囲で3より大きいデータの個数を返す」という内容です。

A1:A6の中で3より大きいのは、A4、A5、A6ですので該当するセルは3個です。
したがって関数の結果は「3」となっています。

ちなみに表にはありませんが、結合したセルはどれだけ複数のセルを結合していても1つのデータとしてカウントします。
データ個数は結合されたセルの個数ではありませんので注意してください。

関数を使う手順は以下の通りです。

  1. 関数結果を表示したいセルに「=COUNTIF(」まで入力します。
  2. 参照先をドラッグで範囲指定します。
  3. 「,(カンマ)」を入力し、検索条件を入力します。
  4. 最後に「)」で関数を閉じて、Enterキーを押すと結果が返ります。

検索条件は必ず「”(ダブルクォーテーション)」で囲みましょう。
囲んでいない場合は参照や数値、結合などの記号とみなされてしまい、エラーとなります。

検索条件に使用できる演算子はエクセルを使用しているなら普段使用するものですので、特別に覚えるものはないかと思います。

エクセル countif

これが数値を検索条件にした場合の書き方です。
次は検索条件に文字列を指定した場合をみていきましょう。

1-2.文字列の指定方法

データ個数をカウントしたいセルに、数値ばかりが入っているわけではありませんよね。
検索する対象が文字列の場合も、数値と同じく検索条件をダブルクォーテーションで囲みます。

たとえば下図でB7に入っている数式は、「A1:A6の範囲で”エクセル”という文字列のデータの個数を返す」という内容です。

エクセル countif

参照範囲の「エクセル」という文字列は2つあるため、結果は「2」が返っていますね。

文字列は255字までなので、それ以上の長い文字列をカウントしたい場合、「”文字列”&”文字列”」というように「&」で文字列を連結して条件に入れましょう。
「&」は結合するための記号のため、ダブルクォーテーションの中に入れないよう注意してください。

1-2-1.ワイルドカードの使い方

文字列の場合、あいまいな検索をしたいときがあるはずです。
たとえば下図の名簿の中で、下の名前に関わらず「鈴木さんだけを数えたい」ときにあいまい検索をしなければなりません。

エクセル countif

この図の場合、検索条件に「”鈴木”」と入れても「鈴木太郎」も「鈴木花子」もカウントされません。
「”鈴木”」だとセルに入っている文字列が「鈴木」だけのセルを数えるからです。

このようなときに使うのが「ワイルドカード」です。
ワイルドカードとはあいまい検索をするための記号のことで、「?(クエスチョンマーク)」や「*(アスタリスク)」を使用します。
どちらも検索したい文字にくっつけて検索条件に入力します。

「?」は検索したい文字列の文字数が分かっている場合に使用します。

たとえば「鈴木太郎はカウントしたいけど、鈴木五右衛門はカウントしたくない」というような場合に、「”鈴木??”」のように鈴木につく名前の文字数を制限して検索します。
「鈴木太郎」と「鈴木花子」は名前が2文字なので「”鈴木??”」に一致しますが、「鈴木五右衛門」は名前が4文字なので一致せずカウントされません。

エクセル countif

もちろん「?」が4つの場合は、「鈴木五右衛門」だけがカウントされます。

エクセル countif

次に「鈴木太郎」も「鈴木花子」も「鈴木五右衛門」もカウントしたいときに使用するワイルドカード「*」について解説します。

使い方は「?」と同じで、検索条件に「”鈴木*”」と書きます。
「*」は文字数制限なしで、「鈴木から始まる全ての文字列」をカウントします。

エクセル countif

「?」も「*」も文字列の前後最中につけることができます。
たとえば「藤」のつく名前をカウントしたい場合、「”*藤*”」とすればいいのです。
最後が「郎」で終わる名前ならば「”*郎”」です。

ワイルドカードを使うとき、注意したいのは検索条件に参照を入れる場合です。
たとえば検索したい文字の「郎」をC1に入力しており、それを検索条件にしたい場合です。

検索条件を「”*C1”」としてはいけません。
なぜならこれだと、最後が「C1」で終わる文字列を検索していることになるからです。
ダブルクォーテーションで囲んだ参照(C1)は文字列としてエクセルは認識してしまうので、検索条件にセル番号を入れる場合は、セル番号にダブルクォーテーションはつけずに書きます。

「=COUNTIF(A1:A6, C1)」ならば問題なく結果が返ります。

ワイルドカードにはダブルクォーテーションが必要ですので、こう書きます。
「=COUNTIF(A1:A6, “*”&C1)」です。

「”*”C1」ではワイルドカードと参照が繋がって認識されないため、必ず間に結合記号「&」を入れます。
ワイルドカードだけでなく、文字列とセル番号は「&」でつなぐことができます。

エクセル countif

  • 検索条件にセル番号を入れる場合は、セル番号にダブルクォーテーションはつけず書くこと。
  • 文字列とセル番号は「&」でつなぐこと。また「&」にダブルクォーテーションはつけないこと。

この2点はワイルドカードをCOUNTIF関数以外の関数で使うときも同じため、覚えておくようにしましょう。

1-3.空白または空白以外を検索する

検索条件の空白は「””」で表せます。
空白や空白以外を検索条件にしたい場合、ダブルクォーテーションと演算子を入力します。

たとえば空白を検索したいときは下図のようになります。(空白のため検索範囲はオレンジ色にしています。)

エクセル countif

空白以外の時は「<>(等しくない)」を使用します。

エクセル countif

2.複数条件を指定する

先に明かしてしまうと、COUNTIF関数で条件を複数指定することはできません。
この関数はあくまで「1つの条件に1つの結果」を返します。

でもそれでは不便なときが必ずあります。
そんなときのためにエクセルでは、COUNTIF関数の「複数条件指定バージョンの関数」がちゃんと用意されています。

COUNTIF関数を覚えたら、次の関数は応用です。
絶対に役立つので、ここでCOUNTIF関数と一緒に覚えておきましょう。

2-1.COUNTIFS関数の使い方

COUNTIFS関数は、「指定された範囲に含まれるセルのうち、複数の検索条件にすべて一致するセルの個数を返す」関数です。
複数条件が指定できるCOUNTIFだから、COUNTIF「S」関数です。

COUNTIFS関数は以下のように書きます。

=COUNTIFS(参照範囲1, 検索条件1 [, 参照範囲2, 検索条件2, 参照範囲3, 検索条件3, …])

参照範囲と検索条件は2以降任意条件で、最大127組まで指定できます。

ではまず下図を見てください。
表の下のB列に隣のC列に表示されている式が入力されています。
「50点以下」の点数はオレンジ色、「51点以上80点以下」の点数は青色にしています。

エクセル countif

「50点以下」の場合、条件が1つのためCOUNTIF関数でも同じ結果を求めることができます。
しかし「51点以上80点以下」の場合、「51点以上」と「80点以下」とうい条件が2つあるため、COUNTIFS関数を使う必要があります。

COUNTIFS関数は条件のすべてが一致しているデータをカウントするため、「51点以上80点以下」の場合、51点以上でも81点以上、80点以下でも50点以下はカウントされていません。

ご覧の通り、COUNTIF関数を理解できれば、COUNTIFS関数も難しくないでしょう。
COUNTIFS関数は条件が1つからでも使用できるので、条件が増えるかもしれない場合は最初からCOUNTIFS関数を使用してもいいですね。

2-2.複雑な条件ならばIF関数と組み合わせる

COUNTIFS関数は分かったけれど、「全ての条件が一致しなければならない」のではなく、「いずれかの条件が一致すれば、その結果を返す」ことがしたい。
そんなときはIF関数を加えましょう。

IF関数は言わずと知れた「もしAならB、AでないならC」ということができる便利な関数です。
ちなみに書き方は「=IF(条件, TRUEの結果, FALSEの結果)」です。

では下図を見てください。
表の下のB~D列に「合格」「不合格」が表示されています。
1段目から順に確認して行きましょう。
※E列の式はB列に入力されている式のため、参照範囲がC列とD列では異なります。

エクセル countif

1段目は「各科目で50点以上が4人以上で合格」です。
そのためB列の式は「=IF(COUNTIF(B2:B8, “>=50”)>=4, “合格”, “不合格”)」です。

これは「もしB2:B8の範囲で50点以上(COUNTIF(B2:B8, “>=50”))が4人以上(>=4)なら、”合格”を表示し、そうでないなら”不合格”を表示する」という内容です。
カウント条件は1つのためCOUNTIF関数を使用しています。

  1. B2:B8の範囲で50点以上(COUNTIF(B2:B8, “>=50”))が4人以上(>=4)なら、”合格”を表示
  2. ①に当てはまらないなら”不合格”を表示

次に2段目は「各科目で51点以上80点以下が2人以上で合格」です。
そのためB列の式は「=IF(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80")>=2, “合格”, “不合格”)」です。

これは「もしB2:B8の範囲で51点以上80点以下(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80"))が2人以上(>=2)なら、”合格”を表示し、そうでないなら”不合格”を表示する」という内容です。
カウント条件は2つのためCOUNTIFS関数を使用しています。

  1. B2:B8の範囲で51点以上80点以下(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80"))が2人以上(>=2)なら、”合格”を表示
  2. ①に当てはまらないなら”不合格”を表示

最後に上記の2つの式を1つの式にしています。
長いですが、「=IF(COUNTIF(B2:B8, “>=50”)>=4, “合格”, IF(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80")>=2, “合格”, “不合格”))」とB列に入っています。

内容は「もしB2:B8の範囲で50点以上(COUNTIF(B2:B8, “>=50”))が4人以上(>=4)なら、”合格”を表示し、51点以上80点以下(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80"))が2人以上(>=2)なら、”合格”を表示し、そうでないなら”不合格”を表示する」です。

  1. B2:B8の範囲で50点以上(COUNTIF(B2:B8, “>=50”))が4人以上(>=4)なら、”合格”を表示
  2. 51点以上80点以下(COUNTIFS(B2:B8, “>=51”, B2:B8, “<=80"))が2人以上(>=2)なら、”合格”を表示
  3. ①②に当てはまらないなら”不合格”を表示

IF関数のFALSEの結果にさらに式を入れる形になっています。
このようにIF関数と組み合わせれば、さらに自由な式を書くことができます。

3.まとめ

COUNTIF関数はいかがでしたか?
この関数は検索条件の演算子や文字列の書き方を覚えれば、特に難しい関数ではなかったと思います。

またCOUNTIFS関数も参照範囲と条件が増えるだけで、基本的にはCOUNTIF関数と仕組みは同じです。
最後に紹介したIF関数との組み合わせは、IF関数でなくても他の関数でも応用できますよ。

COUNTIF関数を含めいろんな関数を使いこなして、作業を効率化してくださいね。

スポンサードリンク

コメント