COUNTIFSとは?
COUNTIFSは、ある範囲の中から条件に一致したものが何個あるか?を数える関数です。
COUNTIF関数を使ったことがある方は、条件を複数指定できる関数、と思ってもらえれば大丈夫です。
今回はCOUNTIFS関数の基本からちょっとした応用までを解説していきます。
基本の使い方
引数の指定方法
COUNTIFS関数では、「範囲」と「検索条件」の2種類を指定することで条件に応じた件数を出すことができます。
COUNTIF関数と比べると、検索条件の部分が1個ではなく、いくつでも追加できるという違いがあります。
今回も論より証拠、ということで試しにサンプルで解説していきたいと思います。
問題:家計簿内で、11月15日までで、コンビニで何回買い物をしたか?を数えて下さい。データは下記です。
答えは簡単ですよね!「1回」です。
これをCOUNTIFS関数で出すとこうなります。
COUNTIFSではこのように指定することで2つ以上の条件のカウントを行うことができます。
では、具体的な使い方を解説していきますね。
まずは、関数の概要から記載します。
=COUNTIFS(範囲1, 検索条件1,範囲2,検索条件2,…)
範囲1 検索したい対象範囲を選択します。A1:B3のようにセルの範囲で指定します。
検索条件1 検索したい項目を指定します。範囲と検索条件を複数指定できるCOUNTIFS関数では、範囲2以降で同じように何個でも指定していくことができます。
今回の例題の場合関数の記述の手順を解説します。
まずは、
=COUNTIFS(
とセルに入力していきましょう。
そうしたら、次は範囲と検索条件を入力していきます。
今回は探したいものが2種類ありますよね。
・11月15日まで
という条件と
・コンビニで
という条件です。
そのため、
・日付が書いてある範囲から11月15日までに当てはまり
かつ
・項目の範囲でコンビニと記載があるもの
という2つを関数内に記述必要があります。
まずは1つめの範囲を入力していきます。
「日付が書いてある範囲」
はA2〜A9セルになるので、A2:A9と記述をしましょう。
=COUNTIF(A2:A9,
範囲を入力のあとは、「,」をつけ、次の条件入力に移ります。
次に検索条件をしていきましょう。
検索条件は「探したいもの」です。今回は11月15日までのデータ、つまり11月15日より小さいデータを検索します。
○○より小さいデータを検索したいときは、”<”を使うので、
“<2020/11/15″
となります。
=COUNTIF(A2:A9,”<2020/11/15″,
終わったら「,」をつけ忘れないようにしましょう。
次に2つめの条件を入力していきます。
項目名を検索するため、範囲は 「B2:B9」となりますね。
=COUNTIF(A2:A9,”<2020/11/15″, B2:B9
検索条件では、コンビニでの回数をカウントしたいため、項目名が「コンビニ」となっているものを指定します。
エクセル/スプレッドシートでは、文字列は「”」(ダブルクオーテーション)で囲うというルールがあるので、「”コンビニ”」と記載しましょう。
=COUNTIFS(A2:A9,”<2020/11/15″,B2:B9,”コンビニ”)
これで記述が完成になります。
エンターを押して確定をすると今回出したい「1」という結果が出てくることがわかります。
例題では、検索条件で日付、文字列の指定を行っていきましたが、
COUNTIFSでは様々な検索条件の指定の仕方があります。
ここからはCOUNTIFS関数でできる様々な検索条件の種類を解説していきます。
数字での条件指定
まずは、数字での一致を解説していきます。
○○と一致する
数字での完全一致、例えばデータの中で50000円に一致するものはいくつあるのか?を検索したい場合は、下記のようになります。
=COUNTIFS(C2:C9,50000)
※条件が1つのみ指定可能なCOUNTIFでも同様に入力することで同じ答えを出す事ができます。
○○以上・○○以下・○○の間
数字については、○○より高い、○○より低い、という指定も行うことができます。複数条件の指定が可能なCOUNTIFSでは、
3000円以上10000円以下
という指定を行うことが可能です。
以上・以下等の指定では、不等号を使っていきます。(不等号わからないよーといった人は、単なる記号として覚えればOK)
不等号の種類は以下の4種類です。
<○○(○○より小さい) >○○(○○より大きい)
>=○○(○○以上)
<=○○(○○以下) 「=」(イコール)をつけることで、検索する値が含むようになる、とおぼえておいて下さい。 3000円から10000円の間にある項目はいくつあるのか?という指定では、下記のようになります。 =COUNTIFS(C2:C9,”>=3000″,C2:C9,”<=10000″) ※ちなみに、3000と10000を数値として指定する必要がある場合は下記のように指定しても同じ答えを得る事ができます。 =COUNTIFS(C2:C9,”>=”&3000,C2:C9,”<=”&10000) 「&」を使うことで文字列と数値や、文字列とセル等違う性質のものを結合することができます。つまり、「”>=”&C3」と記述することでC3セルの内容以上、という指定を行うことができます。別記事でここは解説していきたいと思います。
文字列での指定方法
次に文字列での指定を解説していきます。
数値とことなる部分は、「”」(ダブルクオテーション)で検索条件を囲うか否かです。
エクセル/スプレッドシートではダブルクオーテーションで囲うことで「このデータはテキストだよ!」と認識されます。囲わない場合はテキスト以外のデータとして認識されます。
そのため、テキストを探したい場合はダブルクオーテーションで囲って検索条件の指定を行いましょう。
○○と一致する
最初の例題と同じですが、項目がコンビニとなっているものを数えたい場合、
=COUNTIFS(B2:B9,”コンビニ”)
と記載しましょう。
※条件が1つのみ指定可能なCOUNTIFでも同様に入力することで同じ答えを出す事ができます。
○○を含む
テキスト検索でよく使われる方法は、「あいまい検索」です。
データによっては、同じくコンビニで買ったものでも
コンビニ(食費)
コンビニ(日用品)
コニビニエンスストア
のように似ているけど違う項目が出現してしまうことがあると思います。
こういったケースでも数えられるようにするのがあいまい検索です。
指定方法は下記になります。
=COUNTIFS(B2:B11,”コンビニ“)
この指定をすることで、コンビニを”含む”データを数える事ができます。
曖昧検索には「*」(ワイルドカード)を利用します。
前後にワイルドカードをつけることで、コンビニを含むという指定をすることができます。
(この検索方法は私もものすごく多用するため覚えておいて損は絶対にないです!)
前に○○を含む・後に○○を含む
ワイルドカードを利用することで、細かいあいまい検索を行うことも可能です。
例えば、
・コンビニ(おにぎり)
は数えたいが、
・海外コンビニ
は除きたいという場合(データのネーミングセンスは置いといて…)
=COUNTIFS(B2:B11,”コンビニ*”)
というように指定を行います。
つまり、ワイルドカードを指定した場合は、その方向があいまい検索になる、と覚えていけば良いのです。
逆に
・海外コンビニ
は数えるが、
・コンビニ(おにぎり)
は数えないという場合
=COUNTIFS(B2:B11,”*コンビニ”)
となります。
少し応用になりますが、
=COUNTIFS(B2:B11,”コンビニ*”)
と入力すれば
「コンソールビニエンス」
というコンとビニの間になにか文字列が入っているへんてこなデータを数えるようにできます。
日付での指定方法
○日に一致するものを数える
「○日に買ったものが何個あるか知りたい」といった場合どのように入力するかを解説していきます。
日付指定の場合、入力する形式は異なりますが、今までとほとんど一緒です。
日付指定したい場合はエクセル/スプレッドシートが「日付」と認識できる形式で検索条件を入力してあげなければなりません。
例えば、
2020/11/1
のように、年、月、日を「/」(スラッシュ)で区切りながら入力する方法が当てはまります。
入力方法がわかればもうおわかりかと思います。
11月20日に買ったものを数えたい場合は、下記になります。
=COUNTIFS(A2:A9,”2020/11/20″)
○○日〜○○日に買ったものを数える
少し工夫をしていくことで様々な指定をすることができます。
1日〜15日に買い物をした個数を知りたい場合
・1日より後(1日以上)
かつ
・15日より前(15日以下)
という2つの条件になるので、
=COUNTIFS(A2:A11,”>=2020/11/1″,A2:A11,”<=2020/11/15″)
というようになります。不等号の使い方は既出ですね。
不等号の種類は以下の4種類です。
<○○(○○より小さい) >○○(○○より大きい)
>=○○(○○以上)
<=○○(○○以下)
もちろん日付指定だけでなくても、問題ありませんので、
1日〜15日にコンビニで買い物をした個数を知りたい場合は
=COUNTIFS(A2:A11,”>=2020/11/1″,A2:A11,”<=2020/11/15″,B2:B11,”コンビニ”)
となります。
条件をいくつでも指定できるのは本当に便利ですので、ぜひ色々な指定方法を試してみて下さい。
その他のcount系の関数
”何かを数える”という操作はエクセルはじめデータ処理系においては必須中の必須になります。
最後にCOUNTIF以外にもある便利なCOUNT系の関数を少し紹介したいと思います。
・COUNT(数値の個数をカウントする)
・COUNTA(空白以外のデータの個数をカウントする)
・COUNTBLANK(空白をカウントする)
・COUNTIF(1つの検索条件でのカウント)
・COUNTUNIQUE(一意の値をカウントする)
・COUNTUNIQUEIFS(一位の値に加えて検索条件を指定してカウントする)
数えるは奥が深い…気になる方は他のCOUNT系の関数も試してみて下さい!
解説も徐々にしていきたいと思います。