「COUNT関数とかいろいろ種類あるしよくわからないよ。それにしても数えたり、該当の部分にだけマーカーしたりして探すの大変だな…」
COUNT関数は単体でも、他の関数との組み合わせでも使われやすいく使用頻度も高い関数です。今回はCOUNT関数及びCOUNT関数の変化形についてお話ししていきます。
COUNT関数の種類と用途
AND、ORの条件
他の関数と一緒に使う
COUNT関数を使って出来ることをさらに増やしていきましょう。今回も練習用にExcelファイルを張り付けておきますので必要に応じて使用してみてください。
COUNT関数の種類
いくつかの種類を確認しましょう。
今回の具体例として、ゼミのキャンプの準備での集計をします。
◆COUNT関数
範囲内で数値の入ったセルの個数をカウントする(数える)関数です。
※文字や空白はカウントしない。
=COUNT(B3:B12)で経験者の人数をカウントしています。
◆COUNTA関数
範囲内で空白以外のセルの個数をカウントします。数字だけでなく、文字や記号などもカウントできる関数です。
=COUNTA(B3:B12)で今回の参加人数をカウントしています。
◆COUNTBLANK関数
範囲内で空白のセルの個数を数えます。IFなどで空欄にしたとしても、数式が入っていると認識されて空欄ではないと識別されます。
=COUNTBLANK(B3:B12)で不参加の人数をカウントしています。
◆COUNTIF関数
範囲内で特定の条件を満たすセルの個数の個数を数えられます。
=COUNTIF(B3:B12,”初”)で未経験者の人数をカウントしています。
=COUNTIF(B3:B12,検索条件)が数式の形です。検索条件に関しては選択条件が入っているセルを選んでも問題ありません。(今回ならB4セル)
◆COUNTIFS関数
範囲内で複数の条件を満たすセルの個数を数えられる関数です。
=COUNTIFS(B3:B12,B4,D3:D12,D4)で未経験者でテントを持っていない人の人数をカウントする式になっています。
=COUNTIFS(B3:B12,B4,D3:D12,D4)を分解すると
こうなります。簡単にいればCOUNTIFを1つの式で複数回しているものですね。今回は２つの検索条件ですが、3つ以上にすることも可能です。その場合は数式内のD4のあとにカンマ(,)を付けて後の式を続けて書くだけです。
AND、ORの条件
ANDとORの概念のお話をしていきます。
AND=条件の重ね合わせ
OR＝条件の片側
具体例で見ましょう。
Excelの個人別習得一覧表を作りました。①LOOKUP関数を習得し、更にIF関数も習得した人は何人いるでしょうか？②LOOKUP関数またはIF関数を習得している人は何人いるでしょうか？
①の問いに関しては、IF関数とLOOKUP関数の両方を習得している人数なので、ANDが適応されます。よって、
=COUNTIFS(C1:C8,”○”,D1:D8,”○”)
これで両方を習得している人が3人であることがわかります。
②の問いがこれまでと異なります。LOOKUP関数かIF関数のどちらか(または両方)習得している人は何人いますかという意味になります。
方法は２つあります。
1、全体から両方×の人を除く。
2、DCOUNT関数
では1つ目の両方×の人を除くからやってみましょう。
=COUNTA(A2:A8)-COUNTIFS(C2:C8,”×”,D2:D8,”×”)
そのままですね。
(全体の数)－(両方×の人)＝片方○と両方○の人
これが②の問いの1つ目の解法です。
②のもう1つの解法であるDCOUNT関数についてです。
なおDCOUNT関数を使うには事前準備が必要になります。事前準備とは、『条件を表したセルを用意する』ことです。
条件設定であるIFが○、LOOKUPが○であるという事を入力していきます。
なお、習得確認一覧表でLOOKUPとIFが別の列でそれぞれ○を付ける必要があるので条件設定のセルで○を付ける位置を1行ずらす必要があります。
あとは以下のような数式を結果を表示したいセルに入れてください。
=DCOUNT(B1:E8,,B10:C12)
＝DCOUNT(データベース,フィールド,条件)という形の数式になっています。
データベースは習得確認一覧表の成績部分、フィールドは慣れないうちは無視して構いません。あとは条件のところに作成した条件設定セルを指定してあげれば答えが出ます。
DCOUNTのメリットは条件設定セルを指定することで、数式自体をいじる必要がなくなるので用意に条件を変更しやすい点です。(数式が壊れるのを防ぎやすい)
また、ORとANDを組み合わせたい場合などはDCOUNTでしかできないケースも出てきます。必要に応じて使い分けてください。
他の関数と一緒に使う
COUNT関数関連では、他の関数と組み合わせることでより一層深いデータを提供できるケースもあります。
クラスで実施したテストを使って考えてみましょう。
いわゆる普通のテスト結果一覧表です。しかし、これではあまりに情報が少ないです。
どこに改善要素があるのかといえば、
- 科目ごとの100点取得人数
- 科目ごとの赤点取得人数
- 成績上位/下位者の平均点
付加価値情報を付けていくことで、どこに手を打つべきかといった視点を見つけやすいものにしていくことがExcelの本来の使い方です。
今回は、成績上位/下位者の平均点を見てみましょう。
例えば上位者のくくりを350点以上、平均点未満を下位者として区別してみます。
すると上位者の割合は10人中3名、下位者の割合は10人中5名とクラスの半数が下位者に該当します。また中間層である平均～350点までが一番少ないです。
さて、次回のテストまで残りの日にちはわずかだとします。クラスの先生としては心苦しいですが、全員に万遍なく教える時間は取れそうにありません。
①上位者の成績を上げる。
②中間層の成績を上げる。
③下位者の成績を上げる。
クラスの平均が上がりやすいのは①②③どの選択肢でしょう？
私は③ですね。教える時間が少ないならば、下位者がクラスの半数を占めているのでそこに重点的に的を絞るべきです。
といった形で、クラスの平均点を出すだけでなくどの層がどれぐらいの割合を占めているか付加価値情報を付けることでデータとしての価値が上がります。
ちなみに平均の出し方です。
=AVERAGE(G3:G12)
これでクラス平均が出ます。
=COUNTIF(G3:G12,”>=350″)
これで350点以上の上位成績者の人数が出ます。
=AVERAGEIF(G3:G12,”>=350″,G3:G12)
さらに成績上位者に絞った平均点が出せます。
=COUNTIF(G3:G12,”<“&G13)
これで平均以下の成績下位者が出ます。
=AVERAGEIF(G3:G12,”<“&G13,G3:G12)
さらに成績下位者に絞った平均点が出ます。
このように他の関数と組み合わせてみることで、視野が広がりやすく結果として情報に説得力が増してきます。作りたいものをいろいろ考えてみましょう。
