経理や人事で仕事をしていると、住民税や社会保険料の支払額を求めるために、特定の条件に当てはまる人の数を数えることがあります。そのようなケースでも、Excelの関数を使うと効率よくカウントすることができます。
住民税、社会保険料などは、人事データから、条件にあてはめて計算しています
みなさん、ご存じの通り、税金や社会保険料の計算は、結構複雑です。
複雑な上に、個々の従業員の条件に合わせて、1人1人の税額や保険料を計算し、それを合計して、会社全体の支払額を求めなければいけません。
会社の規模によっては、従業員数もかなりの数になりますから、経理部にとっても、骨の折れる作業です。
「そんなに面倒な計算なら、税務ソフトや会計ソフトにやらせれば良い」というのも一理あるのですが、相応のコストが必要になることですので、全てをソフトに任せるというのも難しいのが現実。
実務の現場では、人事部から従業員データをもらって、Excelにデータを転記し、関数を組んで計算していることが多いです。
同じようにExcelを使って計算するにしても、できれば効率よく作業を終えたいもの。
今回は、Excelで複数の条件を満たすデータの数を数える方法を考えます。
COUNTIFS関数を使う
COUNTIFS関数を使う方法です。
COUNTIFS関数の仕組み
COUNTIFS関数は、とてもシンプルな関数で、
次のように検索範囲と検索条件を順番に指定すれば、
指定した条件の全てを満たすデータの数を数えてくれます。
COUNTIFS関数の使い方
たとえば、次のようなデータがあるとします。
434人分のサッカー選手のリストです。
ポジション、名前、生年月日、身長、体重、代表歴、などが入力されています。
このリストから、「身長185cm以上、かつ、体重が75kg以上の選手の数」を数えるとしましょう。
検索範囲と検索条件を順番に入力していきますが、身長のデータは、
F列に入力されていますから、1番目の検索範囲は、
＝COUNTIFS（① $ F:$ F
になります。
次に、検索条件は185以上なので、
＝COUNTIFS（① $ F:$ F、 ② “ ＞＝ 185 “、
※「〜以上」は、不等号（＞）と等号（＝）をならべて表現します。
このように入力します。
2番目の条件は体重が75kg以上。
体重のデータは、H列に入力されているので、
2番目の検索範囲は、
＝COUNTIFS（① $ F:$ F、 ②” ＞ ＝ 185 “、③$ H:$ H、
このようになります。
次に、検索条件は75以上なので、
＝COUNTIFS（① $ F:$ F、 ②” ＞ ＝ 185 “、③$ H:$ H、④ “ ＞ ＝ 75 “、
こうなります。
その結果は、153と計算されて、
条件を満たす選手の数を、正しく数えることができました。
さらに、条件を増やす場合には、同じ要領で「検索範囲」と「検索条件」を増やしていけばOKです。
COUNTIFS関数の入力の注意点
COUNTIFS関数の入力は以上ですが、1点だけ入力上の注意があります。
それは、検索条件の入力の仕方。
先ほども出てきたのですが、検索条件を入力する時は、「＞＝１８５
」のように、そのまま入力してもダメで、
ダブルクォーテーション（「” ”」）で、式を囲む必要があります。
Excel上で、データをダブルクォーテーショで囲むと文字列を意味することになるですが、
COUNTIFS関数では、条件式を数式で入力する場合でも、ダブルクォーテーションで囲まなければいけません。
この点は注意しましょう。
IF関数＋COUNTIF関数
複数の条件を満たすデータの数を数える方法は、もう１つあります。
IF関数とCOUNTIF関数を組み合わせる方法です。
大まかな流れ
大まかな流れは、
こうなります。
具体例を使った説明
具体的に見ていきます。
まず、IF関数を使って、条件を満たすデータに「独自コード」をつけます。
「独自コード」といっても難しいものではなく、
たとえば、先ほどの「身長185cm以上」の条件なら、条件を満たすデータについて、IF関数を使って「a」と表示させる、といったことです。
具体的なIF関数の式はこうなります。
この式を全てのデータにコピーして、条件を満たすデータ全てに、独自コード（ここでは「a」）をつけます。
次に、独自コードが付いたデータの数を、COUNTIF関数で数えます。
先ほどの例で言うと、K列にある独自コード（「a」）の数を数えるので、
このように入力すると、
条件を満たすデータの数を、数えることができます。
これが、具体的な作業の流れです。
実際に、複数の条件にあてはまるデータの数を数える
IF関数とCOUNTIF関数の組み合わせを使って、先ほどと同じように、サッカー選手のリストから、「身長185cm以上、かつ、体重が75kg以上の選手の数」を数えてみます。
まずは、独自コードです。
IF関数を使って「身長185cm以上、かつ、体重が75kg以上の選手の数」のデータに、独自コード「a」をつけるようにします。
条件式は「身長185cm以上、かつ、体重が75kg以上の選手の数」ですから、「○○かつ△△」といった2つの条件を同時に満たす式を表す、AND関数を使って、
このように入力します。
あとは、条件式が正しければ「ａ」、正しくなければ空欄を表示させるので、
こうなります。
この式を全てのデータにコピーして、条件に当てはまるデータに「a」と表示させます。
あとは、COUNTIF関数で「a」の数を数えます。
「a」を表示させているのがK列なので、
このように入力すると、
条件を満たすデータの数、153が正しく計算されます。
「COUNTIFS関数」と「IF関数とCOUNTIF関数の組み合わせ」どちらがいいのか
ここまでで、条件を満たすデータの数を数える方法、２つを見てきましたが、実務の現場ではどちらを使うべきでしょうか。
より柔軟に条件を設定するためには、「IF関数とCOUNTIFS関数の組み合わせ」を使うのが良いでしょう。
COUNTIFS関数は、「○○かつ△△かつ□□かつ…」と複数の条件を同時に満たすデータを数える場合にはいいのですが、
「○○か△△か□□か…」のように「どれか１つの条件に当てはまる」という条件の設定ができません。
この点、「IF関数とCOUNTIF関数の組み合わせ」なら、IF関数の条件式で、AND関数を使えば、COUNTIFS関数と同じように、「複数の条件を同時に満たすデータ」の数を数えられますし、
OR関数を使えば、「どれか１つの条件に当てはまるデータ」の数を数えることも可能です。
たとえば、先ほどの「身長185cm以上、かつ、体重75kg以上」の条件を、「身長185cm以上、あるいは、体重75kg以上」に変えた場合でも、
IF関数の条件式を、OR関数を使えば「どれか１つの条件に当てはまるデータ」を数えることにも対応できます。
これ以外にも、より複雑な条件を設定することができますので、条件設定の柔軟性の点では、「IF関数とCOUNTIF関数の組み合わせ」は優れていると言えます。
ですので、どちらか一つを選ぶなら「IF関数とCOUNTIF関数の組み合わせ」を知っておくのが良いでしょう。
まとめ
Excelで複数条件にあてはまるデータの数を数える方法には、「COUNTIFS関数」と「IF関数とCOUNTIF関数の組み合わせ」の2つがありますが、条件設定の柔軟性の点で「IF関数とCOUNTIF関数の組み合わせ」が優れていると言えます。
おまけ
青山パン祭りか、サッカーか。
大いに悩む週末。