みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

残業時間月合計(エクセル)を正確に計算できません

エクセル2007で,勤務時間を管理表を作成していますが,残業時間月合計(G36のセル)が正確に計算されません。
社員に入力してもらうのは,出勤時間と退社時間だけです。
勤務時間(在勤時間)は8:25~16:55で,途中の休憩時間は考慮しません。
項目と入力してある関数は下のとおりです。

  A  B     C     D      E         F        G
  日 曜日 出勤時間 退社時間 在勤時間  基本在勤時間   残業時間
5 1  金    8:15    17:00   8時間30分   8:30        0時間15分
6 2   土    9:00    10:10    1時間10分   0:00        1時間10分

36 月合計                 229時間40分  68:10       68時間10分           

E列の関数・・・「=D5-C5」(表示形式は h"時間"mm"分")
F列(非表示)・・・平日は「8:30」と入力(8:25~16:55が8時間30分であるため)
           土日・休日は「0:00」と入力(勤務日でないため)
G列の関数・・・「=D5-C5-F5」(表示形式は h"時間"mm"分")
E36の関数「=SUM(E5:E35)」(表示形式は [h]"時間"mm"分")
F36の関数「=SUM(F5:F35)」(表示形式は [h]:mm)
G36の関数=SUM(G5:G35)」(表示形式は [h]"時間"mm"分")

平日で,C列とD列が入力していないのに,F列に8:00が入力してあるため,G列の計算がマイナスになることが原因で正確に計算できないのでしょうか。
簡単な関数を使って正確に残業時間月合計が計算できる方法を教えてください。
よろしくお願いします。


              

投稿日時 - 2011-01-04 20:40:08

QNo.6425411

すぐに回答ほしいです

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

No.3です!
>でも正確に計算ができません。
とありますので・・・

計算式が入っているセルすべてに
=ROUND(計算式,5) を利用してもダメでしょうか?

=IF(COUNTBLANK(C5:D5),"",ROUND(計算式,5))
といった感じです。

他に時間計算のセルがある場合も単純にプラス・マイナスするだけでなく
=ROUND(計算式,5)
のようにします。

それでも希望通りにならなかったらごめんなさいね。m(__)m

投稿日時 - 2011-01-05 20:44:20

お礼

うまくできました!!
急いでいたので,とても助かりました。
何度も回答いただき感謝しています。
ありがとうございました。

投稿日時 - 2011-01-06 00:15:26

ANo.4

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

こんばんは!
内容を詳しく検証していませんので、外していたらごめんなさい。

(1)F列の数式を =IF(COUNTBLANK(C5:D5),"",計算式) のようにして、C・D列のどちらかが未入力であれば数値を表示しないようにしてみてはどうでしょうか?

(2)No.1の方が仰っているように時間計算は「浮動小数点」の関係でこちらの希望通りの数値にならないことがあります。

それを回避する方法として、計算式を小数点以下5桁くらいで丸める方法があります。
=ROUND(計算式,5)
といったような感じです。

それから余計なお世話かもしれませんが、
>G列の計算がマイナスになることが原因で正確に計算できないのでしょうか。
とありますので、もしマイナスの計算を行いたい場合は
当方使用のExcel2003の方法として
メニュー → ツール → オプション → 計算方法タブ で「1904年から計算する」にチェックを入れれば
マイナス計算も可能になります。
Excel2007以降も同様の方法はあると思いますが、具体的な操作は手元にExcel2007がないのでごめんなさい。

尚、この方法はあくまで最終手段で極力時間計算等にはマイナスが出ない方が良いと思います。

この程度でごめんなさいね。m(__)m

投稿日時 - 2011-01-04 21:08:01

お礼

ありがとうございます。
=IF(COUNTBLANK(C5:D5),"",計算式) は,勉強になりました。
利用させていただきます。
でも正確に計算ができません。
また,勉強してみます。

投稿日時 - 2011-01-04 23:14:06

5/1(金)在勤時間は8:45のはずです。 また、在勤時間と基本在勤時間が同じなのに、残業が0:15つくのは変です
E列の関数にFLOORとか使われているのですか?
さらに F列は8:30と0:00しか入力されないのなら、合計で68:10となるのも変です

>C列とD列が入力していないのに,
>F列に8:00が入力してあるため,
>G列の計算がマイナスになることが
それは大きく誤差を生むはずですので、C列およびD列にシリアル値が入力されていない場合はEもFもGもゼロと入力されるようにしなくてはならないのでは?

投稿日時 - 2011-01-04 20:56:10

お礼

ありがとうございます。分かりにくくてすみません。
「5」「36」は行番号で,
「5」行目が1日,「36」行目が31日で,「6」から「35」の間は日にちが入力されています。

投稿日時 - 2011-01-04 23:12:01

ANo.1

G列を以下の式にするのはどうでしょうか?
IF(C5="",0,D5-c5=f5)

時間計算はかなり難しい物です。
また、出退勤には各社の細かいルールがあり、Excelを
これに併せるのは難しいこともあります。
試行錯誤をしてがんばってください。

投稿日時 - 2011-01-04 20:55:14

お礼

ありがとうございます。
時間を入力すると,「FALSE」が表示されてしまいます。
試行錯誤してみます。

投稿日時 - 2011-01-04 23:07:22

あなたにオススメの質問