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

2019年5月6日

概要

カレンダー
カレンダー

Excelでカレンダーを作る際の、

土日と祝祭日の色分けは

手作業で実施しても構いませんが

月が替わるたびに何度も実施する場合、

単純な手作業では間違いが発生しやすく

作業の効率も悪くなります。

その解消するために条件付き書式で

自動的に色を設定する方法を紹介します。

サンプルファイル

手順

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

月は横に並べるよりもシート別にし

列を合わせるほうが楽ですが今回は並べます。

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

日曜日の設定

3月全て(B2~D32)をセル選択し、

「条件付き書式」から「新しいルール」を選択します。

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

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

数式は下記のように入力します。

=WEEKDAY($B2)=1

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

列を絶対指定しているのは

C列とD列の条件付き書式でも

B列を参照するためです。

$がないとC列はC列、D列はD列を

判断の対象とするため正常に動作しません。

なおWEEKDAY関数の詳細仕様はこちらです。

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

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

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

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

土曜日の設定

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

相違点は土曜日の書式となることと

条件付き書式の数式です。

=WEEKDAY($B2)=7

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

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

祝日の設定

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

祝祭日シート

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

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

条件付き書式を起動し祝日用の書式と

条件の数式を設定します。

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

祝祭日シートのA1~10までの範囲は

不変のため絶対指定にしていますが

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

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

セル範囲に名前を定義。設定と編集・削除方法

カレンダー通り日曜日に合わせて

赤としてもよいですが、

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

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

次にVLOOKUP関数を使用して、

祝日名をD列に表示するようにします。

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

=IFERROR(VLOOKUP(B2,祝祭日!$A$1:$B$10,2,FALSE),"")
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月への書式コピー完了

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

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

設定したため祝日が最も優先度が高い状態と

なっています。(日曜日の祝日は紫)

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

日曜日と土曜日を優先したい場合は

優先度の変更を行う必要があります。

優先度変更を行うセル範囲を選択し、

「ルールの管理」を呼び出します。

上に行くほど優先度の高いルールで

赤枠線部分が優先度変更ボタンです。

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

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

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

5/4と5/5に土曜日と日曜日のルールが

優先されて青と赤の表示になりました。

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

関連記事

VLOOKUP関数の記事一覧

WEEKDAY関数の詳細

COUNTIF関数の詳細

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

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