| Home | What's New |
生産性向上 基本テクニック |
トラブル回避 テクニック |
生産性向上 リンク |
personal.xls 強化講座 |
生産性向上 ツール |
Site Map | Guest Book |
地球風 画像館 |
Q&A
Salon EXCELの質問はこちらへ |
|
11/04/2002 -
|
||
| １８．丸め誤差に気をつけろ！ |
||
|
・
|
EXCELで小数点を含む数値の計算をした場合、思わぬ結果になることがあります。 したがって、皆さん普段はほとんど気にしていないでしょうが、実は小数点数値を使う場合には留意する必要があります。 最初に、実例を見てみましょう。（以下の例は、EXCEL2000を使用しています） |
|
|
・
|
右の図は、セルの書式を特に指定していない（”標準”の状態）シートで、いくつかの計算式を入れた結果です。（実際には、B列に表示されている計算式がA列に入っています） セルA1〜A8、A14〜A15、A20〜A22、A25〜A26は、計算式としては全て結果は０になるはずですが、A4・A7・A8・A14・A21・A22・A26は０になっていません。 A17とA18は、計算式ではなく単純に数字を入れているだけですが、画面上部の数式バーに表示される数字はセルに表示されている数字とは異なっています。 それからA23ですが、LEN関数でセルA22の値の長さを求めていますが、結果は21となっています。右の画面では表示されていませんが、セルA22の表示桁数を増やしていくと、実際には -2.77555756156289E-17 という21文字の計算結果になっていることが分かります。 計算結果が数式どおり０ならば、長さは本来１になるはずです。 このように、小数点を含む数値で計算をすると、目的の結果が得られないケースがあります。 右の例のように、同じ数値を使っていても、演算の順序が違ったり、カッコでくくったり、０を足したり１を掛けたりすることで結果が変わってくることもあります。 当然、A列のセルの値を使って例えば「A8が０ならば〜」といったIF関数を使っている場合、処理結果としては「０ではない」という判断がされてしまうことになります。 |
 |
|
・
|
これは、EXCELのバグというよりも、数値をバイナリ形式（２進数）で内部的に取り扱っているコンピュータに共通の現象（問題）です。 例えば、0.1という数値は、バイナリ形式では .0001100011000111000111 (以下繰り返し) という循環小数になってしまい、正確な0.1を表すことができないため、コンピュータ内部では丸めて格納されているのです。この丸めによって発生する誤差が丸め誤差です。 |
|
| この問題は、マイクロソフトのサポートページでも（当然）取り上げられています。 マイクロソフト サポート技術情報（抜粋） 浮動小数点数値演算の結果に含まれる丸め誤差について 特定の小数部を持つ数値を入力した場合に誤差を生じる 数値を比較した結果が正しく返らないケースについて これらのサポート情報によれば、EXCEL97以降はこの誤差の一部を補正する仕様になったようですが、上の例のように、EXCEL2000でも丸め誤差は残っています。 |
||
| どのように回避すればよいのか？ | ||
|
・
|
コンピュータの問題なので、なんとかごまかして使うしかありません。今のところ。たぶん。 | |
| まず、表示だけ正しくしたい場合（計算結果で判定等を行わない場合）は、書式設定でごまかすことが出来ます。 たとえば、右のように”数値”で”小数点以下の桁数１桁”と設定してみます。（今回の例では17行目・18行目以外は全て小数点以下の桁数が１桁なので） そうすると、元の状態（下の左側の図）が下の右側の図のようになります。 4・7・8・14・21・22・26行目の表示が０になっていることが分かると思います。 しかし、23行目（セルA22の長さを求める式がはいっている部分）は、21のまま変化していません。つまり、書式設定によって表示上は０に見えますが、A22の値は元の状態から変わっていないということです。 したがって、書式設定で表示はごまかせても、セルの内容を用いた判定処理等を行うと誤った結果になる可能性はまだ残っています。 |
 |
|
|
セルの書式が”標準”の状態
|
上の書式を設定した状態
 |
|
| セルの内容を判定等に使いたい場合、計算結果を有効桁数で丸めてしまうことで、求める結果にする方法があります。 今回の例では（17・18行目の例を除き）、小数点以下1桁までの数字の足し算・引き算しかしていないので、計算結果を小数点以下第２位で四捨五入してしまいます。 （丸め誤差によって発生した本来の計算結果との非常に小さな誤差を、有効桁数の１つ下の位で四捨五入することで丸めてしまおうということです） 例として、右上の図にIF文を入れてみます。 下図のセルC8では、セルA8が０かどうかをIF文で判定しています（D8に表示されている式がC8に入っています）が、A8の表示は０でもIF文の判定結果は０ではないとなっています。 そこで、セルC9では、A8をROUND関数で小数点以下第１位までに四捨五入した結果をIF文で判定させています。判定結果は０となっています。 また、セルC23では、A22の値をROUND関数で丸めた結果の長さを表示させています。A23が21という結果になっているのに対し、C23の結果は１となっています。これは、ROUND(A22,1)の部分の結果が０になっているためです。 |
||
 |
||
| あと、可能ならば、実際の数字が小数点以下の部分があっても、セルに入れるときや数式に記述するときは整数で入力しておき、最後の計算結果を目的の桁数に変換するというのも１つの方法です。 2003.08.03 このページを書いたのは2002年11月ですが、2003年6月に 日経PC21 サイトで “達人”芳坂和行氏に学ぶ、エクセル「演算誤差」対策講座 という、とても分かりやすく、かつ詳しい解説が掲載されています（第1回〜第4回まで）。誤差についてはまずこちらのページをご覧になることをお勧めします。 |
||
