実務で使えるExcel入門セミナー 複数条件にあてはまるデータの数を数える方法です

シェアする

  • このエントリーをはてなブックマークに追加

経理や人事で仕事をしていると、住民税や社会保険料の支払額を求めるために、特定の条件に当てはまる人の数を数えることがあります。そのようなケースでも、Excelの関数を使うと効率よくカウントすることができます。

スポンサーリンク
自動サイズレスボンシブ

住民税、社会保険料などは、人事データから、条件にあてはめて計算しています

みなさん、ご存じの通り、税金や社会保険料の計算は、結構複雑です。


複雑な上に、個々の従業員の条件に合わせて、1人1人の税額や保険料を計算し、それを合計して、会社全体の支払額を求めなければいけません。


会社の規模によっては、従業員数もかなりの数になりますから、経理部にとっても、骨の折れる作業です。


「そんなに面倒な計算なら、税務ソフトや会計ソフトにやらせれば良い」というのも一理あるのですが、相応のコストが必要になることですので、全てをソフトに任せるというのも難しいのが現実。


実務の現場では、人事部から従業員データをもらって、Excelにデータを転記し、関数を組んで計算していることが多いです。


同じようにExcelを使って計算するにしても、できれば効率よく作業を終えたいもの。


今回は、Excelで複数の条件を満たすデータの数を数える方法を考えます。

COUNTIFS関数を使う

COUNTIFS関数を使う方法です。

COUNTIFS関数の仕組み


COUNTIFS関数は、とてもシンプルな関数で、 
次のように検索範囲検索条件を順番に指定すれば、

スクリーンショット 2015 05 30 20 49 58


指定した条件の全てを満たすデータの数を数えてくれます。

COUNTIFS関数の使い方

たとえば、次のようなデータがあるとします。

スクリーンショット 2015 05 30 21 06 58


434人分のサッカー選手のリストです。 
ポジション、名前、生年月日、身長、体重、代表歴、などが入力されています。


このリストから、「身長185cm以上、かつ、体重が75kg以上の選手の数」を数えるとしましょう。


検索範囲と検索条件を順番に入力していきますが、身長のデータは、

スクリーンショット 2015 05 30 21 06 58 のコピー 2


F列に入力されていますから、1番目の検索範囲は、

スクリーンショット 2015 05 30 21 12 58


=COUNTIFS(① $ F:$ F


になります。


次に、検索条件は185以上なので、


=COUNTIFS(① $ F:$ F、 ② >= 185


※「〜以上」は、不等号(>)と等号(=)をならべて表現します。

スクリーンショット 2015 05 30 22 36 29


このように入力します。


2番目の条件は体重が75kg以上。 
体重のデータは、H列に入力されているので、

スクリーンショット 2015 05 30 21 06 58 のコピー 3


2番目の検索範囲は、


=COUNTIFS(① $ F:$ F、 ②” > = 185 “、③$ H:$ H

スクリーンショット 2015 05 30 22 22 16


このようになります。


次に、検索条件は75以上なので、


=COUNTIFS(① $ F:$ F、 ②” > = 185 “、③$ H:$ H、④ > = 75

スクリーンショット 2015 05 30 22 41 32


こうなります。


その結果は、153と計算されて、

スクリーンショット 2015 05 30 22 44 40


条件を満たす選手の数を、正しく数えることができました。


さらに、条件を増やす場合には、同じ要領で「検索範囲」と「検索条件」を増やしていけばOKです。

COUNTIFS関数の入力の注意点

COUNTIFS関数の入力は以上ですが、1点だけ入力上の注意があります。


それは、検索条件の入力の仕方。


先ほども出てきたのですが、検索条件を入力する時は、「>=185
」のように、そのまま入力してもダメで、

スクリーンショット 2015 05 30 23 04 04


ダブルクォーテーション(「” ”」)で、式を囲む必要があります。

スクリーンショット 2015 05 30 23 15 32


Excel上で、データをダブルクォーテーショで囲むと文字列を意味することになるですが、


COUNTIFS関数では、条件式を数式で入力する場合でも、ダブルクォーテーションで囲まなければいけません。 
この点は注意しましょう。

IF関数+COUNTIF関数

複数の条件を満たすデータの数を数える方法は、もう1つあります。 
IF関数COUNTIF関数組み合わせる方法です。

大まかな流れ

大まかな流れは、

スクリーンショット 2015 05 31 0 01 25


こうなります。

具体例を使った説明

具体的に見ていきます。


まず、IF関数を使って、条件を満たすデータに「独自コード」をつけます。


「独自コード」といっても難しいものではなく、 
たとえば、先ほどの「身長185cm以上」の条件なら、条件を満たすデータについて、IF関数を使って「a」と表示させる、といったことです。


具体的なIF関数の式はこうなります。

スクリーンショット 2015 05 31 0 11 13

スクリーンショット 2015 05 30 23 32 34


この式を全てのデータにコピーして、条件を満たすデータ全てに、独自コード(ここでは「a」)をつけます。

スクリーンショット 2015 05 30 23 37 18


次に、独自コードが付いたデータの数を、COUNTIF関数で数えます。

先ほどの例で言うと、K列にある独自コード(「a」)の数を数えるので、

スクリーンショット 2015 05 30 23 53 16


このように入力すると、

スクリーンショット 2015 05 30 23 54 22


条件を満たすデータの数を、数えることができます。


これが、具体的な作業の流れです。

実際に、複数の条件にあてはまるデータの数を数える

IF関数とCOUNTIF関数の組み合わせを使って、先ほどと同じように、サッカー選手のリストから、「身長185cm以上、かつ、体重が75kg以上の選手の数」を数えてみます。


まずは、独自コードです。


IF関数を使って「身長185cm以上、かつ、体重が75kg以上の選手の数」のデータに、独自コード「a」をつけるようにします。


条件式は「身長185cm以上、かつ、体重が75kg以上の選手の数」ですから、「○○かつ△△」といった2つの条件を同時に満たす式を表す、AND関数を使って、

スクリーンショット 2015 05 31 0 26 23


このように入力します。


あとは、条件式が正しければ「a」、正しくなければ空欄を表示させるので、

スクリーンショット 2015 05 31 0 40 31

こうなります。


この式を全てのデータにコピーして、条件に当てはまるデータに「a」と表示させます。

スクリーンショット 2015 05 31 0 45 01


あとは、COUNTIF関数で「a」の数を数えます。


「a」を表示させているのがK列なので、

スクリーンショット 2015 05 31 0 50 28


このように入力すると、

スクリーンショット 2015 05 31 0 51 10


条件を満たすデータの数、153が正しく計算されます。

「COUNTIFS関数」と「IF関数とCOUNTIF関数の組み合わせ」どちらがいいのか

ここまでで、条件を満たすデータの数を数える方法、2つを見てきましたが、実務の現場ではどちらを使うべきでしょうか。


より柔軟に条件を設定するためには、「IF関数とCOUNTIFS関数の組み合わせ」を使うのが良いでしょう。


COUNTIFS関数は、「○○かつ△△かつ□□かつ…」と複数の条件を同時に満たすデータを数える場合にはいいのですが、


「○○か△△か□□か…」のように「どれか1つの条件に当てはまる」という条件の設定ができません


この点、「IF関数とCOUNTIF関数の組み合わせ」なら、IF関数の条件式で、AND関数を使えば、COUNTIFS関数と同じように、「複数の条件を同時に満たすデータ」の数を数えられますし、


OR関数を使えば、「どれか1つの条件に当てはまるデータ」の数を数えることも可能です。


たとえば、先ほどの「身長185cm以上、かつ、体重75kg以上」の条件を、「身長185cm以上、あるいは、体重75kg以上」に変えた場合でも、

スクリーンショット 2015 05 31 1 05 57


IF関数の条件式を、OR関数を使えば「どれか1つの条件に当てはまるデータ」を数えることにも対応できます。


これ以外にも、より複雑な条件を設定することができますので、条件設定の柔軟性の点では、「IF関数とCOUNTIF関数の組み合わせ」は優れていると言えます。


ですので、どちらか一つを選ぶなら「IF関数とCOUNTIF関数の組み合わせ」を知っておくのが良いでしょう。

まとめ

Excelで複数条件にあてはまるデータの数を数える方法には、「COUNTIFS関数」と「IF関数とCOUNTIF関数の組み合わせ」の2つがありますが、条件設定の柔軟性の点で「IF関数とCOUNTIF関数の組み合わせ」が優れていると言えます。

おまけ

青山パン祭りか、サッカーか。 
大いに悩む週末。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする