Excelで土日・祝日を自動で色分けする方法|祝日リストと条件付き書式の設定

Excelのカレンダーは、土日をWEEKDAY関数、祝日を日付リストとの照合で色分けできます。祝日一覧を一か所へまとめれば、毎日のセルを手で塗り直す必要はありません。ただし、翌年の祝日や振替休日が自動で追加されるわけではなく、一覧の更新は必要です。

1. 日付の列と祝日一覧を用意する

ここでは、カレンダーのA列を日付、B列を曜日、C列を予定として、2行目からデータを入れます。日付が別の列にある表では、後述の数式の$A2をその列に合わせて変更してください。

祝日一覧は、別シート「祝日マスタ」のA列に日付、B列に名称を入れます。日付セルには2026/5/3、名称セルには「憲法記念日」のように分けて入力します。「2026/5/3(憲法記念日)」を一つのセルへ入れると文字列になり、日付との照合ができなくなります。

年度ごとの祝日・休日は内閣府の祝日一覧とCSVで確認できます。振替休日や祝日に挟まれた休日も含め、対象年の一覧を取り込みます。会社独自の休業日を加える場合は、国民の祝日と見分けられる名称にしておきましょう。

2. 祝日の日付範囲に名前を付ける

1. 「祝日マスタ」で日付だけの範囲を選択します。見出し行や名称の列は含めません。
2. 数式バー左の名前ボックスへHolidayDatesと入力し、Enterで確定します。
3. 「数式」→「名前の管理」で、名前と参照範囲を確認します。複数シートから使う場合は、範囲が「ブック」であることも確認します。

例えば画像の抜粋例なら、参照先は='祝日マスタ'!$A$2:$A$3です。実際の年間カレンダーでは、対象年のすべての日付を含む範囲にしてください。既に「祝日リスト」という名前を使っているブックは、後述の数式のHolidayDatesをその名前へ置き換えれば使えます。

一覧に日付を追加しても、固定範囲の名前定義は自動で伸びるとは限りません。追加後は名前の管理で参照先を広げます。日付かどうか不明なセルは、別の空きセルで=ISNUMBER(A2)などを使い、数値として保存されているか確認できます。

3. 土日と祝日の条件付き書式を設定する

カレンダー側で色を付けたい範囲を選びます。画像ではA2:C4、1か月分の縦型カレンダーならA2:C32など、実際の表に合わせます。左上のA2を起点として選択し、「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を開きます。

土日を同じ青色にする例では、次の式を設定します。WEEKDAYの第2引数を2にすると月曜が1、土曜が6、日曜が7になるため、5より大きい日を判定できます。

=WEEKDAY($A2,2)>5

続けて新しいルールを作り、祝日の赤色を設定します。

=COUNTIF(HolidayDates,$A2)>0

$A2は、判定する列をA列へ固定し、行番号は各行に合わせて変える指定です。$A$2にすると全行がA2の日付で判定されます。B列やC列を含む行全体を色分けしたい時ほど、この違いに注意してください。

空欄を含む広い範囲へ設定するなら、土日は=AND(ISNUMBER($A2),WEEKDAY($A2,2)>5)、祝日は=AND(ISNUMBER($A2),COUNTIF(HolidayDates,$A2)>0)として、日付の値がある行だけに絞れます。土曜と日曜を別色にする場合は、土曜を=WEEKDAY($A2,2)=6、日曜を=WEEKDAY($A2,2)=7としてルールを分けます。

4. 土日と祝日が重なる日は、祝日を優先する

「条件付き書式」→「ルールの管理」で、祝日のルールを土日より上へ移動します。祝日色で確定させたい場合は、そのルールの「条件を満たしたら停止」をオンにします。下位の別の書式も反映させたい表では、停止させてよいかを確認してください。

2026年5月3日は日曜日であり、憲法記念日でもあります。土日を青、祝日を赤とした場合、土日のルールを先に適用して停止すると青のままです。祝日を最上位にすれば、この日だけでなく月曜の5月4日も赤になり、通常の土曜である5月2日は青に残ります。

同じ塗りつぶし色に複数ルールが該当した時の優先関係は、Microsoftの条件付き書式の説明でも確認できます。手で塗った色を直す前に、まずルールの順番・数式・適用先を確認しましょう。

5. 年が変わる時と、色が付かない時の確認

• 翌年のカレンダーを作る時:カレンダーの日付だけでなく祝日一覧も更新し、HolidayDatesの参照範囲に追加日が入っているか確認します。
• 祝日だけ色が付かない時:一覧とカレンダーの日付が文字列になっていないか、名前定義が別シート限定になっていないか調べます。
• 1行ずれて色が付く時:適用先の最初の行と数式の行番号を揃えます。適用先がA3:C33なら式も$A3から始めます。
• 日時データを使う時:表示が日付だけでも、値に時刻が含まれると祝日一覧の午前0時と一致しません。必要なら別列に=INT(A2)で日付部分を作り、その列を判定に使います。

最初は通常の平日・土日・祝日・土日と重なる祝日の4種類を確かめます。画像の一覧は仕組みを示すための抜粋で、5月5日や5月6日などは省いています。業務で使う表には、対象年の休日を漏れなく登録してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】