お仕事に活かす・・・基礎から学ぶエクセル講座
 
 エクセルをたのしみながら身につけられるよう、日常生活に密接したシステムなどをテーマに、そのシステム作成に必要なExcel関数や、基礎的な操作について解説しています。
相互リンク大募集!本文へジャンプ

スケジュール管理
システム UP

 

お仕事に活かす・・・基礎から学ぶエクセル講座のサイトへようこそ

 
ホーム > 面白エクセル > 万年カレンダー

6 万年カレンダー








 万年カレンダーを作りました

このテーマは平成11年秋期、通商産業省(現在の経済産業省)主催の国家試験「情報処理技術者試験・初級システムアドミニストレータ」で実際に出題された問題をアレンジしたものです。

この試験は、いわゆる表計算ソフトを使う問題であり、「Excel」に限定したものではありません。

この問題をエクセルで解く場合の解答の一例を示したものです。

課題を解きほぐしていくステップが参考になります。

かなりの大作ですが、最後までがんばって作ってみましょう。

   


【わかっている事実】
西暦1年1月1日は月曜日である。

1 完成イメージを作る

 新しいブックのSheet1にまず完成イメージを作ってしまいます。
 (説明の都合上、セル番地を合わせて作成してください)




それぞれのセルには計算式が入りますから、罫線・セル色・フォントなどは完成後再調整します。この時点ではあまり見栄えは気にしないほうがいいですよ。

シート名を「カレンダー」に変更しておきましょう。
(シート名の変更は、シートタブをダブルクリックして、文字入力します)

2 作業用ワークシートを作る
「Sheet2」のシート名を「作業用」に変更しておきます。

次のように、表を作成してください。
このワークシートは作業用で、完成後は目に触れませんから、自分が見やすいように作ります。見栄えはまったく関係ありません。




※注意

  • 2月は28日とは限りませんよ。うるう年かどうかを判定して日数を表示します。
  • セルB5の0または1は計算により表示します。


3 うるう年かどうかの判定

 うるう年というのは2月が29日まであって、1年が366日になる年です。
 西暦2000年はうるう年でした。2004年もうるう年です。
 次のうるう年は2008年です。
 そうです。うるう年は4年に一度です。誰もが知っている常識です。

 エクセル風に言えば、西暦年数を4で割って、割り切れる年、言い換えれば余りが出ない年がうるう年です。

 そう! MOD関数を使えば計算できそうですよ。

 しか〜し、西暦1900年はうるう年ではなかったんですよ。そして、西暦2100年もうるう年ではありません。

 そうです、100で割り切れる年はうるう年ではないのです。

 じゃあどうして西暦2000年はうるう年なんですか?100で割り切れるのに!

 もうひとつの決まりがこれ、400年ごとにうるう年にするっていうんです。

うるう年にもこんな決まりがあるんですね。これ、「グレゴリオ暦」といって、全世界で使われている方式なんですよ。

全世界でといっても、日本では戦前・戦後まで「紀元」という暦があったんですね。たしか、戦争中に「紀元は2600年」という歌が流行したようですよ。

さて、余計な話から戻って、うるう年の決まりは、まとめて言えばこうなります。

「西暦年数を4で割って割り切れればうるう年、
 ただし、100で割り切れる年はうるう年ではない(平年という)、
 もうひとつ、ただし、400で割り切れればうるう年。」

400年で97回のうるう年があります。それでも、3000年で1日の狂いが生じるそうです。でも、3000年経てば、太陽と地球の関係が変化していることも考えられるので、これで行きましょうということになっています。

 この決まりをエクセルの数式にして、セルC20に入れ、セルE10の年がうるう年か平年かを判定します。

 条件がいくつかに分かれますから IF関数、余りを求めるから MOD関数を使います。

 まず、簡単なところから、4年に一度のうるう年の式はこうなります。
(うるう年なら1、平年なら0と表示することにします)

 =IF(MOD(B3,4)<>0,0,1)・・・・・@

  ※<>は「≠」を表します。

 セルB3の2006を4で割って余りが0以外なら答は0(平年)、割り切れれば、すなわち余りがゼロなら答は1(うるう年)になります。

 2006年の場合は0になるはずです。
 2000,2008などを入力し、答が1になることを確認してください。

 次は100で割り切れる年は平年であるという式を考えます。もう簡単ですね。

 =IF(MOD(B3,100)<>0,1,0)・・・・・A

 続いて、400で割り切れれば、うるう年です。

 =IF(MOD(B3,400)=0,1,0)・・・・・B

@〜Bの式をまとめます。

@の式の =0,0,1 の右端の1の部分にAの式を入れます。
Aの式の =0,0,1 の右端の0の部分にBの式を入れます。

IF関数は =IF(論理式,真の場合,偽の場合)ですから、「偽の場合」が一筋縄ではいかないため、こうなるわけですね。

結果の式はこうなります。

=IF(MOD(B3,4)<>0,0,IF(MOD(B3,100)<>0,1,
     IF(MOD(B3,400)=0,1,0)))

さまざまな数を入力して0または1が正しく表示されることを確認してください。

4 月ごとの日数入力

セルB8〜B19に各月の日数を入力します。2月以外は固定の定数ですが、2月はセルB5の値を参照して28または29が表示されるようにします。

   セルB9の式: =IF(B5=0,28,29)

5 指定月の前月までの日数を求める

 指定月の1日が何曜日なのかを求めるため、指定月の前月までの今年の日数を計算します。

計算の結果はC列に表示します。

 A列の月数が指定月(セルB4のデータ)より小さい場合は、C列にB列の日数を表示し、そうでない場合は0として、年間合計を計算すればよい。
 セルC8に式を作成し,C19までコピーすることを考え、B4は絶対参照にします。
   セルC8の式: =IF(A8<$B$4,B8, 0)
 セルC20に年間合計を算出します。この数値が表示する月の前日までの今年の日数です。

6 西暦1年1月1日からの経過日数を求める。
 わかっていることは、西暦1年1月1日が月曜日だということだけです。

 表示したい月の前月までに西暦1年1月1日から何日経過したかを求め、これを1週間の7日で割って、その余りにより、曜日を確定することができます。

 西暦1年1月1日からの指定月の1日までの経過日数を求めます。今年の、前月末までの経過日数はセルC20に計算されているので、指定年の前年末までの経過日数を求めます。うるう年を考慮しなければなりませんが、いったんうるう年を考えずに経過日数を計算し、その後、指定年までに何回うるう年があったかを計算して、合計します。
 
6-1 うるう年を考慮しない経過日数を求める
 セルB22にうるう年を考慮しない経過日数を求めます。
  (年数-1)*365+今年の日数+1:=(B3-1)*365+C20+1

6-2 うるう年を考慮した経過日数を求める
 セルB22に求めた、うるう年を考慮しない経過日数に、指定年の前年までに何回うるう年があったかを計算して合計します。

 うるう年は4年に一度巡ってくる、ただし、100年に一度はうるう年ではないので差し引く、しかし、400年に一度はうるう年である。

 これを、式で示すと次のようになります。この場合、4、100、400で割った剰余は切り捨てます。(int関数、または、rounddown関数)

B22+INT((B3-1)/4)      4年に1度のうるう年
     −INT((B3-1)/100)    100年ごとに平年
     +INT((B3-1)/400)    400年ごとにうるう年
続けて書くと、
B22+INT((B3-1)/4)−INT((B3-1)/100)+INT((B3-1)/400)

これで、指定した年月の1日の、西暦1年1月1日からの経過日数が求められました。うるう年の日数も考慮されています。

7 指定月の1日の曜日を求める
 セルB24に指定月の1日の曜日を求めます。

 セルB23に算出された指定月の1日までの経過日数を7で割って剰余を求めます。
 割り切れれば剰余は0で日曜日、剰余が1であれば月曜日となります。剰余を求める関数はMOD関数です。

   =MOD(B23,7)


8 指定月の最終日を求めておく

 カレンダーはその月の最終日で終わっていなければなりません。
 2月に30日があったり、4月に31日があってはいけません。

 セルD8〜D19に指定月の最終日を表示します。

    =IF($B$4=A8,B8,0)

 さらに、セルD20にD列の合計を計算しておきます。
 この合計に意味があるわけではなく、後のステップで、月の最終日を見るとき、このセルを見に来ればいいということになり、式の作成が楽になります。

9 カレンダーを作る

  これで、必要なデータが揃いましたのでカレンダーを作ります。
 考え方は次のとおりです。

G列からI列に次の表を作ります。


 省略しています。
   
 



※セルI6は計算に使うので結合しないでください。

(1)指定月の1日より前の日は何か文字(◆)を表示する。または空白
(2)指定日の1日の曜日から日を表示する。
(3)2日以降の日は前の日に1を加えた日とする。
(4)日が指定月の日数より大きくなったら、何か文字(◆)を表示する。


セルI7に次の式を作成し、セルI44までコピーします。
=IF($B$24>=G7,IF($B$24=G7,1,""),IF(I6="","",IF($D$20<=I6,"",I6+1)))

“セルB24に表示されている曜日コードがセルG7の曜日コードと合致するか大きい場合で、合致する場合、セルI7の値は「1」、そうでなければ空白とする。”
これを式で表示すると、
   IF($B$24>=G7,
    IF($B$24=G7,1,” ”),

3,4行目は1行目の条件に合わない場合、すなわち、B24の値がG7の値より大きい場合の計算です。
 “もし、セルI6(I7の上のセル)が空白であれば空白、I6の値が当月の最終日であるセルD20の値より大きければ空白、小さければ1つ上のセルの値に1を加える。”
これを式で表示すると、
   IF(I6=” ”, ” ”,
    IF($D$20<=I6,” ”、I6+1)))

【仕上げ】
 これでワークシートに万年カレンダーが出来上がりました。
しかし、見栄えが悪く、実用的ではありません。

計算はこのワークシートを生かし、最初に作っておいたカレンダーのワークシートに結果を表示するよう、データをリンク貼り付けします。




セルB8の式: =作業用!$I7
   C8は $I8になります。
セルB9の式: =作業用!$I14

これを繰り返して、カレンダーが完成します。
H13のセルには =作業用!$I48 の式が入るはずです。


このワークシートを表示し、“年”と“月”を入力すれば、作業用のワークシートで必要な事項を計算し、この表に日付が表示されるようにします。

このワークシートに入力した“年”と“月”を“作業用”のワークシートのセルB3とB4にリンクさせておきます。

作業用ワークシートのセルB3の式: =カレンダー!D4
作業用ワークシートのセルB4の式: =カレンダー!F4

 それぞれの日付の枠は、セルI7〜I48にリンクさせておくとカレンダーが出来上がるという仕組みです。
「条件付書式」を使って体裁を整え、さらに、ワークシートやセルの保護をかけておけば完璧です。

【おまけ】
西暦年数が入力されたら、和暦も同時に表示されるようにしておきます。

セルD5には次の式が入っています。
   =VALUE(D4&"/"&F4)
セルの書式設定で元号の表示になるよう、設定しています。

【反省】
情報処理技術者試験では作業用のシートが横長に作られています。
作業用シートからカレンダーシートにデータをリンクする際、このほうが楽でした。

このカレンダーは祭日を考慮していません。そこまで考えるとまたむずかしいですね。

そのほかにも、いろいろな機能を組み込みたいのですが、それは後日にしましょう。
こうしてみると、ネットなどで公開されているフリーのカレンダーソフト、すばらしいですね。


【関連商品】


  
















日本最大級の保険比較サイト-保険市場

このページのTOPへ   ホーム > 面白エクセル > 万年カレンダー
 2009.9 Restart
 Copyright お仕事に活かす・・・基礎から学ぶエクセル講座 All rights reserved