Пределы точности расчетов в Excel и обрезка длинных чисел

Как MS Excel обманывает нас при расчетах

А вы знаете, что даже такая точная и надежная штука как MS Excel запросто может вас обмануть при расчетах, да притом так, что и не догадаешься об ошибке из-за того, что «сюрприз» этот работает только при работе с очень большими числами? А все потому, что существует такая особенность, как пределы точности расчетов в Microsoft Excel.

Правда «обманывает» нас Excel ну совсем не со зла. Просто у него есть определенные ограничения. И об этих ограничениях лучше знать заранее, чтобы избежать ошибок, там где они не ожидаются и совсем неоднозначны.

Ограничения связанные с пределами точности расчетов, о которых я хочу рассказать — это, конечно же не ошибка, а скорее допущение разработчиков. Своего рода архитектурное ограничение, с которым пользователям хочешь — не хочешь придется мириться, но о котором просто необходимо знать любому пользователю Excel.

Длинные числа MS Excel записывает весьма затейливо, однако в строке формул четко видно — это все тоже число, которое я ввел: 1234567890123

Как MS Excel хранит данные, зачем ему предел точности расчетов и почему он обрезает длинные числа

Microsoft Excel делает расчеты не абы как, складывая числа «в лоб», а на основе переменной с плавающей запятой. Это значит, что данные хранятся у него в ячейках не в виде простого числа, а в особом виде, а именно в виде дроби, где одна цифра стоит как целая часть, а остальные составляют дробную часть. Эта дробь умножается на 10 в n-ной степени.

Например, число 12345 будет храниться и обрабатываться (участвовать в расчетах) в Excel’е как 1.2345E4 или точнее как 1.2345*10^4. Правда с маленькими числами это незаметно, а вот если написать  что-то типа: 1234567890123, то «Эксель» сразу же визуально «сократит» эту запись до 1,23457E+12. Выглядит это немного странно для неискушенных пользователей, но исправлено работает для экономии памяти и вычислительных мощностей вашего компьютера.

И все бы хорошо, но в этом методе есть одна особенность… Дело в том, что в общей сложности в этой дроби система проигнорирует все цифры, после пятнадцатого разряда. Позвольте объяснить на примере.

Опа… а если взять ещё более длинное число, MS Excel его обрежет «из экономии». Последние числа программа заменила нулями

Если ввести в ячейку Excel число 123456789012345678, то после нажатия Enter система автоматически преобразует его у себя в 1.23456789012345E17. И все вроде бы правильно, но на самом деле при обратном преобразовании это число превратится уже в 123456789012345000. То есть будет на 678 отличается от оригинала!

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

Как избежать обрезки длинных чисел в MS Excel

Уверен, что читатель на этом месте просто пожмет плечами и задумается — «хм, а когда я в последний раз умножал на 2 двадцатизначное число?». Но, конечно я с ним не соглашусь.

• Во-первых, есть же не только бухгалтеры и офисные работники, но и физики, и астрономы и прочие, которым приходится работать с БОЛЬШИМИ числами. И… видимо Excel им для тих целей не подойдет.
• Во-вторых, и этим самым бухгалтерам и офисным работникам тоже есть о чем волноваться. Ведь мы вводим в Excel не только числа для расчетов, но и различные идентификаторы, порою очень длинные.

Например, номер счета в банках России — двадцатизначный. Если попробовать ввести его в ячейку по умолчанию, то Excel незаметно сотрет из него последние 5 цифр (точнее заменит их нулями). Опочки! То же самое с кодом бюджетной классификации (КБК) или длинными номенклатурными номерами.

Меняем тип поля на «Текстовый» и вводим число любой длины. Победа? Ну….

К счастью, разобраться с этой проблемой не сложно (при условии, что вы о ней вообще знаете). Чтобы избежать ошибок связанных с обрезкой длинных чисел, достаточно изменить тип поля на текстовое (правда вы не сможете проделывать с ними математические операции, но вряд ли 99,9% пользователей табличного редактора действительно перемножают КБК…)

Такие дела. Кто предупрежден — тот вооружен, одним словом. Описанная здесь проблема с пределом точности MS Excel — хороший пример того, что решить проблему можно только в том случае, если о её существовании известно.