Excel。数値の位置を百分率で算出するのがPERCENTRANK.INC関数です【PERCENTRANK.INC】

Excel。数値の位置を百分率で算出するのがPERCENTRANK.INC関数です

＜関数辞典：PERCENTRANK.INC関数＞

PERCENTRANK.INC関数

読み方： パーセントランク・インクルーシブ　　

読み方： パーセントランク・インクルード

分類： 統計 

PERCENTRANK.INC(配列,x,[有効桁数])

数値の位置を百分率で算出します

Excel関数辞典 VOL.59。PERCENTRANK関数～PERCENTRANK.INC関数【dictionary】

Excel関数辞典　VOL.59。PERCENTRANK関数～PERCENTRANK.INC関数

＜Excel関数＞

今回は、PERCENTRANK関数～PERCENTRANK.INC関数までをご紹介しております。

PERCENTRANK関数

パーセントランク

PERCENTRANK(配列,x,[有効桁数])

数値の位置を百分率で算出します

PERCENTRANK.EXC関数

パーセントランク・エクスクルーシブ

パーセントランク・エクスクルード

PERCENTRANK.EXC(配列,x,[有効桁数])

数値がどの位置に相当するのかを百分率で算出します

PERCENTRANK.INC関数

パーセントランク・インクルーシブ

パーセントランク・インクルード

PERCENTRANK.INC(配列,x,[有効桁数])

数値の位置を百分率で算出します

Excel。通知書など5段階相対評価を算出するにはどうしたらいいの？【Relative evaluation】

Excel。通知書など5段階相対評価を算出するにはどうしたらいいの？

＜XLOOKUP+PERCENTRANK.INC関数＞

資料を作成するにあたり、相対的に評価をつけないといけないことがあるわけです。

例えば、先生ご用達というか、「通知書」などの相対評価表です。

では、どうやったら、楽に相対評価を算出することができるのでしょうか？

次の表のD列の評価フィールドを算出していきます。

準備するのは、F1:H6の相対評価表です。

H2は、累計を算出するための数式が設定してあります。

H2の数式は、

=SUM($G$2:G2)

この数式を、H6までオートフィルで数式をコピーしています。

作業スペースをつくらないで、一発で算出するならば、XLOOKUP+ PERCENTRANK.INC関数で算出できるのですが、説明の為、数式を分解して説明していきます。

得点が全体でどの位置にあるのかという相対位置がわからないことには、相対評価を算出することはできません。

そこで登場するのが、PERCENTRANK.INC関数です。

PERCENTRANK.INC関数は、百分位を算出することできる関数です。

E列には、PERCENTRANK.INC関数をつかって百分位を算出しています。

E2の数式は、

=PERCENTRANK.INC($C$2:$C$11,C2,2)

最初の引数は、配列。

範囲選択なので、C2:C11。オートフィルで数式をコピーするので、絶対参照を設定していきます。

2つ目の引数は、C2。

これで、C2の値は、全体の中で、何パーセントの位置にあるのかがわかります。

最後の引数は、有効桁数。

小数点第何位まで表示するのかということですね。

この数式を、次のように変更する必要があります。

E2の数式を変更します。

=1-PERCENTRANK.INC($C$2:$C$11,C2,2)

なぜ、1から減算する必要があったのかというと、原因は、評価表にあります。

百分位は、100％の位置にあるデータは100%と算出されるのはいいのですが、Aの配分累計は10%の位置で、Eの配分累計が100%となっています。

ようするに「逆」になっているので、１から減算する必要があるわけです。

あとは、=1-PERCENTRANK.INC($C$2:$C$11,C2,2) で算出した値が、どの配分累計に属しているのかを算出すればいいわけです。

VLOOKUP関数をつかって評価を検索抽出するように思われますが、F1;H6の表。

一番左側の列に、検索抽出したい値があるわけです。

これだと、VLOOKUP関数は使えません。

INDEX+MATCH関数なんて方法もありますが、XLOOKUP関数が登場したわけですから、XLOOKUP関数をつかえば、一番左側に検索抽出の列があっても問題ありません。

D2に設定した数式は、

=XLOOKUP(E2,$H$2:$H$6,$F$2:$F$6,,1,-1)

引数の確認をしておきましょう。

検索値が、「E2」。

検索範囲が、「$H$2:$H$6」　この範囲は、検索したい範囲です。

戻り範囲が、「$F$2:$F$6」　抽出したい範囲です。

見つからない場合は、省略とします。

一致モードには「1」。これは、「完全合致または次に大きい項目」を選択します。

検索モードには、「-1」の「末尾から先頭へ検索」を選択しました。

本当ならば、スピル機能を考えて範囲選択するのですが、今回は、VLOOKUP関数っぽく使用しております。

二段階の計算式を一つにまとめたのが、次の数式です。

=XLOOKUP(1-PERCENTRANK.INC($C$2:$C$11,C2,2),$H$2:$H$6,$F$2:$F$6,,1,-1)

この数式を、D2に設定すれば相対評価を算出することができます。

見慣れない関数のネストで、長い数式になると、わかりにくくなりますので、二段階で算出するのでいいと思います。

ということで、Excelには様々な関数が用意されていますので、色々組み合わせみると、意外につかえるネストとか見つかるかもしれませんね。

Excel。事務職のデータ分析その22。百分位点を見つけるにはどうしたらいいの？

Excel。事務職のデータ分析その22。百分位点を見つけるにはどうしたらいいの？

＜PERCENTRANK.INC & PERCENTILE.INC & QUARTILE.INC関数＞

百分位のパーセンタイル順位を求めることは、
以前にも、PERCENTRANK.INC関数を使うことで、求めることが出来ました。

最初は、百分位をPERCENTRANK.INC関数を使って算出してみましょう。

【百分位：PERCENTRANK.INC関数】

次の表があります。

B列のデータは、わかりやすいように、降順にしてあります。

C2をクリックして、
PERCENTRANK.INC関数ダイアログボックスを表示しましょう。

配列には、$B$2:$B$16
xには、B2

それでは、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

表示形式をパーセントにして、小数点第1位まで表示しましょう。

C2の数式は、
=PERCENTRANK.INC($B$2:$B$16,B2)

このように簡単に算出することが出来ましたね。
最小値の順位を0%。最大値の順位を100%として、
それぞれの値の場所をパーセントで示したものですね。

ちなみに、Excel2010から、
PERCENTRANK.INC関数とPERCENTRANK.EXC関数にわかれました。

PERCENTRANK.INC関数は、百分位を0以上1以下で算出します。
PERCENTRANK.EXC関数は、百分位を0より大きく1未満で算出します。

このような違いがありますので、使い分けるといいですね。

では、百分位の0%は最小値なので、12。

100%は最大値である96というのは、わかるのですが、
75%や、中間値である50%や25%は、いったいデータの数値としては、
いくつなのでしょうか？

それを求めることが出来るのが、百分位点を算出することが出来る、
PERCENTILE.INC関数を使うことで求めることが出来ます。

【百分位点：PERCENTILE.INC関数】

F2をクリックして、PERCENTILE.INC関数ダイアログボックスを表示しましょう。

配列は、$B$2:$B$16
率は、E2の0%を設定します。
OKボタンをクリックして、数式をオートフィルでコピーしましょう。

F2の数式は、
=PERCENTILE.INC($B$2:$B$16,E2)

25%の場所は、48.5ということがわかりました。
確かに、データ49は28.5%でデータ48は21.4%なので、
その間なので、合致しているのがわかります。

さて今回のように、全体を4つに分割した場合は、
四分位点（クォータイル）ともいいます。

E8に四分位点を算出する表を作ってみましたので確認すると、
先ほど算出した百分位点と同じになっていますよね。

なお、四分位点（クォータイル）を算出する関数がありまして、
QUARTILE.INC関数を使います。

F9の数式を確認してみると、次のようになっています。
=QUARTILE.INC($B$2:$B$16,E9)

引数の「戻り値」に関して確認しておきましょう。

戻り値ですが、25%の1は、第1四分位のことを指します。

50%は第2四分位、
75%を第3四分位というように戻り値を使って算出する関数です。

なお、第2四分位である50%のことを、『中央値』というわけですね。