Excelの予定表で祝日や会社の休日を自動で色付けする方法!

calendar

従業員のシフト表の作成やスケジュール管理、イベント管理、生産の予定を管理するなど、予定を管理する時に使うソフトでExcelを使う人は多いかと思います。その時に会社の休日を色付けする場合どのように処理していますか。

祝日や土曜日、日曜日などの休日ならExcelの関数を使って色付けが行えますが、平日に会社の休日があって色付けする場合、よく見かけるのが会社のカレンダーを確認しながら予定表に手動で色付けしている人は多いと思います。

手入力で祝日や会社の休みの日を色分けすると間違いも起こりやすい。また、会社の業務が忙しく休日の変更によって色の塗り直しになると非常に手間な作業でもあります。

今回はExcelの予定表で祝日や会社の休みの日を自動で色分けする仕組み条件付き書式の設定方法を紹介していきます。

休日を自動で色付けする予定表の作成

列に日付が入力されて行に人が入った簡単な勤務表です。この予定表に条件付き書式を使って休日は自動的に色付けするように設定していきます。

Excelの予定表

会社の休日は日曜日と会社が指定した土曜日とします。

2021年の11月の日曜日は 「11/7」、「11/14」、「11/21」、「11/28」です。

その他に会社が指定した土曜日の休日が「11/6」、「11/20」、「11/27」です。

Excelの関数を使って休日判定するため別シートを作成します。

①シート名は「休日シート」とします。

②会社の休日を入力します。

休日一覧

今回、Excel上に11月度の会社休日を手入力しましたが会社で販売管理システムなどが動いている場合、システム上で休日のマスタ(先ほど手入力した感じのデータ)を持っていることが多いです。

もし実際に休日シートを作成する場合は、会社のシステム担当者に話をして休日データを1年分もらえるかどうか相談してみてください。

休日の色付けには「条件付き書式」を使います。日付の始まりのセル①「C4」を選択して②条件付き書式をクリックして③「新しいルール」をクリックします。

Excel 条件付き書式

①「数式を使用して、書式設定するセルを決定」をクリックして数式バーに②「 =COUNTIF(休日シート!$B$4:$B$10,C4)>=1 」と入力します。③「書式」に文字色白で背景赤を選択して④「OK」をクリックします。

Excel 書式のルールの編集

「=COUNTIF(休日シート!$B$4:$B$10,C4)>=1」について

COUNTIF関数は条件に合ったセルの数を数える関数です。

休日の日付が入力されている「 休日シート!$B$4:$B$10 」内に予定表の「C4」の日付があるか検索して1個以上ある場合に「条件付き書式」が設定されます。

C4のセルをドラッグして条件付き書式を他のカレンダーに設定してカレンダーの完成です。

条件付き書式挿入済み予定表

会社の休日と日曜日の色を分けたい場合

日曜日は赤色、会社の休日は青色にしたい場合は、先ほどは「COUNTIF」を使いましたが、今度は「VLOOKUP」を使います。

「休日シート」に新たに休日区分をつくり会社休日を1、日曜日を2にして設定します。

休日を色分けした場合のカレンダー

条件付き書式を二つ登録します。

一つ目が会社休日の条件付き書式で文字が白で背景を青にします。

=VLOOKUP(C4,休日シート!$B$4:$C$10,2,FALSE)=1

Vlookup 休日に挿入

二つ目が日曜日の条件付き書式で文字が白で背景を赤にします。

=VLOOKUP(C4,休日シート!$B$4:$C$10,2,FALSE)=2

Vlookup 挿入

予定表に休日が色分されて表示されました。

予定表が色分け完了カレンダー

うまく表示されなかった場合は「条件付き書式」から「ルールの管理」をクリックします。

条件付き書式 ルールの管理

作成した条件付き書式のルール一覧がありますのでクリックして再編集します。

条件付き書式のルールの管理

VLOOKUP(C4,休日シート!$B$4:$C$10,2,FALSE)=1 と  VLOOKUP(C4,休日シート!$B$4:$C$10,2,FALSE)=2 について

VLOOKUP関数は検索条件に一致するデータを指定範囲の中から探して表示してくれる関数です。

検索条件「C4」の日付があるか指定範囲「休日シート!$B$4:$C$10」内を探して見つかれば「2」2列目 の休日区分を取り出し「1」だった場合、会社休日の条件付き書式を表示して「2」だった場合日曜日を条件付き書式を表示します。

まとめ

休日マスタを作成しないと休日の一覧データがない場合、少々手間ですがExcelで休日マスタを作成しておけば予定表以外に納期の連絡や確認をするための納期表などにも活用ができます。

年単位や月単位、週単位の予定表など作成する場合、手入力で休日の色を付けてしまうことが多いですが、Excelには予定表を作成するのに便利な「条件付き書式」、「関数」などを備えていますのでこれを機会にぜひ利用してみてください。