【スプレッド初心者必見】COUNTIFSの使い方【関数紹介】

はじめに

スプレッドシートには関数が多々ありますが、その中でも「COUNTIFS」と「SUMIFS」の2つを覚えれば基本的なデータ分析を実施することは可能です。

そこでこの記事では「COUNTIFS」の使い方についてご紹介します。

「SUMIFS」についてはこちらの記事で解説してますので、同時に学んでください。

【スプレッド初心者必見】SUMIFSの使い方【関数紹介】

「COUNTIFS」で何ができるのか？

そもそも、「COUNTIFS」で何ができるのかがわからないという方に、ここでは「COUNTIFS」の関数自体について紹介します。

「COUNTIFS」では「条件に合った数を数える」ことができます。

ただ、この定義だけではわかりづらいと思いますので、具体的に何ができるのかを紹介します。

月ごとの登録人数の把握

よく使われるのが月ごとに数を数えることです。

など月ごとの比較を行う際に「COUNTIFS」は使用します。

年齢層や性別、地域などの把握

次に、年齢層や住んでいる地域などの個人情報を集計するときに使用します。

など、サービスのターゲットの属性層などを把握する際に使用します。

アンケート結果の集計（個数判断）

最後によく利用される例はアンケートの集計です。

アンケート結果は「選択式」や「記述式」など様々ありますが、選択式の回答であれば「COUNTIFS」を使用することで集計可能です。

「COUNTIF」と「COUNTIFS」の違い

「COUNTIFS」について詳しく説明する前に似た関数の「COUNTIF」との違いを紹介します。

この2つの関数の違いは「条件が1つだけか複数選択可能か」です。

しかし、「COUNTIFS」を使用すれば1つの条件の場合も対応できるため私は「COUNTIFS」のみを使うようにしています。

ここでは「COUNTIF」について解説はしませんがどうしても「COUNTIF」を使いたい方はGoogleで再度検索をしていただければと思います。

「COUNTIFS」の形を説明

実践に入る前に「COUNTIFS」の関数自体の説明をします。ただ、文章だけでは理解が難しいので、正直ここは飛ばして、実践をしていただいて大丈夫です。

スプレッドの関数説明では

と書かれています。

条件範囲は「数えたい情報が集まっている場所」という意味で、「A:A」など範囲で記載します。

条件は「数えたい条件の詳細」という意味で、「”>20”」などで記載します。

ただ、正直この情報だけだと理解しづらい部分が多いため、実際に手を動かして学んでいただければと思います。

実際に「COUNTIFS」を使用してみましょう

それでは「COUNTIFS」についてなんとなくイメージが湧いたと思いますので、実際に手を動かして学んでいきましょう。

解説だけでなく私が「COUNTIFS」を使用した「動画」と実際に手を動かすことができるデータも準備していますので、全てを活用して「COUNTIFS」を学んでください。

「COUNTIFS」例題スプレッド

①月ごとの登録人数の把握

まずは基本的な月ごとの登録人数の把握をしていきます。

今回は「2021年1月」「2021年2月」「2021年3月」の登録人数を数えてみましょう。

	A	B
1	月	数字
2	21年1月	=countifs('使用データ'!B:B,">=2021/01/01",'使用データ'!B:B,"<2021/02/01")
3	21年2月	=countifs('使用データ'!B:B,">=2021/02/01",'使用データ'!B:B,"<2021/03/01")
4	21年3月	=countifs('使用データ'!B:B,">=2021/03/01",'使用データ'!B:B,"<2021/04/01")

「COUNTIFS」例題スプレッド

関数自体はこちらで計算ができます。

それぞれ解説していくと

ここで、2個目の条件がなぜ「1日」にしているかを説明します。

まずこれを説明するために「<」と「<=」の違いを説明する必要があります。

「以下」と「未満」の違いを聞いたことありませんか？条件で設定した値が含むか含まないかを考える時に使用するのですが、今回が「未満」を使用しています。

それではなぜ「1日」を使用したかの説明をします。

「2021年1月」を例にして説明するとスプレッドの表記では「2021/01/01」と表記されています。

ただ、この値を厳密に表記すると「2021/01/01 0:00:00」となります。

そのため、2021/01/01と表記すると「2021年1月1日 0時00分00秒」となります。

それでは実際に

	A	B
1	21年1月	=countifs('使用データ'!B:B,">=2021/01/01",'使用データ'!B:B,"<=2021/01/31")

「COUNTIFS」例題スプレッド

と関数を作成するとどうなるのかを説明していきます。

これだと正確なデータを集めることができないですよね。

そのため、

	A	B
1	21年1月	=countifs('使用データ'!B:B,">=2021/01/01",'使用データ'!B:B,"<2021/02/01")

「COUNTIFS」例題スプレッド

として「2021年1月1日 0時00分00秒」~「2021年1月31日 23時59分59秒」まで数えるようにする必要があります。

このあたりは細かい部分にはなるものの、正しい集計をするためには必ず理解しておく必要があるため、わからなければ私のTwitterにDMしていただければと思います。

②年齢層や性別、地域などの把握

次はユーザー属性の傾向を掴んでいきましょう。

今回は「20代」の「男女」を数えてみましょう。

	A	B
1	性別	数字
2	男性	=countifs('使用データ'!H:H,">=20",'使用データ'!H:H,"<=29",'使用データ'!D:D,"男")
3	女性	=countifs('使用データ'!H:H,">=20",'使用データ'!H:H,"<=29",'使用データ'!D:D,"女")

「COUNTIFS」例題スプレッド

それぞれ解説していくと

さきほどの月と同様に年齢を20~29歳で区切り、最後に性別を判断しています。年齢の部分は「<=29」ではなく「<30」としても同じ結果を数えることは可能です。

③アンケート結果の集計（個数判断）

基本編の最後にはアンケート結果の集計を行っていきます。

アンケート結果を正の字で数えることはこれでやらなくて済みます。

今回はアンケート項目が5個あるので、それぞれの個数を数えていきましょう。

	A	B
1	アンケート結果	数字
2	非常に良い	=countifs('使用データ'!I:I,"非常に良い")
3	良い	=countifs('使用データ'!I:I,"良い")
4	普通	=countifs('使用データ'!I:I,"普通")
5	悪い	=countifs('使用データ'!I:I,"悪い")
6	非常に悪い	=countifs('使用データ'!I:I,"非常に悪い")

「COUNTIFS」例題スプレッド

①②よりかは簡単なので、正直解説することはあまりないのですが、1つ思ったのは「項目を入力するのはめんどくさい」ということです。

アンケート結果が増えれば増えるほど「関数の中の条件」を変更していく必要がでてきます。

ただ、その作業を減らしてもっと簡単に集計することも可能なので、それは応用編で説明していきます。

応用編

次に、基本編で用いた考えを応用して「COUNTIFS」の使い方を紹介していきます。

「COUNTIFS」の関数の使用だけでなく、関数全体で用いられる考え方も紹介していきますのでご参考にしていただければと思います。

変数指定

さきほどのアンケート結果の集計をもとに「変数指定」ということを説明していきます。

	A	B
1	アンケート結果	数字
2	非常に良い	=countifs('使用データ'!I:I,A2)
3	良い	=countifs('使用データ'!I:I,A3)
4	普通	=countifs('使用データ'!I:I,A4)
5	悪い	=countifs('使用データ'!I:I,A5)
6	非常に悪い	=countifs('使用データ'!I:I,A6)

「COUNTIFS」例題スプレッド

この関数を見ていただいたらわかる通り、さきほど手で入力していたアンケート項目がどこにも記載されていません。

しかし、さきほどと同じ数字が数えられています。これが変数指定というものです。

こちらは表と比較しながら説明していきます。

A2と書かれていますが、この表の中のどこかというと「非常に良い」という部分を指してます。

	A	B
1	アンケート結果	数字
2	非常に良い	=countifs('使用データ'!I:I,A2)
3	良い	=countifs('使用データ'!I:I,A3)
4	普通	=countifs('使用データ'!I:I,A4)
5	悪い	=countifs('使用データ'!I:I,A5)
6	非常に悪い	=countifs('使用データ'!I:I,A6)

これが何を指すかといいますと、A2と入力すれば勝手に「非常に良い」という言葉に置き換わるのです。

本当に置き換わるのか不安な方、スプレッド上に「=A2」と入力してみてください。

すると、入力したところに「非常に良い」という言葉が表示されます。

このように、A2と入力すれば「非常に良い」という言葉に置き換えることができます。

同様に、A3、A4、A5、A6とすることで、アンケート項目の名前に自動で置き換わり、簡単に全項目の集計を行うことが可能になります。

さらに楽をするために、このようなやり方もございます。

※動画※

こうすることで、1つ作成すれば他のものもすぐ作成することができます。

ワイルドカードについて

次にワイルドカードというものを紹介していきます。

かなり便利なのですが、馴染みのない言葉なので、苦手意識がでるかもしれません。

そのため、ここでも実例を交えてワイルドカードの使い方をご紹介していきます。

ワイルドカードには用途が

の2つあります。

それぞれ実際にどのように使用するかをご紹介していきます。

部分一致を数える

まず、部分一致を数えるということで、そもそも部分一致というものがなんなのかをお話してから、使い方の説明を行います。

部分一致というのは「指定した言葉がその文字列内のどこかに含まれているかどうか」を判断するものになります。

実際に部分一致を使った例として「名前の中から苗字が”田中”という人を数える」関数を作ってみます（実際にこのようなことがあるかは不明ですが）。

	A	B
1	苗字	数字
2	田中	=countifs('使用データ'!C:C,"*田中*")

「COUNTIFS」例題スプレッド

ワイルドカードは条件で使用し、「*」の部分を表してます。

「*田中*」と記載すると「〇〇田中〇〇」このように「田」と「中」が連続で並ぶがその両端には何か文字が入っている状況を表しています。

このことから、条件の前に「*」を入れる（*田中）と、条件よりも前に情報（〇〇田中）がある場合、条件の後に「*」を入れる（田中*）と、条件よりも後に情報（田中〇〇）がある場合を数える時に使用できます。

文字が入力されているところを数える

次にワイルドカードでは「文字が入力されている数」を数えることができます。

例えば、アンケートの任意記入欄が何人入力してくれたかの確認をするときに使えます。

	A	B
1	アンケート結果	数字
2	感想記入	=countifs('使用データ'!J:J,"*")

「COUNTIFS」例題スプレッド

条件の部分に「”*”」と入力しているだけになります。

このように記入すると「空白以外の文字列」を数えることができます。

こちらがなぜ数えられるのか、私は正直わかっていないため、もしご存知の方は私のTwitterにDMで教えていただきたく思います。

ワイルドカードの注意点

ここでワイルドカードを使う上での注意点を紹介します。

電話番号の数を集計するとき

電話番号の個数を数えるときにワイルドカードを使用すると上手くいかないことがあります。

失敗例

さきほど使用した「*」を使用して電話番号の個数を数えてみます。

	A	B
1	項目	数字
2	電話番号の数	=countifs('使用データ'!E:E,"*")

「COUNTIFS」例題スプレッド

実際に数字を見てみると電話番号の数と数えた数に誤差があります。

と同じ電話番号なのに値の種類が「数値」と「文字列」で異なります。

ワイルドカードでは「文字列」しかカウントできない「数値」で記載されている電話番号は数えることができませんでした。

対処法

これを対処するためにこちらを使います。

	A	B
1	項目	数字
2	電話番号の数	=countifs('使用データ'!E:E,"*")+countifs('使用データ'!E:E,">0")

「COUNTIFS」例題スプレッド

まず「文字列」を数えるために「”*”」を使用し、「数値」を数えるために「”>0”」と記載したものを足し合わせることで数えることができます。

まず「数値」を数える方法ですが「”>0”」と記載しており、こちらの説明をしていきます。

電話番号を数値で記載すると「345636350」のようになり、0よりも大きな数字になりますよね？

この考え方を使い、「0より大きいもの」として数えることで「数値」の電話番号を数えることができます。

「文字列の電話番号」と「数値の電話番号」を足し合わせることで電話番号の数を数えることができました。

まとめ

「COUNTIFS」はスプレッドを使用する上で100%身につけておかなくてはいけない関数です。

この記事で「COUNTIFS」の使い方を身につけ、合わせて「SUMIFS」の記事で必須関数2つを身につけていただければと思います。

【スプレッド初心者必見】SUMIFSの使い方【関数紹介】