【Excel】 かなり使えるシフト表作成、日付29日以降と曜日を関数で自動入力

Excel関数だけで、日付と曜日を自動に決定するシフト表サンプルを作る

シフト表サンプルを管理している人の職種を考えて、どんな表だと都合が良いのか？

IF関数で入れ子にすると、ちょっと面倒な式になる。

VBAを他で使う処理の場合は良いが、このためだけにVBAをわざわざ入れるのは避けたい。

という場合はある。

特に、シフト表などは、上位の職種（課長・主任）が原案を作成し、最終的に上位者に承認を貰うという流れがオーソドックスだと思う。

そうなると、自分以外の誰かが表を触る際に、特にVBAが入っている必要はなく、入っていない表の方が自分にとっても相手にとっても都合が良い。

プログラム的に、うるう年を判定するフロー図

うるう年判定 (Yesが下、Noが右のパターン）

・年÷４　　　余りが０ではない　→　２８：うるう年ではない

　↓　余り０

・年÷１００　余りが０ではない　→　２９：うるう年

　↓　余り０

・年÷４００　余りが０ではない　→　２８：うるう年ではない

　↓　余り０

２９：うるう年

うるう年判定 (Yesが右、Noが下のパターン）

• 年÷４　余り０　→　÷１００　余り０　→　÷４００　余り０　→　２９：うるう年
  • （年÷１００）　余り０ではない　→　２９：うるう年
    • （年÷４００）　余り０ではない　→　２８：うるう年ではない
• 年÷４　余り０ではない
  • ２８：うるう年ではない

これをIF関数の入れ子にすれば表は完成するが、見にくいし修正しづらい。

これだと３１日まで入れておいて、手動で毎月日付をいじる方が運用的にはベターだと思う。

それでも重箱の隅をつつかれて嫌な思いをする相手もいるかもしれないので、自分のシートにはマクロ付きのシートで管理して、共有フォルダーには関数（もしくは値）だけの表でアップするというのは、私が現役時代によく使っていたやり方だ。

Excel書式設定「 0 表示 」「曜日 表示」「条件付き書式設定で土日を色指定」

完成サンプル（縦：No.3～8、横：日付４～２７は省略）

書式設定変更

• 年
  • ０年
• 月
  • ０月
• 曜日
  • aaa・・・日・月・火・水・木・金・土
  • ddd・・・Sun・Mon・Tue・Wed・Thu・Fri・Sat
• 名前右横（表では１の下から３１の下　金～日まで）
  • 条件付き書式設定：「日」を設定
    • 範囲を選択
    • ホーム　→　条件付き書式設定
    • セルの強調表示ルール(H)
    • 指定の値に等しい(E)
    • 値：「日」　書式：「濃い赤の文字、明るい赤の背景」
    • OK
  • 条件付き書式設定：「土」を設定
    • 範囲を選択
    • ホーム　→　条件付き書式設定
    • 指定の値に等しい(E)
    • 値：「土」
    • ユーザー設定の書式
      • 色を「青」に変更
      • OK

「年」の書式設定（月も同様）

「月」も同様に設定すると

• 2021と入力すると「2021年」と表示
• 10と入力すると「10月」と表示される

※こうすることで、数値として扱えるので文字列操作せずに値を使えるので関数・VBAでは便利

曜日の書式設定

「曜日」の書式設定はユーザー定義で「 aaa 」か「 ddd 」をよく使う

条件付き書式設定は行や列そして範囲して使えるExcel標準の便利な設定だが・・・

※シートをコピペすると、書式もコピーされるので気にせず作成すると勝手に条件付き書式が発動することがあるので、新しく作る際は見直す必要がある。

関数を設定する

• 曜日　１の下から３１の下まで（サンプル表では金～日）
  • １～２８まで：=WEEKDAY(DATEVALUE($A$1&”/”&$C$1&”/”&I1))
  • ２９：=IF(J1=””,””,WEEKDAY(DATEVALUE($A$1&”/”&$C$1&”/”&J1)))
  • ３０、３１は２９のセルをコピーすればＯＫだが一応記載しておくと
  • ３０：=IF(K1=””,””,WEEKDAY(DATEVALUE($A$1&”/”&$C$1&”/”&K1)))
  • ３１：=IF(L1=””,””,WEEKDAY(DATEVALUE($A$1&”/”&$C$1&”/”&L1)))
• 29：=IF(DAY(DATE(A1,C1,29))=1,””,29)
• 30：=IF(C1<>2,30,””)
• 31：=CHOOSE($C$1,31,””,31,””,31,””,31,31,””,31,””,31)

※シフト表は「年：月」を設定すれば自動的に日付と曜日が自動的に表示される関数シートが完成

※２９はExcelのDate関数を使って2021.2.29と入力すると賢いExcelが自動的に2021.3.1と変換してくれるのを利用して「日付＝２９」を判定している。

もし他の表でVBAを使う必要があるなら、わざわざ枠だけ関数で作成せずVBAで指定した方が後々修正するには簡単だし個人の汎用性は高い！と言っておく。

編集後記

私が勤めていた設備管理だと、シフト表のほかにも日々の朝勤、夜勤、24H勤務、遅番や週間業務、月次処理などを見るため用に、毎月配置表をホワイトボードに書いたり別紙をわざわざ作成して管理しているような事をしていた。

全員の配置表をコピペして、自分の名前だけ際立つ表示をするなら上記サンプルでも使用した「条件付き書式」でできるが、他の人の名前は全部消して、自分の名前だけ残すといった特殊な使い方はVBAでないと関数だけでは、ちょっと難しい作業になってしまい出来上がりが関数だらけで修正しづらいシートを作ってしまうことになり、表の作成よりも関数のデバッグで時間を割くといった本末転倒なことが良く起きる。

次回は今回作成したシフト表から配置表に転記する「VBAを使った値を転記する表」を実際に作成していく。

それでは、またね～😎