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関数によって、ハッピーマンデーはシンプルに求められるのだ！

春分・秋分の日付を求める

  • 西暦YYYY年の春分・秋分を求める数式
    • 年月日の「日」を求める。
    • 1900年から2099年まで計算できる。
    • 但し、将来の予測は外れることもある。
春分の日
=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) )

数式の説明...

  • 以下の2項目の値を差し替えれば、春分の日の数式と仕組みは同じである。
    • 1900年の秋分の日時＝23.8896
    • 1年の差異日数＝0.242032
  • 基本的に1年の差異日数は同じになるはずだが、上記数式では異なっている。なぜだろう？
  • 上記数式は完璧なものではなく、実測に近似する値しか求められない。
  • すると、日付が変わる境界値付近では、間違った日付が算出されてしまうことがある。
  • 少なくとも1900〜2099年までは正しい日付が算出できるように補正しているのだ。


たった一つの数式で、春分・秋分の日付を算出できた！
もはや春分table・秋分tableは不要になってしまった。

参考ページ

素晴らしい情報に感謝です！

祝日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と年月日tableが、すべてのカレンダーの基本になる。



    
デザイン

    

    

  • 好みのカレンダーをデザインしたら、年月日tableを参照するだけで、簡単に祝日情報を取得できるのだ。
    • 




    
シフトカレンダー

    

    

  • かつて業務で使っていた予定表の雛形
    • 








    
表組カレンダー

    

    

  • デザインを見慣れたカレンダー風に変更。
    • 

  • 1年分を一気に表示してみた。
    • 

  • コマンドcal -yに対抗してみた。
    • 


f:id:zariganitosh:20140908162214p:image:w960





    
100年カレンダー

    

    

  • 100年分を一気に表示するカレンダー。
    • 

  • 1969年（UNIXが誕生した年）から100年分表示してみた。
    • 

  • 5月のゴールデンウィーク、9月のシルバーウィークなどが、将来どのような連休形となるか俯瞰できる。
    • 


f:id:zariganitosh:20140908161052p:image:w960]


    

  • 但し、100年分の祝日table・年月日tableに拡張しているので、動作が非常に重い。

      

    • ファイルを読み込んで開くのに数分かかる。
      • 

    • 

  • カレンダー 367行401列、年月日table 36526行9列、祝日table 3100行7列という富豪的データベース*2が原因である。
    • 

  • 自動再計算をオフにすれば動きは良いのだけど、条件付き書式の更新もオフになるので、土日祝日の色分けも表示されなくなってしまう...。

      

    • ちなみにOpenOfficeは、ツール >> セルの内容 >> 自動再計算 で切替できる。
      • 

    • 

  • 100万行を超える最大行数を持ちながら、この動作の重さ。表計算アプリの限界か？
    • 







    
20年カレンダー

    

    

  • 100年だと表示年数が多過ぎて、結局一覧できる幅は20年分くらい。
    • 

  • ならば欲張って無駄に100年表示するよりも、コンパクトに20年にしておいた方が動作も軽いはず。
    • 






    

  • また、1900年以降その年限りの特例的な休日が4回あったことにも気付いた。
    • 

  • すべて皇室慶弔行事に伴う休日である。
    • 

  • 祝日ではないので、振替休日とか国民の休日を計算する必要はない。
    • 

  • 幸いにも、それらの休日は振替休日や国民の休日に影響しない日付となっている。
    • 

  • よって、祝日tableに追加してしまっても問題ないはず。
    • 









    
参考ページ

    素晴らしい情報に感謝です！

*1:それなのに富豪的というタイトル、矛盾している...。

*2:贅沢に広大な行列領域を自由気ままに使っていることをイメージしている。