仕事内容ごとの常勤・残業・深夜残業時間の抽出方法

Question

こんにちは

以前いろいろとご教授いただいて作成した勤怠管理表が、お蔭様で軌道に乗りまして、業務改善にかなり貢献しており、大好評を得ております。

その節は、ご教授いただいた方々に大変お世話になりました。

有難うございました。

今回は、その勤怠管理表より工事ごとの勤務状況を分析したく、その方法でつまづいております。

今、この勤怠管理表では、1日単位で工事番号（Ｇ113）ごとに、作業開始時間（Ｄ113）～作業終了時間（Ｆ113)を入力し、遅早外～深夜（Ｕ113～Ｕ116）に振り分けています。

　Ｕ113　→　=IF(C113="有休半日","",IF(D113="","","7:30"-TEXT(U114,"h:mm")))

　Ｕ114　→　=IF(COUNT(D113:F113),MAX(MIN(F113,"17:30"*1)-MAX(D113,"8:30"*1),0))+IF(COUNT(D114:F114),MAX(MIN　　　　　　　　　　　　(F114,"17:30"*1)-MAX(D114,"8:30"*1),0))+IF(COUNT(D115:F115),MAX(MIN(F115,"17:30"*1)-MAX(D115,"8:30"*1),0))+IF(COUNT(D116:F116),MAX(MIN(F116,"17:30"*1)-MAX(D116,"8:30"*1),0))-((((COUNTIF(D113:D116,"<12:00")>0)+(COUNTIF(F113:F116,">13:00")>0))*"0:15"+(COUNTIFS(D113:D116,"<13:00",F113:F116,">12:00"))*"1:00"))

　Ｕ115　→　=MIN(MAX(F113,"18:00"),"22:00")-MIN(MAX(D113,"18:00"),"22:00")+MIN(MAX(F114,"18:00"),"22:00")-MIN(MAX　　　　　　　　　　　　　(D114,"18:00"),"22:00")+MIN(MAX(F115,"18:00"),"22:00")-MIN(MAX(D115,"18:00"),"22:00")+MIN(MAX(F116,"18:00"),"22:00")-MIN(MAX(D116,"18:00"),"22:00")

　Ｕ116　→　=MIN(MAX(F113,"22:00"),"29:00")-MIN(MAX(D113,"22:00"),"29:00")+MIN(MAX(F114,"22:00"),"29:00")-MIN(MAX(D114,"22:00"),"29:00")+MIN(MAX(F115,"22:00"),"29:00")-MIN(MAX(D115,"22:00"),"29:00")+MIN(MAX(F116,"22:00"),"29:00")-MIN(MAX(D116,"22:00"),"29:00")

Ｗ132～Ｗ151には、1ヶ月分の工事番号ごとのトータル時間を出しています。

　Ｗ132　→　{=SUMPRODUCT(($G$5:$G$128=L132)*(IF($D$5:$D$128="","0",($F$5:$F$128-IF($D$5:$D$128>="8:30"*1,$D$5:

$D$128,"8:30"*1)))))}

今回出したいのは、月単位で工事ごとに、どの時間帯に作業しているかの分析になりますので、工事番号でヒットしたタイミングで常勤・残業・深夜に振り分けていこうと思い、Ｕ114～Ｕ116とＷ132～の関数を上手く組み合わせればよいのかなと思ったのですが、Ｕ114～Ｕ116は日ごとなので、それだとかなり重たく長く無理でした。

月単位だと工事番号も幾つかしかないので、工事番号ごとにＤ列・Ｆ列を抜き出してから振り分けた方がよいのかなと思ったり…

どちらにしろ、かなり複雑で大変な作業になりそうで…

もっと柔軟に考えたほうがよいのかと思うのですが、煮詰まってしまいました。

何かよい方法があればアドバイスをいただきたいと思います。

どうぞ宜しくお願い致します。

Answer 1

こんにちは

先日は漠然とした質問で大変失礼しました。

この勤怠管理表は、工事番号ごとに横1行で入力するようになっている為、行単位で「遅早外」～深夜までの時間を割り振り、横に欄を設けて集計を出すようにしました。

X45　→　=IF(C45="有休半日","",IF(D45="","","7:30"-TEXT(U46,"h:mm")))                                          

Y45　→　=IF(D45>"****">12:00"))*"1:00"))) 

Z45　→  =MIN(MAX(F45,"18:00"),"22:00")-MIN(MAX(D45,"18:00"),"22:00")                                    

AA45→　=MIN(MAX(F45,"22:00"),"29:00")-MIN(MAX(D45,"22:00"),"29:00")　　　　　　　　　　　　　　　　

休憩時間は、  8:30～12：00　　15分

　　　　　　　　12：00～13：00　　60分

                    13:00～17：30　　15分　　となっています。

とりあえず割り振りは出来たと思ったのですが、これだとY行について、1行ごとに15分ずつ引かれろよう

になっています。

これを1日単位（4行）の午前と午後に、それぞれ15分ずつだけひかれるようにするにはどのような方法があるのか、ご教授いただけると幸いです。

ちなみにまだ関数は組んでいませんが、例えば前の行で午前15分引かれたら2～4行目の午前は引かないといった方法か、午前なら10時で区切って、それ以前と以降で分ける方法か…

どうぞ宜しくお願い致します。