
エクセルで質問させてください。
時間計算をするエクセル表を作成したのですが、
計算結果のセルに「#####」のエラー表示になってしまうセルがあります。
負であるか大きすぎる日付時間である場合に表示されるというのはわかっているのですが、
同じに入力しているはずなのに、ある行はエラーにならずに、ある行はエラー######になります。
具体的には、0:00のものがエラーになっています。。
Aセルに「7:30」 Bセルに「7:30」 Cセルに「=Bセル-Aセル」として、
よってCセルには「0:00」となりますよね。
なのに、0:00になるセルと######になるセルがあります。
数式の引き算を逆にして「=Aセル-Bセル」にすると、
エラーだった行は0:00になるのですが、
逆にエラー出なかった行が#####になってしまいます。
どうしてでしょうか・・・
負の数字しゃないんですけど・・・
とても困っています。原因をおしえていただけると大変ありがたいです。
宜しくお願いします。
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
Excelでの時間の取り扱い原理は理解していると思いますが、
>同じに入力しているはずなのに、ある行はエラーにならずに、ある行はエラー######になります。
"########"が表示されるのは時間の計算結果がマイナスで書式設定が時刻になっている時です。
考えられる原因はセルの書式設定が異なっている、計算式の参照対象が相対になっていてマイナスの値が出ている可能性があると推測します。
"########"のセルの書式を数値にして少数点以下の桁数を6位にして数値で値を確認してください、マイナスになっているなら式を見直し参照セルをみます、原因が具体的に判ると思います。
このようなことを防ぐには書式設定を正確に設定する、相対番地でなく絶定番地をしようすれば防げますが恐らく基本的な設定ミスと思われます。
Excel上では日付け、時刻は書式設定で簡単に出ますが日付けは正の整数、時刻は小数点以下の数値が使われます。
時刻を扱う場合日付けと時刻が混在している時int関数で整数部分を引いて少数だけを取り出し操作するということもプログラムを書くときやったりします。
No.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の答えが得られ問題はないでしょう。計算した結果で得られた時刻を入力した時刻のデータと比較するような場合に起こりやすい問題です。
No.5
- 回答日時:
訂正です
誤) 2013/6/9 7:30 - 2013/6/8 7:30 = -1日
正) 2013/6/8 7:30 - 2013/6/9 7:30 = -1日
No.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日
になってしまっている、とかは無いでしょうか?
No.3
- 回答日時:
マイナスの値になっているからエラーになるというのは前のかたが回答しているとおりです。
たしか、日時のオプションで1900年から起算するか、1904年から起算するかを設定する項目があるはずです。
通常は1900年から起算するようになっているのですが、これを1904年から起算するように変更してみてください。
自分が知る限りでは、これで日時がマイナスの値をとってもエラーにならず表示ができたと記憶しています。
ただし、このオプションを変更すると、既存の日付がずれてしまう可能性があります。
注意してください。
※1904年から起算するオプションはアップルコンピュータ社製のパソコン(Mac)向けのExcelとの互換性を保つためにあります。
1900年が閏年でないことからアップルコンピュータ社では1904年以前の日付をサポートしなかったと言われています。
回答ありがとうございます。
負の数字(マイナスの時間表示)ではないので、
「日時のオプションで1900年から起算するか、1904年から起算するかを設定する項目」
というのをしてみようと思ったのですが、
どうやったら確認できるのかわかりませんでした。
どの画面でみれば確認できるのでしょうか?
度々ですみませんが、ご教授いただければありがたいです。
宜しくお願いします。
No.2
- 回答日時:
ごめんなさい。
エラーを出せました。
原因は、[-]の時間結果が出るためです。
人間には『-1:00』って理解できるけど、エクセルでは、エラーとなります。(理解できない)
なので、###が表示されているところは、「=Bセル-Aセル」の計算式が、正しくないのです。
時間は、増えていっても、減る事はありません。
詳しくないけど、時間をシリアル値に変換してからだと、正しく計算出来るかも知れません。
(そんな話しを聞いた覚えがある。)

すみません。
お調べいただいて、再回答までいただいていたのですね。ありがとうございます。
ただ、エラーのセルは「-(マイナス)」ではないんです。
「0:00」なんです。。。
シリアル値変換?
私もよくわからないので、わかる範囲で調べてみることにします。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) エクセルの計算式について教えて下さい。 sheet1のA1セルの値をsheet2のB1セルに返し、尚 1 2022/05/25 11:32
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- 会計ソフト・業務用ソフト Excel IF構文内の計算式を有効にする方法 2 2023/03/22 11:27
- Excel(エクセル) エクセルVBAでセルに表示されているとおりの数値を取得したい(時間の計算結果) 1 2022/03/30 17:52
- Excel(エクセル) 表計算ソフトcalcにおいて、1時間10分を1.1と表記する方法とそれらを集計する方法は? 4 2022/04/06 16:54
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/03/06 13:32
- Excel(エクセル) エクセルのマクロについて教えてください。 1 2023/02/25 16:07
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) エクセルの「セル書式」の「表示形式」で 2 2022/08/07 16:44
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのVBAで集計をしたい
-
【関数】同じ関数なのに、エラ...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
Office2021のエクセルで米国株...
-
【画像あり】オートフィルター...
-
【マクロ】実行時エラー '424':...
-
特定のセルだけ結果がおかしい...
-
【マクロ】列を折りたたみ非表...
-
他のシートの検索
-
【マクロ】アクティブセルの時...
-
【条件付き書式】シートの中で...
-
【マクロ】【相談】Excelブック...
-
ページが変なふうに切れる
-
エクセル ドロップダウンリスト...
-
【マクロ】オートフィルターの...
-
【マクロ】元データと同じお客...
-
【マクロ】3行に上から下に並...
-
エクセルのdatedif関数を使って...
-
【マクロ】数式を入力したい。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
9月17日でサービス終了らし...
-
エクセル
-
【マクロ】WEBシステムから保存...
-
エクセルの循環参照、?
-
エクセル ドロップダウンリスト...
-
エクセルのdatedif関数を使って...
-
特定のセルだけ結果がおかしい...
-
【マクロ】A列にある、日付(本...
-
【マクロ】EXCELで読込したCSV...
-
【マクロ】アクティブセルの時...
-
【エクセル】期限アラートについて
-
iPhoneのExcelアプリで、別のシ...
-
【関数】同じ関数なのに、エラ...
-
Excelの新しい空白のブックを開...
-
【マクロ】3行に上から下に並...
-
【マクロ】宣言は、何のために...
-
VBA チェックボックスをオーバ...
-
Excelについての質問です 並べ...
-
【マクロ】アクティブセルの2...
-
【関数】不規則な文章から●●-●●...
おすすめ情報