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

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

解決済みの質問

エクセルで時刻の計算結果が-0:00となってしまう

エクセルでタイムカードを作成しています。残業時間を出す際に計算結果が0の場合、
0にマイナスがついて-0:00と表示されてしまいます。
時刻計算は[h]:mmで計算し、マイナスとなった場合にも対応できるようオプションを1904年から計算する、としています。
-0:00を普通に0:00と表示させる解決方法を教えてください。
よろしくお願いいたします。

投稿日時 - 2011-03-04 00:36:53

QNo.6566248

困ってます

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

時刻の比較と言うテーマでしょう。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/if_is.htm
小数誤差を含んでいる可能性があるとき
ーー
Googleででも「エクセル 時刻の比較」「エクセル 時刻の比較 誤差」などで照会してめぼしいものを読んでください。
エクセル時刻計算誤差の解消方法
http://www.excel.studio-kazu.jp/mwiki/index.php/%E6%99%82%E5%88%BB%E8%A8%88%E7%AE%97%E8%AA%A4%E5%B7%AE%E3%81%AE%E8%A7%A3%E6%B6%88%E6%96%B9%E6%B3%95 ほか
一方のセルの値が関数の値などのときが問題か。時刻の差の計算も時刻によって要注意。

投稿日時 - 2011-03-04 12:12:52

お礼

ありがとうございます。
参考ページのROUND関数で分単位の整数値で丸める説明がわかりやすく、うまく解消できました。
時刻の引き算だけでは0:00となり誤差はでないのですが、if関数と組み合わせるとー0:00となるようでした。

投稿日時 - 2011-03-05 23:42:40

ANo.5

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

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

回答(5)

ANo.4

 入力されている関数を

=FLOOR(元の関数+1/86400,1/1440)

としては如何でしょうか。

投稿日時 - 2011-03-04 01:36:12

お礼

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

投稿日時 - 2011-03-05 21:04:59

ANo.3

幾つかの原因が考えられますが,たとえば「テストデータ」でそういう状況が発生した場合,オートフィルドラッグでテスト用の時刻データを作成していてよくそういう「誤差」が出てしまう場合があります。
生の時刻データを打ち込み直してみると,改善しているかもしれません。

あるいは今どんな計算を組み立てられたのか不明ですが,時刻データの計算誤差によっても,やはりそういう結果になる可能性もあります。

マイナス時刻が計算されたセルの書式設定の表示形式を一度「標準」に変えてみると,たとえば -1.xxxE-08とかそのぐらいの大きさの数字が計算されてるんじゃないかな?と思います。参考にふつーにプラスの0:00が計算されているセルについても同じように標準の表示形式に変えてみると,やっぱりジャストゼロじゃなくて,プラスの1.xxxE-07とかが計算されていものが多数あると思います。
ちなみに「1秒」は1.157E-05です。

というワケで非常に簡易な対処法としては,今マイナスゼロが出ているセルの計算式に
=今の式+1E-7
ぐらい,つまり0.01秒ぐらいちょっとゲタをはかせておくと,大まかにマイナスゼロ時表示を回避できます。こういのを100ほど集めて合算してもやっと1秒ですから,計算結果に実害はありません。



#注意
同じ理由で,しばしば
=IF(時刻を計算したセル=0,ジャストゼロの時,そうでないとき)
=IF(時刻を計算したセル>0,プラスの時,ゼロやマイナスのとき)
のような判別式やVLOOKUPなどの式を組み立ててゼロ時で仕分けようとして,計算に失敗する場合があります。

どうしても心配なようなら,適当なところで計算の中間結果を一度「分単位の整数」などに丸めてしまい,そこで端数をリセットしてから改めて時刻に戻して続きの計算をする(あるいはそのまま分単位などで以後の計算を続行する)ような工夫もあります。

投稿日時 - 2011-03-04 01:16:53

お礼

ありがとうございます。
計算式はIF関数を使った時刻の引き算です。
計算結果を標準値にしてみると-3.46945E-17でした。
このような誤差が原因なのですね。
今の式+1E-7でうまく0になりました。
ありがとうございました。

投稿日時 - 2011-03-05 20:49:32

ANo.2

参考までに。

No1の回答では簡便な対応策を提示しましたが、時間の大小関係を評価したい時などに、手入力したデータとまったく同じ値にしたいなら、以下のような関数を使ってください。

=TEXT(元の式+10^-10,"[h]:mm")*1

例えば、上記の数式で「1:00」と表示される場合は「1:00」と入力した場合と正確に同じ数値になり、「0:00」と表示される場合は、本当にセルの値が「0」になっています。

投稿日時 - 2011-03-04 01:09:30

補足

先ほどはもとの計算式をコピーするときに間違えていました。
失礼しました。0:00になりました。
ありがとうございました。
しかし、結果がマイナスの時は表示が###なってしまいました。
NO1の方法でしたらうまくできました。

投稿日時 - 2011-03-05 21:02:15

お礼

ありがとうございます。
こちらの式に元の式を入力すると、0:00にはなりましたが、計算がうまくできませんでした。
始業終業時刻が変わって残業が発生する場合でも0:00のままとなりました。
もとの計算式が間違っているのかもしれませんが。

投稿日時 - 2011-03-05 20:38:01

ANo.1

ご質問の現象は、2進数で表示できない小数点以下の数値(時間)の引き算による丸め誤差(引き算の結果が正確に0でなくマイナスの値になること)が原因となっています。

簡便に対応するなら、元のデータに微小値を加えた以下のような数式にしてください。

=元の式+10^-10

投稿日時 - 2011-03-04 00:50:41

お礼

ありがとうございます。
マイナスがとれてうまくできました。

投稿日時 - 2011-03-05 20:34:22

あなたにオススメの質問