条件付範囲内での最大値を求めるには？

質問

以下のような表があるとします。
日付　時間　数値　判定列
8/2　9:05　　8
8/2　9:10　　2
8/2　9:15　　6
　　(中略）
8/2　15:15　15
8/3　9:05　　-5
8/3　9:10　　-2
8/3　9:15　　0
　　(中略)
8/3　15:15　　-30

このような表がある場合に判定列に同じ日付の中でのその時間までの最高値または最安値を拾い出してくるには
どのようにすればよいでしょうか？

たとえば、8/2の最高値が15:15の15だとします。しかし、9:15の時点では未来が予測できないので
最高値は9:05の8を判定列に返すようにしたいわけです。
ですのでデータベースであらかじめ範囲を指定してその中から探してくるというよりも一行ずつデータベースが増えていくというイメージになるかと思います。

もちろん、日付が連続していますので日付が変わればまたその範囲内の中での最高値または最安値を探すことになります。

こういった場合にどのような関数を作ればよいのかアイデアをいただけるでしょうか。
日付が連続しているのでそこの切り替わり部分でどのような判定をすればよいのかも悩んでおります。

どちらか一点だけでもかまいませんのでよろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

当初思っていた以上にむつかしいですね。

＞最高値の方には必ずプラス圏だけで
＞最安値のほうは必ずマイナス圏だけ反映したいと思っています。

やはり、配列数式でしか解決する方法というか、式を短くして書く方法は、こういう方法しかないようなのです。

再び、配列確定が必要ですから、目的の式にF2を押して、『ShiftとCtrlを押しながらEnterキー』を押して、配列数式に変換してくださいね。

ご不便かけて、すみません。

最高値

=IF(AND(ISNUMBER(A2),MAX(IF(A$2:$A2=A2,C$2:$C2))>0),MAX(IF(A$2:$A2=A2,C$2:$C2)),"")

最安値
=IF(AND(ISNUMBER(A2),MIN(IF(A2:$A$2=A2,C2:$C$2))<0),MIN(IF(A2:$A$2=A2,C2:$C$2)),"")

試してみてください。

お礼

不便だなんてとんでもない。
早速修正してくださり、とても感謝しております。
おかげで空白セルが生まれました。
いただいた式を元に勉強したいと思います。

回答No.8

sige1701

エラー処理を含めて
最大値
=IF(SUMPRODUCT((ISNUMBER(A2))*($A$2:A2=A2)*($C$2:C2>0)),MAX(INDEX((A2=$A$2:A2)*($C$2:C2),)),"")

最小値
=IF(SUMPRODUCT((ISNUMBER(A2))*($A$2:A2=A2)*($C$2:C2<0)),MIN(INDEX((A2=$A$2:A2)*($C$2:C2),)),"")

お礼

完璧です。0が適正に処理されました。
ありがとうございます。

回答No.7

sige1701

最大値
=IF(SUMPRODUCT(($A$2:A2=A2)*($C$2:C2>0)),MAX(INDEX((A2=$A$2:A2)*($C$2:C2),)),"")

最小値
=IF(SUMPRODUCT(($A$2:A2=A2)*($C$2:C2<0)),MIN(INDEX((A2=$A$2:A2)*($C$2:C2),)),"")

回答No.6

quiteafew

完全な蛇足ですが、もし配列数式を避けたいのであれば。

最大値
=IF(ISNUMBER(A2), IF(MAX(OFFSET(C$2,MATCH(A2,A$2:A2,0)-1,0):C2)>0,MAX(OFFSET(C$2,MATCH(A2,A$2:A2,0)-1,0):C2),""),"")

最小値
=IF(ISNUMBER(A2), IF(MIN(OFFSET(C$2,MATCH(A2,A$2:A2,0)-1,0):C2)<0,MIN(OFFSET(C$2,MATCH(A2,A$2:A2,0)-1,0):C2),""),"")

お礼

ありがとうございます。
そういった式の組み方もあるのですね。
大変、勉強になりました。
自分で使う数式なのに既に理解できなくなっております(笑)
ところで配列数式を避けたほうが良い根拠はなにでしょうか？

回答No.4

Wendy02

もう一度、トライしてみました。

中間行に、文字列や空白行がなく日付が並んでいるのでしたら、以下のような形で、配列確定をしなくてもできるように思います。

最高値
=IF(ISNUMBER(A2),LARGE(INDEX((A2=$A$2:A2)*($C$2:C2),,),COUNTA($A$2:A2)-COUNTIF($A$2:A2,A2)+1),"")

最安値
=IF(ISNUMBER(A2),LARGE(INDEX((A2=$A$2:A2)*($C$2:C2),,),COUNT($A$2:A2)),"")

お礼

早速チャレンジしていただき、大変うれしく思います。
配列確定を回避できました。
0対策ができれば完璧です。ある特定日は-2,-8,-5,-10,-15といったように
ひたすらマイナス圏の日もあるのでそのときはどうなるのか早速書いていただいた数式を入れてみます。

回答No.3

Wendy02

Wendy02 です。

#2さんでご指摘のとおり、非該当の０を取り除く方法は、見つけられませんでした。
なるべく、配列確定を避けたかったのですが、最大値は分かりましたが、最小値のほうがどうにもできませんでした。でしたら、MAX, MIN でもよいと思いますね。

配列数式ですので、「配列確定」で変換が必要です。
確定の方法は、一旦式を入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押します。

最高値
=IF(ISNUMBER(A2),MAX(IF(A2:$A$2=A2,C2:$C$2)),"")

最安値
=IF(ISNUMBER(A2),MIN(IF(A2:$A$2=A2,C2:$C$2)),"")

補足

少し説明が足りなかったので補足します。
最安値の列には必ずマイナス圏だけが反映されるようにしたいのです。
ですので数値が1,6,8,4,3,0,-2,-5,-10といった感じで推移した場合には最高値の方には必ずプラス圏だけで
最安値のほうは必ずマイナス圏だけ反映したいと思っています。
はじめに作っていただいた数式ですと下の方の指摘があったように0になりますが
できれば空欄になる数式になればベストです。
説明が足りなくてすいません。

回答No.2

sige1701

最高値
=IF(ISNUMBER(A2),LARGE(IF(A2=$A$2:A2,$C$2:C2),1),"")
最安値
=IF(ISNUMBER(A2),SMALL(IF(A2=$A$2:A2,$C$2:C2),1),"")

ともに配列数式です。

日付の変わった日の値が0以外の場合
(A2=$A$2:A2)*($C$2:C2)を使用すると
（マイナスの場合、最高値。＋の場合最小値が違ってきませんか）

お礼

ご指摘ありがとうございます。
確かにこの部分はどうなるのかと思案していたところです。

回答No.1

Wendy02

こんばんは。

2行目からデータが始っているとしたら、

　A　　　　B　　　　　C　　　　D　　　　　　　E
日付　　　時間　　　数値　　　最高値　　　　最安値

最高値
D2～

=IF(ISNUMBER(A2),LARGE(INDEX((A2=$A$2:A2)*($C$2:C2),,),1),"")

最安値

E2～
=IF(ISNUMBER(A2),SMALL(INDEX((A2=$A$2:A2)*($C$2:C2),,),1),"")

この式を下方向に、フィルダウン・コピーすればよいかと思います。

お礼

わたしの意図したものと全く同じものができました。
大変、的確に答えていただきありがとうございました。
急いでいたので大変助かりました。