Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2010)
「7.27596E-12」と表示される
投稿日時: 18/12/30 21:51:38
投稿者: ko-porasu

いつもお世話になります。
 
Excelの小数点の加減算について、ご相談となります。
 
以下のデータ(※)をピボットテーブルを使って集計すると
セルは「7.27596E-12」(数式バーは「7.27595761418343E-12」)と表示されます。
 
※通貨USDの請求書を科目別に分けています。
また、「プラス」を借方、「マイナス」を貸方としております。
そのため、合計はゼロとなります。
 
-30089.14
-279.72
217.29
144.99
536.94
409.65
5453.1
1086.16
268.36
63.08
2211.02
19978.27
 
電卓で計算すると、ゼロになるのですが、上記のような結果が表示されます。
表示形式を「標準」から「通貨」にすれば、見た目(セル表記)は直るのですが、
数式バーは変わりません。
 
色々調べてみると、以下のサイトにたどりつきました。
https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
 
ただ、確証が持てず、また納得もできないため、ご相談させていただきました。
 
理由や解決方法等、ご存知の方がいらっしゃいましたら、ご回答いただけると幸いです。
恐れ入りますが、何卒よろしくお願いいたします。

回答
投稿日時: 18/12/30 22:22:28
投稿者: WinArrow
投稿者のウェブサイトに移動

コンピュータは、全て2進数で計算されます。
要するに、見た目と、コンピュータの数値が異なることがあるということです。
 
小数点以下の数値を取り扱う場合、小数点以下の桁数を指定することで解決します。
例示の場合だと、小数点以下の桁数が2桁なので、通貨型にすると、意図した結果が得られる
ということなので、その証といえるでしょう。
 
↓参考までに、通貨型のデータの扱われ方
 
通貨型フィールドは、計算時に丸めによる誤差が生じないようにする場合に使用します。
通貨フィールドでは、整数部分は 15 桁まで、小数部分は 4 桁までの精度が保証されます。、

回答
投稿日時: 18/12/30 23:07:01
投稿者: WinArrow
投稿者のウェブサイトに移動

>表示形式を「標準」から「通貨」にすれば、見た目(セル表記)は直るのですが
この操作は、合計だけ「通貨型」に変更したのでしょう。
 
元数値を「通貨型」もしくは、「数値型の小数点2桁」に設定する必要があります。

回答
投稿日時: 18/12/30 23:12:00
投稿者: WinArrow
投稿者のウェブサイトに移動

連続レス
 
7.27595761418343E-12
という数値を
どこか任意のセルに入力し
表示形式を「数値型」少数点以下の桁数を15位に設定してみてください。
小数点以下の誤差値が分かります。

回答
投稿日時: 18/12/30 23:12:34
投稿者: Mike

ko-porasu さんの引用:
以下のデータ(※)をピボットテーブルを使って集計すると
セルは「7.27596E-12」(数式バーは「7.27595761418343E-12」)と表示されます。
参考までに、どういうレイアウトのテーブルですか?

回答
投稿日時: 18/12/31 10:59:05
投稿者: んなっと

 フィールドの設定
→表示形式
→通貨 小数点以下の桁数:2
 
としたのですよね。それでいいと思います。
 
 
※どうしても数式バーに3.63797880709171E-12とか
7.27595761418343E-12と演算誤差が表示されるのが気になるなら...
 
 フィールド/アイテム/セット
→集計フィールド
→数式:
=ROUND(金額,2)
→OK
 
として、合計の代わりに集計フィールドを使いましょう。

回答
投稿日時: 18/12/31 11:22:39
投稿者: んなっと

データ次第では、念のため
=ROUND(金額,3)
などと増やした方がいいかもしれません。

投稿日時: 18/12/31 18:18:33
投稿者: ko-porasu

投稿者: WinArrow様
 
>>コンピュータは、全て2進数で計算されます。
>>要するに、見た目と、コンピュータの数値が異なることがあるということです。
 
  今まであまり小数点以下の値を使うことがなかったので、
  正直、知りませんでした。
  二進数で、十進数を表そうとする弊害なのですね。
  非常に勉強になりました。ありがとうございました。
 
 
投稿者: んなっと様
 
>> フィールド/アイテム/セット
>>→集計フィールド
>>→数式:
>>=ROUND(金額,2)
>>→OK
 
  こちらで解決しました。ありがとうございました。
  ピボットテーブルでこんな機能あるのですね。
  知りませんでした。。。勉強します。
 
 
年末のお忙しいところ、お二方ともありがとうございました。