エクセル 月間予定表作成(閏月の月末日処理、祝祭日表示)

カレンダーはパソコンソフトやメールソフトなどで充実しており、多くの方はそちらを使われていると思います。仲間とのスケジュール共有などでもこれらのものは便利です。エクセルで作った予定表を使っている方は今や少数派だと思いますが、エクセル演習と思ってお付き合いください。

昔ながらのエクセルでの予定表ですが、ポイントが二つあります。
ポイントの一つは、閏月により月の最終日は28日、29日、30日、31日までありますが、正しく最終日までのみを表示させます。
もう一つは、年度ごとの祝祭日を、振替休日まで含めてすべての休日を表示するようにしていることです。
その月の月間予定を書き込んだ後は、「カレンダー」シートをPDF化して書き出しメールで配布することも、印刷して貼りだすこともできます。大元の月間予定表は繰り返し何年でも使えますので、それなりに便利と思います。
月間予定表年間祝祭日一覧カレンダーシートで年度と月を決めると、後は予定を書き込むだけで済むようにしています。
15-祝祭日

1.月終わりの日処理(月毎に28日、29日、30日、31日で終わる)
1)日
月初めの1日は、年度を決める$C$2セルと、月を指定する$E$2セルから DATE 関数で指定しますが、これは月間予定表の年月日タイトルを兼ねています。そして、その後2日以降の日は直前の日に 「 +1」してゆきます。これを28日まで繰り返す。
1-日付

29日以降は数式を使い、DAY関数からの日が29ならそのまま、29日でなければ(月が変って1日)空白とするだけです。30,31日も同じように設定。
2-日付

2)曜日
左隣の「年月日」(日で表示されている)をTEXT関数で曜日に変えるだけです。
1日の「曜日」は
=TEXT(WEEKDAY(A4),” aaa”)
これを28日まで続けます。
29日以降の「曜日」は「日」が空欄(28日までの月)なら「曜日」も空欄にし、そうでなければ通常通り曜日を表示させます。30,31日についても同様に行う。
=IF(A32=””,””,TEXT(WEEKDAY(A31+1),””aaa”)
7-曜日
8-曜日

2.祝祭日の表示
1)祝祭日表示
別シートに万年祝祭日一覧表(年度を変えると祝祭日はその年度のものとなる)を作り、VLOOKUP関数で祝祭日を表示します。
カレンダーシート(月間予定表)に年度を入力すると、万年祝祭日一覧はその年度の祝祭日となります。
年間祝祭日一覧

祝祭日には日付が確定している祝日のほか、週と曜日で決まっているもの、地球の自転公転で決まるもの、振替休日、それに国民の休日もありますが、すべて関数や計算で決まります(春分、秋分については地球の公転自転に不規則性がありますので、1980年の気象庁データからの計算ではずれがありますが、数分もない誤差ですので、日の確定には当分問題ありません)。
万年祝祭日一覧表の祝祭日一覧部分は名前定義を「祭日」として、カレンダーの祝祭日表示式をわかり易くしました。
10-祝祭日

カレンダーへの祝祭日表示は1日から31日で同じ数式で行えます。なお振替休日や国民の休日などが存在する年度とそうでない年度がありますので、エラー処理も必要です。
11-祝祭日

13-祝祭日 14-祝祭日

2)祝祭日の計算
計算式のみを示します。
振替休日以外は祝祭日一覧の「日」(E列)に計算式を入れる)
成人の日
=CHOOSE(WEEKDAY(DATE(C6,D6,1)),9,8,14,13,12,11,10)

海の日

=CHOOSE(WEEKDAY(DATE(C17,D17,1)),16,15,21,20,19,18,17)

敬老の日

=CHOOSE(WEEKDAY(DATE(C20,D20,1)),16,15,21,20,19,18,17)

体育の日

=CHOOSE(WEEKDAY(DATE(C24,1,1)),9,8,14,13,12,11,10)

※ 曜日を返すWEEKDAY関数で一日が何曜日であるかを知り、該当の月曜日が何日あるかを
選ぶようにしました。
(例)成人の日第2月曜日なので、元旦が日曜日なら9日、月曜日なら8日、
火曜日なら14日・・・・・・・・・という具合です。

国民の休日
=IF((E22-E20)=2,E20+1,””)

春分の日

=INT(20.8431+0.242194*(C9-1980)-INT((C9-1980)/4))

秋分の日

=INT(23.2488+0.242194*(C22-1980)-INT((C22-1980)/4))

振替休日
祝祭日(F列)と月(D列)に計算式を入れる
例:祝祭日一覧の建国記念日の振り替え休日を例に計算式を示します
(祝祭日に計算式設定)

=IF(WEEKDAY(B7)=1,”振替休日”,””)
 — 建国記念日が日曜日なら振替休日と表示
(月に計算式設定)

=IF(F8<>””,D7,””)     
————————– 祝祭日欄(F8)が空欄でなければ、
建国記念日の月(2月)を入れる

13-祝祭日

 

 

カテゴリー: エクセル タグ: , パーマリンク

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください