みてみて、ExcelのCOUNTIF関数でこんなことできたよ！

ExcelのCOUNTIF関数とCOUNTIFS関数の使い道を列挙します。画像多めです。

「使い方」ではなく「使い道」

「使い方」は方法や手順です。一方「使い道」は用途や目的、あるいは、それで何ができるかという具体的な部分です。

道具の使い方だけ知っていても、実用的なことはできませんね。ですので今回は COUNTIF/COUNTIFS関数の実用的な「使い道」を紹介します。進路指導部にいたときにたくさん使いました。

※テーブルはあえて使っていません。
※エラー対策は省略しています。実際の利用状況に合わせて対策してください。

基本の使い道

条件に一致するデータの個数を数えます。

条件に一致するデータの個数を数える

重複確認

まずは簡単なところから。座席表を作るときに生徒の重複がないか確認するのに使いました。

重複確認（完成形）

重複というのは、ある範囲に同じデータが2つ以上あることです。なので、COUNTIFで結果が2以上になったら重複しています。IFで分岐したらいいですね。

次の数式をO2セルに入れて、下方向にコピーします。

=IF(COUNTIF($A$2:$L$15,N2)>1,"重複","")

重複確認の数式

条件付き書式と組み合わせるといいです。

存在確認

座席表を作るときに生徒の入力漏れを探すのに使いました。

ある範囲にそのデータが存在するならば、COUNTIFの結果が1以上になります。これもIFで分岐しましょう。

1つ以上あればいいなら、こう。

=IF(COUNTIF($A$2:$L$15,N2)>0,"ある","")

存在確認（1つ以上あればいい）

入力漏れを探すなら、むしろこっちか。

=IF(COUNTIF($A$2:$L$15,N2)=0,"ない","")

存在確認（入力漏れを探す）

上の重複確認と合わせて、重複と入力漏れを同時に探すなら、こう。

=IF(COUNTIF($A$2:$L$15,N2)>1,"重複",IF(COUNTIF($A$2:$L$15,N2)=0,"ない",""))

重複と入力漏れを同時に探す

ヒストグラム

ヒストグラム（完成形）

進路指導部で模試結果の資料につけたら結構喜ばれました。集計したデータの視覚化に挑戦しましょう。私の一番好きな使い方です。

視覚化！　データ分析！　テンション上がってきた！

学年全体のヒストグラムを作る

左側の表から5点刻みで人数を数え、右側の表にヒストグラムを作成することを考えましょう。

たとえばI2セルに入れる「95点から100点の生徒の人数」というのは、

1. 点数が95以上
2. 点数が100以下

という2つの条件を満たす生徒の人数と考えます。すると数式は次のようになります。この数式を下方向にコピーしてデータバーを付けると完成です。絶対参照と相対参照に気を付けてください。

=COUNTIFS($C$2:$C$161,">="&$F2,$C$2:$C$161,"<="&$H2)

ヒストグラムの数式

数字と罫線だけより超絶見やすい。見にくいものを頑張って見るのではなく、頑張って見やすいものを作りましょう。

条件で不等号を扱うときは、">="&F2 のようにします。不等号は文字列なので "" でくくります。セルは文字列ではないのでくくりません。& を忘れるとエラーになります。

クラスごとのヒストグラムを作る

クラスごとのヒストグラムも作りましょう。クラスの列を追加すると作業しやすいです。

クラスごとのヒストグラムを作る準備

たとえば「95点から100点の、1組の生徒の人数」というのは、

1. 点数が95以上
2. 点数が100以下
3. クラスが1

という3つの条件を満たす生徒の人数と考えます。数式は以下のようになります。絶対参照と相対参照に気を付けてください。

=COUNTIFS($C$2:$C$161,">="&$F2,$C$2:$C$161,"<="&$H2,$D$2:$D$161,I$1)

この式をI2セルから縦と横にコピーしてデータバーを付けましょう。

クラスごとのデータバー（完成形）

種類別通し番号

種類別通し番号（完成形）

たしか修学旅行のしおりで使いました。「2日目の午後は行き先を3カ所から選んで班行動。その行き先ごとに班に通し番号を振る」みたいな用途だったかな。

通し番号は「一番上のセルから自分のセルまでの範囲の、自分と同じデータの個数」と考えます。ポイントは「自分のセルまで」というところです。

一番上のセルから自分のセルまでの範囲に、自分と同じデータの個数

次の数式をD2セルに入れて、下方向にコピーします。絶対参照と相対参照、それからセル範囲に気を付けてください。

=COUNTIF($C$2:C2,C2)

一番上のセル

式が正しければこのようになるはずです。

=COUNTIF($C$2:C2,C2)
=COUNTIF($C$2:C3,C3)
=COUNTIF($C$2:C4,C4)
=COUNTIF($C$2:C5,C5)
=COUNTIF($C$2:C6,C6)
=COUNTIF($C$2:C7,C7)

数式のコピー（上から2行目）

数式のコピー（上から3行目）

数式のコピー（上から4行目）

あとは行き先と通し番号を & で結合すればいいですね。

RANKIF関数（仮称）

RANKIF関数（仮称）（完成形）

同じグループの中での順位を求めます。模試の選択科目ごとに順位を出すときに使いました。

1つの列に日本史Bと世界史Bの点数が入っています。その状態で「日本史Bの生徒の中での順位」を出すにはどうしたらいいでしょう。

SUMIF や AVERAGEIF があるなら RANKIF関数もありそうなものですが、意外とないんですよ。あればいいのに。

ないなら別の物で代用しましょう。「日本史Bの中での順位」は、「(科目が日本史Bで、点数が自分よりも高い生徒の人数)+1」と考えます。自分より上が1人だったらその人は2位、0人だったら1位ですので、"+1" をお忘れなく。もちろん世界史Bの中での順位も同じ方法で出せます。

次の数式をE2セルに入力して、下方向にコピーします。絶対参照と相対参照に気を付けてください。

=COUNTIFS($C$2:$C$11,C2,$D$2:$D$11,">"&D2)+1

RANKIF関数（仮称）の数式

ヒストグラムでも言いましたが、条件で不等号を扱うときは、">"&E2 のようにします。不等号は文字列なので "" でくくります。セルはくくりません。& を忘れるとエラーになります。

同率を扱うために > を使います。>= だと上手くいきません。やってみてください。

IF+ワイルドカード

IF+ワイルドカード（完成形）

IF関数の中でワイルドカードを使って、「対象が "文系" で始まる場合はこうする」ということをします。

そのまま普通に考えれば次の式になりますが、IF関数の条件式にワイルドカードは使えません。C2セルに "文系国公立" と入力した状態でD2セルに次の式を入力してみてください。エラーこそ出ませんが期待した結果にならないはずです。

=IF(C2="文系*","文系","文系ではない")

代わりにCOUNTIF関数を組み合わせてこうしましょう。

=IF(COUNTIF(C2,"文系*")=1,"文系","文系ではない")

COUNTIF(C2,"文系*") ですので、「C2セルという範囲に『文系～』という文字列を含むセルはいくつあるか」という意味です。範囲はC2セルただひとつなので、結果は1か0です。

これを利用して、進学希望が文系なのか理系なのかを振り分けることができます。

=IF(COUNTIF(C2,$G$1&"*")=1,$G$1,IF(COUNTIF(C2,$H$1&"*")=1,$H$1,""))

IF+ワイルドカードの数式

ウォーリーを探す

ウォーリーを探す（完成形）

ネタです。説明はなくていいか。

ウォーリーが複数人いた場合は上手くいきませんが、ミ〇ケ！ならともかくウォーリーを〇がせ！の場合そんなことはないでしょう。

ウォーリーがいる行を探す数式

ウォーリーがいる列を探す数式

ウォーリーの住所（セル番地）を特定しましょう。

ウォーリーの住所（セル番地）を特定（完成形）

ウォーリーがいる行の行番号を取得する数式

ウォーリーがいる列の列番号を取得する数式

ウォーリーの住所（セル番地）を取得する数式

まとめ

人に教えるときも勉強するときも、具体的な使い道まで意識するといいと思います。

COUNTIF/COUNTIFS関数大好き。

変更履歴

冒頭の注意書きの位置と色を変更 (2021/07/18)