数式・関数

【Excel】条件に一致したセルの個数を数える「COUNTIF」の使い方

この記事は約8分で読めます。

セルの個数を数える関数というと、初歩的なところでは以前別の回でも取り上げたCOUNT(カウント)関数がありますが、COUNTは数えられるセルの種類が固定化されていますので、使いどころはそこそこ限られてしまうかもしれません。

しかし、実際に仕事などでExcelを使っていると、「この表から<済>と入っているセルの数を数えたい」「年齢の列から30歳以上の人が何人いるか数えたい」等々、数える条件を自分で指定したい場合も多々あります。

そんな時に役立つのが、指定した条件と一致するセルの個数を数えるCOUNTIF(カウントイフ)です。使い方をマスターして、色々な場面で活用してみてください。

※解説画像はMicrosoft365を使用しています

スポンサーリンク

COUNTIFの引数

COUNTIFの引数の構成はこちらになります。

=COUNTIF(範囲,検索条件)

範囲」で指定したセル範囲から「検索条件」に指定した値と合致するセルを探し、そのセルの個数を結果として返す、という仕組みです。

範囲

個数を数えたいセルが含まれるセル範囲を指定します。複数の範囲の同時指定には対応していませんので、Ctrlキーを押しながら離れたセル範囲を選択すると引数オーバーでエラーになります。

検索条件

個数を数える基準となる条件を指定します。特定の数値や文字列の他、他のセルを参照させる事もできます。また、等号・不等号を絡め「~以上」「~以下」などの条件を指定する事も可能です。ただし条件は1つしか入れられません。

関数のカテゴリーは「統計」に属します。「関数の挿入」ダイアログボックスや[数式]タブから入力する場合は「統計」の一覧から探してください。

では、ここから実際にどのように使うのかを見ていきましょう。

特定の文字列と一致するセルの個数を数える

検索条件に文字列を指定する

まずは基本中の基本、指定した文字列と一致するセルの個数を数える方法です。こちらのシートのセルB2~B8には4種類の果物の名前が入力されています。この中から「りんご」と入力されたセルの個数を求め、D3に結果を表示してみます。

COUNTIFで数を求める表

ここでは[数式]タブから関数を入力してみます。セルD3にカーソルを合わせ、[数式]タブをクリックすると・・・あれ?関数ライブラリに「統計」がありません。

数式タブ

この場合は、まず「その他の関数」をクリックします。この中に「統計」がいますのでクリックし、関数の一覧のウィンドウを少し下にスクロールして「COUNTIF」を選択しましょう。

その他の関数から統計カテゴリの関数を選択するところ

関数の引数ダイアログボックスが開きます。ひとつめの引数「範囲」のボックスに点滅カーソルが表示されていますので、そのままB2~B8をドラッグしてセル範囲を入力します。

セル範囲をドラッグして入力するところ

Tabキーを押して「検索条件」にカーソルを移動します。ここに検索したい文字「りんご」を入れますが、引数に文字列を指定する時は「”」(半角ダブルクォーテーション)で前後をはさむというのがお約束です。従ってここは「“りんご”」と入力するのが正解になります。

検索条件を入力したところ

入力し終わりましたらEnterキーで数式を確定しましょう。「りんご」と入力されているセルの個数が求められました。

数式が完成したところ

「検索条件」を「”みかん”」や「”いちご”」など他の文字列に変更すればそれぞれの個数が求められます。また、「””」のようにダブルクォーテーションを2つ連続で入れると空白セルの個数を数えることができます。

変則技:条件に複数の文字列を指定したい場合は?

COUNTIFでは1つの検索条件しか指定できませんが、さきほど例題で使用した表で「りんご」と「いちご」の個数の合計を出したいという場合はどうしましょう。

2つの条件に合致する個数合計を出したい表の例

ひとつの方法として、

=COUNTIF(B2:B8,”りんご”)+COUNTIF(B2:B8,”いちご”)

というように、条件の異なるCOUNTIF式を乗算記号(+)で結び結果を合計するというやり方があります。しかし、これだと式がどうしても長くなってしまうのが欠点です。

このような場合は、SUM関数と組み合わせ、COUNTIFの検索条件を「配列定数」で入力すれば数式をシンプルかつコンパクトにする事ができます。配列定数・・・何だかちょっと難しい言葉が出てきましたが、簡単に言うと複数のデータを1つの固まりとして扱うことができる入れ方です。

例えば、SUM関数の引数の数値1に「りんご」を数える式、数値2に「いちご」を数える式を入れると・・・

=SUM(COUNTIF(B2:B8,”りんご”),COUNTIF(B2:B8,”いちご”))

このようになりますが、これでは先の「+」で合計するのと変わりませんね。むしろSUMを入れる事によってかえって面倒になるという(^^;)

では、COUNTIFで条件指定した「りんご」と「いちご」の文字列を配列定数に置き換えて数式を作ってみましょう。

セルD3にカーソルを合わせ、[数式]タブの「オートSUMの下半分をクリックし、「合計」をクリックします。

SUM関数を入力するところ

セルD3に「=SUM()」と入力されたらそのまま名前ボックスの下向き山括弧をクリックし、ドロップダウンリストから「COUNTIF」を選択します。(リストにCOUNTIFがない場合は、「その他の関数」をクリックして「関数の挿入」ダイアログボックスを表示し、分類:統計から選択しましょう)

名前ボックスからCOUNTIFを選択するところ

COUNTIFの「関数の引数」ダイアログボックスが表示されたら、セルB2~B8をドラッグして範囲を入力します。

範囲をドラッグで入力するところ

Tabキーで「検索条件」にカーソルを移動しましょう。ここに「りんご」「いちご」の2つの条件を入れますが、Excelに「これは配列定数として扱ってね」と指示する場合は、{}(中かっこ)の中に値を「,」(コンマ)で区切って入力します。従って、ここは {“りんご”,”いちご”} と入力します。記号は全部半角で入れてくださいね。

配列定数で検索条件を入力するところ
はにわ先生
はにわ先生

{}中かっこは通常の日本語入力用キーボードだと
Enterキーの左側に固まっていますよ。

配列定数を使う時によくある間違いが {“りんご,いちご”} と入れてしまうパターンです。これだと「りんご」と「いちご」ではなく「りんご,いちご」という条件で検索されてしまい正しい結果が求められません。コンマで区切る文字列ごとに前後を「”」ではさむようにしましょう。

入力できたらEnterを押します。このような式が出来上がりました。

=SUM(COUNTIF(B2:B8,{“りんご”,”いちご”}))

結果も正しく計算されていますね。

結果が求められたところ

ちなみに、Microsoft365もしくはExcel2019をお使いの場合、COUNTIF単独で配列定数を使うと「スピル機能」が働いて、数式を入れたセルには1つ目の検索条件の結果が、隣接したセルに2つ目の検索条件の結果が別々に表示されます。スピル機能については別の機会にお話ししようと思いますが、これはこれで使いどころがありそうですね。

スピル機能で結果が表示されたところ

尚、配列定数ではセル参照や式をまとめることはできませんので、基本的には文字列や数値をまとめたい時に使うものだと覚えておきましょう。

ワイルドカードで指定の文字が含まれるセルの個数を数える

「〇〇という文字が含まれる」というあいまい検索を条件にしたい時はワイルドカードを使用します。ワイルドカードとは検索時に任意の文字列に置き換えられる特殊記号で、代表的なものに「*」(アスタリスク)と「?」(クエスチョンマーク)があります。

*」は0文字以上の任意の文字を表すものです。置く位置によってどの部分を一致させるかを指定できます。

検索条件の例(範囲はA1:A100と仮定)
「東京」で始まる文字列(前方一致)=COUNTIF(A1:A100,“東京*)
「東京」で終わる文字列(後方一致)=COUNTIF(A1:A100,*東京”)
「東京」が含まれる文字列(部分一致)=COUNTIF(A1:A100,*東京*)
どぐうさん
どぐうさん

「*」は「0文字以上」の任意の文字を表すので、「”*東京*”」と部分一致で検索した場合、「東京」とだけ入っているセルも検索対象になります。

また、「?」は任意の1文字を表すものです。文字数を限定した条件にしたい時は「*」でなく「?」を文字数分だけ入れましょう。

検索条件の例(範囲はA1:A100と仮定)
「A」と「D」の間に任意の1文字が入っている文字列=COUNTIF(A1:A100,“A?D”)
「P」のあとに任意の2文字が入っている文字列=COUNTIF(A1:A100,“P??”)

いろいろな検索条件の設定例

ここからは、文字列以外の値を「検索条件」に指定する場合の入力の仕方を見ていきましょう。

検索条件に数値を指定する

検索条件に数値を指定する場合は「”」は必要ありません。検索条件にそのまま個数を数えたい数値を入力しましょう。

検索条件をセル参照で指定する

指定のセルに入力された値と完全一致するセルの個数を数える場合は、参照するセル番地をそのまま検索条件に入力します。

セル参照の数式例

検索条件をセル参照にしておくと、条件を変える時も数式はそのままで参照先のセルの値を変更すれば良いだけなので、便利でお手軽な方法だと思います。

「〇〇以上」「〇〇以下」などの条件を指定する

検索条件に等号・不等号を使用して「以上」「以下」などを指定する事もできます。入力時にはちょっとしたルールがありますので注意が必要です。

検索条件となる値を直接指定する場合

等号・不等号を含めた式全体を「”」で囲みます。

検索条件の例(範囲はA1:A100と仮定)
値が50以上のセルの個数=COUNTIF(A1:A100,“>=50”)
値が100ではないセルの個数=COUNTIF(A1:A100,“<>100”)
2021年2月15日より前の日付
入力されたセルの個数
=COUNTIF(A1:A100,“<2021/2/15”)

セル参照で等号・不等号を使う場合

指定したセルの値に対して等号・不等号をつける場合は、等号・不等号の部分だけ「”」で囲み、セル番地との間に「&」(アンパサンド)を追加しましょう。

検索条件の例(範囲はA1:A100と仮定)
B4の値以上のセルの個数=COUNTIF(A1:A100,“>=”&B4)
E3の値と異なるセルの個数=COUNTIF(A1:A100,“<>”&E3)

等号・不等号つきの条件も、別のセルに入力しておき、そのセルを参照するようにすれば「”」や「&」の存在を気にしなくて良くなるので簡単に式が作成できます。入力ルールが難しくてうまく入れられない・・・という時はお試しください。

セル参照の等号不等号式
スポンサーリンク
でじログ部
タイトルとURLをコピーしました