Excel SUM не равен введенному вручную значению

Question

На скриншоте выше в Excel я открыл CSV-файл денежных транзакций из моего банка. Дата транзакции находится в столбце A , сумма в B и расчетный баланс банка в D Я вычисляю свой собственный текущий баланс в столбце F ( =SUM($A$2:An)), а в столбце H выделяется любое несоответствие ( =IF( Dn = "", TRUE, Fn=Dn ) , к сожалению, как на скриншоте видно, что Excel говорит, что £898.15 не равно £898.15 .

Я хотел посмотреть, произошла ли ошибка округления, поэтому в столбце K я установил формулу =Dn-Fn а затем расширил число отображаемых десятичных разрядов. Который показывает минутную ошибку -0.00000000000272848 .

Вопрос 1. Откуда возникает эта незначительная ошибка и почему Excel внезапно считает значения в строке 206 разными?

Вопрос 2. Другие строки имеют идентичное расхождение (например, 188 и 184 против строки 206), так почему же Excel внезапно жалуется?

Вопрос 3: Почему Excel не рассматривает валютные значения как целочисленные значения, а не с плавающей запятой, поэтому не должно быть никакой ошибки вообще.

Answer 1

Когда вы вводите числа в Excel, они преобразуются во внутреннее двоичное представление, например, 8.25 переводится в 1000.01 , а 8.26 становится 1000.01000010100011110101110000101000 .

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

Таким образом, тест должен быть =IF(ABS(Calculated-Real)<0.01,"OK","Not Equal") . Пока мы не получим десятичный компьютер, вы получите эту ошибку в любом двоичном цифровом компьютере.

Answer 2

1. незначительная ошибка, вероятно, из-за округления. Вы видите две десятичные цифры только в столбцах банка, но, возможно, их больше, попробуйте увеличить количество десятичных знаков там же.
2. Минимальная разница, которая должна быть определена с помощью Excel, не является абсолютной величиной, но она связана с величиной сравниваемых чисел. Вы можете прочитать больше здесь
3. Причина использования чисел с плавающей запятой, как правило, заключается в большей гибкости охватываемого диапазона, но я не думаю, что это важно сейчас, поскольку мы не можем это изменить.

Answer 3

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