万年カレンダー(excel)

エクセルの日付関数を利用して西暦を入力すれば年間カレンダーが出来るシートを作ってみました。

エクセルでは1900年1月1日以降しか対応出来ないので、万年は言い過ぎですが、複数年対応可能なカレンダーです。

西暦の入力部分です。

セルN2に2021と数値が入ってます。この数値を西暦として利用します。

制作時には、絶対参照を使って効率よく作成しましたが、コピー利用に便利なように、絶対参照は相対参照に変更しました。変更と言っても、必要範囲内の”$”記号を””に置換して消しただけです。

”令和3年”と表示されている結合されたセルのDATE関数は、N2を参照して、2021年1月1日の日付を表現しています。「セルの書式設定」で [$-ja-JP-x-gannen]ggge”年” という書式を与えてあります。日付のうち「和暦の年」だけを表示する指定です。

月の表示部分です。

セルN2を参照して2月のセルには2021年2月1日としてDATE関数を配置してあります。書式は m”月” として、月だけ表示させています。セルをコピーして他の月をセットする時は、N2の参照を絶対参照にしてコピーします。セルの中のN2の文字にカーソルを重ねてF4キーを押すと、$の文字が付きます。F4を何度も押すと、$の付く文字が変わります。コピーしても$の付いた次のセル番地の要素(Nとか2の文字)は変化しません。今回は$N$2としてコピーすれば、どこにコピーしても$N$2の文字は変化しません。同じセルを参照させたい時に便利です。

セルの書式設定です。日付が入っているセルでも、表示の設定で月だけとか年だけと表示を選択できます。

ユーザー設定に色々入ってます。これらは自分で入力します。デフォルトですべて入っているわけではありません。種類の欄に式を入力するとサンプル欄に実際の表示例が見えるので、色々試しながら入力できます。

各月の日付を設定します。それぞれの月の1日の曜日をWEEKDAY関数で求めます。

その値をもとに、一番上の行の設定を行います。下図からは作業中の様子です。

セルG3は1月と表示されている結合されたセルです。結合されたセルは左端に相当するセルの番地が参照先になります。

WEEKDAY(G3) と指定すると、G3セルは2021年1月1のDATE関数です。その曜日に応じて1 (日曜) ~ 7 (土曜)の数値を返してくれます。上図では最初の週の日曜日の日付(27)を求めています。日曜日の欄は書式設定で赤字にしてあります。

2021年1月1日は金曜日なので WEEKDAY(G3) は6です。なので日曜日は5日前( WEEKDAY(G3) -1)なので、「G3- (WEEKDAY(G3) -1)」が日曜日の日付です。カッコを外して「 + G3- WEEKDAY(G3) +1 」となります。

関数を使うのは、この最初の日曜日だけです。2月は以下の通りです。

$がついてない相対参照なので、コピーすればG3の参照がP3に変わってくれます。

表などを設計する時、同じものが並ぶ時は、コピーで複製出来るように考えて設計すると全体を効率よく作成できます。まあ、最初は試行錯誤なので、ある程度うまくいったら制作しやすいように工夫してます。

毎月の最初の日曜日欄以外は以下のように単純な加算式を入力します。

月~土は左のセルに+1です。日曜日は上のセル+7です。一つ入力したらあとはコピーで他も埋めていきます。月->土(下左図)、日は下へ(下中図)。月ー土も下へ(下右図)一気にコピー。

図のように、毎月6週分のスペースを用意します。前後の月の日付も表示されてます。次は余分な他の月の日付を消します。

ホームの「条件付き書式」で、該当部分で他の月の日付の文字を白にして見えなくします。

適用する範囲を選択して、ルールの管理をクリックします。

新規ルールをクリック。

「数式を使用して、書式設定するけるを決定」をクリックし、「=month(」まで入力したら、カーソルを移動し、日曜日の最初欄(上図では27)をクリック。

「=month($G$5」とセルが絶対表示で入るので、上図のように修正して条件式を入れる。「=month(G5)<>1」と。最初の=を忘れると、文字列になるので注意。式の意味は「G5の月の値が1以外」という条件。これを範囲内のセルに対して自動で設定してくれます。コピーと同じで、絶対参照、相対参照は機能します。

条件を入力したら、書式をクリック。

色から白を選択

色の欄の自動の文字が消えて、白になる。このままOKをクリック。

プレビューが白になる。OKをクリック。

適用先が選択している範囲になっている。確認は、適用先欄の右の上矢印をクリックする。

確認できたら、上図のダイアログの右端の青い部分をクリックすると戻る。

まだ1月以外も表示されている。適用をクリック。

1月以外の月の日付が白くなって見えなくなった。ダイアログのOKをクリックして「条件付き書式ルール」を終了する。

これで1月のカレンダーは完成した。2月以降のレイアウトは出来ているので、

後は、日付部分をコピーする。1月からコピーすると、1月の日付だけ表示されていると思う。

「条件付き書式ルール」 から「ルールの管理」をクリック。

「ルールの編集」をクリック

前の書式が残っていて、範囲は新しい範囲になっている。条件の数値を、1月から2月なので1を2に変更(下図)。

変更後、OKをクリック。適用をクリックすると2月のカレンダーも完成。

操作を繰り返して、1年分のカレンダーを作成すれば完了。

最初の完成図では、日曜日の横にその年の何週目かの数値が入れてある。不要なら入れなければ良い。

その行が何週目かは下図の通り

(日曜日の日付)-(1月最初の日曜日の日付)を7で割り+1する。書式は標準。書式を日付にすると、31を超える表示ができなくて1に戻ってしまうので注意。

(日曜日の日付)の差は必ず7の倍数なので、7で割っても必ず整数になる。最初が0週目ではなく1週目なので+1。

6週のうち、2月は5週目と6 週 目、他の月は6週目にはIF文を追加して、その週が空欄の時に表示を消します。原理は、日曜日の日付がその月の日付で無ければ文字を表示しないようにします。

日付文字を、「条件付き書式ルール」 を使わないで、IF文で他の月の日付を消す事も可能です。セルの内容が長くなるので、今回は 「条件付き書式ルール」 を併用しました。

calender.xls(zip) をダウンロードする。