【Excel Tips】土日祝祭日の日付に自動で色を付ける
キノシタ目次
この記事の要点
使う関数やスキル
- Excelのシートのカレンダーに、土日と祝日の行に、それぞれ別の色をつける。COUNTIF関数を使用
- 大の月と小の月、閏年も自動的に表示を変える。DATE関数、IFS関数、EOMONTH関数を、TEXT関数を使用
- 今年と来年の祝祭日を、内閣府のWebサイトにあるCSVファイルをPower Queryを使って読み込む。（年に一度更新ボタンで更新する）
【完成版のカレンダー】
こちらからダウンロードできます。
日付の自動設定
書式設定・入力規則
- 上の完成図をして参考に、1ヶ月分のカレンダー罫線を引いて下さい。
- A4～A34を範囲選択し、ユーザー定義で「d」を指定します。
- A2セルにユーザー定義で次の書式を設定します。
###0"年" - 同様に、B2セルに次の書式を設定します。
#0"月" - 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ファイルの箇所で右クリックし、「リンクのアドレスをコピー」します。
Powerクエリを起動する
「データ」タブ、「Webから」を選択し、ダイアログボックスにコピーした「リンクのアドレス」を貼り付けます。
https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv
「OK」ボタンをクリックします。
初回だけ「webコンテンツへのアクセル」ダイアログボックスが出ます。
「匿名」にして適用対象レベルを上のCSVファイルにし、「接続」をクリックします。
（https://www8.cao.go.jp/でも構わない）
Power Queryエディタ
CSVファイルの内容が表示されるので、「データの変換」ボタンをクリックすると、PowerQueryエディタが起動します。
日付フィルター
左側の列の項目名右にある▽をクリックし①、「日付フィルター」②、「カスタムフィルター」③とクリックします。
「行のフィルター」ダイアログボックスが出るので、図のように「年単位」-「今年」、「◎また」「年単位」-「来年」を選びます。「および」を選ぶと祝日は表示されないので注意してください。
閉じて次に読み込む
「OK」ボタンをクリックすると、今年の祝日から表示されます。
「ホーム」タブの「閉じて読み込む」の下半分をクリックしてください。（上半分だと新しいシートができます）
「次に読み込む」をクリックします。
「データインポート」ダイアログボックスが出るので、「既存のワークシート」を選び、挿入したいセル番地（例ではF4セル）を選択して「OK」ボタンをクリックします。
クエリと接続ウインドウ、名前を付ける
「OK」ボタンをクリックすると、シートに祝日のリストが表示されます。
「クエリと接続」ウインドウも表示され、「４０行が読み込まれました」と祝日の日数が記されています。
次に、祝日の日付列の項目名を除いた全体（例ではF4:F43を範囲選択して、「名前ボックス」に「祝祭日」と入力し、エンターキーをクリックします。
祝祭日の更新
内閣府の祝祭日のデータは、例年２月に翌年分が更新されます。
２月を過ぎた頃、祝祭日テーブルで右クリックし、「更新」を選んで下さい。
また「クエリ」タブから抽出範囲の変更もできます。それにはPowerQueryの知識が必要です。
条件付き書式で、土、日曜日に色を付ける
新しい書式ルール
カレンダーの項目名を除いた全体（例ではA4:C34）を範囲選択します。
「ホーム」タブ「条件付き書式」「新しいルール」と選択し、「新しい書式ルール」で、「数式を使用して、書式設定するセルを決定」を選びます。
土曜日の色塗り
「次の数式を満たす場合に値を書式設定」に次の数式を入力します。
＝TEXT($A4,"aaa")="土"
条件式がTrueの場合に書式が有効になります。
A4の位置でF4キーを３回クリックして複合参照（Aの前だけ$が付く）にしておくのがポイントです。
「書式」ボタンをクリックして書式設定に進みます。
「セルの書式設定」で、「塗りつぶし」①、好みの「色」②を選び「OK」をクリックします。
日曜日の色塗り
範囲選択を維持したまま、＝TEXT($A4,"aaa")="日" と変更し、好みの色を選んで下さい。
土日は完成
土曜と日曜が塗り分けられました。
COUNTIF関数で祝祭日を判断する
次に「祝祭日」をの判断をします。しかし、Excelには祝祭日を判断する関数はありません。
そこでCOUNTIF関数を使い、先に作った「国民の祝日・休日月日」リストから探します。
ある日付がこの中にあるかどうかを数えるのです。
数えた結果が「１」であれば、その日付は祝祭日であると判断できます。
条件付き書式を設定する
土日の設定と同じく、項目名を除いたスケジュール表の全体（例ではA4:C34）を範囲選択します。
①「ホーム」タブの「条件付き書式」を選び、「新しい書式ルール」で、①「数式を使用して、書式設定するセルを決定」を選びます。
②「次の数式を満たす場合に値を書式設定」に次の数式を入力します。
＝COUNTIF(祝祭日,$A4)=1
名前範囲「祝祭日」を使います。
ここでもA4の位置でF4キーを２回クリックして複合参照（Aの前だけ$が付く）にしておくのがポイントです。
③「書式」をクリックして祝祭日の書式を任意に設定してください。
以上で完成です。
年末年始休業日や会社の創立記念日を入れてもよいですね。
このように３つの条件がそれぞれOR条件で設定されています。
隔週で曜日を設定する方法
第1と第3金曜日に予定を入れたいなど、隔週でカレンダーに記入するってこと、結構ありますよね。
その方法はこちらで紹介しています。土日祝祭日と併用すれば完璧ですね。
