役立つ! 総務マガジン

Excelの条件付き書式で休業日を色分けする

カレンダーの土日・祝日を自動で色分けして見やすく

Excelで管理している予定表や勤怠管理表で土日や祝日を色分けすると、見やすくなって便利です。条件付き書式を少し工夫して使えば、自動で色分けが可能になります。今回は比較的簡単な「土日の色分け」と、ワンランク上の「祝日の色分け」を解説します。

[2019年 5月13日公開]

WEEKDAY関数で土日に色を付ける

一般的なカレンダーでは土曜日を青、日曜日や祝日を赤で色分けして、見やすくしています。Excelで管理する予定表や勤怠管理表なども同様に色分けしたいものですが、やや複雑な設定が必要になるため、あきらめて手動でチマチマ塗っている人もいるのではないでしょうか。

曜日による色分けには条件付き書式を利用します。ただし、通常の条件付き書式の機能では日付から曜日を直接調べることはできないため、設定するルールの中で関数を利用して、日付に対応した曜日を取り出します。日付に対応した曜日を数値で返すWEEKDAY関数を利用しましょう。

WEEKDAY関数の構文
=WEEKDAY(シリアル値,週の基準)
週の基準で指定する値
1、または省略 …… 1(日)~7(土)
2 …… 1(月)~7(日)
3 …… 0(月)~6(日)など

シリアル値とは、1900年1月1日からの経過日数を表す数値で、例えば2019年7月1日なら43647となります。WEEKDAY関数の引数としては「=WEEKDAY("2019/7/1",2)」のように「"」で囲んだ日付を表す文字列や、「=WEEKDAY(B2)」のように日付データが入力されたセルの参照を指定でき、その日付が自動的にシリアル値に変換され、処理されます。

週の基準は、各曜日に対応した数値を返すパターンを指定します。「1」を指定するか省略した場合は、一般的なカレンダーの曜日の並びに応じた1~7の数値(日曜日なら1、月曜日なら2、そして土曜日なら7)となります。

「2」を指定した場合は月曜日が1で土曜日が6、日曜日が7となり、土日をまとめて扱いやすくなります。

以下の勤怠管理表を例に、WEEKDAY関数を使った条件付き書式を設定する手順を見ていきましょう。

(1)条件付き書式を設定したいセルを選択する

「日」と「曜日」に条件付き書式を設定するため、日付と曜日が入力されているセルA12~B41をドラッグして選択します。

この表では、先頭の「平成31年4月度」と表示されているセルに月を入力することで、「日」(A列)に対応した日付データが「曜日」(B列)にセットされます。「曜日」のセルでは書式設定により曜日だけを表示するようにしていますが、実際には「2019年4月1日」のように、日付のデータが設定されています。

(2)条件付き書式の[新しいルール]を選択する

[ホーム]タブの[スタイル]グループにある[条件付き書式]→[新しいルール]の順にクリックします。

(3)WEEKDAY関数を使い、土曜日に対応したルールを入力する

[新しい書式ルール]ダイアログボックスが表示されました。[ルールの種類を選択してください]から[数式を使用して、書式設定するセルを決定]を選び、[ルールの内容を編集してください]の[次の数式を満たす場合に値を書式設定]に「=WEEKDAY($B12,2)=6」と入力して、[書式]をクリックします。

ここでは週の基準に2を指定して土曜日なら6、日曜日なら7が返されるようにしたうえで、6(土曜日)の場合の書式を設定します。

シリアル値のために参照する日付データのセルは「$B12」と、B列を固定した複合参照とします。これにより、同じ行にあるA列のセルも、B列のセルの値に応じて書式が設定されるようになります。

(4)土曜日の書式を設定する

[セルの書式設定]ダイアログボックスの[フォント]タブが表示されました。土曜日を青の太字にするため[スタイル]で[太字]を、[色]で青を選択して[OK]をクリックします。

(5)条件付き書式の設定を完了する

[新しい書式ルール]ダイアログボックスに戻ったら[OK]をクリックして、条件付き書式の設定を完了します。

(6)土曜日の書式が変更された

土曜日の日付(A列)と曜日(B列)が、青の太字で表示されるようになりました。続けて、日曜日の分の条件付き書式を設定しましょう。

(7)日曜日の分の条件付き書式を設定する

続けて、日曜日の分の条件付き書式を設定します。土曜日の場合と同様に、セル範囲を選択して[ホーム]タブの[スタイル]グループにある[条件付き書式]→[新しいルール]の順にクリックします。

(8)日曜日に対応したルールを入力する

[書式ルールの編集]画面で[数式を使用して、書式設定するセルを決定]を選び、[ルールの内容を編集してください]の[次の数式を満たす場合に値を書式設定]で「=WEEKDAY($B12,2)=7」と入力して[書式]ボタンをクリックします。7は、週の基準が2のときに日曜日を表す数値です。

(9)日曜日の書式を設定する

[セルの書式設定]ダイアログボックスの[フォント]タブが表示されたら、[スタイル]に[太字]、[色]に赤を指定して、[OK]をクリックします。

(10)条件付き書式の設定を完了する

[新しい書式ルール]ダイアログボックスに戻ったら[OK]をクリックして、条件付き書式の設定を完了します。

(11)日曜日の書式が設定された

日曜日の日付と曜日が、赤の太字で表示されるようになりました。

祝日をリスト化して対応させる

次に、祝日を色分けするための条件付き書式を設定しましょう。祝日を判別するには、ブックの別のワークシートの祝日の日付をリスト化しておき、1日ごとにリストを検索して、その日が祝日に該当しているかを判定します。

ここでは、指定した日付が祝日に該当するか検索するためにCOUNTIF関数を使います。

COUNTIF関数の構文
COUNTIF(範囲,検索条件)

(1)別のワークシートに祝日のリストを用意する

ブックに新しいワークシートを作成し、祝日の日付を一列に入力してリスト化します。ここでは「HOL」ワークシートを作成して、セルA2~A73に祝日の日付を入力しました。「曜日」と「名称」は、祝日の判定には使用しないため省略しても構いません。

ここでは日本の祝日のみ入力していますが、会社の創立記念日や定期の休業日などがあれば、一緒に入力しておくことで祝日と同じ扱いにできます。

(2)祝日に対応したルールを入力する

土日の場合と同様に条件付き書式の設定を行いましょう。ルールは、以下で解説する数式を入力します。書式は、日曜日と同様に赤の太字を設定しています。

祝日を判定するルールとして、このような数式を入力します。

=COUNTIF(HOL!$A$2:$A$73,$B12)=1

COUNTIF関数は、セル範囲の中で、指定した条件と同じ値がいくつあるかを数える関数です。ここでは、一つ目の引数(範囲)に祝日リスト(HOLワークシート上のセルA2~A73)を、二つ目の引数(検索条件)に位置を固定する「$」を列番号の前に入れた$B12を指定して、セルB12と同じ日付がいくつあるかを数えるように設定します。

祝日リストに同じ日付があれば、上記の関数の戻り値は1になります。そして、条件付き書式では、1が返された場合に書式を設定するようにしているため、日付が祝日に該当したら、指定した書式が設定される、というわけです。

(4)祝日の書式が設定された

祝日の日付と曜日が、赤の太字で表示されました。

一度設定すれば、手動よりもずっと手軽で正確に

このように、条件付き書式と関数を組み合わせることで、曜日に対応した書式を設定して色分けができます。一度設定した書式はコピーして別の表に貼り付けたりもできるため、何度も再入力する必要はありません。

このような条件付き書式の設定が分からず、全て手動で書式を設定していた人もいるでしょう。今回解説した方法で条件付き書式を使えば、作業を大幅に効率化でき、間違った色分けをしてしまうミスも、一度消してもう一回塗り直す悪夢も避けられます。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事