Excelカレンダーの土日祝日を自動色付け：別シート参照と関数で日程管理の手作業負荷を最小化する方法
はじめに
Excelカレンダーにおける土日や祝日の手作業による色付けは、ミスの発生源であり、非効率的な作業です
Excelで作成したカレンダーに土日や祝日の色を手動で設定していると、入力ミスや更新漏れが発生しやすく、時間的コストも無視できません。
本記事では、条件付き書式と関数を活用した自動色付けの手順を解説することで、日程管理やスケジュール作成における手作業負荷を最小化します。
特に多くのユーザーが直面する「祝日の色が自動でつかない」という課題の解決に焦点を当て、別シートに用意した祝日リストを参照する方法を中心に説明します。
本記事の内容
- 条件付き書式による土日祝日の自動判定の仕組み
- COUNTIF関数を用いた祝日判定の具体的設定方法
- WEEKDAY関数による曜日判定の実装
- 複数月への適用と参照エラーの修正手順
- 色がつかない・消える問題の原因別チェックリスト
概要解説動画
前提と準備
本記事の手順を実行する前に、以下を用意してください。
- 日付と曜日を配置したカレンダーシートを作成済みであること
- 別シートに祝日リスト（日付と祝日名の2列）を正確に入力済みであること
祝日リストは、日付列に「2025/10/1」のようなExcelが日付として認識可能な形式で入力する必要があります。
条件付き書式による自動判定の仕組み
条件付き書式は、セルの値や状態に基づいて自動的に書式を適用する機能です。
この機能と関数を組み合わせることで、カレンダーの日付が祝日、土曜日、日曜日のいずれに該当するかを自動判定し、対応する色を適用できます。
祝日判定の設定（最優先課題の解決）
祝日の色付けは、別シートに用意した祝日リストとカレンダーの日付を照合することで実現します。
設定手順
- カレンダーシートで色付けしたい範囲（例：A1:C31）を選択
- ホームタブから「条件付き書式」→「新しいルール」を選択
- 「数式を使用して、書式設定するセルを決定」を選択
- 数式を入力。数式例 :
=COUNTIF(祝日!$A:$A,$A1)=1
- 書式ボタンをクリックし、祝日用の塗りつぶし色（例：薄いオレンジ）を設定
- OKをクリックして適用
数式の解説
- COUNTIF関数：指定した範囲内に条件に合うセルがいくつあるかをカウントする関数
- 祝日!$A:$A：別シート「祝日」のA列に入力された祝日リストを参照
- $B1：カレンダーシートのB列（日付列）の現在行を参照
- =1：リスト内に該当日付が1つ存在する（つまり祝日である）場合にTrueを返す
絶対参照の原則（重要）
祝日リストの範囲（$A$:$A）には必ず絶対参照（$）を適用してください。絶対参照を省略すると、条件付き書式が他のセルにコピーされた際に参照範囲がずれ、正しく判定されません。
一方、日付セル（$B1）は列のみを絶対参照（$B）とし、行（1）は相対参照とすることで、各行の日付を正しく参照できます。
効率化のための名前定義（推奨）
祝日リストの範囲に名前を定義すると、数式の可読性が向上し、リスト行数の増減に対するメンテナンスが容易になります。
- 祝日シートでA:Aを選択
- 数式タブから「名前の定義」を選択
- 名前欄に「祝日リスト」と入力してOK
数式は以下のように変更できます。
=COUNTIF(祝日リスト,$A1)=1
日曜日の判定設定
WEEKDAY関数を使用し、日付の曜日番号を取得して日曜日を判定します。
設定手順
- カレンダーの範囲を選択
- 「条件付き書式」→「新しいルール」を選択
- 「数式を使用して、書式設定するセルを決定」を選択
- 以下の数式を入力 :
=WEEKDAY($B1)=1 - 書式で日曜日用の色（例：薄い赤）を設定
数式の解説
- WEEKDAY関数：日付から曜日番号を返す関数（デフォルトでは日曜日=1、月曜日=2…土曜日=7）
- =1：戻り値が1（日曜日）の場合にTrueを返す
土曜日の判定設定
日曜日と同様の手順で、土曜日を判定します。
数式
=WEEKDAY($A1)=7注意点
曜日判定においても、日付セル（$B1）の列のみを絶対参照とすることが重要です。これにより、日付列以外の列（曜日名列や祝日名列など）にも正しく書式が適用されます。
祝日名の自動抽出とデータ検証
VLOOKUP/XLOOKUPによる祝日名の表示
祝日の色が正しく適用されているかを視覚的に確認するため、祝日名を自動表示する列を設けることを推奨します。
関数の役割の違い
VLOOKUP+IFERROR数式
=IFERROR(VLOOKUP(A1,祝祭日!$A:$B,2,FALSE),"")数式の解説
- VLOOKUP：A1の日付を祝祭日シートのA列から検索し、2列目（祝日名）を返す
- FALSE：完全一致検索を指定
- IFERROR：該当しない場合（平日）は空白を返す
XLOOKUP数式（Excel 2021以降推奨）
=XLOOKUP(A1,祝日!$A:$A,祝日!$B:$B,"")XLOOKUPはVLOOKUPより柔軟で、列の並び順に制約がなく、エラー処理も組み込まれているため推奨されます。
複数月への適用とトラブルシューティング
複数月への適用と効率的な構造
条件付き書式の設定工数を最小化するためには、月ごとにシートを分け、列位置を統一することが最も効率的です。
手順
- 1ヶ月分の条件付き書式設定を完了
- 設定済みのセル範囲を選択してコピー
- 他の月のシートの対応する範囲を選択
- 「ホーム」タブ→「貼り付け」→「書式設定」を選択
この方法により、条件付き書式のルールが他の月にもコピーされます。
複数月コピー後の参照エラー修正（重要）
カレンダーを横に並べた構造（1つのシートに複数月を配置）で書式コピーを行うと、条件付き書式の数式が元の月の列を参照したままとなり、色がつかない問題が発生します。
エラーの原因
例えば3月のカレンダーがA列から始まり、4月がF列から始まる場合、4月の範囲にコピーした条件付き書式の数式は以下のようになります。
=WEEKDAY($A1)=1この数式は4月の範囲内でも3月のA列を参照し続けるため、正しく判定されません。
修正手順
- 4月の範囲を選択
- 「ホーム」タブ→「条件付き書式」→「ルールの管理」を選択
- 各ルール（日曜日、土曜日、祝日）を1つずつ選択し、「ルールの編集」をクリック
- 数式内の参照セルを手動で修正
例：4月がF列から始まる場合
- 修正前：
=WEEKDAY($A1)=1 - 修正後：
=WEEKDAY($F1)=1
- すべてのルールを修正してOK
この手動修正が必要な点は、横並び構造の欠点です。
月ごとにシートを分ける構造であれば、この問題は発生しません。
優先度調整による表示の制御
条件付き書式は複数のルールが設定されている場合、上位にあるルールほど優先度が高く、最初に合致したルールが適用されます。
調整が必要なケース
祝日が土日と重なった場合、どちらの色を優先するかを制御できます。
優先順位の変更手順
- 「条件付き書式」→「ルールの管理」を選択
- 優先したいルールを選択
- 上向き矢印または下向き矢印で順序を変更
例：日曜日の色（赤）を優先したい場合は、祝日（COUNTIF）のルールを日曜日ルールの下に移動させます。
色が消える・つかない時の原因別チェックリスト
参照セルの確認
確認内容
COUNTIF関数の祝日リスト範囲が正しく絶対参照されているか確認します。
=COUNTIF(祝日!$A:$A,$A1)=1- 範囲の行列番号の前に
$が付いているか - 日付セルは列のみ絶対参照（$A1）となっているか
修正方法
条件付き書式のルール管理から該当ルールを編集し、$を追加します。
日付形式の確認
確認内容
祝日リスト（別シート）に入力されている日付が、Excelで認識可能な日付形式になっているか確認します。
確認方法
- 祝日リストのセルを選択
- ホームタブの「表示形式」を確認
- 「日付」または「ユーザー定義」になっているか確認
よくある問題
- 文字列として入力されている（表示形式が「標準」または「文字列」）
- 先頭にアポストロフィ（'）が付いている
- 全角文字が混在している
修正方法
- 該当セルを選択
- ホームタブから「表示形式」→「日付」を選択
- または、正しい日付形式で再入力
適用範囲の確認
確認内容
条件付き書式を適用したい範囲が正しく指定されているか確認します。
確認方法
- 「条件付き書式」→「ルールの管理」を選択
- 「適用先」欄を確認
よくある問題
- 適用範囲が一部のセルのみになっている
- 適用範囲に日付列が含まれていない
修正方法
- ルールの管理画面で適用先を編集
- または、正しい範囲を選択してルールを再設定
複数月の参照セルの確認
確認内容
複数の月を横に並べている場合、各月の条件付き書式ルールが、それぞれの月の開始列を参照しているか確認します。
確認方法
- 色がつかない月の範囲を選択
- 「ルールの管理」でルールを編集
- 数式内の参照列を確認
修正方法
各月の開始列に合わせて数式内の列参照を手動で修正します。
Excelのバージョンと関数の互換性
確認内容
使用している関数が現在のExcelバージョンでサポートされているか確認します。
対処方法
古いバージョンを使用している場合は、VLOOKUPとIFERRORの組み合わせを使用してください。
まとめ
Excelカレンダーの土日祝日を自動で色付けする手法は、条件付き書式とCOUNTIF関数、WEEKDAY関数の組み合わせにより実現できます。
これらの設定により、手作業による色付けの負荷を最小化し、ミスのないカレンダー管理が可能になります。色がつかない問題が発生した場合は、本記事のチェックリストに従って原因を特定し、適切に修正してください。
関連記事
XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧
類似例
条件付き書式と関数を利用する類似例
条件付き書式と関数を利用する類似例
Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。