【Excel Tips】土日祝祭日の日付に自動で色を付ける

この記事の要点

使う関数やスキル

  • Excelのシートのカレンダーに、土日と祝日の行に、それぞれ別の色をつける。COUNTIF関数を使用
  • 大の月と小の月、閏年も自動的に表示を変える。DATE関数、IFS関数、EOMONTH関数を、TEXT関数を使用
  • 今年と来年の祝祭日を、内閣府のWebサイトにあるCSVファイルをPower Queryを使って読み込む。(年に一度更新ボタンで更新する)

【完成版のカレンダー】

こちらからダウンロードできます。

日付の自動設定

書式設定・入力規則

  1. 上の完成図をして参考に、1ヶ月分のカレンダー罫線を引いて下さい。
  2. A4~A34を範囲選択し、ユーザー定義で「d」を指定します。
  3. A2セルにユーザー定義で次の書式を設定します。
    ###0"年"
  4. 同様に、B2セルに次の書式を設定します。
    #0"月"
  5. A2セル、B2セルに次のデータの入力規則を設定し、入力時メッセージとIMEを設定します。

    【A2セル】

【B2セル】

関数を使った計算式を入力

A4セルに、=DATE(A2,B2,1) つづいて
A5セルに、=IFS(A4=EOMONTH($A$4,0),"",A4="","",A4<>EOMONTH($A$4,0),A4+1)
の計算式を入力し、A5セルをオートフィルボタンで31日までドラッグします。(※EOMONTH関数の$A$4は"絶対参照"、他の"相対参照"と正しく区別して入力して下さい。

B4セルに、=TEXT(A4,"aaa") と入力し、オートフィルボタンで下までドラッグします。これで曜日が表示されます

IFS関数はExcelの2019以降で使用できます。Excel 2016以前のバージョンではIF関数をネストにして使います。

=IF(A4=EOMONTH($A$4,0),"",IF(A4="","",IF(A4<>EOMONTH($A$4,0),A4+1,"")))

内閣府のWebサイトから「国民の祝日・休日」データを取得

1955年からの祝祭日が、内閣府の「国民の祝日について」ページにあります。

下にスクロールしていくと、

昭和30年(1955年)から令和7年(2025年)国民の祝日(csv形式:20KB)

CSVファイルにリンクが貼られています。

リンクをコピー

上のCSVファイルの箇所で右クリックし、「リンクのアドレスをコピー」します。

STEP
1

Powerクエリを起動する

「データ」タブ、「Webから」を選択し、ダイアログボックスにコピーした「リンクのアドレス」を貼り付けます。

https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv

「OK」ボタンをクリックします。

初回だけ「webコンテンツへのアクセル」ダイアログボックスが出ます。

「匿名」にして適用対象レベルを上のCSVファイルにし、「接続」をクリックします。
(https://www8.cao.go.jp/でも構わない)

STEP
2

Power Queryエディタ

CSVファイルの内容が表示されるので、「データの変換」ボタンをクリックすると、PowerQueryエディタが起動します。

STEP
3

日付フィルター

左側の列の項目名右にある▽をクリックし①、「日付フィルター」②、「カスタムフィルター」③とクリックします。

「行のフィルター」ダイアログボックスが出るので、図のように「年単位」-「今年」、「◎また」「年単位」-「来年」を選びます。「および」を選ぶと祝日は表示されないので注意してください。

STEP
4

閉じて次に読み込む

「OK」ボタンをクリックすると、今年の祝日から表示されます。


「ホーム」タブの「閉じて読み込む」の下半分をクリックしてください。(上半分だと新しいシートができます)
「次に読み込む」をクリックします。

「データインポート」ダイアログボックスが出るので、「既存のワークシート」を選び、挿入したいセル番地(例ではF4セル)を選択して「OK」ボタンをクリックします。

STEP
5

クエリと接続ウインドウ、名前を付ける

「OK」ボタンをクリックすると、シートに祝日のリストが表示されます。 
「クエリと接続」ウインドウも表示され、「40行が読み込まれました」と祝日の日数が記されています。

次に、祝日の日付列の項目名を除いた全体(例ではF4:F43を範囲選択して、「名前ボックス」に「祝祭日」と入力し、エンターキーをクリックします。

STEP
6

祝祭日の更新

内閣府の祝祭日のデータは、例年2月に翌年分が更新されます。

2月を過ぎた頃、祝祭日テーブルで右クリックし、「更新」を選んで下さい。

また「クエリ」タブから抽出範囲の変更もできます。それにはPowerQueryの知識が必要です。



条件付き書式で、土、日曜日に色を付ける

新しい書式ルール

カレンダーの項目名を除いた全体(例ではA4:C34)を範囲選択します。

「ホーム」タブ「条件付き書式」「新しいルール」と選択し、「新しい書式ルール」で、「数式を使用して、書式設定するセルを決定」を選びます。

STEP
1

土曜日の色塗り

「次の数式を満たす場合に値を書式設定」に次の数式を入力します。
=TEXT($A4,"aaa")="土"

条件式がTrueの場合に書式が有効になります。

A4の位置でF4キーを3回クリックして複合参照(Aの前だけ$が付く)にしておくのがポイントです。

「書式」ボタンをクリックして書式設定に進みます。

「セルの書式設定」で、「塗りつぶし」①、好みの「色」②を選び「OK」をクリックします。

STEP
2

日曜日の色塗り

範囲選択を維持したまま、=TEXT($A4,"aaa")="日" と変更し、好みの色を選んで下さい。

STEP
3

土日は完成

土曜と日曜が塗り分けられました。

STEP
4

COUNTIF関数で祝祭日を判断する

次に「祝祭日」をの判断をします。しかし、Excelには祝祭日を判断する関数はありません。
そこでCOUNTIF関数を使い、先に作った「国民の祝日・休日月日」リストから探します。

ある日付がこの中にあるかどうかを数えるのです。
数えた結果が「1」であれば、その日付は祝祭日であると判断できます。

条件付き書式を設定する

土日の設定と同じく、項目名を除いたスケジュール表の全体(例ではA4:C34)を範囲選択します。

①「ホーム」タブの「条件付き書式」を選び、「新しい書式ルール」で、①「数式を使用して、書式設定するセルを決定」を選びます。

②「次の数式を満たす場合に値を書式設定」に次の数式を入力します。
=COUNTIF(祝祭日,$A4)=1

名前範囲「祝祭日」を使います。

ここでもA4の位置でF4キーを2回クリックして複合参照(Aの前だけ$が付く)にしておくのがポイントです。

③「書式」をクリックして祝祭日の書式を任意に設定してください。

以上で完成です。

年末年始休業日や会社の創立記念日を入れてもよいですね。

このように3つの条件がそれぞれOR条件で設定されています。

隔週で曜日を設定する方法

第1と第3金曜日に予定を入れたいなど、隔週でカレンダーに記入するってこと、結構ありますよね。

その方法はこちらで紹介しています。土日祝祭日と併用すれば完璧ですね。

【Excel Tips】隔週の曜日に自動で色を付ける

目次日付の自動設定考え方(WEEKDAY関数を使う)条件付き書式を使う方法VBAマクロを使う方法 毎月第1と第3の金曜日に定期的な行事があるので、カレンダーに自動的に色塗…

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です