Excel 土日と祝日に色を自動設定する方法

2020年5月8日

カレンダーのサンプル
カレンダーのサンプル

Excelでカレンダーを作る際の土日と祝祭日の色分けは

手作業で実施しても構いませんが、月が替わるたびに何度も実施する場合、

単純な手作業では間違いが発生しやすく作業の効率も悪くなります。

その解消するために条件付き書式で自動的に色を設定する方法を紹介します。

手順

まず、このようなカレンダーを用意します。

月は横に並べるよりもシート別にし列を合わせるほうが楽ですが今回は並べます。

書式設定前の縦カレンダー
書式設定前の縦カレンダー

日曜日の設定

3月全て(B2~D32)をセル選択し、「条件付き書式」から「新しいルール」を選択します。

3月全て(B2~D32)をセル選択し「条件付き書式」から「新しいルール」を選択
「新しいルール」を選択(赤枠)

「数式を使用して、書式設定するセルを決定」で

数式はWEEKDAY関数を使用し、下記のように入力します。

=WEEKDAY($B2)=1

「日付(B2)が日曜日」という判定です。

列を絶対指定しているのはC列とD列の条件付き書式でもB列を参照するためです。

$がないとC列はC列、D列はD列を判断の対象とするため正常に動作しません。

次に日曜日に設定したい書式を設定します。

書式ルール設定例(日曜日)
書式ルール設定例(日曜日)

3月分の日曜日が設定されました。

日曜日の書式ルールが設定された3月分カレンダー
日曜日の書式ルールが設定された3月分カレンダー

土曜日の設定

日曜日の設定と同様です。

相違点は土曜日の書式となることと条件付き書式の数式です。

=WEEKDAY($B2)=7

=7が土曜日であることの判定です。

書式ルール設定例(土曜日)
書式ルール設定例(土曜日)
日曜日と土曜日の書式ルールが設定された3月分カレンダー
日曜日と土曜日の書式ルールが設定された3月分カレンダー

祝日の設定

まず祝日のシートを用意します。

祝祭日シート例
祝祭日シート例

祝日はこちらに最新版を置いています。

そして土日と同様に3月を全セル選択した上で

条件付き書式を起動し祝日用の書式とCOUNTIF関数の数式を設定します。

=COUNTIF(祝祭日!$A$1:$A$10,$B2) =1

祝祭日シートのA1~10までの範囲は不変のため絶対指定にしていますが

範囲名を付けて指定するとベターです。

書式ルール設定例(祝祭日)
書式ルール設定例(祝祭日)

カレンダー通り日曜日に合わせて赤としてもよいですが、

今回は見分けのため紫としています。

祝祭日の色が設定されたカレンダー
祝祭日の色が設定されたカレンダー

次にVLOOKUP関数XLOOKUP関数でも可)と

IFERROR関数を使用して祝日名をD列に表示するようにします。

D2列に下記の数式を入力します。

=IFERROR(VLOOKUP(B2,祝祭日!$A$1:$B$10,2,FALSE),"")

XLOOKUP関数では下の数式になります。

=XLOOKUP(B2,祝祭日!$A$1:$A$10,祝祭日!$B$1:$B$10,"")
VLOOKUP関数の設定例
VLOOKUP関数の設定例

この数式を3月全てにコピーすれば完成です。

3/21に春分の日が設定されました。

祝祭日名が設定されたカレンダー
祝祭日名が設定されたカレンダー

別の月へコピー

3月1日分のみをコピーし、4月と5月に書式コピーします。

3月1日分のみをコピー
3月1日分のみをコピー
4月全てにコピー
4月全てにコピー
5月全てにコピー
5月全てにコピー

この時、4月と5月をCtrlで同時選択しないようにします。

(するとうまくいきません)

なおこの状態では色変更がうまく行っていません。

※4月と5月を別シートにして列を合わせているとここで完了です。

4月も5月も条件付き書式の数式が3月に設定されているためです。

それを変更するためには「ルールの管理」を呼び出します。

4月のルールの管理
4月のルールの管理

ルールの編集を選択します。

条件付き書式ルールの編集(赤枠)
条件付き書式ルールの編集(赤枠)

B2セルを指定している個所を全ルール、F2に変更します。

書式ルール設定(列調整)
書式ルール設定(列調整)

5月分も同様に設定し、D2セルの祝祭日名の数式をコピーすれば

4月分と5月分のカレンダーも完成です。

4月5月への書式コピー完了
4月5月への書式コピー完了

土日祝日の書式の優先度変更

条件付き書式を日曜日→土曜日→祝日の順番に

設定したため祝日が最も優先度が高い状態となっています。(日曜日の祝日は紫)

このままでよい場合は何もする必要がありませんが、

日曜日と土曜日を優先したい場合は優先度の変更を行う必要があります。

優先度変更を行うセル範囲を選択し、「ルールの管理」を呼び出します。

上に行くほど優先度の高いルールで赤枠線部分が優先度変更ボタンです。

5月の書式ルール(優先度変更前)
5月の書式ルール(優先度変更前)

祝祭日の優先度を下げました。

5月の書式ルール(優先度変更前)
5月の書式ルール(優先度変更前)

5/4と5/5に土曜日と日曜日のルールが優先されて青と赤の表示になりました。

優先度変更された5月のカレンダー
優先度変更された5月のカレンダー

関連記事

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧

類似例

条件付き書式と関数を利用する類似例

条件付き書式と関数を利用する類似例