Excelで勤務時間の管理表を作成したい

Excel初心者です。
Excelに詳しい方、どうかご教示いただきたく思います。
現在、タイムカードを元に、Excelで勤務時間の管理をしようとしていますが、
思うように出来ず、大変困っております。

作成したい勤務管理表としては、添付の画像のような形式になるのですが、
出社時刻も退社時刻も、昼休憩の開始時間や昼休憩の時間の長さも
人によってまちまちで特殊な働き方のため、管理が難しいです。
そのため、添付画像のように、
タイムカードの出社時刻と退社時刻を記入するだけではなく、
実際に業務を行った時間の内訳がわかるよう、
午前と午後の開始時間と終了時間の枠を設け(E列～J列)、
それを元に勤務時間合計(K列)を出すようにしています。

そこで、勤務が午前～午後にまたがり昼休憩を挟む場合は、
午前の終了時間(G列)と午後の開始時間(H列)は手入力をするようにし、
それ以外は、タイムカードの出社時刻(C列)と退社時刻(D列)が、
午前の開始(E列)および午後の終了(J列)の欄に反映されるようにしたいです。

なおその際に、以下の4点を考慮して作成したいと考えています。

(1)出社時刻(業務開始時間)は30分単位で切り上げる。
(2)退社時刻(業務終了時間)は30分単位で切り下げる。
(3)タイムカードの出社時刻欄(C列)や退社時刻欄(D列)に何も入力されていない場合は、
　午前・午後の開始欄・終了欄(E列～J列)は空欄にさせる。
　同様に、午前だけの勤務時は、午後の開始欄・終了欄(H列～J列)を空欄、
　午後だけの勤務時は、午前の開始欄・終了欄(E列～G列)を空欄にさせる。
(4)タイムカードの出社時刻(C列)に合せて、その時刻が午前ならば午前の開始欄(E列)に、
　午後ならば午後の開始欄(H列)に、(1)で丸めた時間を表示させる。
　同様に、退社時刻(D列)に合わせて、その時刻が午前ならば午前の終了欄(G列)に、
　午後ならば午後の終了欄(J列)に、(2)で丸めた時間を表示させる。
(※但し、添付画像の6行目の例の様に、午前だけの勤務の際に、終了時間が12時を過ぎていた場合は、午前の終了欄(G列)に表示させる。)

現在は勤務時間合計(K列)だけ計算式を入れており、それ以外は全て手入力のため、
手間がかかるだけでなく、入力ミス等にも繋がるため、
出来るだけミスの少ない管理表を作成したいと思っています。

条件の多い質問で大変恐縮ではありますが、
なるべく簡単な方法を教えていただけると大変助かります。

どうぞ宜しくお願い致します。

No.3 ベストアンサー 回答者： tom04 回答日時： 2013/11/30 19:25

こんばんは！

＞勤務が午前～午後にまたがり昼休憩を挟む場合は、
＞午前の終了時間(G列)と午後の開始時間(H列)は手入力をするようにし・・・
と
＞午前・午後の開始欄・終了欄(E列～J列)は空欄にさせる。
＞同様に、午前だけの勤務時は、午後の開始欄・終了欄(H列～J列)を空欄、
をこちらの解釈では
数式を入れたセルに直接手入力するような感じになりますよね？
これでは「手入力」した段階で入っている数式は消えてしまいますけど、
それでも良いのであれば、一例です。

尚、一般的な考え方として　12:00～13:00　を昼休憩としていますので、
G・H列に「手入力」しなければ　G列は12：00　H列には13：00　と表示されます。

画像の配置通りだとして、
E3セルに
=IF(OR(C3="",C3>="12:00"*1),"",CEILING(C3,"0:30"))

G3セルに
=IF(OR(D3="",C3>="12:00"*1),"",IF(D3<="12:00"*1,FLOOR(D3,"0:30"),"12:00"))

H3セルに
=IF(OR(C3="",D3<="13:00"*1),"",IF(C3<="13:00"*1,"13:00",CEILING(C3,"0:30")))

J3セルに
=IF(OR(D3="",D3<="13:00"*1),"",FLOOR(D3,"0:30"))

K3セルに
=IF(COUNTBLANK(C3:D3),"",SUM(IFERROR(G3-E3,0),IFERROR(J3-H3,0)))

という数式を入れ、それぞれのセルをオートフィルで月末行までコピーしてみてください。

こんな感じではどうでしょうか？m(_ _)m

この回答へのお礼

こんばんは。
早速ご回答をいただきどうもありがとうございます。


＞数式を入れたセルに直接手入力するような感じになりますよね？
＞これでは「手入力」した段階で入っている数式は消えてしまいますけど、
＞それでも良いのであれば、一例です。
はい、そうなんです、本当は数式を消してしまいたくないのですが、
日によっても、人によっても昼休憩の開始時間および休憩の長さが違うため、
やむを得ず手入力で対処しようと思っている次第です。


＞尚、一般的な考え方として　12:00～13:00　を昼休憩としていますので、
＞G・H列に「手入力」しなければ　G列は12：00　H列には13：00　と表示されます。
はい、基本的には　12:00～13:00　で昼休憩をとる人の方が多数ですので、
G列は12：00　H列には13：00　と表示された方が良いことに、
恥ずかしながら、tom04さんのご提案で気付きました。
どうもありがとうございます。


具体的な数式を、添付画像の例に沿って示して下ったおかげで、
実際に数式を入力し、試すことが出来ました。
完全に煮詰まってしまっていた勤務管理表でしたが、
思っていた通りの表が出来ました！

こちらに投稿するまでに自分なりに色々と調べてみましたが、
知識が浅すぎてなかなか形にすることが出来ず、とても困っていたため、
すごく具体的に丁寧にお教えいただき、
本当に助かりました！

お教えいただいたこの方法で、
勤務管理表に活かしていきたいと思います。
どうもありがとうございました。

お礼日時：2013/11/30 21:53

No.4 回答者： bunjii 回答日時： 2013/11/30 19:50

>午前は会社に出勤し、午後は在宅で仕事をする、というスタイルなどがあるため、

それでは管理にならないでしょう。
単純な勤務時間の計算と言うことになるのではないですか？
在宅での仕事は申告時間を丸呑みで受け入れるでしょうし、開始時刻と終了時刻の申告は考えなくて良いと思います。
時給の単価に差が無ければ午前と午後の区分けが必要と言う理由もないように感じます。
時間外勤務や深夜勤務のように割増の要素も提示されていませんので何を知りたいのか分かりません。

タイムカードについてもカードに打刻された時刻をそのまま手入力するのであれば30分単位の繰り上げ、切り捨ても暗算でできるのではないでしょうか？

アイムレコーダーに保存されたデータをメモリー等でPCへ読み込むのであれば30分未満の繰り上げ、切り捨ての計算に数式をどうするか考える必要があるでしょう。
今回の質問ではそれ以外に必要性はなさそうです。

Excelで時間の計算をする場合は1時間が1/24と言う基準の小数ですから、それを基に算出すれば良いので手入力の時に文字列として入力しない（シリアル値で入力する）ことが肝要です。

この回答へのお礼

二度に渡りご回答をいただき、どうもありがとうございます。

＞時給の単価に差が無ければ午前と午後の区分けが必要と言う理由もないように感じます。
＞時間外勤務や深夜勤務のように割増の要素も提示されていませんので何を知りたいのか分かりません。
おっしゃる通り午前と午後で時給単価に差はないのですし、
時間外勤務等のように割増の要素もありません。
今回作成しようとしている勤務管理表では、直接時給の計算をするためのものではなく、
あくまでも、勤務時間の内訳(内勤時間・在宅勤務時間・昼休憩時間)を具体的に示し、
残すために管理しているといった位置づけのものです。

＞タイムカードについてもカードに打刻された時刻をそのまま手入力するのであれば
＞30分単位の繰り上げ、切り捨ても暗算でできるのではないでしょうか？
はい、現在はタイムカードに打刻された時刻を手入力し、
繰り上げ・切り捨ては暗算し、手入力しています。
ですが、会社の人数が少しずつ増えてきて、
この作業をひとつひとつこなしていくのがだんだん非効率になってきたため、
出来るだけ作業を効率化し、ミスを減らしたく、
今回こちらでご質問させていただいた次第でした。

＞Excelで時間の計算をする場合は1時間が1/24と言う基準の小数ですから、
＞それを基に算出すれば良いので手入力の時に文字列として入力しない
＞（シリアル値で入力する）ことが肝要です。
はい、この点をしっかり気を付けて入力致します。
お教えいただき、どうもありがとうございます。


投稿時、全体的に私の言葉足らずなために、
主旨が伝わりづらく大変失礼致しました。

そんな中でも二度に渡りご回答いただき、
本当にどうもありがとうございました。

お礼日時：2013/11/30 22:19

No.2 回答者： bunjii 回答日時： 2013/11/30 16:00

管理手法を考え直した方がシンプルにできます。

出勤、退勤はタイムカードからの算出として休憩の開始と終了を申告させる方法で良いと思われます。
極論すれば申告が無いときは12時から13時を休憩とすることでも良いとすれば勤務時間=退勤時刻-出勤時刻-1で良いことになります。
上司または先輩と相談して腹案を作成されると良いでしょう。
実際の運用は労使で合意されるべき問題です。

この回答へのお礼

早速のご回答をどうもありがとうございます。
ご提案いただいた休憩の開始と終了を申告させる方法なのですが、
実はそれも管理の仕方として考えたのですが、
質問を投稿する際に記載し忘れていた事があり(大変申し訳ありません)、
そのためにご提案いただいた方法をとりづらい現状です。

というのも、働き方の現状として、
午前は会社に出勤し、午後は在宅で仕事をする、
というスタイルなどがあるため、
それを勤務管理表の中でも一目見たらわかるようにするためには、
現在の午前と午後で開始・終了をそれぞれ設ける形式がわかりやすく、
もし可能であれば、
出来るだけこの形を崩さない方向で出来ると大変助かります。
(在宅の勤務時間には色を付けるようにしています。)

投稿時にきちんと現状を記載していなかったために、
大変申し訳ありません。
ご提案いただきどうもありがとうございました。

お礼日時：2013/11/30 17:19

No.1 回答者： dimio38 回答日時： 2013/11/30 12:16

http://template.k-solution.info/2006/09/01_excel …

探せば、無料のテンプレートがあるので探してみては。

この回答へのお礼

早速のご回答、そしてテンプレートのご紹介をどうもありがとうございます。
テンプレートに関しては、こちらに投稿する前に私自身も色々と探してはみたのですが、
なかなか条件に合致するようなものを見つけることが出来ず、
今回投稿させていただいた次第でした。
ですが、ご紹介いただき、参考になりました。
どうもありがとうございました。

お礼日時：2013/11/30 16:57