【Excel】祝日も自動で表示するカレンダーを1から作る!(画像付きガイド)

【アフィリエイト広告を利用しています】

Excel(エクセル)の数式や条件付き書式を使って、自動で表示されるカレンダーを作る手順を紹介します。
祝日も自動で表示し、土日祝日は色付けされるカレンダーです。

【Excel】カレンダーに祝日を入れる方法
自動表示カレンダー
サンプル画像
いのこ
いのこ

カレンダー作成手順

カレンダーの作成手順はコチラです。

  1. カレンダーの基本のレイアウトを考える
  2. 数式で日付が自動で表示されるようにする
  3. 書式設定で”日”のみが表示されるようにする
  4. 祝日データをつくる
  5. 数式で祝日が自動表示されるようにする
  6. 条件付き書式で、色を付ける
  7. 体裁を整える

1. カレンダーの基本レイアウト

まず、A1セルに年(2024)、B2セルに月(10)を入力します。
そして、その下A2に「日」(月曜スタートのカレンダーは「月」)と入力し、オートフィルで曜日を「土」まで入力します。

【Excel】カレンダーの作り方ガイド
いのこ
いのこ

曜日は「日曜日」と記述しても、「日曜」と記載してもかまいません。
「Sun」と入力すれば、「Sunday」と認識され、「Mon」、「Tue」と入力してくれます。お好みで記述してください!

このままだと、2024と10に年、月が表示されないので、セルの書式設定で自動表示されるようにします。

【Excel】カレンダーの作り方ガイド

1. A1セルを選択する
2. 「セルの書式設定」を開く
3. 「ユーザー定義」を押す
4. 種類に「#年」と入力する

あのこ
あのこ

年、月を表示しないのもありですし、
英語表記にするものいいと思います!
その場合は「#月」と入力する個所を「mmmm」とすると「January」
「mmm」と入力すると「Jan」と表示してくれます。

枠線や表示位置はこの段階ではいじらなくて大丈夫です!

2. 数式で日付が自動で表示されるようにする

1. まず、日曜日の下のA3セルに、下記の数式を入力します。

=DATE(A1,B1,1)+(1-WEEKDAY(DATE(A1,B1,1)))
【Excel】カレンダーの作り方ガイド
いのこ
いのこ

数式の説明をすると、
=DATE(A1,B1,1)は「A1年B1月1日」という事。(つまり、ほしいカレンダーの月の最初の日なので、今回は2024/10/1
WEEKDAY(DATE(A1,B1,1))は「A1年B1月1日」の曜日を表す数字を求めています。日曜日が1、月曜日が2…(2024/10/1の場合は3の火曜日)

=DATE(A1,B1,1)+(1-WEEKDAY(DATE(A1,B1,1)))

=2024/10/1 +(1-3)=2024年10月1日の2日前=2024年9月29日

=A3のセルには2024/9/29を表示するという数式となります!

2. つぎに、B3以降の日付を入力します。前日に日付に1を足すことで、次の日付が表示されます。

【Excel】カレンダーの作り方ガイド

3. 翌週も同様に数式で入力します。

【Excel】カレンダーの作り方ガイド

できた表がこちら。ごちゃごちゃした表が出来上がりました!(次ですっきりさせますよ!)

【Excel】カレンダーの作り方ガイド

3. 書式設定で ”日” のみが表示されるようにする

日付が入力されているセルを全て選択し、セルの書式設定を開きます。
ユーザー定義で「d」と入力すると、「2024/10/10」 が 「10」に表示が変わります。
セルの書式設定について簡単にまとめた記事はコチラ

【Excel】カレンダーの作り方ガイド
いのこ
いのこ

はdayという意味です。
日付データ(年・月・日)の中で “日” のみ表示する、という書式設定となります。

4. 祝日データをつくる

カレンダーに祝日を表示する為には、祝日が記載されたデータが必要になります。
祝日は、毎年決まっている祝日(元旦など)もありますが、振替休日や海の日などはその年により異なるからです。
祝日データはさまざまなサイトで配布してくれていますが、実際に祝日を決めている内閣府が、配布しているデータが一番正確(だと思う)ので、そちらのデータを利用する方法を紹介します。

国民の祝日は、内閣府が次年の分までを発表しています。
csv形式で1955年から最新の祝日までをまとめたデータを配布してくれているので、そちらをダウンロードすると、古いカレンダー(自分の誕生した月など)を作る事も簡単にできます。

<< 国民の祝日について – 内閣府 (cao.go.jp) >>ホームページはこちら

ホームページに飛んで、下にスライドしていくと、上記の表示が現れるので、クリックしてください。すると、右上にポップアップ窓が出てくるので、ファイル開くをクリックします。

【Excel】カレンダーに祝日を入れる方法

開いたエクセルファイル「syukujitsu」のタブを右クリックし「移動またはコピー」を選択、
移動先のブック名で対象のファイル「カレンダー」を選択し、「末尾へ移動」を選んでOKを押します。

いのこ
いのこ

今回はデータをダウンロードしましたが、祝日データは自分で作成することも可能です。A列に日付、B列に祝日名を入力すれば、今回のカレンダーは動作します。

カレンダーシートの隣に祝日一覧のシートを追加できたので、そのデータに名前を付けます。
まず、ABの列を選択し、数式タブの「名前の管理」を押します。

【Excel】カレンダーに祝日を入れる方法

名前を適当に付けて、(今回は「祝日」とします)他はさわらずOKを押します。

【Excel】カレンダーに祝日を入れる方法

これで、祝日という名前のデータが出来ました。
このデータを使って、祝日を検索する数式をA4セルに入力します。

5. 数式で祝日が自動表示されるようにする

次は、実際に数式を入力して祝日を表示します。
そのためにまずは、祝日を表示する行をそれぞれの日付の下に追加しましょう。

【Excel】カレンダーに祝日を入れる方法
いのこ
いのこ

行の挿入は、Ctrl で可能です。
例えば、5行目と6行目の間に行を挿入したい場合は、
6行目を選択した状態で Ctrl を押すと挿入できます。

祝日を表示する為の数式はコチラです。↓↓

=IFERROR(VLOOKUP(A3,祝日,2,FALSE),"")

一番左上の日付(ここでは29日)の下のセル(A4)セルに数式を入力します。
そして、入力した数式を他のセルにコピーする為に、A4セルをコピーし、他の祝日を入力したいセル(日付の下の空白セル)に数式のみ貼り付けします。

【Excel】カレンダーに祝日を入れる方法
いのこ
いのこ

数式の説明をすると、
VLOOKUP(A3,祝日,2,FALSE)
A3セルの値が、「祝日」と名前を付けたデータの中に完全一致であるかを(FALSE)検索し、見つけたらその2列目(隣)の値を返します。
そのままだと、値が見つからない場合に「#N/A」と表示されてしまうので、
IFERROEを付けて、エラーだったらセルに何も入力しない(””)を付けています。

これで、祝日がカレンダーに表示されるはずです。(黒字で!)

6. 条件付き書式で、色を付ける

次は、祝日とその名前を赤色に変更する為に、条件付き書式でルールを作成します。
その前に、日曜日と土曜日の色を変更しましょう。
いのこは、日曜日は赤、土曜日は青にしましたが、お好きな色に設定してください。
この後、祝日は赤、カレンダーの月以外の月はグレーに変更する条件付き書式を追加します。

対象月以外の日付をグレーに変更する条件付き書式

条件付き書式は、数式で指定したルールを適応することで、対象月以外の日付の書式を変更することができます。<条件付き書式でできる事についてまとめた記事はコチラ

1. まずは、日付が表示されているセルを全選択。
2. 条件付き書式で、「新しいルール」を選択する。
3. 「数式を使用して、書式設定するセルを決定」を選択し、
4. 下記の数式を入力し、書式設定で文字色をグレーに設定する。

=MONTH(A3)<>$B$1

いのこ
いのこ

数式の説明をすると、
=MONTH(A3)<>$B$1
は「A3に入力されている日付の”月”が、B1に入力されている”月”と異なる」
という意味の数式です。
「A3」は対象セル。日付が入力されているセルです。
「B1」は絶対参照。対象セルが変更になっても絶対参照のセルは変更しません。

例えば「A3」に入力されているのは、2024/9/29。これの月は9月で、「B1」には10月が入力されているので、ルールが適応され、文字がグレーになります。

祝日を赤色に変更する条件付き書式

カレンダーの全てのセル(A3~G12)を選択し、条件付き書式で「新しいルール」をクリック。
「数式を使用して~」を選び下記の数式を入力します。

=COUNTIF(祝日,A3)>0
【Excel】カレンダーに祝日を入れる方法
いのこ
いのこ

=COUNTIF(祝日,A3)>0

COUNTIF関数は範囲の中に検索する条件と一致するセルを探して、その個数を返す関数です。
祝日データの中にA3セルの中身があったら1となる為、0より多い、つまり祝日である場合に、このルールを発動させよ、という意味になります。

これで、祝日が無事赤色表示されたのですが、このままだと、当月以外の日にちも赤色で表示されてしまいます。(例えば、5月のカレンダーにした場合、4/29昭和の日は当月以外なのでグレーにしたいのに赤色表示されてしまいます)

【Excel】カレンダーに祝日を入れる方法

原因は2つ。
・祝日がカレンダーの月以外だったら、祝日名をグレーにする書式設定が追加されていないため
・条件付き書式は、ルール一覧いちらんの上の方に表示されているルールが優先されるため

祝日名をグレーに変更する条件付き書式

まずは「祝日名をグレーにする書式設定」です。(分かりやすく、5月のカレンダーでやります)

  1. 昭和の日(4/29のセルB4)を選択
  2. 条件付き書式で『新しいルール』を選択する
  3. 「数式を使用して、書式設定するセルを決定」を選択
  4. 数式【=MONTH(B3)<>$B$1】を入力する
  5. 書式設定で文字色をグレーに設定する
  6. 「OK」を押して、4/29の昭和の日がグレーになるか確認する
【Excel】カレンダーに祝日を入れる方法
いのこ
いのこ

【=MONTH(B3)<>$B$1】は、
B3に入力されているつきがB1より多いもしくは少ない(つまり、異なる)場合にルールを適用する数式です。
B3の日付は2024/4/29で、つきは4。カレンダーは5月(B1)のものなので、ルールが適用されグレーになります。

祝日名がグレーになるのを確認したら、条件付き書式の範囲を広げます。

  1. 条件付き書式の「ルールの管理」を選択する
  2. 先ほど作成したグレーになるルールの適用先を確認する
    (ルールが見つからない場合は、「書式ルールの表示」のところを「このワークシート」に変更してみてください)
  3. 矢印の個所を押すと、セルの選択ができるようになるので、カレンダー内の祝日名が入力される予定のセルを全て選択して適用させる
    Ctrlを押しながらA4~G4、A6~G6、、、と選択)
  4. 完了!
【Excel】カレンダーに祝日を入れる方法

これで、カレンダーの月以外の日の祝日名がグレーに自動で変更されるようになります。

ルールの管理を調整する

最後に、条件付き書式の適用順序を入れ替えて、文字色を確認します。

条件付き書式の「ルールの管理」を開くと、ルールがいくつかあると思います。
文字を赤色にするルールよりも、文字をグレーにするルールを優先させればOKです。

【Excel】カレンダーに祝日を入れる方法

条件付き書式の「ルールの管理」で、赤色にするルールを を押して下に下げるだけです。

これで、条件付き書式の設定は完了です!他の月に変えても問題なく表示されるか、確認してみてくださいね!

7. 体裁を整えて完成!

最後に自分好みに表を整えます。
文字のサイズやフォントを変更し、枠の色を変更したりして、整えましょう!

まとめ

いかがでしょう!もし、シートを追加したくない、という方は別の記事で、ネットから自動で祝日を表示する方法を紹介していますので、そちらをご覧ください!