エクセルで「32.7*1500」の答えが・・・

質問

エクセルの計算について、質問させていただきます。
「32.7*1500=49050」になると思いますが、この計算結果の「49050」と入力した「49050」を差し引きして「100000000」を掛けると、結果が「0.000727596」となり「0」になりません。
理解に苦しむ状況ですが、なにか解決する方法はあるでしょうか。

回答No.1

t_ohta · ベストアンサー

=(INT(32.7*1500)-49050)*100000000

コンピュータは小数点のある数値の計算が苦手です。
32.7×1500をした時点で微妙な誤差が隠れていて、それを1億倍したときに顕在化してきたのです。
基本的には小数点のある数値を計算したときは、有効桁で切って見えない誤差を消す事で対処します。

回答No.6

WindFaller

こんにちは。

最近は、あまり話題に登りませんので、忘れている人もいるようですから、もう少し話を延長して書かせてもらいます。

現代のCPUでしたら、何も2進で計算する必要はないのです。電卓は、10進で計算しています。例えば、ジャストシステムの「三四郎」は、10進演算が可能です。（とはいえ、標準的な演算ではありません)

http://support.justsystems.com/faq/1032/app/servlet/qadoc?QID=024464

この問題は、例えば、Excelのワークシートに、こんなことをしてみると分かるはずです。

サンプル（暇な人は試してみてください)
A1:0　　B1:0.1　　C1:　=B1-A1　D1: =IF(C1=0.1,"○","×")
A2:0.1　B2:0.2　　C2:　=B1-A1　D2: =IF(C1=0.1,"○","×")
　　　．
　　　．
　　　．
A100:9.9　B100:10　C100: =B1-A1　D100:=IF(C1=0.1,"○","×")

後は、オートフィルで100 行目まで、コピーします。
そうすると、○が、32個出てきます。

つまり、約3分の2が、正確な計算はしていないということになります。
（これは、表示上は、0.1と出ていますから、出てきた答えを更に計算させる、二次計算には用いることは出来ない、ということです)これは、欠陥製品だという人もいます。

そこで対策としては、前回の書き込みと一部重複しますが、MSでは、以下のように示されています。

・関数による修正する（MSの指導では　ROUND関数が薦められている）
・整数にして計算する　(#5さんの方法)－「整数変換法」
・エクセルのオプション設定による方法（詳細設定の中の「表示桁数で計算する」をONにする、（非推奨)

もう一つ、「固定小数点法」を使ってよいのですが、MS側の表計算のサポートには出てきません。
私自身は、VB6のガイドで読みました。
上記のサンプルの問題で「固定小数点法」は、
　=FIXED(B1-A1,1)*1　または、=VALUE(FIXED(B1-A1,1))
このようにします。

関数自体も、「浮動小数点型丸め誤差」が発生します。
　MOD ,CEILING, FLOOR, EVEN, ODD MROUND　など。
　(INTも実は丸めをしていますから負数の時に発生します)

ワークシートの場合は、ある程度の補正が働いてはいますが、このようなソフトウェアでは、銀行など金利計算では、よほど自信がなければ、なかなかできないと思います。

http://okwave.jp/qa/q8823328.html
こちらの#3は、VBAの考え方です。

補正は、
　=1.1-1-0.1
この計算では、完全に「0」になっています。Excelのあるバージョンから補正されたようです。

例えば、会社や学校で、ランクわけで、選別する時などに、パーセンテージなどを使いますが、その境目の、微妙なところで、当落が分かれてしまうかもしれません。気をつけるに越したことはありません。

なお、Excelには、これ以外の丸め誤差が存在しています。

回答No.5

tom04

こんばんは！
すでに「浮動小数誤差」の回答は出ていますので、余計なお世話かもしれませんが・・・

人間社会では10進数が普通になっていますが、PC内では2進数で計算しています。
そうなると、10進数で小数点以下の数値の場合は値によっては「浮動小数誤差」が生じてしまいます。

少々面倒ですが、単純に整数で計算した値を出し、最後に桁合わせのために10の乗数で割る方法もあります。

今回の場合だと
=32.7*10*1500/10
としてみてください.
（乗算の小数点以下の桁数分だけ10の乗数で割ります）
厳密に書けば
=(32.7*10)*1500/10　という感じです。

=32.7*1500*10/10
としては何の意味もありません。

こうすればおそらく浮動小数誤差は出にくいと思います。m(_ _)m

回答No.4

WindFaller

こんにちは。

最初に、この問題の解決方法としては、3つの方法があります。一部では、VBAを用いた方法を示しています。Microsoft の古い教科書（ガイド)では、
　切り捨て・丸めによる修正　←正式な名前は知りません。
「固定小数点法」
「整数変換法」
がありますが、現在のExcel上では、INTやROUND, ROUNDDOWN, TRUNC, FIXED 関数などが用いられます。この中で、もっとも古いスタイルのものが、FIXED関数で、固定小数点法に使用されます。

A1 に、「32.7*1500=49050」と入力して

=FIXED(A1-49050)*100000000

私個人は、INT関数をお勧めしないのは、INT関数自体が、正数の小数に対して丸めているからです。

=INT(49050-A1)*100000000
は、0となりません。

現在、実務では、TRUNC関数が、その代わりに用いられます。
=TRUNC(49050-A1)*100000000

この問題を称して、「浮動小数点演算丸め誤差」と言います。
この話をすると長くなりますが、以下のような説明が出ています。

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

PC-VAN（現Biglobe)の構想を作った、三重大学 奥村晴彦教授はここで、その仕組を書いています。
http://oku.edu.mie-u.ac.jp/~okumura/software/excel/roundoff.html

“達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座
http://pc.nikkeibp.co.jp/pc21/special/gosa/eg4.shtml
（ただし、こちらの方は、記述ミスがあります。)

>理解に苦しむ状況
以前、ネットでは猫も杓子も、この問題でもちきりでした。先日、ここで回答を書かせていただいたけれども、今は、誰も、その問題を考えないようです。基本的には、小数点による演算すべてに誤差が発生します。

理屈は、確かに浮動小数点型の数値を使うということが原因です。Binary だからなのですが、"Excel"だから仕方がないというしかありません。現代の技術を持ってすれば、10進に変えることは造作もないはずなのです。一時期、各社が、この補正措置案を考えたそうです。各社各様のノウハウはあるものの、ロータス社とマイクロソフト社、共同で、この問題を発表したのは、今から、20年以上前の話です。しかし、その時は、このような問題とは、質が異なっていました。今よりもずっと精度が高かったのです。

三重大の奥村教授がおっしゃっているように、"Excel"特有の問題のようです。決して、IEEE規格の責任というわけにはいかないと思うのです。ここら辺のノウハウに、パテントが絡んでいるから、私は、このような問題が発生すると考えています。

回答No.3

MackyNo1

ご質問の状況は、計算機（2進数）で小数点以下の数字を扱う時の丸め誤差によるものです。

すなわち、32.7のような2のｎ乗の和で表せない数字は、演算するときに丸め誤差が発生します。
たとえば、１０進法の場合、0.1や.0.2のような数字は丸め誤差が出ませんが、1/３が0.3333.．となり、演算時に有効数字桁数で切り捨てなければならない（1/3*3が0.99999になる）のと同じことです。
一方、2進数では0.5や0.75のような2進数で表せる数字では丸め誤差が発生しませんが、0.1や0.2などの数字は丸め誤差が発生することになるわけです。

今回のケースでは32.7*1500の結果が、丸め誤差の影響で正確には49050そのものと等しくないため、引き算すると誤差が顕在化することになります。
同様の丸め誤差は時間や時刻のセルをオートフィルして連続データを作成するときにも発生しますので注意が必要です（オートフィルして入力した時間と手入力の時間が等しくない）。

このような丸め誤差を避けて計算するには、単純な数値の場合は、十分な桁数を確保したROUND関数で処理するのが確実です（INT関数やROUNDDOWN関数では対応できません）。

=(ROUND(32.7*1500,10))

回答No.2

Chiquilin

実際にはどういう計算をしましたか？　計算式をそのまま提示して下さい。
演算誤差が発生しています。

その「32.7」は手で入力したものですか？