Excel - это просто!

A+ A A-

Расчеты в Excel - пределы точности

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

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

В чем суть, и как это работает

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

Например, число 12345 будет храниться и обрабатываться (участвовать в расчетах) в Excel'е как 1.2345E4 или 1.2345*10^4. Это в целом правильно, и, казалось бы, не о чем беспокоиться... Пока вы не задумаете астрономические расчеты.

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

Если ввести число 123,456,789,012,345,678.00 в Excel, то после нажатия Enter система автоматически преобразует его у себя в 1.23456789012345E17. Что на самом деле равно 123,456,789,012,345,000.00. То есть на 678 отличается от оригинала. То же самое касается точных дробей. Они тоже будут обрезаться таким образом, чтобы общее число разрядов в числе было меньше 15. Это ограничение введено как необходимость, для того чтобы ограничить ненужную точность при огромных вычислениях с делением или дробными степенями (корнями), например модели дисконтированных денежных потоков при приведении точно будут вызывать огромное количество чисел с большим количеством дробных знаков (особенно, если модель приводится не по годам, а по полугодиям, кварталам или месяцам).

И что?

Предвкушая вопрос "Да кому вообще надо считать с такой точностью", попробую возразить.

Во-первых, есть физики, астрономы и прочие, которым, видимо, придется отказаться от Excel в пользу MathCAD из-за этого дурного ограничения.

Во-вторых, и простым офисным работникам есть о чем волноваться. Да, мы обычно не используем Excel для расчетов с квадриллионами (если мы не живем в Зимбабве, конечно). Но нам надо быть очень осторожными при выборе типа переменной.

Пример. Номер счета в банках России - двадцатизначный. Если попробовать заколотить его в числовое поле, то вы потеряете последние пять цифр (вместо них будут нули). Неприятный получится сюрприз, правда? Ваша компания может использовать длинные номенклатурные номера. Опять-таки, не хотелось бы потерять несколько цифр в конце. КБК (код бюджетной классификации) не сохраняется целиком? Все о том же - плавающая запятая...

Как избежать ошибки

Чтобы избежать этих ошибок достаточно изменить поле на текстовое (мало, кому нужно производить расчеты, скажем, с номерами банковских счетов - умножать, делить, складывать, вычитать, возводить в степень...)

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

Последние новости

Как быстро удалить тысячи строк из громадной таблицы по филь…

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

О нас

Напишите мне edward@youcanexcel.ru

О проекте

Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.

Веб-сайт оптимизирован под разрешение не менее 1024x768.

Сайт отражается некорректно? Должно быть вы пользуетесь Internet explorer. Установите себе хороший браузер!