Excel業務カイゼンブログ

Excelの日付関数を使うと、毎月の万年カレンダーを作成できます。実際に次のようなカレンダーを作成してみましょう。操作するファイルはこちらからダウンロードできます。

万年カレンダーのもとのデータ

どんな万年カレンダーにするか仕様を考えます。

• 毎月のカレンダーなので、年と月を指定したらその年月のカレンダーを表示するようにします。
• 祭日を休日表示します。
• 曜日はExcelのWEEKDAY関数で算出します。

ダウンロードしたファイルは、2018年の祭日の一覧です。このデータを使って祭日を休日と判断させます。では、新規ワークシートを追加して、作業を始めましょう。

セルA1に「2018」、セルB1に「年」と入力します。セルA2に「1」、セルB2に「月」と入力します。

セルA3に「日」と入力し、G3までオートフィルし、「日」から「土」の曜日の一覧を作成します。

YEAR関数

YEAR関数は日付データから西暦年の数値を求める関数です。書式は次の通りです。
=YEAR(日付)
2018年1月1日の西暦年の数値求めるときは、次の式になります。
=YEAR("2018/1/1") 2018という答えになります。
今日の西暦年の数値求めるときは、次の式になります。
=YEAR(TODAY())
今回は、条件付き書式を使って、カレンダーの日付が表示する年月ではない場合、表示の色を変える判定するのに使います。

MONTH関数

MONTH関数は日付データから月の数値を求める関数です。書式は次の通りです。
=MONTH(日付)
2018年1月1日の月の数値求めるときは、次の式になります。
==MONTH("2018/1/1") 1という答えになります。
今日の月の数値求めるときは、次の式になります。
=MONTH(TODAY())
今回は、条件付き書式を使って、カレンダーの日付が表示する年月ではない場合、表示の色を変える判定するのに使います。

WEEKDAY関数

カレンダーを作成するときに便利な、曜日に対する数値を求めるWEEKDAY関数があります。日付から曜日に対応する数字に変換する関数です。書式は、次の通りです。
=WEEKDAY(曜日を調べる日付,どの曜日をスタートにするか)
どの曜日をスタートにするかは指定しなくてもよくその場合の結果は、日曜を1、土曜が7になります。どの曜日をスタートにするかの指定は以下のような数字で指定します。
1：結果は日を1として土を7
2：結果は日を0として土を6
3：結果は月を1として日を7
11：結果は月を1として日を7
12：結果は火を1として月を7
13：結果は水を1として火を7
14：結果は木を1として水を7
15：結果は金を1として木を7
16：結果は土を1として金を7
17：結果は日を1として土を7
2018年1月1日の曜日に対する数字を求める場合は次の式になります。
=WEEKDAY("2018/1/1")
どの曜日をスタートにするかは指定していないので、日曜が1となるはずです。2018年1月1日は月曜なので結果は、2となります。

カレンダーの始まりの日付を求める

セルA4には、カレンダーに表示する始まりの日付を表示します。その前に、セルA1、A2で指定されている年月の1日の日付を考えます。3つの数字から一つの日付を求めるDATE関数を使います。式は次の通りです。
=DATE(A1,A2,1)
DATE関数については、こちらの記事で詳しく解説しています。今、セルA1、A2で指定されている2018年1月の1日は月曜なので、その前の日曜である、2017年12月31日から始まるカレンダーになります。この日付は、カレンダーで指定する1日の日付から、1日の日付をWEEKDAY関数で求めた曜日番号を引いて、1を足すことで求まるのです。計算式にすると次のようになります。
=DATE(A1,A2,1)-WEEKDAY(DATE(A1,A2,1))+1
この式をセルA4に入力しましょう。

始まりの日付以降の日付の作成

始まりの日付が求まったら、次々と１日ずつ増える式で計算していけば日付が求まります。セルB4に「=A4+1」を入力し、この式をセルG4までコピーします。

セルA5に、前の行の最終日に１つ足す「 =G4+1 」の式を入力します。

セルB5に、「=A5+1」を入力し、この式をセルG5までコピーします。

セルA5からG5に出来たパターンは、次の週でも同じなので、1月のカレンダーに必要な週の数だけコピーします。1月は最大で31日あり、その月の1日が土曜だった場合、1週間7日のパターンが最大6行必要です。今は2行出来上がったので、残りの4行分、A5からG5の範囲をA6からG9までにコピーします。

書式の設定

A3からA9までの範囲を赤色のフォントに、G3からG9までの範囲を青色のフォントにしましょう。

A4からG9までの範囲の日付の表示形式を日のみ表示にします。A4からG9までの範囲を選択し、Ctrl∔+1のショートカットキーで［セルの書式設定］の［表示形式］タブで、［ユーザー設定］で［d］に指定し、[OK]ボタンをクリックします。

また、適宜、中央揃えやセルの塗りつぶし、罫線を設定します。

祭日を赤いフォントにする

祭日は「2018祭日一覧」シートに記録されているので、そのデータを使って赤いフォントにする条件付き書式を設定します。A4からG9までの範囲を選択し、［ホーム］タブの［条件付き書式］の下向き三角をクリックし、［新しいルール］をクリックします。

［数式を利用して、書式設定するセルを決定］をクリックし、［次の条件を満たす場合に値を書式設定］に次の数式を入力します。
=COUNTIF('2018祭日一覧'!$A:$A,A4)>0

2018祭日一覧のA列の中にその日付の個数が0個以上（つまり存在していたら）、という条件を設定しました。［書式］ボタンをクリックします。

［フォント］タブの［色］を赤にし、［OK］ボタンをクリックします。

［OK］ボタンをクリックします。

祭日が赤い文字になりました。

最終週がその月ではなかったら表示しない

カレンダーを6週間分作成しましたが、最後の週は、すべて翌月が表示される月があります。この場合は、カレンダーを表示しません。つまり、A9からG9の範囲は、セルA9の年月がセルA1とA2の年月と違う場合、フォント、塗りつぶし、罫線の色を白にします。A9からG9までの範囲を選択し、［ホーム］タブの［条件付き書式］の下向き三角をクリックし、［新しいルール］をクリックします。［数式を利用して、書式設定するセルを決定］をクリックし、［次の条件を満たす場合に値を書式設定］に次の数式を入力します。
=NOT(AND(YEAR($A$9)=$A$1,MONTH($A$9)=$A$2))

A9からG9の日付の判定は、先頭のA9が指定した年月かのみを確認すればよいです。YEAR関数、MONTH関数を使って、年と月、両方で一致しているかをAND関数でチェックして、最終的にNOT関数で一致しない場合に反転させた条件にしています。［書式］ボタンをクリックします。

［フォント］タブの［色］が白、［塗りつぶし］が白、［罫線］が白を選んで左右と下のみ指定し、［OK］ボタンをクリックし、もう一度、［OK］ボタンをクリックします。

最後の1行が白になり、表示しなくなりました。

セルA2を9に書き換えましょう。2018年の9月は6週目まであるので、最終行が表示されます。

当月ではない文字を灰色のフォントにする

カレンダーの日付がその月ではない場合は、目立たせないように灰色表示します。最終週がその月ではなかったら表示しない時と同じ計算式を使いますが、日付をチェックするセルは絶対参照のA9ではなく、そのセルがセルA1の年とA2の月と一致しているかを調べることになります。A4からG9までの範囲を選択し、［ホーム］タブの［条件付き書式］の下向き三角をクリックし、［新しいルール］をクリックします。［数式を利用して、書式設定するセルを決定］をクリックし、［次の条件を満たす場合に値を書式設定］に次の数式を入力します。
=NOT(AND(YEAR(A4)=$A$1,MONTH(A4)=$A$2))

［書式］ボタンをクリックし、［フォント］タブの［色］を灰色に指定し、［OK］ボタンをクリックし、もう一度、［OK］ボタンをクリックします。

当月ではない日付は灰色表示になりました。

条件付き書式の優先順位を変更する

セルA2を1に書き換えましょう。2018年の1月は6週目までないので、最後の週は非表示になります。

しかし、翌月の日付が灰色で表示されているかもしれません。条件付き書式の設定順によっては、このような表示になります。今、条件付き書式は、祭日を赤く、最終行が当月ではなかったら非表示、日付が当月ではなかったら灰色の文字という順で条件付き書式を設定しました。設定した条件付き書式の優先順位は新しく設定した順で表示されます。つまり、現段階の最優先は日付が当月ではなかったら灰色の文字なので、非表示の設定はされません。そこで、優先順位を変更する必要があります。A4からG9までの範囲を選択し、［ホーム］タブの［条件付き書式］の下向き三角をクリックし、［ルールの管理］をクリックします。

設定されているルールの一覧が表示されるので、2番目の非表示になっている条件付き書式をクリックして、上向きの三角をクリックします。

すると、非表示の優先順位が上に移動しますので、[OK]ボタンをクリックします。

最終行が灰色の文字ではなく、非表示になりました。

まとめ

今回は日付関数と条件付き書式を使った万年カレンダーを作りました。どんな関数でも単体で使うと応用範囲が限られますが、このように全体を通して使ってみると、できることがとても広がることがわかります。また、計算式だけではなく、日付関数を使った条件付き書式機能を組み合わせることで、さらに見やすい表の書式を自動で設定できることもおわかりいただけたのではないでしょうか。ぜひ、いろいろな機能と計算式を組み合わせていろいろなものを作ってみましょう。