エクセルの数式で教えてください。

エクセル数式について教えてください。

数式「=REPT("期日は本日",ISNUMBER(FIND(JIS(TEXT(TODAY(),"m/d"))&"まで",CA31617)))」があります。
この数式はセル「CA31617」に本日の日付「８／３１」が表示された時に
数式のセルに「期日は本日」と表示されます。
本日の日付より
セル「CA31617」に休日を除いた3日後の日付「9／５」
セル「CA31617」に休日を除いた2日後の日付「9／４」
セル「CA31617」に休日を除いた1日後の日付「9／１」
の場合に数式を設定しているセルに「期日が近いです」
と表示出来る方法を教えてください。
よろしくお願いします。

質問者からの補足コメント

• 

  回答ありがとうございます。
  申し訳ありませんでした。
  セル「CA31617」に「８／３１まで」と表示されたときに「期日は本日」と表示されます。
  です。
  今年の9月22日の「休日を除いた3日後」は9月26日となります（9月22日当日も含めての3日後の計算になります。）
  又、基本は、「WORKDAY」ですが、その他の休日として、同じブックのシート名「休日」のセルB1:M135には「日・土・祭日」を除いた自分で決めた休日を設定しております。
  REPT関数を使っている意図はなんですか？
  TEXT関数ではダメですか？
  の件ですが、以前質問をさせて頂いた時に教えて頂いた数式ですので
  私は判断がつきません。申し訳ありません。

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/08/31 14:59

• 

  セル「CA31617」に入っている日付が全角でなければなりませんか？
  たとえば値を2023/08/31として、書式設定で　m/d"まで"　などとしてはいけませんか？
  の件ですが、私以外の者が入力しており、常に大文字入力にしておりますので「JIS」を設定しております。
  解決方法をよろしくお願いします。

    補足日時：2023/08/31 15:00

No.5 ベストアンサー 回答者： goomania 回答日時： 2023/09/01 11:05

No.4です。

前回回答の数式について、補足説明します。

ご質問者の説明からTODAY()および「CA31617」が土日祝日であることはないという前提で数式を作成しています。

このため、万一、TODAY()および「CA31617」が土日祝日であった場合、例えば「CA31617」に、「９／３まで」と入力されていて、TODAY()が本日(2023/9/1)だったら、土日祝日の日数は無視されるので、「期日は本日」が表示されます。

また、万一、「CA31617」が「９／４まで」と入力されていて、2023/9/2(土)に休日出勤して、当該EXCELを開くと、期限について表示されるセルに「期日は本日」が表示されます。

これは、土日祝日は日数に含めないという計算方法のためです。
つまり、期日については、日付が一致しているかではなく、営業日数が本日を含めて何日あるかで判断する数式になっているということです。

この回答へのお礼

ご連絡ありがとうございます。
色々教えて頂き感謝いたします。
全て上手くできました。

お礼日時：2023/09/01 11:51

No.4 回答者： goomania 回答日時： 2023/09/01 00:05

>セル「CA31617」に休日を除いた3日後の日付「9／５」

>セル「CA31617」に休日を除いた2日後の日付「9／４」
>セル「CA31617」に休日を除いた1日後の日付「9／１」
>の場合に数式を設定しているセルに「期日が近いです」と表示出来る方法

とのことですが、そもそも「CA31617」には「○月○日まで」の情報しかないとすると越年のプロジェクトに対応できないのではないですか?

例えば、12月末に「CA31617」が「1月4日まで」となっていたら、「2023年1月4日」なのか「2024年1月4日」なのか判別する手段がないので、「2024年1月4日」と見做して「期日が近いです」と表示するのか、期日を大幅に経過しているので何等かのアラートを出すべきなのか判断できないことになりませんか?

「CA31617」は人手で入力しているようなので、入力ミスも発生すると思います。何故年号を省略して入力しているのか判りません。

仮に本年11月1日に①「1月15日まで」と入力された場合と②「2023/1/15まで」と入力された場合を考えると、②はもしかすると「2023/11/15」または「2024/1/15」の誤りなのではないかと気づく余地がありますが、①の場合、年号がないので間違いを疑うこともできません。

「CA31617」はNo.1さんも指摘しておられますが、「2023/8/31」のように入力することにして、表示形式を「m/d"まで」としておけば「CA31617」の中身はシリアル値です。その上で、「期日は本日」と表示したいセルに、

=IFERROR(CHOOSE(NETWORKDAYS(TODAY(),CA31617,祝日),"期日は本日","期日が近い","期日が近い","期日が近い"),"")

という数式を入れればご質問者のご希望は解決することになります。

これを「CA31617」が「８／３１まで」と入力されているとすると、同じことをするのに、

=IFERROR(CHOOSE(NETWORKDAYS(TODAY(),EDATE(SUBSTITUTE(CA31617,"まで","")*1,(TODAY()>SUBSTITUTE(CA31617,"まで","")*1)*12),祝日),"期日は本日","期日が近い","期日が近い","期日が近い"),"")

という長い数式になってしまいます。

※上記数式中の「祝日」は「祝日」と名前を付けた範囲に土日以外の祝日一覧があるものとして数式を作成しています。

余計なお世話ですが、ご質問者はやや複雑なEXCELのシステムを作成していて、数式やVBAで疑問が湧く都度、このサイトでご質問されているのではないかと推測します。

その時点で最適な回答であっても、その後、「もっとこうしたい」というニーズが発生したとき、以前の回答をそのまま使い続けることがよいのかどうかという疑問が湧きます。

今回も、No.1さんが、REPT関数を使っている意図はなんですか？TEXT関数ではダメですか？という疑問を述べておられます。

また、一つのEXCELによるシステムの部分部分を異なる回答者による回答を繋ぎ合わせて作成すると、ご質問者のEXCELのスキルレベルが、
>以前質問をさせて頂いた時に教えて頂いた数式ですので私は判断がつきません。
というレベルだとすると、それぞれの最適解を求めた結果が全体最適にはならないという「合成の誤謬」に陥る懸念もあるので心配です。

この回答へのお礼

回答ありがとうございました
色々と教えて頂き感謝いたします
参考にさせていただきます

お礼日時：2023/09/01 05:05

No.3 回答者： kigoshi 回答日時： 2023/08/31 16:59

訂正

正）MsgBox ("「XX／XXまで」の様式ではありません")
誤）MsgBox ("「X月X日まで」の様式ではありません")

No.2 回答者： kigoshi 回答日時： 2023/08/31 16:57

ワークシート関数の組み合わせで実現するには複雑すぎて無謀だと思います。

ユーザー定義関数を使う例です。

手順
１）ブックを開いて[Alt]＋[F11]
２）[挿入]→[標準モジュール]
３）右側のエディタエリアに下記コードを貼り付け

Function kijitu(kDay As String) As String
Dim xMon As Integer
Dim xDay As Integer
kijitu = ""
If Right(kDay, 2) = "まで" Then
kDay = StrConv(Left(kDay, Len(kDay) - 2), vbNarrow)
Else
MsgBox ("「X月X日まで」の様式ではありません")
Exit Function
End If
xMon = Val(Left(kDay, InStr(kDay, "/") - 1))
xDay = Val(Right(kDay, Len(kDay) - InStr(kDay, "/")))
If Date = DateSerial(Year(Now), xMon, xDay) Then
kijitu = "期日は本日"
Exit Function
End If
If WorksheetFunction.WorkDay(Date, 4) > DateSerial(Year(Now), xMon, xDay) Then
kijitu = "期日が近いです"
Exit Function
End If
End Function

これでkijitu関数が使えるようになります。
どこかのセルに　=kijitu(CA31617)　を入れてみてください。

ただし、祝祭日は
> 「休日」のセルB1:M135
の様式が分からないので対応していません。
上記コード中
If WorksheetFunction.WorkDay(Date, 4,XXXX)
のXXXX部分に工夫すれば、可能かもしれません。

この回答へのお礼

回答ありがとうございます。
早速試してみます。

お礼日時：2023/08/31 17:32

No.1 回答者： kigoshi 回答日時： 2023/08/31 14:24

いくつか整理しなければならない事柄があります。

> 本日の日付「８／３１」が表示された時に数式のセルに「期日は本日」と表示されます。

とありますが、表示さません。セル「CA31617」に「８／３１まで」と表示されたときに「期日は本日」と表示されます。

> 休日を除いた

とありますが、休日の内容を示してください。
例えば、今年の9月22日の「休日を除いた3日後」は何月何日ですか？
同じく9月9日の休日を除いた1日後の日付は何月何日ですか？

REPT関数を使っている意図はなんですか？
TEXT関数ではダメですか？

セル「CA31617」に入っている日付が全角でなければなりませんか？
たとえば値を2023/08/31として、書式設定で　m/d"まで"　などとしてはいけませんか？