助けてください – セルに222222.09482という数字を入力すると、数式バーに222222.0948199999という別の数字が表示されるという奇妙な状況に陥っています。以下は問題のスナップショットです
以下の数字を入力しても同じ動作をします
22222.09482
33333.09482
44444.09482
55555.09482
しかし、111111.09482と6666.09482、777777.09482と入力すると、99999.09482までは正しく表示されます。これは丸めに関係しているのでしょうか？丸めのプロファイルは設定していません。この問題を解決するために私を助けてください
29 user954171 2018-10-15
バグです
エクセルは、他の回答によると、通常のIEEE倍精度表現を使用しています。その精度は有効2進数53桁で、これはおよそ16桁の10進数に相当します
小数点以下の最初の15桁を表示することは常に「安全」である。15桁で与えられた任意の小数的に「提示された」数字は、小数点第15桁を1つずつ変えることによって得られる数字と安全に区別できるという意味で。例えば、15桁の数字
22222.09481 99999
22222.09482 00000
22222.09482 00001
は3つの異なる倍精度の数値にマップされます.この場合、これら3つのうちのどれも倍精度表現では「隣人」にはなりません
なので、ユーザー表示で最初の2つを混同してしまうのは、Excelのバグです
実際、この領域（１６３８４〜３２７６８の間）では、絶対精度は２〜３８であり、以下のような数値を表すことができる
...
22222.09481 99998 96571 9714760780334472656250000
22222.09481 99999 00209 9502831697463989257812500 <-- the one closest to what Excel showed to the user
22222.09481 99999 03847 9290902614593505859375000
22222.09481 99999 07485 9078973531723022460937500
22222.09481 99999 11123 8867044448852539062500000
22222.09481 99999 14761 8655115365982055664062500
22222.09481 99999 18399 8443186283111572265625000
22222.09481 99999 22037 8231257200241088867187500
22222.09481 99999 25675 8019328117370605468750000
22222.09481 99999 29313 7807399034500122070312500
22222.09481 99999 32951 7595469951629638671875000
22222.09481 99999 36589 7383540868759155273437500
22222.09481 99999 40227 7171611785888671875000000
22222.09481 99999 43865 6959682703018188476562500
22222.09481 99999 47503 6747753620147705078125000
22222.09481 99999 51141 6535824537277221679687500
22222.09481 99999 54779 6323895454406738281250000
22222.09481 99999 58417 6111966371536254882812500
22222.09481 99999 62055 5900037288665771484375000
22222.09481 99999 65693 5688108205795288085937500
22222.09481 99999 69331 5476179122924804687500000
22222.09481 99999 72969 5264250040054321289062500
22222.09481 99999 76607 5052320957183837890625000
22222.09481 99999 80245 4840391874313354492187500
22222.09481 99999 83883 4628462791442871093750000
22222.09481 99999 87521 4416533708572387695312500
22222.09481 99999 91159 4204604625701904296875000
22222.09481 99999 94797 3992675542831420898437500
22222.09481 99999 98435 3780746459960937500000000 <-- the one closest to what the user types
22222.09482 00000 02073 3568817377090454101562500
22222.09482 00000 05711 3356888294219970703125000
22222.09482 00000 09349 3144959211349487304687500
22222.09482 00000 12987 2933030128479003906250000
22222.09482 00000 16625 2721101045608520507812500
22222.09482 00000 20263 2509171962738037109375000
22222.09482 00000 23901 2297242879867553710937500
22222.09482 00000 27539 2085313796997070312500000
22222.09482 00000 31177 1873384714126586914062500
22222.09482 00000 34815 1661455631256103515625000
22222.09482 00000 38453 1449526548385620117187500
22222.09482 00000 42091 1237597465515136718750000
22222.09482 00000 45729 1025668382644653320312500
22222.09482 00000 49367 0813739299774169921875000
22222.09482 00000 53005 0601810216903686523437500
22222.09482 00000 56643 0389881134033203125000000
22222.09482 00000 60281 0177952051162719726562500
22222.09482 00000 63918 9966022968292236328125000
22222.09482 00000 67556 9754093885421752929687500
22222.09482 00000 71194 9542164802551269531250000
22222.09482 00000 74832 9330235719680786132812500
22222.09482 00000 78470 9118306636810302734375000
22222.09482 00000 82108 8906377553939819335937500
22222.09482 00000 85746 8694448471069335937500000
22222.09482 00000 89384 8482519388198852539062500
22222.09482 00000 93022 8270590305328369140625000
22222.09482 00000 96660 8058661222457885742187500
22222.09482 00001 00298 7846732139587402343750000
...
さらに詳しく説明するには、あるセルに22222.09482と入力し、別のセルに22222.0948199999(最後に9をつける5つの数字)と入力してみてください。エクセルは上の矢印で示された2つのIEEEの代表者を選ぶはずです。そして、この2つのセルの差を計算して9.82254E-11を求めることができるので、私はそうしていると思います。しかし、どちらも同じように表示されています
エクセルが最初の17桁を表示していたとしたら、それは小数点以下の数字の「下」に何のIEEE番号があるのかを正確に選ぶのに役立つでしょう。その場合は
22222.0948199999 --> 22222.09481 99999 00
22222.09482 --> 22222.09481 99999 98
しかし、誤った方法で丸められた15桁の数字を表示することは、誤解を招き、助けになりません
誰かが意図的だと主張する前に、ではなぜ8.7は同じ挙動を示さないのでしょうか？8.7に最も近い倍精度の数字は
8.69999999999999 93
と表示されているので、意図的なものであれば 8.69999999999999 と表示されるはずです。しかし、そうではありません
32 Jeppe Stig Nielsen 2018-10-16
Excelは、IEEE 754の2進数64ビット浮動小数点フォーマットで数値を格納します。重要なのは「保存する」ということです。10進数から2進数への変更は、実際の計算で使用されるときだけでなく、数値が保存されるたびに行われます
これに関する素晴らしい記事は Understanding Floating Point Precision, 別名 “Why does Excel Give Me Me Seemingly Wrong Answers?” にあります
本当に大きな数字を扱うような表計算プログラムを、有効数字の多い数字で作ることは可能です。しかし、それは恐ろしく実用的ではありません。Excelは、IEEE 754 decimal128フォーマットを使用するように設計されていたかもしれません。しかしその代わりに、はるかに一般的なbinary64 Double Precisionフォーマットを使用しています。10桁しかない数字にはこれで十分だと思うかもしれませんが、10進数から2進数に変換すると少し複雑になります
一般的にスプレッドシートは財務データに使用されることを覚えておいてください。デフォルトで、あるいは特別な設定によって、より大きな数値フォーマットを使用できるツールは確かに他にもありますが（おそらく他の表計算プログラムもありますが、最近は検索していません）、Excelはその中の一つではありません
LibreOffice の方が優れた処理をしていると指摘する人にとっては、見た目に惑わされることもあるかもしれません。詳細はこの記事を参照してください。LibreOfficeでは、大きな数字の扱いが若干異なるようですが、基本的な64ビット浮動小数点表現は同じで、似たような問題を抱えています
22 manassehkatz-Moving 2 Codidact 2018-10-16
エクセルは計算を行う際に、使用する数値の内部バイナリ表現を見つける必要があります。あなたの場合、浮動小数点数を使用していますが、実際のところ、このデータ形式はあなたの数値に対して（非常に良い）近似値を持っていますが、完全には一致しません。そのため、どの出力形式を使用するかをExcelに明示的に指示しないと、Excelは「最善の努力」をして、内部的に計算された値に近い出力を行いますが、入力されたテキストとは正確に一致しません
ただこれを明確にするために：あなたが入力したテキストが数字を表し、数字のシーケンスを数字に変換することを理解することは、すでに上から “計算 “の定義を満たしています
EDIT
私は、64ビット浮動小数点表現を使用するという選択が実際には良い選択であると考えていることを十分に明確にしていませんでした。小数点以下の11桁目の丸め誤差が大きな影響を与えるような科学者のためのツールとしてはExcelは向いていませんが、会計士は決して使わない数字に現れるかもしれない不正確な計算の原因を収容するために、処理速度を数百万分の1にまで低下させたくはありません
スプレッドシートプログラムを用途に合わせて使用し、明示的な出力フォーマットを使用して、これらの効果が目に見える領域に出てこないようにすれば、問題ありません
11 Eugen Rieck 2018-10-15
11111.09482と6666.09482、777777.09482…と入力すると、99999.09482まで正しく表示されます。これは丸めに関係しているのでしょうか？丸めのプロファイルは設定していません。この問題を解決するために私を助けてください
数字は正しく表現できるものとできないものがあります
計算に合わせて表示精度を適切に設定し、round()関数を使用してください
Explanation:
ウィキペディア – “Microsoft Excelの数値精度“
オラクルの数値計算ガイド – “浮動小数点演算についてすべてのコンピュータ科学者が知っておくべきこと“
Solution:
- マイクロソフトのオフィスサポート – “丸め精度の設定“
データに数値フォーマットを適用する前に、[表示される精度]オプションを設定することで、浮動小数点丸めエラーによる作業への影響を頻繁に防ぐことができます。このオプションを使用すると、ワークシート内の各数値の値が、ワークシートに表示されている精度になるように強制されます
ファイル > オプションをクリックします。 Excel 2007の場合。Microsoft OfficeボタンのOfficeボタンの画像をクリックし、[Excelのオプション]をクリックします。
詳細設定]をクリックし、[このワークブックを計算するとき]で[表示される精度を設定する]チェックボックスを選択し、[OK]をクリックします
Click OK.
ワークシートで、書式設定するセルを選択します
ホーム]タブの[数字]の横にある[ダイアログボックスランチャーボタン]の画像をクリックします。
カテゴリ] ボックスで [番号] をクリックします
小数点以下の桁数］ボックスに、表示する小数点以下の桁数を入力します
ヒント: 浮動小数点演算のストレージの不正確さの影響を最小限に抑えるために、ROUND関数を使用して計算に必要な小数点以下の桁数に丸めることもできます
会計ジャーナル – “Excelの計算ミスに悩まされる“
特定の奇数は繰り返しの 2 進数を作成し、それらの繰り返し桁が 15 位の後に切断されると、2 進数は意図した数値に正確に変換されません。例として、Excelのすべてのエディションでは、22.26 – 21.29の式は0.97を生成する必要がありますが、代わりに0.970000000000002を生成します。試してみてください。計算問題がわかるように、列の幅と小数点以下の桁数を増やすことを忘れないでください
このようなエラーは、通常、意味のある計算エラーになることはほとんどないため、取るに足らない、または重要ではないと考えられています。しかし、ここでは、潜在的な浮動小数点エラーを排除するために取ることができる2つの対策を紹介します
ROUND関数。エクセルのROUND関数を使用すると、計算した値を希望する小数点以下の位置に丸めることができ、15桁の誤差が生じる可能性を排除することができます。例えば、=ROUND(-21.29 + 22.26,2)の式は、正確には0.97が得られます
精度を表示します。Excelの[表示される精度]オプションをオンにすると、すべての数式が表示されている桁数に基づいて計算値を切り捨てたり、丸めたりすることを強制的に行うことができます
Excel 2013、2010、および 2007 でこのオプションを有効にするには、[ファイル]（または [Office Orb]）、[オプション]（または [Excel オプション）、[詳細設定] の順に選択し、[このワークブックを計算するとき] セクションで、[表示される精度を設定する] ボックスにチェックを入れてから [OK] をクリックします
Excel 2003、2002、および2000では、[ツール]メニューから[オプション]を選択し、[計算]タブの[ワークブックのオプション]で、[表示される精度]ボックスにチェックを入れ、[OK]をクリックします
2 Rob 2018-10-16
ご存知だと思いますが、コンピュータは内部的には0と1（通称ビット）のみで動作し、値を表すビット数は決まっています（現在では64ビットが一般的です）。つまり、表現できる値の数は64乗の2です。確かにこれは膨大な数ですが、可能な値の数は有限なので、すべての数を表現できるわけではありません。正確に表現できない数に遭遇したとき、それは自動的にそれが表現できる最も近いものに置き換えられます。それがあなたが見ているものです
0 Javier Alvarado 2018-10-15
コンピュータは2進数で計算を行い、ほとんどの場合、非整数の値には浮動小数点を使用します。浮動小数点で正確に表現できる唯一の分数値は、設計された精度限界（通常は53ビット）で終わる2（1/2, 1/4, 1/8, 1/16, 1/32, …）の分数乗のいくつかの組み合わせの合計でなければなりません。これらの値は常に10進数で整然とした正確な表現ができるわけではなく、逆に、10進数で正確に表現できるすべての分数値が2進数で正確に表現できるわけではありません。例えば、0.1.永遠に続かない２の小数の和として表現することはできません
スプレッドシートに10進数の値を入力すると、それは2進数に変換されて保存され、あなたが説明したようなケースは、2進数で表現できる最も近い近似値になります。表示されると10進数に変換され、再び近似値が必要になりますが、これは入力した表現と全く同じ表現には変換されない可能性があります
なぜ 53 ビットなのでしょうか？なぜなら、「倍精度」浮動小数点を格納するための典型的な標準規格では64ビットを使用しており、その中には仮数（符号とも呼ばれる）、符号指示子、指数が含まれています。指数は通常10ビットが割り当てられ、符号は1ビットを取り、53ビットを仮数に残します。これは保存用です。計算は通常80ビットを使用して行われ、四捨五入されます
コンピュータがベース10で動作する状況があり、特に金銭的な値を扱う場合には、丸めのアーチファクトが許容できない場合があります
-1 Zenilogix 2018-10-16
上の多くの方がおっしゃっているように、これは内部表現の誤りです。Excelは倍精度、64ビットの浮動小数点数を選択しました。これにより、2つの64の可能な値が与えられます。実数領域には無限の値が含まれていますので、Excelで表現できない値を使おうとすると、表現可能な値に最も近い値が使われます
無限のメモリがあれば、どんな実数も表現できるというコメントを見たことがあります。確かにその通りですが、「無限のメモリ」などというものは存在しないので、これは意味がありません。他の人は、エクセルはより大きな内部表現、例えば128ビットを使用することができたと述べています。その通りですが、コンピュータはプロセッサのバスサイズに合ったビット数で表現された数値に対して数学的な演算を行うのが得意だということがわかりました。ですから、32ビットのコンピュータは32ビットの数字の数学的な操作が最も速く、64ビットのコンピュータは64ビットの数字の数学的な操作が最も速くなります。もし128ビットコンピュータが登場したら、Excelは128ビットの数値表現に移行することが予想されます。その場合でも、表現できる数は限られていますが、非常に大きな数になります。数値が異なるだけで、同じ効果はまだ存在するでしょう
スプレッドシート上での数字の見え方が気になる場合は、設定された精度（小数の数）を使用することで、一貫した結果を得ることができます。入力した数字とExcelに保存されている実際の数字との差が気になる場合は、心配するのは当然です。違いは実際にありますし、その誤差はどのような計算をしたとしても、それが反映されてしまいます。私はあなたがこのエラーで行き詰っていることを恐れています。これはExcelの制限であり、一部の方がおっしゃっているようなバグではありません。これはすぐに変更されることはないでしょうから、もしそれが受け入れられないのであれば、より高い精度で数値を表現できる別のスプレッドシートアプリケーションを探すことをお勧めします。しかし、そのようなアプリケーションを見つけたとしても、制限はまだあることを心に留めておいてください。誤差の大きさが違うだけです
-1 Tibi 2018-10-17
