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

締切り済みの質問

エクセルで#####が表示されてしまう

エクセルで質問させてください。

時間計算をするエクセル表を作成したのですが、
計算結果のセルに「#####」のエラー表示になってしまうセルがあります。

負であるか大きすぎる日付時間である場合に表示されるというのはわかっているのですが、

同じに入力しているはずなのに、ある行はエラーにならずに、ある行はエラー######になります。

具体的には、0:00のものがエラーになっています。。

Aセルに「7:30」  Bセルに「7:30」  Cセルに「=Bセル-Aセル」として、
よってCセルには「0:00」となりますよね。
なのに、0:00になるセルと######になるセルがあります。

数式の引き算を逆にして「=Aセル-Bセル」にすると、
エラーだった行は0:00になるのですが、
逆にエラー出なかった行が#####になってしまいます。

どうしてでしょうか・・・
負の数字しゃないんですけど・・・
とても困っています。原因をおしえていただけると大変ありがたいです。
宜しくお願いします。

投稿日時 - 2013-06-07 22:21:39

QNo.8124014

すぐに回答ほしいです

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

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

回答(7)

ANo.7

Excelでの時間の取り扱い原理は理解していると思いますが、

>同じに入力しているはずなのに、ある行はエラーにならずに、ある行はエラー######になります。
"########"が表示されるのは時間の計算結果がマイナスで書式設定が時刻になっている時です。

考えられる原因はセルの書式設定が異なっている、計算式の参照対象が相対になっていてマイナスの値が出ている可能性があると推測します。

"########"のセルの書式を数値にして少数点以下の桁数を6位にして数値で値を確認してください、マイナスになっているなら式を見直し参照セルをみます、原因が具体的に判ると思います。

このようなことを防ぐには書式設定を正確に設定する、相対番地でなく絶定番地をしようすれば防げますが恐らく基本的な設定ミスと思われます。

Excel上では日付け、時刻は書式設定で簡単に出ますが日付けは正の整数、時刻は小数点以下の数値が使われます。

時刻を扱う場合日付けと時刻が混在している時int関数で整数部分を引いて少数だけを取り出し操作するということもプログラムを書くときやったりします。

投稿日時 - 2013-06-09 00:44:44

ANo.6

浮動小数点誤差による症状で時間の引き算でマイナスになっていることによるものでしょう。
時間の計算はシリアル値のよって行われています。24時間が1として1時間は1/24ということになります。
7時はシリアル値では0.291666666666667に相当します。この値はセルの表示形式を標準にすることで見ることができます。
このように小数点以下かなりの数までになっていますが実際にはもっと低くまでの数値になっているところですが最後に7に切り上げられて表示されるにすぎないのす。そのような数値の引き算などでは誤差がどうしても伴ってきます。
例えば次のような操作を行ってみましょう。
A1セルに7:00、A2セルに7:10、A3セルに7:20と…A7セルに8:00と入力します。
B1セルには=A1-A$1と入力してB7セルまで下方にドラッグコピーします。答えは正しく0:00,0:10,0:20・・のように表示されますね。何らの問題もありません。
そこでC2セルには0:10、C3セルには0:20、C4セルには0:30・・・・と手動で入力します。
その後にD2セルには =B2-C2 と入力してD7セルまで下方にドラッグコピーします。
D2セルやD3セル、D5セル、D6セルには######が表示されますね。
そこでD2セルからD7セルを選択して右クリックし、「セルの書式設定」の「表示形式」で「標準」を選択します。
非常に低い桁数のところの数値が元で答えがマイナスになっていることが分かりますね。本来ですと0の表示になるはずですが0の表示となっているのは0:30のときのD4セルのみとなっています。このように非常に少ない桁数のところで生じる誤差によっておこる現象を浮動小数点誤差と呼んでいます。
このようにD列での問題を解消するためには例えばシリアル値の小数点以下の数値を例えば10ケタ程度にして差を求めるといったことをすればよいでしょう。
例えばD2セルに入力する式を =ROUNDDOWN(B2,10)-ROUNDDOWN(C2,10) として下方にドラッグコピーします。その場合にはシリアル値が0となって時刻表示では 0:00 となって問題を解消することができます。
単純に0:10とA1セルに入力しB1セルにも0:10と入力し =A1-B1 といったケースでは0の答えが得られ問題はないでしょう。計算した結果で得られた時刻を入力した時刻のデータと比較するような場合に起こりやすい問題です。

投稿日時 - 2013-06-08 08:49:08

ANo.5

訂正です
誤) 2013/6/9 7:30 - 2013/6/8 7:30 = -1日
正) 2013/6/8 7:30 - 2013/6/9 7:30 = -1日

投稿日時 - 2013-06-08 00:25:42

ANo.4

Excelで日付を使うと、内部ではシリアル値という数値で処理されます。
これは、基準日を0として、1日を1として、1時間を1/24として、基準からどれくらい経ったかを表わしています。

表示形式を、「G/標準」 等の数値に変更すると、日付では無く、シリアル値がそのまま表示されます。
一度、A,B,C全て書式を G/標準 とか、 0.00 等の数値にしてみてください。どうなりますか?

あと、時間だけの表示形式にすると
2013/6/8 7:30

2013/6/9 7:30

7:30
と表示されます。
7:30-7:30=0:00
のつもりが
2013/6/9 7:30 - 2013/6/8 7:30 = -1日
になってしまっている、とかは無いでしょうか?

投稿日時 - 2013-06-08 00:23:45

ANo.3

マイナスの値になっているからエラーになるというのは前のかたが回答しているとおりです。

たしか、日時のオプションで1900年から起算するか、1904年から起算するかを設定する項目があるはずです。
通常は1900年から起算するようになっているのですが、これを1904年から起算するように変更してみてください。
自分が知る限りでは、これで日時がマイナスの値をとってもエラーにならず表示ができたと記憶しています。

ただし、このオプションを変更すると、既存の日付がずれてしまう可能性があります。
注意してください。


※1904年から起算するオプションはアップルコンピュータ社製のパソコン(Mac)向けのExcelとの互換性を保つためにあります。
1900年が閏年でないことからアップルコンピュータ社では1904年以前の日付をサポートしなかったと言われています。

投稿日時 - 2013-06-07 23:07:04

お礼

回答ありがとうございます。

負の数字(マイナスの時間表示)ではないので、

「日時のオプションで1900年から起算するか、1904年から起算するかを設定する項目」
というのをしてみようと思ったのですが、
どうやったら確認できるのかわかりませんでした。

どの画面でみれば確認できるのでしょうか?

度々ですみませんが、ご教授いただければありがたいです。

宜しくお願いします。

投稿日時 - 2013-06-07 23:52:56

ANo.2

ごめんなさい。
エラーを出せました。

原因は、[-]の時間結果が出るためです。
人間には『-1:00』って理解できるけど、エクセルでは、エラーとなります。(理解できない)
なので、###が表示されているところは、「=Bセル-Aセル」の計算式が、正しくないのです。
時間は、増えていっても、減る事はありません。

詳しくないけど、時間をシリアル値に変換してからだと、正しく計算出来るかも知れません。
(そんな話しを聞いた覚えがある。)

投稿日時 - 2013-06-07 22:48:22

お礼

すみません。
お調べいただいて、再回答までいただいていたのですね。ありがとうございます。

ただ、エラーのセルは「-(マイナス)」ではないんです。
「0:00」なんです。。。

シリアル値変換?
私もよくわからないので、わかる範囲で調べてみることにします。

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

投稿日時 - 2013-06-07 23:48:41

ANo.1

あの~、それって、単純にセル幅が足りていないだけじゃないの?

表示の桁数未満のセル幅だと、そういう表示になることがあるけど・・・。

投稿日時 - 2013-06-07 22:37:10

お礼

回答していたいてありがとうございます。

ただ、、セル幅は足りています。。
どんなに幅を広げても ###########になるだけなのです。。。

なぜでしょうか・・

投稿日時 - 2013-06-07 23:43:25

あなたにオススメの質問