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

2012/02/10 15:24

このQ&Aのポイント

出勤簿の作成において、出社時間と退社時間を入力すると、勤務時間、残業時間、深夜残業時間が自動的に計算される数式をExcelで作成したいです。
出社時間と退社時間を基に、勤務時間を計算するための数式の作成が課題です。また、特定の休憩時間が存在することも複雑さの一因です。
例えば、出勤時間が9：00、退社時間が21：00の場合、勤務時間は10.5時間となります。残業時間は2.5時間となり、特定の休憩時間は除外されます。このような条件に基づいて、出勤簿の数式を作成する方法を教えてください。

hishishis
お礼率72% (8/11)

その他MS Office製品
回答数4
ありがとう数2

みんなの回答 （4）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

135ok
ベストアンサー率34% (26/75)

2012/02/11 12:38 回答No.2

　興味があり作ってみました。　完璧ではないので、ご参考程度で。　以前、同様なものを作った事があります。その際は、No.１さんが提案された表引きで作成しました。　今回は別の方法で作成しましたが、表を作って作成した方が楽なような気がします。　さて、画像が２つ必要なので、２回に分けて記入します。　まず始めに時間を計算するために、別添の表を作成します。　出社用と退社用の二つを表を作成し、これを元に時間の計算をします。　

質問者

お礼 2012/02/12 16:58

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

その他の回答 (3)

135ok
ベストアンサー率34% (26/75)

2012/02/11 13:08 回答No.4

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

135ok
ベストアンサー率34% (26/75)

2012/02/11 13:05 回答No.3

　画像を添付しますので、そちらを参照ください。　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)),"") 　　　　＊深夜時間を出して、休憩時間を引いた時間。　ざっと、関数だけ書いてみました。　何箇所か問題があります。　１、出社時間はこのままでは使えないと思います。　　　表を少し加工しなしと、思ったとおりの時間が表示されないはずです。　２、出社時間が何時になるかで、残業時間、深夜時間が思ったとおりの時間を表示してくれません。　　　色々考えました、表を使って対応しないとだめな気がします。　たいした説明も書いていませんので、ご参考程度で読み流してください。　　