列番号や相対的な位置を返す

質問

恐れ入ります。

A1,A2,A3,A4,A5,A6,A7,A8,A9,A10　にそれぞれ
10,　7,　6,　1,　4,　8,　4,　4,　3,　4　と数値が入っています。

したいこと：A1から優位をつけて、6番目に大きな数字の相対的な位置
　　　　　　を返したい。すなわち、A7の相対的な位置である７を返したい。

失　敗　点：5番目と6番目に大きな数字が４で同じなので、A5の相対的な位置である５が返ってしまった。

失敗経緯　：
過程１=LARGE(A1:A10,6)=4
過程２=MATCH(4,A1:A10,0)=5

どなたかお知恵を拝借できれば幸いです。よろしくお願い申し上げます。

回答No.2

noname#204879

A　　B　　C　 D　E　 F
1　 10　9.99　 1　　　1　 1
2　　7　6.98　 3　　　2　 6
3　　6　5.97　 4　　　3　 2
4　　1　0.96　10　　　4　 3
5　　4　3.95　 5　　　5　 5
6　　8　7.94　 2　　　6　 7
7　　4　3.93　 6　　　7　 8
8　　4　3.92　 7　　　8　10
9　　3　2.91　 9　　　9　 9
10　 4　3.90　 8　　 10　 4

B1: =A1-ROW()/100
C1: =RANK(B1,B$1:B$10)
E1: =ROW(A1)
F1: =MATCH(E1,C$1:C$10,0)

「A1から優位をつけて、6番目に大きな数字の相対的な位置」はセル F6 に示されています。7 ですね。

回答No.1

nihonjinn

作業列を使います。
B1に
=A1-ROW()*0.00001
と入力し、B10までコピーします。そうすると同じ大きさの数字があっても、上の行の数字のほうが大きくなります。
あとは
=MATCH(LARGE(B1:B10,6),B1:B10,0)
とすればうまくいくと思います。

「0.00001」は適当な桁数にしてください。
整数だと数万行あったとしても正しくなると思います。
小数だと桁数を増やしたほうがいいかもしれないです。

お礼

ありがとうございました。こんなにはやく回答がいただけると思っていなかったので驚きました。
作業列を使うと、いろいろバリエーションが増えますね。現在入力しているデータのマスが大きいので、できればそのままの列を使いたかったのですが、どう考えても無理そうです。新しいブックにデータ変換して作業を進めています。おかげで、はかどりました。止まっていた作業が進むときの嬉しさを味わっているところです。改めてお礼申し上げます。ありがとうございました。まだまだわからないことだらけで恐縮ですが、今後ともよろしくお願い申し上げます。