表計算アプリでカレンダー作り
勤務予定表や業務予定表など、仕事をしていると月間の予定を書いたカレンダーがどうしても必要になる。今時、素晴らしいカレンダーアプリがたくさんあるのだけど、今でもExcelなどの表計算アプリを使って、自分が所属するチームに合った予定表を作ってしまうことって、よくあると思う。
（もしかして自分だけか？いや、多くの方が同じようなことをやっているという前提で話を進めてしまう）
たいした手間ではないのだけど、毎月、毎年繰り返していると、やはり可能な限りカレンダーを自動生成したくなるのが人情というもの。毎月の単純なコピペの繰り返しは嫌気がさす。一方、手間はかかるけど自動生成されるカレンダーを作り始めると、思わず熱中する。
果たしてどこまで自動化できるのか？
環境
- MacBook Pro Retina 15
- OSX 10.9.4
- Apache OpenOffice 4.0.1
目指すイメージ
- 業務予定表のイメージはこんな感じ。
- 土曜は青、日曜と祝日は赤の背景色にする。
- 祝日の名称もちゃんと入れる。
- A1のセルに「9/1」と入力すれば、9月のカレンダーが出力されるようにしたい。
日付と曜日
- 多くの表計算アプリは「9/1」と入力すると、入力した年の9月1日と解釈され、日付データに変換される。
数式
- 日付は1日以降、+1ずつ加算しておく。
- 曜日は隣の日付データを参照するだけ。
| A | B | |
|---|---|---|
| 1 | 2014/09/01 | 曜日 |
| 2 | =A1 | =A2 |
| 3 | =A2+1 | =A3 |
| n | =An+1 | =An |
書式（二本指タップ >> セル書式の設定(F)...）
- 上記の日付データを利用して、日付と曜日を簡単に表示できる。
| A | B | |
|---|---|---|
| 1 | 2014/09/01 | 曜日 |
| 2 | D | [~gengou]NN |
| 3 | D | [~gengou]NN |
| n | D | [~gengou]NN |
条件付き書式で背景色を設定（メニュー >> 書式 >> 条件付き書式設定...）
- 土曜は青、日曜は赤で背景色を設定する。（祝日はまだ未設定）
- 月ごとに曜日は変化するので、条件付き書式で設定するのだ。
- ちなみに、条件付き書式で設定した書式（背景赤・背景青など）は、メニュー >> 書式 >> スタイルと書式設定（command-T）で変更・削除できる。
祝日の設定は、まだない...。
祝日の難しさ
- 祝日をルール化して、自動的にカレンダーに反映させるのはけっこう手間がかかる。
- 現在までに法律でルール化された祝日は、以下ページで確認できる。
- 月日が確定している祝日は、簡単にデータベース化できるが...
- ハッピーマンデーは、第2月曜日、第3月曜日が何月何日か求める必要がある。
- 国民の休日は、祝日と祝日に挟まれた平日かどうかを判定しなくてはならない。
- 振替休日は、日曜日と重なる祝日の判定と、その後に迎える最初の平日を判定しなくてはならない。
- さらに、春分・秋分については、天文観測の結果を反映させる必要がある。
祝日テーブル
- 上記が祝日の仕様書である。
- 一気にデータベース化するのは難しいので、できる所から徐々にデータベース化していく。
ハッピーマンデー
- まずは、月日が確定している祝日とハッピーマンデーの祝日から。
- 上記の祝日テーブルには、以下の数式が入っている。
- ハッピーマンデーについては、段階的に計算している。
- 月初の曜日を求めて、
- 第一月曜日の日付を求めて、
- 求める月曜の週数に応じて、7の倍数を加算している。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 基準日 | 第何月曜 | 月初曜日 | 第一月曜日 | 幸月曜日 | 祝日日付 | 祝日名称 |
| 2 | =DATE(YEAR(カレンダー.$A$1);1;1) | =IF(E2;A2+E2-1;DATE(YEAR(カレンダー.$A$1);MONTH(A2);DAY(A2))) | 元旦 | ||||
| 3 | =DATE(YEAR(カレンダー.$A$1);1;1) | 2 | =WEEKDAY(A3) | =MOD(2-C3+7;7)+1 | =(B3-1)*7+D3 | =IF(E3;A3+E3-1;DATE(YEAR(カレンダー.$A$1);MONTH(A3);DAY(A3))) | 成人の日 |
国民の休日
- 国民の休日については、祝日と祝日に挟まれる平日を判定する必要がある。
- 現在の暦では、国民の休日が発生する可能性のある期間は、9月の敬老の日と秋分の日の間のみである。
- 敬老の日は、必ず月曜日である。
- 秋分の日は、敬老の日より後だ。
- よって、敬老の日と秋分の日の差が2日だったら、国民の休日と判定できる。
- 曜日は必ず月火水の並びになるはずである。
- 日曜やその他の祝日を挟む可能性はない。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 基準日 | 第何月曜 | 月初曜日 | 第一月曜日 | 幸月曜日 | 祝日日付 | 祝日名称 |
| ... | ... | ... | ... | ... | ... | ... | |
| 12 | 9月1日 | 3 | 2 | 1 | 15 | 2014/9/15 | 敬老の日 |
| 13 | =IF(F14-F12=2;F12+1;"") | 国民の休日 | |||||
| 14 | 9月23日 | 2014/9/23 | 秋分の日 | ||||
祝日の表示
| A | B | C | |
|---|---|---|---|
| 1 | 2014/09/01 | 曜日 | 祝日 |
| 2 | 1 | 月 | =VLOOKUP(A2;祝日table.$F$2:$G$20;2;0) |
振替休日
- 祝日が日曜と重なったら、振替する必要がある。
- その後最初に迎える平日を判定する必要がある。
- 振替は、必ずしも翌日になる訳ではないのだ。
- 例えば、5月3日が日曜日だった場合、振替の遅延が最大3日後になる可能性がある。
- そのことを考慮して、いくつか列を追加して、段階的に振替休日を判定してみる。
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2020/05/01 | 曜日 | 祝日参照 | 祝日判定 | 振替判定1 | 振替判定2 | 振替判定3 | 振替判定4 | 休日種類 | 祝日 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | |
| 4 | 3 | 金 | 憲法記念日 | =ISNA(C4)=0 | =AND(D4;WEEKDAY(A4)=1) | =IF(AND(E4;D5;D6);5; IF(AND(E4;D5);4; IF(E4;3;0))) |
=IF(F4+G3-1<0;0;F4+G3-1) | =G4=1 | =IF(OR(D4;H4);1;WEEKDAY(A4)) | =IF(D4;C4;IF(H4;"振替休日";"")) |
春分の日と秋分の日
1900年 - 2099年までの春分の日の数学的な簡易な求め方 西暦年数の4での剰余が0の場合 1900年 - 1956年までは3月21日 1960年 - 2088年までは3月20日 2092年 - 2096年までは3月19日 西暦年数の4での剰余が1の場合 1901年 - 1989年までは3月21日 1993年 - 2097年までは3月20日 西暦年数の4での剰余が2の場合 1902年 - 2022年までは3月21日 2026年 - 2098年までは3月20日 西暦年数の4での剰余が3の場合 1903年 - 1923年までは3月22日 1927年 - 2055年までは3月21日 2059年 - 2099年までは3月20日春分の日 - Wikipedia
以上の仕組みを表計算のデータベースとして設定すればいいのだ。1900年〜2099年までの秋分の日の数学的な簡易な求め方 西暦年数の4での剰余が0の場合 1900年〜2008年までは9月23日 2012年〜2096年までは9月22日 西暦年数の4での剰余が1の場合 1901年〜1917年までは9月24日 1921年〜2041年までは9月23日 2045年〜2097年までは9月22日 西暦年数の4での剰余が2の場合 1902年〜1946年までは9月24日 1950年〜2074年までは9月23日 2078年〜2098年までは9月22日 西暦年数の4での剰余が3の場合 1903年〜1979年までは9月24日 1983年〜2099年までは9月23日秋分の日 - Wikipedia
- 但し、未知の彗星や惑星の影響を受けて、地球の運行が変化する可能性もある。
- よって、上記の予測は、将来のカレンダーが確定する時にズレるかもしれない。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 西暦 | 剰余 | 剰余0 | 剰余1 | 剰余2 | 剰余3 | 春分日 | 春分年月日 |
| 2 | 1900 | =MOD(A2;4) | 21 | 0 | 0 | 0 | =IF(D2;D2;IF(C2;C2;IF(F2;F2;IF(E2;E2;0)))) | =DATE(A2;3;G2) |
| ... | ... | ... | ... | ... | ... | ... | ... | |
春分の日
# 剰余0 C2の数式 =IF(AND(B2=0;A2>=1900;A2<=1956);21;IF(AND(B2=0;A2>=1960;A2<=2088);20;IF(AND(B2=0;A2>=2092;A2<=2096);19;0)))
# 剰余1 D2の数式 =IF(AND(B2=1;A2>=1901;A2<=1989);21;IF(AND(B2=1;A2>=1993;A2<=2097);20;0))
# 剰余2 E2の数式 =IF(AND(B2=2;A2>=1902;A2<=2022);21;IF(AND(B2=2;A2>=2026;A2<=2098);20;0))
# 剰余3 F2の数式 =IF(AND(B2=3;A2>=1903;A2<=1923);22;IF(AND(B2=3;A2>=1927;A2<=2055);21;IF(AND(B2=3;A2>=2059;A2<=2099);20;0)))
秋分の日
# 剰余0 C2の数式 =IF(AND(B2=0;A2>=1900;A2<=2008);23;IF(AND(B2=0;A2>=2012;A2<=2096);22;0))
# 剰余0 D2の数式 =IF(AND(B2=1;A2>=1901;A2<=1917);24;IF(AND(B2=1;A2>=1921;A2<=2041);23;IF(AND(B2=1;A2>=2045;A2<=2097);22;0)))
# 剰余0 E2の数式 =IF(AND(B2=2;A2>=1902;A2<=1946);24;IF(AND(B2=2;A2>=1950;A2<=2074);23;IF(AND(B2=2;A2>=2078;A2<=2098);22;0)))
# 剰余0 F2の数式 =IF(AND(B2=3;A2>=1903;A2<=1979);24;IF(AND(B2=3;A2>=1983;A2<=2099);23;0))これで1900年から2099年までの春分・秋分を求められる！
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 基準日 | 第何月曜 | 月初曜日 | 第一月曜日 | 幸月曜日 | 祝日日付 | 祝日名称 |
| ... | ... | ... | ... | ... | ... | ... | |
| 7 | =VLOOKUP(YEAR(A2);春分table.$A$2:$H$201;8;0) | 2020/03/20 | 春分の日 | ||||
| ... | ... | ... | ... | ... | ... | ... | |
| 16 | =VLOOKUP(YEAR(A2);秋分table.$A$2:$H$201;8;0) | 2020/09/22 | 秋分の日 | ||||
月中始まりのカレンダーにも対応
- 現状、A1のセルに月初の日付を入力することを前提としているが、
- 場合によっては、月中始まりのカレンダーにした方が使い易いかもしれない。
- 月締決算は月末から翌月上旬にかけて、一連の処理が続くはずなので。
- 月中始まりのカレンダーにした場合、年を跨ぐ12月から1月を出力する場合に問題が発生する。
- 祝日tableを12月の西暦で計算してしまうので、翌年1月の祝日が反映されなくなるのだ。
- その対策として、祝日tableの1月分だけ、翌年の日付も追加してみた。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 基準日 | 第何月曜 | 月初曜日 | 第一月曜日 | 幸月曜日 | 祝日日付 | 祝日名称 |
| 2 | =DATE(YEAR(カレンダー.$A$1);1;1) | 2020/01/01 | 元旦 | ||||
| 3 | =DATE(YEAR(カレンダー.$A$1)+1;1;1) | 2021/01/01 | 元旦 | ||||
| 4 | =DATE(YEAR(カレンダー.$A$1);1;1) | 2 | 4 | 6 | 13 | 2020/01/01 | 成人の日 |
| 5 | =DATE(YEAR(カレンダー.$A$1)+1;1;1) | 2 | 6 | 4 | 11 | 2021/01/01 | 成人の日 |
- 例えば、12月20日始まりのカレンダーにしても、翌年の1月の祝日がちゃんと表示されるのだ。
ダウンロード
改善ポイント
これで1900年から現在予測可能な2099年までのカレンダーが出力できるようになった！- 但し、祝日tableには2014年現在、法律で確定している祝日を登録している。（2016年開始の山の日も追加している）
- よって、過去のカレンダーを出力した場合も、現在確定している祝日で表示されてしまう...。
- より完璧を目指すなら、祝日tableに「運用開始年」と「運用終了年」を追加して、その年に対応した祝日tableとすべきである。
- 国民の休日（祝日と祝日に挟まれた平日）は、祝日tableではなく、カレンダー側で判定すべきかもしれない。
- 現状は9月の敬老の日と秋分の日に挟まれる場合に限定された判定になっている。
- 将来、祝日が追加・変更された時も、漏れなく国民の休日を判定する仕組みにしておきたい。
- 現状は出力するカレンダーに振替休日などの判定ロジックが含まれてしまっているが、
- 将来の拡張性を考えると、365＋31日分の日付tableを作って、そこで振替休日や国民の休日を判定すべきかもしれない。
- 出力するカレンダーは、日付tableから必要な情報を参照するだけにしておくべき。
- そうしておくことで、自由なデザインのカレンダーに素早く作り替えることができる。
- MVC的な構造にしておくべきなのだ。
- Model＝各種tableであり、View＝出力用のカレンダーである。Controllerは無し。
- もしマクロを使いだしたら、それがControllerとなるのかもしれない。
