エクセル日報 休日出勤 定時内も残業扱いにしたい

度々申し訳ありません。
完璧に出来たと思っていましたらまた問題が出てしまいました。
昨日http://oshiete.goo.ne.jp/qa/8167846.htmlで質問した者です。

早出　5：00～8：50
定時　8：50～17：20
普通残業　17：20～22：00
深夜　22：00～5：00

　　C　　　D　　　E　　　　　F　　　　　　G　　　　　　H　　　　　　I　　　　　　　　　　　
９　出社　退社　昼休憩　夕方休憩　早出残業　普通残業　普通残業内休憩

　　J　　　　　　　K　　　　　　　　　　　L
９　深夜残業　深夜残業内休憩　　実労時間
　

G９セル　=IF(ISBLANK(C9),"",IF(COUNT(C9:D9)<>2,"",MIN(D9,"8：50")-MIN(C9,"8：50"))*24)
Ｈ９セル　=IF(ISBLANK(C9),"",IF(O9="休日出勤",MIN(D9,"21:59")-C9,MAX("17:20",MIN(D9,"21:59"))-"17:20")*24)
Ｊ９セル　=IF(D9<=TIME(21,59,0),0,(D9-"21:59")*24)
Ｌ９セル　=(TIMEVALUE(TEXT((1+D9)-C9,"h:mm"))-TIME(0,E9,0)-TIME(0,F9,0)-TIME(0,I9,0)-TIME(0,K9,0))*24

という数式が入ってます。
Ｏ９セルが休日出勤入力欄です。
休憩時間は分単位(例えば45／分)で入力してもらうようにしました。


上記で作成したところ、通常出勤に於いては問題ないのですが、休日出勤として8：50より前に早出出勤した場合、早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。

例えば8：20出社　17：05退社　昼休憩60分　と入力すると、早出残0.50　普通残8.75　実労7.75　と出てしまいました。
Ｏ９セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にすることは出来るでしょうか？
夕方休憩、普通残業内休憩は表の下部で残業合計から差し引くようにしてますので考慮しなくて大丈夫です。
ただ、休日出勤でない通常勤務の際は普通残業から昼休憩を引かれてはおかしくなってしまいますので、その辺りも検討頂けると有り難いです。

色々とややこしくて申し訳ありませんが、お力を貸して頂けると有り難いです。
説明不足ありましたらご指摘ください。

No.2 ベストアンサー 回答者： don9don9 回答日時： 2013/07/10 11:18

「退社」の次に「通常勤務」の列を追加し、以降1列ずつずれて

C列…出社（時刻を入力）
D列…退社（時刻を入力）
E列…通常勤務
F列…昼休憩（分を入力）
G列…夕方休憩（分を入力）
H列…早出残業
I列…普通残業
J列…普通残業内休憩（分を入力）
K列…深夜残業
L列…深夜残業内休憩（分を入力）
M列…実働時間
P列…休日出勤の場合に"休日出勤"と入力
とします。

Q～T列を作業列とします。
Q9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"8:50"*1)-MAX(C9,"5:00"*1),0),0)
R9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"17:20"*1)-MAX(C9,"8:50"*1),0),0)
S9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"22:00"*1)-MAX(C9,"17:20"*1),0),0)
T9には
=IF(COUNT(C9:D9)=2,MAX(MIN(IF(C9>D9,D9+1,D9),"5:00"*1+1)-MAX(C9,"22:00"*1),0)+MAX((MIN(D9,"5:00"*1)-MIN(C9,"5:00"*1)),0),0)
と入力します。

これで休憩時間や休日出勤等関係なく、純粋に出社時刻と退社時刻から計算した早出・定時・残業・深夜の時間数がQ～T列に入ると思います。
このQ～T列の時間数から、P列の値（"休日出勤"）に基づいて条件分岐しつつ、休憩時間を差し引く計算をします。

E9には
=FLOOR(IF(P9="休日出勤",0,R9-F9/1440),"0:15")*24
H9には
=FLOOR(IF(P9="休日出勤",0,Q9),"0:15")*24
I9には
=FLOOR(IF(P9="休日出勤",Q9+R9+S9-(F9+G9+J9)/1440,S9-(G9+J9)/1440),"0:15")*24
K9には
=FLOOR(T9-L9/1440,"0:15")*24
M9には
=E9+H9+I9+K9
と入力します。

夕方休憩と普通残業内休憩は考慮しなくてよいと書かれていますが、一応普通残業から差し引くようにしています。
それと勝手ながら15分単位で切り捨てる処理も含めています。

この回答へのお礼

ありがとうございます。
初めからこのして聞いた方が早かったですね。
極力自分で何とかしようとネットで調べて色々やりましたが、計算式が長くなるにつれて訳が分からなくなり、混乱してしまいました。
教えて頂いたものを元に作りつつ、曖昧な関数の知識もちゃんとした知識になるよう勉強しようと思います。

ありがとうございました。

お礼日時：2013/07/10 15:53

No.1 回答者： keithin 回答日時： 2013/07/09 18:51

＞早出残業欄にも早出分が表示されてしまい、普通残業分にも含まれる為重複してしまいます。

はい，早出残業を別枠で計算しているというご説明はありませんでしたが，先の回答ではその旨指摘をしておきました。


休日の８：５０以前を「早出枠」で計算するのか「残業枠」で計算するのかが不明でしたが，
＞Ｏ９セルに休日出勤と入力した場合、早出残0.50を出ないようにし、普通残は昼休憩を差し引いた7.75にする

ということで「休日出勤では全部残業枠にしたい（早出には数えない）」という事で良いのでしたら，普通残の計算は元の式マイナスIF(休日は,昼休み時間数,そうでないならゼロ)とでもしておいて，

Ｇ９：
=IF(O9="休日出勤",0,今の早出の式)
としておきます。



＃補足
今の表組みに囚われているため，段々とこのように継ぎ接ぎ継ぎ足しになっていきます。
たとえば
　平日早出
　平日遅刻
　平日通常勤務時間
　平日早退
　平日普通残
　平日深夜残
　平日休憩
　休日普通残
　休日深夜残
　休日休憩
などのように漏れなく列を設けてそれぞれしっかり計算し，合計するような作り方に勤務表全体を見直す事をお薦めします。

この回答へのお礼

再度の回答ありがとうございました。
ご指摘は分かっていたつもりでしたが実際は理解出来ておりませんでした。
実際早出で入力したらおかしいことに気付き、締めてしまった後でしたので再度質問した次第です。

ネットで調べた情報を元に付け足したりしていた為、ごちゃごちゃになり更に分からなくなっている現状でもありますので、これを機に関数をちゃんと勉強しようと思います。

ありがとうございました。

お礼日時：2013/07/10 15:50