excel2016 ある月の第n水曜日を求める(第x回目の●曜日)
方針
1 求める月を決める(2020年2月)
2 月初を基準日とする(2020年2月1日)
3 第0水曜日を求める(2020年1月29日)
4 第n水曜日を求める(2020年1月29日+n×7)
5 第n水曜日が休日だったら、翌営業日(前営業日)にする
計算
2 date関数を使うと、年月が個別に入ったセルから月初を求められる。
=date(year(2020),month(2),1)
求めたい月の日付が入ったセルがあれば、eomonth(日付、0)でもよい。
3 月初から1以上の数を引くと確実に前月になる。月初から1~7を引けば、当月の第1週の前週(第0週)を求められる。その値を求めるのがweekday関数(曜日(と第2引数)に応じて0~7の数字を返してくれる)。第2引数に1(第2引数を省略しても同じ)を使うと、戻り値として「1(日曜)~7(土曜)」を返す。「求めたい曜日に対応する値を引いてからweekday関数した値」を引けば、その曜日を始点にできる。
なので、
=月初-weekday(月初-4)
とすれば第0水曜日。
3番の検証
weekday(月初)は、月初の曜日に応じて
日 月 火 水 木 金 土
1 2 3 4 5 6 7
を返す。1スタートと考えるのが自然だが、ここでは1週間7日間の倍数である7を持っている土曜が基準になっていると考えるとすっきりする。(第2引数を3にしたときは、戻り値が0(月曜)~6(日曜)となるが、そのときは7の倍数0を持つ月曜が基準と捉えられる。月初から0を引いても第0週にならず、月初のままなので、今回のように月初を基準日として計算する場合に0を戻り値として持たせるのは分かりにくい。0を基準とするのは、前月末を基準にするときがよい。⇒井上先生のブログ)
weekday(月初-1)は、月初の曜日に応じて
日 月 火 水 木 金 土
7 1 2 3 4 5 6
を返す。月初が何曜日であっても、月初-weekday(月初-1)は第0日曜になる。
詳細:weekday(月初-1)の値は、月初が月曜なら1、火曜なら2、水曜なら3、木曜なら4、金曜なら5、土曜なら6、日曜なら7となるから、
月初-weekday(月初-1)
は必ず日曜になる。そして、引いているのは1~7の値だから、月初から引けば必ず先月になっており、なおかつ、その週は必ず第0週に該当する。
weekday(月初-2)は、月初の曜日に応じて
日 月 火 水 木 金 土
6 7 1 2 3 4 5
を返す。月初が何曜日であっても、月初-weekday(月初-2)は第0月曜になる。
weekday(月初-3)は、月初の曜日に応じて
日 月 火 水 木 金 土
5 6 7 1 2 3 4
を返す。月初が何曜日であっても、月初-weekday(月初-3)は第0火曜になる。
weekday(月初-4)は、月初の曜日に応じて
日 月 火 水 木 金 土
4 5 6 7 1 2 3
を返す。月初が何曜であっても、月初-weekday(月初-4)は第0水曜になる。
weekday(月初-5)は、月初の曜日に応じて
日 月 火 水 木 金 土 木
3 4 5 6 7 1 2
を返す。月初が何曜日であっても、月初-weekday(月初-5)は第0木曜になる。
weekday(月初-6)は、月初の曜日に応じて
日 月 火 水 木 金 土
2 3 4 5 6 7 1
を返す。月初が何曜日であっても、月初-weekday(月初-6)は第0金曜になる。
weekday(月初-7)は、月初の曜日に応じて
日 月 火 水 木 金 土
1 2 3 4 5 6 7
を返す。月初が何曜日であっても、月初-weekday(月初-7)は第0土曜になる。
(7も0も7で割った余り0なので、weekday(月初-7)はweekday(月初ー0)と同じ値になる。曜日の値を引くという点では、7を引くと統一感があると思う。)
4 第n水曜日を求める
第n水曜日=第0水曜日+7×n
だから、
第1水曜日=月初-weekday(月初-4)+7×1
第2水曜日=月初-weekday(月初-4)+7×2
第3水曜日=月初-weekday(月初-4)+7×3
第4水曜日=月初-weekday(月初-4)+7×4
第5水曜日=月初-weekday(月初-4)+7×5
5 第n水曜日が休日だったら、翌営業日(前営業日)にする
(1) 事前準備
A1セルに月初の日付「2020/2/1」を入力し、「月初」と名前の定義をした。
C1セルに「休日」、C2セルに「2020/2/11」C3セルに「2020/2/24」を入力し、セル範囲C1:C3をテーブル設定して、C2:C3 に「休日TBL」と名前の定義をした。下に休日を書き足せば自動拡張する(連続さえしていれば順不同でよい)。
(2)翌営業日(前営業日)
翌営業日=workday(第n水曜日ー1, 1,休日TBL)
前営業日=workday(第n水曜日+1,-1,休日TBL)
作業用に、
E1セルに第1水曜日
E2セルに第2水曜日
E3セルに第3水曜日
E4セルに第4水曜日
E5セルに第5水曜日
を入力しておき、翌営業日(前営業日)の関数の「第n水曜日」のところはE列のセルを指定する。workday関数の第1引数で第n水曜日の前日(翌日)を指定し、第2引数で1(翌営業日)かー1(前営業日)を指定し、第3引数で休日を入力したセル範囲を指定する。第2引数がプラスならば日付は後ろに倒れていくし、マイナスなら前に倒れてくれるので助かる。workday関数ありがたや。