指定した日付を除いた日付を算出したい（エクセル関数）

①B列に日付が入力されています。
②D列に整数が入力されています。
③別シートSheet2のA列に日付が入力されています。（会社の休日日）
E列に上記①-②の日付を表示させたいのですが、その計算結果が③と合致した場合、それよりも前の
日付を表示させたいですが関数で算出できるのであれば手法をご教授お願い致します。

例
B列4行目　2019/9/30
D列4行目　15
別シートSheet2のA列
2019/9/1
2019/9/7
2019/9/8
2019/9/13
2019/9/14
2019/9/15
2019/9/21
2019/9/22
2019/9/29
2019/9/30

普通に計算したら2019/9/15になりますが、Sheet2のA列と合致しているので、
この場合、合致しない日付になる2019/9/12
と表示したいです。

No.4 ベストアンサー 回答者： ママチャリ 回答日時： 2019/10/05 21:52

こうかな。

=IF(ISERROR(MATCH(B4-D4,Sheet2!A:A,0)),B4-D4,WORKDAY.INTL(B4-D4,-1,"0000000",Sheet2!A:A))

この回答へのお礼

ありがとうございます。
参考にさせて頂き勉強致します。

お礼日時：2019/10/07 10:15

No.7 回答者： tom04 回答日時： 2019/10/05 22:56

No.6です。

前回の回答は無視してください。
単純にWORKDAY関数を使ってしまうと土日が絡んでくるとお望みの結果になりませんね。

No.4さんと同じやり方になりますが・・・
=WORKDAY.INTL(B4-D4+1,-1,"0000000",Sheet2!A2:A1000)

に変更してください。

どうも失礼しました。m(_ _)m

この回答へのお礼

ありがとうございます。
参考にさせて頂き勉強致します。

お礼日時：2019/10/07 10:16

No.6 回答者： tom04 回答日時： 2019/10/05 22:42

こんばんは！

Sheet2のA列は1行目が項目名でシリアル値は2行目以降にあるとします。
表示したいセルに
=WORKDAY(B4-D4+1,-1,Sheet2!A2:A1000)

という数式を入れてみてください。

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

※　WORKDAY関数の「祭日」の欄に文字列セルが含まれるとエラーになりますので、
数式の　A2:A1000　のように2行目以降としています。
空白セルはいくつあっても構いませんので、お手元のデータ範囲で調整してください。m(_ _)m

No.5 回答者： mike_g 回答日時： 2019/10/05 22:02

チカラヅクでやってみました。

Sheet2 において、
D4: =IF(D$1+E4-E$4<MIN(A$1:A$31),"",D$1+E4-E$4)
E4: =MATCH(D$1,A$1:A$9)+1-ROW(A1)
F4: =E4-E$4
G4: =COUNTIF(A$1:A$9,D4)
H4: =MATCH(0,G$4:G$34,0)
範囲 D4:H4 を下方にズズーッとオートフィル
D2: =INDEX(D$4:D$9,H4)

この回答へのお礼

ありがとうございます。
参考にさせて頂き勉強致します。

お礼日時：2019/10/07 10:16

No.3 回答者： mike_g 回答日時： 2019/10/05 20:58

[No.2]の補足確認、

「休日日」とは所謂 休日リストのこと？

この回答へのお礼

ありがとうございます。
休日リストの事になります。

お礼日時：2019/10/07 10:11

No.2 回答者： mike_g 回答日時： 2019/10/05 19:40

「会社の休日日」の「休日日」(キュージツビ？)とはどういう意味ですか？

この回答へのお礼

ありがとうございます。
休日リストの事になります。

お礼日時：2019/10/07 10:11

No.1 回答者： prius770c 回答日時： 2019/10/05 19:28

何パターンか考え方があると思いますが、

別シートのデータに合致した場合に代わりに表示させる日付を追加した方が良さそうですね。
B列に代わりに表示させる日付を入れます。
例の場合は、9/15の隣に9/12を入れておく
9/13と14のとなりも9/12を入れる事になります。

関数の使い方もいくつかパターンがありますが一例としては下記ようなものがあります。
=IFERROR(VLOOKUP(B4-D4,Sheet2!$A$1:$B$10,2,0),B4-D4)

上記の例だと
①算出された日付が別シートあった場合はその隣の代用する日付を使用する
②別シートに日付が無い場合は、通常通りの計算式で日付を表示させています。

この回答へのお礼

ありがとうございます。
参考にさせて頂き勉強致します。

お礼日時：2019/10/07 10:10