総得点が270点満点の試験での標準偏差と偏差値

総得点が270点満点の試験での標準偏差と偏差値の求め方がよく分かりません。

総得点270満点で、
・平均点：130点
・合格点：160点（これ以上で合格）
・合格点以上での上位占有率：21.56%

ということが明らかにされており、具体的な実データ（何点に何人いるか）もあります。
ただ、54点以下はまとめて何人、という省略があるデータです。
54点以降は適当にダミーを入れて人数調整しました。

これらを使いEXCELで計算してみました。

元データを加工しタテに得点を並べ、平均のAVERAGE()等ではそれを全選択しています。

225 ・・・215点の人が１人いる、という意味です。
219 ・・・219点の人が１人
218 ・・・218点の人が１人
216 ・・・216点の人が１人
215 ・・・215点の人が１人
214 ・・・214点の人が３人
214
214
212 ・・・212点の人が２人
212
211・・・211点の人が３人
211
211
：
19 ・・・19点の人が３人
19
19
16 ・・・16点の人が１人
13 ・・・13点の人が１人
5 ・・・・5点の人が１人

◆270点満点基準
元データそのままの270点満点基準で計算すると、
・平均点：129.94点 =AVERAGE(得点を全選択)
・合格点：160点
・標準偏差：33.27 =STDEVP(得点を全選択)
・合格点の偏差値：59.03 =(合格点 - 平均点)*10/標準偏差+50
・合格点以上の上位占有率：18.31% =1-NORMDIST(合格点, 平均点, 標準偏差, TRUE)

◆100点満点基準
得点をすべて100/270倍して、100点満点基準で計算すると、
・平均点：48.13点 =AVERAGE(得点を全選択)
・合格点：59.25点 =160*(100/270)
・標準偏差：12.32 =STDEVP(得点を全選択)
・合格点の偏差値：59.03 =(合格点 - 平均点)*10/標準偏差+50
・合格点以上の上位占有率：18.31% =1-NORMDIST(合格点, 平均点, 標準偏差, TRUE)

いずれにしても「合格点以上の上位占有率」が21.56%になりません。
54点以降は適当にダミーを入れて人数調整したとはいえ、こんなにも違う．．．？

データの散布図のキャプチャを貼りました。ご参照下さい。

何が間違っていて、どこをどうすれば正しく計算できるのでしょうか？
皆さん、よろしくお願い致します。

質問者からの補足コメント

• 採点対象者：10,665人
  合格点(160点)以上：2,299人
  合格点以上での上位占有率：21.556% (= 2299/10665*100)
  
  ということは元データで実際に数えて確認できています。
  160点以上での上位占有率21.556%を標準偏差を使って出したいんです。

    補足日時：2018/09/12 07:23

• 標準偏差に基づく得点毎の人数を270点から0点まで算出して前の散布図に追加したらきれいな曲線で、実データと誤差があるのは無理もないなぁ、と納得しました。
  実際の値の単なる散らばりが原因だったんですね。
  どうもお騒がせ致しました。
  お付き合い頂きありがとうございます。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/09/12 11:26

No.2 ベストアンサー 回答者： usa3usa 回答日時： 2018/09/12 09:20

＞補足コメントの通り、合格点以上での上位占有率は実際に合格点(160点)以上の人数を数えて（SUM関数を使ってですが）21.56%だと確認をとってあります。

どうやって確認をとったのか、式と、計算途中の数値を手を動かして紙に書いて確認しましょう。

>ということは、私の式は正しいということなのでしょうか？
「合格点以上の上位占有率」の定義の式を、（エクセルの関数ではなく）手を動かして紙に書いて確認しましょう。
その定義式が、1-NORMDIST(合格点, 平均点, 標準偏差, TRUE)で計算できるのかは、
あなたが判断することと思います。

この回答へのお礼

ご回答ありがとうございます。

ちょっと思ったので．．．そもそも論なのですが、統計について詳しくないのでお伺いします。

=1-NORMDIST(合格点, 平均点, 標準偏差, TRUE) というのは標準偏差から得られる占有率の理論値であって、実際の生データから得られる占有率とは当然異なるもの、ということはないでしょうか？


合格点以上での上位占有率は、以下のように調べました。

◆270点から60点までの人数を合計

点 人員
270 0
269 0
268 0
267 0
266 0
265 0
264 0
:
169 91
168 79
167 84
166 102
165 78
164 94
163 95
162 101
161 104
160 73
----------------
270点から160点まで人員のタテの列を範囲選択→C6:C116
2299 =SUM(C6:C116)


◆270点から0点までの人数を合計

点 人員
270 0
269 0
268 0
267 0
266 0
265 0
264 0
:
6 0
5 1
4 0
3 0
2 0
1 0
0 0
----------------
270点から0点まで人員のタテの列で範囲選択→C6:C276
10665 =SUM(C6:C276)

よって、
採点対象者：10,665人
合格点(160点)以上：2,299人
合格点以上での上位占有率：21.556% (= 2299/10665*100)

であるという確認を行いました。

お礼日時：2018/09/12 10:09

No.1 回答者： usa3usa 回答日時： 2018/09/12 07:26

＞何が間違っていて、どこをどうすれば正しく計算できるのでしょうか？

具体的な実データの「合格点以上の上位占有率が18.31%」ですので、「合格点以上での上位占有率：21.56%」という記述が間違っている。
ということでは？

もし学校の演習問題か何かであれば、疑うべきは
　実データのエクセルへの入力ミス
でしょうか。

この回答へのお礼

ご回答ありがとうございます。

> 具体的な実データの「合格点以上の上位占有率が18.31%」ですので、・・・（省略）

ということは、私の式は正しいということなのでしょうか？
（関数の引数は何を入れたか、という実際の値ではないのですが．．．）

補足コメントの通り、合格点以上での上位占有率は実際に合格点(160点)以上の人数を数えて（SUM関数を使ってですが）21.56%だと確認をとってあります。
また、エクセル入力の値を再度チェックしましたが、入力間違いはありませんでした。
実データはPDFで「何点に何人」と、最高点225点から55点まで記載され、54点以下は57人とざっくりした記載です。


> もし学校の演習問題か何かであれば、疑うべきは

学校の課題ではありません。
ある試験の合格レベルの難易度(偏差値)を調べています。
合格点での上位占有率が一致すれば、その偏差値は信用できるかなぁ．．．と思いました。

お礼日時：2018/09/12 07:49