[[20210314130719]]　『第5〇曜日の設定について』（クローバー）　ページの最後に飛ぶ

『第5〇曜日の設定について』（クローバー）

仕出し弁当の2021年4月の注文書を作成しています。
　
シート1　休日表,シート2　雛形,シート3　月曜日,……シート8　土曜日とシート名を設定しています。
月曜日から土曜日のシートには、第1週から第5週まで5ページあります。

雛形シートのセルD5に西暦の2021、セルG5に月の4が入力してあります。
第1週～第4週までは、問題ないのですが、第5週になると合わなくなってしまいます。

月曜日　第4週ページには、DB5セルに＝雛形!$G$5の計算式が入っていて、値は、4になっています。（表示形式　0）
DE5セルには、=CEILING（DATE(雛形!$D$5,雛形!$G$5),6,7)+16の計算式が入っていて、値は26になっています。（表示形式　d)
ところが、第5週ページは、EL5セルに＝CEILING（DATE(雛形!$D$5,雛形!$G$5,6
)7)+23が入っていて、値は3になってしまいます。
火曜シートは第5週は、値が4で水曜シートは、5、木曜シートは、29(問題なし）
金曜シートは、30（問題なし）土曜シートは、1となっています。

IF関数で5週目がなかったら、空白にするという式をいくつか考えましたが、いずれもうまくいきません。
又、条件式書式で背景を白にするやり方も試しましたが、別セルに休日表でVlookup関数で休日を設定してあるので、このやり方は、ダメでした。

どなたかお解りになる方がいらっしゃいましたら、教えていただけないでしょうか?
宜しくお願いいたします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

値が29未満なら空白にする、というのはどうですか。
（半可通） 2021/03/14(日) 14:42

すみません、day関数の戻り値の話です。
（半可通） 2021/03/14(日) 14:45

month関数の戻り値と雛形シートG5の値が等しくないとき、というのも。
（半可通） 2021/03/14(日) 14:48

半可通さん、お早い対応ありがとうございます。

値が29未満なら、ということで、計算式設定してみました。
木曜日シートIF（DAY29)＞EL5,"",CEILING（DATE(雛形!$D$5,雛形!$G$5),3,7)+26
上記、木、金、土で空白になってしまいます。（第5週あり）

month関数の戻り値と雛形シートG5の値が等しくないとき
木曜日シート
IF(MONTH(雛形!$G$5)<>MONTH($E＄5),"",CEILING（DATE(雛形!$D$5,雛形!$G$5),3,7)+26)
木、金は結果が反映されましたが、土曜日でまた、1になってしまいました。

上記式で間違っていたら、すみません。

（クローバー） 2021/03/14(日) 15:42

雛形シートのG5セルの値は4なので（日付シリアル値ではないので）month関数はつけない
dayやmonth関数の引数はEL5またはCEILING(※省略)+26
※省略部分の式は明らかに間違えているので確認してください
（半可通） 2021/03/14(日) 17:02

少し詳しく解説することにします。

エクセルの日付データというのは、1900年1月1日を1、1900年1月2日を2、…とする数字です。
2021年3月14日は44269です。セルに「=TODAY()」と入力してセルの表示形式を日付から標準に変えるとこの数字が表れます。
この数字のことを「シリアル値」といいます。

DATE関数は、年、月、日の情報をもとにシリアル値を求める関数です。
「=DATE(2021,3,14)」でその年月日のシリアル値44269が求められます。

CEILING関数は、ある値Aをある値Bの倍数に切り上げる関数です。
「=CEILING(44269,7)」で、直上の7の倍数である44275の値が求められます。
なお、44275のシリアル値は2021年3月20日を意味します。
この日は土曜日です。
「シリアル値を7の倍数にすると土曜日」になります。
その日に1～6の値を足す（引く）ことで、日～金曜日の日付も求められます。

DAY関数は、日付シリアル値をもとにそのシリアル値の「日」を求める関数です。
「=DAY(44269)」で、14の値が求められます。これは2021年3月14日の14です。

MONTH関数は、日付シリアル値をもとにそのシリアル値の「月」を求める関数です。
「=MONTH(44269)」で、3の値が求められます。これは2021年3月14日の3です。

これをふまえて、「=CEILING(DATE(年,月,3),7)+26」が何なのかを考えてみます。
なぜ「3」「26」なのでしょうか。

「26」は、「21+5」です。
「21」は3週間、「5」は上述したCEILING関数の補足の「土曜日の日付から次の木曜日を求めるための値」です。
つまり、「26」は「ある土曜日から4週間目の木曜日」を求めるための値です。

しかし、2以下の数字に26を足すと28以下になり第4週の日の値を返してしまいます。
そのために「3」があります。
3日以降の最初の土曜日のシリアル値を求めるのが「CEILING(DATE(年,月,3),7)」です。

同様に29日以上の戻り値（第5週の特定曜日のシリアル値）を得るための式は、他の曜日はこのようになります。

 6日が土曜日のとき、第5月曜日が29日→CEILING(DATE(年,月,6),7)+23　6日以降の最初の土曜日の値に23(21+2)を足す
 5日が土曜日のとき、第5火曜日が29日→CEILING(DATE(年,月,5),7)+24　5日以降の最初の土曜日の値に24(21+3)を足す
 4日が土曜日のとき、第5水曜日が29日→CEILING(DATE(年,月,4),7)+25　4日以降の最初の土曜日の値に25(21+4)を足す
 2日が土曜日のとき、第5金曜日が29日→CEILING(DATE(年,月,2),7)+27　2日以降の最初の土曜日の値に27(21+6)を足す
 1日が土曜日のとき、第5土曜日が29日→CEILING(DATE(年,月,1),7)+28　1日以降の最初の土曜日の値に28(21+7)を足す
 7日が土曜日のとき、第5日曜日が29日→CEILING(DATE(年,月,7),7)+22　7日以降の最初の土曜日の値に22(21+1)を足す

こうしてシリアル値を求めると、たとえば2021年4月の場合

 44315,44316,44317,44318,44319,44320,44321

になります。これを日付に直すと

 2021/4/29、2021/4/30、2021/5/1、2021/5/2、2021/5/3、2021/5/4、2021/5/5

です。セルの表示形式「d」なら「日」の値だけを表示するので

 29,30,1,2,3,4,5

になります。

表示されているのは「日」でも、セルの実際の値はシリアル値です。
DAY関数やMONTH関数でシリアル値から日や月の値を得られることはすでに書いたとおりです。

（半可通） 2021/03/14(日) 22:05

半可通様

お忙しいところ、とても詳しい説明ありがとうございます。
基本的な事がまだまだ分かってなかったので、とても参考になります。
上記、少し難しいので、もう一度よく読んで、検証してみたいと思います。
又、ご報告いたします。
ありがとうございました。

クローバー
（クローバー） 2021/03/14(日) 22:19

3/14 22:05の解説で非常にわかりづらい点があったので修正しました。
（半可通） 2021/03/15(月) 01:23

半可通　様

何度もありがとうございます。
遅くまで、考えて頂いたようですみません。
上記計算式、他サイトからパクってきたもので、詳しい意味は理解してませんでした。
上記説明少しづつですが、理解できました。
今、仕事先で詳しく検証しています。

取り急ぎ、ご報告まで。
（クローバー） 2021/03/15(月) 14:09

半可通　様

上記の件、解決できました。
なぜ、"日"の部分が合わないのかも、理解できました。
本当にお手数をお掛けしました。

又、何かありましたら、相談させて下さいね。
ありがとうございました。

（クローバー） 2021/03/16(火) 15:53

コメント返信：

[ 一覧(最新更新順) ]

[[20210314130719]] 『第5〇曜日の設定について』（クローバー） ページの最後に飛ぶ

[[20210314130719]]　『第5〇曜日の設定について』（クローバー）　ページの最後に飛ぶ