前回の連載で紹介した「関数COUNTIF」は、条件を「セル参照」で指定したり、条件に「文字」を指定したりすることも可能となっている。ただし、そのためには「&」(文字の連結)や「*」(ワイルドカード)の使い方を学んでおく必要がある。関数COUNTIFを便利に活用するためにも、この機会にぜひ覚えておこう。
条件を「セル参照」で指定するときの記述方法
前回の連載に引き続き、今回も「関数COUNTIF」の便利な使い方を紹介していこう。条件付きで「数値データ」の個数をカウントする場合だけでなく、条件付きで「文字データ」をカウントする場合にも関数COUNTIFが役に立つ。
前回の連載でも少し触れたが、関数COUNTIFの条件を記す際に「セル参照」を活用することも可能だ。ただし、そのためには少し特殊な方法で条件を記述する必要がある。
まずは、よくありがちな失敗例から紹介していこう。以下の図は、「平均未満の人が何人いるか?」を調べるために関数COUNTIFを記述した例だ。「横浜支社」の平均点はC24セルで算出しているため、第2引数(条件)に"<C24"と記述すれば正しい結果を得られると思うかもしれない。
しかし、これは間違った記述となる。上記のように関数COUNTIFを記述した場合、「"<C24"という文字データが何個あるか?」をカウントすることになる。もちろん、このようなデータは1つもないので、その結果は0と表示される。
このように「"」(ダブルクォーテーション)の中にセル参照を記述すると、「C24セルを参照して・・・」ではなく、「"<C24"という文字」として認識されてしまう。
では、どうすれば良いのか? このような場合は、比較演算子だけを「"」で囲むのが基本だ。今回の例のように「未満」を条件にするのであれば、"<"と記述する。同様に、「以上」の場合は"=>"、「等しい」の場合は"="と記述すればよい。
続いて、文字を連結する「&」(アンド)を記述し、その後に「参照するセル」を記述する。つまり、以下のように関数COUNTIFを書くのが正しい記述方法になる。
その後、「Enter」キーを押すと、平均未満(C24セルの値未満)の「数値データの個数」を正しくカウントできる。
このように、関数COUNTIFの条件に「セル参照」を使用する場合は、少し特殊な記述方法を用いなければならない。念のため“おさらい”しておくと、以下のようになる。
(1)比較演算子だけを「"」で囲んで記述する (2)続けて「&」(アンド)を記述する (3)続けて「セル参照」を記述する
この記述方法を知っていれば、関数COUNTIFの条件に「セル参照」を利用できるようになる。Excelの初心者だけでなく、中級者も間違いやすい部分なので、勘違いしないように注意しておこう。
「文字」を条件にしたデータ数のカウント
関数COUNTIFは、「文字」を条件にして「データの個数」を数えることも可能だ。こちらも関数COUNTIFの代表的な使い方といえるので、ぜひ覚えておこう。
今度は、会員名簿を例にして具体的な使い方を紹介していこう。以下の図は、ある店舗の会員情報をExcelにまとめたものだ。会員情報は、ワークシートの6~505行目に計500件のデータが入力されている。
この500件のデータのうち、「男性が何人いるか?」を関数COUNTIFで調べたいとしよう。この場合は「性別」が入力されているD6~D505を第1引数(セル範囲)に指定し、第2引数(条件)に"=男"(データが「男」に等しい)と記述すればよい。
「Enter」キーを押して結果を見ると、234という数値が表示された。つまり、「男性の会員は234人にいる」ということを即座に確認できた訳だ。
同様に、「女性の会員が何人いるか?」を調べたい場合は、以下のように関数COUNTIFを記述すればよい。
今度は、266という数値が表示された。つまり、「女性の会員は266人にいる」ということになる。
両者を合計すると、234+266=500(人)となり、全データ数(500件)と一致しているのを確認できるだろう。
このように「文字」を条件にして「該当するセルが何個あるか?」をCOUNTIFで調べることも可能だ。なお、文字には「大きい」や「小さい」といった概念がないため、この場合の比較演算子は「=」(等しい)、もしくは「<>」(等しくない)のいずれかを用いるのが基本的な使い方となる。
ワイルドカードを使ったデータ数のカウント
続いては、「住所が東京都の会員が何人いるか?」を関数COUNTIFで調べてみよう。この場合の第1引数(セル範囲)はF6~F505となる。一方、第2引数(条件)は"=東京都"でよい、と思うかもしれない。
しかし、この結果は0(個)となり、求めていた数値を示してくれない。
というのも、先ほどのように関数COUNTIFを記述した場合、「データが"東京都"に等しいセル」が条件になってしまうからだ。実際の住所データは「東京都△☆※・・・」のように"東京都"の後にも文字データが続いている。データが「東京都」だけで完結してるセルは1つもないので、関数COUNTIFの結果も0になる。
このような場合は、「データが"東京都"で始まる」を条件に指定するのが正しい記述方法となる。具体的には、「*」(アスタリスク)の記号を使って"=東京都*"と条件を指定すればよい。
「*」の記号はワイルドカードと呼ばれるもので、「任意の文字列」を示している。"=東京都*"と条件を記述した場合は、最初の3文字が「東京都」で、以降は「任意の文字」という意味になる。つまり、「データが"東京都"で始まる」という条件を指定したことになる。
「Enter」キーを押して結果を確認すると、67という数値が表示された。すなわち、「住所が東京都の会員は67人いる」という結果を即座に得られた訳だ。
もちろん、"=東京都*"の部分を"=埼玉県*"に修正して「住所が埼玉県の会員が何人いるか?」を調べることも可能だ。
そのほか、「*」(ワイルドカード)を記述する位置を工夫して、「データが"★★"で終わる」などの条件を指定することもできる。たとえば、「氏名」のセル範囲を対象に、"=*子"と条件を記述すると、氏名の最後が「子」で終わるデータの個数をカウントできる。同様に、"=*翔*"と条件を記述すると、氏名に「翔」の文字を含むデータの個数をカウントできる。
工夫次第で色々な条件を指定できるので、時間に余裕があるときにワイルドカードの使い方も研究しておくと役に立つだろう。
セル範囲に「列全体」を指定するときの注意点
最後に、関数COUNTIFのセル範囲に「列全体」を指定するときの注意点を紹介しておこう。今回の例のように何百件ものデータが入力されている表では、「何行目までデータが入力されているか?」を調べるのが面倒な場合もある。
このような場合は「列全体」をセル範囲に指定してもよい。たとえば、第1引数に「F:F」と記述すると、「F列全体」をセル範囲に指定できる。
この方法を知っていれば、「何行目までデータが入力されているか?」を確認しなくても、すぐに関数を入力できる。そのほか、「C:E」と記述して複数の列(C~E列全体)をセル範囲に指定することも可能だ。
ただし、いくつかの注意点もある。セル範囲に「列全体」を指定するときは、以下のようなミスを犯さないように注意する必要がある。
一つ目は、循環参照が発生しないように注意すること。たとえば、下図のように関数COUNTIFを記述すると、循環参照が発生していることを示す警告が表示される。
この例では関数COUNTIFの対象に「F列全体」を指定しているが、肝心の関数COUNTIFもF列に記述されている・・・、という状況になっている。つまり、自分自身が「対象とするセル範囲」に含まれてしまっている訳だ。このような場合は、正しい結果を得られないのが普通だ。関数COUNTIFは「F列以外」のセルに入力する必要がある。
二つ目は、条件に「以外」を指定する場合だ。たとえば、「住所が東京都以外の会員が何人いるか?」を調べようとして、以下のように関数COUNTIFを記述したとしよう。
すると、104万8,509(個)という、とてつもなく大きな数値が結果として表示される。
このような結果になる理由は、F列にある「空白セル」も「データが"東京都"で始まらないセル」としてカウントされてしまうからだ。
Excelのワークシートは、最大で104万8,576行までデータを入力できる仕様になっている。つまり、F列には全部で104万8,576個のセルがあることになる。このうち、今回の例では「東京都」で始まるデータが67個ある。よって、104万8,576(個)-67(個)=104万8,509(個)が「データが"東京都"で始まらないセル」になる。これが関数COUNTIFの結果として返される訳だ。
このようにセル範囲を「列全体」にして、条件に「★★以外」を指定すると、求めていた結果は得られなくなる。多少、面倒でも「F6:F505」のように限定的なセル範囲を指定して関数COUNTIFを利用しなければならない。少し特殊なケースになるが、間違えないように注意しておこう。