Excelで相対評価したいとき

質問

仕事がらExcelをよく使うのですが，たとえばＢ列に上からつまりＢ１から順に以下のような数値が入っているとします。

90,96,84,78,90,100,66,60,92

　このデータを踏まえＣ列に

Ｂ列の数値上位５分の１（２０％）は５，その次の５分の１（２０％）は４…

という風に出力したい場合はどのような関数を入れればよいのでしょうか？
　わかりにくい質問かもしれませんがご教示いただければ幸いです。よろしくお願い致します。

回答No.2

ta123 · ベストアンサー

非常に分かりにくくなりましたが、以下の式を確定する時にCtrl+Shift+Enterでお願いします。（配列数式）

セルＣ１の場合
=ROUNDUP((MAX(RANK(B$1:B$10,B:B))-RANK(B1,B:B)+1)/MAX(RANK(B$1:B$10,B:B))*5,0)

B$1:B$10とあるのは、データの範囲です。
B1とあるのは、セルＢ１の評価点を求めるケースです。

あとはコピー／ペーストでＯＫです。

回答No.3

imogasi

例データＡ１：Ｆ２３です。一部データはなく、埋まりません。
計数 ランク 点数 ％ 順位 点数
5 16 2 0 1 5
3 18 1 20 5 4
4 17 1 40 9 3
2 19 1 60 13 2
16 5 4 80 17 1
20 1 5 100 21
18 3 5
6 15 2
11 10 3
12 9 3
19 2 5
14 7 4
17 4 5
7 14 2
8 13 2
9 12 3
10 11 3
15 6 4
13 8 4
1 20 1

20
Ａ２３の２０は=COUNT($A$2:A21)
Ｂ２は=RANK(A2,$A$2:$A$21)、Ｂ３：Ｂ２１はその式を複写。
Ｃ２は=VLOOKUP(B2,$E$2:$F$7,2,TRUE)、Ｃ３：Ｃ２１はその式を複写。ＶＬＯＯＫＵＰ関数はＴＲＵＥ型に」すること。段階的区分別表の表引きをやってます。
％の行Ｄ行は、上例では２０％固定値刻みですが、標準偏差がらみ（偏差値など）になるときはここの累積値を変えてください。
Ｅ列はＲａｎｋ数の区切りです。Ｆ列は与える点数です。
上例はＡ列を連続数字にして不自然ですが、実際の数値でやってみてください。
ランク関数が同点では同じ数字になること、％数字が四捨五入の関係で１ぐらいあわないケースがありそう
、ＶＬＯＯＫＵＰ関数の表の範囲の作り方が１ぐらい
間違っているかも知れませんので心配ですが、大筋の
考え、道具立ては上記でいかがでしょうか。
よく考えていただいて、私の回答のアラが見えるようになれば、解決のゴールは近いでしょう。

お礼

お三方ありがとうございました。まとめてのお礼ですみません。RANK関数を使うことでうまいこと解決致しました。今後ともよろしくお願い致します。

回答No.1

Poer

列を2つ使ってよいのなら、
C1に =RANK(B1,B:B)
D1に =IF(C1<=MAX(C:C)/5,5,IF(C1<=MAX(C:C)*2/5,4,IF(C1<=MAX(C:C)*3/5,3,IF(C1<=MAX(C:C)*4/5,2,1))))
と入力し、必要な行だけコピーすればできます。もちろん、同点の場合は同じ評価になります。