Microsoft Excel

трюкиприёмырешения

Как в Excel решить проблему чисел с плавающей запятой

Пользователи Excel часто озадачены очевидной неспособностью Excel выполнять простые математические операции. Это не ошибка программы: компьютеры по своей природе не обладают бесконечной точностью. Excel хранит числа в двоичном формате, используя 8 байт, которых хватает для обработки числа с 15 цифрами.

Некоторые числа не могут быть выражены именно с помощью 8 байт, поэтому хранятся в виде приближений. Чтобы продемонстрировать, как это ограничение может вызывать проблемы, введите следующую формулу в ячейку А1: =(5,1-5,2)+1.

Результат должен быть равен 0,9. Однако если вы отформатируете ячейку так, чтобы она отображала 15 знаков после запятой, то обнаружите, что программа вычисляет формулу с результатом 0,899999999999999 — значением, очень близким к 0,9, но никак не 0,9. Такой результат получается, поскольку операция в круглых скобках выполняется первой, и этот промежуточный результат сохраняется в двоичном формате с использованием приближения. Затем формула добавляет 1 к этому значению, а погрешность приближения распространяется на конечный результат.

Во многих случаях ошибки такого рода не представляют проблемы. Однако если вам необходимо проверить результат формулы с помощью логического оператора, это может стать головной болью. Например, следующая формула (которая предполагает, что предыдущая формула находится в ячейке А1) возвращает ЛОЖЬ: =A1-0,9.

Одно из решений этого типа ошибки заключается в использовании функции ОКРУГЛ. Следующая формула, например, возвращает ИСТИНА, поскольку сравнение выполняется с использованием значения в А1, округленного до одного знака после запятой: =ОКРУГЛ(А1;1)=0.9. Вот еще один пример проблемы точности. Попробуйте ввести следующую формулу: =(1,333-1,233)-(1,334-1,234). Эта формула должна возвращать 0, по вместо этого возвращает -2,22045Е-16 (число, очень близкое к нулю).

Если бы эта формула была в ячейке А1, то формула вернула бы Not Zero: =ЕСЛИ(А1=0;"Zero";"Not Zero"). Один способ справиться с такой ошибкой округления состоит в использовании формулы вроде этой: =ЕСЛИ(ABS(А1)<0.000001;"Zero";"Not Zero"). В данной формуле используется оператор «меньше чем» для сравнения абсолютного значения числа с очень малым числом. Эта формула будет возвращать Zero.

Другой вариант — указать Excel изменить значения листа в соответствии с их отображаемым форматом. Для этого откройте окно Параметры Excel (выберите Файл ► Параметры) и перейдите в раздел Дополнительно. Установите флажок Задать точность как на экране (который находится в области При пересчете этой книги).

Флажок Задать точность как на экране позволяет изменять числа в ваших листах так, что они постоянно будут соответствовать появлению на экране. Этот флажок применяется для всех листов активной книги. Такой вариант не всегда будет вам подходить. Убедитесь, что вы понимаете последствия установки флажка Задать точность как на экране.

По теме

Новые публикации

Top