【スプレッドシート】COUNTIF関数の基礎から応用まで

こんにちは、たま(@closuppo)です！

今回は、スプレッドシートの「COUNTIF関数」の上級編をお伝えします。

初級編・中級編に続いてのマスター編となります。復習しながら今回の記事の内容を実践して、COUNTIF関数の理解を深めましょう。

条件に一致するセルを数えるCOUNTIF関数

【スプレッドシート】データ集計の自動化（COUNTA、SUM、COUNTIF）

※目次をクリックすると
タイトルの先頭まで移動します（↓）

まずはじめに

COUNTIF(カウントイフ)関数とは、条件に一致するセルが何個あるか計算をしてくれる関数です。

選択した範囲の中から指定した条件に合うデータを検索し、検索条件に一致する項目の数(セル)の個数を数えてくれます。

指定する条件は【女性】などの単語だけでなく【数値】や【日付】なども指定することができ、「〇〇より大きい」「〇月〇日以降」など比較条件を指定して数えることもできます。

例えば、下図はCOUNTIF関数を使って、表の中の指定した範囲から【女性】を条件と指定して、人数を自動で調べて集計結果を出しています。

それでは、サンプルシートを使ってCOUNTIF関数でセルの個数を求めてみましょう。

≫サンプルシートはこちら

はじめてのCOUNTIF関数

COUNTIF関数の構文は以下の通りです。

下図の名簿の中から女性の人数をCOUNTIF関数で集計してみましょう。

　結果を表示させたい女性の人数欄セルB3に=COUNTIF(と入力

検索したい範囲の、性別が記載されたセル範囲B6:B11を指定

「 , (カンマ) 」を入力

条件:B6:B11の中から女性の人数を調べたいので、"女性"と入力

文字を指定する場合は記号「 " (ダブルクォーテーション) 」で文字の前後を囲みます。

• "女性"と手入力せず、すでに女性と入力されているセルA3をクリックして条件を指定することもできます。

条件に一致するセルの個数は3、検索範囲B6:B11に女性が3名いることが計算結果で表示されました。

指定した範囲B6:B11の中に女性と入力されたセルは3つあるので、セルB3に表示される答えは3となります。

プルダウンを使って条件を選択する

一つ前の項目でも少し触れましたが、=COUNTIF(❶範囲, ❷検索条件)の❶範囲と同じように❷検索条件もセルを選択して指定することができます。

下図のように、条件として指定するセルA2の項目をプルダウンで選択できるように設定しておくと、選択した条件に一致する集計結果をB3に返すことができます。

スプレッドシートでプルダウンリストを作成する方法（データ入力規則）

プルダウンリストから作成する場合はサンプルシート2、セルA2のデータの入力規則を削除してからお使いください。

結果を表示させたい人数欄セルB２に入力する数式はこうなります。

操作手順はこちらです。

• セルB2に「 = COUNTIF( 」と入力
• マウスでB6からB11を選択
• 「 , 」を入力
• マウスでA2をクリック
• 「 ) 」で閉じる

条件に一致するセルの個数は3、検索範囲B6:B11に男性が3名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

COUNTIF関数応用編

COUNTIF関数は、特定の数値や日付を比較条件に指定して【〇〇より小さい】【〇〇以上】【〇〇を含む】などの結果を求めることができます。

例えば、上の名簿を参考に下記のように条件をつけることができます。

• 【〇〇より小さい】を条件に、点数のC列の中から、80点未満の不合格の人数を調べる
• 【〇〇以上】を条件に、点数のC列の中から、合格点80点以上の人数を調べる
• 【〇〇以上】を条件に、日付のA列の中から、4月以降にテストを受けた人数を調べる
• 【〇〇を含む】を条件に、評価のＤ列の中から、【不】という文字が入っているセルを集計して不合格者の人数を調べる

COUNTIF関数で使える比較演算子とワイルドカード

まずはCOUNTIF関数の構文をおさらいしましょう。

COUNTIF関数では、2つめの引数、検索条件に比較演算子とワイルドカードを使うことができます。

比較演算子

比較演算子とは演算子の分類のひとつで、「 > (大なり) 」や「 = (イコール) 」など、記号の左側と右側を比較するときに使います。

左右の二つを比べて条件に合っているかどうかを判定するものです。

比較演算子には下記の5種類があります。

記号(比較演算子)	処理	条件値
>	より大きい	含まない
>=	以上	含む
<	より小さい	含まない
<=	以下	含む
<>	等しくない（以外）	条件値以外

先ほどの表のとおり、【〇〇より大きい】は条件値(〇〇)を含まない、【〇〇以上】は条件値(〇〇)を含むという違いがあります。

条件値を100とすると、このようになります。

• 【100より大きい】:100を含まない→101.102.102.104…
• 【100以上】:100を含む→100.101.102.103…

• 〇〇を含まない場合は「 > (大なり) 」、〇〇を含みたい場合は「 >= (以上) 」を使いましょう

ワイルドカード

ここで言うワイルドカードとは、関数の条件として文字を指定するときに使う記号のことを指します。

ワイルドカードは文字列のみ、使用することができます。

3種類ありますので、まずは見てみましょう。

記号(ワイルドカード)	読み方	処理
*	アスタリスク	任意の0文字以上の文字列を検索
?	クエスチョン	任意の1文字の文字列を検索
~	チルダ	ワイルドカードを無効にする

まずは、ワイルドカードの記号をどの位置に置くかによって、どのような意味になるかを下の表で見てみましょう。

使用例	意味	該当例
*ティー*	「ティー」を含む文字列	ストレートティー、レモンティー、ハーブティー、チャイティーラテ、ティーソーダ
ティー*	「ティー」から始まる文字列	ティーソーダ
*ティー	「ティー」で終わる文字列	ストレートティー.レモンティー.ハーブティー
???テ	「テ」で終わる4文字の文字列	モカラテ、抹茶ラテ
ティー???	「ティー」で始まる6文字の文字列	ティーソーダ

「ティー」とつくメニューがいくつあるか数えたいので、条件を*ティー*と指定します。

「ティー」の文字列を含むセルが5個あることがわかりました。

• 「 ~(チルダ) 」とは、ワイルドカードを無効にするはたらきがあります。

例えば、「 ? (クエスチョン) 」が含まれた表の中でCOUNTIF関数の条件に「 ? 」を指定すると、文字として「 ? 」を指定したいのに、ワイルドカード記号として認識されてしまいます。

文字の「 ? 」と認識させるために、?の前に~を入れて「 ~? 」と指定するとワイルドカードではなく文字の「 ? 」として上手く認識されるのです。

〇〇より小さい

比較演算子の「 < (小なり) 」を使って【〇〇より小さい】セルの個数をカウントすることができます。

COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。

上の図は、合格基準の80点に満たない不合格の人数を集計しています。

条件を【80より小さい】と指定しているので、80を含まない79点までが不合格にカウントされます。

検索条件にテキストを直接入力する

比較演算子の「 < (小なり) 」を使って80より小さい不合格のセルの個数を集計してみましょう。

先ほどご説明したポイントをおさえて数式をテキストで直接入力します。

セルF6に入力する数式はこうなります。

操作の手順はこちらです。

• セルF6に「 = COUNTIF( 」と入力
• セル範囲C2からC21を選択
• 「 ,  」を入力
• 条件に「 " 」、「 < 」、80、「 " 」の順で入力
• 「 ) 」で閉じる

条件に一致するセルの個数は11、検索範囲C2:C21に80より小さい不合格が11名いることが計算結果で表示されました。

❷検索条件にセルを指定する

先ほどと同じように、比較演算子の「 < 」を使って80より小さい不合格のセルの個数を集計します。

検索条件には数値の入ったセル番地を指定しましょう。

セルF6に入力する数式はこうなります。

操作の手順はこちらです。

• セルF6に「 = COUNTIF( 」と入力
• セル範囲C2からC21を選択
• 「 , 」を入力
• 条件に「 " 」、「 < 」、「 " 」、「 & 」順で入力し、セルG1をクリック
• 「 ) 」で閉じる

条件に一致するセルの個数は11、検索範囲C2:C21にセルG1の数値より小さい不合格が11名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

〇〇以上

比較演算子の「 >= 」を使って【〇〇以上】のセルの個数をカウントすることができます。

COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。

上の図は、合格基準の80点以上の合格人数を集計しています。

条件を【80以上】と指定しているので、80を含む大きい点が合格にカウントされます。

検索条件にテキストを直接入力する

比較演算子の「 >= 」を使って80以上の合格のセルの個数を集計してみましょう。

先ほど説明したポイントをおさえて数式をテキストで直接入力します。

セルF8に入力する数式はこうなります。

操作の手順はこちらです。

• セルF8に「 = COUNTIF( 」と入力
• セル範囲C2からC21を選択
• 「 , 」を入力
• 条件に「 " 」、「 > 」、「 = 」、80 、「 " 」の順で入力
• 「 ) 」で閉じる

条件に一致するセルの個数は9、検索範囲C2:C21に80以上の合格が9名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

検索条件にセルを指定する

比較演算子は数値だけでなく日付を指定することができますので、日付に【〇〇以上】と指定しすると、【〇〇日以降】として調べることができます。

例えば、「 ">=4/1" 」と指定すると、4/1以降という条件になります。

セルF10の4月以降の受験者の人数を集計してみましょう。

検索条件には日付が入ったセル番地を指定します。

セルF10に入力する数式はこうなります。

操作の手順はこちらです。

• セルF10に「 = COUNTIF( 」と入力
• セル範囲A2からA21を選択
• 「 , 」を入力
• 条件に「 " 」、「 > 」、「 = 」、「 " 」、「 & 」順で入力し、セルG2をクリック
• 「 ) 」で閉じる

条件に一致するセルの個数は6、検索範囲A2:A21にセルG2の日付以降の受験した人が6名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

〇〇を含む

COUNTIF関数は、ワイルドカードを使って特定の文言を含むセルをカウントすることができます。

「 * 」の使い方についてはワイルドカードの項目で解説しましたが、この項目では「 * 」を使って【〇〇を含む】の使い方を見てみましょう。

COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。

テキストを直接入力する

「 * (アスタリスク) 」を使って「ティー」を含む文字列のセルの個数を集計してみましょう。

セルD3に入力する数式はこうなります。

操作手順はこちらです。

• セルD3に「 = COUNTIF( 」と入力
• セル範囲A2からA11を選択
• 「 , 」を入力
• 条件に「 " 」、「 * 」、ティー、「 * 」、「 " 」の順で入力
• 「 ) 」で閉じる

条件に一致するセルの個数は5、検索範囲A2:A11にティーを含む文字列が5個あることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

検索条件にセルを指定する

先ほどと同じように、「 * (アスタリスク) 」を使って「ティー」を含む文字列のセルの個数セルの個数を集計します。

検索条件には数値の入ったセル番地を指定しましょう。

セルD3に入力する数式はこうなります。

操作手順はこちらです。

• セルD3に「 = COUNTIF( 」と入力
• セル範囲A2からA11を選択
• 「 , 」を入力
• 条件に「 " 」、「 * 」、「 " 」、「 & 」の順で入力、D2をクリック、「 & 」、「 " 」、「 * 」、「 " 」の順で入力
• 「 ) 」で閉じる

条件に一致するセルの個数は5、検索範囲A2:A11にセルD2の文字を含む文字列が5個あることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

あいまい検索

ワイルドカードの項目でも触れましたが、ワイルドカードを活用すると、部分的に一致する文字を条件に指定して検索することもできます。

先ほどは「 * 」を使って「ティー」を含む文字列を検索しましたね。

「 ? 」を使えば、「 ? 」と部分的な文字を組み合わせた条件で、文字数を指定して検索することができます。

数式にするとこうなります。

操作手順はこちらです。

• 結果を表示させたいセルに「 = COUNTIF( 」と入力
• セル範囲A2からA11を選択
• 「 , 」を入力
• 条件に「 " 」、「 ? 」、「 ? 」、「 ? 」、「 ティー 」、「 " 」の順で入力
• 「 ) 」で閉じる

6文字の「 〇〇〇ティー 」、「 レモンティー 」と「 ハーブティー 」がカウントされました。

「 ? 」を使う場合は組み合わせる文字や「 ? 」を入力する数で文字数を指定できます。

〇〇以外

比較演算子の「 <> 」を使って特定の文字を含まないセルを集計することができます。

例えば、「 <>不合格 」と指定した場合、不合格以外の項目が条件を満たしていると認識します。

COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。

テキストを直接入力する

比較演算子の「 <> 」を使って、不合格者以外のセルの個数を集計してみましょう。

セルF8に入力する数式はこうなります。

操作手順はこちらです。

• セルF8に「 = COUNTIF( 」と入力
• セル範囲D2からD21を選択
• 「 , 」を入力
• 条件に「 " 」、「 <  」、「 > 」、不合格、「 " 」の順で入力
• 「 ) 」で閉じる

条件に一致するセルの個数は9、検索範囲D2:D21に不合格以外が9名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

セルを指定する場合

先ほどと同じ条件で、セルを指定する場合の数式はこうなります。

操作手順はこちらです。

• セルF8に「 = COUNTIF( 」と入力
• セル範囲D2からD21を選択
• 「 , 」を入力
• 条件に「 " 」、「< 」、「>」、「 " 」、「 & 」の順で入力、F5をクリック
• 「 ) 」で閉じる

条件に一致するセルの個数は9、検索範囲D2:D21にセルF5の文字以外が9名いることが計算結果で表示されました。

日本語で読むように数式を書くと、こうなります。

セルを固定する

セルを参照した数式をコピーすると、参照先のセル範囲が移動して、正しく計算できないことがあります。

そんな時は絶対参照で参照先を固定しましょう。

参照とは

セル参照とは、「=A1」のように特定のセル番地を指定することを言います。

セル番地を指定(参照)することによって、指定先のセルの値が変化しても、自動的に変更された値を引っ張って自動更新してくれます。

まず、果物の項目のセル範囲A2:A15の中に、C2の文字「りんご」がいくつあるかCOUNTIF関数で集計します。

セルD2に入力する数式はこうなります。

セルD2に「りんご」の個数が反映されました。

続いてC3の文字「みかん」を集計したいので、先ほどの数式を1行下のセルD3にコピペします。

上の図のように、数式をコピペすることで参照先のセル範囲が移動します。

セル範囲も1行下に移動されてしまうため、項目セルA2の「みかん」が検索範囲から外れてしまい、このままでは正しく集計することができません。

このように、コピペすることで参照するセルが動かないよう固定することを絶対参照といいます。

セルの固定方法

固定したいセル番地の列番号(アルファベット)と行番号(数字)の左横にそれぞれ「 $(ドル記号) 」を付けることで、コピペしてもセルが常に固定されるようになります。

操作手順はこちらです。

• セルD2に「 = COUNTIF( 」と入力
• セル範囲A2からA15を選択
• 列番号と行番号の各左横に「 $ 」を入力
• 「 , 」を入力
• 条件にC2をクリック
• 「 ) 」で閉じる

次に、セルD2の数式をセルD3、セルD4にコピペします。

セル範囲が固定され、正しく集計することができました。

• もう1つの例を見てみましょう

絶対参照でセル範囲が固定されているので右にコピペしてもセル範囲が移動せず、正しく集計することができます。

サンプルシート6のセルD2に同じ数式を入力し、右方向へオートフィルでコピペしてみましょう。

このように絶対参照で固定しておけば、参照するセルは動かないままになるので、同じ数値を何度も使いたい場合に使用します。

参照方法は他にもありますので、ぜひ参照についての記事もご覧ください。

スプレッドシートでセルを参照する方法(絶対参照・相対参照・複合参照)

まとめ

Googleスプレッドシートの「COUNTIF関数」マスター編としてお伝えしました。

COUNTIF関数は大量のデータから探したい項目の数を調べるのに便利な関数で、実務でも頻繁に使われている関数のひとつです。

何通りも活用方法がありますので、ぜひ理解を深めて実践してみてください。