次のような営業成績に関する表があったとき、以下の3つの内容をExcelですぐに計算できるでしょうか。COUNTIFやCOUNTIFSで計算できるということはわかると思いますが、関数をどのように記述すればよいかというのは意外に忘れがちだと思います。特に3は条件が複数あり、かつ、2軸の条件でカウントする必要があるため、少々複雑です。
- 勤続年数5年以上の人数
- 勤続年数5年以上10年未満の人数
- 勤続年数0年以上5年未満で営業成績が0件以上5件未満の人数
本記事ではCOUNTIFSを使って3のような複数条件で2軸でカウントする方法を紹介したいと思います。また、基礎編として1、2を計算する方法も紹介します。
COUNTIFを使って1つの条件でカウントする
それではまずCOUNTIFを使って以下の条件に該当する人数をカウントしたいと思います。
勤続年数5年以上の人数
まず、カウントするための表を作成します。基準値としているのは5年以上という条件をセル参照により設定するため記載しております。数式に条件をベタ打ちすることもできますが、条件を変更したい場合こちらの方がすぐに条件を変更できるので、この方法で行いたいと思います。
5以上の人数を検索する場合、セルH4に以下のように記述します。COUNITIFでは、COUNTIF(検索範囲,検索条件)を記述します。
=COUNTIF($C$4:$C$13,”>=”&G4)
検索範囲は勤続年数のデータのあるセルC4からセルC13を選択します。
検索条件は、直接数値を入力する場合とセルを参照する場合で入力方法が異なります。
| 条件の数値を直接入力 | =COUNTIF($C$4:$C$13,”>=5″) | 不等号と数字を””で囲む |
| 条件の数値をセル参照 | =COUNTIF($C$4:$C$13,”>=”&G4) | 不等号を””で囲み&参照したいセルを入力 |
5以上なので不等号は「>=」と入力します。
Enterキーを押すと勤続年数5年以上の人数6が計算されます。
5年未満を計算する場合は、不等号を「<」とします。
COUNTIFSを使って複数の条件でカウントする
次は、以下の条件に該当する人数をカウントしたいと思います。～以上～未満のような複数の条件に該当するものをカウントする場合は、COUNTIFS関数を使用します。
勤続年数5年以上10年未満の人数
まずカウント用の表を作成します。5年以上10年未満の人数を求めたいので、5年ずつ区切って計算します。この場合基準値の上限と下限の両方を入力しておきます。
5年以上10人未満の人数を検索する場合、セルI6に以下のように記述します。COUNITIFSでは、COUNTIF(検索範囲1,検索条件1,検索範囲2,検索条件2,…)を記述します。
=COUNTIFS($C$4:$C$13,”>=”&G6,$C$4:$C$13,”<“&H6)
検索範囲はいずれも勤続年数のデータのあるセルC4からセルC13を選択します。
1つ目の条件は5年以上なので、セルG6基準値5を参照し、不等号>=を入力します。2つ目の条件は10年未満なので、セルH6基準値10を参照し、不等号<を入力します。
Enterキーを押すと勤続年数5年以上10年未満の人数2が計算されます。
セルI6の数式をコピーすれば、他の条件の数値も計算することができます。
COUNTIFSを使って複数条件かつ2軸でカウントする
最後に本題である以下の条件に該当する人数をカウントしたいと思います。考え方としては4つの条件があると考えればよいだけで、基本は先ほどのCOUNTIFSと変わりはないです。
勤続年数0年以上5年未満で営業成績が0件以上5件未満の人数
まずカウント用の表を作成します。少々複雑ですが、縦軸に先ほどと同様に勤続年数をカウントするための条件と基準値を記載します。今回は営業成績の条件もあるため、横軸に営業成績をカウントするための条件と基準値を同様に記載します。
条件に該当するセルであるセルJ10に以下の数式を入力します。少々わかりづらいので以下の表で整理していますが、4つの条件が入力されていることがわかります。注目していただきたい点は検索範囲が条件によって変わっているということです。
=COUNTIFS($C$4:$C$13,”>=”&$G10,$C$4:$C$13,”<“&$H10,$D$4:$D$13,”>=”&J$4,$D$4:$D$13,”<“&J$5)
| 条件 | 数式 | 検索範囲 | 検索条件 |
|---|---|---|---|
| 勤続年数0年以上 | $C$4:$C$13,”>=”&$G10 | 勤続年数の列「$C$4:$C$13」 | 基準値0以上「”>=”&$G10」 |
| 勤続年数5年未満 | $C$4:$C$13,”<“&$H10 | 勤続年数の列「$C$4:$C$13」 | 基準値5未満「”<“&$H10」 |
| 営業成績0件以上 | $D$4:$D$13,”>=”&J$4 | 営業成績の列「$D$4:$D$13」 | 基準値0以上「”>=”&J$4」 |
| 営業成績5件未満 | $D$4:$D$13,”>=”&J$4 | 営業成績の列「$D$4:$D$13」 | 基準値5未満「”<“&J$5」 |
Enterキーを押すと勤続年数0年以上5年未満で営業成績が0件以上5件未満の人数1が計算されます。
セルJ10の数式を他のセルにコピーすれば、他の条件の数値も計算することができます。
基本的な考え方としては、4つの条件のCOUNTIFSですが、カウントする軸によって検索範囲が異なるということに注意しましょう。
コメント