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

解決済みの質問

エクセル時間の合計

エクセルでSUMを使って時間の合計を換算しているのですが、合計が合いません。
勤務日数は30日、勤務時間は8時間で合計時間は240時間となるはずでが、合計値が239:59:59と表示されています。
同じように残業がすべて0時間ですが、0:01:26と表示されています。
修正の方法を教えてください。

投稿日時 - 2011-11-19 10:13:20

QNo.7141721

困ってます

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

私も勤務表などで時間の計算で頭を悩ませた経験があります。
もしかしたら…と勝手な臆測を交えつつ、回答してみます。
解決のヒントになるところが見つかれば幸いです。

まず試してみて欲しいこと。
時刻「239:59:59」の入っているセルの隣でも下でも

=[そのセル]/30

として時刻表示してみて下さい。
「8:00:00」([h]:mm:ss表示)
となるはずです。
同じ「8:00」でも、30倍して240時間となる場合と、そうならない場合があるということがわかると思います。

時間について。
セルに「1」を入力して
表示形式 [h]:mm:ss のセルに「1」を入力してみて下さい。
「24:00:00」
となります。
整数「1」が「24時間="1日"」であることがわかります。
時間計算といっても、Excelの中では、数値として計算されています。
1 = 24:00
0.5 = 12:00
0.33333… = 8:00
といった具合。
この時、8時間のようにキリの悪い小数になってしまう時間の計算では、誤差が生じてくる事があります。

おそらく…
勤務時間の「8時間」というのは、直接「8:00」と入力しているのではなく、計算で出しているのでは?
[勤務時間] = [退社時間]-[出勤時間]-[休憩時間]
とか。残業時間も同様。
この計算の結果で出てきている結果に生じている誤差が、望む結果にしていないのです。
「8:00:00.431」とか、「7:59:59.567」とか。[h]:mm:ss表示に表れないような誤差。


時間の計算をしているセル(1日の勤務時間、1日の残業時間、合計時間など)で、
計算式の前後に以下のようなものを書き加えてみて下さい。 

=VALUE(TEXT( 【入力されている計算式】 ,"[h]:mm"))

これで誤差が完全に解消されるという訳ではありませんが、当面の問題は解消できるんじゃないかと思います。

投稿日時 - 2011-11-19 13:28:33

お礼

ご丁寧な回答ありがとうございました。おかげさまで解決しました。

投稿日時 - 2011-11-20 04:21:06

ANo.4

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

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

回答(5)

ANo.5

>勤務時間は8時間
8時間と表示されているセルの表示形式を
ユーザー定義 h:mm:ss.00
と分、秒、百分の一秒まで 表示させてみてください。
 8:00:00.00
でなくて
 7:59:59.50
などと表示される数値が含まれていませんか。

投稿日時 - 2011-11-19 18:46:23

お礼

ありがとうございました。

投稿日時 - 2011-11-20 04:21:37

ANo.3

あまりこの時刻に関連した個所で、エクセル計算上の疑義が出たようにはWEB故事など記事を見たことはないが。
(数値計算については色々言われている。)
時刻の=IF(A1=時刻・・)などでは疑問の例が出た記憶はある。
Googleででも「エクセル 計算 間違い(や不正確や誤差のいずれか)」などで照会すれば記事が出る。これら記事は難しい場合のケースである。
エクセルの時刻の場合の値は、セルの値は時刻シリアル値というべきもので24時間=1日を1として、8時間は1/3 の0.3333333・・と循環小数になる。背後にそういう仕組みは在る。
しかしA1に8:00:00と入れて、A30まで複写し、Σで合計すると240:00:00になった。
30セルのどこかに、書式設定がおかしい(他と違う)ところは無いですか。
一度30セルの表示形式を数値にして小数点以下14桁ぐらいにして、全セルで0.333・・・(3ばかり)かどうかチェックしてみては。

投稿日時 - 2011-11-19 13:13:03

お礼

ありがとうございました。おかげさまで解決しました。

投稿日時 - 2011-11-20 04:22:52

ANo.2

通常、SUM関数を使用していて、時間の合計(合計セルの書式設定の「表示形式」が経過時間の[h]:mmとなっている事が前提)が合わないような事はありませんので、可能性の話になってしまいますが・・・

1.SUM関数で範囲を指定する時にミスをしている
2.隠れたセルが存在している
3.時間のはいっている列のどこか一箇所、書式設定の「表示形式」が違う場所がある
  (通常は、時間のhh:mm)
4.バグが発生した

4の場合は新規のBookで作り直すしか方法はないと思います。

それでも、問題が解決しない場合は、もう少し詳しい情報を下さい。
Sum関数の内容や、書式設定「表示形式」の内容や、時間の入力方法などなど

投稿日時 - 2011-11-19 11:17:25

お礼

ありがとうございました。おかげさまで解決しました。

投稿日時 - 2011-11-20 04:20:10

ANo.1

毎日の勤務時間を計算するのに使った元データ(毎日の出社・退社時刻)に入れてある試験用のデータを,オートフィルドラッグなどでお手軽に作って埋めたときによく起こる症状です。
毎日のデータを,毎日使うのと同じように一日一日(つまり1セルごと)手で記入していれば,殆どの場合は誤差無しで計算できます。

簡易には,
1日目の出社時刻,退社時刻等を手入力で記入する
それぞれのデータをコピーし,他の日にただ貼り付ける
という手順で,240時間,残業0:0:0が計算できるか確認してみます。


若しくは,たとえばある一日分の「勤務時間数」を計算するセルについて
=今の式

=ROUND(今の式/"0:1",0)*"0:1"
としてみるような修正の方法もあります。

投稿日時 - 2011-11-19 11:17:18

お礼

ご指摘の通りコピーしてました。おかげさまで解決しました。

投稿日時 - 2011-11-20 04:24:11

あなたにオススメの質問