【スプレッドシート】COUNTIF関数の基礎から応用まで
こんにちは、たま(@closuppo)です！
今回は、スプレッドシートの「COUNTIF関数」の上級編をお伝えします。
初級編・中級編に続いてのマスター編となります。復習しながら今回の記事の内容を実践して、COUNTIF関数の理解を深めましょう。
初級編・中級編も復習しましょう
※目次をクリックすると
タイトルの先頭まで移動します（↓）
まずはじめに
COUNTIF(カウントイフ)関数とは、条件に一致するセルが何個あるか計算をしてくれる関数です。
選択した範囲の中から指定した条件に合うデータを検索し、検索条件に一致する項目の数(セル)の個数を数えてくれます。
指定する条件は【女性】などの単語だけでなく【数値】や【日付】なども指定することができ、「〇〇より大きい」「〇月〇日以降」など比較条件を指定して数えることもできます。
例えば、下図はCOUNTIF関数を使って、表の中の指定した範囲から【女性】を条件と指定して、人数を自動で調べて集計結果を出しています。
それでは、サンプルシートを使ってCOUNTIF関数でセルの個数を求めてみましょう。
サンプルシートを用意しているので
コピーしてご活用ください
はじめてのCOUNTIF関数
COUNTIF関数の構文は以下の通りです。
COUNTIF関数
=COUNTIF(❶範囲, ❷検索条件)
詳しくご説明しますので、順番に入力してみてください
下図の名簿の中から女性の人数をCOUNTIF関数で集計してみましょう。
結果を表示させたい女性の人数欄セルB3に=COUNTIF(と入力
セル範囲はマウスで選択できます
検索したい範囲の、性別が記載されたセル範囲B6:B11を指定
「 , (カンマ) 」を入力
条件:B6:B11の中から女性の人数を調べたいので、"女性"と入力
文字を指定する場合は記号「 " (ダブルクォーテーション) 」で文字の前後を囲みます。
範囲と同じで、条件もセルをクリックして指定することができます。
次の項目でご説明しますので、続けてご覧ください。
条件に一致するセルの個数は3、検索範囲B6:B11に女性が3名いることが計算結果で表示されました。
知りたい結果が出たけど、どういう仕組みか
いまひとつわからない…
日本語に直して読んでみましょう
B3のセル=
セル範囲B6:B11の中から条件とする女性を探して一致するセルの個数を数えた結果を表示する
指定した範囲B6:B11の中に女性と入力されたセルは3つあるので、セルB3に表示される答えは3となります。
声に出して読んでみるとわかってきた気がします。
慣れるまでは難しく感じますが、くりかえし実践することで理解が深まります。
苦手意識を持たずにトライしてみましょう。
プルダウンを使って条件を選択する
一つ前の項目でも少し触れましたが、=COUNTIF(❶範囲, ❷検索条件)の❶範囲と同じように❷検索条件もセルを選択して指定することができます。
条件を手入力するよりも、セルを選択して指定することで
半角・全角などの入力ミスを防ぐこともできます。
下図のように、条件として指定するセルA2の項目をプルダウンで選択できるように設定しておくと、選択した条件に一致する集計結果をB3に返すことができます。
それでは一緒に実践してみましょう。
サンプルシートはシート2をお使いください。
プルダウンリストから作成する場合はサンプルシート2、セルA2のデータの入力規則を削除してからお使いください。
結果を表示させたい人数欄セルB２に入力する数式はこうなります。
操作手順はこちらです。
- セルB2に「 = COUNTIF( 」と入力
- マウスでB6からB11を選択
- 「 , 」を入力
- マウスでA2をクリック
- 「 ) 」で閉じる
条件に一致するセルの個数は3、検索範囲B6:B11に男性が3名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
B2のセル=
セル範囲B6:B11の中からA2のプルダウンで選択して指定した条件(男性)を探して一致するセルの個数を数えた結果を表示する
くりかえし読むことで理解できました
プルダウンで選択できるようにしておくと計算結果の欄を一つにできるので、結果が見やすい表をつくることができますね。
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関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。
ポイントをお伝えします
ポイント
上の図は、合格基準の80点に満たない不合格の人数を集計しています。
条件を【80より小さい】と指定しているので、80を含まない79点までが不合格にカウントされます。
それでは実践してみましょう。
サンプルシートはシート3をお使いください。
検索条件にテキストを直接入力する
比較演算子の「 < (小なり) 」を使って80より小さい不合格のセルの個数を集計してみましょう。
先ほどご説明したポイントをおさえて数式をテキストで直接入力します。
セルF6に入力する数式はこうなります。
操作の手順はこちらです。
条件に一致するセルの個数は11、検索範囲C2:C21に80より小さい不合格が11名いることが計算結果で表示されました。
日本語で読んでみよう
F6のセル=
セル範囲C2:C21の中から80より小さいを探して一致するセルの個数を数えた結果を表示する
❷検索条件にセルを指定する
先ほどと同じように、比較演算子の「 < 」を使って80より小さい不合格のセルの個数を集計します。
検索条件には数値の入ったセル番地を指定しましょう。
ポイント
セルF6に入力する数式はこうなります。
操作の手順はこちらです。
条件に一致するセルの個数は11、検索範囲C2:C21にセルG1の数値より小さい不合格が11名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F6のセル＝
セル範囲C2:C21の中からセルG1の数値より小さいを探して一致するセルの個数を数えた結果を表示する
同じ結果を求める場合でも、条件の指定方法が2つあることがわかりましたね
【〇〇より大きい】の場合はどうなるんですか？
【〇〇より大きい】も操作手順は同じで、数値の前には比較演算子「 >(大なり) 」を入力します。
〇〇以上
比較演算子の「 >= 」を使って【〇〇以上】のセルの個数をカウントすることができます。
COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。
ポイントをお伝えします
ポイント
上の図は、合格基準の80点以上の合格人数を集計しています。
条件を【80以上】と指定しているので、80を含む大きい点が合格にカウントされます。
サンプルシートはシート3をお使いください
検索条件にテキストを直接入力する
比較演算子の「 >= 」を使って80以上の合格のセルの個数を集計してみましょう。
先ほど説明したポイントをおさえて数式をテキストで直接入力します。
セルF8に入力する数式はこうなります。
操作の手順はこちらです。
条件に一致するセルの個数は9、検索範囲C2:C21に80以上の合格が9名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F8のセル=
セル範囲C2:C21の中から80以上を探して一致するセルの個数を数えた結果を表示する
次は、日付を使って集計する方法を学びましょう
検索条件にセルを指定する
比較演算子は数値だけでなく日付を指定することができますので、日付に【〇〇以上】と指定しすると、【〇〇日以降】として調べることができます。
例えば、「 ">=4/1" 」と指定すると、4/1以降という条件になります。
【〇〇以上】は〇〇を含むので、4/1を含む4/1以降となるんですね
それではポイントを抑えて実践してみましょう
セルF10の4月以降の受験者の人数を集計してみましょう。
検索条件には日付が入ったセル番地を指定します。
ポイント
セルF10に入力する数式はこうなります。
操作の手順はこちらです。
条件に一致するセルの個数は6、検索範囲A2:A21にセルG2の日付以降の受験した人が6名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F10のセル=
セル範囲A2:A21の中からセルG2の数値以上を探して一致するセルの個数を数えた結果を表示する
〇〇を含む
COUNTIF関数は、ワイルドカードを使って特定の文言を含むセルをカウントすることができます。
「 * 」の使い方についてはワイルドカードの項目で解説しましたが、この項目では「 * 」を使って【〇〇を含む】の使い方を見てみましょう。
COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。
ポイント
それでは実践してみましょう。
サンプルシートはシート4をお使いください。
テキストを直接入力する
「 * (アスタリスク) 」を使って「ティー」を含む文字列のセルの個数を集計してみましょう。
セルD3に入力する数式はこうなります。
操作手順はこちらです。
条件に一致するセルの個数は5、検索範囲A2:A11にティーを含む文字列が5個あることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
D3のセル=
セル範囲A2:A11の中からティーを含むセルを探して一致するセルの個数を数えた結果を表示する
検索条件にセルを指定する
先ほどと同じように、「 * (アスタリスク) 」を使って「ティー」を含む文字列のセルの個数セルの個数を集計します。
検索条件には数値の入ったセル番地を指定しましょう。
セルD3に入力する数式はこうなります。
操作手順はこちらです。
条件に一致するセルの個数は5、検索範囲A2:A11にセルD2の文字を含む文字列が5個あることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
D3のセル=
セル範囲A2:A11の中からセルD2の文字を含むセルを探して一致するセルの個数を数えた結果を表示する
あいまい検索
ワイルドカードの項目でも触れましたが、ワイルドカードを活用すると、部分的に一致する文字を条件に指定して検索することもできます。
先ほどは「 * 」を使って「ティー」を含む文字列を検索しましたね。
「 ? 」を使えば、「 ? 」と部分的な文字を組み合わせた条件で、文字数を指定して検索することができます。
数式にするとこうなります。
操作手順はこちらです。
6文字の「 〇〇〇ティー 」、「 レモンティー 」と「 ハーブティー 」がカウントされました。
「 * 」と「 ? 」の違いは何ですか？
簡単に言うと文字数を指定するかどうか、という違いがあります
「 ? 」を使う場合は組み合わせる文字や「 ? 」を入力する数で文字数を指定できます。
たくさんのデータの中から【〇〇を含む】が検索できるととても便利ですね
ワイルドカードの組み合わせで何通りも検索ができますので、色々試してみてください。
〇〇以外
比較演算子の「 <> 」を使って特定の文字を含まないセルを集計することができます。
例えば、「 <>不合格 」と指定した場合、不合格以外の項目が条件を満たしていると認識します。
COUNTIF関数の構文は =COUNTIF(❶範囲, ❷検索条件) でしたね。
ポイント
それでは実践してみましょう。
サンプルシートはシート3をお使いください。
テキストを直接入力する
比較演算子の「 <> 」を使って、不合格者以外のセルの個数を集計してみましょう。
セルF8に入力する数式はこうなります。
操作手順はこちらです。
条件に一致するセルの個数は9、検索範囲D2:D21に不合格以外が9名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F8のセル=
セルを指定する場合
先ほどと同じ条件で、セルを指定する場合の数式はこうなります。
操作手順はこちらです。
条件に一致するセルの個数は9、検索範囲D2:D21にセルF5の文字以外が9名いることが計算結果で表示されました。
日本語で読むように数式を書くと、こうなります。
F8のセル=
色々なパターンで検索してみて、使い方がわかってきました
次が応用編の最後です！ここまで学んだことを思い出して実践してみましょう。
セルを固定する
セルを参照した数式をコピーすると、参照先のセル範囲が移動して、正しく計算できないことがあります。
そんな時は絶対参照で参照先を固定しましょう。
絶対参照とは参照方式のひとつで、参照するセルを固定することをいいます。
参照って何だったっけ？
参照について復習しながら見ていきましょう。サンプルシートはシート5をお使いください。
参照とは
セル参照とは、「=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関数は大量のデータから探したい項目の数を調べるのに便利な関数で、実務でも頻繁に使われている関数のひとつです。
何通りも活用方法がありますので、ぜひ理解を深めて実践してみてください。
