エクセル２００３ 入荷予定日を算出する

質問

いつも回答して頂き、とても感謝しています。

入荷予定日を算出する計算式を考えていますが、条件が入る時の方法がさっぱり思付きません。御指導お願い致します。

（条件）
(1)入荷予定日は注文してから４日後です。
(2)日曜日をまたぐ時は、一日伸びます。
(3)第２土曜日をまたぐ時は１日伸びます（日曜分を含めると２日伸びます）。
(4)不定期の注文は予定日は未定です。

A列に定期or不定期を入力、B列に注文日を入力、C列に入荷予定日が表示される様に考えています。

回答No.9

tom04 · ベストアンサー

No.2・8です！

前回（No.8）の数式も厳密にいえば間違った日付が表示される可能性があります。
（たとえば第1土曜日にしたい場合とか・・・）

そこで第何土曜日（他の曜日）でも対応できる数式にしてみましたので
C2セルの数式を↓に変更してください。

=IF(COUNTBLANK(A2:B2),"",IF(A2="定期",IF(AND(B2-DAY(B2)-WEEKDAY(B5-DAY(B2)-5,3)+7*2>=B2,B2-DAY(B2)-WEEKDAY(B2-DAY(B2)-5,3)+7*2<=B2+4),WORKDAY(B2,4),IF(WEEKDAY(B2)<=3,B2+4,B2+5)),"未定"))
（今回も祝日は考慮していません）

＞2010の場合にも分析ツールを使用しなければいけないのでしょうか？
について
Excel2007以降であれば何もせずにそのまま数式が使用できます。
Excel2003までのバージョンの場合のみ「分析ツール」にチェックが必要です。

＞ちょっとでいいので軽く教えて頂けないでしょうか？
というコトですので、今回の数式の意味を軽く・・・

COUNTBLANK等は問題ないと思いますので
IF(AND(B2-DAY(B2)-WEEKDAY(B5-DAY(B2)-5,3)+7*2>=B2,B2-DAY(B2)-WEEKDAY(B2-DAY(B2)-5,3)+7*2<=B2+4),WORKDAY(B2,4),IF(WEEKDAY(B2)<=3,B2+4,B2+5))
の部分の説明を

（1）B2-DAY(B2) でB2セルに入力されている日付の前月末日を求めます
（2）B2-DAY(B2)-WEEKDAY(B5-DAY(B2)-5,3)　の部分でB2セルの前月の最終土曜日を求めます。
（3）（1）・（2）で求めた前月の最終土曜日に7*2（2週間後）をプラス！これでB2セルの第2土曜日が求まります。
＜第3土曜日の場合は　7*3　をプラス　とココで第○土曜日なのか？を調整＞

（4）その第2土曜日が「注文日」～「4日後」に含まれる場合は普通にWORKDAY関数を使用（土曜日を休日とみなす）
（5）上記以外の場合B2セルが日～火曜日までであればB2セルの4日後・水曜日以降であれば5日後を表示
という関数になります。
さらに最初のIF関数でA列が「定期」の場合と「不定期」の場合を分岐しているだけです。

※　前月の最終土曜日を求める数式内の
B2-DAY(B2)-WEEKDAY(B5-DAY(B2)-5,3)
の中のWEEKDAY関数の「5」の部分が「土曜日」を求める数値となりますので
この数値を変更すれば
0　→　最終月曜日
1　→　最終火曜日
2　→　最終水曜日
3　→　最終木曜日
4　→　最終金曜日
6　→　最終日曜日

をそれぞれ求めることが可能です。

以上、長々と書きましたがこの程度でよろしいでしょうか？m（_ _）m

お礼

説明を参考にして、第２土曜日・第４土曜日の複合版を考えてみます。ありがとうございました。

回答No.8

tom04

No.2です！
たびたびごめんなさい。
一番大切な
＞第２土曜日をまたぐ
を見逃していました。

C2セルの数式を
=IF(COUNTBLANK(A2:B2),"",IF(A2="定期",IF(AND(B2-DAY(B2)-WEEKDAY(B2-DAY(B2)-7)+7*2>=B2,B2-DAY(B2)-WEEKDAY(B2-DAY(B2)-7)+7*2<=B2+4),WORKDAY(B2,4),IF(WEEKDAY(B2)<=3,B2+4,B2+5)),"不明"))

に変更してください。

※　今回もWORKDAY関数を使用していますので、
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れるのを忘れないでください。
どうも失礼しました。m（_ _）m

お礼

だいぶ返事が遅れてしまい申し訳ありません。
家で確認しているので、今は2010です。
2010の場合にも分析ツールを使用しなければいけないのでしょうか？一応、ネットで分析ツールの設定方法を見て、設定できたのですが、更に細かい設定をしなければいけないみたいで、さっぱり分かりません。

とりあえず、分析ツール無しで確認してみました。
ルール通りに出来ているので申し分ないです。で、計算式をみましたが、意味がさっぱり分かりません。ちょっとでいいので軽く教えて頂けないでしょうか？ちょこっと数字を変えてやってみたのですが、思った通りにならないので・・・。

回答No.7

ayayakko1

Ｎｏ．５の者です。何度もスミマセン。

エクセル２００３では使えませんが、

エクセル２０１０で追加されたWORKDAY.INTL 関数で、日曜日のみ週末扱いにできるようです。

エクセルのバージョンアップをしてみてはいかがでしょうか？

参考：

WORKDAY.INTL 関数
http://office.microsoft.com/ja-jp/excel-help/HA010354380.aspx

引数３の週末に11を指定し、
引数４の祝日に祝日リストを別シートで別途用意して範囲指定しておけば、
Ｎｏ．２の方の数式がほぼそのまま使えてすべての条件を満たし、
且つ汎用的に祝祭日を指定できるようになるはずです。

※祝日リスト　というシートを作成し、Ａ列に祝日を列記しておきます。
参照先は同一シート内でもどこでも構いませんが別シートの方が使い安いと思います。
この際、祝日に加え毎月の第２土曜日も列記しておきます。
当然毎年、指定し直す必要があります。

Ｎｏ．２の方の数式
=IF(COUNTBLANK(A2:B2),"",IF(A2="定期",WORKDAY(B2,4),"未定"))

エクセル２０１０以降でWORKDAY.INTL 関数を用いて、Ｎｏ．２の方の数式を流用したもの
=IF(COUNTBLANK(A2:B2),"",IF(A2="定期",WORKDAY.INTL(B2,4,11,祝日リスト!A:A),"未定"))

※当方Kingsoft Spreadsheets 2012の為、非対応関数なので検証しておりませんが、ご了承下さいm(__)m

お礼

何度も連投して頂き、ありがとうございます。
エクセル２００３は会社で使用しているものです。家は２０１０なので問題無いのですが。御提案して頂き、ありがとうございました。

回答No.6

ayayakko1

Ｎｏ．５です。どうしてもきになったので、何度もスミマセン。

もし汎用的に祝祭日を考慮するなら、

４連休や５連休・・・１０連休など連休の考慮、

連休と連休の飛び石の考慮などなど。

条件を考慮したらきりがないのでしょうが実務に使うのならセルの数式のみでは限界があると思います。

余計なお世話だと思いますが、VBA関数などを使うことも検討してみてはどうでしょうか？

格段に楽に処理できるようになるはずです。

お礼

たしかに、GWや盆休み等考慮しなければならない部分が抜けていましたが、祝日等は考慮不要です。

回答No.5

ayayakko1

気になったので一応コメントしておきます。

Ｎｏ．２の方の、WORKDAY関数は土曜日と日曜日を祭日扱いにしますので、条件３を満たていません。

Ｎｏ．４の方の数式は、この質問の条件は満たしているようですが、

Ｎｏ．３の方のご指摘通り祝祭日対応されていないので商品の受発注の実務にはそのままでは年間を通して使えないと思います。

余計なお世話かもしれませんが、WORKDAY関数を使わずに、ＮＯ．４の方の式をさらに年間を通して祭日対応する為には別途、祭日を入力しておいて参照し判定させる数式が必要だと思います。

または、Ｎｏ．４の方の数式を利用し、祭日をまたぐところだけその都度手動で対応するかです。

参考：

WORKDAY関数　http://kokodane.com/kan28.htm
類似質問　http://okwave.jp/qa/q4515763.html
祭日一覧　http://koyomi8.com/sub/syukujitsu_table.htm

お礼

御提案と御指摘ありがとうございました。いろいろ考えながら、作成させていただきます。

回答No.4

KURUMITO

A2およびB２セルから下方にデータが有るとしてC２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(A2="",A2="不定期"),"",IF(WEEKDAY(B2)=1,B2+5,IF(AND(WEEKDAY(B2)>1,WEEKDAY(B2)<=2),B2+4,IF(AND(WEEKDAY(B2)>=3,B2<=DATE(YEAR(B2),MONTH(B2),8+(2-1)*7-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1))),B2+4>=DATE(YEAR(B2),MONTH(B2),8+(2-1)*7-WEEKDAY(DATE(YEAR(B2),MONTH(B2),1)))),B2+6,IF(WEEKDAY(B2)>=4,B2+5,B2+4)))))

回答No.3

noname#204879

後になって、「祝日をまたぐときも」とか「年末年始のときは」とか「その他の特定の日も」とか、言い出さないでしょうね。

お礼

言われてみればそうですね。GWや盆休みや正月休み等の条件が足りませんでした。御指摘ありがとうございました。ただ、この部分の休日設定はその時々で変わるので、今は質問した時の条件を満たせばよいかと考えています。

回答No.2

tom04

こんにちは！
データは2行目以降に入力するとします。

C2セルに表示形式を「日付」にして
=IF(COUNTBLANK(A2:B2),"",IF(A2="定期",WORKDAY(B2,4),"未定"))

という数式を入れオートフィルで下へコピーしてみてください。

※　Excel2003をお使いだというコトですので、
WORKDAY関数を使用するにあたり
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。m（_ _）m

回答No.1

kuma310min

かなり長い式で恥ずかしいのですが、C1には、
=B1+4+IF(WEEKDAY(B1)>3,1,0)+IF(AND(4<=DAY(B1),DAY(B1)<=10,2<WEEKDAY(B1),WEEKDAY(B1)<7),1,0)+IF(AND(4<=DAY(B1),DAY(B1)<=10,WEEKDAY(B1)=3),1,0)

・IF(WEEKDAY(B1)>3,1,0)　→　日曜と重なるなら+1

・IF(AND(4<=DAY(B1),DAY(B1)<=10,2<WEEKDAY(B1),WEEKDAY(B1)<7),1,0)　→
　第2土曜は、8日～14日のいずれかなので、それと重なる可能性があるのは4日～10日。
　かつ、火曜～金曜なら+1。

・IF(AND(4<=DAY(B1),DAY(B1)<=10,WEEKDAY(B1)=3),1,0)　→
　2番目の条件で火曜日だった場合、1番目を満たさないので+1。

一応正常に見えますが、間違っていたらご指摘ください。