【Excel Chips】土日祭日の日付に自動で色を付ける

Excelのシートに、土日と祝日の行に色をつける方法です。

WEEKDAY関数で土日かどうかを判断する

日付から曜日の番号を取り出すWEEKDAY関数

B2セルに日付のシリアル値がはいっているとして、論理式

=WEEKDAY(B2,2)>=6

とすれば、TRUEかFALSEが返ってきます。

「条件付き書式」は論理式の結果がTRUEの場合に、セルの書式を有効にするためのものです。これを利用します。

これで土日の判断はできました。

COUNTIF関数で祝祭日を判断する

Excelには祝祭日を判断する関数はありません。
そこでCOUNTIF関数を使います。
祝祭日のリストを作っておき、ある日付がこの中にあるかどうかを数えるのです。
数えた結果「１」であれば、その日付は祝祭日であると判断できます。

=COUNTIF(範囲,条件)

引数「範囲」に祝祭日のリスト範囲を、「条件」には調べたい日付を指定します。
右図の例では、論理式

=COUNTIF(G3:G19,B2)=1

とすれば、5月4日は「みどりの日」なのでTRUEが返ってきます。

OR関数でWEEKDAY関数とCOUNTIF関数を結合する

WEEKDAY関数で土日の判断をして、COUNTIF関数で祝祭日の判断をし、そのどちらかがTrueであればセルを色塗りすれば良いのです。OR関数を使います。

A3セルに日付が、G3:G19に祝祭日が入力されているとして、

=OR(WEEKDAY(A3,2)>=6, COUNTIF(G3:G19,A3)=1)

の結果がTRUEであれば、土日または祝祭日であると判断できます。
この式を条件付き書式に設定してあげます。

条件付き書式を設定する

これからが実際の手順となります。

WEEKDAY_2.xlsxをダウンロードして使ってください。

祝祭日の表をテーブルに変換する

2023年5月のスケジュール表と、2023年の祝祭日リストを用意します。（WEEKDAY_2.xlsxのシート「設定前」を開く）

①祝祭日の表の任意のセルをクリックしてアクティブにし、②「挿入」タブ「テーブル」をクリックしてテーブルに変換します。

テーブルにしておけば、祝祭日が増えても条件付き書式を変更する必要がなくなります。

「先頭行をテーブルの見出しとして使用する」に✅を忘れないでください。

条件付き書式の手順

スケジュール表のA3:C33を範囲選択します。

「ホーム」タブから「条件付き書式」を選択し、「新しいルール」をクリックします。

一番下の「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」の欄に次の論理式を入力します。

=OR(WEEKDAY($A3,2)>=6, COUNTIF($G$3:$G$19,$A3)=1)

ポイントは、A3をクリックしたあと、F4キーを3回押してA列だけ固定の複合参照 $A3にしておくことです。

条件付き書式はA~C列に設定しますが、コピーしても条件となるセルの列が変動しないようにA列を固定しておくのです。

※カーソルの移動に←→キーを使うと不要なセル番地が入力されます。

祝祭日の範囲は、選択すれば自動的に絶対参照になり、$F$3:$F$13となります。

次に「書式」ボタンをクリックして書式を設定します。

「塗りつぶし」タブをクリックして適当な色を選択します。

以上で完成です。2023年5月は5連休になることが確認できます。

祝祭日を追加する
テーブル形式に変換してあるので何も変更の必要がない。

祝祭日は毎年変わります。

さらに年末年始、夏の休業や会社の創立記念日など独自の休日を設定することもあります。そのときも、表をテーブルに変換してあるので、何も変更する必要がありません。

実際にやってみましょう。

5月1日が創立記念日だと仮定追加します。

祝祭日の表の下の行(E20:F20)に次のデータを入力します。

創立記念日  2023/5/1

2023/5/1のセルが色塗りされました。

F2セルのフィルターボタンで「昇順」を選んで並び替えておきます。