いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Excel)期間中の最後のx曜日を求める

 Excelで、指定した期間内の日付でx曜日であるもののうち最後のものを求める方法についてです。
 ここではSEQUENCE関数を使って候補となる日付を生成し、FILTER関数等で絞り込むアプローチを紹介します。

  • SEQUENCE関数やFILTER関数などを使って、指定した期間内の最後のx曜日の日付を求められます。

手順

期間の初日と末日を指定する例

f:id:accs2014:20200920223242p:plain:right:w550

 画像ではB3:B5の範囲に次のように条件が記されています。

B3…期間の初日
B4…期間の末日
B5…曜日
 (1…日曜,2…月曜……)

 F2セルに式を入力して、この条件にマッチする最後の日の日付を求めるものとします。


 

 F2セルに式を入力したところです。結果は数字(シリアル値)となって表示されますので、セルの書式設定で表示形式を「日付」に変更すると…‥

f:id:accs2014:20200920223246p:plain:w780
 

 完了です。2020年9月1日から30日までのうち最後の火曜日は「2020/9/28」であることが示されています。

f:id:accs2014:20200920225214p:plain:w780

 さて、F2に入力した式の内容ですが、次のようになっています。

=LET(期間,SEQUENCE(C3-C2+1,,C2),MAX(FILTER(期間,WEEKDAY(期間)=C4)))

 「SEQUENCE(C3-C2+1,,C2)」の部分で期間中のすべての日付を生成し、FILTER関数とWEEKDAY関数で条件に該当する曜日(ここでは火曜日)の日付を抽出し、MAX関数でそれらのうち最後の日付を抽出しています。
 LET関数を用いることで「SEQUENCE(C3-C2+1,,C2)」を繰り返し入力せずに済み、式を若干短くできています。


対象となる月(年月)を指定する例

 検索対象の期間を「年」と「月」の数値で指定する場合の例です。
 早速ですが画像を結果で示します。設定と表示形式の変更については上記の例と同様です。

f:id:accs2014:20200920231735p:plain:w780

 F2セルの式は次のとおりとなっています。

=LET(期間,SEQUENCE(7,,DATE(C2,C3+1,0),-1),FILTER(期間,WEEKDAY(期間)=C4))

 「SEQUENCE(7,,DATE(C2,C3+1,0),-1)」の部分で月末の7日間の日付を生成し、あとはFILTER関数とWEEKDAY関数で条件に該当する曜日の日付を抽出し、あとはFILTER関数とWEEKDAY関数で条件に該当する曜日のものを抽出しています。
 対象期間が1か月間(少なくとも28日間)ありますので、月末の7日間を抽出しておけばよいのがポイントです。7日のうち条件の曜日に該当する日付は1つしかありませんので、最初の例のようなMAX関数は必要ありません。

対象となる月が属する日付を指定する例

 指定された日付に対し、その日付が属する月の最後のx曜日を求める例です。
 こちらも早速ですが画像を結果で示します。設定と表示形式の変更については上記の例と同様です。

f:id:accs2014:20200920231741p:plain:w780

 F2セルの式は次のとおりです。

=LET(期間,SEQUENCE(7,,EOMONTH(C2,0),-1),FILTER(期間,WEEKDAY(期間)=C3))

 ここでも「SEQUENCE(7,,EOMONTH(C2,0),-1)」で月の最後の7日間の日付を生成し、FILTER関数とWEEKDAY関数で条件に該当する曜日の日付を抽出しています。
 条件が日付で指定されているため「EOMONTH(C2,0)」で月末の日付を取得できるのがポイントで、「DATE(YEAR(C2),MONTH(C2)+1,0)」などとするより若干短くできます。

備考

 ちなみに、「最初の」x曜日の日付についても上記と同じような考え方で求められます。
 具体的には次の記事のやり方が挙げられます(n=1とする)。
 
www.officeisyours.com