【シフト表作り】土日祝の色付けを完全自動化しよう

【シフト表作り】土日祝の色付けを完全自動化しよう Excel

こんにちは、零二(@ReijiMinami)です。

前回は年月を指定するだけで日付と曜日が全て自動で入力される方法を説明しました。

今回は、日付や曜日だけではなく、土日祝に合わせて色も完全自動で変わる仕組みを作っていきましょう。

零二
零二

見た目をデザインする時間がなくして、
シフトを組むことだけに時間を使えるね!

今日からあなたの職場のシフト表は
年月を入れるだけ土日祝の色付けが完全に自動化されます

スポンサードサーチ

祝日がいつなのかを別シートで用意する

Excelは土日がいつなのかは計算で分かりますが、
祝日についてはこちらで「この日だよ」と教えないと分かってくれません。

そこで、勤務表のシートとは別に、祝日の日付一覧が書かれたシートを作っていく必要があります。

東子
東子

祝日は自分で入力しなければならないの?面倒だな~

零二
零二

まさか!?ちゃんとお国が用意してくれているから大丈夫だよ

日本の祝日の公式データがきちんと政府から無料で提供されています。
それを活用していきましょう!

内閣府のホームページから祝日データをダウンロード

初めに、以下のURLにアクセスします。

【国民の祝日について – 内閣府】
https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html

次に、ページの少し下の方にcsv形式と書かれたリンクを見つけます。
今分かっている日本の祝日が書かれたCSVファイルがあるので、それをダウンロードして下さい。
(ちなみに上図は本記事を執筆したときのものです。)

それでは開いて内容を確認してみましょう。

コラム
数年前までは直近2年分の祝日データが提供されていましたが、最近では加えて過去全ての祝日が書かれたファイルが提供されるようになりました。

祝日のシートを作る

無事公式の祝日データをダウンロード出来たら、
次はシフト表に「祝日」のシートを新たに作成します。

今回は「祝日」と名付けたシートを作成しました。
次に、先ほどダウンロードした内閣府の祝日データを開きます。

全部コピーしても良いですが、シフト表を作るために過去の祝日は不要かと思いますので、自分がこれを作り始める日以降のデータだけコピーすればOKです。

今回は分かりやすく2019年以降の祝日だけコピーして貼り付けました。

祝日のデータを貼り付けたら、A列全体を選択し、上の「A1」となっている部分を「祝日」という名前に変更します。

この部分があとで祝日かどうかを判断する上で非常に重要なので、間違わないようにして下さい。

コラム
ここで朗報です!
この祝日リストに、皆さんの職場で決まっている休みを追加することが出来ます!。

例えば、開院・開設記念日やお盆休み、年末年始の休業日などをここに追加しておけば、祝日と一緒に色分けされるようになります。

以上で「祝日」のシートは完成です。

自動で色が変わるよう設定をする

それでは、いよいよシフト表の日付によって自動で色が変わるよう設定をしていきましょう。

土曜・日曜に色付けする方法

まずは土日から設定していきましょう。

はじめに、曜日や祝日によって色を変えたいセルを全て選択します。
ここでは、上図のように選択しました。

「ホーム」タブにある「条件付き書式」「新しいルール」を選択します。

「数式を使用して、書式設定するセルを決定」を選択し、その下の欄に以下の数式を入力します。

土曜日の設定
=WEEKDAY(1日が入力されているセル)=7
ここではC3なので、
=WEEKDAY(C$3)=7

日曜日の設定
=WEEKDAY(1日が入力されているセル)=1
ここではC3なので、
=WEEKDAY(C$3)=1

【注意】$マークは数字の手前に1つ。「F4」キー2回押すとそのようになるはずです。
【解説】WEEKDAY()関数は、指定したセルの日付の曜日によって数字で表される関数です。今回の場合は、日曜日が1~土曜日が7となっています。

数式を入力したら、その右側の「書式」を選択します。
ここでは私が例として作った色の設定方法を説明しますが、
皆さんはお好みで見た目を設定していただいて構わないです。

まずは土曜日から。
背景色だけでも正直十分だとは思いますが、外枠にも色をつけるとやや引き締まった感じが出ます。

外枠にも色をつける場合は、図に説明してあるように、色を選んだあとに「外枠」を選択しないと反映されないので注意して下さい。

次に日曜日の設定です。
日本人のイメージ的には赤っぽい色が良さそうですね。

ここまで設定し終えると、上図のように土日に自動で色がつくようになりました。

ここで年月を変えて本当に自動で土日に色がつくのか試してみましょう!

祝日に色付けする方法

土日の色付けが完了したら、続いては祝日の色付けです。

曜日や祝日によって色を変えたいセルを全て選択した状態で、
土日の設定時と同じように、「条件付き書式」「新しいルール」を選択します。

「数式を使用して、書式設定するセルを決定」を選択し、その下の欄に以下の数式を入力します。

祝日の設定
=COUNTIF(祝日, 1日が入力されているセル)=1
ここではC3なので、

=COUNTIF(祝日, C$3)=1
となります。

【注意】$マークは数字の手前に1つ。「F4」キー2回押すとそのようになるはずです。
【解説】このCOUNTIF()関数が何をやっているかというと、指定したセルの日付と同じ日付が「祝日」の日付リストにあるかを数える、という意味です。

数式を入力したら、その右側の「書式」を選択します。

今回は上図のように設定しました。
繰り返しますが、皆さんは好きな色に設定してくださいね。

これで祝日にも自動で色がつくようになりました。

祝日と土日の色付けはどちらが優先されるのか?

祝日は、時々土日と重なることがありますよね。
そんな時に、この作ったシフト表ではどちらが優先されるのでしょうか?

答えは、自分で優先順位を決めることが出来ます

以下、やり方です。

「条件付き書式」「ルールの管理」で上図の設定がみられます。
ここで、赤く囲んでいる▲▼マークのボタンを、ルールを選択して押すと、上下に入れ替わります。

これで上に書いてあるほど、優先されるというわけです。
図では土日よりも祝日の色が優先されていますが、
これはお好みで変更して良いので、気になる方は確認しておきましょう。

本記事で紹介している勤務表のサンプルはこちら

本連載記事の3回目まで終えた時点での勤務表サンプルです。
皆さんの職場に合わせて自由に改変していただいて構いません。

まとめ

いかがでしたでしょうか?

これで、日付と曜日の自動入力に合わせて、色付けまでが一瞬で終わるようになりました。
見た目にこだわっている方はここまでだけでだいぶ作成時間が削られたのではないかと思います。

次回からは、いよいよシフト表を組む上で必要な勤務日数の確認や、休みの数などを自動で数えられるようにして、シフトを組む工程を少しでも楽にしていきます。

連載目次: 見やすいシフト表をエクセルで簡単に作る方法

本シリーズでは、見やすいシフト表を時間をかけず少しでも簡単に作れるよう記載しています。

  1. 【シフト表作り】月日の入力を完全自動化しよう
  2. 【シフト表作り】 土日祝の色付けを完全自動化しよう ~今ここ~
  3. 【シフト表作り】 勤務日数や休みの数を自動で数えてみよう
  4. ~以下、随時加筆予定~

コメント

タイトルとURLをコピーしました