連続しない範囲が複数ある場合の順位の出し方について

質問

こんにちは、

学校で習熟度別のクラス分けをするのですが、行き詰ってしまったのでよろしくお願いします。

21行～370行目までにデータが入っています(20行目までは平均点などを表示するために空けています)。A列に個人番号10101～10750までが入っています。10101は１年１組１番です。各クラス40人ですが、転入生のことも考え50人分ずつ取っています(ですので２組は71行目、３組は121行目から始まるようになっています)。B列に氏名、C列に点数が入っています。

１組と３組の２クラスを応用、標準、基礎クラスの３クラスに分けて、習熟度別授業をしています。テストの成績でクラス分けをするのですが、１組と３組の計80人から上位35人が応用クラス、次の30人が標準クラス、残りの15人が基礎クラスに分けたいのです。

順位を出すのにrank関数を使うのはわかるのですが、範囲が離れている場合はどのようにしたらよいでしょうか？

できれば関数で解決したいのですが、無理ならマクロでもかまいません。

よろしくお願いします。

回答No.4

noname#96418 · ベストアンサー

>順位は#VALUE!と出てしまってできないのです。

念のためにおたずねしますが、2番目の引数の全体をかっこでくくられましたか？手元の2007の97-2003互換モードでは、かっこでくくればうまくゆくのですが・・・

お礼

ありがとうございます！解決しました。

ご指摘の通り、引数全体をかっこでくくっていませんでした。

助かりました。

回答No.5

noname#262398

1位～80位まで順位を出すとなると、
=MATCH(1,INDEX((LARGE(($C$21:$C$60,$C$121:$C$160),ROW(INDIRECT("1:"&COUNT($C$21:$C$60,$C$121:$C$160))))=C21)*1,),)

順位を出さず、クラス分けだけなら、(A,B,Cの3クラスだとして)
=IF(PERCENTRANK(($C$21:$C$60,$C$121:$C$160),C21)<=15/80,"C",IF(PERCENTRANK(($C$21:$C$60,$C$121:$C$160),C21)<=(15+30)/80,"B","A"))
または、
=IF(C21<=PERCENTILE(($C$21:$C$60,$C$121:$C$160),15/80),"C",IF(C21<=PERCENTILE(($C$21:$C$60,$C$121:$C$160),(15+30)/80),"B","A"))

お礼

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

う～ん、複雑ですね。少し時間をかけて解読したいと思います。

回答No.3

koko88okok

C列のランクに関係のないデータの表示形式を文字列に変更することが可能なら、予め文字列に変更しておけば、範囲を $C$21:$C$370 としても支障なくランクが計算されます。（Excel 2003）

ただし、数式を370行までオートフィルした時、文字列の部分は #N/A のエラーが表示されますので、削除する必要があります。

お礼

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

他のクラスの得点を文字列にするわけにはいかないのです。クラスごとや学年全体の平均も必要ですし、質問には書いていませんでしたが、他に２組と４組も３クラスに分けて習熟度別授業をしていますので。

回答No.2

hallo-2007

作業列使いますが
　　　　Ａ　　　　　Ｂ　　　Ｃ　　Ｄ　　Ｅ
20　個人番号　氏名　点数
21　・・・
Ｄ列に　=IF(OR(MID(A21,2,2)="01",MID(A21,2,2)="03"),C1,"")
Ｅ列に　=IF(D21="","",IF(RANK(D21,D:D)<36,"応用",IF(RANK(D21,D:D)<66,"標準","基礎")))
入れて下までコピィしてみては如何でしょうか。

お礼

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

やはり作業列が要りますね。マクロで配列を使ってやるしかないでしょうかね。

回答No.1

noname#96418

２番目の引数を
($C$21:$C$60,$C$121:$c$160)
のように入力してみてください。
2007ではこれで OK です。

お礼

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

Excelのバージョンは2003なんです。平均はお示しいただいた方法でできるので、試してみたのですが、順位は#VALUE!と出てしまってできないのです。