エクセル2003で１０段階評価

質問

お世話になります。

一学年分の生徒３５０名の成績をテストの得点をデータとして、例えば「上位３％以上を１０、３％未満７％以上を９、７％未満１１％以上を８、…」などと、人数ではなくテストの到達度を％で分けて１０段階評価をしていく際、どのような関数を用いてどのような数式を組み立てたらよいのでしょうか？
お手数をおかけしますがよろしくご教授ください。
よろしくお願いします。

回答No.9

tom04 · ベストアンサー

No.5です！
たびたびごめんなさい。
前回の投稿は無視してください。
大きく外しています。

お詫びのしるしとして、もう一度画像をアップしておきます。
今回も安直な方法ですが・・・
Sheet2に評価用の表を作成しておきます。
（ランク付けは勝手に画像のようにしていますので、適宜変更してください。）

Sheet1のC2セルに
=INDEX(Sheet2!$C$2:$C$11,MATCH(COUNTIF(B:B,">"&B2)/COUNT(B:B),Sheet2!$B$2:$B$11,-1))
（エラー処理はしていません）
という数式を入れフィルハンドルでダブルクリック、またはオートフィルでずぃ～～～！っと下へコピーすると
画像のような感じになります。

お役に立てれば良いのですが、
尚、他の方も仰っていらっしゃるように個人の成績に関することだと思いますので、
実際にご希望の結果になるかどうか確認してみてください。m(__)m

お礼

ご回答ありがとうございました。よくわかりました。
大変参考になりました。ありがとうございました。

回答No.8

mitarashi

lookupなんて使った事もありませんでしたが、なかなか面白い事ができるのですね。勉強になります。
>人数ではなくテストの到達度を％で分けて
のところは、皆さんいろいろに解釈しているようですが（当方も専門外で分かりませんが）
A1:A350に得点が入っているとして、B1以下に、
=LOOKUP(PERCENTRANK($A$1:$A$350,A1)*100,{0,2,7,16,31,50,69,84,93,98},{1,2,3,4,5,6,7,8,9,10})
でいかがでしょうか。
検証の都合上、到達度の数値はWEBで見つけた他の値を採用していますので、実際に合わせて修正下さい。
間違った成績がついてしまうと生徒が可哀想ですので、しっかり確認をお願いします。

お礼

PERCENTRANKなどという関数は使ったことがなく参考になります。
ありがとうございました。研究してみます。

回答No.7

noname#204879

[No.6]において、

セル D2 の式は =MATCH(C2,G$2:G$11) でもＯＫです。
この場合、Ｈ列は不要となり、「H2:H11　　上から 1、2、3、…、10 を入力」の説明も削除してください。

お礼

ご回答ありがとうございます。大変参考になりました。
お礼が遅くなり申し訳ありませんでした。ありがとうございました。

回答No.6

noname#204879

添付図参照

先ず、右図のような表を作成しておきます。
H2:H11　　上から 1、2、3、…、10 を入力
F2:　　　 100% を入力
F3:F8　　 お望みの %値を入力(黄色部分)
F9:F11　　上から 11%、7%、3% を入力
G2:　　　 =PERCENTILE(C:C,1-F2)
　　　　　この式を下方にズズーッとドラッグ＆ペースト

D2:　　　 =VLOOKUP(C2,G$2:H$11,2)
　　　　　この式を下方にズズーッとドラッグ＆ペースト

回答No.5

tom04

こんばんは！
安直な方法ですが・・・一例です。

↓の画像のようにSheet2に段階評価の表を作成しておきます。
今回は○％以上ということなので降順で作成し、薄い緑色部分だけでよいのですが、判りやすくするために「～を超えて」の列も敢えて作っています。
ランクは勝手に決めていますので、実状に合わせて表を作ってみてください。

まずSheet1のデータを点数の降順で並び替えを行います。
（元データに手を付けたくないのであれば別Sheetにコピー＆ペーストして行ってみてください。）

作業列E2セルに
=B2/SUM(B:B)
F2セルに
=SUM($E$2:E2)
として、E2・F2セルを範囲指定しF2セルのフィルハンドルでずぃ～～～！っとオートフィルで下へコピーします。
（画像では％表示にしていますが、小数点のままでもOKです）
当然最下位の人の行では100％（「1」）が表示されます。

これでE列には総得点に対する構成比を表示させ、F列にその構成比を順次累計して上位何パーセントに入っているかを表示させます。

最後にC2セルに
=INDEX(Sheet2!$C$2:$C$12,MATCH(F2,Sheet2!$B$2:$B$12,-1))
という数式を入れ、フィルハンドルでダブルクリックするとSheet2に作成した評価が表示されると思います。

参考になれば良いのですが、的外れならごめんなさいね。m(__)m

回答No.4

imogasi

あまり本核的に使わないのですが、また説明の時間がないのですが
・ＰＥＲＣＥＮＴＩＬＥ関数
・ＰＥＲＣＥＮＴＲＡＮＫ関数
・エクセルメニューでツールー分析ツールの「順位と百分位数」
が使えないか勉強してください。
ＷＥＢでも解説が大雑把で判りにくいが。
今回役立たずとも、今後で使えるかも。

お礼

ご回答ありがとうございました。
ＰＥＲＣＥＮＴＩＬＥ関数…使ったことがありませんので研究してみます。ありがとうございました。

回答No.3

KURUMITO

勘違いをしておりました。

ランク付けについて例えばD1セルに0、E1セルに１０、D2セルには0.0301、E2セルには9、D3セルには0.0701、E3セルには8、D4セルには0.1101、E4セルには7などのように入力して10段階の表を作ります。

お礼

ありがとうございました。実際にやってみました。望み通りになりました。
お礼が遅くなり申し訳ありませんでした。お世話になりました。

回答No.2

keithin

＞人数ではなくテストの到達度を％で分けて１０段階評価をしていく

それなら単純に点数だけ見て仕分けるというイミですから，たとえば100点満点なら
上３％　＝　97点以上
上７％　＝　93点以上
上１５％　＝　85点以上
　：
というお話ですね。

A1に100点満点での点数の場合：
=IF(A1="","",LOOKUP(A1,{0,85,93,97},{1,8,9,10}))

A1に点数，B1にそのテストの満点の値を入れた場合：
=IF(A1="","",LOOKUP(A1/B1,{0,0.85,0.93,0.97},{1,8,9,10}))

お礼

ご回答ありがとうございました。
テストの難易度によって平均点なども変わってきてしまいまして、上位３％が９７点以上とは一概に言えない状況があります。「７０点以上が一人もいない」などということもしばしばです。またよろしくお願いします。

回答No.1

KURUMITO

A列に点数が下方に入力されているとします。
ランク付けについて例えばD1セルに0、E1セルに１０、D2セルには0.00301、E2セルには9、D3セルには0.00701、E3セルには8、D4セルには0.01101、E4セルには7などのように入力して10段階を表を作ります。

その上でB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",VLOOKUP(RANK(A1,A:A,0)/COUNT(A:A),D:E,2,TRUE))