エクセルで最大値の隣の隣の数値を返したい

質問

お世話になります
使用はエクセル２００３です。
シート１のＡ１～Ｄ３５にぎっしり１～９９９のいづれかの数値が入っているとして、その中で最大値の右の隣の隣の数値をシート２に表示させたいのです。（たとえば最大値がＡ１にあればＣ１を返し、Ｄ５にあれば
Ｆ５の値を返すということです。）
同じく２番目に大きい数の右の隣の隣の数値というふうに５番目まで
数値を出したいです。
ＩＦとＬＡＲＧＥとＯＦＦＳＥＴを組み合わせてやろうと思ったのですが
うまくいきません。
よろしくお願いいたします

回答No.4

kuri222

VBAによる解法ではだめでしょうか?

回答No.3

mu2011

NO1です。
一例です。
sheet2の任意セルに設定、下方向に５行分コピーして下さい。
=OFFSET(Sheet1!$A$1,SUMPRODUCT((Sheet1!$A$1:$D$35=LARGE(Sheet1!$A$1:$D$35,ROW(A1)))*(ROW(Sheet1!$A$1:$D$35)))-1,SUMPRODUCT((Sheet1!$A$1:$D$35=LARGE(Sheet1!$A$1:$D$35,ROW(A1)))*(COLUMN(Sheet1!$A$1:$D$35)))+1)

回答No.2

Wendy02

こんにちは。

私は、関数はあまり得意ではありませんが、ちょっと考えてみました。

Sheet2 に貼り付けて、必要なだけドラッグ・コピーしてみてください。
ただ、あまり範囲が大きい場合は、延べセル数が限界を超えますから、動きが鈍くなります。その場合は、VBA によるユーザー定義関数のほうが楽に出せるはずです。(むろん、数式をばらして使えば、楽に出せるはずです。)

=OFFSET(Sheet1!$A$1,MAX(INDEX((Sheet1!$A$1:$D$35=LARGE(Sheet1!$A$1:$D$35,ROW(A1)))*ROW($A$1:$D$35),,))-1,MAX(INDEX((Sheet1!$A$1:$D$35=LARGE(Sheet1!$A$1:$D$35,ROW(A1)))*COLUMN($A$1:$D$35),,))+1)

お礼

ありがとうございます。
このとおりでいきました！
式の中身はこれから考えて見ます

回答No.1

mu2011

因みにＥ１：Ｄ３５に最大値が存在した場合を補足して下さい。

補足

申し訳ありません。説明不足でした。
Ａ１～Ｄ３５以外には数値は入っておらず、その他のセルには
なんらかの文字列がはいっています。
なのでＤ３５に最大値があった場合、Ｆ３５の文字列を返す
ということになります。
あくまで最大値を探すのはＡ１～Ｄ３５の中からです