【Excel】日付が自動更新される月間スケジュール表★
こんにちは、HARUです！
今回は日付が自動更新される月間スケジュール表を作成していきます。
更新される情報は、以下の通りです。
①指定の年月に応じた日付の自動更新。
（ex.5月は"31日"まで、6月は"30日"まで、2月は"28日"まで）
②日付に対応する曜日の自動更新。
③土日祝日をわかりやすく表現する色分けの自動更新。
たとえば2023年5月を2023年6月に切り替えると……、
こんなイメージです。
スケジュール表に限らず、シフト表や工程管理表といった月単位のフォーマット作成に欠かせないテクニックがふんだんに詰まっていますので、ぜひご一読ください！
事前準備
フォーマットの作成
下記２つの要件を満たす入力欄を用意します。
①年・月の値がそれぞれ独立して入力できる。
②日付とそれに対応する曜日が同じ行に入力できる。
ウィンドウ枠の固定
下へスクロールしても表の見出しが見切れないように、見出しを固定しておくことをおススメします。
見出しの固定は、固定したい行の１つ下のセル（今回の場合はA5セル）をアクティブにした状態で、「表示」タブ→「ウィンドウ」グループ→「ウィンドウ枠の固定」を選択することで実行できます。
祝日リストの作成
別シートに「祝日リスト」用意しておきます。
土日”祝日”に該当する日付を色分けするときに活用します。
引用元：内閣府「国民の祝日」より
日付の自動更新
「年」の欄に"2023"、「月」の欄に"5"を入力しておきます。
月初日の設定
月初日には、DATE関数を使います。
第１引数「年」、第２引数「月」にそれぞれ該当するセルを参照し、第３引数「日」には初日を示す"1"を入力します。
※このDATE関数は月初日にのみ使いますので、参照元は絶対参照でなくても構いません。
結果を見てみると、エラーのような表示になります。
これは入力した値に対して列幅が足りないために返される記号です。
列幅を広げると日付が正しく入力されていることがわかります。
ただ、スケジュール表の対象年月を改めて日付の欄にも明記するのはくどいので、日にちの”日”のみを表示する設定を行います。
日付の表示形式
①日付の範囲を選択する。
②[Ctrl]+[1]で「セルの書式設定」ダイアログボックスを開く。
③「表示形式」タブ→「ユーザー定義」を選択する。
④「種類」の欄に"d"と入力する。（d＝day：日にち）
これにより、年月日のうち日にちのみが表示されます。
２日目以降の設定
続いて、2日以降の日付を入力します。
2日以降は以下の条件分岐が求められます。
①前日（上）のセルが当月の月末日であれば空白とする。
②前日（上）のセルが空白であれば、同じく空白とする。
③上記①②いずれにも該当しなければ、上のセル＋１の日付（シリアル値）とする。
①IF関数で１つ目の条件分岐を設定します。
前日のセルが月末日であるかはEOMONTH関数とDATE関数を組み合わせて判定し、この条件を満たす場合は""(空白)を返します。
②さらにIF関数を挿入し、２つ目の条件分岐を設定します。
上のセルが""(空白)であれば""(空白)を返します。
③３つ目の条件分岐を設定します。
ここまでの条件にいずれも当てはまらなければ、上のセル＋１の日付を返します。
今回は前日のセルが１日なので（月末日でも空白でもないので）、＋１を加えた「"２"日」が表示されます。
2日目の数式を残りの日付にコピーします。
5月の月末日は31日ですので、"31"日まで表示されます。
試しに"6"月に変更すると、"30"日までの表示になります。
曜日の自動更新
「曜日」の表示
曜日の表示にはTEXT関数を使います。
TEXT関数は値のデータを文字列データに変換し、指定した表示形式で表記する関数です。（text：文章、文字列）
今回は日付のシリアル値（値）を曜日表記（文字列）に変換します。
①第１引数「値」に日付のセルを参照する。
②第２引数「表示形式」に"aaa"と入力する。
※"aaa"は値を「月」「火」といった曜日表記に変換する表示形式です。
「月曜日」「火曜日」と表記する場合は"aaaa"と入力します。
2023年5月1日の曜日として"月"曜日が返ります。
2日目以降の曜日の欄に数式をコピーします。
各日付に対応する曜日が表示されます。
土日祝日の色分け
土日祝日をわかりやすく表現するために、「条件付き書式」を活用して以下の設定を行います。
①土曜日のフォント色を「青」にする。
②日曜日と祝日のフォント色を「赤」にする。
③土日祝日に該当する行の背景色を「グレー」に塗りつぶす。
条件付き書式とは、セル（範囲）に入力された値や数式の結果に応じてフォント色や塗りつぶし、罫線といった書式を適用する機能です。
土曜のフォント色を「青」に
曜日の範囲を選択した状態で、「ホーム」タブ→「スタイル」グループ→「条件付き書式」→「新しいルール」にアクセスします。
「新しい書式ルール」ダイアログボックスが開いたら、ルールの種類から「数式を使用して、書式設定するセルを決定」を選択します。
「次の数式を満たす場合に値を書式設定」欄に、以下の数式を入力します。
【=$B5="土"】
「曜日のセルが土曜日」であることを条件に設定しています。
B5セル（代表セル）を列固定の複合参照とすることで、この条件付き書式が選択範囲内のすべての曜日に適用されます。
次に、右下の「書式」を選択します。
「セルの書式設定」→「フォント」タブ→「色」のリストを開き、「青」色を選択します。
求めている色が見つからない場合は、「その他の色」をクリックして「色の設定」にアクセスします。
今回は「標準」タブから「青」色を選択します。
「色の設定」「セルの書式設定」を「OK」で閉じます。
「新しい書式ルール」のプレビューが青字で表記されています。
このダイアログボックスも「OK」で決定します。
土曜日のフォント色が青字になります。
日曜と祝日のフォント色を「赤」に
曜日の範囲を選択し、前述と同じ手順で条件となる数式入力欄に以下のように入力します。
【=OR($B5="日",COUNTIF(祝日リスト!$B:$B,$A5)>0)】
OR関数は、ＡまたはＢといういずれかの条件に該当するかどうかを判定する関数でしたね。
今回は「日曜」または「祝日」に該当するかが条件となり、日曜は土曜のときと同じ要領で【$B5="日"】です。
そして、祝日の判定にはCOUNTIF関数を用います。
COUNTIF関数は、対象範囲内に検索条件となるデータがいくつ含まれるかを数える関数でした。
COUNTIF関数部分を抜き出した数式がこちらです。
【COUNTIF(祝日リスト!$B:$B,$A5)】
ここでは、第１引数の検索対象範囲に祝日リストの日付の列を参照し、第２引数の検索条件に日付のシリアル値を参照しています。
祝日リストにおいてその日のシリアル値の個数を数え、それが0以上であるか、要は祝日に該当するかを最後に【>0】で判定しているのです。
最後に、土曜日と同じ手順で今度はフォント色を「赤」に設定し、すべてのダイアログボックスを「OK」で閉じます。
日曜日と祝日のフォント色が赤字になります。
土日祝日の背景色を「グレー」に
スケジュール表の範囲をすべて選択します。
前述と同じ手順で条件となる数式入力欄に以下のように入力します。
【=OR($B5="土",$B5="日",COUNTIF(祝日リスト!$B:$B,$A5)>0)】
土曜：【$B5="土"】
日曜：【$B5="日"】
祝日：【COUNTIF(祝日リスト!$B:$B,$A5)>0】
これらはいずれも、ここまでフォント色を変えてきた数式と同じ構成です。
フォントは土曜（青）と日曜祝日（日）で異なる色だったため２回に分けて設定しましたが、今回は土曜も日曜も祝日も同じグレーで塗りつぶしますので、OR関数にすべてのパターンを入れ込むことで一括設定します。
数式を入力したら「書式」を押し、「塗りつぶし」タブに切り替えます。
グレーの背景色を選択し、すべてのダイアログボックスを「OK」で閉じます。
土日祝日の背景色がグレーになります。
ただこのままだと、月によっては月末の空白行までグレーに塗りつぶされます。
そのため最後に、日付の存在しない行が塗りつぶされない設定をします。
空白行の塗りつぶしを「なし」に
スケジュール表の範囲をすべて選択し、前述と同じ手順で条件となる数式入力欄に以下のように入力します。
【=$A5=""】
日付のセルが空白("")であるかどうかを判定するシンプルな構成です。
数式を入力したら「書式」を押し、塗りつぶしの「色なし」を選択します。
そして、すべてのダイアログボックスを「OK」で閉じます。
月末の空白行は塗りつぶしがなくなります。
日数の少ない2月でも、書式が正しく設定されています。
いかがでしたか？
今回は日付を自動更新する月間スケジュール表の作り方をご紹介しました。
シリアル値のしくみや表示形式、論理関数による条件分岐、条件付き書式といったExcelスキルをふんだんに活用した便利なアイテムですので、ぜひ活用してみてくださいね！
↓月間スケジュール表サンプルデータはこちらから無料ダウンロードできます↓
https://ux.getuploader.com/HARUHARU/download/14
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？