[[20080214174140]]　『正しく０と表示させる方法はありますか？』( kentackie ）　ページの最後に飛ぶ

『正しく０と表示させる方法はありますか？』( kentackie ）

　Excel2000, WindowsXP

 =SUM(Q5:Q26) のように関数を入れて計算させたところ、合計値が０になる場合だけ、 5.66214E-15 と表示されます。
 （Ｑ5：Ｑ26の枠内には、他に関数を入れ数値を表示するようにしています）
 正しく０と表示させる方法はありますか？

 5.66214E-15
 ↑この数値はsumで計算した数式内の数値ですね？
 このセルをコピーして、どこか他のセルに「値」に変換して、数式バーを見てください。
 数式バーも 5.66214E-15 になってますか？
 もしなってたら、[ツール] メニューの [オプション]
 →[計算方法]タブの[表示桁数で計算する] にチェック　OK。
 これで数式バーに
 0.00000000000000566
 と表示されると思います。

 たぶん
 計算結果が 0 では無く演算誤差が出てると思います。
 誤差を関数などで 0 にする、等が考えられます。

 [Excel で浮動小数点演算の結果が正しくない場合がある]
http://support.microsoft.com/kb/78113/ja

 なお、上記の[表示桁数で計算する] にチェックで
 「データの正確さが失われます。元に戻せません」のメッセージが出ると思います。
 データのバックアップをとってから試してください。 　　　（jun53）

 Ｈｅｌｐを、ありがとうございます☆

 ●はい。ＳＵＭ関数を入れてあるセル内の表示です。
合計値が 0 以外になるようにデータを変えて見ると、0.01 -0.03 のように正しく表示されます。

   >5.66214E-15 
   >↑この数値はsumで計算した数式内の数値ですね？

 ●Ｑ列以外のセルに、例えばＯ列にコピーした場合、数式バー内は、=SUM(O5:O26)となり、
コピーしたセル内の表示は 0 になります（実際の O5:O26 内のセルは空白です）。
然し、コピーしたＯ列のセル内の関数 =SUM(O5:O26)を、手入力して、
コピー元と同じ=SUM(Q5:Q26)に訂正すると、また可笑しな表示 1.21014E-14 になりました。

 =SUM(Q5:Q26)の、 Q5:Q26 の各セル内には、
他の関数で出した数値 [0が11個] [0.9が１個] [-0.3が3個]が表示されていますが、
手計算したところ、Q5:Q26 内の答えは合っています。なので、合計は 0 で間違いありません。 
やはり、合計値が 0 の時だけ、変な表示になります。

   >このセルをコピーして、どこか他のセルに「値」に変換して、数式バーを見てください。
   >数式バーも 5.66214E-15 になってますか？
   >もしなってたら、[ツール] メニューの [オプション]
   >→[計算方法]タブの[表示桁数で計算する] にチェック　OK。
   >これで数式バーに
   >0.00000000000000566
   >と表示されると思います。

   >たぶん
   >計算結果が 0 では無く演算誤差が出てると思います。
   >誤差を関数などで 0 にする、等が考えられます。

 １）セルの表示形式を教えて下さい（標準とかxx桁の数値とか、なるべく詳しく）

 ２）合計値が 0 で変な表示になるセルに直接 0 を手入力してみて下さい。
     これは「元に戻す」の操作で簡単に元に戻ります。0手入力でも変な表示になりますか？

 ３）>他の関数で出した数値、
     ↑これは、SUM以外の関数も使ってる、のですね？
    可能なら、=SUM(Q5:Q26) の Q5:Q26 のデータ（数値?）と
    「他の関数」をどのように使ってるのか？を教えて下さい。
    例えば、Q5:Q26 のデータが端数のない数値なのか、
    計算式は、=SUM(Q5:Q26)/COUNT(Q5:Q26) とか。

 通常、数値での0が 1.21014E-14 等に表示されるのは考えにくいのですが。
 考えられる可能性として、シートあるいはブックがおかしくなってる。
 　これは新規ブックで同様作業をしてみると確認出来ますね。
 もう一つは、元データに端数が有る、計算方法、使用する関数で誤差が発生してる。
 　関数によっては誤差や精度が落ちるものが有るようです。

 ほかにも原因がありそうですが、私の思考が固まってるのか思いつきません。
 別角度からの回答もお待ち下さい。 　　　（jun53）

お世話になっております

1）セルの表示形式を何度か調節してみても、変わらないので、現在は標準にしています。

2）手入力では０と表示されました。

3）★ =SUM(Q5:Q26)のセル内には、このような関数を入れております。
　　　=IF($L14="Ｂ",IF($H14>$Q$4,-$Q$4,-G14),0)

　　　式の中で参照している G14,H14,のセルには、=F10-C10 のような引き算を、

  　  L14,のセルには、=IF(D13>J12,"Ｂ",IF(E13<K12,"Ｓ","-"))の式をいれています。

　　　$Q$4 のところは、セルに手入力した係数を変化させて使います。

   ★ 新しいＢＯＯＫにコピーしても同じに再現されます。

　 ★ 元データは、190.02 のような、小数点２桁までの数値になっております。

　　　　　　　　　　　　　　　　　　　　　　宜しくお願い致します。

 複雑な計算方法をされてるのですね。

 >手計算したところ、Q5:Q26 内の答えは合っています。なので、合計は 0 で間違いありません。
 前回このように書かれてますが、

 > 2）手入力では０と表示されました。 
 本当の0なら「０と表示される」ので、
 やはり私には、 どこかのセルに演算誤差が発生してるのだと思います。
 前回も提案しましたが言葉が足りなかったようなので、もう一度書きますね。

 簡単な計算で
=190.03-190.02
 表示形式「標準」で、答えが 0.01 ですね
 このセルをコピーして、どこか他のセルに、右クリックから「形式を選択して貼り付け」から「値」にチェックして、OK
 数式バーには 0.00999999999999091 が表示されます。
 コンピュータ内部ではこのように計算してるようです。
 また、このセルの表示形式を「ユーザー定義」で
 0.00000000000000000
 などと、小数点以下の桁を増やします。例は17桁ですが、30桁まで設定出来るようですよ。

 =F10-C10 や Q5:Q26 の中でこのような事が発生し、それを SUM(Q5:Q26) してる訳ですね。
 =F10-C10 や Q5:Q26 や SUM(Q5:Q26) も、コピーして「値」に変換して確認して下さい。

 対策の一つとして、どこかの数式の段階で、
 ROUND や TRUNC などで差し支えない程度の桁数処理をする。
 桁数処理で、今までとは違った結果になるかもしれません。

 小数計算で発生する「誤差」
http://pc.nikkeibp.co.jp/pc21/special/gosa/eg1.shtml

 あと、私の返信はモタモタしてます、返信まで時間がかかります。
 もしお急ぎのようなら、「どなたかわかる方、Ｈｅｌｐをお願いします」
 とでも書き添えて下さい。
 たくさんの方が返信してくれると思います。 　　　（jun53）

jun53さん　どうもありがとうございました＼(*^ー^*)／

エクセル自体の計算方法が理解で来たので、=ROUND(SUM(Q5:Q26),2)と関数を訂正し、０が表示されるようになりました。(*^。^ゞ

コメント返信：

[ 一覧(最新更新順) ]

[[20080214174140]] 『正しく０と表示させる方法はありますか？』( kentackie ） ページの最後に飛ぶ

[[20080214174140]]　『正しく０と表示させる方法はありますか？』( kentackie ）　ページの最後に飛ぶ