Excelで作る出勤簿の数式について

こんにちわ。

がんばって数式と格闘しましたが、どうしてもできないので教えて下さい。

「出社時間」と「退社時間」を入力すれば、
自動的に「勤務時間」と「残業時間」と「深夜残業時間」が出るような出勤簿を作ろうとしています。

各項目の定義は以下のとおりです。なお、時間単位は30分です。

・出社時間　・・・　定時は9：00。ただし、深夜作業などもあり、24時間のパターンが考えられます。
・退社時間　・・・　定時は18：00。こちらも、24時間のパターンが考えられます。
・勤務時間　・・・　休憩時間を除いた、残業・深夜残業を含んだトータルの時間
・残業時間　・・・　18：00～22：00
・深夜残業時間　・・・　22：00～5：00

休憩時間が、一般的な12：00～13：00だけであれば問題ないのですが、
実は他にも、以下の時間を休憩時間として考える必要があり、ここがネックになっています。

★休憩時間
12：00～13：00
19：30～20：00
23：30～24：00
3：30～4：00
7：30～8：00

例）出勤時間が9：00、退社時間が21：00の場合
・勤務時間
21-9＝12　12-1.5（休憩時間）＝10.5　※12：00～13：00、19：30～20：00が休憩時間となるため
・残業時間
21-18＝3　3-0.5＝2.5　※19：30～20：00が休憩時間のため、残業とはならない

このような条件で、良い方法はないでしょうか。
複雑な案件で申し訳ありませんが、ヒントでも結構です。教えて下さい。

No.1 回答者： mu2011 回答日時： 2012/02/10 15:48

一例です。

「勤務時間」と「残業時間」と「深夜残業時間」別に出社時刻・退社時刻で２次元表を作成して
抽出するようにしたら如何でしょうか。
参考例をＵＲＬに貼り付けしていますのでご確認下さい。

参考URL：http://homepage1.nifty.com/gak/MSTips/lookup.html

この回答へのお礼

なるほどです。ちょっと分かってきました。参考にさせていただきます。

お礼日時：2012/02/12 16:57

No.2 ベストアンサー 回答者： 135ok 回答日時： 2012/02/11 12:38

　興味があり作ってみました。

　完璧ではないので、ご参考程度で。

　以前、同様なものを作った事があります。その際は、No.１さんが提案された表引きで作成しました。
　今回は別の方法で作成しましたが、表を作って作成した方が楽なような気がします。

　さて、画像が２つ必要なので、２回に分けて記入します。
　まず始めに時間を計算するために、別添の表を作成します。
　出社用と退社用の二つを表を作成し、これを元に時間の計算をします。

　

この回答へのお礼

ご丁寧な回答、ありがとうございます。ならって作り始めたら、いろいろと発見があり、できそうな気がしてきました。カスタマイズして使いたいと思います！

お礼日時：2012/02/12 16:58

No.3 回答者： 135ok 回答日時： 2012/02/11 13:05

　画像を添付しますので、そちらを参照ください。

　B3　出社時間を入力

　C3　退社時間を入力

　D３　=VLOOKUP(B3,出社,2,1)
　　　＊出社用の表から時間を表示

　E３　=VLOOKUP(C3,退社,2,1)
　　　＊退社用の表から時間を表示

　F３　=E3-D3
　　　＊退社時間-出社時間

　G３　=IF(AND(B3<$G$1,F3>TIME(3,59,0)),TIME(1,0,0),"")
　H３　=IF(AND(D3<$H$1,E3>$H$2),TIME(0,30,0),"")
　Ｉ３　=IF(AND(D3<$I$1,C3<TIME(9,0,1)),TIME(0,30,0),"")
　Ｊ３　=IF(AND($C3>TIME(4,0,1),$C3<TIME(9,0,1)),TIME(0,30,0),"")
　Ｋ３　=IF(AND($C3>TIME(7,30,1),$C3<TIME(9,0,1)),TIME(0,30,0),"")
　　　＊出社、退社時間を元に、休憩時間を表示しています。

　Ｌ３　=F3-SUM(G3:K3)
　　　　＊勤務時間から休憩時間を引いた時間。

　Ｍ３　=IF(E3-D3>TIME(0,31,0),IF(E3-TIME(18,0,0)-SUM(H3)>TIME(4,0,0),TIME(4,0,0)-H3,C3-TIME(18,0,0)-SUM(H3)),"")
　　　　＊残業時間を出して、休憩時間を引いた時間。

　Ｎ３　=IF(E3>TIME(22,0,0),IF(D3>TIME(22,0,0),E3-D3-SUM(I3:K3),E3-TIME(22,0,0)-SUM(I3:K3)),"")
　　　　＊深夜時間を出して、休憩時間を引いた時間。

　ざっと、関数だけ書いてみました。
　何箇所か問題があります。
　１、出社時間はこのままでは使えないと思います。
　　　表を少し加工しなしと、思ったとおりの時間が表示されないはずです。
　２、出社時間が何時になるかで、残業時間、深夜時間が思ったとおりの時間を表示してくれません。
　　　色々考えました、表を使って対応しないとだめな気がします。

　たいした説明も書いていませんので、ご参考程度で読み流してください。
　
　

No.4 回答者： 135ok 回答日時： 2012/02/11 13:08

　先ほどの資料が見づらかったので、新たに資料を添付します。