Excel - Почему эта проверка на равенство не проходит?

Question

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

Содержимое ячейки выглядит следующим образом:

A1: 11474.97
A2: -10781.34
A3: =A1+A2
A4: 693.63
A5: =A3=A4

Я использую Office 16.0.6965.2117. Это ошибка, или я упускаю что-то фундаментальное о том, как работает оператор равенства?

Answer 1

Поскольку начальные числа намного больше, чем результат, вы наткнетесь на потерю точности из-за арифметики IEEE 754 с плавающей запятой. В Excel используется стандарт IEEE 754 Double Precision.

Я не буду вдаваться в подробности, а проиллюстрирую проблему. Во-первых, давайте конвертируем все числа в IEEE 754:

  11474.97 ≙ 01000000 11000110 01101001 01111100 00101000 11110101 11000010 10001111

... где 0 - знак (положительный), 10000001100 - показатель степени, а остальное - мантисса.

 -10781.34 ≙ 11000000 11000101 00001110 10101011 10000101 00011110 10111000 01010010

    693.63 ≙ 01000000 10000101 10101101 00001010 00111101 01110000 10100011 11010111

Как видите, большие числа имеют одинаковый показатель степени. Чем больше показатель степени, тем менее точно хранится число:

  11474.97 →  11474.9699999999993451638147235

 -10781.34 → -10781.3400000000001455191522837

    693.63 →    693.629999999999995452526491135

Как вы уже можете видеть из выровненных чисел, способ хранения 693.63 является более точным.

Итак, результат сложения чисел:

01000000 10000101 10101101 00001010 00111101 01110000 10100011 11010000

Давайте снова сравним:

01000000 10000101 10101101 00001010 00111101 01110000 10100011 11010000 – Result
01000000 10000101 10101101 00001010 00111101 01110000 10100011 11010111 – Constant

Так что не совсем так. Вы должны всегда сравнивать числа с плавающей запятой с допуском. Как это:

abs(a - b) < 0.000000001

Answer 2

Я не знаю, почему это происходит, но я использую Office 2010 и получаю ту же проблему.

Чтобы обойти это, я изменил A5 на чтение = ROUND(A3, 2) = ROUND(A4, 2), что устранило проблему, так что я могу только предположить, что есть какая-то незначительная проблема с частями небольших сумм, где-то вызывающими ошибку.

И, как сказал @yass в комментариях, использование оператора IF будет работать лучше и будет держать вас немного более аккуратным, хотя в данном случае это не на 100% важно.