Как избежать ошибок округления в результатах формул (Microsoft Excel)

Ник отмечает, что формула «= 0,28100-INT (0,28100) = 0» возвращает значение «Ложь», хотя это очевидно верно. Он считает, что проблема связана с округлением и тем, как компьютер использует двоичную арифметику и т. Д. Ник использует формулу как часть более крупного оператора ЕСЛИ, и он предполагает, что в Excel есть другие ошибки округления, которые могут дотянуться до него и укусить. . Он задается вопросом, есть ли простой способ избежать подобных ловушек формул.

Прежде чем искать простые способы избежать этого типа проблем, важно понять, почему проблема существует. Как отмечает Ник, это действительно связано с двоичной арифметикой и тем фактом, что Excel округляет информацию. За кадром Excel всегда округляет информацию до 15 цифр. Рассмотрим расчет, с которым работает Ник:

Когда Excel впервые вычисляет это значение, приоритет, за которым следует Excel, вычисляет его следующим образом:

Обратите внимание на дополнительный набор скобок. Результатом всего, что находится слева от этого последнего знака равенства, будет 3.55E-15, что означает, что вы получите следующую (от руки) формулу:

Это явно неверно, поэтому вы получаете возвращаемое значение False. Есть несколько способов «исправить» эту ситуацию. В этом случае, возможно, самый простой способ — просто изменить формулу, чтобы избавиться от необходимости сравнивать с нулем:

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

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

Вместо этого вы заставляете Excel округлять значения — все значения, с которыми вы работаете, — до любого уровня точности, который вам нужен для сравнения.

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

Это возвращает True, как и следовало ожидать.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (8143) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Avoiding_Rounding_Errors_in_Formula_Results [предотвращение ошибок округления в результатах формулы].