質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%

Q&A

解決済

2回答

5443閲覧

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

King_of_Flies

総合スコア382

0グッド

1クリップ

投稿2018/02/20 06:28

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

投稿2018/02/20 12:36

編集2018/02/20 13:14
imihito

総合スコア2166

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

King_of_Flies

2018/02/21 00:31

回答ありがとうございます。 参考になりました。 ちなみにA列がソートされていない場合は大分厳しいのでしょうか
imihito

2018/02/21 13:11

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

0

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

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

=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列に貼り付けてください。

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

投稿2018/02/20 07:29

ttyp03

総合スコア16996

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

King_of_Flies

2018/02/20 07:56

コメントありがとうございます。 ひょえぇぇぇ・・・! 式を見てみてビックリしました。 お時間使わせてしまったみたいで申し訳ありません。 私のほうでも実行環境に合わせて式の書き換えが必要になるので、 しばらくお待ちいただけますか。
ttyp03

2018/02/20 08:59

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

2018/02/20 09:05

二つほど確認させてください。 COUNTA(A:A)で何をしているのかが不明です。 Match関数の第三引数の0の完全一致、-1の以上など、の使い方が不明です。
ttyp03

2018/02/20 09:08

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問