相対評価の数字をつける関数

質問

300人の5段階評価をつけます。

５，４，３，２，１の割合は、それぞれ１０％、２５％、５０％、１５％、総合得点を降順にした後、評価をつけたいのですが、関数を教えてください。

回答No.4

bunjii · ベストアンサー

>例示していただきたいのですが。
甘え過ぎです。
模擬データまで作成させることは身勝手すぎるようです。
当方の勝手解釈で貼付画像のような模擬データを作成しました。
A列：No.（ソート後に元へ戻せるようにしました）
B列：氏名（イニシャルで重複なし）
C～G列：各科目の得点
H列：合計（総合得点）
I列：順位（評価の境目を確認するために設けました）
J列：評価
J2=IF(RANK(H2,H$2:H$301)<=30,5,IF(RANK(H2,H$2:H$301)<=105,4,IF(RANK(H2,H$2:H$301)<=255,3,2)))
評価5から順にIF関数で振り分けています。
評価5：300人*0.1=30人
評価4：300人*0.25=75人
評価3：300人*0.5=150人
評価2：300人*0.15=45人（残り全員）
模擬データでは総合得点が同じで評価の境目に複数となり上位に評価されています。
下位の評価では上位の規定数より多くなった分がシワ寄せで減少します。
L列とM列は評価毎の人数を集計してあります。

お礼

例示すみませんでした。詳しくありがとうございました。

回答No.5

MackyNo1

特典がE2:E301セルに入力されているなら、空白列の2行目に以下の式を入力して下方向にオートフィルしてください（降順に並べ替える必要はありません）。

=MAX((PERCENTILE($E$2:$E$301,{0,0.15,0.65,0.9})<=E2)*{2,3,4,5})

お礼

簡潔にご説明くださいましてありがとうございました。

回答No.3

kagakusuki

>１は単位不認定なので、除外しています。

との事ですが、100%から「１０％、２５％、５０％、１５％」を差し引いた残りは0%ですので、結局、1の人数は0となり、５，４，３，２の4段階評価となってしまいます。
　もし仮に、「単位が認定された受験者のみの中での割合で『１０％、２５％、５０％、１５％』」という意味なのだとしますと、御質問文の中では単位を認定するかしないかを判断する基準が説明されていないため、300人の中の何人に単位を認定すれば良いのかが不明となりますので、結局、「単位が認定された受験者のみの中での割合」を求める事は出来ません。
　それとも、その300人データとは評価が2以上の受験者のみのデータしか入力されていないという事なのでしょうか?(それですと、結局、５，４，３，２の4段階評価となってしまいます)

　上記の様に、

>５，４，３，２，１の割合は、それぞれ１０％、２５％、５０％、１５％

というだけでは5段階評価とする事は出来ませんので、取り敢えず仮の話として５，４，３，２の4段階評価とする場合に関して回答致します。

　今仮に、元データとしてSheet1のA列に受験番号、B列に受験者の氏名、C列～G列に各教科の点数、H列に総合点数が入力されていて、Sheet1の1行目には「受験番号」、「氏名」、「総合点」等の項目名が入力されていて、実際のデータは2行目以下に入力されているものとします。
　そして、関数を使用してSheet2のA列～C列に各受験者の「受験番号」、「氏名」、「総合点」を総合点の高い順に表示し、Sheet2のD列に５，４，３，２の4段階評価を表示させるものとします。

　次に、Sheet2のA1セルに次の関数を入力して下さい。

=Sheet1!$A$1

　次に、Sheet2のC1セルに次の関数を入力して下さい。

=Sheet1!$H$1

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF($C1="","",IF(ROWS($2:2)>COUNT(Sheet1!$H:$H),"",LARGE(Sheet1!$H:$H,ROWS($2:2))))

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER($C2),INDEX(Sheet1!$A:$A,MATCH($C2,IF($C2=$C1,INDEX(Sheet1!$H:$H,MATCH(A1,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$H:$H,MATCH(9E+307,Sheet1!$H:$H)),Sheet1!$H:$H),0)+MATCH(A1,Sheet1!$A:$A,0)*($C2=$C1)),"")

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF($A2="","",VLOOKUP($A2,Sheet1!$A:$B,2,FALSE))

　次に、Sheet2のD2セルに次の関数を入力して下さい。

=IF(ISNUMBER($C2),6-MATCH((ROWS($2:2)-1)/COUNT(Sheet1!$H:$H),{0,0.1,0.35,0.85}),"")

　次に、Sheet2のA2～D2の範囲をコピーして、Sheet2の3行目以下に貼り付けて下さい。

　以上です。

お礼

大変詳しくご説明くださいましてありがとうございました。

回答No.2

mshr1962

B2:B301に点数が有ったとして
=LOOKUP(RANK(B2,B$2:B$301)/COUNT(B$2:B$301)*100,{0,10,35,85},{5,4,3,2})

お礼

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

回答No.1

bunjii

RARGE関数で総合得点の大きい順に抽出すれば良いでしょう。
模擬データがあれば具体的な数式を提示できると思います。
但し、総合得点で同点となる境目の人については別途順位の基準が必要になるでしょう。

>５，４，３，２，１の割合は、それぞれ１０％、２５％、５０％、１５％、
その割合では4段階になりますので、5段階評価になりません。

お礼

ご回答ありがとうございます。
例示していただきたいのですが。

補足

１は単位不認定なので、除外しています。