いまさら聞けないExcelの使い方講座

【Excel】年・月・第○週を入力すると自動で日付が切り替わる当番表を作る方法

今井孝

2023年9月20日 06:55

「年・月・第○週」指定で日付を更新できます

毎月「第○週」の当番表を作る

窓の杜から
ダウンロード

　スケジュールをデジタルデータとして管理する前に、手書きが必要な場面がありますよね。例えば、作業の割り振りを決めるために紙の表を回覧したり、外回りの現場でパソコンが使えなかったりする時も紙のチェック表が使われます。

囲み部分を手書きで記入するために、日付を書き換えて表を使い回すことがあるのではないでしょうか？

　この表を使い回す場合、担当欄はそのままで日付を書き換えて印刷するでしょう。セルB2の「10月9日」を書き換えてオートフィルでコピー、もしくは、セルC2～H2まで「=B2+1」のような数式が入力されていて、セルB2と連動して日付が更新されるかもしれません。

　しかし、この表を毎週作るのは面倒。何週分かまとめて印刷したくなりますが、週初めの日付を間違えて、やり直す状況が容易に想像できます。今回は、週替わりの当番表を例に「年」「月」「第○週」を指定して日付を更新できる仕掛けを紹介します。

DATE関数で月初日を求める

　「年」と「月」の入力欄を用意しておきます。以下の例は「2023」年「10」月を意図しています。「年」「月」の情報があれば、DATE関数を使って月初日（一日）を簡単に求められます。DATE関数の引数［年］［月］［日］に数値を指定すると「=DATE(A2,B2,1)」となります。「1」は一日の意味です。

セルA2に「年」（①）、セルB2に「月」（②）を入力できるようにしてあります。「2023」年「10」月の意味です。セルE2には「=DATE(A2,B2,1)」（③）と入力すると「10月1日」と表示されます

WEEKDAY関数で月初日の曜日を求める

　続けて、WEEKDAY関数を使って「2023/10/1」の曜日を求めます。WEEKDAY関数は日付（シリアル値）の曜日にあたる数値を返します。構文は以下の通りです。引数［週の基準］を省略した場合、日曜日～土曜日にあたる数値として「1」～「7」が返されます。

WEEKDAY関数の構文。引数［シリアル値］に指定した日付の曜日にあたる数値を返します

セルF2に「=WEEKDAY(DATE(A2,B2,1))」（④）と入力したところ「1」（日曜日）と表示されました

DATE関数とWEEKDAY関数から週初めの日付を求める

　WEEKDAY関数の結果「1」から2023/10/1は日曜日であることがわかりました。日～土には「1」～「7」が割り当てられているため、この数値を元の日付から引くことで直前の土曜日の日付を求めることができます。以下に各月の計算結果を求めてみました。

セルC1に「=DATE(A1,B1,1)」（⑤）、セルD1に「=WEEKDAY(DATE(A1,B1,1))」（⑥）、セルE1は引き算の「=C1-D1」（⑦）と入力してあります。いずれも直前の土曜日の日付が求められていることがわかります

　以上をふまえて元の表に戻ります。土曜日（7）の2日後が月曜なので「+2」すれば、月初日が含まれる週の月曜日の日付を求められます。数式は「=E2-F2+2」です。

セルG2に「=E2-F2+2」（⑧）と入力しました。第一週の月曜日は「10月2日」であることがわかります

　なお、DATE関数とWEEKDAY関数を組み合わせて「=DATE(A2,B2,1)-WEEKDAY(DATE(A2,B2,1))+2」としても同じ結果を求めることができます。

SWITCH関数で「第○週」を切り替える

　指定した「第○週」によって日付が書き換わるようにします。数値によって場合分けするには、SWITCH関数が便利です。セルG2の数式で求めた日付を基準に「+7」「+14」……と足し算します。存在しない第六週などが指定された場合は「月を切り替えてください」と表示させます。

SWITCH関数の構文。［検索値］に一致する［値］に対応する［結果］を返します。［検索値］が［値］に該当しない場合は［既定の結果］を返します

セルC2には「1」と入力してあります。セルB5の日付を「=SWITCH(C2,1,G2,2,G2+7,3,G2+14,4,G2+21,5,G2+28,"月を切り替えてください")」（⑨）と書き換えました

10月の第三週に切り替える（⑩）と日付が切り替わります（⑪）

12月の第二週に切り替えた状態です（⑫）。12月の第二月曜日ではなく、第二週の月曜日であることに注意してください

第六週などと指定すると「月を切り替えてください」（⑬）と表示されます

　この数式の結果は月の第○月曜日ではなく、第○週目の月曜日を表示していることに注意してください。例えば第一月曜日を求めるには、IF関数でWEEKDAY関数の結果を判定して「=IF(WEEKDAY(E2)>2,7-WEEKDAY(E2)+2,2-WEEKDAY(E2))+E2」のような数式が考えられます。