スプレッドシートのPERCENTRANK.EXC関数の使い方｜パーセント順位（0,1除外）

スポンサーリンク

「PERCENTRANK関数を使ったら最小値が0、最大値が1になったけど、これでいいの？」。パーセント順位を求めていて、こんな疑問を持ったことはありませんか？

通常のPERCENTRANK関数では端の値が0や1になります。しかし統計分析では「完全な0%や100%は存在しない」と考えるのが一般的です。

そんなときに使うのがスプレッドシートのPERCENTRANK.EXC関数です。この記事では基本の書き方からPERCENTRANK関数との違い、実務での活用例まで解説します。

PERCENTRANK.EXC関数とは？スプレッドシートで排他的パーセント順位を返す関数

PERCENTRANK.EXC関数（読み方: パーセントランク・エクスクルーシブ）は、データセットの中で指定した値が何%の位置にあるかを返す関数です。

EXCは「Exclusive（排他的）」の略です。戻り値の範囲から0と1を除外するのが特徴です。

通常のPERCENTRANK関数は0〜1の範囲を返します。一方、PERCENTRANK.EXC関数は0より大きく1より小さい範囲のみを返します。最小値でも0にはならず、最大値でも1にはなりません。

PERCENTRANK.EXC関数にできることをまとめると、次のとおりです。

  • データの中で値が何%の位置にあるかを求める（0と1を除外）
  • 統計的に厳密なパーセント順位を計算する
  • 正規分布に基づく分析で正確な確率計算を行う
  • PERCENTILE.EXC関数と対で使う

NOTE

PERCENTRANK.EXC関数はGoogleスプレッドシートとExcel（2010以降）で使えます。Excelとの互換性も問題ありません。

PERCENTRANK.EXC関数の書き方（構文と引数）

基本構文

=PERCENTRANK.EXC(データ, 値, [有効桁数])

引数の説明

引数必須/任意説明
データ必須パーセント順位を求める対象のセル範囲または配列
値必須順位を調べたい数値
有効桁数任意戻り値の有効桁数。省略すると3（小数第3位まで）

引数の構成はPERCENTRANK関数とまったく同じです。違いは戻り値の範囲だけなので、切り替えも簡単ですよ。

TIP

有効桁数は省略すると3桁です。データ件数が多いときは5〜6を指定すると、より細かい順位の差がわかります。

PERCENTRANK.EXC関数の基本的な使い方

以下の売上データでPERCENTRANK.EXC関数を使ってみましょう。

B2からB11に10人分の売上データが入っているとします。

 A列（担当者）B列（売上）
2行目田中120
3行目鈴木85
4行目佐藤200
5行目山田150
6行目高橋95
7行目伊藤180
8行目渡辺110
9行目中村130
10行目小林160
11行目加藤140

各担当者のパーセント順位を求める

C2セルに以下の数式を入力します。

=PERCENTRANK.EXC($B$2:$B$11, B2)

範囲を絶対参照（$B$2:$B$11）にして、下にコピーしましょう。結果は以下のとおりです。

担当者売上パーセント順位意味
田中1200.363下から36.3%の位置
鈴木850.090最下位でも0にならない
佐藤2000.909最上位でも1にならない
山田1500.636下から63.6%の位置
高橋950.181下から18.1%の位置
伊藤1800.818下から81.8%の位置
渡辺1100.272下から27.2%の位置
中村1300.454下から45.4%の位置
小林1600.727下から72.7%の位置
加藤1400.545下から54.5%の位置

注目すべきポイントは2つあります。鈴木（最小値85）のパーセント順位が0ではなく0.090です。佐藤（最大値200）も1ではなく0.909になっています。これがEXC（排他的）の特徴です。

計算のしくみ

PERCENTRANK.EXC関数は、PERCENTRANK関数とは異なる計算式を使います。

計算式は (その値の昇順順位) / (データ件数 + 1) です。10件のデータなら分母は11になります。

  • 最小値（85）: 1 / 11 = 0.090
  • 2番目（95）: 2 / 11 = 0.181
  • 5番目（130）: 5 / 11 = 0.454
  • 最大値（200）: 10 / 11 = 0.909

PERCENTRANK関数では分母が「データ件数 – 1」でした。EXC関数は「データ件数 + 1」を使います。この違いにより、0と1が結果に含まれなくなるしくみです。

有効桁数を変えてみる

第3引数で結果の精度を変えられます。

=PERCENTRANK.EXC($B$2:$B$11, B9, 1)   → 0.4
=PERCENTRANK.EXC($B$2:$B$11, B9, 3)   → 0.454（デフォルト）
=PERCENTRANK.EXC($B$2:$B$11, B9, 6)   → 0.454545

使い方はPERCENTRANK関数と同じです。

PERCENTRANK.EXC関数の実践的な使い方・応用例

正規分布に基づく偏差値を計算する

PERCENTRANK.EXC関数の結果を正規分布の逆関数（NORM.S.INV）に渡すと、Z得点（標準化得点）を求められます。Z得点から偏差値への変換も簡単です。

=NORM.S.INV(PERCENTRANK.EXC($B$2:$B$31, B2)) * 10 + 50

この数式で偏差値が計算できます。PERCENTRANK.EXC関数を使う理由は、NORM.S.INV関数が0と1を受け付けないためです。PERCENTRANK関数だと最小値で0、最大値で1が返り、エラーになってしまいます。

WARNING

NORM.S.INV関数は引数に0または1を指定すると#NUM!エラーになります。偏差値計算にはPERCENTRANK.EXC関数を使いましょう。

成績をパーセンタイル評価する

テスト点数の相対的な位置を、0と1を除外した厳密なパーセント順位で評価します。

=PERCENTRANK.EXC($B$2:$B$31, B2)

結果が0.9以上なら「上位10%」と判定できます。IF関数と組み合わせればランク分けも可能です。

=IF(PERCENTRANK.EXC($B$2:$B$31, B2)>=0.9, "S",
 IF(PERCENTRANK.EXC($B$2:$B$31, B2)>=0.75, "A",
 IF(PERCENTRANK.EXC($B$2:$B$31, B2)>=0.5, "B",
 IF(PERCENTRANK.EXC($B$2:$B$31, B2)>=0.25, "C", "D"))))

PERCENTRANK関数でも同じことはできますが、EXC関数なら最下位でもD評価（0より大きい値）になります。0ちょうどの判定で迷うことがありません。

PERCENTILE.EXC関数と組み合わせる

PERCENTRANK.EXC関数とPERCENTILE.EXC関数は入力と出力が逆の関係です。

=PERCENTILE.EXC(B2:B11, 0.75)    → 165（75パーセンタイルの値）
=PERCENTRANK.EXC(B2:B11, 165)    → 0.75（値165のパーセント順位）

EXC同士で組み合わせると結果が正確に一致します。EXCとINCを混ぜると値がずれるので注意してください。

組み合わせ整合性
PERCENTRANK.EXC + PERCENTILE.EXC一致する
PERCENTRANK.EXC + PERCENTILE.INCずれる
PERCENTRANK関数 + PERCENTILE.INC関数一致する

よくあるエラーと対処法

#N/Aエラー

PERCENTRANK.EXC関数で最もよく見るエラーです。

原因対策
値がデータ範囲の最小値〜最大値の範囲外範囲内の値を指定する
データ範囲に数値が1つもない数値が入った範囲を指定する

データが {50, 100, 150} のとき、値に200を指定すると#N/Aになります。PERCENTRANK.EXC関数はデータ範囲外の値を処理できません。

TIP

IFERROR関数で囲めばエラーを回避できます。=IFERROR(PERCENTRANK.EXC(B2:B11, B2), "範囲外") のように書くと安心ですよ。

#NUM!エラー

以下の場合に発生します。

  • 有効桁数に1未満の値（0やマイナス）を指定した
  • データ範囲が空（数値が1つもない）

有効桁数は1以上の整数を指定してください。

#VALUE!エラー

引数に文字列を指定すると発生します。値や有効桁数には数値を入力しましょう。

PERCENTRANK関数（INC）との違い・使い分け

PERCENTRANK.EXC関数とPERCENTRANK関数の最大の違いは、戻り値に0と1を含むかどうかです。

比較表

項目PERCENTRANK（INC）PERCENTRANK.EXC
戻り値の範囲0〜1（端点を含む）0より大〜1より小（端点を含まない）
最小値の結果01/(N+1)
最大値の結果1N/(N+1)
計算式の分母N-1（データ件数-1）N+1（データ件数+1）
統計的な厳密さ包含的（inclusive）排他的（exclusive）で厳密
NORM.S.INVとの併用#NUM!エラーの可能性エラーなし

10件のデータで結果を比べる

先ほどの売上データ（10人分）で、両関数の結果を並べてみましょう。

担当者売上PERCENTRANKPERCENTRANK.EXC差
鈴木8500.090+0.090
高橋950.1110.181+0.070
渡辺1100.2220.272+0.050
田中1200.3330.363+0.030
中村1300.4440.454+0.010
加藤1400.5550.545-0.010
山田1500.6660.636-0.030
小林1600.7770.727-0.050
伊藤1800.8880.818-0.070
佐藤20010.909-0.091

端に近いほど差が大きく、中央付近ではほぼ同じ値です。データ件数が増えるほど両者の差は小さくなります。

どちらを使うべき？

使い分けの目安は以下のとおりです。

  • PERCENTRANK関数でOK: 売上ランキング、成績の相対評価など通常の業務用途
  • PERCENTRANK.EXC関数を使う: 偏差値計算、正規分布分析、NORM.S.INVとの組み合わせ

迷ったらPERCENTRANK関数（INC）を使えば大丈夫です。NORM.S.INVなどの統計関数と組み合わせるときだけ、EXC関数を選びましょう。

まとめ

PERCENTRANK.EXC関数は、データセットの中で値が何%の位置にあるかを0と1を除外して返す関数です。

この記事のポイント

  • 構文は =PERCENTRANK.EXC(データ, 値, [有効桁数]) の3引数
  • 戻り値は0より大きく1より小さい範囲（排他的）
  • 計算式は 昇順順位 / (データ件数 + 1) で0と1を除外
  • PERCENTRANK関数との違いは端点（0と1）を含むかどうか
  • NORM.S.INVとの併用で偏差値計算に活用できる
  • PERCENTILE.EXC関数と対で使うと結果が正確に一致する

次のステップ：関連する統計関数

PERCENTRANK.EXC関数の使い方がわかったら、以下の関数もあわせて覚えてみてください。データ分析の幅が広がりますよ。

スポンサーリンク
タイトルとURLをコピーしました