例えば、英語のテストを10,000人位で実施して、5歳区切りで年齢別に集計します。
各年齢層の8割の人がその点数以上となる点数をその年齢層の基準点とします。
各年齢層別に、この基準点を算出したいのですが、いい方法はありませんか？
データベースのフィールドを"年齢","点数"としてピボットテーブルで集計し、
年齢をグループ化すれば集計は出来ます。
集計フィールドに =SMALL("点数",LEN(COUNTA("点数")*0.2)) のような式を挿入してみましたがだめでした。
何か、いい方法がありましたら教えてください。
No.1
20
3
28pt
仮に、
A2:A10001セルに全回答者の点数が、
B2:B10001セルに全回答者の年齢が、
それぞれ収まっているとします。
このとき、
D1:D2xセルに"0,5,10,15,･･･"と５刻みの数値を（必要な範囲で）入力して、
E1セルに=PERCENTILE(IF(INT(B2:B10001/5)*5=D1,A2:A10001,""),0.2)と入力し、CtrlとShiftを押しながらEnter。
E1セルをコピーしてE2:E2xセルを選択してペースト、で求める結果が得られることと思います。
（E1セルをコピーしてE1:E2xセルを選択してペースト、ではダメです。）
上記のような計算式は「配列数式」と呼ばれるエクセルの機能なので、
http://pc.nikkeibp.co.jp/pc21/special/hr/
などを見て機能を覚えると、いろいろ応用できることと思いますよ。
No.1
203
ここでベストアンサー
28pt
仮に、
A2:A10001セルに全回答者の点数が、
B2:B10001セルに全回答者の年齢が、
それぞれ収まっているとします。
このとき、
D1:D2xセルに"0,5,10,15,･･･"と５刻みの数値を（必要な範囲で）入力して、
E1セルに=PERCENTILE(IF(INT(B2:B10001/5)*5=D1,A2:A10001,""),0.2)と入力し、CtrlとShiftを押しながらEnter。
E1セルをコピーしてE2:E2xセルを選択してペースト、で求める結果が得られることと思います。
（E1セルをコピーしてE1:E2xセルを選択してペースト、ではダメです。）
上記のような計算式は「配列数式」と呼ばれるエクセルの機能なので、
http://pc.nikkeibp.co.jp/pc21/special/hr/
などを見て機能を覚えると、いろいろ応用できることと思いますよ。
ありがとうございます。
No.2
22049
26pt
母数が10000件程度と比較的多いテスト結果なので、標準分布していると考えられます。
ピボットテーブルで平均と標準偏差を出して、累積分布関数で80%の位置まで出せばほぼ正解の基準値が出ると思います。
ピボットを作ってから、C1,C2,D4に数式を入れます。D4,D5を選択してオートフィルして年齢の最後まで引っ張れば、全年齢分入ります（ついでに全体も入る）。
C1の%を変更すれば、一発で70%入る基準、50%（平均）、上位10%などに基準値が変わります。
| 数式 | A | B | C | D |
|---|---|---|---|---|
| 1 | 0.8 | =NORMSINV(1-C1) | ||
| 2 | ||||
| 3 | 年齢 | データ | 集計 | |
| 4 | 10-19 | 平均 / 点数 | 83 | =C4+C5*$D$1 |
| 5 | 標準偏差 / 点数 | 12 | ||
| 6 | 20-29 | 平均 / 点数 | 73 | =C6+C7*$D$1 |
| 7 | 標準偏差 / 点数 | 13 | ||
| 8 | 30-39 | 平均 / 点数 | 91 | =C8+C9*$D$1 |
| 9 | 標準偏差 / 点数 | 7 | ||
| 10 | 全体の 平均 / 点数 | 81.5 | =C10+C11*$D$1 | |
| 11 | 全体の 標準偏差 / 点数 | 13.6 |
↓
| 値 | A | B | C | D |
|---|---|---|---|---|
| 1 | 80% | -0.84162 | ||
| 2 | ||||
| 3 | 年齢 | データ | 集計 | |
| 4 | 10-19 | 平均 / 点数 | 83 | 73 |
| 5 | 標準偏差 / 点数 | 12 | ||
| 6 | 20-29 | 平均 / 点数 | 73 | 62 |
| 7 | 標準偏差 / 点数 | 13 | ||
| 8 | 30-39 | 平均 / 点数 | 91 | 85 |
| 9 | 標準偏差 / 点数 | 7 | ||
| 10 | 全体の 平均 / 点数 | 82 | 70 | |
| 11 | 全体の 標準偏差 / 点数 | 14 |
なお、ピボット中の式として統計関数を入れるのは、何かうまくいかないようです（私の知識不足かも知れません）。
ありがとうございます。平均が低く標準偏差が大きい層があるのでうまくいかないのが難点です。
No.3
527
26pt
面倒くさい方法かもしれませんが、僕ならこのように行います。
1. まず、点数で全員のソートを行い、次に年齢を元に作ったグループでソートを行う。どちらも昇順で。
2. RANK関数を使い、各グループ内での順位を決める。引数はもちろん点数。
3. COUNTIF関数で各グループに所属する人数を求める。
4. "3"で求めた人数に0.8をかけて、各グループ内における8割番目の順位の値を求める。
5. Rounddown関数で、各"4"の値の小数点以下を切り捨てる。
6. vlookup関数を使い、各グループ内における"5"の順位の人の点数を求める。"5"の順位の人が存在しない場合もあるので、検索方法は"true"にしておきましょう（※）。
※例えば全100人のグループの場合、8割番目となるのは80位の人です。このとき79位の人が2人現れると、80位の人は存在しないことになってしまいます。そのため、検索方法は近似値をとる"true"にしています。
ありがとうございます。
zuguimo
2008/09/03 22:58:16
11
10
2
1
2
ありがとうございます。