RANKとPERCENTILEで順位を求める

多くの場合、知りたいのは値そのものだけではなく、グループ内での位置です。この営業担当者は1位ですか、それとも12位ですか。この得点は上位10%に入っていますか。

これに答えるのが2つの関数群です。RANKは順位を示し、PERCENTILEは指定した境界における値を示します。これらを組み合わせると、生の数値を順位や基準値に変換できます。

RANK.EQは、範囲内での値の順位を返します。値、範囲、並べ替え順序のフラグを指定します。

• 順序0（または省略）：最大値から順に順位を付けます（降順）。
• 順序1：最小値から順に順位を付けます（昇順）。

ここでは、最高の売上額が1位になります。

ドル記号に注目してください：$B$2:$B$20。数式を下方向にコピーしても、範囲は固定されたままにする必要があります。一方、値の参照B2はB3、B4というように移動します。

ドル記号を付け忘れると、行ごとに範囲も移動し、意味のない順位が生成されます。ここでは、範囲に絶対参照を指定することが重要です。

RANK.EQは同じ値に同じ順位を付け、その次の順位を飛ばします。2つの値が2位で同じ場合、両方に2が表示され、次の値は4位になります（3位はありません）。

同順位の場合に平均順位を付けたい場合は、RANK.AVGを使います。2位と3位に当たる2つの値が同順位の場合、それぞれに2.5が表示されます。

同順位があっても一意の順位にするには、COUNTIFを使ったタイブレークを追加します。現在の行より上に同じ値がいくつあるかを数え、その分を順位に加えます。

これにより、重複のない1、2、3、4というきれいな連番が保証されるため、リーダーボードに便利です。

PERCENTILE.INCは、指定したパーセント点に位置する値を返します。範囲と、0から1までの小数を指定します。

下の数式は90パーセンタイルを返します。これは、データの90%がその値以下になる値です。「上位10%の成績者」のような基準を設定するのに最適です。

四分位数は、データを4つの部分に分けます。QUARTILE.INC関数はそのショートカットです。第1四分位数は25パーセンタイル、第2四分位数は50パーセンタイル（中央値）、第3四分位数は75パーセンタイルに相当します。

下の数式は第3四分位数を返します。これはPERCENTILE.INC(A2:A101,0.75)と同じ結果になります。

方法には2種類あります。PERCENTILE.INC（包含的）は0と1を受け入れ、PERCENTILE.EXC（排他的）は0と1の間だけを受け入れ、両端の値は対象にしません。

日常的なレポートでは、通常.INCを選びます。境界点を除外する統計上の慣例に従う場合は、.EXCを使います。

RANKは順位を返し、PERCENTILEは境界における値を返します。PERCENTRANK.INCはパーセンタイルとは逆に、値を指定して、それが何パーセンタイルに当たるかを返します。

ある生徒の得点が88で、PERCENTRANKが0.92を返した場合、その生徒はグループの92%を上回ったことになります。100を掛けるとパーセント表示にできます。

RANK と PERCENTILE は数値に対してのみ機能します。範囲内のテキストラベルや空白セルは無視されるため、誤って見出しが含まれていても順位付けに影響しません。

ただし、テキストとして保存された数値には注意してください。これらは完全に無視されるため、すべての順位とパーセンタイルがずれます。順位がおかしい場合は、すべての値が左揃えのテキストではなく、正しい数値になっていることを確認してください。

B列に20人の担当者の売上が入力されています。それぞれの担当者の順位と、上位四分位に入っているかどうかを求めます。

• 順位：=RANK.EQ(B2,$B$2:$B$21,0)
• 上位四分位のフラグでは、売上を75パーセンタイルと比較します。

以下の数式は、基準値に基づいて各担当者に Top または Standard を返します。

順位付け関数の理解度を確認しましょう。

値をグループ内で位置付ける方法を学びました。

• RANK.EQ は順位を返します。順序 0 は大きい値から、1 は小さい値から並べます。$ を使って範囲を固定します。
• RANK.AVG は同順位の順位を平均します。COUNTIF を追加すると、一意の順位を作成できます。
• PERCENTILE.INC は基準値における値を返します。QUARTILE.INC は、25・50・75パーセンタイルを簡単に求める方法です。
• PERCENTRANK.INC はその逆で、指定した値のパーセンタイルを返します。