エクセルで質問させてください。
時間計算をするエクセル表を作成したのですが、
計算結果のセルに「#####」のエラー表示になってしまうセルがあります。
負であるか大きすぎる日付時間である場合に表示されるというのはわかっているのですが、
同じに入力しているはずなのに、ある行はエラーにならずに、ある行はエラー######になります。
具体的には、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で質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
あなたが好きな本屋さんを教えてください
どのくらいの規模間で、どのような本が並んでいるか、どのような雰囲気なのかなどなど...
-
エクセル #### 表示させない方法
Excel(エクセル)
-
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
-
4
エクセルで時刻の計算結果が-0:00となってしまう
Excel(エクセル)
-
5
エクセルで「負の日付または時間は####と…
その他(コンピューター・テクノロジー)
-
6
エクセルで時間表示を負(-)で表示方法
Excel(エクセル)
-
7
エクセルにて負の時間を0:00と表示する方法を教えてください
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・スタッフと宿泊客が全員斜め上を行くホテルのレビュー
- ・【大喜利】【投稿~8/27】 こんなガソリンスタンド二度と来るか!なぜそう思った?
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・【お題】動物のキャッチフレーズ
- ・【お題】甲子園での思い出の残し方
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・「それ、メッセージ花火でわざわざ伝えること?」
- ・自分用のお土産
- ・人生で一番お金がなかったとき
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・ちょっと先の未来クイズ第1問
- ・ゴリラ向け動画サイト「ウホウホ動画」にありがちなこと
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】functionプロシージ...
-
Excelの警告について
-
Excelシートに画像を貼る時
-
googleのスプレッドシート
-
エクセル IF計算式?でしょうか?
-
【マクロ】for nest について ...
-
エクセル折れ線グラフについて...
-
EXCELで数値が異なった数字を足...
-
Excelで数値を時間数に変換する...
-
Excelについて
-
エクセルでファイルの最終更新...
-
エクセルを使用して、円周率を...
-
エクセルの数式バーのフォント...
-
【マクロ】VLOOKUPにて参照元に...
-
Excel 対象のセルに入力が無い...
-
エクセルでセルに標準で入力さ...
-
EXCELの散布図で日付が1900年に...
-
Excelで表を作ったところに文字...
-
【マクロ】名前を保存する際に...
-
Excelについて。Excelに縦1列に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
2列に入っているデータを1列...
-
データチェックを行うエクセル...
-
再質問です。マクロの修正箇所...
-
エクセルで 自動的に◯や数字を...
-
数字入力後他の文字等が表示さ...
-
Excel 小さくなったスクロール...
-
F9キーについて。
-
【ExcelVBA】ダブルクォーテー...
-
Excelに詳しい方! B列が「日...
-
セルの数を求めたい
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
【Excel】別シートから条件に合...
-
EXCELの散布図で日付が1900年に...
-
ある表にフィルターをかけて出...
-
【ExcelVBA】名前を付けて保存→...
-
Excelセルを跨いで合計を出す方法
-
エクセルのツールバーから数値...
おすすめ情報