CEILING.MATH関数とROUNDUP関数の結果が異なる

Excel関数の計算結果が意図したものと異なるため、その原因を探っています。

次の様な数式の時に最終の計算結果が異なります。

[ セル = 数式 = 計算結果 ]

A1 =CEILING.MATH(36.8,0.1)*10 =368

B1 =(A1-INT(A1)) =5.6843418860808E-14

C1 =A1-INT(A1) =0

A2 =ROUNDUP(36.8,1)*10 =368

B2 =(A2-INT(A2)) =0

一見すると上記 B1、C1、B2はいずれもゼロになるように思いますが、

なぜかB1のみゼロとなりません。

則ちCEILING.MATH関数の計算結果が一部の状況で異常値になるという事です。

(なお「CEILING関数」と書き換えても同様でした)

そこで「数式の検証」機能で確認したところ、B1が次のように展開されました。

=(A1-INT(A1))

=(368-INT(A1))

=(368-INT(368))

=(368-368)

=(5.6843418860808E-14)

その他、いくつか試してみると(書式を数値とし、小数点以下を14桁以上表示)次の様になります。

=A1=368.0000000000000000000000000

=(A1)=368.0000000000000000000000000

=INT(A1)=368.0000000000000000000000000

=(INT(A1))=368.0000000000000000000000000

=(-INT(A1))=-368.0000000000000000000000000

=A1-A1=0.0000000000000000000000000

=(A1-A1)=0.0000000000000000000000000

=B1=0.0000000000000568434

=(INT(A1)-A1)=-0.0000000000000568434

=(INT(A1)+A1)=736.0000000000000000000

また、A1セルの「36.8」という数字を36.7や36.9にしたり、=CEILING.MATH(368,1)とすると、上記の様な現象は発生しません。

まとめると、以下の要件の時のみ現象を確認しています。

1.CEILING.MATH関数で特定の数値を処理

2.「1」とINT関数で処理した「1」とで減算

3.「2」をカッコ内で行う

状況は以上です。

原因分かりますでしょうか。よろしくお願いします。

** モデレーター注 **
この質問は [Office | Excel | Windows 10 | Office 365 for business] のカテゴリーに投稿されましたが、内容から判断し、こちらのカテゴリに移動いたしました。適切なカテゴリーに投稿すると、返信や回答が得られやすくなり、同じ質問を持つ他のユーザーの参考にもなります。

|
私にはよく分からないのですが、下記ページが参考になるのでは?

https://support.microsoft.com/ja-jp/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel
「Excel で浮動小数点演算の結果が正しくない場合がある」 
https://support.microsoft.com/ja-jp/help/214118/how-to-correct-rounding-errors-in-floating-point-arithmetic
「浮動小数点演算における丸め誤差を修正する方法」

この回答が役に立ちましたか?

はい
いいえ

役に立ちませんでした。

素晴らしい!フィードバックをありがとうございました。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。

リンク先を拝見しました。

=(0.5-0.4-0.1) ←こんな単純な計算でも規格上の問題が出るとは驚きました。

小数の扱いは難しいですね。プログラムでもわざわざ「通貨型」なんてデータ型を用意するわけだ。

私も浮動小数点の問題は気になっていたのですが、

  • 単純に理解できない
  • やはりカッコの有無で計算結果が変わるのはなぜ?
  • 浮動小数点演算は数値自体(格納されたバイナリ値)の問題なのに、上記の様に関数を噛ませた時だけ再現する(*)のはなぜ?
  • 前述のA1セルとA2セルはバイナリ値が異なるということ?値はどちらも「3.68.E+02」(368)なのに?

ということで分からなさに拍車がかかっています。

* 前述のA1を「=36.8*10」とすると再現しません。

ただ一つ分かったのは、ROUND系の関数を使えと公式でも謳っているのですね。

ということはCEILING(FLOOR)関数とROUND系関数は出力は類似しますが、

どうも内部の処理は異なり、指定した精度が出るのはROUND系関数、と理解しています。

この回答が役に立ちましたか?

はい
いいえ

役に立ちませんでした。

素晴らしい!フィードバックをありがとうございました。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。

私には難しくて回答出来なくて申し訳ありませんが、、、

>やはりカッコの有無で計算結果が変わるのはなぜ?
カッコの有無ではありませんが、こんなのを見つけました。
http://gihyo.jp/dev/serial/01/java-calculation/0012?page=2
ここに、こんなのがありました。

エクセルで試したら下図のようになりました。

「前者の式では絶対値の等しい数値を減算することでゼロとなります。
 これに1を加えているので結果は1です。

 しかし後者の式はそうなりませんでした。
 浮動小数点数は加算を行う場合,ふたつの数値の指数部を指数の大きい方にそろえます。
 f3は,f2に指数部をそろえると,仮数部の値は小数点以下に19もゼロが続いた先にやっと1が現れます。
 float型の仮数部は10進数にして8桁程度しかありません。
 このため,1という数値は1×10^20との演算の際に仮数部に表現可能な桁数から欠落し,
 0としか表現できなくなってしまうのです。
 プログラム中の計算式は基本的に左から右へ計算されます。
 数学的には同じ式でも,加減算の順に注意しないと,
 今回の問題のように望まない結果となってしまいますので注意が必要です。」

と理由があり、私には難解でしたが、加減算の順序も大事だとは分かりました。

この回答が役に立ちましたか?

はい
いいえ

役に立ちませんでした。

素晴らしい!フィードバックをありがとうございました。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。

>•やはりカッコの有無で計算結果が変わるのはなぜ?

私も気になってネットで検索したらこんな記事を見つけました。
これに該当するのかどうか分かりませんが、、、

http://7shino.blogspot.jp/2016/04/excel-2013_66.html
「Excel 2013で数式のカッコの有無で計算結果が変わる例 」

http://oku.edu.mie-u.ac.jp/~okumura/software/excel/roundoff.html
「Excelの演算誤差」

(補足)
マクロの場合は括弧の有無に関係なく演算誤差がでました。

Sub 括弧有無test()
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
    Dim e As Double
    a = 0.5
    b = 0.4
    c = 0.1
    d = a - b - c
    e = (a - b - c)
    MsgBox "0.5-0.4-0.1= " & d
    MsgBox "(a - b - c)= " & e
End Sub

この回答が役に立ちましたか?

はい
いいえ

役に立ちませんでした。

素晴らしい!フィードバックをありがとうございました。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。

仕組みは分かりませんが、どうやらExcelの数式におけるカッコは、計算の順序を制御する以外にも機能があるようですね。

その点と、[[CEILING関数および/またはINT関数の仕様]および/または「36.8」という数値]が影響しているようです。

この回答が役に立ちましたか?

はい
いいえ

役に立ちませんでした。

素晴らしい!フィードバックをありがとうございました。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。

この回答にどの程度満足ですか?

フィードバックをありがとうございました。

 
 

質問情報


2021年6月28日最終更新日 表示数 448 適用先: