はじめに
スプレッドシートには関数が多々ありますが、その中でも「COUNTIFS」と「SUMIFS」の2つを覚えれば基本的なデータ分析を実施することは可能です。
そこでこの記事では「COUNTIFS」の使い方についてご紹介します。
「SUMIFS」についてはこちらの記事で解説してますので、同時に学んでください。
「COUNTIFS」で何ができるのか?
そもそも、「COUNTIFS」で何ができるのかがわからないという方に、ここでは「COUNTIFS」の関数自体について紹介します。
「COUNTIFS」では「条件に合った数を数える」ことができます。
ただ、この定義だけではわかりづらいと思いますので、具体的に何ができるのかを紹介します。
月ごとの登録人数の把握
よく使われるのが月ごとに数を数えることです。
- 1月に何人登録してきたか
- 退会が毎月何人してきているのかを把握し、いつの時期に退会が多いか把握したい
など月ごとの比較を行う際に「COUNTIFS」は使用します。
年齢層や性別、地域などの把握
次に、年齢層や住んでいる地域などの個人情報を集計するときに使用します。
- 20代と30代の登録者がそれぞれ何人いるのか
- 関東と関西、どっちのほうが多いのか
など、サービスのターゲットの属性層などを把握する際に使用します。
アンケート結果の集計(個数判断)
最後によく利用される例はアンケートの集計です。
アンケート結果は「選択式」や「記述式」など様々ありますが、選択式の回答であれば「COUNTIFS」を使用することで集計可能です。
記述式も集計可能ではあるが、少しアンケート結果を修正する必要があります。
「COUNTIF」と「COUNTIFS」の違い
「COUNTIFS」について詳しく説明する前に似た関数の「COUNTIF」との違いを紹介します。
この2つの関数の違いは「条件が1つだけか複数選択可能か」です。
数えたい条件の数が1つの場合→「COUNTIF」
複数条件の数を数える場合→「COUNTIFS」
しかし、「COUNTIFS」を使用すれば1つの条件の場合も対応できるため私は「COUNTIFS」のみを使うようにしています。
大は小を兼ねるです
ここでは「COUNTIF」について解説はしませんがどうしても「COUNTIF」を使いたい方はGoogleで再度検索をしていただければと思います。
「COUNTIFS」の形を説明
実践に入る前に「COUNTIFS」の関数自体の説明をします。ただ、文章だけでは理解が難しいので、正直ここは飛ばして、実践をしていただいて大丈夫です。
スプレッドの関数説明では
COUNTIFS(条件範囲1, 条件1, [条件範囲2, …], [条件2, …])
例
COUNTIFS(A1:A10, ">20", B1:B10, "<30")
概要
複数の基準に応じた範囲の個数を返します。
条件範囲1
[条件1] に対して検証する範囲です。
条件1
[条件範囲1] に適用するパターンまたはテストです。
条件範囲2… - [任意] 反復可能
検証対象となる追加の範囲です。
条件2… - [任意] 反復可能
検証対象となる追加の条件です。
と書かれています。
条件範囲:数えたい情報の範囲
例:登録日、性別、年齢など
条件:数える条件
例:20代、1月に登録、男性など
条件範囲は「数えたい情報が集まっている場所」という意味で、「A:A」など範囲で記載します。
条件は「数えたい条件の詳細」という意味で、「”>20”」などで記載します。
ただ、正直この情報だけだと理解しづらい部分が多いため、実際に手を動かして学んでいただければと思います。
とりあえず「COUNTIFS」は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") |
関数自体はこちらで計算ができます。
それぞれ解説していくと
ここで、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") |
と関数を作成するとどうなるのかを説明していきます。
こちらの関数では「2021年1月1日 0時00分00秒」~「2021年1月31日 0時00分00秒」の間に登録した方しか数えることができません。
そのため、「2021年1月31日 0時00分01秒」~「2021年1月31日 23時59分59秒」という1月31日に登録した方のほぼ100%の方が数え漏れになります。
これだと正確なデータを集めることができないですよね。
そのため、
A | B | |
---|---|---|
1 | 21年1月 | =countifs('使用データ'!B:B,">=2021/01/01",'使用データ'!B:B,"<2021/02/01") |
として「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,"女") |
それぞれ解説していくと
さきほどの月と同様に年齢を20~29歳で区切り、最後に性別を判断しています。年齢の部分は「<=29」ではなく「<30」としても同じ結果を数えることは可能です。
個人的には「<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,"非常に悪い") |
①②よりかは簡単なので、正直解説することはあまりないのですが、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) |
この関数を見ていただいたらわかる通り、さきほど手で入力していたアンケート項目がどこにも記載されていません。
しかし、さきほどと同じ数字が数えられています。これが変数指定というものです。
こちらは表と比較しながら説明していきます。
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,"*田中*") |
ワイルドカードは条件で使用し、「*」の部分を表してます。
「*田中*」と記載すると「〇〇田中〇〇」このように「田」と「中」が連続で並ぶがその両端には何か文字が入っている状況を表しています。
このことから、条件の前に「*」を入れる(*田中)と、条件よりも前に情報(〇〇田中)がある場合、条件の後に「*」を入れる(田中*)と、条件よりも後に情報(田中〇〇)がある場合を数える時に使用できます。
- メールアドレスの会社数を調査
文字が入力されているところを数える
次にワイルドカードでは「文字が入力されている数」を数えることができます。
例えば、アンケートの任意記入欄が何人入力してくれたかの確認をするときに使えます。
A | B | |
---|---|---|
1 | アンケート結果 | 数字 |
2 | 感想記入 | =countifs('使用データ'!J:J,"*") |
条件の部分に「”*”」と入力しているだけになります。
このように記入すると「空白以外の文字列」を数えることができます。
こちらがなぜ数えられるのか、私は正直わかっていないため、もしご存知の方は私のTwitterにDMで教えていただきたく思います。
ワイルドカードの注意点
ここでワイルドカードを使う上での注意点を紹介します。
電話番号の数を集計するとき
電話番号の個数を数えるときにワイルドカードを使用すると上手くいかないことがあります。
失敗例
さきほど使用した「*」を使用して電話番号の個数を数えてみます。
A | B | |
---|---|---|
1 | 項目 | 数字 |
2 | 電話番号の数 | =countifs('使用データ'!E:E,"*") |
実際に数字を見てみると電話番号の数と数えた数に誤差があります。
電話番号の表記方法が「0345636350」と「03-4563-6350」の2種類あり
0345636350→初期設定だと「345636350」と最初のゼロが消え、「数値扱い」になる
03-4563-6350→「文字列扱い」になる
と同じ電話番号なのに値の種類が「数値」と「文字列」で異なります。
ワイルドカードでは「文字列」しかカウントできない「数値」で記載されている電話番号は数えることができませんでした。
対処法
これを対処するためにこちらを使います。
A | B | |
---|---|---|
1 | 項目 | 数字 |
2 | 電話番号の数 | =countifs('使用データ'!E:E,"*")+countifs('使用データ'!E:E,">0") |
まず「文字列」を数えるために「”*”」を使用し、「数値」を数えるために「”>0”」と記載したものを足し合わせることで数えることができます。
まず「数値」を数える方法ですが「”>0”」と記載しており、こちらの説明をしていきます。
「”>0”」は「0より大きいもの」という意味になります。
電話番号を数値で記載すると「345636350」のようになり、0よりも大きな数字になりますよね?
この考え方を使い、「0より大きいもの」として数えることで「数値」の電話番号を数えることができます。
「文字列の電話番号」と「数値の電話番号」を足し合わせることで電話番号の数を数えることができました。
まとめ
「COUNTIFS」はスプレッドを使用する上で100%身につけておかなくてはいけない関数です。
この記事で「COUNTIFS」の使い方を身につけ、合わせて「SUMIFS」の記事で必須関数2つを身につけていただければと思います。