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

更新:2025年6月27日;作成:2018年6月28日

• ここでは1年分のカレンダーを作成します。
  1月〜12月の各シートを作成して、各シートに条件付き書式を設定します。
  12枚のシートを作成するのがちょっと面倒・・・と思ったので、簡単なVBAで処理しています。

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

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

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

1年分のカレンダーを作成する Topへ

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

1. 手作業でもよいのですが、ちょっと手間がかかりますので、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
2. ところが、VBAで入力した数式が機能しませんでした、よって修正します。
   シート 2025年1月 を選択して、[Shift]キーを押した状態で シート 2025年12月 を選択します。
   2025年1月〜2025年12月のシートを選択しました。
   数式バーで不要な @ を削除して、[Enter]キーを押してすべてのシートに一気に入力します。
   
3. B5セル以降に日付が入力されます。表示形式は、d(aaa) となっています。
   
   • 手作業では、B5:B35セルを選択して、[Ctrl]+[1]でセルの表示形式を選択して、d(aaa) とします。
     または、[ホーム]タブの数値グループにあるダイアログボックス起動ツールをクリックして、セルの書式設定ダイアログボックスを開きます。
     
   • [表示形式]タブを選択します。
     分類で[ユーザー定義]を選択して、種類に d"日" と入力します。
     • 日付と同じセルに曜日を表示したい場合は、 d(aaa) とします。
4. 下図のように1年間のカレンダーが作成できました。
   

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

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

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

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

• 日曜の行に色を付けます。
  セルを塗りつぶす範囲を選択します。B5:D35セルを選択しています。
  [ホーム]タブの[条件付き書式]→[新しいルール]を選択します。
  日曜日の判定は =TEXT($B5,"aaa")="日" とします。
  
• 土曜の行に色を付けます。
  セルを塗りつぶす範囲を選択します。B5:D35セルを選択しています。
  [ホーム]タブの[条件付き書式]→[新しいルール]を選択します。
  日付が未入力のセルに土曜日の書式が設定されるため、
  =AND($B5<>"",TEXT($B5,"aaa")="土")
  としました。
  セルが未入力で かつ 「土曜日」であるときに色を付けます。
  
• 条件付き書式のルールは下図のようになります。
  ルール、書式、適用先の順序で各ルールが表示されています。
  ポイントは祝日のルールを一番上に配置します。
  
• 土日および祝日のセルまたは行に色を付けると下図のようになりました。
  

他のシートに条件付き書式をコピーする Topへ

• シートをグループ化していると、条件付き書式はグレーになって設定することができません。
  2025年1月に設定した条件付き書式をコピーしてコピーします。

1. 条件付き書式を設定したセル B5:D35セルを選択します。
   リボンの クリップボード グループの[書式のコピー/貼り付け]をクリックします。
   
2. 2025年2月〜12月のシートw選択してグループ化します。
   B5:D35セルをドラッグして、書式を貼り付けます。