Почему в Excel нельзя точно вычислить 0,1 + 0,2: машинная арифметика

Как Excel хранит число внутри ячейки

Любое число в Excel лежит в памяти в формате double precision по стандарту IEEE 754 — том же, что используют процессоры Intel, AMD и почти все языки программирования. На число выделяется 64 бита: один бит под знак, одиннадцать под порядок и пятьдесят два под дробную часть мантиссы, к которой добавляется ещё один неявный старший бит. Такого запаса хватает примерно на 15 значащих десятичных цифр — независимо от того, где стоит запятая.

Из этого ограничения следует не самый очевидный вывод: 15 цифр точности одинаково жёстко ограничивают и огромные числа, и крошечные дроби. Максимум, который способен уместиться в ячейку, — примерно 1,8 умножить на 10 в 308-й степени, минимум для положительного числа — около 2,2 умножить на 10 в минус 308-й степени. Но сколько бы нулей ни стояло до или после запятой, для содержательных цифр всегда остаётся ровно те же 15 позиций.

Почему 0,1 не превращается в ровную дробь

В десятичной системе дробь одна третья не записывается конечным числом цифр — только бесконечным 0,333333 и так далее. С двоичной системой та же история случается уже с самой обычной, на первый взгляд простой десятой долей. Число 0,1 в двоичном виде превращается в повторяющуюся последовательность 0001100110011 и так далее без конца, потому что десятая доля не раскладывается на конечную сумму степеней двойки. Компьютер обязан где-то оборвать эту бесконечную запись, и после обрезки хранимое значение получается чуть больше истинной десятой доли — на величину порядка 0,0000000000000000056, ошибка проявляется уже в восемнадцатом знаке после запятой.

У числа 0,2 внутри та же природа и похожая по масштабу погрешность. Когда Excel складывает два уже слегка неточных значения, результат отклоняется от идеальной 0,3 ровно настолько, чтобы в последнем, семнадцатом знаке появилась лишняя четвёрка: 0,30000000000000004. Разница исчезающе мала для человека и одновременно принципиальна для формулы, которая сравнивает числа побитно.

Что видно на экране и что хранится в ячейке на самом деле

В обычном формате «Общий» Excel показывает короткую, аккуратно округлённую запись вместо всей хранимой цепочки цифр, и разница между видимым и хранимым значением остаётся незаметной для большинства расчётов. Раскрыть её помогает увеличение числа знаков после запятой или переключение на научный формат — именно так был получен пример выше.

У этой истории есть и обратная сторона. Раньше вычитание близких чисел могло оставить на экране крошечный мусор вместо честного нуля: в Excel 95 формула =1,333+1,225-1,333-1,225 выводила минус 2,22044604925031E-16 вместо 0. Начиная с версии 97 программа сама проверяет результаты сложения и вычитания, близкие к нулю, и подчищает такой хвост — сегодня та же формула честно показывает 0.

Где погрешность реально всплывает в таблицах

Пятнадцать значащих цифр одинаково подводят на обоих концах числового диапазона. Если в одной ячейке стоит 1,2 умножить на 10 в 200-й степени, а в соседней — 1 умножить на 10 в 100-й степени, их сумма формулой попросту не отличается от первого слагаемого: младшее число слишком мало на фоне пятнадцати доступных разрядов, чтобы вообще на что-то повлиять. Формула ЕСЛИ, сравнивающая результат с исходным большим числом, вернёт «истина», хотя с точки зрения математики сумма обязана быть больше. Похожая история и с маленькими числами: 0,000123456789012345 плюс 1 даёт на выходе 1,00012345678901 — хвост из последних цифр просто некуда девать.

• проверка контрольной суммы отчёта формулой ЕСЛИ по строгому равенству, где визуально одинаковые числа формула считает разными
• накопление копеечных расхождений при многократном пересчёте НДС через цепочку промежуточных формул
• точный поиск ВПР или ПОИСКПОЗ по вычисленному, а не введённому вручную числу, который не находит существующую строку
• проверка «баланс сошёлся в ноль» в финансовой модели, где на экране пусто, а в ячейке спрятана микроскопическая ненулевая величина

Функция ОКРУГЛ и сравнение с допуском

Функция ОКРУГЛ не просто меняет то, что видно на экране, — она пересчитывает и само хранимое значение до заданного числа знаков. Формула ОКРУГЛ(0,1+0,2;2) даёт ровно 0,3 уже внутри памяти, а не только в отображении, и дальнейшее сравнение с 0,3 срабатывает корректно. Второй рабочий приём — сравнивать значения с допуском вместо строгого равенства: вместо А1=B1 писать АBS(A1-B1)<0,00001. Тогда крошечный хвост в семнадцатом знаке перестаёт мешать формуле увидеть правильный ответ.

Флажок «Задать точность как на экране» — крайняя мера

В параметрах Excel есть отдельная настройка для радикального решения проблемы: Файл, затем Параметры, раздел Дополнительно, блок «При пересчёте этой книги», флажок «Задать точность как на экране». После включения каждое число в книге принудительно обрезается до того количества знаков, которое сейчас видно в ячейке при текущем формате — вся точность за пределами отображаемых разрядов теряется навсегда, уже в самой ячейке, а не только визуально.

Насколько это опасно на практике

Масштаб ошибки крошечный — она прячется в семнадцатом знаке после запятой, а обычные денежные суммы, округлённые до копеек, эту глубину никогда не затрагивают. Опасность растёт там, где формулы сравнивают вычисленные значения между собой напрямую, а не через округление, и особенно в финансовых моделях, построенных на логике «сумма обязана дать ровно ноль». Простой привычки округлять перед сравнением и проверять равенство с допуском хватает, чтобы двоичная арифметика никогда не испортила видимый в таблице результат.