В Excel, когда я ввожу 22222.09482, я вижу число 22222.0948199999 в строке формул

Question

Не могли бы вы помочь - у меня странная ситуация, когда я ввожу в ячейку число 22222.09482, тогда в строке формул я вижу другое число 22222.0948199999. Ниже приведен снимок проблемы.

Я вижу то же поведение при вводе следующих чисел:

22222.09482
33333.09482
44444.09482
55555.09482

но когда я ввожу 11111.09482 и 66666.09482, 77777.09482 .. до 99999.09482, тогда они отображаются правильно. Я не уверен, что это связано с округлением? Я не настроил никаких профилей округления. Не могли бы вы помочь мне в решении проблемы.

Answer 1

Это ошибка.

Excel использует обычное представление двойной точности IEEE, согласно другим ответам. Его точность составляет 53 значащих двоичных разряда, что соответствует примерно 16 десятичным разрядам.

Всегда безопасно отображать первые 15 значащих десятичных цифр. В том смысле, что любое десятичное "представленное" число, содержащее 15 цифр, можно безопасно отличить от чисел, полученных путем изменения 15-го десятичного знака на единицу. Например, 15-значные числа:

22222.09481 99999
22222.09482 00000
22222.09482 00001

сопоставить с тремя различными числами двойной точности. Ни один из этих трех не будет "соседями" в представлении двойной точности, в данном конкретном случае.

Таким образом, путаница первых двух на пользовательском дисплее является ошибкой Excel.

Фактически, в этой области (между 16384 и 32768) абсолютная точность составляет 2 ^-38, и представляются следующие числа:

...
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 (пять конечных девяток) в другой ячейке. Excel должен выбрать двух представителей IEEE, указанных стрелкой выше. И я думаю, что это так, потому что вы можете вычислить разницу этих двух ячеек, чтобы получить 9.82254E-11 . Но оба показаны одинаково.

Если бы Excel показал первые 17 цифр, было бы полезно точно определить, какой номер IEEE находится "под" десятичным числом. В таком случае:

22222.0948199999 --> 22222.09481 99999 00
22222.09482      --> 22222.09481 99999 98

Но показ 15 цифр, округленных неправильно, вводит в заблуждение и бесполезен.

---

Прежде чем кто-либо заявляет, что это намеренно, то почему 8.7 не показывает такое же поведение? Ближайшее число с двойной точностью до 8.7 :

8.69999999999999 93

поэтому он должен отображаться как 8.69999999999999 если это было сделано намеренно. Но это не так.

Answer 2

Excel хранит числа в двоичном 64-битном формате с плавающей запятой IEEE 754 . Ключ "хранит" - изменение с десятичного на двоичное происходит при каждом сохранении числа, а не только при его использовании в реальных вычислениях.

Хорошая статья на эту тему - « Понимание точности с плавающей запятой», или «Почему Excel дает мне, казалось бы, неправильные ответы?»

Можно создать программу для работы с электронными таблицами, которая будет обрабатывать действительно большие числа с большим количеством значащих цифр. Но это не очень практично. Excel мог бы быть разработан для использования формата IEEE 754 decimal128, который допускает 34 десятичных знака - более чем достаточно для хранения 22222.09482. Но вместо этого он использует гораздо более распространенный двоичный формат Double Precision64, который имеет точность 53 бита, что составляет чуть менее 16 цифр. Хотя вы можете подумать, что этого будет достаточно для числа, содержащего только 10 цифр, преобразование из десятичного в двоичное несколько усложняет ситуацию, т. Е. 2222209482 может храниться на 100% правильно как двоичное число 64, а 22222.09482 - нет.

Имейте в виду, что электронные таблицы обычно используются для финансовых данных, которые обычно не требуют большого количества цифр точности, или для моделирования "что если" в различных сценариях, где не требуется сверхвысокий уровень точности. Конечно, есть и другие инструменты (и, возможно, другие программы для работы с электронными таблицами, но я в последнее время не искал), которые по умолчанию или с помощью специальных параметров конфигурации могут использовать больший числовой формат, но Excel не является одним из них.

Для тех, кто отмечает, что LibreOffice справляется с этим лучше, внешность может быть обманчива. Смотрите этот пост для более подробной информации. Кажется, что LibreOffice обрабатывает большие числа немного по-разному, но имеет одинаковое базовое 64-битное представление с плавающей запятой с похожими проблемами.

Answer 3

При выполнении вычислений Excel должен найти хорошее внутреннее двоичное представление для используемых чисел. В вашем случае он использует число с плавающей запятой, и по сути этот формат данных имеет (очень хорошее) приближение для вашего числа, но не дает точного соответствия. Поэтому, если вы явно не укажете Excel, какой выходной формат использовать, он приложит "все усилия", что приведет к выводу, который ближе к внутреннему вычисляемому значению, но не является точно введенным вами текстом.

Просто чтобы прояснить это: понимание того, что введенный вами текст представляет число, и преобразование последовательности цифр в число уже соответствует определению "вычисления", приведенному выше.

РЕДАКТИРОВАТЬ

Я не совсем ясно дал понять, что считаю использование 64-битного представления с плавающей точкой действительно хорошим: Excel не является инструментом для ученых, где ошибка округления в 11-й цифре после десятичной запятой составляет большое влияние, но бухгалтеры не хотят, чтобы их скорость обработки, уменьшенная в миллионы раз, стала источником неточных вычислений, которые могут проявиться в цифрах, которые они никогда не используют.

Если вы используете программу для работы с электронными таблицами, для которой она была разработана, и используете явное форматирование вывода, чтобы эти эффекты никогда не попадали в видимую область, у вас все будет хорошо.

Answer 4

Когда я ввожу 11111.09482 и 66666.09482, 77777.09482 .. до 99999.09482, тогда они отображаются правильно. Я не уверен, что это связано с округлением? Я не настроил никаких профилей округления. Не могли бы вы помочь мне в решении проблемы.

Некоторые числа могут быть представлены правильно, а некоторые нет.

Установите отображаемую точность в соответствии с вашими расчетами и используйте функцию round() .

• Объяснение:

  • Википедия - « Числовая точность в Microsoft Excel »

  • Руководство по вычислительным вычислениям Oracle - « Что должен знать каждый компьютерщик об арифметике с плавающей точкой »

• Решение:

  • Поддержка Microsoft Office - « Установить точность округления »:

  Вы часто можете предотвратить влияние ошибок округления с плавающей запятой на вашу работу, установив параметр Точность как отображаемый, прежде чем применять числовой формат к вашим данным. Эта опция заставляет значение каждого числа на листе быть с точностью, которая отображается на листе.

  1. Нажмите Файл> Параметры.
     В Excel 2007: нажмите кнопку «Microsoft Office» на изображении кнопки «Office» и выберите «Параметры Excel».
     

  2. Нажмите кнопку «Дополнительно», а затем в разделе «При вычислении этой книги» установите флажок «Установить точность как отображается» и нажмите кнопку «ОК».

  3. Нажмите ОК.

  4. На листе выберите ячейки, которые вы хотите отформатировать.

  5. На вкладке Главная щелкните изображение кнопки запуска диалогового окна рядом с номером.
     
     

  6. В поле «Категория» нажмите «Число».

  7. В поле «Десятичные знаки» введите количество десятичных знаков, которое вы хотите отобразить.

  Совет: Чтобы минимизировать любые эффекты погрешности арифметического хранения с плавающей запятой, вы также можете использовать функцию ROUND для округления чисел до количества десятичных разрядов, которое требуется для ваших вычислений.

• Журнал бухгалтерии - « Ошибка в расчетах Excel »:

  Некоторые нечетные числа создают повторяющиеся двоичные десятичные дроби, и когда эти повторяющиеся цифры обрезаются после 15 разрядов, двоичное число не преобразуется обратно точно в предполагаемое числовое значение. Например, во всех выпусках Excel формула 22.26 - 21.29 должна давать 0,97, но вместо этого дает 0,970000000000002. Попробуйте, и не забудьте увеличить ширину столбца и десятичные разряды, чтобы вы могли увидеть проблему вычисления.

  Такие ошибки обычно считаются незначительными или несущественными, поскольку они редко проявляются в значимых ошибках в расчетах; тем не менее, вот две меры, которые вы можете предпринять, чтобы устранить потенциальные ошибки с плавающей запятой:

  1. Круглая функция. Используйте функцию ROUND в Excel, чтобы округлить вычисленные значения до нужного десятичного знака, тем самым исключив любую возможность появления аномалий из 15 цифр. Например, формула = ROUND(-21,29 + 22,26,2) точно дает 0,97.

  2. Точность. Можно включить параметр «Точность как отображаемый» в Excel, чтобы все формулы усекались и округляли вычисленные значения на основе видимых цифр.

  Чтобы включить этот параметр в Excel 2013, 2010 и 2007, выберите «Файл» (или «Сфера Office»), «Параметры» (или «Параметры Excel»), «Дополнительно», а в разделе «При вычислении этой книги» установите флажок «Задать точность как отображаемый» и затем нажмите ОК.

  В Excel 2003, 2002 и 2000 в меню «Сервис» выберите «Параметры» и на вкладке «Расчет» в разделе «Параметры рабочей книги» установите флажок «Точность как отображается» и нажмите кнопку «ОК».

Answer 5

Как я уверен, вы знаете, что компьютеры внутренне работают только с нулями и единицами (иначе говоря, битами) и имеют фиксированное количество бит для представления значения (обычно 64 бита в настоящее время). Это означает, что число различных значений, которые могут быть представлены, составляет от 2 до 64 степени. Конечно, это огромное число, но число возможных значений конечно, поэтому не все числа могут быть представлены. Когда он встречает число, которое он не может представить точно, он автоматически заменяется ближайшим, который он может представить. Это то, что вы видите.

Answer 6

Компьютеры выполняют свою математику в двоичном формате и почти всегда используют числа с плавающей запятой для нецелых значений. Единственные дробные значения, которые могут быть точно представлены в плавающей точке, должны быть суммой некоторой комбинации дробных степеней 2 (1/2, 1/4, 1/8, 1/16, 1/32, ...), заканчивающихся с заданным пределом точности (обычно 53 бита). Эти значения не всегда имеют аккуратное или точное представление в десятичном формате, и, наоборот, не все дробные значения, которые вы можете представить точно в десятичном виде, будут иметь точное представление в двоичном виде. Например: 0,1. Он не может быть представлен как сумма дробных степеней 2, которая не вечна.

Когда вы вводите десятичное значение в вашу электронную таблицу, оно будет преобразовано и сохранено в двоичном формате, и такие случаи, как вы описали, станут самым близким приближением, которое может быть представлено в двоичном виде. При отображении он преобразуется обратно в десятичную, что опять-таки требует аппроксимации, которая может не преобразовать обратно в точно такое же представление, которое вы ввели.

Почему 53 бита (дать или взять)? Потому что типичный стандарт для хранения "плавающей запятой двойной точности" использует 64 бита, в которых есть мантисса (также называемая значением), индикатор знака и показатель степени. Экспоненту обычно выделяется 10 битов, знак занимает один, оставляя 53 для мантиссы. Это для хранения. Расчеты обычно выполняются с использованием 80 битов и округляются назад.

Существуют ситуации, когда компьютеры будут работать в базе 10, особенно при работе с денежными значениями, когда артефакты округления неприемлемы.

Answer 7

Как и многие другие, это внутренняя ошибка представления. Excel сделал выбор для 64-битных чисел с плавающей запятой двойной точности. Это дает вам 2 ⁶⁴ возможных значения. Домен действительных чисел содержит бесконечность значений, поэтому, когда вы пытаетесь использовать тот, который не может быть представлен в Excel, он будет использовать ближайшее, которое может быть представлено.

Я видел комментарии о том, что, учитывая бесконечную память, можно представить любое действительное число. Правда, но не существует такой вещи, как "бесконечная память", так что это спорный вопрос. Другие утверждают, что Excel мог бы использовать большее внутреннее представление, например 128 бит. Правда, но оказывается, что компьютеры лучше выполняют математические операции над числами, представленными с количеством битов, которое соответствует размеру шины процессора. Таким образом, 32-разрядный компьютер будет быстрее всех в математических операциях с 32-разрядными числами, а 64-разрядный компьютер будет быстрее всех в математических операциях с 64-разрядными числами. Если и когда будет 128-битный компьютер, то мы можем ожидать, что Excel перейдет на 128-битное представление чисел. Это все еще обеспечит очень большой, но ограниченный набор чисел, которые могут быть представлены. Тот же эффект все еще будет присутствовать, только с другими номерами.

Если вас беспокоит то, как числа выглядят в электронной таблице, то использование заданной точности (числа десятичных знаков) даст вам согласованные результаты. Если вы беспокоитесь о разнице между введенным вами номером и фактическим номером, сохраненным в Excel, вы вправе быть обеспокоенным. Разница реальна, и ошибка будет учтена во всех ваших расчетах. Я боюсь, что вы застряли с этой ошибкой. Это ограничение Excel, а не ошибка, как утверждают некоторые. Скорее всего, оно не изменится в ближайшее время, поэтому, если оно неприемлемо для вас, я предлагаю вам найти другое приложение для работы с электронными таблицами, которое может представлять числа с более высокой точностью. Но имейте в виду, что если вы найдете какое-либо такое приложение, ограничение все еще существует. Это просто размер ошибки, который отличается.