エクセル勤務表で残業時間を自動で算出したいのですが

今年になり勤務表の様式が変更になりましたので残業時間を自動で算出出来る様に
したいのですがどうしたら良いか解らず困ってます。
教えていただけないでしょうか？

(1))残業開始時間（Ａ１時、Ａ２分）及び残業終了時間（Ａ３時、Ａ４分）のように別々のセルに
　なっています。
(2)これを残業の欄に入れるようになっています。

(3)深夜残業（２２：００～５：００）の残業の欄もあります。
　

これを各日の残業時間及び深夜残業は分単位で出し　又、集計欄では残業の分単位の合計を出　す欄が１つ、それを時間単位で出す欄が１つあります。
時間を出す欄の方は３０分以上を切り上げて出し３０分未満を切り捨てて出さなければいけません

　これらを自動で入力出来る様になりますでしょうか？
　どなたか解られる方いらっしゃいませんでしょうか。　よろしくお願いします！　
　
　
　

No.3 回答者： bunjii 回答日時： 2015/01/10 10:59

>これらを自動で入力出来る様になりますでしょうか？

「自動で入力」ではなく「自動で算出」でしょう。
出来ますが、丸投げの依頼のように思われます。
労務管理の一部であれば上司と相談して専門業者に有償で依頼すべきことのように思います。
出勤時刻の指定が有って遅刻や早退も同様な問題があるかと思われます。
単純に早出・残業の割増賃金の計算だけでなく深夜勤務の割増や休日出勤の割増なども関係してきますので勤務体系や労務管理の基準等も含めて検討されることをお勧めします。

単純に残業時間を計算する場合は残業終了時刻から残業開始時刻を引けばよいので難しい要素はありません。

>(1))残業開始時間（Ａ１時、Ａ２分）及び残業終了時間（Ａ３時、Ａ４分）のように別々のセルになっています。
そのような表を作成すること自体がアブノーマルなので1つのセルに残業開始時刻 17:30 のように入力し、隣接するセルに残業終了時刻 23:00 のように入力すべきです。
表の作成方法から見直す必要があるでしょう。
注意すべきことは終了時刻が翌日に跨るときどのように入力するかを決めておくことです。
表のフォーマットができてから再度質問されると良いでしょう。

No.2 回答者： ryo_ky 回答日時： 2015/01/10 09:48

話をすこし整理すると

A1：開始　時間
A2：開始　分
A3：終了　時間
A4：終了　分

各セルの配置については質問文から読み取れないところもあるので仮定して答えますが，
１つのシートで1ヶ月分とすれば
A列は1日，B列は2日・・・・AE列は31日ということですね．
残業時間は開始時間と終了時間の差分なのでこれを分単位に直したものが5行目にくるのであれば，
=(A3*60+A4)-(A1*60+A2)
とすれば，分単位の残業時間は分かります．
ただし，深夜まで残業した場合には翌日になることもあるので，仮定として翌日の5時以降は残業しない事とします．その場合には
=IF(A3>=22,(A3*60+A4)-(A1*60+A2),IF(A3*60+A4<=300,(A3+24)*60+A4-(A1*60+A2),0))

時間単位であるなら，分単位を60で割れば良いので，6行目に入れるのであれば
=A5/60
これらの式を31日分までオートフィルすれば各日の残業時間が分かります．

集計欄は分単位と時間単位があるのですね．
分単位はSUM関数で良いので5行目の分単位の残業時間から
=SUM(A5:AE5)

時間単位は30分，つまり0.5時間なので残業時間の合計値を小数点第一位を四捨五入すれば求まります．
=ROUND(SUM(A6:AE6),0)

次に深夜残業の部分ですが，22:00～5:00の範囲が深夜という事ですが，仮定として翌日の5時以降は残業しない事とします．
深夜残業の終了時間を分単位で求めるには，先ほどの残業時間の式の残業開始時間の部分を22時に固定すれば良いので
=IF(A3>=22,(A3*60+A4)-22*60,IF(A3*60+A4<=300,(A3+24)*60+A4-22*60,0))
これを60で割れば時間単位で分かります．


質問文の最後に書かれている自動入力というのは残業開始・終了時間を手入力せずに自動的に入れる方法という事でしょうか？

No.1 回答者： lv4u 回答日時： 2015/01/10 07:19

>>これらを自動で入力出来る様になりますでしょうか？

エクセルＶＢＡでプログラミングをすることで可能になります。
当然、それなりの知識が必要になります。
実際にプログラミングすると、休日、祭日や代休取得、遅刻、早退、有給など色々と考慮する条件が増えてきて、わりと行数の長いプログラムになります。

>>どなたか解られる方いらっしゃいませんでしょうか。　よろしくお願いします！　

初心者であれば、書店でエクセルＶＢＡの入門書を購入して勉強するのがいいと思います。
「お金をかけずにネットでお勉強」ってやり方もあるのですけど、これはプログラミング素養のある方向けです。
ゼロから勉強って方は、書籍を使って学ばれるほうがいいと思います。

この回答へのお礼

ありがとうございます！
そうですか～簡単には行かないみたいですね。
本屋さんに行ってこようと思います。

お礼日時：2015/01/10 07:35