自由に設定した期間内での最大値を表示したい

エクセル2000です。

A列に時間　B列に数字があるデータから
時間を区切ってその中の最大の数値を表示させたいです。

sheet2に
A列　　　　B列
15：00　　10
15：10　　20
15：20　　18
15：30　　15
15：40　　25

というデータを入れて
sheet1のA1に開始時刻
同じシートのA2に終了時刻を入れ
その最大値とその時刻をB1　B2　に表示させたいです。

A1に15：10　A2に15：30を入れると
B1に20　B2に15：10を表示させたいです。

どうすればいいのでしょうか？
お願いします。

No.3 ベストアンサー 回答者： tom04 回答日時： 2016/01/28 14:46

こんにちは！

一例です。
前提条件として、範囲内に最大値は複数存在しない！とします。

↓の画像でB1セルに
=MAX(IF((Sheet2!A1:A100>=A1)*(Sheet2!A1:A100<=A2),Sheet2!B1:B100))
配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は上記数式をドラッグ＆コピー　→　B1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

B2セル（同じく配列数式です）に
=INDEX(Sheet2!A1:A100,MAX(IF((Sheet2!A1:A100>=A1)*(Sheet2!A1:A100<=A2)*(Sheet2!B1:B100=B1),ROW(A1:A100))))
とし、Ctrl＋Shift＋Enterで確定！

※　万一最大値が複数存在する場合はB2に最後の時刻が表示されます。
（最初の時刻を表示したい場合はB2セルの数式内の「MAX」を「MIN」に変更します。）

※　エラー処理をしていません。
エラー処理をする場合はExcel2000だというコトですので、
ISERROR関数等を併用する必要があると思います。m(_ _)m

No.2 回答者： 銀鱗 回答日時： 2016/01/28 14:20

・B1セル

MATCH関数で入力した時間（A1,A2それぞれの時間）に相当するセルの位置を調べ、
OFFSET関数でB列の検索範囲を指定し、
MAX関数で最大値を求める。

・B2セル
上の式同様にOFFSET関数でB列の範囲を指定し、
MATCH関数で上の式で得られた値と一致するセルの位置を調べ、
INDEX関数またはOFFSET関数でA列の値を求める。

…ってちょっと違うけど、もう数式だけを示されてたわ。

面倒かもしれないけれど、この順番に考え、数式を組み合わせることで解決するはず。
組み合わせが分からなければ、C列以降のセルを使って、一つずつ進めてみると良い。
（一つずつ関数を使えるように回答に気を配っている）
こうすることで次から同様な処理は自力でできるようになる。

No.1 回答者： ryo_ky 回答日時： 2016/01/28 13:17

関数は下記の通りです．

=MAX(OFFSET(Sheet2!$B$1,MATCH($A$1,OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-1,1),0)-1,0,MATCH($A$2,OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-1,1),0)-MATCH($A$1,OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-1,1),0)+1,1))

参考
http://excel-ubara.com/excel3/EXCEL016.html