100年カレンダー対応富豪的表計算データベース
前回からの続き。その後、改善ポイントに書いたことを実装しながら、表計算カレンダーの改良に努めた。また、調べるほどに、より効率的な方法があることに気付き、何度も修正を繰り返した。同じことができるなら、より少ないシート数、より少ないセル数、より短い数式で実現できた方がエライのだ*1。
ハッピーマンデーの日付を求める
- 西暦YYYY年M月の第1〜4月曜日を求める数式
- 上から順に、第1・第2・第3・第4月曜日を求める。
= 7 - WEEKDAY(DATE(YYYY年;M月;0);3) =14 - WEEKDAY(DATE(YYYY年;M月;0);3) =21 - WEEKDAY(DATE(YYYY年;M月;0);3) =28 - WEEKDAY(DATE(YYYY年;M月;0);3)
数式の説明...
- WEEKDAY関数は、日付から曜日を意味する数値1〜7を返す。
- 日付に続けて指定する引数によって、月曜始まりや、0〜6を返す機能に変化する。
書式 | 日 | 月 | 火 | 水 | 木 | 金 | 土 |
---|---|---|---|---|---|---|---|
WEEKDAY(日付) WEEKDAY(日付;1) |
1 | 2 | 3 | 4 | 5 | 6 | 7 |
WEEKDAY(日付;2) | 7 | 1 | 2 | 3 | 4 | 5 | 6 |
WEEKDAY(日付;3) 1と2以外ならこの動作 |
6 | 0 | 1 | 2 | 3 | 4 | 5 |
- 月火水木金土日が0123456に対応する時、(7 − 前月末の曜日数) = 第1月曜日の曜日数 となる。よって...
- 第2月曜日を求めるなら、さらに7を加算して、(14 − 前月末の曜日数)
- 第3月曜日を求めるなら、さらに7を加算して、(21 − 前月末の曜日数)
- 第4月曜日を求めるなら、さらに7を加算して、(28 − 前月末の曜日数)
月曜始まりの0から6を返すWEEKDAY関数によって、ハッピーマンデーはシンプルに求められるのだ!
春分・秋分の日付を求める
春分の日
=INT(21.4471 + 0.242377*(YYYY年 - 1900) - INT((YYYY年 - 1900)/4) )
数式の説明...
- 21.4471は、1900年に地球が春分点を通過する瞬間の日時。......1900年の春分の日時
- 21.4471日 = 21日10.7304時 = 21日10時43.824分 = 21日10時43分49秒44
- 0.242377は、暦の1年と、公転周期の1年で、発生する差異日数。......1年の差異日数
- 0.242377*(YYYY年 - 1900)は、1900年からYYYY年までの差異日数の合計。......差異日数合計
- INT((YYYY年 - 1900)/4)は、閏年によって修正された日数。......閏年修正日数
- 差異日数合計 - 閏年修正日数 = 未修整の差異日数
- 1900年の春分の日時に、未修整の差異日数を加算することでYYYY年の春分の日を求めているのだ。
秋分の日
=INT(23.8896 + 0.242032*(YYYY年 - 1900) - INT((YYYY年 - 1900)/4) )
数式の説明...
- 基本的に1年の差異日数は同じになるはずだが、上記数式では異なっている。なぜだろう?
- 上記数式は完璧なものではなく、実測に近似する値しか求められない。
- すると、日付が変わる境界値付近では、間違った日付が算出されてしまうことがある。
- 少なくとも1900〜2099年までは正しい日付が算出できるように補正しているのだ。
祝日table
- 1900年から2099年までの祝日を正確に出力するために、祝日tableに運用開始年と運用終了年を追加した。
- 上記の祝日データベース元に、1年後の祝日データベースも作る。
- もちろん、数式を使って自動計算されるようにしておくのだ。
- この1年後の祝日データベースをコピーすれば、2年後、3年後...も素早く完成するのだ。
年月日table
- 1年分の日付をキーにした年月日tableで、振替休日と国民の休日を判定するようにした。
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 年月日 | 曜日 | 祝日参照 | 祝日判定 | 振替判定1 | 振替判定2 | 国民判定 | 休日種類 | 祝日 |
... | ... | ... | ... | ... | ... | ... | ... | ... | |
81 | 2014/03/21 | 金 | 春分の日 | TRUE | -76 | FALSE | FALSE | 1 | 春分の日 |
81 | =A80+1 | =A81 | =VLOOKUP(A81;祝日参照;2;0) | =ISNA(C81)=0 | =IF(AND(D81;WEEKDAY(A81)=1); 2; E80+D81-1) |
=E81=1 | =AND(D80;D83;D81=0; WEEKDAY(A81)<>1) |
=IF(OR(D81;F81;G81); 1; WEEKDAY(A81)) |
=IF(D81;C81; IF(F81;"振替休日"; IF(G81;"国民の休日";""))) |
デザイン
- 好みのカレンダーをデザインしたら、年月日tableを参照するだけで、簡単に祝日情報を取得できるのだ。
100年カレンダー
- 100年分を一気に表示するカレンダー。
- 1969年(UNIXが誕生した年)から100年分表示してみた。
- 5月のゴールデンウィーク、9月のシルバーウィークなどが、将来どのような連休形となるか俯瞰できる。
- 但し、100年分の祝日table・年月日tableに拡張しているので、動作が非常に重い。
- ファイルを読み込んで開くのに数分かかる。
- カレンダー 367行401列、年月日table 36526行9列、祝日table 3100行7列という富豪的データベース*2が原因である。
- 自動再計算をオフにすれば動きは良いのだけど、条件付き書式の更新もオフになるので、土日祝日の色分けも表示されなくなってしまう...。
- ちなみにOpenOfficeは、ツール >> セルの内容 >> 自動再計算 で切替できる。
- 100万行を超える最大行数を持ちながら、この動作の重さ。表計算アプリの限界か?