【Excel】度数分布表から99パーセンタイル値を求める方法メモ

実は「パーセンタイル」の定義には「Nearest rank」やら「Lenear interpolation between closest ranks」やら「Weighted percentile」やらいろいろあるようで、Excelですら、Excel 2007 互換の PERCENTILE関数 が PERCENTILE.ENC関数 と PERCENTILE.INC関数 に再編されるくらいややこしいものです。

詳しい説明はWikipediaのPercentileを読んで欲しいのですが、そこにも「There is no standard definition of percentile」と書かれています。

例えば Apache Commons の Percentile クラスでは、次のように定義されています。

1. Let n be the length of the (sorted) array and 0 < p <= 100 be the desired percentile.
2. If n = 1 return the unique array element (regardless of the value of p); otherwise
3. Compute the estimated percentile position pos = p * (n + 1) / 100 and the difference, d between pos and floor(pos) (i.e. the fractional part of pos)
4. If pos < 1 return the smallest element in the array.
5. Else if pos >= n return the largest element in the array.
6. Else let lower be the element in position floor(pos) in the array and let upper be the next element in the array. Return lower + d * (upper - lower)

（引用元）

今回はこの定義を採用したいと思います（実は、Excel2010から新しく追加されたPERCENTILE.EXCとほぼ同じ）。この定義を採用した場合についての定義は、この記事の後半の「Apache CommonsのPercentileと同じ事をする」の項目で説明しています。

PERCENTILE関数（PERCENTILE.EXC関数、PERCENTILE.INC関数）は、生のデータから、○○パーセンタイルを求めるための関数です。

ですから、1000件のデータからPERCENTILE関数を使ってパーセンタイルを求められるのは、あくまで1000個のセルに各実測データが入っている場合です。

すでに度数分布を求めてある状態からパーセンタイルを求めようとしてPERCENTILE系関数を使うと、変な値になってしまいます（集計値が実測データ扱いされてしまう）。

同様に、PERCENTRANK.EXCやPERCENTRANK.INCも違います。

最初は初歩的な99パーセンタイルを目指したいと思います。

それは、「データ全体の99%がこの値以下に収まるという値を、度数分布表に書かれている値から見つけてそれを99パーセンタイルとする」という定義です。補完しません。

つまり、950人（99%は940.5人目）が受けた、10点満点のテストで、99パーセンタイルを「941人目（小数点以下四捨五入）」とする方式（nearest rank法もどき）です。940人目と941人目の値が違って940.5人目がきわどい時であっても、値の間は補間せず、とりあえず大きい方を採用する、というわけです。

7点以下の人が909人、8点以下の人が971人、9点以下の人が993人いるとすると、この場合の99パーセンタイルは、941人目の8点となります。

計算方法

この場合は、図のようにまず度数分布表の横に、「○○点までの累計」を計算します。これ自体は「=SUM($B$2:$B2)」をオートフィルして合計すればOKです。

ここから、累計値の値の中から、941を最初に超える点数を検索します。

ポイント１：四捨五入する

とりあえず何番目の人のデータを取り出せばいいかを計算します。その値は「データ数に0.99をかけ算して、四捨五入したもの(例での941)」なので、ROUND関数を使って「ROUND([データ数]*0.99, 0)」と計算できます。

ポイント２：VLOOKUPは左端の列しか検索キーにできない

ここでVLOOKUPを使って、累計値を検索して対応する点数を取り出したいのですが、検索に使う累計値が取り出す値（点数）より右にあるとすると、VLOOKUPでは扱えません。もちろん、累計値の右に点数の列をもう一度書けば良いのですが、今回は「INDEX関数」と「MATCH関数」で回避します。

仕組みとしては、まずMATCH関数で、累計値列の上から、941を最初に超える数字がある場所を検索し、その列の何番目にあったかという番号を取得します。INDEX関数では、範囲の「なんとか番目」にあるものを取り出すことができるので、INDEX関数に点数の列を指定して、MATCHで計算した何番目にあるかという値を渡せば、VLOOKUPと同じ事を右の列を検索対象とした場合で実行できます（MATCHは昇順に並んでいないと検索に失敗しますが、累計値は当然昇順なので大丈夫です）。

ポイント３：MATCHの検索を補正する

MATCHで[検索の種類]にTRUEを付けた状態（デフォルトなので省略可）で数値を検索すると、「検索値以下の最大の値（の位置）」を見つけ出してしまいます。今回は、「検索値を超える最小の値（の位置）」を見つけたいので、ちょっと違います。これを補正するために、MATCH関数を「MATCH(ROUND([データ数]*0.99-1,0),[累計])+1」のように補正して使います。

結果

結果、パーセンタイルを求める式は、次のような式になります。

SUM(B2:B12）は、単純に度数分布表からデータの総数を計算しているだけです。また、セルの絶対参照相対参照は意識せず記述しています。意味で書き直すとこうなります。

ところで、そもそも累計値を計算してセルに書き込まない方法はどうすればいいんでしょう。。。できれば余計なセルを使いたくないのですが、今回はここ止まりでした。

次に、より一般的な（普通の）パーセンタイルの計算方法を使ってみます（詳細は前述のApache Commons MathのPercentileの定義参照）。

これには重要な点が２つあります。

pパーセントの指し示す位置

まず一つ目は、パーセントの使い方です。100個データがある時、なんとなく0%は１番目で、100%は100番目だと思いがちですが、これは勘違いです（これを採用していたのがExcel 2007互換のPERCENTILE関数と、それと同じ動作をするPERCENTILE.INC）。

これは、pパーセントの指し示す位置(pos)を1始まりの「1+(p/100)*(データの個数)」と計算しているのですが、こうではなく、普通は0始まりの「0+(p/100)*(データの個数+1)」にするのです。つまり、0%があたかも0番目に位置し、100%があたかも100番目にあるかのような位置決めを行うのです。したがって、例えば1000件のデータがある時の90%が、900人目を指す、というわけではなく、少しずれることになるのです。

このようにして計算した%の指し示す位置(pos)が1未満なら「１番目」、データの個数以上なら「(データの個数）番目」の値がpパーセンタイルの値となります。そして、それ以外の大部分については、次のように線形補完しつつ計算します。

補完方法

位置（pos）が決まったら、その前後の値を線形補完します。

たとえば、posが7.2なら、7番目の値（v[7]）と8番目の値（v[8]）を持ってきて（1始めの配列表記であることに注意）、v[7]とv[8]の値を「(1-0.2)*v[7]+0.2*v[8]」次のように内分します（もしかすると、「v[7]+(v[8]-v[7])*0.2」のほうが分かりやすい人もいるかも知れません）。

つまり一般的に書けば、posを整数部分iと、小数部分fに分けて、パーセンタイルを「(1-f)*v[i]+f*(v[i+1]-v[i])」として計算するわけです。

※考え方のポイント：v[i]とv[i+1]が等しい場合は、そのままv[i]（v[i+1]でも同じ）の値がパーセンタイルの結果になり、v[i]とv[i+1]の値が違う時のみ、線形補間の効果で中途半端な値になる。

計算方法

さて、先ほどの方法でExcelを使って計算したパーセンタイルは、だいぶ近いことをしています。特に、度数分布表から特定の順位kの値(v[k])を取り出す方法はそのまま利用できそうです。

累計から特定の順位kを取り出す方法

しかし、より正確には一工夫必要です。

度数分布表から特定の順位kの値（v[k]）を取り出すには、「INDEX([点数], MATCH(k-1, [累計])+1)」とすれば大方の場合問題ありません。しかし、k番目のデータが一番下の階級に属している場合、エラー（#N/A）になってしまいます。これは、検索値以下の値が見つからないからです（MATCH関数の仕様によりエラーになる）。

しかし、一番下を認識させようとして「MATCH(k-1,[累計])+1」の代わりに「MATCH(MAX(k-1,INDEX([累計],1)),[累計])+1」のように詰め込もうとしてしまうと、一見正しく動くようで、最後の+1が余計となってしまったり、累計の一番下の値と下から２番目の値が同じとき、おかしくなってしまいます。

というわけで、素直に場合分けをして、「INDEX(A2:A12,IF(k<=MIN(K2:K12),1,MATCH(k-1,K2:K12)+1))」でk番目を取り出すことができます。

※累計の一番下を0にする（0を追加する）手もありますが。。。

ポイント１：posを再計算する

とりあえず「pos」の計算方法を直します。先ほどは「データ数*0.99」をMATCHに渡していましたが、今回は「(データ数+1)*0.99」を渡すために、「pos=SUM(B2:B12)*0.99」を「pos=(SUM(B2:B12)+1)*0.99」に変更します。

ポイント２：posの整数部分iと小数部分fを計算する

次に、その値を元に整数部分と小数部分を計算します。整数部分と聞くとFLOORやROUNDDOWNも思い浮かびそうですが、ここではINT関数で十分です。小数部分は、シンプルに値から整数部分を引き算する方法を採用してもいいのですが、長い値を２回書くのが嫌なので、MOD関数を使って１で割ったあまりをとる方法を使います。

つまり、i=INT((SUM(B2:B12)+1)*0.99)、f=MOD((SUM(B2:B12)+1)*0.99,1)となります。

したがって、次の説で紹介する例外処理がなければ「=(1-MOD((SUM(B2:B12)+1)*0.99,1))*INDEX(A2:A12,IF(INT((SUM(B2:B12)+1)*0.99)<=MIN(C2:C12),1,MATCH(INT((SUM(B2:B12)+1)*0.99)-1,C2:C12)+1))+MOD((SUM(B2:B12)+1)*0.99,1)*INDEX(A2:A12,IF(INT((SUM(B2:B12)+1)*0.99)+1<=MIN(C2:C12),1,MATCH(INT((SUM(B2:B12)+1)*0.99)+1-1,C2:C12)+1))」となります。長いのですが、適宜まとめればそれなりに短くなるかと。結果より、この式を得るまでの過程が大事です。

ポイント３：例外処理

posが1未満やデータ数以上の場合は、それぞれ一番小さい値と一番多い値を返す必要があります。

この例外処理を行うために、posの値に応じて、IF関数を使って分岐します。例えばこんな具合です「IF(pos < 1, [最小値], IF(pos >= [データ数], [最大値], [例外でない計算]))」

ここでさりげなく問題となるのが、[最小値]と[最大値]ですが、これは要するにv[1]とv[（データ数）]なので、先ほどと同じ方法で計算できます。

最小値は「INDEX(A2:A12,IF(1<=MIN(K2:K12),1,MATCH(0,K2:K12)+1))」で、最大値が「INDEX(A2:A12,IF(SUM(B2:B12)<=MIN(K2:K12),1,MATCH(SUM(B2:B12)-1,K2:K12)+1))」です。

結果

これらを総合すると、次のようなかなり長めの数式になってしまいます。実用的な意味では、共通部分をセルに書き込んだりして簡略化したり、実用上のパラメータである0.99もセルに移したりするなどして、工夫してください。

ここまで99%と書きつつも、99%に限らない場合にも対応できるように考えてきました。もし99%と固定してしまうのなら、最小値側の例外になることはあり得ないので、一番外側のIF文は外すことができます。

あまりちゃんと動作チェックしていないので、ヒントだと思って参考にしてください。実際にちゃんとしたところで使う際には、しっかり確認してください。