excel PERCENTILE 関数：順位の値を比率で指定して取得する
はじめに
エクセルの PERCENTILE.INC と PERCENTILE.EXC 関数の使い方を紹介します。
PERCENTILE 関数はデータの中から順位を比率で指定してその値を取得します。
上位や下位何パーセントの値を取得したいときに使用します。関数名の違いも紹介します。
順位比率を取得するには「PERCENTRANK 関数」を使用します。
順位の値を取得するには「SMALL 関数」または「LARGE 関数」を使用します。
excel構文
PERCENTILE(範囲, 順位比率)
範囲の中から順位比率の値を取得します。
引数「範囲」 セルの範囲、数値を指定できます。
引数「順位比率」 引数「範囲」の中から取得する順位を比率で指定します。
excel使用例
セルの範囲「B4」～「B10」で上位 20 % や下位 20 % の値を取得する。順位比率 0.2 が下位 20 % の値で、0.8 が上位 20 % の値になります。
=PERCENTILE.INC($B$4:$B$10,D3)
"excel2021507-404-1"
セルの範囲「B4」～「B10」でセル「D3」の順位比率の値を取得する。
=PERCENTILE.INC($B$4:$B$10,D3)
順位比率は次の数式で求めています。
=1/(COUNT($B$4:$B$10)-1)*C3
"excel2021507-404-2"
セルの範囲「B4」～「B10」でセル「D3」の小数を含む順位の順位比率の値を取得する。
=PERCENTILE.INC($B$4:$B$10,D3)
順位比率は次の数式で求めています。
=1/(COUNT($B$4:$B$10)-1)*C3
"excel2021507-404-3"
解説
引数「範囲」の値は昇順や降順に並んでいる必要はありません。
引数「範囲」に空のセルや数値以外の値を指定したときは、そのセルを無視して範囲に含まれていないように処理します。
引数「範囲」に 1 つも有効な値が含まれていないときは、エラー#NUM!になります。
引数「順位比率」は「PERCENTRANK 関数」で求められます。詳しくはそちらをご覧ください。簡単に説明すると、指定した順位の順位比率は1/(引数「範囲」の値の数-1)*順位で求められます。このとき一番小さい値の順位が 0 から開始します。
引数「順位比率」が1/(引数「範囲」の値の数-1)の倍数なら、引数「範囲」の中にあるその順位の値が取得されます。
倍数でないなら、その前後の倍数の値に対しての比率で求めます。順位 1 が10で 2 が20のとき、順位 1.2 の順位比率で求められる値は、前の順位 1 を 0 %10とし、後の順位 2 を 100 %20としたときの、20 %12の値になります。
"excel2021507-404-4"
引数「順位比率」が 0 なら、引数「範囲」の中の最小値を取得します。
引数「順位比率」が 0.5 なら、引数「範囲」の中の中央値を取得します。
引数「順位比率」が 1 なら、引数「範囲」の中の最大値を取得します。
引数「順位比率」が 0 より小さいまたは 1 より大きい値なら、エラー#NUM!になります。
同率順位の扱い
引数「範囲」に同じ値が複数あるときは、その順位比率でも次の順位比率でも値を取得できます。次のように順位 1 位が複数あるときは、1 位の比率でも 2 位の比率でも取得できます。
"excel2021507-404-5"
PERCENTILE.INC と PERCENTILE.EXC の違い
「PERCENTILE.INC 関数」と「PERCENTILE.EXC 関数」の違いは順位比率の扱い方にあります。
「PERCENTILE.INC 関数」：引数「順位比率」は「PERCENTRANK.INC 関数」で求められます。求め方は1/(引数「範囲」の値の数-1)*順位のようになっています。「PERCENTILE 関数」と全く同じ結果を返します。詳細は上記の通りです。
「PERCENTILE.EXC 関数」：引数「順位比率」は「PERCENTRANK.EXC 関数」で求められます。求め方は順位/(引数「範囲」の値の数+1)のようになっています。引数「順位比率」に 0 % と 100 % とそれと同じ結果を返す値を指定したときは、エラー#NUM!になります。
「PERCENTILE 関数」は古いため、使用するなら「PERCENTILE.INC 関数」を使います。