「PERCENTILE関数とPERCENTILE.EXC関数って何が違うの？」。スプレッドシートでパーセンタイルを求めるとき、こんな疑問が出てきませんか？
PERCENTILE関数だけでも十分使えますが、統計的に厳密な分析をしたい場面ではPERCENTILE.EXC関数の出番です。0と1を除外した「排他的パーセンタイル」を返すのが特徴ですよ。
この記事では、PERCENTILE.EXC関数の基本の書き方からPERCENTILE関数との違い、実務での活用例まで解説します。
PERCENTILE.EXC関数とは？排他的パーセンタイルを求める関数
PERCENTILE.EXC関数（読み方: パーセンタイル・エクスクルーシブ）は、データの中から排他的パーセンタイル値を返す関数です。「EXC」は「Exclusive（排他的）」の略です。
排他的パーセンタイルとは、0%と100%を除外した範囲でパーセンタイルを計算する方式のことです。通常のPERCENTILE関数が0〜1の全範囲を使うのに対し、PERCENTILE.EXC関数は1/(n+1)〜n/(n+1)の範囲で計算します。
たとえばデータが10個なら、指定できる範囲は1/11（約0.09）〜10/11（約0.91）です。「最小値そのもの」や「最大値そのもの」は返しません。
PERCENTILE.EXC関数にできることをまとめると、次のとおりです。
- 0%と100%を除外した厳密なパーセンタイル値を求める
- 端の値（最小値・最大値）に引っ張られにくい分析ができる
- 統計学の教科書に沿った正確なパーセンタイル計算ができる
- PERCENTILE関数では対応しにくい小データ数の精密分析に使える
NOTE
PERCENTILE.EXC関数はGoogleスプレッドシートで利用できます。ExcelではExcel 2010以降で対応しています。
PERCENTILE.EXC関数の書き方（構文と引数）
基本構文
=PERCENTILE.EXC(データ, パーセンタイル)引数の説明
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| データ | 必須 | パーセンタイル値を求めたいセル範囲または配列 |
| パーセンタイル | 必須 | 0より大きく1より小さい数値（0と1は指定不可） |
第2引数の有効範囲は1/(n+1) 〜 n/(n+1)です。nはデータの個数を指します。この範囲外の値を指定すると#NUM!エラーになります。
TIP
データが10個なら、第2引数は約0.09〜0.91の範囲で指定できます。0.01や0.99のような極端な値を指定するには、データ数がもっと必要ですよ。
PERCENTILE.EXC関数の基本的な使い方
以下のテスト得点データでPERCENTILE.EXC関数を使ってみましょう。
B2からB11に10人分の得点データが入っているとします。
| A列（名前） | B列（得点） | |
|---|---|---|
| 2行目 | 田中 | 72 |
| 3行目 | 鈴木 | 85 |
| 4行目 | 佐藤 | 91 |
| 5行目 | 山田 | 68 |
| 6行目 | 高橋 | 78 |
| 7行目 | 伊藤 | 95 |
| 8行目 | 渡辺 | 83 |
| 9行目 | 中村 | 76 |
| 10行目 | 小林 | 88 |
| 11行目 | 加藤 | 60 |
75パーセンタイルを求める
上位25%のラインを知りたいときは、第2引数に0.75を指定します。
=PERCENTILE.EXC(B2:B11, 0.75)結果は 88.75 です。得点88.75以上なら上位25%に入るということがわかります。
いろいろなパーセンタイルを求める
第2引数を変えて、さまざまな位置の値を取得してみましょう。
| 数式 | パーセンタイル | 結果 | 意味 |
|---|---|---|---|
=PERCENTILE.EXC(B2:B11, 0.25) | 25 | 71 | 下位25%のライン |
=PERCENTILE.EXC(B2:B11, 0.5) | 50 | 80.5 | 中央値 |
=PERCENTILE.EXC(B2:B11, 0.75) | 75 | 88.75 | 上位25%のライン |
データが10個の場合、0.09〜0.91の範囲で指定できます。0.9を指定すると94.6になります。
PERCENTILE関数と結果を比較してみよう
同じデータ・同じパーセンタイルで、PERCENTILE関数と比べてみましょう。
| パーセンタイル | PERCENTILE（INC） | PERCENTILE.EXC | 差 |
|---|---|---|---|
| 25% | 73 | 71 | 2 |
| 50%（中央値） | 80.5 | 80.5 | 0 |
| 75% | 87.25 | 88.75 | 1.5 |
50%（中央値）は同じ結果ですが、25%と75%では値が異なります。PERCENTILE.EXC関数のほうが端に寄った値を返す傾向があります。
PERCENTILE.EXC関数の実践的な使い方・応用例
成績評価のボーダーライン設定
PERCENTILE.EXC関数を使って、統計的に厳密なボーダーラインを設定できます。IFS関数と組み合わせてA〜Dの評価を振り分けましょう。
=IFS(B2>=PERCENTILE.EXC($B$2:$B$11,0.75), "A",
B2>=PERCENTILE.EXC($B$2:$B$11,0.5), "B",
B2>=PERCENTILE.EXC($B$2:$B$11,0.25), "C",
TRUE, "D")結果は以下のようになります。
| 名前 | 得点 | 評価 |
|---|---|---|
| 田中 | 72 | C |
| 鈴木 | 85 | B |
| 佐藤 | 91 | A |
| 山田 | 68 | D |
| 高橋 | 78 | C |
| 伊藤 | 95 | A |
| 渡辺 | 83 | B |
| 中村 | 76 | C |
| 小林 | 88 | B |
| 加藤 | 60 | D |
範囲を絶対参照（$B$2:$B$11）にするのがポイントです。数式を下にコピーしても範囲がずれません。
品質管理での外れ値検出
製品の品質管理では、正常範囲から外れたデータを検出する必要があります。PERCENTILE.EXC関数を使ったIQR法で外れ値を見つけましょう。
まず、第1四分位数（Q1）と第3四分位数（Q3）を求めます。
=PERCENTILE.EXC(B2:B11, 0.25) → Q1（第1四分位数）
=PERCENTILE.EXC(B2:B11, 0.75) → Q3（第3四分位数）次に、IQR（四分位範囲 = Q3 – Q1）を計算します。
=PERCENTILE.EXC(B2:B11, 0.75) - PERCENTILE.EXC(B2:B11, 0.25)外れ値の判定式は以下のとおりです。
=IF(OR(
B2 > PERCENTILE.EXC($B$2:$B$11,0.75) + 1.5*(PERCENTILE.EXC($B$2:$B$11,0.75)-PERCENTILE.EXC($B$2:$B$11,0.25)),
B2 < PERCENTILE.EXC($B$2:$B$11,0.25) - 1.5*(PERCENTILE.EXC($B$2:$B$11,0.75)-PERCENTILE.EXC($B$2:$B$11,0.25))),
"外れ値", "")数式が長く見えますが、やっていることはシンプルです。Q1とQ3の差（IQR）の1.5倍を超えたデータに「外れ値」と表示しています。
PERCENTILE.EXC関数を使うと、端の値に影響されにくいため、より安定した外れ値検出ができますよ。
条件付き書式で上位・下位を色分けする
PERCENTILE.EXC関数を条件付き書式と組み合わせると、データの上位・下位を視覚的に把握できます。
- B2:B11を選択する
- 「表示形式」→「条件付き書式」を開く
- 「カスタム数式」を選び、以下の数式を入力する
上位25%のセルに色を付ける場合はこちらです。
=B2>=PERCENTILE.EXC($B$2:$B$11, 0.75)下位25%のセルに別の色を付ける場合はこちらです。
=B2<=PERCENTILE.EXC($B$2:$B$11, 0.25)これで上位と下位のデータが一目でわかるようになります。
よくあるエラーと対処法
#NUM!エラー
PERCENTILE.EXC関数で最もよく見るエラーです。以下の原因が考えられます。
| 原因 | 対策 |
|---|---|
| 第2引数が0以下または1以上 | 0より大きく1より小さい値を指定する |
| 第2引数がデータ数に対して範囲外 | 1/(n+1)〜n/(n+1)の範囲内に収める |
| データ範囲が空（数値なし） | 数値が入った範囲を指定する |
よくある間違いは、第2引数に「0」や「1」を指定するケースです。PERCENTILE.EXC関数では0と1は使えません。最小値を求めたい場合はMIN関数、最大値はMAX関数を使ってください。
また、データ数が少ないと指定できる範囲が狭くなります。データが5個なら1/6（約0.17）〜5/6（約0.83）の範囲しか指定できません。0.9を指定したいのにエラーが出る場合は、データ数を確認してみましょう。
#VALUE!エラー
第2引数に文字列を指定すると発生します。数値を直接入力するか、数値が入ったセルを参照してください。
TIP
セル参照で第2引数を指定する場合は、参照先に数値が入っているか確認しましょう。空欄や文字列が入っていると#VALUE!エラーの原因になります。
文字列・論理値の扱い
データ範囲に含まれる文字列やTRUE/FALSEは自動的に無視されます。エラーにはなりませんが、データ件数が想定より少なくなることがあります。有効なデータ数が減ると、第2引数の有効範囲も変わる点に注意してください。
PERCENTILE関数・PERCENTILE.INC関数との違い
PERCENTILE.EXC関数を正しく使い分けるために、PERCENTILE関数・PERCENTILE.INC関数との違いを整理しましょう。
3つの関数の比較表
| 項目 | PERCENTILE | PERCENTILE.INC | PERCENTILE.EXC |
|---|---|---|---|
| 第2引数の範囲 | 0〜1 | 0〜1 | 0より大きく1より小さい |
| 0と1の扱い | 含む（包含的） | 含む（包含的） | 除外（排他的） |
| 最小値の取得 | 可（0を指定） | 可（0を指定） | 不可 |
| 最大値の取得 | 可（1を指定） | 可（1を指定） | 不可 |
| 計算方法 | 包含的補間 | 包含的補間 | 排他的補間 |
| PERCENTILEとの関係 | ― | 同一の動作 | 異なる結果 |
PERCENTILE関数とPERCENTILE.INC関数は完全に同じ動作をします。INCは「Inclusive（包含的）」の略で、PERCENTILEの正式名称という位置づけです。
どちらを使えばいい？使い分けの基準
| 場面 | おすすめ関数 | 理由 |
|---|---|---|
| 通常の業務（売上分析・ランク分け） | PERCENTILE関数 | シンプルで直感的。0と1が使えるので便利 |
| 統計的に厳密な分析 | PERCENTILE.EXC | 学術・統計の標準的な計算方式に準拠 |
| データ数が少ない（10個未満） | PERCENTILE関数 | EXCは指定範囲が狭くなるため使いにくい |
| データ数が多い（30個以上） | どちらでも可 | データが多いほど両者の差は小さくなる |
通常の業務ではPERCENTILE関数で問題ありません。統計的に厳密な結果が必要な場合や、学術的なレポートを作成する場合にPERCENTILE.EXC関数を使いましょう。
まとめ
PERCENTILE.EXC関数は、0と1を除外した排他的パーセンタイル値を返す関数です。
この記事のポイント
- 構文は
=PERCENTILE.EXC(データ, パーセンタイル)の2引数 - 第2引数は0より大きく1より小さい値（有効範囲: 1/(n+1)〜n/(n+1)）
- PERCENTILE関数 = PERCENTILE.INC関数（包含的）とは計算結果が異なる
- 通常の業務にはPERCENTILE関数、統計的に厳密な分析にはPERCENTILE.EXCを使う
- データ数が多いほど両者の差は小さくなる
次のステップ：関連する統計関数
PERCENTILE.EXC関数の使い方がわかったら、以下の関数もあわせて覚えてみてください。データ分析の幅が広がりますよ。
