[[20180311185525]]　『変動する範囲を自動で判断するRANK関数の組み方に』（しじみ漬）　ページの最後に飛ぶ

『変動する範囲を自動で判断するRANK関数の組み方について』（しじみ漬）

ご質問させてください。
範囲が変動する範囲を自動で判断してRANKを出したいです。

たとえば、

クラス人点数順位
1年1組 A 90 1
1年1組 B 80 2

1年2組 A 100 1

のようになっている表で、クラスの人数が変動します。
（1組は10人、2組は15人、など）

クラスが切り替わったところから切り替わるまでの人数で
集計をしてみようと試みましたが当方には難しく…

実際に使用したい表では、クラスに値するものが10000件以上あるため、
任意で範囲を変えるのは労力がかかりすぎるため、
この方法についてご教授頂きたく思います。

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

 クラス	氏名	点数	順位
 1年1組	 A	 90	 1
 1年1組	 B	 77	 2
 1年2組	 C	 96	 1
 1年2組	 D	 45	 3
 1年2組	 E	 63	 2

 順位セルに、入っている関数
 =SUMPRODUCT((A:A=A2)*(C:C>C2))+1
（通りすがり） 2018/03/11(日) 19:57

 クラス　人　点数　が↓の様に並び替っているとして
同じ点数がなかったら
D1に　1　と入力して
D2に=IF((A3<>A2)+(B2<>B3),1,D2+1)
と入力してCtrl+Shift+Enterで↓の様にはなるけど
そんな簡単じゃないですよね？
まぁ、参考程度にしてください。
同じ点数がいっぱいある時のことを考えないといけませんね(;^_^A
クラス	人	点数	順位
1年1組	A	90	1
1年1組	A	100	2　=IF((A3<>A2)+(B2<>B3),1,D2+1)
1年1組	A	115	3
1年1組	A	130	4
1年1組	A	145	5
1年1組	A	160	6
1年1組	B	80	1
1年1組	B	105	2
1年1組	B	120	3
1年1組	B	135	4
1年1組	B	150	5
1年2組	A	100	1
1年2組	A	110	2
1年2組	A	125	3
1年2組	A	140	4
1年2組	A	155	5
v(=∩_∩=)v
（SoulMan） 2018/03/11(日) 20:09

 あぁぁぁ、すみません
1組にそんなに沢山同じ名前の人がいるわけないので↓こうですね(;^_^A
同じ点数がいっぱいある時のことは他の回答者の方の回答をお待ちください。
クラス	人	点数	順位
1年1組	F	160	1
1年1組	K	150	2　=IF((A3<>A2),1,D2+1)
1年1組	E	145	3
1年1組	J	135	4
1年1組	D	130	5
1年1組	I	120	6
1年1組	C	115	7
1年1組	H	105	8
1年1組	B	100	9
1年1組	A	90	10
1年1組	G	80	11
1年2組	E	155	1
1年2組	D	140	2
1年2組	C	125	3
1年2組	B	110	4
1年2組	A	100	5
v(=∩_∩=)v
（SoulMan） 2018/03/11(日) 20:40

SoulMan様

これは、降順に並んでいることが前提ですよね？

だから、もしKさんの点数が150点ではなくて180点だった場合は
毎回並べ替えからするのでしょうか？

 =SUMPRODUCT((A:A=A2)*(C:C>C2))+1

　で、あれば途中点数が変わっても順位は勝手に変更されますよ？

　しかも、今の並びのままで。たぶん、名前の順か何かですよね？←しじみ漬さんへの質問
（通りすがり2） 2018/03/11(日) 20:54

 あっあぁ、すみません
全然トンチンカンでしたね(^_^;)
忘れて下さい
何やってんだか
猛省
（SoulMan） 2018/03/11(日) 21:03

皆様コメントありがとうございます。

点数は降順にソートされてなく、人の並び順に依存するものです。
説明不足ですみません……(ヽ´ω`)

1年1組 A 80
1年1組 B 50
1年1組 C 60
.
.
.
1年2組 A 10
1年2組 B 40

な感じです。

（しじみ漬） 2018/03/11(日) 21:09

 通りすがり2様
⬇いいですねぇ
=SUMPRODUCT((A:A=A2)*(C:C>C2))+1
勉強になります
ありがとうございました
また、大変失礼しましたm(_ _)m
しじみ漬様 へ
通りすがり2様
の回答で出来てませんか？
私のは全然ですけど＿|￣|○

（SoulMan） 2018/03/11(日) 21:15

 通りすがり様と
通りすがり2様
ふたりいらっしゃるのかな？
どちら様もどうもすみませんでした
なんかボロボロ（ ;  ; ）
（SoulMan） 2018/03/11(日) 21:28

通りすがり2様

補足ありがとうございます。

SoulMan様

いえいえ、でもｉｆ文では無理だなぁ～ってちぃと思ってました(笑)
どんまいです。

そうですね、二人いるのかも・・・
（通りすがり） 2018/03/11(日) 21:39

そうですか
ありがとうございます😊
久しぶりにやらかしました
なんかへんだなぁとは思ってました
トピ主様もどうもすみませんでした
早く解決されるといいですね
では、では、
（SoulMan） 2018/03/11(日) 21:45

 こんばんわ。

 >実際に使用したい表では、クラスに値するものが10000件以上ある
 と言う事は数式を入力するセルも10000セル以上あると言う事ですよね。

 >=SUMPRODUCT((A:A=A2)*(C:C>C2))+1
 ではEXCELが返ってこなくなるかも？
 範囲指定は必須ですね。

 以下の式ではどうですか？
 =IF(A2="","",RANK(C2,OFFSET(C$1,MATCH(A2,A:A,0)-1,0,COUNTIF(A:A,A2),1)))

 もしくはクラスが必ず昇順に並んでいるなら、以下の式の方が計算は早いです。
 =IF(A2="","",RANK(C2,INDEX(C:C,MATCH(A2,A:A,0)):INDEX(C:C,MATCH(A2,A:A))))

（sy） 2018/03/12(月) 01:24

皆様
有難うございます。

   ＞ =SUMPRODUCT((A:A=A2)*(C:C>C2))+1
 こちらで行けそうです。

 ＞以下の式ではどうですか？
 ＞=IF(A2="","",RANK(C2,OFFSET(C$1,MATCH(A2,A:A,0)-1,0,COUNTIF(A:A,A2),1)))
 ＞もしくはクラスが必ず昇順に並んでいるなら、以下の式の方が計算は早いです。
 ＞IF(A2="","",RANK(C2,INDEX(C:C,MATCH(A2,A:A,0)):INDEX(C:C,MATCH(A2,A:A))))

こちらも試してみましたが、同じ点数のときの挙動が不安定に感じました。
たしかにエクセルが重くなる（すでに重いので…）ことも考慮して、
ある程度の範囲毎に教授頂いた関数で順位を出して、数値に変換することで逃げようと思います。

とても助かりました。(;_;)
（しじみ漬） 2018/03/12(月) 10:30

 =COUNTIFS(A:A,A2,C:C,">"&C2)+1
（GobGob） 2018/03/12(月) 12:03

コメント返信：

[ 一覧(最新更新順) ]

[[20180311185525]] 『変動する範囲を自動で判断するRANK関数の組み方に』（しじみ漬） ページの最後に飛ぶ

[[20180311185525]]　『変動する範囲を自動で判断するRANK関数の組み方に』（しじみ漬）　ページの最後に飛ぶ