【Excel】毎月の更新に手作業不要！ 予定表は土日・祝日・末日対応でも自動更新

「年」「月」「日」の表示形式を整える

　「年」「月」「日」を入力するセルには「表示形式」を設定しておきましょう。まず、「2024」や「1」と入力するだけで、「2004年」「1月」と表示できるようにします。

「2024」と入力されているセルA1を選択して（①）、［Ctrl］＋［1］キーを押します（②）

［セルの書式設定］ダイアログボックスが表示されます。［ユーザー定義］（③）をクリックして、［種類］に「#"年"」と入力します（④）。［OK］（⑤）をクリックします

セルA1の表示が「2024年」になりました。数式バーを確認すると、データは「2024」となっていることがわかります。数値を入力すれば自動的に「年」が追加されるようになりました

同じ要領でセルB1の表示形式も変更します。［Ctrl］＋［1］キーを押して（⑥）［セルの書式設定］ダイアログボックスを表示。［ユーザー定義］（⑦）を選択して、「#"月"」（⑧）と入力します。［OK］（⑨）をクリックします

セルB1の表示が「1月」になりました

　続けて「2024/1/1」を「1月1日（月）」と表示させてみましょう。［Ctrl］＋［1］キーを押して［セルの書式設定］ダイアログボックスを表示する操作は同じです。「1月1日（月）」のように表示するには「m"月"d"日"(aaa)」と指定します。

［Ctrl］＋［1］キーを押して（⑩）［セルの書式設定］ダイアログボックスを表示し、［ユーザー定義］（⑪）を選択。「m"月"d"日"(aaa)」（⑫）と入力します。［OK］（⑬）をクリックします

「1月1日（月）」と表示されました

更新可能な日付の仕掛けを作る

　日付をいきなりオートフィルしてはいけません。自動化するために「=A3+1」のような数式を入力します。一度だけオートフィルが必要です。「31日」分までコピーしてください。

セルA4に「=A3+1」と入力します（⑭）

セルA1の「1月1日（月）」に「+1」した「1月2日（火）」と表示されました

セルA4の数式を31日分コピーします（⑮）

　続けて先頭の日付「1月1日（月）」を「=DATE(A1,B1,1)」という数式に書き換えます。DATE（デイト）関数は「年」「月」「日」の数値から、日付に変換するための関数です。セルA1の「2024」とセルB1の「1」を参照して、「1日」を表す「1」を3つめの引数に指定することで、「2024/1/1」という日付が表現されます。

　セルの表示形式を変更してあるため、変更前と変わらずに「1月1日（月）」と表示されますが、セルA1の「年」とセルB1の「月」を変更することで、スケジュール表の日付が自動的に切り替わるようになります。

セルA3に「=DATE(A1,B1,1)」と入力します（⑯）。セルA1の「2024」とセルB1の「1」を参照して、「1日」を表す「1」を3つめの引数に指定しています

表示は「1月1日（月）」のままですが、数式バーで確認すると「=DATE(A1,B1,1)」（⑰）と入力されていることがわかります

セルB1を「2」に変更すると（⑱）、日付が自動的に更新されます（⑲）。「3月1日（金）」「3月2日（土）」が表示されてしまっていますが、後ほど非表示にするのでこのままにしておきます

土日の行に色を付ける

　［条件付き書式］を利用して、土日の行に色を付けます。日付に対応する数値を返すWEEKDAY（ウィークデイ）関数を利用して土日を判定します。「1」が日曜日、「7」が土曜日を表すことを利用します。

条件付き書式を設定するセル範囲を選択しておきます（⑳）。［ホーム］タブ（㉑）の［条件付き書式］（㉒）－［新しいルール］（㉓）の順にクリックします

［新しい書式ルール］ダイアログボックスが表示されます。［数式を使用して、書式設定するセルを決定］（㉔）を選択して「=WEEKDAY($A3)=1」（㉕）と入力します。［書式］（㉖）をクリックして、セルの背景色とフォントの色を設定します。［OK］（㉗）をクリックします

日曜日の行の書式が自動的に設定されました

同様の手順で土曜日の書式を設定します。設定するセル範囲を選択してから［新しい書式ルール］ダイアログボックスを表示しておきます。［数式を使用して、書式設定するセルを決定］を選択（㉘）して「=WEEKDAY($A3)=7」（㉙）と入力します。［書式］（㉚）をクリックして、セルの背景色とフォントの色を設定します。［OK］（㉛）をクリックします

土曜日の行の書式が自動的に設定されました

祝日の行に色を付ける

　祝日の行に色を付けるには、COUNTIF（カウント・イフ）関数を利用します。［新しい書式ルール］ダイアログボックスに入力する「=COUNTIF($E$3:$E$23,$A3)=1」という数式は、セルE3～E23の中からセルA3の値を数える処理になります。一致する日付があれば、結果は「1」となり、「=1」で日付の有無を判定できます。

　なお、祝日リストは内閣府のWebページに掲載されています。

祝日リストを用意しておきます（㉜）。セル範囲を選択して（㉝）、［新しい書式ルール］ダイアログボックスで「=COUNTIF($E$3:$E$23,$A3)=1」（㉞）という数式を入力します。セルの背景色とフォントの色を設定（㉟）して［OK］（㊱）をクリックします

祝日の行の書式が自動的に設定されました

末日を非表示にする

　2，4，6，9，11月は、31日までありませんよね。オートフィルで数式をコピーしているため、「3月1日（金）」「3月2日（土）」と翌月の日付が表示されています。

「3月1日（金）」「3月2日（土）」と翌月の日付が表示されています

　翌月の日付は『月初の日付の「月」と一致するかどうか』という条件を利用して、非表示にすることが可能です。MONTH（マンス）関数を使います。

　MONTH（マンス）関数は、日付から「月」を取り出す関数です。セルA3に入力されている日付を比較して、一致していない場合という数式「=MONTH($A$3)<>MONTH($A3)」を入力します。この条件を満たす場合に「塗りつぶしなし」「白文字」と書式を変更すれば、日付が隠れて見えるようになるわけです。

［新しい書式ルール］ダイアログボックスで「=MONTH($A$3)<>MONTH($A3)」（㊲）という数式を入力します。セルの背景色を［なし］、フォントの色を［白］に設定（㊳）して［OK］（㊴）をクリックします

翌月の日付が非表示になりました。

例えば、4月（㊵）に切り替えた時は「4月30日（火）」まで表示されます

［条件付き書式］を活用しよう

　［条件付き書式］では日付に関する関数がよく利用されます。ここで紹介した条件式は定番の使い方といってもいいでしょう。失敗しても問題の少ない個人用のスケジュール表で練習してみるのはいかがでしょうか。