【Excel】PERCENTRANK.INC関数を使って「上位〇%で合格」を判定する方法

Excelでは、PERCENTRANK.INC関数を使うことで「上位○%で合格」を判定することができます。

PERCENTRANK.INC関数とは

PERCENTRANK.INC関数とは、指定した数値が範囲の中でどの程度の位置にあるかを表示させることができる関数です。

関数	役割
PERCENTRANK.INC(配列,x,[有効桁数])	指定した数値「x」が参照した「配列」の範囲内でどの程度の位置にあるかを示す。最上位が1、最下位が0で相対的に判断される。

たとえば、以下のようなテストの成績に基づき、それぞれの相対的な位置を算出したいと思います。

まず、「田中 紘一」の位置を求めるために、セルD4に以下のように数式を入力します。

=PERCENTRANK.INC($C$4:$C$13,C4,2)

数式	役割
PERCENTRANK.INC($C$4:$C$13	位置を求める範囲はセルC4～C13。他の人も同じ範囲で位置を求めるため絶対参照とします。
C4	位置を求める数値はセルC4。
2	小数点以下の有効桁数は2桁とする。

そうすると、以下のように「0.55」と表示されます。つまり、最上位を「1」とすると「田中 紘一」は「0.55」に位置することがわかります。

セルD4の数式を他の人にもコピーすると、以下のようにそれぞれの位置が表示されます。「1」の「大崎 千尋」が最上位、「0」の「杉本 隆」が最下位であることがわかります。

わかりやすくショートカットキー「Ctrl + Shift + 5」でパーセンテージ表示にします。

「上位〇%で合格」を判定する方法

次に「上位〇%で合格」を判定する方法を紹介します。

PERCENTRANK.INC関数で求めた位置を活用して、それぞれの合否を判定します。

まず、合格とする上位の割合を別のセルに記載しておきます。数式に直接入力しても良いのですが、上位〇%の割合を変更した際に数式も変更するのは手間なので、今回は参照する形式にしたいと思います。

「田中 紘一」の合否を判定するため、セルE4に以下のように数式を入力します。

=IF(D4>=1-$H$4,”合格”,”不合格”)

数式	役割
=IF(D4>=1-$H$4	セルD4が（1-セルH4）以上かどうかを判定する。 注意が必要なのは、上位30%以上を判定する場合、PERCENTRANK.INC関数で表示される値の70%以上かどうかなので、「1-$H$4」とする。
“合格”	セルD4が（1-セルH4）以上であれば、「合格」と表示する。
“不合格”	上記以外の場合は不合格と表示する。

そうすると、「田中 紘一」は「不合格」と表示されます。「田中 紘一」の位置は55%で70%よりも小さいため、この判定は正しいことがわかります。

セルE4の数式を他の人のセルにもコピーすることで、「合格」「不合格」が表示されます。

なお、上記では位置を求める列と合否を判定する列を分けましたが、以下のように2つの数式を組み合わせることで、一つの列で合否を判定することも可能です。

=IF(PERCENTRANK.INC($C$4:$C$13,C4,2)>=1-$G$4,”合格”,”不合格”)

このように関数を利用することで、点数が変更した場合も自動的に合否を判定することができます。