データの分布を把握するとき、平均値だけでは全体像が見えにくいことがあります。たとえばテストの点数を分析する場合、上位25%のラインや中央値がわかれば、データの偏りを素早く把握できます。GoogleスプレッドシートのPERCENTILE関数を使えば、指定したパーセンタイル値を簡単に計算できます。この記事ではPERCENTILE関数の構文と具体的な使い方を、サンプルデータを使いながら解説します。
【要点】PERCENTILE関数でパーセンタイル値をすばやく求める方法
- =PERCENTILE(データ範囲, パーセンタイル): 基本構文で、第1引数に数値データのセル範囲、第2引数に0〜1のパーセンタイル値を指定します。
- パーセンタイル値は0〜1の小数で指定: 中央値なら0.5、第1四分位数なら0.25、第3四分位数なら0.75と入力します。
- データに空白や文字列があるとエラー: PERCENTILE関数は数値のみを対象とします。空白セルは無視されますが、文字列があるとエラーになります。
- PERCENTILE.INCとPERCENTILE.EXCの違い: 標準のPERCENTILE関数はINC(包含型)と同じ動作です。EXCは両端を除外して計算するため、結果が微妙に異なります。
ADVERTISEMENT
PERCENTILE関数の概要と仕組み
PERCENTILE関数は、指定したパーセンタイルの値を返す統計関数です。パーセンタイルとは、データを小さい順に並べたときに、全体の何%の位置に当たるかを示す指標です。たとえば第1四分位数(25パーセンタイル)はデータの下位25%の境界値を表します。中央値(50パーセンタイル)はデータを半分に分ける値で、平均値よりも外れ値の影響を受けにくいという特徴があります。
PERCENTILE関数の構文は =PERCENTILE(データ, パーセンタイル) です。第1引数「データ」には数値が含まれるセル範囲を指定します。第2引数「パーセンタイル」には0以上1以下の小数を指定します。0を指定すると最小値、1を指定すると最大値が返ります。0.5を指定すれば中央値です。なお、Googleスプレッドシートでは、標準のPERCENTILE関数はPERCENTILE.INC(包含型)と同じ計算方法を採用しています。PERCENTILE.EXC(排他型)は0と1を指定できず、データ数によって結果が変わります。
PERCENTILE関数の具体的な使い方
ここでは、テストの点数データを使って各パーセンタイル値を計算する手順を説明します。サンプルデータとして、A1:A20に20名の点数が入力されていると仮定します。
基本的なPERCENTILE関数の入力手順
- 結果を表示するセルを選択する
パーセンタイル値を表示したいセル(例:B1)をクリックします。 - PERCENTILE関数を入力する
=PERCENTILE(A1:A20, 0.5) と入力し、Enterキーを押します。これで中央値が計算されます。 - 他のパーセンタイルも同様に計算する
第1四分位数(0.25)や第3四分位数(0.75)も同様に、=PERCENTILE(A1:A20, 0.25)、=PERCENTILE(A1:A20, 0.75) と入力します。
パーセンタイル値を一覧で表示する方法
- パーセンタイル値のリストを作成する
D列に0.1、0.25、0.5、0.75、0.9などのパーセンタイル値を入力します。 - ARRAYFORMULAと組み合わせて一括計算する
E1セルに =ARRAYFORMULA(PERCENTILE(A1:A20, D1:D5)) と入力します。これでD列の各パーセンタイルに対応する値が一気に表示されます。範囲がずれないように注意してください。
PERCENTILE関数の注意点とよくあるエラー
パーセンタイル値が0〜1の範囲を超えている場合
PERCENTILE関数の第2引数に0未満または1より大きい値を指定すると、#NUM!エラーが発生します。必ず0以上1以下の小数を指定してください。0.5など、小数で正しく入力しましょう。
データ範囲に文字列やエラー値が含まれている場合
PERCENTILE関数は数値以外のセルを無視できません。文字列やTRUE/FALSEの論理値、エラー値が範囲内に1つでもあると、#VALUE!エラーになります。データ範囲を確認し、数値のみにしてください。空白セルは無視されるので問題ありません。
PERCENTILEとPERCENTILE.INC、PERCENTILE.EXCの違い
Googleスプレッドシートでは、PERCENTILE関数はPERCENTILE.INCと同じ結果を返します。INCは包含型(0と1を含む)で、データの最小値と最大値をそのまま返します。一方、PERCENTILE.EXCは排他型で、0と1を指定できず、データ数によってパーセンタイル値が補間されます。データ数が少ないときは結果が異なることがあります。通常はPERCENTILE.INC(またはPERCENTILE)を使用すれば問題ありません。
パーセンタイル値がデータの間にある場合の補間方法
指定したパーセンタイルがデータのちょうど位置にない場合、PERCENTILE関数は線形補間を行います。たとえばデータが5個で25パーセンタイルを計算するとき、2番目と3番目のデータの間の値を比例配分して算出します。この補間方法はExcelと同じです。
ADVERTISEMENT
PERCENTILE関数と類似関数の比較
| 関数名 | 戻り値 | 主な用途 |
|---|---|---|
| PERCENTILE(PERCENTILE.INC) | 指定したパーセンタイルの値(包含型) | データの分布を把握する標準的なパーセンタイル計算 |
| PERCENTILE.EXC | 指定したパーセンタイルの値(排他型、0と1は不可) | 統計学的に厳密なパーセンタイルが必要な場合 |
| PERCENTRANK | データ内での相対的な順位(パーセント) | ある値が全体の中でどの位置にあるかを調べる |
| QUARTILE | 四分位数(0=最小値、1=第1四分位数、2=中央値、3=第3四分位数、4=最大値) | 四分位数を簡潔に取得したい場合 |
まとめ
PERCENTILE関数を使うと、データの分布を様々な視点から分析できます。中央値や四分位数を求めることで、平均値だけでは見えないデータの偏りを把握できるようになります。PERCENTILE関数の基本を覚えたら、PERCENTRANKやQUARTILE関数と組み合わせてより詳細な分析に挑戦してみてください。また、FILTER関数やQUERY関数と組み合わせて条件付きパーセンタイルを計算することも可能です。ぜひ実際のデータで試しながら、関数の使い方を身につけてください。
ADVERTISEMENT
超解決 第一編集部
疑問解決ポータル「超解決」の編集チーム。正確な検証と、現場視点での伝わりやすい解説を心がけています。
Googleスプレッドシートの人気記事ランキング
- 【Googleスプレッドシート】GOOGLEFINANCE関数で株価・為替を取得!リアルタイムデータの呼び出し
- 【Googleスプレッドシート】印刷範囲を指定して印刷!特定範囲だけPDFや紙に出す手順
- 【Googleスプレッドシート】複合グラフで棒と折れ線を同時表示!2軸グラフの作り方
- 【Googleスプレッドシート】FILTER関数で条件に合う行だけ抽出!数式1つで動的な絞り込み
- 【Googleスプレッドシート】フィルタ表示で他の人の表示を変えずに絞り込み!共有時の使い分け
- 【Googleスプレッドシート】重複データを抽出して表示!COUNTIFと条件付き書式の併用
- 【Googleスプレッドシート】新しいスプレッドシートを作成する3つの方法!ドライブ・URL・テンプレート
- 【Googleスプレッドシート】ARRAYFORMULAで列全体に数式を一括適用!セル分の入力を不要に
- 【Googleスプレッドシート】データ範囲を動的に伸縮させる!INDIRECTと参照範囲のコツ
- 【Googleスプレッドシート】共有解除と権限の取り消し!アクセスを完全に閉じる手順