エクセルで昇順データから検査値以上の最小の値の位置を取得するには

検査範囲（A1:A10）のデータが、昇順に並んでいます。
検査値(B1)以下の最大の値および検査値以上の最小の値の両方を検索したいのです。
=MATCH(B1,A1:A31,1)で、検査値(B1)以下の最大の値の位置は取得できますが、=MATCH(B1,A1:A31,-1)で検査値以上の最小の値の位置を取得するには検査範囲のデータは、降順に並べ替えておく必要があります。

データの並び順をいじらず、両方を取得するワークシート関数はないのでしょうか？

No.1 ベストアンサー 回答者： shkwta 回答日時： 2005/04/30 18:57

細かい条件がわかりませんが、

=MATCH(B1,A1:A31,1)+IF(COUNTIF(A1:A31,B1),0,1)
とするのではダメでしょうか。

この回答へのお礼

なるほど、検査値と一致するデータがなければ+1するわけですか。
参考になりました。
ありがとうございます。

お礼日時：2005/04/30 20:39

No.3 回答者： goomania 回答日時： 2005/04/30 22:20

Ａ1からＡ３１までにデータが昇順に並んでいる場合で

ご質問者の意図は検査値以下の最大値と検査値以上の最小値
そのものの抽出ではなく、最大値・最小値が格納されている
セルの位置を求めたいということでよろしいでしょうか？

>=MATCH(B1,A1:A31,-1)で検査値以上の最小の値の位置を取得するには
>検査範囲のデータは、降順に並べ替えておく必要があります。

であれば、例えばＤ列にダミー列を作って、Ａ1：Ａ３１を降順に
並べてしまえばどうでしょう。
ただし、Ａ1からＡ３１まで昇順にデータが並んでいるとの前提です。


Ｄ１からＤ３１に=OFFSET($A$1,31-ROW(),0,1,1)を記述する。

検査値以上の最小の値の位置を取得するセル、例えばＥ３２に
「=MATCH(B1,A1:A31,-1)」に替えて=MATCH(B1,D1:D31,-1)を記述する。

ただし、このままではD列における位置を示すことになりますので
Ａ列での位置にこだわるなら
=32-MATCH(B1,D1:D31,-1)にする必要があります。

他のデータをＡ1からＡ３１に貼り付けて同様に結果を求めるような
ケースでは、まずＡ列をキーに昇順にソートする必要がありますが
この場合でもＤ列に記述された数式はソートの影響を受けません。

余談ですが、
最大値・最小値が格納されているセルの位置を求めたいではなく、
検査値以下の最大値と検査値以上の最小値そのものの抽出という
ことであれば
ＶＬＯＯＫＵＰ関数を使用する手段もあると思います。

この回答への補足

お礼で追加質問してしまいましたが、質問をあらたに投稿したほうがよさげですね。

そうします。ありがとうございました。

補足日時：2005/05/01 12:00

この回答へのお礼

ありがとうございます。
極力作業列は作りたくなかったのです。

> 検査値以下の最大値と検査値以上の最小値そのものの抽出ということであればＶＬＯＯＫＵＰ関数を使用する手段もあると思います。

いえ、別に値自体も必要なんです。
それでVLOOKUPも考えたのですが、
=VLOOKUP(B1,A1:A31,TRUE)
では、やはり検査値以下の最大の値しか所得できなかったのですが、検査値以上の最小の値をもとめるにはどうすればいいのでしょう？

お礼日時：2005/05/01 00:49

No.2 回答者： Wendy02 回答日時： 2005/04/30 21:25

私は、およびでないのかもしれませんが、別の答えを考えてみました。

=ROWS(A1:A31)-MATCH(15,LARGE(A1:A31,ROW(A1:A31)),-1)+1
配列確定(Shift+Ctrl - Enter)で出来ますね。
これは、数式の中で、数値を並べ替えて、逆さにして数を取っています。

この回答へのお礼

とんでもない、いつも頼りにしてますよ～（笑）
配列数式は使ったことがありませんでした。
勉強になります。
いつもありがとうございます。

お礼日時：2005/05/01 00:51