Excelの集計機能の中には、条件に合うデータの個数を数えてくれる関数があります。それがCOUNTIF関数です。今回はこのCOUNTIF関数を応用して、複数の条件を満たすデータの個数を数えます。
COUNTIF関数は、条件と調査するデータの範囲を指定します
先述の通りCOUNTIF関数は、条件と調査するデータの範囲を指定すると、その範囲内で条件に合うデータの数を表示してくれる関数です。
たとえば、次のようなデータがあるとします。
世界の独立国をリストにしたデータです。
途中で切れていますが、全体で200を越える国が掲載されています。
この中で、重複するデータがないかどうかを調べるように言われたら、どうでしょうか。
一つ一つ目で確認することも一つの方法ですが、それでは時間がかかりますし、整然としたデータの場合は見落とす可能性も高くなります。
このようなときに役に立つのがCOUNTIF関数です。
リスト全体を範囲に指定して、国名を条件に指定しすることで、
指定した国名と一致するデータがいくつあるのかを数えます。
具体的な条件の指定方法を確認します。
まず、リストのすぐ隣にあるC列に関数を入力します。
「=count」と入力すると、関数の候補が表示されますのでその中から「COUNTIF」を選びます。
まず、入力するのはデータを調査する範囲です。
ここでは、「独立国の一覧」のリストを対象にしてデータの数を調査しますから、リストが記載されているB列全体を指定します。
次に、条件の指定です。
国名が記載されているセルを指定します。たとえば、B1セルを指定すると、B1セルに入力されている「アイルランド」が条件として指定されることになります。
これで、
・B列全体を対象として調査して
・B1セルにあるデータ（「アイルランド」）の数を数えて下さい
という条件の指定ができたことになります。
COUNTIF関数で必要とされる入力は完了です。
その結果がこちらです。
B列の中に、B1セルと同じデータは1つだけしか存在せず、重複はないことが分かりました。
同様にC列の他のセルでもCOUNTIF関数を入力していきますが、ここでも効率的な方法を採ります。
C1セルで既に正しく入力された関数があるのですからそれをコピーしていきましょう。
方法はいろいろありますが、ここでは、隣の列が連続するデータの場合に使える簡単なコピーの方法を使いましょう。
C1セルをアクティブにして、セルの右下の角にカーソルを合わせると、カーソルが十字になります。
カーソルが十字になった状態でダブルクリックします。
すると、隣の列にある連続するデータに対応する形でコピーができます。
「Control＋C」＆「Control＋V」よりも簡単ですので、是非使ってみて下さい。
それでは、結果を見てみましょう。
オートフィルタを使って、リスト上に2回以上入力された国名がないかを確かめます。
２回入力されている国がありました。
オートフィルタで抽出してみましょう。
２つの国に重複がありました。
このように、COUNTIF関数は、調査する範囲と条件を指定することで、データの数を表示してくれることが分かりました。
次に、このCOUNTIF関数を応用したCOUNTIFS関数で、複数条件に一致するデータの数を表示させることで、人員データを表示することを考えてみましょう。
COUNTIFS関数を使って、人員の変動を表示する
次のデータをご覧下さい。
これは、2014年のJリーグでの移籍データです。
ある程度ボリュームのあるデータを扱うために、フットボールのデータにしていますが、利用できる範囲はそれに留まりません。
人事戦略や事業税の算定等のために作成される、人員の変動データを作成する時のヒントとして考えていただければと思います。
改めて、このデータから何をするのかですが、次のような表を作る事を考えます。
Clubのセルにクラブ名を入力すれば、そのクラブの加入と移籍の人数がポジション別で表示される表です。
この表を作るのに使うのが、COUNTIF関数の考え方を応用した、「COUNTIFS関数」です。
応用と言っても難しいところはなく、先ほどのCOUNTIF関数が条件が1つだったのに対して、COUNTIFS関数は2つ以上の条件を設定することができると言う違いだけです。
条件の設定の仕方もCOUNTIF関数と同じで、「調査するデータの範囲」と「条件」を複数回指定すれば大丈夫です。
早速やってみましょう。
まず、全体ではなく一つのセルについて考えます。
ここでは、C3セルに該当するFWの加入（IN）の人数を数える式を考えます。
この表に沿うように条件を設定していけば問題ありません。
入力すべき条件は、
・クラブ名
・加入（IN）か移籍（OUT）か
・ポジション
以上の3点です。
もう一度、元のデータを見ていただくと、
・G列にクラブ名
・H列に加入（IN）か移籍（OUT）
・J列にポジション
のデータが入力されていることが分かります。
これを先ほど見たCOUNTIF関数の入力内容に照らし合わせて考えると、G列、H列、J列、を「調査するデータの範囲」に指定することになります。
次に、条件の入力です。
・クラブ名はA3セルに入力されるので、A3を指定
・INかOUTかはC2セルに対応させるのでC2を指定
・ポジションはB3セルに対応させるのでB3を指定することになります。
各セルに該当する条件が入力されていることを下の画像でご確認下さい。
これで、入力内容がまとまりました。
実際に入力して確かめてみます。
１．
C3セルで、COUNTIFS関数を選択
２．
調査するデータの範囲を入力します。
最初はクラブ名から指定することにしましょう。
元データ上、クラブ名が入力されているG列を指定します。
入力の仕方に注意して下さい。
セルではなく、列番号のGをクリックすることで列全体を指定することができます。
また、コピーによって表を完成させますので、貼り付けるセルが移動しても、指定する列が変わらないように”＄”をつけて絶対参照にしておきましょう。
３．
条件を入力します。
クラブ名が一致するデータを探しますから、表の中でクラブ名が入力されるA3セルを指定します。
入力の仕方には注意しましょう。
こちらも、コピーで表を作成しますから、貼り付けるセルが移動しても、クラブ名のセルを指定できるように、”＄”を列番号（A列）と行番号（3行）の両方につけて$A$3と指定します。
４．
次に加入（IN）か移籍（OUT）の条件を入力します。
先ほどと同様に、調査するデータの範囲から指定しましょう。
元データ上、「加入（IN）か移籍（OUT）か」の情報が入力されているのはH列ですから、列ごと指定します。
ここも、後から他セルへコピーすることを考えて、セルが移動しても指定する列が変わらないように”＄”をつけて絶対参照にしている点に注意して下さい。
５．
条件を入力します。
「加入（IN）か移籍（OUT）か」については、表に合わせますから、「加入（IN）」の場合はC2セルの「IN」を指定するようにします。
入力の仕方には注意して下さい。
「C＄2」について、行番号である”２”に対してだけ＄がつけられて
います。
これは、後から他セルにコピーした際、
「下への移動については2行目のまま」
「横への移動についてはセルに合わせて移動させる」
ように指定することで、「移籍（OUT）」の列へのコピーについては、正しく「D＄2」が指定されるようにするためです。
6．
次にポジションの条件を入力します。
調査するデータの範囲から指定します。
元データ上、ポジションの情報が入力されているのはJ列ですから、列ごと指定します。
ここでも列指定は”＄”を使った絶対参照です。
７．
条件を入力します。
ポジションについては、表に合わせますから、「FW」の場合はB3セルを指定するようにします。
入力の仕方には注意して下さい。
「＄B3」について、列番号である”B”に対してだけ＄がつけられて
います。
これは、後から他セルにコピーした際、
「横への移動についてはB列のまま」
「下への移動についてはセルに合わせて移動する」
ように指定することで、他のポジションへのコピーについては、正しく「＄B4」（＝MF）、「＄B5」（＝DF）、「＄B6」（＝GK）、が指定されるようにするためです。
これで、C3セルへの入力が完成しました。
８．
他のセルへコピーします
これで表が完成しました。
その結果がこちらです
クラブ名は誤った入力をしないようにドロップダウンリストにして、選択できるようにしています。
クラブを選択すれば、各ポジションの加入と移籍の人数が表示されるようになりました。
E列には「加入ー移籍」による純粋な増減を、7行目は加入と移籍の合計を表示するセルにしています。
まとめ
今回作成したのは、Jリーグクラブの人員変動の表でした。
ですが、クラブを会社の本店と支店、ポジションを人事、営業などの部署に置き換えれば、支店別の人事データにすることも可能です。
COUNTIF関数とCOUNTIFS関数でできることを知って、幅広く使えるようになっておきましょう。
＜おまけ＞
Jリーグのクラブは26日で営業を終えているところが多いですが、移籍のリリースはガンガン出てきますね。
選手も早く来シーズンの居場所を決めたいでしょうから年内ギリギリまで活発に動くかもしれません。
