Excelの計算式について教えてください

質問

Excelの計算式で、引数（月日）に60日を加えたものを戻り値になるようにしたのですが
戻り値が土日祝日となった場合、翌日のウィークデーになるようにするにはどのような
式を使えばよいのか教えてください。

回答No.2

tom04 · ベストアンサー

No.1です!

投稿後気になったのですが、
＞60日を加えたものを・・・
とは実働で60日の解釈が前回の数式です。
すなわちこの期間の土日・祝日は除外されてしまいますので、
期間内の土日・祝日は無視して60日後が土日・祝日の場合は

=WORKDAY(A1+59,1,祝日範囲)
としてください。

入力中に気になったのですが、
60日という期間が「2か月後」という意味の場合は大の月・小の月の関係で
きっちり60日後というわけにはいかないので

=WORKDAY(EDATE(A1,2)-1,1,祝日範囲)
としてみてください。

何度も失礼しました。m（_ _）m

回答No.4

kagakusuki

回答No.3です。

　申し訳御座いません、B2セルの関数に関して見落としがあり、A2セルに日付として扱う事が出来ない値が入力された場合だけではなく、A2セルが空欄の場合においても「計算範囲外」という表示が現れてしまう様になっておりました。
　ですから、B2セルに入力する関数を次の様に修正して下さい。

=IF(AND($A2<>"",ISNUMBER(YEAR($A2))),IF(YEAR($A2+60)<2007,"計算範囲外",MIN($A2+60+999*((WEEKDAY($A2+60,2)>5)+COUNTIF($E:$E,INT($A2)+60)),$A2+61+999*((WEEKDAY($A2+61,2)>5)+COUNTIF($E:$E,INT($A2)+61)),$A2+62+999*((WEEKDAY($A2+62,2)>5)+COUNTIF($E:$E,INT($A2)+62)),$A2+63+999*((WEEKDAY($A2+63,2)>5)+COUNTIF($E:$E,INT($A2)+63)),$A2+64+999*((WEEKDAY($A2+64,2)>5)+COUNTIF($E:$E,INT($A2)+64)),$A2+65+999*((WEEKDAY($A2+65,2)>5)+COUNTIF($E:$E,INT($A2)+65)))),"")

>式の仕組みそのものを理解することから始めたいと思っております。

　上記のB2セルの関数の中の

YEAR($A2)

という部分は、A2セルに入力された日付の西暦年数を求める関数です。
　Excelでは日付や時刻を取り扱う際に、シリアル値という数値を用いて日付や時刻を表しています。
　シリアル値は、1900年1月1日の午前0時丁度から経過した時間を日数で表した数値になります。
　例えば、1900年1月2日の午前6時は、1900年1月1日の午前0時から1日と6時間(即ち1.25日)が経過した日時ですから、シリアル値は1.25になります。
　つまり、もし、日付ではなく単なる数値の1.25が入力されているセルに対して、書式設定を行って、表示形式を

2001/3/14 1:30 PM

にした場合には、そのセルには、

1900/1/2 6:00AM

と表示される事になります。
　Excelで扱う事が出来る年数は西暦1901年～9999年の範囲であり、A2セルに入力されている値が0よりも小さかったり、2958466以上であったり、数値ではなく文字が入力されていたりした場合には、Excelではその値を日付として取り扱う事が出来ませんので、その様な場合に

YEAR($A2)

で西暦年を求めようとしてもエラーとなります。
　ISNUMBER関数は、その中に記述された値が数値であるか否かを判定する関数ですので、

ISNUMBER(YEAR($A2))

とする事で、A2セルに入力されている値が日付として扱う事が出来るか否かを判定している訳です。
　尚、A2セルが空欄の場合、空欄は数値の0として扱われますから、1900/1/0という日付として扱われてしまい、YEAR($A2)の部分の値は1900となってしまいますが、その様な場合に関しては、

$A2<>""

の所で、それ以上のデータ処理を行う事を止める様になっております。
　又、回答No.3でも触れましたように、2007年よりも前の年においては、法律による祝日の決め方が現在とは異なっておりましたので、E列の祝日を求める関数を使う事が出来ません。
　そのため、

IF(YEAR($A2+60)<2007,"計算範囲外"

という箇所で、A列に入力された日付が2007年よりも前である場合には、「計算範囲外」と表示する様になっております。

　次に、WEEKDAY関数は曜日を求める関数であり、

WEEKDAY($A2+60,2)

という部分では、A2セルに入力された日付の60日後の日付が何曜日になるかを計算して、月曜日をの場合には数値の1を、火曜日の場合には2を、という具合に数値で曜日を表して行き、土曜日の場合の値は6、日曜日の場合の値は7となります。
　つまり、

(WEEKDAY($A2+60,2)>5)

という箇所はA2セルに入力された日付の60日後の日付が土日であるのか否かを判定している訳です。
　Excelでは判定結果がTRUEである場合を数値の1と同じものとして扱っていますから、例えば

999*(WEEKDAY($A2+60,2)>5)

とした場合には、A2セルに入力された日付の60日後の日付が土日である場合には、999×1と同じ事になるため計算結果は999になり、逆に土日ではない場合には、999×0と同じ事になるため計算結果は0になります。

　次に、

COUNTIF($E:$E,INT($A2)+60)

という部分では、A2セルに入力された日時から(INT関数を使って)時刻のデーターを削除した上で、A2セルに入力された日付の60日後の日付と同じ日付が、E列に表示されている日付の中に何個あるのかという事をカウントしています。
　E列に表示されている日付は祝日と振替休日なのですから、

COUNTIF($E:$E,INT($A2)+60)

という部分のカウント結果が、もし0よりも大きかった場合には、A2セルに入力された日付の60日後の日付は祝日か振替休日のどちらかであるという事になります。
　つまり、A2セルに入力された日付の60日後の日付が、もしも土日祝日か振替休日であった場合には、

((WEEKDAY($A2+60,2)>5)+COUNTIF($E:$E,INT($A2)+60))

という部分の値は1よりも大きくなりますから、

$A2+60+999*((WEEKDAY($A2+60,2)>5)+COUNTIF($E:$E,INT($A2)+60))

という部分の値は「A2セルに入力された日付の60日後の日付」の更に999日後（祝日と土日が重なった日であった場合には999日×(1+1)＝1998日）の日付を表す値となります。
　同様に、

$A2+61+999*((WEEKDAY($A2+61,2)>5)+COUNTIF($E:$E,INT($A2)+61))

という部分では、「A2セルに入力された日付の61日後の日付」が平日の場合には、そのまま61日後の日付とし、土日祝日・振替休日の場合には更に999日を加えた日付を求めています。
　そして

$A2+62+999*((WEEKDAY($A2+62,2)>5)+COUNTIF($E:$E,INT($A2)+62)),$A2+63+999*((WEEKDAY($A2+63,2)>5)+COUNTIF($E:$E,INT($A2)+63)),$A2+64+999*((WEEKDAY($A2+64,2)>5)+COUNTIF($E:$E,INT($A2)+64)),$A2+65+999*((WEEKDAY($A2+65,2)>5)+COUNTIF($E:$E,INT($A2)+65))

という部分において、同様の計算処理を62日後、63日後、64日後、65日後に対しても行っています。
　そして、

$A2+60+999*((WEEKDAY($A2+60,2)>5)+COUNTIF($E:$E,INT($A2)+60))

という部分から

A2+65+999*((WEEKDAY($A2+65,2)>5)+COUNTIF($E:$E,INT($A2)+65))

という部分までをMIN( )で囲む事で、A2セルに入力された日付の60～65日後の範囲内の日付の中で（土日祝日・振替休日の日付には、999日が加算されるため、最小値にはなり得ない事を利用して、）最も先に現れる平日の日付を求めている訳です。
　土日祝日が連続するのは最大でも5日間までなのですから、60～65日後の範囲内の日付の中には、必ず平日の日が含まれている筈ですので、60～65日後の範囲さえ調べれば、60日後以降の日付の中で最も先に現れる平日の日付を求める事が出来る訳です。

お礼

式の構造を勉強させていただいているところで
私には気が付きませんでした。

本当にありがとうございました。

回答No.3

kagakusuki

WORKDAY関数では、途中の土日祝日を除いた日数が60日に達した日の日付を返してしまうため、元の日付に60日を加えた日付とはなりません。
　又、戻り値が5月1日の土曜日だった場合などの様に、ゴールデンウィークも合わせて、戻り値には最大で＋5日しなくてはならない場合もあり得ます。
　ですから例えば以下の様にされては如何でしょうか？

　今仮に、A2以下に元の日付が並んでいて、その戻り値をB列に表示するものとします。
　又、E2以下にその年度の祝日を並べらておくものとします。

　まず、E2以下にその年度の全ての祝日と振り替え休日を入力して下さい。(尚、"春分の日と秋分の日を除く"祝日や振り替え休日を自動計算する方法は後述致します)
　次に、B2セルに次の関数を入力して下さい。

=IF(ISNUMBER(YEAR($A2)),IF(YEAR($A2+60)<2007,"計算範囲外",MIN($A2+60+999*((WEEKDAY($A2+60,2)>5)+COUNTIF($E:$E,INT($A2)+60)),$A2+61+999*((WEEKDAY($A2+61,2)>5)+COUNTIF($E:$E,INT($A2)+61)),$A2+62+999*((WEEKDAY($A2+62,2)>5)+COUNTIF($E:$E,INT($A2)+62)),$A2+63+999*((WEEKDAY($A2+63,2)>5)+COUNTIF($E:$E,INT($A2)+63)),$A2+64+999*((WEEKDAY($A2+64,2)>5)+COUNTIF($E:$E,INT($A2)+64)),$A2+65+999*((WEEKDAY($A2+65,2)>5)+COUNTIF($E:$E,INT($A2)+65)))),"")

　次に、B2セルをコピーして、B3以下に貼り付けて下さい。
　これで、御希望の戻り値が得られるかと思います。
　尚、年度内の祝日や振替休日を自動計算で求める方法も御座います。
　但し、春分の日と秋分の日に関しましては、国立天文台が天体観測等のデータに基づいて発表する日付によって決まるため、計算の自動化は困難ですので、春分の日と秋分の日だけは手入力する必要があります。
　それから、2006年以前には祝日の決め方が現在とは異なっていたため、今回の方法で自動計算の対象としているのは2007年以降の場合に限っております。

　まず、D1セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,"<"&"9999/12/31"+0)-COUNTIF($A:$A,"<"&"2007/1/1"-60)>0,YEAR(SMALL($A:$A,COUNTIF($A:$A,"<"&"2007/1/1"-60)+1)),"")

　次に、E2セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,1,1),"")

　次に、E3セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(E2)=1)),E2+1,"")

　次に、E4セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE(D$1,1,14-WEEKDAY(DATE(D$1,1,0),3)),"")

　次に、E5セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,2,11),"")

　次に、E9セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,4,29),"")

　次に、E11セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,5,3),"")

　次に、E12セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,5,4),"")

　次に、E13セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,5,5),"")

　次に、E14セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(WEEKDAY(E13)<4)),ISNUMBER(E13)),E13+1,"")

　次に、E15セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE(D$1,7,21-WEEKDAY(DATE(D$1,7,0),3)),"")

　次に、E16セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE(D$1,9,21-WEEKDAY(DATE(D$1,9,0),3)),"")

　次に、E17セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(E18=E16+2)),E16+1,"")

　次に、E20セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE(D$1,10,14-WEEKDAY(DATE(D$1,10,0),3)),"")

　次に、E21セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,11,3),"")

　次に、E23セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,11,23),"")

　次に、E24セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1,12,23),"")

　次に、E26セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1+1,1,1),"")

　次に、E28セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE(D$1+1,1,14-WEEKDAY(DATE(D$1+1,1,0),3)),"")

　次に、E29セルに次の関数を入力して下さい。

=IF(ISNUMBER(($D$1&"/1/1")+0),DATE($D$1+1,2,11),"")

　次に、E3セルをコピーして、E6セル、E8セル、E10セル、E19セル、E22セル、E25セル、E27セル、E30セル、E32セルの各セルに貼り付けて下さい。

　最後に忘れずに、その年の春分の日をE7セルに、その年の秋分の日をE18セルに、及び翌年の春分の日をE31セルに、それぞれ入力して下さい。(E列の中であれば、春分の日や秋分の日を、E32以下のセルに入力しても構いません)

お礼

ありがとうございました。
参考にさせていただきます。
つくずく自分のスキル不足を感じます。
式の仕組みそのものを理解することから始めたいと思っております。

回答No.1

tom04

こんにちは！

WORKDAY関数があります。

祝日データはシリアル値で表を作成しておき、その範囲を名前定義しておくことも可能です。
仮に祝日データ範囲を　祝日　と名前定義したとします。

A1セルに開始日を入力します、その実働60日後を表示させたいセルに

=WORKDAY(A1,60,祝日)
としてみてください。

※　お使いのExcelのバージョンが2003までの場合は
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。m（_ _）m