労基法の週４０時間を超える時間を計算するため、日曜日を起点とする１週間

質問

労基法の週４０時間を超える時間を計算するため、日曜日を起点とする１週間毎の労働時間を集計する暦月単位の勤務実績表（１２Ｓｈｅｅｔ）があります。
例えば本年４月分のＳｈｅｅｔであれば次表（簡略化）のとおりです。

　　　A列 　　 　　　B列 　　C列 　　　　　　　D列
2　　 　前月繰越 　　　　　 32.0
3 　　　4月1日 　　　木 　　12.5 　　=IF(B6<>"土","",SUM(C$2:C3))
4 　　　4月2日 　　　金 　 　6.5 　　=IF(B6<>"土","",SUM(C$2:C4))
5 　　　4月3日 　　　土 　　10.0 　　=IF(B6<>"土","",SUM(C$2:C5))
6 　　　4月4日 　　　日 　　 8.0 　　=IF(B6<>"土","",SUM(＃REF))
　　↓（中略）
26 　　4月24日 　　土 　 　7.5 　　=IF(B6<>"土","",SUM(C20:C26))
27 　　4月25日 　　日 　 　9.5 　　=IF(B6<>"土","",SUM(C21:C27))
28 　　4月26日 　　月 　　11.0 　　=IF(B6<>"土","",SUM(C22:C28))
29 　　4月27日 　　火 　　 8.0 　　=IF(B6<>"土","",SUM(C23:C29))
30 　　4月28日 　　水 　 　7.0 　　=IF(B6<>"土","",SUM(C24:C30))
31 　　4月29日 　　木 　 　9.5 　　=IF(B6<>"土","",SUM(C25:C31))
32 　　4月30日 　　金 　 　9.0 　　=IF(B6<>"土","",SUM(C26:C32))
33 　　（空行） 　　　 　　　 　　　　　=IF(B6<>"土","",SUM(C27:C33))
34 　　次月繰越 　　　　　　　　　　　　？

　 この表の行数はブックを１年単位で作っているため、作業の都合上、月の前後の繰越行を含め３３行に統一しています。

　さて、質問ですが、最終週の末日が土曜日でないときは翌月に繰り越す就労日数があるので、次月繰越欄（この場合D34）に最終週の日曜から月末までの就労日数を集計する数式を入力する必要があります。このセルに１２か月分すべてにあてはまる同じ関数式を入力したいのですが、月によって曜日が異なるため良い知恵が浮かびません。どなたか教えていただけないでしょうか。

　ついでに、前月繰越がある月の初週のD列数式もできれば１か月を通じ同じ数式に統一したいのですが、良い方法がありましたら併せてお願いします。

更に、１２か月分のSheetをアクティブにして、翌月繰越の数値を、次Sheetの前月繰越セル（この場合C7)に１２Sheet一括入力（又は最初の月分を残りの１１Sheetにコピー）できる関数式があれば、それについても教えていただければ助かります。

　以上の作業は、各月毎に曜日の配置を見て手作業で入力しても大して手数はかかりませんが、実際のブックはスタート月が違うものも含めて多数存在するため、一括した数式を入力できれば作業が容易なのでお尋ねする次第です。

よろしくお願いします。

回答No.5

KURUMITO · ベストアンサー

初めに4月から翌年の3月までのシートをシート1からシート12までに作成するとします。
そこでシート見出しでSheet1をクリックしてからShiftキーを押しながらSheet12をクリックします。これですべてのシートが同じ作業グループとなります。
そこで次の操作はシート1で行います。
Ａ1セルには2010/4/1と入力してからセルの表示形式の日付で2010年4月と表示させます。
Ａ2セルには前月繰越と入力します。
Ｃ2セルには次の式を入力します。

=INDIRECT("Sheet"&SUBSTITUTE(TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,10)),"Sheet","")-1&"!D34")

この式を入力した時点ではエラー表示になってしまうでしょう。しかしそのままにします。実はこの式は前月シートのＤ34セルのデータを表示させるための式でシート1ではエラーとなりますが後ほど、作業グループの解除を行った後に、シート1については実際のデータで数値を入力することにします。
Ａ3セルには次の式を入力してＡ33セルまでオートフィルドラッグします。

=IF(A$1="","",IF(MONTH(A$1+ROW(A1)-1)<>MONTH(A$1),"",A$1+ROW(A1)-1))

これでその月に応じた末尾までの日にちが表示されます。セルの表示形式は4月1日などとします。
次にＢ3セルには次の式を入力してＢ33セルまでオートフィルドラッグします。

=IF(A3="","",TEXT(A3,"aaa"))

Ｄ3セルには次の式を入力してＤ33セルまでオートフィルドラッグします。

=IF(OR(B3<>"土",B3=""),"",IF(ROW()=3,C2+C3,SUM(C$2:C3)-SUM(D$2:D2)))

Ａ34セルには次月繰越の文字を入力しＤ34セルには次の式を入力します。

=SUM(C$2:C33)-SUM(D$2:D33)

これでシート1からシート12には同じ式や文字が入力されました。この後はシート見出しを右クリックして「作業グループの解除」を選択します。この操作は必ず行ってください。
その後にシート1のＣ2セルには32.0などの数値を入力します。

なお、各シートには4月の表示がされていますが各シートでＡ1セルには5月のシートすなわちシート2のＡ1セルには2010/5/1と入力してください。2010年5月と表示されその月の日付が自動的に表示されるでしょう。

ここで示した方法を新しいブックなどで試験してください。ご参考になりましたら幸いです。

お礼

不在のためレスポンスが遅れ、申し訳ありません。
丁寧かつ懇ろなご回答、心からお礼申し上げます。

最初のお答えのうち、作業グループを作っての一括作業や、曜日を表す関数、あるいはシートネームを現す関数などはずっと以前から使っておりますが、問題はお示しの次の式です。
=INDIRECT("Sheet"&SUBSTITUTE(TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,10)),"Sheet","")-1&"!D34")

この数式は、シート名が、昇順で「Ｓｈｅｅｔ1」から「Ｓｈｅｅｔ12」までとなっていることを前提にしたご回答だったみたいですね。

実は最初質問の際にシート名のことを記述していればよかったのですが、それを省いたのが私の手落ちでした。
実際のシート名は「1004」「1005」～「1302」「1303」と年月を簡略化した３年分の名称にしておりまして、しかも作業の都合でシート順を随時入れ替えたり、中途で半年計や年度計のシートが入ったり、シートを非表示にしたりすることなどもありますので、ご教示の式では答えがエラー（#REF!）になってしまいます。

そこで私は別シートに下表を作って挿入→名前で「前月シート」と命名し、Ｃ2に次の式を入力することにして答えを出すことにしました。

Ａ列　 　　Ｂ列
1105 　　'1004'!D34
1106 　　'1005'!D34
　　　　　中略
1302 　　'1301'!D34
1303 　　'1302'!D34

（Ｃ2の式）
=INDIRECT(VLOOKUP(VALUE(MONTH(A1)),前月シート,2))

尤も、この方法を考える過程で、ご教示の数式が間接的なヒントになり、何かと参考になりました。

Ｄ３セルへの数式（No6回答）は、mt2008さんのNo3及びNo4ご回答と同様なので、補足を割愛させていただきます。

次に参考までに申し上げますと、まず３年分の36シートですが、最初に左端「Ｓｈｅｅｔ1」のＡ1～Ａ36までに、年月を数値化した1004から1303までの数値を入力し、フリーソフトの「シート名一覧でシート作成」を使って一度に全シートを作成しました。

次に各シート「Ａ1」の月初日の表示ですが、まずＳｈｅｅｔ1のＢ1からＢ36に、2010/4/1から2013/3/1までの各月初日の日付を入力し、「Ａ1：Ｂ36」の範囲を「月初日」と命名し、36シートをアクティブ（作業グループ）にして、Ａ1に次の式を入力し、各月初日を表示することにしました。

=VLOOKUP(VALUE(RIGHT(CELL("filename"),4)),月初日,2)
なお、Vlookup関数を使わず、Ａ1に直接次の式を入力しても答えは出ましたが、あまり式が長くなるので、やめました。

=DATE(INT(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))/100)+100,MOD(RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))),1000),1)

ところで、理論的には「LEN(CELL(""filename""))-FIND(""]"",CELL(""filename""))」の代わりに、数字の「４」（シート名の文字数）を置き換えてもよいはずなのに、そうすると、作業グループでコピーしたとき何故か各シートとも同じ日付になってしまうことがあります。

この理由がいくら首をひねっても分からないので、もしお分かりでしたらご教示ください。
以上が、ご教示の内容を参考に検討の上、出した結論ですが、検討過程でいろいろと参考になったことを報告してお礼に代えさせていただきます。
ありがとうございました。

補足

先にお礼の中で

「ところで、理論的には「LEN(CELL("filename"))-FIND(""]"",CELL("filename"))」の代わりに、数字の「４」（シート名の文字数）を置き換えてもよいはずなのに、そうすると、作業グループでコピーしたとき何故か各シートとも同じ日付になってしまうことがあります。
この理由がいくら首をひねっても分からないので、もしお分かりでしたらご教示ください。」

と記述しておりましたが、その後再度数式を見直したら、
("filename")の中の右カッコ「）」の前に「,$A$1」を入れたら正しい答えが出ることが分りましたので、あらためて補足させていただきます。

回答No.6

KURUMITO

Ｄ3セルへは次の式でよいですね。

=IF(B3<>"土","",SUM(C$2:C3)-SUM(D$2:D2))

回答No.4

mt2008

Ano.3です。
訂正です
誤：私なら、D1に↓をいれ、D33までオートフィルします。
正：私なら、D3に↓をいれ、D33までオートフィルします。

回答No.3

mt2008

D34には↓でいいと思います。
=SUM(C2:C33)-SUM(D3:D33)

しかしながら、提示されているD3:D33までの式、おかしくないですか？
私なら、D1に↓をいれ、D33までオートフィルします。
=IF(B3<>"土","",SUM(C$2:C3)-SUM(D$2:D2))

お礼

不在のためレスポンスが遅れ、すみません。
ご回答ありがとうございました。

Ｄ３４及びＤ列（Ｄ３～Ｄ３３）の式は両方ともお示しのとおりでした。
ちょっと考えれば簡単な足し算引き算の問題なのに、頭が老化していたのでしょう。何か難しく考えすぎていました。

負け惜しみと思われるかも知れませんが、コロンブスの卵みたいに、なーんだそうか、というのが偽らざる心境でお恥ずかしい限りです。

ともあれ、ご教示心から感謝いたします。

回答No.2

Sinogi

ちょっとだけ変更
次月以降の表示が無くてもＯＫにしてみました

=IF(MONTH(A3+30)=MONTH(A3),WEEKDAY(A3+30),WEEKDAY(OFFSET(A33,DAY(A3+30)*-1,0)))

お礼

不在のためレスポンスが遅れ、すみません。
ご回答ありがとうございました。
ところが、残念ながらお示しの数式では正答が得られませんでした。
結論だけ申し上げますと、４月分の次月繰越時間数は「54」ですが、お示しの計算式では答えが「6」になってしまいます。

回答No.1

Sinogi

Ｄ34の式だけですが

=IF(MONTH(A33)=MONTH(A3),WEEKDAY(A33),WEEKDAY(OFFSET(A33,DAY(A33)*-1,0)))

但し、A列は３３行目まで次月以降の日付を入れておいてください