EXCEL RANK関数で使用するデータ範囲を動的に変更したいです。

Question

sheet1に以下のようなデータがあります。

■,A___,B______,C____,D_________
1,一組,Aさん,99点,RANK関数
2,一組,Bさん,10点,RANK関数
3,一組,Cさん,50点,RANK関数
4,二組,Dさん,90点,RANK関数
5,二組,Eさん,20点,RANK関数
/
/
/
/
9,三組,Xさん,55点,RANK関数

Range("D1")のRANK関数のところには
=RANK(C1,C1:C9)という風に入れています。

実際にやりたいのは組みごとにRANK関数の範囲を分けるという処理がやりたいです。

イメージ的には
Range("D1")には=IFERROR(RANK(C1,C1:C3),"")
Range("D2")には=IFERROR(RANK(C2,C1:C3),"")
Range("D3")には=IFERROR(RANK(C3,C1:C3),"")
Range("D4")には=IFERROR(RANK(C4,C4:C5),"")
Range("D5")には=IFERROR(RANK(C5,C4:C5),"")

というような感じになる想定です。

offSet関数や、Indirect関数というものがあるようなのですが、
説明のページを参考にしてもよく理解ができませんでした。

http://www.officetanaka.net/excel/function/function/offset.htm

https://oshiete.goo.ne.jp/qa/350140.html

解決方法がありましたらなにとぞ、よろしくお願いいたします。

Answer 1

OFFSET 関数をベースに考えてみました。

OFFSET(基準, 行数, 列数, [高さ], [幅])

前提条件

• A列でソートされていること
• D1セルに式を入れて後はオートフィル

OFFSET関数でRANK関数の参照範囲を動的に設定します。

=RANK(C1, OFFSET(これから考える))

第1引数（基準位置）

点数の範囲を参照したいので、第1引数（基準位置）はC1で固定します。

=RANK(C1, OFFSET($C$1, …))

第2引数（基準からずらす行数）

今の行の組が最初に出てくる位置は、 ttyp03 さんが示されているように
A列をMATCHで探せば見つかります。
MATCH(A1, A:A, 0)（計算速度度外視で列全体を参照しています）
一組なら1、二組なら4になるはずです

MATCHの結果をそのまま使うと、1行目でも一段ずれてしまうため、-1した値を使用します。

=RANK(C1, OFFSET($C$1, MATCH(A1, A:A, 0) - 1, …))

第3引数（基準からずらす列数）

今回はずらす必要はないので0にしておきます。

=RANK(C1, OFFSET($C$1, MATCH(A1, A:A, 0) - 1, 0, …))

第4引数（参照範囲の高さ、行数）

現段階では各組の最初の位置だけを参照しているため、必要な範囲になるまで拡張します。
必要な行数 = その組に所属している人数 = A列の○組の数 となるため
A列全体から、今の行の組の数をCOUNTIFで数えます。
COUNTIF(A:A, A1)（計算速度度外視で列全体を参照しています）

=RANK(C1, OFFSET($C$1, MATCH(A1, A:A, 0) - 1, 0, COUNTIF(A:A, A1), …))

第5引数（参照範囲の幅、列数）

変える必要は無いので省略します。

=RANK(C1, OFFSET($C$1, MATCH(A1, A:A, 0) - 1, 0, COUNTIF(A:A, A1)))

最終的な式

以下の式をD1セルに入力し、残りはオートフィルしてください。

=RANK(C1, OFFSET($C$1, MATCH(A1, A:A, 0) - 1, 0, COUNTIF(A:A, A1)))

補足

OFFSET関数はその性質上、値が変更されると常に再計算が行われるため、数が多くなると非常に重くなる可能性があります。

Comments

[King_of_Flies]

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

参考になりました。

ちなみにA列がソートされていない場合は大分厳しいのでしょうか

[King_of_Flies]

http://tamecell.net/rankif/
こちらの記事でもく手の結果が取得できることがわかりました！
ソートされていなくても動作しました！

[imihito]

リンク先の方法の方が良さそうですね
自己解決（解答以外の方法で解決）した場合は、自分で解答して、そちらをベストアンサーとした方が良いです。
https://teratail.com/help#resolve-myself

Answer 2

組ごとにランク付けをしたいということですね。
四苦八苦してみました。

組の最初の行は以下で取得できます。

=MATCH(A1,INDIRECT("A$1:A$" & COUNTA(A:A)),0)

組の終わりの行は以下で取得できます。

=MATCH(A1,INDIRECT("A$1:A$" & COUNTA(A:A)),1)

これをRANK関数に組み合わせると次のようになります。

=RANK(INDIRECT("C"&ROW()),INDIRECT("C$"&MATCH(A1,INDIRECT("A$1:A$" & COUNTA(A:A)),0)&":C"&MATCH(A1,INDIRECT("A$1:A$" & COUNTA(A:A)),1)))

D列に貼り付けてください。

注意事項
当方の環境だと、組名が漢字だと最終行のみうまく動作しませんでした。
調整が必要かもしれません。

Comments

[King_of_Flies]

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

ひょえぇぇぇ・・・！
式を見てみてビックリしました。

お時間使わせてしまったみたいで申し訳ありません。

私のほうでも実行環境に合わせて式の書き換えが必要になるので、
しばらくお待ちいただけますか。

[King_of_Flies]

すいません、まったく治せませんでした。

http://www.ixam.net/ms-excel/guan-shu-bian/deta-fan-tongwo-dong-deni-zhi-dingsuru
こちらの方法で対応は可能かもしれないので、現在調査中です。

[ttyp03]

そんなに難しいことはしていないんですけどねぇ。

[King_of_Flies]

二つほど確認させてください。
COUNTA(A:A)で何をしているのかが不明です。

Match関数の第三引数の0の完全一致、-1の以上など、の使い方が不明です。

[ttyp03]

COUNTA(A:A)で行数を計算しています。
Match関数についてはマニュアル見れば載ってると思いますが、0を指定することで最初に見つかった場所を求めることができました。
1は最後の場所です。