花園・パソコン研究会

アクセスカウンタ

zoom RSS エクセル関数で、休日一覧を作る

<<   作成日時 : 2014/07/31 13:11   >>

なるほど(納得、参考になった、ヘー) ブログ気持玉 1 / トラックバック 0 / コメント 0

syuku01

西暦を入れると、その年の休日を計算するものです。

日本の休日は、日にちが決まっているもの、月の第何週の月曜となっているものがあります。

日にちが決まっているものは、その休日が日曜と重なっている場合に、翌月曜日が振替休日となります。5月の連休の場合は、3日間のどこが日曜になっていても、5月6日が振替休日になります。

また、地球の公転の周期が日数単位で半端が出ることによって、春分の日・秋分の日は天文台が決めることになっています。うるう年に関係し、これはエクセルの日付データのシリアル値に反映されていますが、春分・秋分の日は、別に求めることになります。

秋分の日と、9月第2週の月曜の敬老の日の間が1日の場合は、その間の日を「国民の祝日」として3連休にすることも定められています。

さらに、2016年からは8月11日を「山の日」という祝日にすることも決まりました。

こういう面倒な計算があるので、関数で作る一覧表は、振替休日がありそうな行を作っておかなくてはならず、きれいに詰めて並べることはできません。

左のように、該当しなかった年には振替休日の位置が空欄になります。

左上のA1のセルに、西暦を入れれば、その年の休日を計算するシートを作っておけば、ここからカレンダーや予定表を作ることができます。


●春分・秋分の日の計算式

これは、一定の範囲であれば計算できます。誤差は出るので、一定の範囲となりますが、100年くらいは使えます。そういう式は、ネット上で紹介されています。その式を借用することにします。A1を西暦を入れる場所にし、C1とD1にそれぞれ以下の数式を入れておきます。

C1には春分の日の数値を求める式
= INT(20.8431+0.242194*(A1-1980)-INT((A1-1980)/4)) を

D1には、同じく春分の日の式
=INT(23.2488 + 0.242194 * ( A1 - 1980)) - INT((A1 - 1980)/ 4)

を入れます。ドラッグしてコピーし、そのまま貼り付けて使えます。これらは、いずれも日日だけの式で、日付のシリアル値ではありません。

●シリアル値を作る関数 DATE

=DATE(年,月,日) で、シリアル値が作れます。例えば、=DATE(2014,1,1)とすると、セルには41640が入り、これを日付の書式にして2014/1/1が表示されます。ここでは、書式を○月○日にして、年を表示しないようにします。

エクセルで、日付のシリアル値を使う場合の基本ですが、日付データには「書式」があること・実際には1900/1/1から数えた日数(シリアル値)が入るということを理解してください。

=DATE(年,月,日) の引数は、ありえない数字を入れるとエラーになります。例えば、月に20を入れたり、日に40を入れると、そのような日付はありえないので、エラーです。
しかし、日に0を入れると、前月末の日付が求められます。月末日を求める関数は他にありますが、DATE関数でも求められます。

●曜日だけの書式

B列に曜日が入っていますが、ここは =A3 のように、左の日付を引いてきています。これを、書式を変えて日本語の曜日にします。
式を書いたのちに、セルの書式をユーザー設定の aaa にします。これがない場合は、種類のところに半角で aaa と書き、OKします。

A列が完成してから、B3のセルを+でドラッグして、下にコピー貼り付けします。

syuku02

●日にちが固定された祝日

1月1日元旦のように、日にちが固定されている祝日の場合は、2行使います。2行目は、IF文を使って祝日が日曜と重なった場合の振替休日を計算します。

そこで、A3は、=DATE(A1,1,1) で、年をA1から引いてきて、月を1に、日を1に固定します。
2月11日の建国記念の日も =DATE(A1,2,11) です。

●振替休日の計算・曜日を求めるWEEKDAY関数

日付のシリアル値が入っているセルを引数にして、例えば =WEEKDAY(A3) の式を書くと、1から7までの数字が帰ってきます。1が月曜日です。

引数を増やして、=WEEKDAY(日付,種類) の種類を指定すると、帰る数字を変更できます。
3を指定すると、日曜が0になる0から6の数字が帰ります。日曜が7でなく0になるだけですが、第2月曜などを計算する際に便利です。

いずれにしても、この数字を直接使うことは無いので、関数の条件式などに組み込んで使います。

固定祝日の下のA列には、その上の日付の曜日が月曜かどうかで日付を入れるか決めます。

A4には、 =IF(WEEKDAY(A3)=1,A3+1,"") という式を入れます。

=IF(条件,○,×) の 条件は WEEKDAY(A3)=1 で、上の日付の曜日が月曜なら A3+1 で翌日のシリアル値を、そうでなければ ""で空欄を入れる、という式です。そうでなければ何もしないのではなく、""で空欄を入れるというのがポイントです。これを省くと、空欄でなく0(ゼロ)が入ってしまいます。

また、C列にも式が必要です。A列が空欄でなければ、"振替休日"という文字列を、そうでなければ""を入れます。

=IF(A4<>"","振替休日","") が C4に入りますが、以下、振替休日のC列は、同じ式を張り付ければ、座標も正しく変わって使えます。

●第2月曜日・第3月曜日を求める

1月の第2月曜を成人の日とし、連休にするということが決まり、成人の日は日にちが一定ではありません。

第2月曜は、最大14日、最小8日です。その月が何曜日で始まるかで異なります。こういう場合、表を作って考えます。

月初
第2月曜 8 9 10 11 12 13 14
14との差 6 5 4 3 2 1 0

WEEKDAY関数で0から6の数字を返すのは、種類を3にした場合ですが、月曜が0で始まります。そこで、月初ではなく前月の月末のWEEKDAYの値を14から引けばよいことが分かります。

というわけで、成人の日は、 日にちを、14-WEEKDAY(DATE(A1,1,0),3) として、DATE関数に入れれば良いのです。
=DATE(A1,1,14-WEEKDAY(DATE(A1,1,0),3)) となります。

同じことが、第3月曜でも言えます。この場合は、21から引くことになります。
このパターンで決まる祝日には、2行目は不要ですが、例外が9月第2週の敬老の日の翌日です。ここは後で説明するので、秋分の日の間を1行開けておきます。

●春分・秋分の日

日にちは、C1・D1から持ってきます。

=DATE(A1,3,C1) または =DATE(A1,9,D1) です。 3月・9月のみ固定です。

●ゴールデンウィークの振替休日

前日の曜日のみチェックするのではなく、2日前・3日前が日曜日かどうか見ます。IFの中にまたIFさらにIFを組み入れます。

=IF(WEEKDAY(A14)=1,A14+1,IF(WEEKDAY(A13)=1,A14+1,IF(WEEKDAY(A12)=1,A14+1,"")))

A15には、上の式が入ります。A14の子供の日、A13のみどりの日、A12の憲法記念日の曜日を調べています。

●国民の祝日という例外

A20には、A19とA21が2日間隔になっている場合祝日になる、という例外を式にします。

=IF(A19+2=A21,A19+1,"") をA20に、C20には

=IF(A19+2=A21,"国民の祝日","") を入れます。

●2016年以降の「山の日」

A17は、2016年以降は =DATE(A1,8,11) で良いのですが、それ以前も必要ならIF文をかませます。

=IF(A1>2015,DATE(A1,8,11),””) です。A1が2015より大きければ、DATE(A1,8,11) が、そうでなければ””です。

C列は、他と同じです。A17が空欄でなければ、"振替休日" そうでなければ""です。

出来上がったら、書式の設定を整えて、A1の値を変えてみてください。A1以外のセルは、関数が入っているので、入力禁止です。

●シートにロックをかける

関数が壊されないように、ロックをかけます。

先ず、A1を選択して、セルの書式の保護タグを開き、図のチェックを外します。
その上で、校閲メニューのシートの保護を開きます。図は一緒に表示していますが、これらは別のメニューから開きます。

syuku03


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ
気持玉数 : 1
なるほど(納得、参考になった、ヘー)

コメント(0件)

内 容 ニックネーム/日時

コメントする help

ニックネーム
本 文
エクセル関数で、休日一覧を作る 花園・パソコン研究会/BIGLOBEウェブリブログ
文字サイズ:       閉じる