エクセルで時間計算を教えてください！！

質問

作業の効率化と計算ミスを防ぐため、エクセルで勤務表を作成したいと思っております。
イメージとして

　　　　A　　　　B　　　　　C　　　　　D　　　　　E　　　　　F　　　　　G　　　　　H
1列　日 　 出勤　　　退勤　　　残業　　　深夜　　総労働　　遅・早　 　休息
2列 1　　4:20　　　17:42 　　　4:22　　　0：40 　　12:22
3列 2　　7:51 　　 24:00 　　　　　　　　　2:00 　　 7:57 　　　0:03 　　　7:12
4列 3　　0:00 　 　10:50 　　　1:00 　　　5:00 　　 9:00 　　 0:50
5列 4　 16:30 　　　6:30 　　　5:00 　　　7:00 　 13:00
6列　5 　8：30　　　17：30　　　　　　　　　　　　　 　8：00

A(日)・B(出勤）・C（退勤）・H列（休息）は、手入力しますが、
D（残業）・E（深夜）・F（総労働）・G（遅刻・早退）は、自動計算させたいと考えております。

F（総労働）は 退勤-出勤-休憩＝X (Xが8時間以上なら-1時間　8時間に満たない時はそのまま表示）
D（残業）は、X-8時間 （Xが8時間に満たない時は、空白のまま）
E（深夜）は、22：00から5：00とする。
G（遅刻・早退）は、Xが8時間に満たない時のみ　（8時間-X＝Y）を表示させる。

★出勤時間・退勤時間は、人や仕事の内容によりバラバラです。　
　 H（休息）は、2日運行等になった時のみです。
　 休日など空白になっている時は、空白のまま。

説明が下手で申し訳ありませんが、お知恵を貸してください！！
宜しくお願いします。

回答No.4

KURUMITO · ベストアンサー

Ｄ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B2:C2)<>2,"",IF(F2<="8:00"*1,"",F2-"8:00"))

Ｅ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B2:C2)<>2,"",IF(C2>B2,IF(AND(B2>="5:00"*1,C2<="22:00"*1),"",IF(B2<"5:00"*1,"5:00"-B2+IF(C2>"22:00"*1,C2-"22:00",0),IF(C2>"22:00"*1,C2-"22:00",0))),IF(C2<=B2,"24:00"-MAX(B2,"22:00")+MIN(C2,"5:00"),"")))

Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B2:C2)<>2,"",IF(C2>B2,IF(C2-B2>="8:00"*1,C2-B2-"1:00"-H2,C2-B2-H2),IF(C2+"24:00"-B2>="8:00"*1,C2+"24:00"-B2-"1:00"-H2,C2+"24:00"-B2-H2)))

Ｇ2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B2:C2)<>2,"",IF(F2>="8:00"*1,"","8:00"-F2))

お礼

回答ありがとうございました。
とても勉強になりました。
作業が、はかどりそうです。
またわからないことがありましたら
宜しくお願いします。

回答No.5

MarcoRossiItaly

No.2・3です。No.2の「G2」で、どういうわけか「=-min(…」などと書いてしまいましたが、その式はもちろん、次のより短い式でも同じ結果になります。すみません。

=max(0,8/24-f2)

お礼

親切に教えていただき ありがとうございました。

回答No.3

MarcoRossiItaly

No.2です、何度もすみません。言い忘れました。

時間として「0」が算出されているセルでも、「ユーザー定義書式」を使えば、見かけ上は空白であるように見せることは可能です。

設定したいセルを選択した状態で「セルの書式設定＞表示形式＞ユーザー定義」と進み、「種類」ボックスに「h:mm;;」と入力してOKボタンを押すだけです。「0」だけど、見かけは空白同様になります。

お礼

回答ありがとうございます。
『空白』の問題が解決いたしました。
またわからないことがありましたら宜しくお願いします。

回答No.2

MarcoRossiItaly

質問文には「空白」と何回か書かれていますが、「0」か「空文字列」のいずれかを入力することとなります。完全なる空白にすることは、一発ではできません。なお、「空文字列」の入力されているセルは、見かけ上は空白セルと同様になります。

時間の計算は、単に引き算をするだけです。条件により式が変わる場合は、IFなどを組み合わせます。質問文に書いてある条件を忠実に式に直すと次のとおりです。とりあえず「空白」とお書きになっている箇所には「0」が表示されるようにしてあります。「空文字列」にするには、もう少し式が長くなります。

F2　=if(c2+if(b2>c2,1)-b2-h2>=8,c2+if(b2>c2,1)-b2-h2-1,c2+if(b2>c2,1)-b2-h2)
D2　=f2-8/24
E2　=if(b2>c2,min(c2,5/24)+1-max(b2,22/24),)
G2　=-min(0,f2-8/24)

？？？
気になるのは、質問文に書いてあるとおりだと、労働時間が8時間だとX=7となり、7.5時間だとX=7.5となって、逆転しますが、この理解で合っているのでしょうか？

それから、日付が変わるケースがあるようですので、以前私が回答した類似の質問（参考URL）を参照し、全部読んでみてください。マイナスの時間は「####」と表示されますが、Excelのエラーではなく、マイナスの時間を表示する際のルールです。日付を表中に導入すればマイナス回避の必要がなくなるので、式がもっと簡単になるし、入力ミスなどもしにくい分かりやすいものとなりそうですが。

あと、日勤／夜勤の別を表示する列もあったほうが、紛れがなくて好ましいと思いました。上の計算にも利用できます。

参考URL：

http://okwave.jp/qa/q7354714.html

お礼

回答ありがとうございました。
とても勉強になりました。
またわからないことがありましたら 宜しくお願いします。

回答No.1

googoo900

いろいろなケースを入力してみて、正しいことを確認してから使用下さい。
質問者さんが書かれた例の3日目、Xは8時間以上ですが『遅・早』が0:50になっているのが理解できませんでした。作成したものでは0:00（空白）になります。

イ、　最上部に3行挿入して、下記のように計算のための値を入力します。（A1～F2セル）
ロ、　I,J列に計算のため22:00を0:00に換算した値を表示するセルを作ります。

　　A　　　　B　　　　　C　　　　D　　　　　E　　　　F　　　G　　　　H　　　I　　　　J
1　休憩時間　所定時間　深夜開始　深夜終了　深夜時間　1日の時間
2　1:00　　　8:00　　　2:00　　　5:00　　7:00　　24:00:00
3　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　22:00⇒0:00
4　日　　　　出勤　　　退勤　　　残業　　　深夜　　総労働　遅・早　　休息　　(出勤)　(退勤)
5　　　　　　　　　　　　　　　　D5　　　　E5　　　F5　　　G5　　　　　　　　I5　　　J5
6
7

ハ、　自動計算させたいD（残業）・E（深夜）・F（総労働）・G（遅刻・早退）と追加したI,J列に以下のように入力します。この例では5行目(D5,E5,・・・)とします。
D5：=IF(F5<=$B$2,"",F5-$B$2)
E5：=IF(AND($E$2<J5,J5<K5),"",IF(J5>$E$2,MIN(K5,$E$2),MIN(K5,$E$2)-J5))
F5：=IF(C5>B5,IF(C5-B5-H5>=$B$2,C5-B5-H5-$A$2,C5-B5-H5),IF(C5-B5-H5>=$B$2,C5-B5-H5-$A$2,C5-B5-H5)+$F$2)
G5：=IF(F5<$B$2,$B$2-F5,"")
I5：=IF(B5>=$C$2,B5-$C$2,B5+$F$2-$C$2)
J5：=IF(C5>=$C$2,C5-$C$2,C5+$F$2-$C$2)

ニ、　5行目を必要な日数分だけコピーすれば完成です。

お礼

回答ありがとうございました。
とても勉強になりました。
またわかならいことがありましたら 宜しくお願いします。