5/16/2017

Excel。条件付き順位を算出するにはどうしたらいいの?【RANK】

Excel。条件付き順位を算出するにはどうしたらいいの?

<RANK.EQ関数・IF+COUNTIFS関数>


次のような表があります。

C列の数値に基づき、順位を算出するのですが、
D列の全体順位は、RANK.EQ関数で算出することが出来ますよね。

これは、定番の関数なので、難しくはないと思います。

ただ、今回厄介なのは、B列にある組を参照して、
E列F列にそれぞれの組ごとの順位を算出したいということなのです。
ようするに、【条件付き順位】を算出したいわけです。

RANK.EQ関数には、条件を加味して算出できるようになっていません。

では、復習を兼ねて、
D列の全体順位をRANK.EQ関数を使って算出してみましょう。

D4をクリックして、RANK.EQ関数ダイアログボックスを表示しましょう。

数値には、C4
参照には、$C$4:$C$10
オートフィルで数式をコピーしますので、
絶対参照の設定も忘れずに行っておきましょう。

順序には、ベストのランキングなので、0を設定します。

あとは、OKボタンをクリックして、オートフィルで数式をコピーしましょう。
D4の数式は、
=RANK.EQ(C4,$C$4:$C$10,0)
となっていますね。

さて、いよいよここからが本題。

1組に所属している人たちだけの順位を求めていきましょう。

本来なら、
組ごとに表を作ってもらえるとRANK.EQ関数で算出できるのですが、
今回はまとまってしまっています。

そして、RANK.EQ関数では、条件が付けられない。

そもそも、順位というのは、
その数値より大きい数値が何件あるのか?
ということで順位が算出できるわけです。

ということは、【件数】を求められる関数を使えば、
RANK.EQ関数の代用が出来そうですね。

そこで、COUNTIFS関数を使ってD列と同じように、
まずは全体順位を算出してみます。

数式を作る前に、この表の補足説明をしておきましょう。

E3とF4は、それぞれ1・2という数値が入力されていて、
表示形式を使って、1組順位としています。

では、E列にCOUNTIFS関数ダイアログボックスを表示しましょう。

検索条件範囲1には、$B$4:$B$10

B列の範囲ですね。
オートフィルで数式をコピーすることを考慮して、
絶対参照が設定されています。

検索条件1は、E$3。

これは、数値の1なので、1組かどうかを判定させています。

検索条件範囲2には、$C$4:$C$10
C列の範囲ですね。

検索条件2は、">"&$C4

C4よりも大きいものという意味になります。

なお、比較演算子とセル番地を合体させて使うときには、
””(ダブルコーテーション)と&を使ってセル番地を
結合させてあげる必要があります。

&を使わないと、">$C4"という文字を検索してしまいますので、
注意が必要です。

では、OKボタンをクリックして、
数式をオートフィルでコピーしてみましょう。

今は、1組と2組を「強引」に混ぜた状態ですので、
2位がおかしいとかありますが気にしないようにしましょう。

ただ、E7が0になってしまっています。

ここは1にしたいので、数式に、+1を加えます。
E4の数式は、
=COUNTIFS($B$4:$B$10,E$3,$C$4:$C$10,">"&$C4)+1
今はこのようになっています。

あとは、1組だけの順位に変更してあげます。

つまり、1組だったら、COUNTIFS関数、
そうでなければ空白という数式を作りますので、
先程作成したCOUNTIFS関数をIF関数でネストしていきます。

E4の数式を次のように修正します。
=IF($B4=E$3,COUNTIFS($B$4:$B$10,E$3,$C$4:$C$10,">"&$C4)+1,"")

あとは、1組・2組とも数式をオートフィルでコピーしてみましょう。

すると、『条件付き順位』を算出することができました。

このように、条件付き順位を算出する場合には、
IF+COUNTIFS関数で対応できます。