以下のような表があるとします。
日付 時間 数値 判定列
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ベストアンサー
- 回答日時:
こんばんは。
当初思っていた以上にむつかしいですね。
>最高値の方には必ずプラス圏だけで
>最安値のほうは必ずマイナス圏だけ反映したいと思っています。
やはり、配列数式でしか解決する方法というか、式を短くして書く方法は、こういう方法しかないようなのです。
再び、配列確定が必要ですから、目的の式に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.7
- 回答日時:
最大値
=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
- 回答日時:
完全な蛇足ですが、もし配列数式を避けたいのであれば。
最大値
=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
- 回答日時:
もう一度、トライしてみました。
中間行に、文字列や空白行がなく日付が並んでいるのでしたら、以下のような形で、配列確定をしなくてもできるように思います。
最高値
=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 です。
#2さんでご指摘のとおり、非該当の0を取り除く方法は、見つけられませんでした。
なるべく、配列確定を避けたかったのですが、最大値は分かりましたが、最小値のほうがどうにもできませんでした。でしたら、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
- 回答日時:
最高値
=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
- 回答日時:
こんばんは。
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),"")
この式を下方向に、フィルダウン・コピーすればよいかと思います。
わたしの意図したものと全く同じものができました。
大変、的確に答えていただきありがとうございました。
急いでいたので大変助かりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) Accessテーブルの結合で別々のテーブルのフィールドを組み合わせて値を出す方法について 2 2022/07/20 19:43
- Excel(エクセル) エクセルの関数で質問です。 3 2023/02/24 14:07
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- 数学 数学の質問です。三角関数の合成の問題で、最大値を求めるとき、右下の円のような値の範囲から最大値を求め 2 2023/01/09 21:21
- 高校 偏差値を上げれる限界 3 2023/07/04 01:19
- その他(コンピューター・テクノロジー) 【Tableau Desktop】文字列から8桁の数字を日付型(yyyyMMdd)として取得 1 2023/07/31 10:17
- 数学 2*2の行列に対して固有値の最大実部を与えるkの値を求めたい 3 2022/11/08 16:26
- 数学 数学 2時間数に関わる問題について教えてください。 x≧1 y≧-1 2x+y=5 であるとき、xy 7 2022/10/29 10:57
- 数学 数学の証明問題について質問です。 今日私大入試があったのですが、AとBの共通部分となるxの範囲を求め 1 2023/02/10 15:27
- Excel(エクセル) エクセルで指定範囲にある名前と重複した場合に入力できないようにしたい 1 2023/07/13 09:58
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報