Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2016)
第2土曜日の年月日を求める
投稿日時: 23/12/07 17:34:00
投稿者: shimoichimabu

3月と9月の第2土曜日の年月日を求める式をネットから引っ張ってきましたが、
 
= DATE(年,月,2*7-WEEKDAY(DATE(年,月,-6),2)) だと何故か
2024年では、9月7日
2026年では、3月7日
2030年では、9月7日
と第1土曜日になります。
年度によって違ってくる場合があります。
式が間違っているのでしようか?
 
但し、
= DATE(年,月,2*7-WEEKDAY(DATE(年,月,-5),3))
こちらは、間違いなく求めることができます。
 
=FLOOR(前月末,7)+7*2
こちらも間違いなく求めることができます。

回答
投稿日時: 23/12/07 19:21:06
投稿者: んなっと

9/14が(土)ならば、9/14(土)そのものが第2土曜日。
9/14が(日)ならば、1日前の9/13(土)が第2土曜日。
9/14が(月)ならば、2日前....
9/14が(金)ならば、6日前が第2土曜日。
 
表にすると
 
     A     B
1   日付 戻るべき日数
2 9/14(土)     0 ★
3 9/14(日)     1
4 9/14(月)     2
5 9/14(火)     3
6 9/14(水)     4
7 9/14(木)     5
8 9/14(金)     6
 
これと似た数列を作れるのがWEEKDAY(日付,3)です。
日付に対して0〜6の数字を繰り返してくれます。
(これは、ある数字を7で割った余りと同じですね。)
 
     A     B
1   日付 WEEKDAY(日付,3)
2 9/14(土)     5 ★おしい、これを0にしたい
3 9/14(日)     6
4 9/14(月)     0
5 9/14(火)     1
6 9/14(水)     2
7 9/14(木)     3
8 9/14(金)     4
 
2つの表の★を比べてください。下の表の5の場所を上の表のように0にしたい。
そこでWEEKDAYの中の日付を、日付-5 としてみましょう。
(それでも7で割った余りの0〜6が繰り返されます。)
 
     A     B        C
1   日付 WEEKDAY(日付,3) WEEKDAY(日付-5,3)
2 9/14(土)     5 ★      0 ★最初の表の戻るべき日数と一致した。
3 9/14(日)     6        1
4 9/14(月)     0        2
5 9/14(火)     1        3
6 9/14(水)     2        4
7 9/14(木)     3        5
8 9/14(金)     4        6
 
あとは9/14から0日前、1日前、2日前...と戻れば、すべて第2土曜日になる。解決。
 
     A     B        C          D
1   日付 WEEKDAY(日付,3) WEEKDAY(日付-5,3) 日付-WEEKDAY(日付-5,3)
2 9/14(土)     5 ★      0 ★      9/14(土) ★
3 9/14(日)     6        1        9/13(土)
4 9/14(月)     0        2        9/12(土)
5 9/14(火)     1        3        9/11(土)
6 9/14(水)     2        4        9/10(土)
7 9/14(木)     3        5         9/9(土)
8 9/14(金)     4        6         9/8(土)
 
 
 
【以下、質問の最初式がなぜ間違えているのかの説明】
 
WEEKDAY(日付,2)だと、0〜6ではなくて1〜7の数字を返してきます。
 
     A     B    
1   日付 WEEKDAY(日付,2) 
2 9/14(土)     6 ★0にしたい
3 9/14(日)     7       
4 9/14(月)     1       
5 9/14(火)     2       
6 9/14(水)     3       
7 9/14(木)     4       
8 9/14(金)     5       
 
6を0にしたいから上と同じようにWEEKDAY(日付-6,2)とすると....うまくいきません。
0〜6ではなく、1〜7が繰り返されてしまいます。
 
     A     B        C
1   日付 WEEKDAY(日付,2) WEEKDAY(日付-6,2)
2 9/14(土)     6 ★       7 ★0ではなくて7になってしまう
3 9/14(日)     7        1  ※MOD(WEEKDAY(日付-6,2),7)とすればOK
4 9/14(月)     1        2
5 9/14(火)     2        3
6 9/14(水)     3        4
7 9/14(木)     4        5
8 9/14(金)     5        6  
 
だから9/14が土曜日のとき、戻る必要がないのに7日前、つまり第1土曜日になってしまう。
 
     A     B        C            D
1   日付 WEEKDAY(日付,2) WEEKDAY(日付-6,2) 日付-WEEKDAY(日付-6,2)
2 9/14(土)     6 ★      7 ★       9/7(土) ★第1土曜日
3 9/14(日)     7        1        9/13(土)
4 9/14(月)     1        2        9/12(土)
5 9/14(火)     2        3        9/11(土)
6 9/14(水)     3        4        9/10(土)
7 9/14(木)     4        5         9/9(土)
8 9/14(金)     5        6         9/8(土)

回答
投稿日時: 23/12/07 20:06:17
投稿者: んなっと

それから「1週間前、1週間後、2週間後...の曜日は同じ」ですよね。
 
ですからWEEKDAY(日付, )のような形はWEEKDAY内部の日付のところを1週間後、
つまり +7日 しても変わりません。7だけではなく7の倍数を足したり引いたりしても同じです。
 
WEEKDAY(日付+7, )
WEEKDAY(日付+14, )
WEEKDAY(日付+21, )
WEEKDAY(日付-7, )
WEEKDAY(日付-14, )
→すべてWEEKDAY(日付, )と同じ。
 
質問の2番目の式が
WEEKDAY(DATE(年,月,7*2)-5, ) ではなく、7*2を消して
WEEKDAY(DATE(年,月,-5), )になっているのはこのためです。

回答
投稿日時: 23/12/07 22:05:34
投稿者: んなっと

それから3番目の式は日付の元となる「シリアル値」が「7の倍数の時に土曜日になる
ことを利用しています。
 
=FLOOR(DATE(月,日,7*2),7) でもいいようです。
 
シリアル値 年月日 7で割った余り    第2●曜日を求める式
  0 1900/1/0(土)   0    土 =FLOOR(DATE(月,日,7*2 ),7)  8〜14日の土曜日を求める
  1 1900/1/1(日)   1    日 =FLOOR(DATE(月,日,7*2-1),7)+1 7〜13日の土曜日を求めて+1
  2 1900/1/2(月)   2    月 =FLOOR(DATE(月,日,7*2-2),7)+2 6〜12日の土曜日を求めて+2
  3 1900/1/3(火)   3    火 =FLOOR(DATE(月,日,7*2-3),7)+3 5〜11日の土曜日を求めて+3
  4 1900/1/4(水)   4    水 =FLOOR(DATE(月,日,7*2-4),7)+4 4〜10日の土曜日を求めて+4
  5 1900/1/5(木)   5    木 =FLOOR(DATE(月,日,7*2-5),7)+5 3〜9日の土曜日を求めて+5
  6 1900/1/6(金)   6    金 =FLOOR(DATE(月,日,7*2-6),7)+6 2〜8日の土曜日を求めて+6
   ............................
45267 2023/12/7(木)   5    木

回答
投稿日時: 23/12/08 06:37:39
投稿者: んなっと

余談ですが、存在しない1900/2/29を存在するものと仮定してシリアル値と日付の変換をしているので、1900/1/1は実際は(日)ではありません。(月)です。
 
しかし1900/3/1からは正確な曜日を返します。実用上は問題なし。

回答
投稿日時: 23/12/08 08:02:07
投稿者: Mike

A1→“2024”年
A2→“9”月
A3→“2”第
A4→“土”曜日
 
=DATE(A1,A2,A3*7-WEEKDAY(DATE(A1,A2,1-(FIND(A4,"月火水木金土日"))),3))

投稿日時: 23/12/08 15:02:48
投稿者: shimoichimabu

 んなっとさん回答ありがとうございます。
詳細にわたって解説していただき、勉強になりました。
 
= DATE(年,月,2*7-WEEKDAY(DATE(年,月,-6),2)) は
やはり、間違いだったのですね。
 
 んなっとさんから提示された式を一般化し、
第N・W曜日の式
=FLOOR(DATE(年,月,7*N-W),7)+W
といたしました。
 
Mikeさんの下記式はわかり易いですね。
パラメーターではなく、「土」と直に指定できますので。
=DATE(2024,9,2*7-WEEKDAY(DATE(2024,9,1-(FIND(土,"月火水木金土日"))),3))