条件に合う日付を表示させる

下記データがあります
　　　　K列　L列　　　　　M列　　　　　N列　　　　　O列　　　　　P列・・・　
1行目　　　　2018/12/2　2018/12/9　　2018/12/16　2018/12/23　2018/12/30・・・
2行目　　　　20000　　　10000　　　　5000　　　　　10　　　　　-100・・・
3行目　　　　100　　　　-2300　　　　　-5000　　　　-5100　　　　-10000・・・
・
・
L列目以降の数値は昇順です。（例：M列目とN列目だと、M列目の方が数値が大きい）

数値がマイナスになった時の1行目の日付-※日をK列に表示したいですが、方法をご教示お願い致します。

上記※日の数値はどこかのセルに整数として入力してあるのを使いたいです。
例えば、※日＝5日とした場合、2行目の場合はセルK2に2018/12/25、3行目の場合はセルK3に2018/12/4を表示。
（ただし、日付の結果が本日より前の日付になる場合は本日の日付、L列目でいきなりマイナスの場合も本日の日付、マイナスが無い行の場合は"-"を表示）
途中に空白行は存在しません。
可能であれば、数値データが変わったらリアルタイムでK列のデータも変わるようなのが良いです。
（関数で出来るでしょうか？出来なければVBAでお願いします）

No.1 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2018/12/29 18:42

スタートは「最初に負の値になった列の1行目を返せ」ですね。

L列から必要範囲の値をsign関数にぶち込んだ配列を生成し、match関数で-1を見つければいいんです。K2には、

=index($L$1:$AA$1, match(-1, index(sign($L2:$AA2),0), 0))

あとはどこかのセルの日数を引き算し、今日より過去にならないようmax関数で今日を下限とし、負の値がなくてN/Aエラーになってたらiferror関数で"—"を返すと。

=iferror(max(today(), 上記の数式-どこかのセル), "—")

この回答へのお礼

ありがとうございます。
MAX関数だと本日の日付しか表示されなかったので、下記で意図した日付が表示されるようになりました。
=IFERROR(IF(INDEX($L$1:$AA$1, MATCH(-1, INDEX(SIGN($L2:$AA2),0), 0))-$J$1>TODAY(),INDEX($L$1:$AA$1, MATCH(-1, INDEX(SIGN($L2:$AA2),0), 0))-$J$1,TODAY()),"-")

お礼日時：2019/01/02 10:36

No.5 回答者： tom04 回答日時： 2019/01/02 17:12

No.2・3です。

＞セルK2には2019/1/2←意図通り
＞セルK3には2018/12/29←過去の日付なので2019/1/2と表示されて欲しい
＞セルK4には2019/1/4←意図通り

K2セルの数式を
=IF(COUNTIF(L2:IV2,"<0"),MAX(TODAY(),MIN(IF(L2:IV2<0,L$1:IV$1))-$J$1),"-")

前回同様、配列数式なのでCtrl＋Shift＋Enterで確定してください。

ではどうでしょうか？m(_ _)m

この回答へのお礼

ありがとうございます。
意図通りの結果を出す事が出来ました。

お礼日時：2019/01/09 09:36

No.4 回答者： tom04 回答日時： 2018/12/29 23:51

No.3です。

投稿後ふと思ったのですが・・・
＞ただし、日付の結果が本日より前の日付になる場合は本日の日付

の「日付の結果」とはマイナス※日した日付のコトでしょうか？
それとも1行目に表示されている日付のコトでしょうか？

前回の数式は該当する1行目の日付が「本日」以降の場合に、マイナス※日（J1セルの数値）としています。

もし、本日または該当する1行目の日付のマイナス※日というのであれば
=IF(COUNTIF(L2:IV2,"<0"),MAX(TODAY(),MIN(IF(L2:IV2<0,L$1:IV$1)))-5,"-")

（前回同様配列数式です。）
としてみてください。

※　上記数式の場合は必ず1行目の日付または本日より
マイナス※日（J1セルの数値）が表示されます。m(_ _)m

この回答へのお礼

ありがとうございます。
日付の結果と言うのはマイナス＊日した日付の事になります。
例えば、最初のマイナス数値がM列目⇒2019/1/6となっておりマイナス日付を5日とした場合、日付の結果的には
2019/1/1となりますが、本日の日付が2019/1/2の場合、2019/1/2と表示させたいです。

お礼日時：2019/01/02 11:08

No.3 回答者： tom04 回答日時： 2018/12/29 23:17

こんばんは！

＞上記※日の数値はどこかのセルに整数として入力してあるのを使いたいです。

J1セルに単に「5」という数値だけを入れているという前提で・・・

K2セルに
=IF(COUNTIF(L2:IV2,"<0"),IF(MIN(IF(L2:IV2<0,L$1:IV$1))>=TODAY(),MIN(IF(L2:IV2<0,L$1:IV$1))-J$1,TODAY()),"-")

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）し
フィルハンドルで下へコピーしてみてください。

※　エラー処理はしていません。

とりあえずIV列（256列目）まで対応できる数式にしています。m(_ _)m

この回答へのお礼

ありがとうございます。
L列目の日付-＊日が本日よりも過去の日付の場合は本日の日付が表示されるのですが、M列目以降の日付-＊日が本日よりも過去の日付の場合、M列目以降の日付-＊日が表示されてしまいました。
　　　　K列　L列　　　　　M列　　　　　N列　　　　　O列　　　　　P列・・・　
1行目　　　　2019/1/1　　2019/1/3　　2019/1/5　　2019/1/7　　2019/1/9・・・
2行目　　　　-100　　　　-100　　　　　-100　　　　-100　　　　-100・・・
3行目　　　　100　　　　-2300　　　　　-5000　　　　-5100　　　　-10000・・・
4行目　　　　100　　　　100　　　　　100　　　　　100　　　　　-10000・・・
上記で、本日が2019/1/2、J1セルに5と入力されている場合、
セルK2には2019/1/2←意図通り
セルK3には2018/12/29←過去の日付なので2019/1/2と表示されて欲しい
セルK4には2019/1/4←意図通り

お礼日時：2019/01/02 10:56

No.2 回答者： ママチャリ 回答日時： 2018/12/29 21:01

これくらい条件が重なると作業行を使った方が良いかもしれません。

もし、それで良ければ、こんな感じです。添付画像のように、1行目を作業行として使います。非表示にしておけば、邪魔にならないですよ。
蛇足ですが、「L列目以降の数値は昇順です」・・・降順ですよね？

【K1】=TODAY()
【L1】=IF(L2="","-",MAX($K1,L2-5))　※Q1までオートフィル。
【K3】=OFFSET($L$1,0,IFERROR(MATCH(0,L3:P3,-1),-1))　※必要な行までオートフィル。

この回答へのお礼

ありがとうございます。
作業業を使う事で意図した日付が表示されました。
すみません昇順です。ご指摘ありがとうございます。

お礼日時：2019/01/02 10:46