Excel не считает дробные числа

Обновлено: 07.07.2024

Погрешность при суммировании Excel
Никто не знает с чем может быть связана такая погрешность при суммировании? Она конечно не.

Крах Excel при суммировании
Крах Excel при суммировании. Столкнулся с уникальной ошибкой Excel. Если в ячейке D10 нажать.

Ошибка Excel в суммировании дробных чисел
"Ваш Excel не правильно считает!" - сколько раз слышал от экономистов/менеджеров подобные.

Проверка на переполнение при суммировании
Составить программу , j проверяет, не приводит ли суммирование двух целых чисел A и B к.

А Вы не возмущайтесь

Давно известно, что калькулятор считает точнее Excel, когда речь идёт о не целых числах. На то он и калькулятор, что бы считать, а Excel - это электронная таблица, которая умеет гораздо больше калькулятора, но имеет свои недостатки

Вот с других форумов (к сожалению здесь ссылки давать нельзя):

Фактически источником ошибки является ошибка округления. Данная ошибка является следствием дефекта арифметики плавающей точки и того факта, что чаще всего десятичные дроби являются повторяющимися долями в двоичной системе счисления. Такие числа не могут представляться в конечном количестве битов. В связи с этим текстовое округление получается не всегда точным, т.к. большинство компьютеров подбирает последние цифры дробной части, исходя из ближайшего (с наименьшей разницей) эквивалента. Некоторые компьютеры не производят округления, а просто обрезают (выключают) последние биты, получая результирующую ошибку, правильно называемую ошибкой округления (в противоположность ошибке усечения, когда усекается расширение ряда). Для получения дополнительной информации обратитесь к Introduction to Numerical Methods (введение в числовые методы) авторов Peter A. Stark, Macmillian Company, 1970

. я готов объяснить, почему 3.1-3=0.1, а 4.1-4<0.1

Основа проблемы кроется именно в особенностях работы с плавающей запятой. Итак:

Для начала - вот формат числа с плавающей запятой с двойной точностью (именно он используется в Excel). И "забудем" для простоты, что байты идут от младшего старшему. Так выглядит +1:
+1: 0 011 11111111 0000 00000000 00000000 00000000 00000000 00000000 00000000
Первый "0" - это знаковый бит: 0 - "+", 1 - "-"
Затем группа из 11 битов - порядок числа. Она увеличена (для данного типа формата) на величину 3ffh, чтобы было удобно отображать отрицательные степени экспоненты. Т.е., фактически, порядок числа +1 равен 0, что и понятно.
Не очень ясно, как интерпретировать 52 последующих нуля. Тут нужно сказать, что в формате чисел с плавающей запятой используется т.н. "нормализованная" запись чисел, то есть когда значащий разряд целой части мантиссы не равен нулю - в общем случае, или равен "1" в двоичной системе исчисления; экспонента при этом соответствующим образом изменяется. Но если старший разряд - всегда "1", то зачем его хранить? Его и не хранят для экономии места, а лишь подразумевают:
1.0000 00000000 00000000 00000000 00000000 00000000 00000000 = 1.0e0
Посмотрим теперь как выглядят другие наши "подопытные":
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10011010
(обратите внимание на выделенные жирным шрифтом биты последнего байта, они сыграют потом ключевую роль!)
+3.1: 0 100 00000000 1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 0 100 00000000 1000 00000000 00000000 00000000 00000000 00000000 00000000
Вычтем одно число из другого и получаем (порядок в вычитании не участвует, только мантисса и "подразумеваемая" единица):
+3.1: 1.1000 11001100 11001100 11001100 11001100 11001100 11001101
+3.0: 1.1000 00000000 00000000 00000000 00000000 00000000 00000000
---------------------------------------------------------------------------------
+0.1: 0.0000 11001100 11001100 11001100 11001100 11001100 11001101
Целая часть мантиссы оказалась равной нулю, поэтому выполним нормализацию - сдвинем ее влево на 5 позиций, дополнив справа нулями(!), а порядок, соответственно, уменьшим на 5 - было 400h, стало 3fbh:
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10100000
Обратите внимание, число на долю, на "1" в 47м разряде, но больше, чем 0.1, поэтому при округлении мы и видим в ячейке Excel то, что нам нужно!

Проделаем тоже самое с 4.1-4.0 (но уже без дополнительных комментариев):
+4.1: 0 100 00000001 0000 01100110 01100110 01100110 01100110 01100110 01100110
+4.0: 0 100 00000001 0000 00000000 00000000 00000000 00000000 00000000 00000000
---------------------------------------------------------------------------------
+0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10000000
В 47м разряде "0", а, следовательно, мы получили меньше, чем 0.1, что нам Excel честно и показывает при большой разрядности. Почему он не поступает столь же честно при превышении? А посмотрите внимательнее - где больше отличие от "эталонного" 0.1? Во втором случае, в первом ему просто не хватает точности.

Если проделать все это с последующими числами, то будет то же самое, только хуже

Так что здесь не проблема Excel, а родимое пятно двоичной арифметики. Хотя, конечно, доля вины дяди Билла в этом тоже есть. Можно было бы воспользоваться иным представлением чисел. Однако, я не думаю, чтобы в Quattro или Lotus решение было бы иным.
А теперь представьте, что дело идет не о копейках, а о миллионах и миллиардах рублей или долларов? Вот тут-то эта мелочь и вылезет. И о подобных случаях округлений писали.

Часто при вычислении разницы двух ячеек в Excel можно видеть, что она не равна нулю, хотя числа одинаковые. Например, в ячейках A1 и B1 записано одно и тоже число 10,7 , а в C1 мы вычитаем из одного другое:

И самое странное то, что в итоге мы не получаем 0! Почему?

Причина очевидная - формат ячеек
Сначала самый очевидный ответ: если идет сравнение значений двух ячеек, то необходимо убедиться, что числа там действительно равны и не округлены форматом ячеек. Например, если взять те же числа из примера выше, то если выделить их -правая кнопка мыши -Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 7:

Теперь все становится очевидным - числа отличаются и были просто округлены форматом ячеек. И естественно не могут быть равны. В данном случае оптимальным будет понять почему числа именно такие, а уже потом принимать решение. И если уверены, что числа надо реально округлять до десятых долей - то можно применить в формуле функцию ОКРУГЛ:
=ОКРУГЛ( B1 ;1)-ОКРУГЛ( A1 ;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
Так же есть более кардинальный метод:

Excel 2007:Кнопка офис -Параметры Excel (Excel options) -Дополнительно (Advanced) -Задать точность как на экране (Set precision as displayed)
Excel 2010:Файл (File) -Параметры (Options) -Дополнительно (Advanced) -Задать точность как на экране (Set precision as displayed)
Excel 2013 и выше:Файл (File) -Параметры (Options) -Дополнительно (Advanced) -Задать указанную точность (Set precision as displayed)

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

Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
=((10,8*1000)-(10,7*1000))/1000=0,1

Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора :)

Читайте также: