Excelでランキングを作るとき、
「部署ごとに売上順位を出したい」
「地域と部署の両方が一致する中で順位を出したい」
「同点の場合は利益が高いほうを上位にしたい」
と思ったことはありませんか？
単純な順位であればランキング関数で対応できますが、実務では「条件付きで順位を出したい」というケースも多いです。
たとえば、
- 営業部の中だけで売上ランキングを出す
- 東日本エリアの営業部だけで順位を出す
- 部署と地域が同じ人の中で順位を出す
- 売上が同じ場合は利益で順位を分ける
といったケースです。
このような複数条件のランキングは、COUNTIFS関数やSUMPRODUCT関数を使うと作成できます。
この記事では、Excelで複数条件を加味したランキングを作る方法を、実務で使いやすい形で解説します。
今回使うサンプル表
この記事では、以下のような表を例に解説します。
今回は主に、
- 部署ごとの売上ランキング
- 部署＋地域ごとの売上ランキング
- 同点時に利益で順位を分けるランキング
を作っていきます。
複数条件ランキングの基本的な考え方
複数条件でランキングを作るときは、次のように考えると分かりやすいです。
同じ条件に当てはまるデータの中で、自分より数値が高い人が何人いるかを数える
↓
その人数に1を足す
↓
自分の順位になる
たとえば、自分より売上が高い人が0人なら1位です。
自分より売上が高い人が2人いれば3位です。
つまり、ランキングを直接出しているというより、
自分より上にいる人の数を数えて順位を作る
というイメージです。
COUNTIFS関数でランキングを作る
COUNTIFS関数で部署別ランキングを作る
まずは、部署ごとの売上ランキングを作る方法です。
B列に部署、D列に売上がある場合、以下の数式で部署別ランキングを出せます。
=COUNTIFS($B$3:$B$14,B3,$D$3:$D$14,">"&D3)+1この数式では、「同じ部署の中で、自分より売上が高い人の人数」を数えています。
最後に +1 することで順位になります。
たとえば、同じ部署の中で自分より売上が高い人がいなければ、結果は1位になります。
部署と地域の2条件でランキングを作る
次に、部署と地域の両方が一致する中でランキングを作る方法です。
たとえば、
- 部署が同じ
- 地域も同じ
- その中で売上順位を出す
という場合は、以下の数式を使います。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)+1この数式では、次の3つの条件を指定しています。
・B列がB3と同じ部署であること
・C列がC3と同じ地域であること
・D列の売上がD3より大きいこと
つまり、「同じ部署・同じ地域の中で、自分より売上が高い人の人数」を数えているわけです。
部署だけでなく、地域や担当商品など、複数の条件を加味して順位を出したいときに便利です。
特定の条件に当てはまる行だけ順位を表示する
営業部かつ東日本エリアの人だけに順位を表示したい場合は、IF 関数と組み合わせます。
=IF(AND($B3="営業部",$C3="東日本"),COUNTIFS($B$3:$B$14,"営業部",$C$3:$C$14,"東日本",$D$3:$D$14,">"&$D3)+1,"")この数式では、「部署が営業部、かつ地域が東日本の場合だけ順位を表示、それ以外は空白にする」という処理をしています。
ランキングを表示したい対象が決まっている場合は、このように IF 関数を組み合わせると見やすくなります。
SUMPRODUCT関数で複数条件ランキングを作る
条件に一致する場合はTRUE、不一致の場合はFALSEになります。
SUMPRODUCT関数では、このTRUE/FALSEを1/0のように扱えるため、条件をすべて満たした行だけが1としてカウントされます。
同じランキングは、SUMPRODUCT関数でも作れます。
部署と地域が同じ人の中で売上ランキングを出す場合は、以下のようにします。
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*($D$3:$D$14>D3))+1考え方はCOUNTIFS関数と同じです。
同じ部署
かつ
同じ地域
かつ
自分より売上が高い
この条件を満たす人数を数え、最後に +1 して順位を出しています。
SUMPRODUCT関数では、条件を「*」でつなげることで、複数条件をまとめて判定できます。
COUNTIFS関数とSUMPRODUCT関数はどちらを使うべき？
基本的には、分かりやすさを重視するならCOUNTIFS関数でOKです。
部署別ランキングや、部署＋地域のような条件付きランキングであれば、COUNTIFS関数のほうが読みやすいです。
一方で、SUMPRODUCT関数は、
- 条件を掛け合わせて考えたい
- 複雑な条件を1つの数式で処理したい
- 他の配列計算と組み合わせたい
という場合に便利です。
まずはCOUNTIFS関数で作り、より複雑な条件にしたい場合はSUMPRODUCT関数を使う、という流れがおすすめです。
同点の場合はどうなる？
ここまで紹介した数式では、売上が同じ場合は同じ順位になります。
たとえば、同じ部署・同じ地域の中で売上150の人が2人いる場合、どちらも同じ順位になります。
これは、「自分より売上が高い人の人数」を数えて、その人数に1を足して順位を出しているためです。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)+1この数式では、”>”&D3 としているため、自分と同じ売上の人はカウントされません。
つまり、売上150の人が2人いても、どちらから見ても「自分より売上が高い人」は0人です。
そのため、どちらも 0+1=1位 になります。
なお、”>=” を使うと、自分自身や同点の人までカウントしてしまうため、順位がズレる原因になります。
同点時に利益で順位を分ける方法
売上が同じ場合に、利益が高い人を上位にしたい場合は、条件を追加します。
たとえば、
- 第1条件：売上が高い人を上位
- 第2条件：売上が同じなら利益が高い人を上位
というランキングにしたい場合です。
COUNTIFS関数では、以下のように書けます。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)
+COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,D3,$E$3:$E$14,">"&E3)
+1少し長く見えますが、分けて考えるとシンプルです。
1つ目のCOUNTIFSでは、「同じ部署・同じ地域の中で、自分より売上が高い人」を数えています。
2つ目のCOUNTIFSでは、「同じ部署・同じ地域・同じ売上の中で、自分より利益が高い人」を数えています。
この2つを足して、最後に +1 することで、売上が同じ場合でも利益で順位を分けることができます。
なお、売上も利益も同じ場合は、同じ順位になります。完全に順位を分けたい場合は、社員番号や行番号など、さらに別の条件を追加する必要があります。
SUMPRODUCT関数で同点時の順位を分ける方法
SUMPRODUCT関数でも、同点時に利益で順位を分けることができます。
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*(($D$3:$D$14>D3)+(($D$3:$D$14=D3)*($E$3:$E$14>E3))))+1この数式では、
同じ部署・同じ地域の中で、
・自分より売上が高い人
または
・売上が同じで、自分より利益が高い人
を数えています。
COUNTIFS関数より少し難しく見えますが、複数条件をまとめて扱いたい場合には便利です。
ただし、初心者の方はまずCOUNTIFS関数で作る方法から覚えるのがおすすめです。
注意点：条件に「>=」を使うと順位がズレることがある
ランキングを作るときは、基本的に「>=」ではなく「>」を使うのがおすすめです。
たとえば、次のような数式です。
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*($D$3:$D$14>=D3))この場合、自分自身もカウントされてしまいます。
そのため、思った順位とズレることがあります。
順位を出すときは、自分より大きい数を数えるという考え方で、> を使い、最後に +1 するのが分かりやすいです。
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*($D$3:$D$14>D3))+1
うまく順位が出ないときの確認ポイント
複数条件ランキングがうまくいかない場合は、以下を確認してみてください。
範囲がずれていないか
COUNTIFS関数やSUMPRODUCT関数では、参照範囲をそろえる必要があります。
たとえば、B列は3行目から14行目、D列は3行目から15行目のように範囲がずれていると、正しく計算できません。
悪い例：
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$15=C3)*($D$3:$D$14>D3))+1正しい例：
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*($D$3:$D$14>D3))+1
絶対参照にしているか
数式を下にコピーする場合は、ランキング対象の範囲を固定する必要があります。
$B$3:$B$14
$C$3:$C$14
$D$3:$D$14このように「$」を付けて絶対参照にしておくと、コピーしても範囲がずれません。
数値が文字列になっていないか
売上や利益の列が数値ではなく文字列になっていると、正しく比較できないことがあります。
見た目は数字でも、文字列として入力されている場合があるので注意しましょう。
よくある質問（FAQ）
複数条件のランキングはCOUNTIFSだけで作れますか？
はい、部署別や地域別などの条件付きランキングであれば、COUNTIFS関数だけで作れます。
たとえば、部署と地域が同じ人の中で売上順位を出す場合は、以下の数式で対応できます。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)+1SUMPRODUCT関数を使うメリットはありますか？
あります。
COUNTIFS関数のほうが分かりやすいですが、SUMPRODUCT関数は複数条件を掛け合わせて処理できるため、より複雑な条件に対応しやすいです。
ただし、基本的な条件付きランキングであれば、まずはCOUNTIFS関数で十分です。
同点を同じ順位にできますか？
できます。
自分より大きい値だけを数える数式にすれば、同点は同じ順位になります。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)+1同点時に別条件で順位を分けられますか？
できます。
たとえば、売上が同じ場合に利益が高い人を上位にするなら、以下のようにします。
=COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,">"&D3)
+COUNTIFS($B$3:$B$14,B3,$C$3:$C$14,C3,$D$3:$D$14,D3,$E$3:$E$14,">"&E3)
+1まとめ：複数条件のランキングはCOUNTIFS関数とSUMPRODUCT関数で作れる
Excelで複数条件を加味したランキングを作りたい場合は、COUNTIFS関数やSUMPRODUCT関数を使うと便利です。
基本的な考え方は、
同じ条件に当てはまるデータの中で、
自分より数値が高い人の人数を数えて、
最後に1を足す
です。
部署別ランキングや、部署＋地域のような条件付きランキングであれば、COUNTIFS関数で分かりやすく作れます。
より複雑な条件を組み合わせたい場合は、SUMPRODUCT関数を使うのもおすすめです。
特に実務では、
- 部署別の売上順位
- 地域別の成績ランキング
- 担当者別の評価順位
- 同点時に利益や達成率で順位を分ける
といった場面で活用できます。
複数条件のランキングを作れるようになると、売上管理表や成績表、評価表の使い勝手がかなり良くなります。
ぜひ実務でも活用してみてください。
コメント