スプレッドシートのPERCENTILE関数の使い方｜パーセンタイル値を求める方法

スポンサーリンク

「上位20%のラインって、具体的に何点？」。データ分析でこんな疑問に出くわすことはありませんか？

平均や中央値だけでは「全体のどのあたりか」がつかみにくいですよね。データの分布をもっと細かく把握したい場面は意外と多いものです。

そんなときに使うのがPERCENTILE関数です。この記事では基本の書き方から実務での活用例まで解説します。QUARTILE関数やMEDIAN関数との関係もあわせて整理しました。

PERCENTILE関数とは？パーセンタイル値を求める関数

PERCENTILE関数（読み方: パーセンタイル関数）は、データの中から指定したパーセンタイル（百分位数）に相当する値を返す関数です。

パーセンタイルとは、データを小さい順に並べたとき「下から何%の位置にあたる値か」を表す指標です。たとえば90パーセンタイルなら「下から90%の位置の値」、つまり上位10%のラインを意味します。

身近な例でいえば、健康診断の結果に書かれている「身長は90パーセンタイル」は「100人中90番目（上位10%）の位置」ということです。

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

  • 指定したパーセンタイル位置の値を求める
  • 上位○%のラインを数値で把握する
  • データの分布（ばらつき）を細かく分析する
  • QUARTILE関数やMEDIAN関数と同じ計算を柔軟に行う

NOTE

PERCENTILE関数はGoogleスプレッドシートの全バージョンで使えます。Excelとの互換性も問題ありません。

PERCENTILE関数の書き方（構文と引数）

基本構文

=PERCENTILE(データ, パーセンタイル)

引数の説明

引数必須/任意説明
データ必須パーセンタイル値を求めたいセル範囲または配列
パーセンタイル必須0〜1の数値。0.9なら90パーセンタイル

第2引数は「0〜1」の範囲で指定します。0.25なら25パーセンタイル、0.5なら50パーセンタイル（中央値）です。

TIP

「上位10%のラインを知りたい」なら、パーセンタイルには 0.9（= 下から90%）を指定します。「上位○%」と「下から○%」が逆になる点に注意してください。

PERCENTILE関数の基本的な使い方

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

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

90パーセンタイルを求める

上位10%のラインを知りたいときは、第2引数に0.9を指定します。

=PERCENTILE(B2:B11, 0.9)

結果は 186 です。このデータでは「売上186以上なら上位10%に入る」ということがわかります。

いろいろなパーセンタイルを求める

第2引数を変えるだけで、さまざまな位置の値を取得できます。

数式パーセンタイル結果意味
=PERCENTILE(B2:B11, 0.25)25106.25下位25%のライン
=PERCENTILE(B2:B11, 0.5)50135中央値
=PERCENTILE(B2:B11, 0.75)75157.5上位25%のライン
=PERCENTILE(B2:B11, 0.9)90186上位10%のライン

50パーセンタイルの「135」は、MEDIAN関数で求めた中央値と一致します。

計算のしくみ（線形補間）

PERCENTILE関数は、データが指定したパーセンタイルの位置にぴったり収まらないとき、前後の値から線形補間で計算します。

たとえばデータが10個で90パーセンタイルを求める場合、「10個 x 0.9 = 9番目」の位置を計算します。小さい順で9番目（180）と10番目（200）の間を補間して、186という値が求まります。

ちょっとむずかしく見えますが、PERCENTILE関数が自動でやってくれるので、計算方法を覚える必要はありません。

PERCENTILE関数の実践的な使い方・応用例

成績のランク分けに使う

PERCENTILE関数で各ランクのボーダーラインを求め、IFS関数と組み合わせてA〜Dランクに振り分けます。

まずボーダーラインを求めます。

=PERCENTILE(B2:B11, 0.75)   → 上位25%ライン（Aランク基準）
=PERCENTILE(B2:B11, 0.5)    → 上位50%ライン（Bランク基準）
=PERCENTILE(B2:B11, 0.25)   → 上位75%ライン（Cランク基準）

次に、IFS関数で各担当者をランク分けします。

=IFS(B2>=PERCENTILE($B$2:$B$11,0.75), "A",
     B2>=PERCENTILE($B$2:$B$11,0.5), "B",
     B2>=PERCENTILE($B$2:$B$11,0.25), "C",
     TRUE, "D")

結果は以下のようになります。

担当者売上ランク
田中120C
鈴木85D
佐藤200A
山田150B
高橋95D
伊藤180A
渡辺110C
中村130C
小林160A
加藤140B

範囲を絶対参照（$B$2:$B$11）にするのを忘れずに。数式を下にコピーしても範囲がずれません。

外れ値の検出に使う

極端に大きい値や小さい値（外れ値）を見つけるには、IQR（四分位範囲）を使う方法が一般的です。

=PERCENTILE(B2:B11, 0.75) - PERCENTILE(B2:B11, 0.25)

この計算でIQR（第3四分位数 – 第1四分位数）が求まります。IQRの1.5倍を超える値を外れ値として検出できます。

=IF(OR(B2 > PERCENTILE($B$2:$B$11,0.75) + 1.5*(PERCENTILE($B$2:$B$11,0.75)-PERCENTILE($B$2:$B$11,0.25)),
       B2 < PERCENTILE($B$2:$B$11,0.25) - 1.5*(PERCENTILE($B$2:$B$11,0.75)-PERCENTILE($B$2:$B$11,0.25))),
   "外れ値", "")

数式が長くなりますが、やっていることはシンプルです。75パーセンタイルと25パーセンタイルの差（IQR）を求め、その1.5倍を超えたデータに「外れ値」と表示しています。

条件付き書式で上位10%をハイライトする

「上位10%のセルだけ色を付けたい」ときは、条件付き書式にPERCENTILE関数を使います。

  1. B2:B11を選択する
  2. 「表示形式」→「条件付き書式」を開く
  3. 「カスタム数式」を選び、以下の数式を入力する
=B2>=PERCENTILE($B$2:$B$11, 0.9)

これで売上が90パーセンタイル以上のセルが自動的にハイライトされます。

よくあるエラーと対処法

#NUM!エラー

PERCENTILE関数で最もよく見るエラーです。以下の原因が考えられます。

原因対策
第2引数が0未満または1超0〜1の範囲で指定する
データ範囲が空（数値なし）数値が入った範囲を指定する

第2引数に「90」のように0〜1の範囲外の値を入れてしまうケースが多いです。90パーセンタイルなら「0.9」と指定してください。

#VALUE!エラー

第2引数に文字列（例: “0.9”）を指定すると発生します。数値を直接入力するか、数値が入ったセルを参照してください。

TIP

第2引数のセルが空欄になっている場合も#VALUE!エラーの原因になります。セル参照を使う場合は、値が入っているか確認しましょう。

文字列・論理値の扱い

PERCENTILE関数は、データ範囲に含まれる文字列やTRUE/FALSEを自動的に無視します。エラーにはなりませんが、データ件数が想定より少なくなることがあります。

QUARTILE関数・MEDIAN関数との関係

PERCENTILE関数は、QUARTILE関数やMEDIAN関数と密接な関係があります。使い分けを整理しましょう。

PERCENTILE関数とQUARTILE関数の関係

QUARTILE関数は、PERCENTILE関数の特殊なケースです。QUARTILE関数で求められる値は、すべてPERCENTILE関数でも求められます。

QUARTILE関数同じ結果のPERCENTILE意味
=QUARTILE(データ, 0)=PERCENTILE(データ, 0)最小値
=QUARTILE(データ, 1)=PERCENTILE(データ, 0.25)第1四分位数（25パーセンタイル）
=QUARTILE(データ, 2)=PERCENTILE(データ, 0.5)第2四分位数（中央値）
=QUARTILE(データ, 3)=PERCENTILE(データ, 0.75)第3四分位数（75パーセンタイル）
=QUARTILE(データ, 4)=PERCENTILE(データ, 1)最大値

QUARTILE関数は25%刻み（0/25/50/75/100）の5つしか指定できません。PERCENTILE関数なら0.1刻みでも0.01刻みでも自由に指定できます。

つまり、PERCENTILE関数はQUARTILE関数の「上位互換」です。

PERCENTILE関数とMEDIAN関数の違い

=PERCENTILE(データ, 0.5) と =MEDIAN(データ) は同じ結果を返します。どちらもデータの中央値です。

項目MEDIAN関数PERCENTILE関数
構文=MEDIAN(値1, [値2, ...])=PERCENTILE(データ, パーセンタイル)
求められる値中央値のみ任意のパーセンタイル値
引数の数最大30個2つ
使い分け中央値だけ知りたいとき分布全体を分析したいとき

中央値だけが必要ならMEDIAN関数がシンプルです。複数のパーセンタイルを求めるならPERCENTILE関数が便利です。

PERCENTILE.INC / PERCENTILE.EXCとの関係

スプレッドシートには、PERCENTILE関数の派生版が2つあります。

関数第2引数の範囲説明
PERCENTILE0〜1標準版（PERCENTILE.INCと同じ動作）
PERCENTILE.INC0〜1PERCENTILEの正式名。0と1を含む
PERCENTILE.EXC0より大〜1より小0と1を除外する。統計的にはこちらが厳密

通常の業務ではPERCENTILE関数（= PERCENTILE.INC）で問題ありません。

まとめ

PERCENTILE関数は、データの中から指定したパーセンタイル位置の値を返す関数です。

この記事のポイント

  • 構文は =PERCENTILE(データ, パーセンタイル) の2引数
  • 第2引数は0〜1の範囲で指定する（0.9 = 90パーセンタイル）
  • 「上位○%のライン」を求めるのに最適
  • QUARTILE関数の上位互換。25%刻みに限らず自由な位置を指定できる
  • 50パーセンタイル = 中央値 = MEDIAN関数と同じ結果
  • ランク分けや外れ値検出の応用に使える

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

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

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