エクセルにてacosを使い＃NUM!とエラー

質問

質問させていただきます。

エクセルの演算機能にてですが、

D4：-28.2
D5：-33 　　F5：4.8
D6：=D5-D4　　　 F7：DEGREES(ACOS(D6/F5))

と入力したところF7に＃NUM!とエラーが出てしまいます。

D5-D4=4.8となりF7には180と出る予定なのですが、出ません。

これが、D5：-33.4、F5：5.2と入力するとF7に180と出力されます。

同じ、小数点一桁同士の計算なのにどうして前者は結果が出ないのでしょうか？

一応、D6にROUNDを追加することで解決はしましたが、気になって仕方ありません。

わかる方いましたら、教えてください。

なお、使用しているのはwindows7です。

よろしくお願いいたします。

回答No.2

chie65535 · ベストアンサー

＞同じ、小数点一桁同士の計算なのにどうして前者は結果が出ないのでしょうか？

二進数では「0.1」は「循環小数」になります。

二進数の世界では、小数点以下は「1/2」「1/4」「1/8」「1/16」「1/32」「1/64」などを足し合わせた数で表現します。

なので「0.5」とか「0.25」とか「0.125」は「誤差無しで記憶できる」のですが「0.1」は、どうしても誤差が出てしまいます。

この誤差は「0.1と入力すると、内部的に0.1じゃない数が記録されるけど、画面には0.1と表示される」と言う事からも判るように「目で見ても判らない」のです。

D4：-28.2
D5：-33

では、引き算した結果は「見えない誤差を含む」のです。

一方、

D4：-28.2
D5：-33.4

では、引き算した結果は「見えない誤差を含む」のは同じですが、誤差の「向き」が違います。

この「誤差の向き」が、ACOSに成功するか失敗するかを決定しています。

空いているセルに

=(D6+F5)

と入力してみてください。括弧付きの式にしているのが肝要です。

正負が違うだけで、絶対値が同じ値を足せば、０になる筈ですが、０になりません。

D4：-28.2
D5：-33
F5：4.8

の場合、結果が

-8.9E-16
（-8.9×10の-16乗、を意味します。-0.00000000000000089です）

と表示されます。

これは「D6の値が、-4.8よりも、誤差分だけ小さい」と言う事を意味します。

負の数なので、言い換えれば「-4.8よりも、誤差分だけ、０から遠い」のです。

ほんのちょっとだけ「-4.9の方に寄っている」のです。

なので「D6/F5」の割り算をすると、割り算の結果は「ほんのちょっとだけ、-1からズレて、-1.1の方に寄っている」のです。

ACOSの引数は「-1～1」に限られ、この範囲を逸脱すると「#NUM!」になります。

割り算の結果が「ほんのちょっと1.1の方に寄っている」って事は「-1～1からハミ出している」って事です。

一方、

D4：-28.2
D5：-33.4
F5：5.2

の場合、

=(D6+F5)

の結果が

8.88E-16

になります。

これは「D6の値が、-4.8よりも、誤差分だけ大きい」と言う事を意味します。

負の数なので、言い換えれば「-4.8よりも、誤差分だけ、０に近い」のです。

ほんのちょっとだけ「-4.7の方に寄っている」のです。

なので「D6/F5」の割り算をすると、割り算の結果は「ほんのちょっとだけ、-1からズレて、-0.9の方に寄っている」のです。

この値は、ACOSの引数の条件「-1～1」に一致していますから、正常に表示されます。

＞一応、D6にROUNDを追加することで解決はしましたが、気になって仕方ありません。

計算結果が小数点以下第１位までで良いなら、D6を

=VALUE(TEXT(D5-D4,"#.#"))

にして下さい。

小数点以下第５位までなら

=VALUE(TEXT(D5-D4,"#.#####"))

にします。

これは「一旦、文字列にして、文字列を数値に変換する」と言う意味です。

セルに「4.8」と値を打ち込むと、内部的に「4.8と言う文字列を受け取って、文字列を数値に変換する」と言う処理をして、数値を記録します。

上記の式も「文字列を数値に変換する」と言う事をするので「誤差が消えて、直接に値を入力したのと同じ結果」が得られます。

このようにすると、計算結果のセルと値入力したセルの誤差が消えて

=(D6+F5)

の結果が「0」と表示される筈です。

こう表示されれば「誤差無し」って事なので、ACOSも失敗しません。

お礼

とても分かりやすかったです！
なんとなくNUM!が出るということは、1～-1の値に入っていないんだろうとは考えてましたが
、、、

二進数の誤差の関係だったのですか、、、

非常にわかりやすく、解決案も出していただいて助かりました！

ありがとうございます。

回答No.1

mindatg

浮動小数点の誤差ってやつです。
-4.8/4.8＝-1と表示されていても内部的には　-1.000000000000000000000000001
みたいな値になっています。
ACOS()のように値の範囲が-1～1　みたいな関数だと、-1より小さい為エラーになっちゃうんです。

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

下記なんかが比較的簡単に説明されています。

小数計算で発生する「誤差」―― 単純な計算の答えが合わない理由
http://pc.nikkeibp.co.jp/pc21/special/gosa/eg1.shtml

お礼

迅速な対応ありがとうございます。

リンク先も見ましたが非常にわかりやすかったです。

エクセルにて小数点以下の計算をし小数点の表示桁数を変えたところ上記のようになりました。

大変勉強になりました。

ありがとうございます。