月間予定表の祝日や土日のセルや行に色を付ける(条件付き書式)：Excel基本講座

スポンサードリンク

更新:2024/10/14;作成:2018/6/28

祝日の一覧表を作成する	カレンダーを作成する
祝日の行に条件付き書式を設定する	土日のセルや行に色を付ける
条件付き書式を他のシートにコピーする

祝日の一覧表を作成する　　　　　 Topへ

カレンダーの祝日のセルや行に色を付けるには、日付が祝日か否かを判定する必要があります。
Excelに機能には日付データだけで祝日と判定することができませんので、祝日の一覧表のデータを準備します。

次表のように、必要な期間の祝日の一覧表を作成します。
ここでは2025年の祝日の一覧表をシート[祝日一覧]に作成します。
次の年の祝日データを追加しても使用できるようにテーブル機能を利用します。(自動でデータ範囲を拡張できるのでテーブル機能を使います)
- この祝日のデータは　国民の祝日について - 内閣府　からCSVファイルをダウンロードできます。
  https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html
祝日を入力します。データ範囲を選択して、[挿入]タブの[テーブル]をクリックします。
ショートカットキーは　[Ctrl]+[T]です。
データ範囲と、「先頭行をテーブルの見出しとして使用する」にチェックが入っているのを確認します。
[OK]ボタンをクリックします。
(なお、A2セルから始まっているのは2024年の祝日が入力されていたからです）
選択していた範囲がテーブルに変換され、書式が設定されました。
[テーブルデザイン]タブのプロパティグループのテーブル名に　祝日　と入力します。
(テーブル名をテーブル1から「祝日」に変更します。)

カレンダーを作成する　　　　　 Topへ

1年分の月間予定表を作成したいと思います。
各月ごとのシート 2025年1月～ 2025年12月　を作成します。

手作業でもよいのですが、ちょっと手間がかかりますので、VBAで作成しました。

Sub sheet_add()
　Dim i As Long
　　For i = 1 To 12
　　　ActiveWorkbook.Sheets.Add.Name = "2025年" & 13 - i & "月"

　　　Range("B2").Value = DateSerial(2025, 13 - i, 1)
　　　Range("B2").NumberFormatLocal = "yyyy""年""m""月"""
　　　Range("B5").Formula = "=SEQUENCE(DAY(EOMONTH(B2,0)),,B2)"
　　　Range("B5:B35").NumberFormatLocal = "d(aaa)"

　　Next i
End Sub

ところが、VBAで機能する数式が入力できませんので、2025年1月～ 2025年12月を選択します。
数式バーで不要な　@ を削除して、[Enter]キーを押してすべてのシートに一気に入力します。

↓

手入力する場合は、シート 2025年1月～ 2025年12月を作成して、これらのシートを選択します。
B2セルには 2025/1/1　と入力して、[Ctrl]+[1]でセルの表示形式を選択して、yyyy"年"m"月”とします。
シート見出しを右クリックして、「シートのグループ解除」します。
2025年2月＝12月に　B2セルの日付をシートの月　2～12に変更します。
この部分を上のVBAで処理しています。
シート 2025年1月～ 2025年12月を作成して、これらのシートを選択します。
B3セルには =SEQUENCE(DAY(EOMONTH(B2,0)),,B2) と入力します。
B5:B35セルを選択して、[Ctrl]+[1]でセルの表示形式を選択して、d(aaa)　とします。
[ホーム]タブの数値グループにあるダイアログボックス起動ツールをクリックして、セルの書式設定ダイアログボックスを開きます。
ショートカットキー　[Ctrl]+[1] (テンキーの１は使用不可)です。
[表示形式]タブを選択します。
分類で[ユーザー定義]を選択して、種類に　d"日"　と入力します。
- 日付と同じセルに曜日を表示したい場合は、　d"日"(aaa)　とします。

祝日の行に色を付ける(条件付き書式を設定する)　　　　Topへ

シートをグループ化していると、条件付き書式はグレーになって設定することができません。
グループを解除して、2025年1月のシートに条件付き書式を設定します。
祝日の判定は　=COUNTIF(祝日[日付],B5)　とすることができます。この数式はテーブルの構造化参照を使っています。
テーブル　祝日の　[日付]の列に　B5セルの値がいくつあるかを求めて、1が返されると祝日であるとなります。　0が返されると祝日ではないと判定できます。
E列は説明用に入力したものです。実際に入力する必要はありません。
- 構造化参照に関しては　Excel2013のテーブルの構造化参照を利用する　をご覧ください。
2025/5/3　～　5/6は祝日であるのが確認できました。
祝日名を入力します。
シート 2025年1月～ 2025年12月を選択して、(グループ化して、全てのシートに数式を入力します。）
C5セルに　=XLOOKUP(B5:B35,祝日[日付],祝日[名称],"")　と入力します。

祝日の時、セルを塗りつぶす範囲を選択します。B5:D35セルを選択しています。
[ホーム]タブの[条件付き書式]→[新しいルール]を選択します。
「数式を使用して、書式設定するセルを決定」を選択します。
ルールの内容を編集してくださいの次の数式を満たす場合に書式設定に
　=COUNTIF(INDIRECT("祝日[日付]"),$B5)=1　と入力します。
- この数式（条件式）は選択しているセル範囲 B5:D35 のアクティブセル（B5セル）の条件式を作成します。
  隣の C5、D5セルにも適用する条件式なので、参照する　B5 は列部分を絶対参照にして（複合参照） $B5 となります。
[書式]ボタンをクリックして書式を設定します。
- Excel2010以降はこのように INDIRECT("祝日[日付]")　としてテーブル名を使用することができます。
- 書式は[塗りつぶし]タブを選択して、背景色を選択しました。
祝日のセル及び行を塗りつぶすことができました。

土日のセルや行に色を付ける　　　　 Topへ

日曜の行に色を付けます。
セルを塗りつぶす範囲を選択します。B5:D35セルを選択しています。
[ホーム]タブの[条件付き書式]→[新しいルール]を選択します。
日曜日の判定は　=TEXT($B5,"aaa")=”日”　とします。
土曜の行に色を付けます。
セルを塗りつぶす範囲を選択します。B5:D35セルを選択しています。
[ホーム]タブの[条件付き書式]→[新しいルール]を選択します。
土曜日の判定は　=TEXT($B5,"aaa")=”土”　とします。

↓
日付が未入力のセルに土曜日の書式が設定されるため、条件を変更します。
=AND($B5<>"",TEXT($B5,"aaa")="土")
としました。
条件付き書式のルールは下図のようになります。
ルール、書式、適用先の順序で各ルールが表示されています。
ポイントは祝日のルールを一番上に配置します。
土日および祝日のセルまたは行に色を付けると下図のようになりました。