訂正 エクセルでは「0.14*100-15」は「-１」でないのは何故？

質問

すみません。あちこち間違っていたので再投稿させて下さい。

セルにと「0.14*100-15」入力すれば「-1」と返してくれますよね。
で、rounddown(-1,0)　は「-1」となるのですが
rounddown(0.14*100-15,0)は「0」となります。

何故だろう？と思い試しに「0.14*100-15」コピーし
「形式を選択」→「値」で別のセルに貼りつけたら「-１」と見えますが数式ボックスの値は「-0.9999999998」となっています。

原因は分かったのですがrounddown(0.14*100-15,0)を本来の値通り
「-１」とすることは出来ないのでしょうか。
つまりちゃんと「0.14*100-15」を「-１」と計算してくれるように
できないでしょうか。
（ちなみに「0.16*100-15」は「１」となります）

よろしくお願いします。

回答No.9

Wendy02

こんばんは。
マクロで実験してみました。

A1:0.01 ～1 までで、A2:1～1000　までの間の組み合わせで、
-------------------------------------------
=(A1*100*10-A2*10)/10　では、124種類の誤差が発生
=(A1*100*100-A2*100)/100では、96種類の誤差が発生
=(A1*100*1000-A2*1000)/1000では、88種類の誤差が発生
=(A1*100*10000-A2*10000)/10000　では、124種類の誤差が発生

A1:0.01 ～1 までで、A2:1～1000　までの間の組み合わせで、
=(A1*1000*100-A2*1000)/1000　は、誤差は出ていませんが、

A1:0.01 ～10 までで、A2:1～1000　までの間の組み合わせで、
2640種の誤差が発生　（例：A1:2.01, A2:191 ～210)
-------------------------------------------
A1:0.01 ～1 までで、A2:1～1000　までの間の同じ組み合わせで、

=FIXED(A1*100)-A2
=ROUND(A1*100,6)-A2
=ROUND(A1*100,0)-A2

これらでは、誤差は発生していません。
また、足し算では、発生していません。
=A1*100+A2

回答No.8

Wendy02

こんにちは。

#5の回答者です。

正直なところ、私にも良く分かっていないのです。分かったようなことを言っても、私も、質問者様と同様です。難しい理屈やそのつど違う数式を書いても、とても、その都度、対処できないと思っています。今回は、バグに近いのではないでしょうか？それは、0.14にしか発生しないからです。

-------------------------------------------
A1:0.01 ～10.00
=(A1*100)-15
0.01 ～10.00までの間を調べましたが、0.14 だけにしか発生していません。

前回書いたように、
-15　を変数とした場合は、1～10,000の間で、13,15,16 しか発生していません。

補正処理されていないままでしたら、0.01～1 までなら、
0.07, 0.14, 0.28, 0.29, 0.55, 0.56, 0.57, 0.58
8 個なるはずです。これが、0.01 ～10 までなら、135個出てきます。
15の部分を、1～100に変更すると、1～30までは、誤差が出ています。
-------------------------------------------
#6様の数式
(A1*1000*100-15*1000)/1000
これは、OKですが、
-----------------------------------
×
=(A1*100*100-15*100)/100
=(A1*100*10-15*10)/10

100倍や10倍では、上手くいかないということですね。最近接偶数丸めとかいう単語を出したところで、それでは納得いきませんね。
-----------------------------------
しかし、

○
=(A1*10*100-15*10)/10
100と10を入れ替えただけで、なぜか、直ります。
ところが、
=(A1*100*100-15*100)/100
ではダメで、1000 なら、OKです。
----------------------------------
他にも、少し状況が違いますが、このような例があります。

ROUNDUP を使った場合
0.375111　　=ROUNDUP(A1,6)　...0.375112 (当たり前だ思います）

0.374111　　=ROUNDUP(A1,6)　...0.375111 (これも良く分かりません）

5 を 4に変えると、切り上げされません。
-------------------------------------------
[これらは、バージョンによって異なる可能性があります。]

原則としては、
=========================================
・Excelのセルに入れた「小数点＋引き算や割り算」を演算すると、確実に誤差が発生する。
・小数点そのものの何割かには誤差が発生している。
・整数と整数の演算には誤差は発生しない。
=========================================

ことだと思います。これ自体は、変わらないはすです。だから、「演算誤差」と「演算」すると発生するというのは間違っているかもしれません。

>数式ボックスの値が「-0.99999１9999999998」であって
小数点にこのような数値は存在しませんが、循環小数になっていますから、確かに、丸める桁数によって変わる可能性はあります。

日経BP21の芳坂さんは、ユーザー定義関数で、10進のCurrency型で計算することを勧めているようですが、それでは、ワークシートの計算自体では不可能だということになってしまいます。

http://pc.nikkeibp.co.jp/pc21/special/gosa/
このような長い説明は不要ですし、対処法としても、もう少しまとめる必要がありそうです。

「丸める時の桁数で、本来の値と変わる可能性」については、

私も気になります。上記の例もあります。

それはあると思います。いちいち、桁数で対応していたら、対応出来ません。それは、時間の計算も同じです。たぶん、ご質問者さんは、場当たり的な計算に疑問を持っておられるのだと思います。

そこで私は、求める桁数の一つ手前の桁数で丸めればよいのではないか、と考えています。
これは、VBAでのプログラミングでも同様に、問題が発生していません。

「0.14 * 100 - 15」　-->　ROUND(0.14*100, 0) - 15
なぜ、IBMでは、

=TRUNC(ROUND(0.14*100, 6) - 15)

とするのか、本当は良くわかっていません。

一応、私の考えた理屈は、15 が、整数型だから、ROUND(0.14*100, 1)は、こちらも整数型にすれば誤差が発生しないと思いました。

他の方法としては、

=(FIXED(A1*100))-15

こういう方法は「小数点固定法」といます。FIXED　というのは、本来の誤差丸めの専用関数のようですが、詳しい説明は、ワークシート側の関数には出ていないと思います。

回答No.7

shinh

その上手く行かない　任意の値とは　具体的に　どんな値なのですか？

回答No.6

shinh

もう一つ良いのが浮かびました。
意図的に　1000倍し　少数での計算を避けるという方法です。
A1：　0.14
B1：=　(A1*1000*100-15*1000)/1000
C1：=　ROUNDDOWN( B1,　0　)

お礼

何度もご回答を頂戴し誠にありがとうございます。

この1000倍するというのは0.14だから1000倍する、と言うことでしょうか。
もしそうだとしますと、任意の値に対応出来ないことになりませんか。
どうでしょうか。

回答No.5

Wendy02

こんにちは。

原因は、#2さんが少し触れているようですが、原因は、倍精度浮動小数点型の数字なのです。(IEEE754 形式)

http://oku.edu.mie-u.ac.jp/~okumura/software/excel/roundoff.html
Texで有名な奥村晴彦先生が、ここで触れられています。

私自身、Microsoft 側の内部の処理に関しては、あまり詳しくはありませんが、単に、倍精度浮動小数点型の数字をそのまま使っているわけではなく、補正処理されているようです。かといって、他社製、または、旧Microsoft 表計算ソフトと比較しても、Excelは、この問題は顕著のようです。

その対処法自体は、いくつかあるようですが、少なくとも、0.14 * 100 で、整数になっていると思ったのですが、なっていないわけですね。

0.14 は、2進で、0.0010001111010111
循環小数になっていますから、正確には出ていません。だから、整数部の計算を、このように丸めればよいと思うのです。

= ROUND(0.14 * 100, 0)

他に、丸める関数としては、TRUNC, FIXED 関数があります。INTを使わないのは、マイナスが入ると丸め方が変わるからです。

>常に正しい値が返されることを知りたかったのでした。
0～10,000　までの間では、13,15,16 のみに、誤差が出ています。ワークシートでは、ある程度の補正処理はされていますが、その兼ね合いがはっきりしません。

例えば、
= ROUND(0.14 * 100, 0) - 15

このように計算すれば、問題は発生していません。
もちろん、これは、整数計算する目的ですから、求める精度によって、ROUNDの桁の部分は、2でも3でも良いと思います。

参考までに、IBM Notesの対処法をみると、こんなスタイルになるようですが……。むろん、OOo やLotus Symphony の表計算では、すでに誤差はでないようになっています。ただし、オリジナルは、TRUNC の代わりにINTになっています。

=TRUNC(ROUND(0.14*100,6)-15))

6桁にするのは、単精度(Single　32bit)の浮動小数点の仮数部が、23bitで、指数部が8bit、符号に、1bit を使用しているので、保持できる桁数は 2 の 23乗になり、それは、10進で6桁になりますので、6桁程度で良いということだそうです。

お礼

専門的な内容まで含めありがとうございます。

ところで調べましたら
「0.14*100」は「１４」となるのですが
「0.14*100-15」とすると「-0.999999999999998」
となります。何なんでしょうか。

それはともかく、ご教示頂いたように一度丸めることで
なんとか対応は出来そうです。

ただ、おそらく問題はないと思うのですが
なんらかの計算をして表示上は（正しい計算では）「－１」
(仮にA1とします）だが
数式ボックスの値が「-0.99999１9999999998」であって
round関数で丸める桁数を偶然にも「５」としてしまい
rounddown(A1,5)の値が「０」となったら怖いなあと思います。
つまり丸める時の桁数で、本来の値と変わる可能性を
今の私にとっては否定出来ない怖さです。

計算の結果が実はどの様になっているのか
一つづつ確かめる訳にはいきませんからね。

回答No.4

shinh

では、ちょっと修正して　下記で　どうでしょう。
例
A1：　0.14
B1：=　A1*100-15
C1：=　ROUNDDOWN( IF( B1 < 0, 　B1-0.00000000000001,　B1),　0　)

回答No.3

noname#204879

「原因は分かった」のなら、実用的な対策を知りたいと？

私の常套手段は“必要かつ充分に小さい桁で四捨五入する”です。
「必要かつ充分に小さい」という意味は小さ過ぎても駄目ということです。Excel が処理できる有効桁は最大で 15 であるからです。
「0.14*100-15」は小数点以下２桁を取り扱っています。従って、この場合は小数点以下５桁くらいが「必要かつ充分に小さい」目安になります。
つまり、=ROUND(0.14*100-15,5) とすれば好い。

=rounddown(0.14*100-15,0) も次のようにするのです。
=ROUNDDOWN(ROUND(0.14*100-15,5),0)

補足

ご回答ありがとうございます。
参考になりました。

ところで、私の質問内容に不足がありました。
今回は「0.14*100-15」の値のみを問題としましたが
「0.14」という個別の値だけでなく「0.14」意外の値でも
例えばrounddown(A1*100-15,0)でA1の値がなんであっても
常に正しい値が返されることを知りたかったのでした。

せっかくご回答頂いたのにすみません。

回答No.2

hoshitama5

エクセルの数字は内部的に１０進法でなく２進法で計算するので、負の数とか、小数点以下の小さい桁で誤差がでることがあります。（と記憶してます）

一旦テキストにすれば、自動的に内部で持てる最小桁で丸めますので、それを再び数値化すれば誤差がなくなります。

=rounddown(value(text(0.14*100-15,0)),0)

とすれば、「-1」になります。

補足

ご回答ありがとうございます。
参考になりました。

ところで、私の質問内容に不足がありました。
今回は「0.14*100-15」の値のみを問題としましたが
「0.14」という個別の値だけでなく「0.14」意外の値でも
例えばrounddown(A1*100-15,0)でA1の値がなんであっても
常に正しい値が返されることを知りたかったのでした。

実際「0.141」とするとtext(0.141*100-15)は「－１」が
出力され（この場合(0.141*100-15)=-0.9 ですよね）、rounddown(0.141*100-15,0)も「－１」となり
本来の正しい値「０」と出力されません。

なにか良い知恵はないでしょうか。

再度お願いします。

せっかくご回答頂いたのにすみません。

回答No.1

shinh

浮動小数点として扱うためによる誤差のためですね。
対処療法的ですが　IF　で　分けて　0.5を加算したり減算したりして調節しては
例
A1：　0.14
B1：=　A1*100-15
C1：=　ROUNDDOWN( IF( B1 < 0, 　B1-0.5,　B1+0.5　),　0　)

補足

ご回答ありがとうございます。

ところで、私の問題提起の内容に不足がありました。
今回は「0.14*100-15」の値のみを問題としましたが
実は、上記にならうならば
A1:= 0.14
B1:= A1*100-15
C1:= rounddonw(B1,0)
で、A1の値が変わっても常にc1に正しい値が返される、という
ことを知りたかったのでした。

つまり、A2以下にも値が入力されており
B2以下、C2以下にコピーして使用したいと言うことです。

せっかくご回答頂いたのにすみません。