更新日：、 作成日：

エクセル PERCENTILE 関数：順位比率に対応する値を取得する

はじめに

エクセルの PERCENTILE 関数の使い方を紹介します。

PERCENTILE 関数は指定した順位比率に対応する値を取得します。

=PERCENTILE(A1:A3,0.2) のようにして、小さい順で上位 20 % のしきい値を取得できます。

=PERCENTILE(A1:A3,0.8) のようにして、小さい順で上位 80 % のしきい値を取得できます。

PERCENTILE.INC 関数と PERCENTILE.EXC 関数の違いも紹介します。

順位比率を取得するには「PERCENTRANK 関数」を使用します。
順位の値を取得するには「SMALL 関数」または「LARGE 関数」を使用します。

PERCENTILE 関数の引数

PERCENTILE(範囲, 順位比率)
範囲の中から順位比率の値を取得します。

引数「範囲」セルの範囲、数値を指定できます。スピル化
引数「順位比率」引数「範囲」の中から取得する順位を比率で指定します。スピル化

スピル化：セルの範囲や配列を指定すると結果が「スピル」します。

使い方

PERCENTILE 関数の使い方を紹介します。

上位 20 % や下位 20 % の値を取得する

セルの範囲「B4」～「B10」の上位 20 % や下位 20 % の値を取得します。順位比率 0.2 が小さい順で上位 20 % の値で、0.8 が上位 80 % (下位 20 %) の値になります。

=PERCENTILE.INC($B$4:$B$10,C3)
6

値の順位比率からその値を取得する

値を「PERCENTRANK 関数」で求めた順位比率から元の値を取得します。

=PERCENTILE.INC($B$5:$B$11,C3)
=PERCENTRANK.INC($B$5:$B$11,B4)

順位比率の有効桁数が少ないため、同じ値が取得されません。

7

順位比率の有効桁数を指定してその値を取得する

15 の順位比率を「PERCENTRANK 関数」で有効桁数を 1 ～ 9 の間で求めて元の値を取得します。

=PERCENTILE.INC($B$5:$B$11,D3)
=PERCENTRANK.INC($B$5:$B$11,15,C4)

同じ値を取得するのに少なくとも有効桁数が 7 必要なことがわかります。

8

非表示のセルを無視して取得する

非表示のセルを無視して値を取得するには「AGGREGATE 関数」を使用します。

解説

  • PERCENTILE 関数：順位比率に対応する値を取得します。順位比率 → 値
  • PERCENTRANK 関数：値の順位比率 (上位何パーセントか) を取得します。値 → 順位比率

引数「範囲」の値の順位比率は「PERCENTRANK 関数」で取得できます。

引数「範囲」の値が昇順や降順に並んでいる必要はありません。

引数「範囲」が日付なら、日付の実際の値は数値なのでその値として扱います。

引数「範囲」に文字列や TRUE や FALSE、空のセルが含まれるときは、そのセルを無視します。

引数「範囲」に 1 つも有効な数値が含まれていないときはエラー #NUM! になります。

引数「順位比率」が 0 なら、引数「範囲」の中の最小値を取得します。

引数「順位比率」が 0.5 なら、引数「範囲」の中の中央値を取得します。

引数「順位比率」が 1 なら、引数「範囲」の中の最大値を取得します。

引数「順位比率」が 0 より小さいまたは 1 より大きい値なら、エラー #NUM! になります。

順位の順位比率

引数「範囲」を昇順で順位を付けたとき、その順位の値の順位比率は 1/(引数「範囲」の値の数-1)*順位 で取得できます。このとき一番小さい値の順位が 0 から開始します。これは「PERCENTRANK 関数」でその値の順位比率を取得したときと同じ値です。

引数「順位比率」が 1/(引数「範囲」の値の数-1) の倍数なら、引数「範囲」の中にあるその順位の値が取得できます。

倍数でないなら、その前後の倍数の値に対しての比率で求めます。順位 1 が 10 で 2 が 20 のとき、順位 1.2 の順位比率で求められる値は、前の順位 1 を 0 % 10 とし、後の順位 2 を 100 % 20 としたときの、20 % 12 の値になります。

9

同率順位

同じ数値が複数あるときは、その順位比率でも次の順位比率でも値を取得できます。次のように順位 1 位が複数あるときは、1 位の比率でも 2 位の比率でも取得できます。

10

PERCENTILE.INC と PERCENTILE.EXC の違い

PERCENTILE.INC 関数と PERCENTILE.EXC 関数の違いは順位比率の扱い方にあります。

  • PERCENTILE 関数、PERCENTILE.INC 関数：まったく同じ結果を返します。引数「順位比率」は「PERCENTRANK.INC 関数」で取得できます。求め方は 1/(引数「範囲」の値の数-1)*順位 のようになっています。PERCENTILE 関数は互換性のために残されている関数なので、将来のことを考えるなら PERCENTILE.INC 関数を使用します。
  • PERCENTILE.EXC 関数：引数「順位比率」は「PERCENTRANK.EXC 関数」で取得できます。求め方は 順位/(引数「範囲」の値の数+1) のようになっています。引数「順位比率」に 0 % と 100 % とそれと同じ結果を返す値を指定したときは、エラー #NUM! になります。

著者：Tipsfound