Возмутительная погрешность при суммировании в Excel 2010 !

При суммировании в Microsoft Excel 2010 функцией СУММ или методом простого суммирования чисел в столбце , полученных умножением двух констант , выскакивает невероятная для такой простой операции погрешность - 0,02 сотых на каждых 12 слагаемых!
Я в шоке! Даже самый простой китайский калькулятор считает лучше.
Можете проверить - посмотрите файл.

Вложения

Проблема вычислений.rar (9.3 Кб, 218 просмотров)

0

А если взять три знака после запятой?
Все точно.

Вложения

Проблема вычислений.xls (25.5 Кб, 208 просмотров)

0

Сообщение от OldMan1 Я в шоке!

А Вы не возмущайтесь

Давно известно, что калькулятор считает точнее Excel, когда речь идёт о не целых числах. На то он и калькулятор, что бы считать, а Excel - это электронная таблица, которая умеет гораздо больше калькулятора, но имеет свои недостатки

Вот с других форумов (к сожалению здесь ссылки давать нельзя):

Фактически источником ошибки является ошибка округления. Данная ошибка является следствием дефекта арифметики плавающей точки и того факта, что чаще всего десятичные дроби являются повторяющимися долями в двоичной системе счисления. Такие числа не могут представляться в конечном количестве битов. В связи с этим текстовое округление получается не всегда точным, т.к. большинство компьютеров подбирает последние цифры дробной части, исходя из ближайшего (с наименьшей разницей) эквивалента. Некоторые компьютеры не производят округления, а просто обрезают (выключают) последние биты, получая результирующую ошибку, правильно называемую ошибкой округления (в противоположность ошибке усечения, когда усекается расширение ряда). Для получения дополнительной информации обратитесь к Introduction to Numerical Methods (введение в числовые методы) авторов Peter A. Stark, Macmillian Company, 1970

...я готов объяснить, почему 3.1-3=0.1, а 4.1-4<0.1 Основа проблемы кроется именно в особенностях работы с плавающей запятой. Итак: Для начала - вот формат числа с плавающей запятой с двойной точностью (именно он используется в Excel). И "забудем" для простоты, что байты идут от младшего старшему. Так выглядит +1: +1: 0 011 11111111 0000 00000000 00000000 00000000 00000000 00000000 00000000 Первый "0" - это знаковый бит: 0 - "+", 1 - "-" Затем группа из 11 битов - порядок числа. Она увеличена (для данного типа формата) на величину 3ffh, чтобы было удобно отображать отрицательные степени экспоненты. Т.е., фактически, порядок числа +1 равен 0, что и понятно. Не очень ясно, как интерпретировать 52 последующих нуля. Тут нужно сказать, что в формате чисел с плавающей запятой используется т.н. "нормализованная" запись чисел, то есть когда значащий разряд целой части мантиссы не равен нулю - в общем случае, или равен "1" в двоичной системе исчисления; экспонента при этом соответствующим образом изменяется. Но если старший разряд - всегда "1", то зачем его хранить? Его и не хранят для экономии места, а лишь подразумевают: 1.0000 00000000 00000000 00000000 00000000 00000000 00000000 = 1.0e0 Посмотрим теперь как выглядят другие наши "подопытные": +0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10011010 (обратите внимание на выделенные жирным шрифтом биты последнего байта, они сыграют потом ключевую роль!) +3.1: 0 100 00000000 1000 11001100 11001100 11001100 11001100 11001100 11001101 +3.0: 0 100 00000000 1000 00000000 00000000 00000000 00000000 00000000 00000000 Вычтем одно число из другого и получаем (порядок в вычитании не участвует, только мантисса и "подразумеваемая" единица): +3.1: 1.1000 11001100 11001100 11001100 11001100 11001100 11001101 +3.0: 1.1000 00000000 00000000 00000000 00000000 00000000 00000000 --------------------------------------------------------------------------------- +0.1: 0.0000 11001100 11001100 11001100 11001100 11001100 11001101 Целая часть мантиссы оказалась равной нулю, поэтому выполним нормализацию - сдвинем ее влево на 5 позиций, дополнив справа нулями(!), а порядок, соответственно, уменьшим на 5 - было 400h, стало 3fbh: +0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10100000 Обратите внимание, число на долю, на "1" в 47м разряде, но больше, чем 0.1, поэтому при округлении мы и видим в ячейке Excel то, что нам нужно! Проделаем тоже самое с 4.1-4.0 (но уже без дополнительных комментариев): +4.1: 0 100 00000001 0000 01100110 01100110 01100110 01100110 01100110 01100110 +4.0: 0 100 00000001 0000 00000000 00000000 00000000 00000000 00000000 00000000 --------------------------------------------------------------------------------- +0.1: 0 011 11111011 1001 10011001 10011001 10011001 10011001 10011001 10000000 В 47м разряде "0", а, следовательно, мы получили меньше, чем 0.1, что нам Excel честно и показывает при большой разрядности. Почему он не поступает столь же честно при превышении? А посмотрите внимательнее - где больше отличие от "эталонного" 0.1? Во втором случае, в первом ему просто не хватает точности. Если проделать все это с последующими числами, то будет то же самое, только хуже Так что здесь не проблема Excel, а родимое пятно двоичной арифметики. Хотя, конечно, доля вины дяди Билла в этом тоже есть. Можно было бы воспользоваться иным представлением чисел. Однако, я не думаю, чтобы в Quattro или Lotus решение было бы иным. А теперь представьте, что дело идет не о копейках, а о миллионах и миллиардах рублей или долларов? Вот тут-то эта мелочь и вылезет. И о подобных случаях округлений писали.

0

Сообщение от OldMan1 Можете проверить - посмотрите файл.

Какой результат должен получиться от суммы следующих слагаемых:
=326,527+326,527+326,527+333,12+333,12+3 33,12+333,12+333,12+333,12+341,795+341,7 95+348,388 ?
Именное эти числа у Вас находстся в ячейках C3:C14 после вычислений, а не
=326,53+326,53+326,53+333,12+333,12+333, 12+333,12+333,12+333,12+341,8+341,8+348, 39
т.к 941,000*0,347=326,527, а не 326,53 (можете проверить это на калькуляторе)
Отсюда и погрешность.

Чтобы Excel считал "как на калькуляторе" (т.е. округлял промежуточные вычисления до двух знаков после запятой, и только потом из суммировал), можно воспользоваться одним из следующих способов:
1. используйте формулы в ячейках C3:C14: =ОКРУГЛ(A3*B3;2)
2. используйте окончательную формулу суммирования в ячейке С15: =СУММПРОИЗВ(ОКРУГЛ(C3:C14;2))
3. установите в параметрах Excel "Точность как на экране" (не рекомендую)

Добавлено через 4 минуты
PS: какой результат должен получится "на калькуляторе" если 3 раза сложить по 1/3:
=1/3+1/3+1/3

0,99 или 1,00?

0

Для начала немного о себе:
я окончил механико-математический факультет Одесского государственного университета по специальности прикладная математика в далёком 1985 году. И уж поверьте мне, я очень много чего знаю об арифметических операциях в разных системах исчисления, об окрулении чисел, умею складывать числа в столбик на бумаге. Это первое.
Второе: уже очень-очень много лет я пользовал Excel 2003 для работы с электронными таблицами, и уж опять поверьте мне , что я умею пользоваться функциями, создавать свои функции, устанавливать точность вычислений и т.д и т.п.
Третье: файл, который я привел в качестве примера, это бухгалтерская таблица, и все рассуждения об округлении и трёх разрядах после запятой разбиваится о простое рассуждение:
проверяющий форму человек не должен ничего знать и уметь, кроме умения складывать в столбик на бумаге числа, которые он видит перед собой. А если он сложит эти числа на бумаге, то получит результат, на 0,02 сотых больше, чем напечатано на бумаге Excelem 2010. КЛЯНУСЬ!:с табличными вычислениями в Excel 2003 ничего подобного никогда не происходило.
Вывод: для того, чтобы не выглядеть идиотом перед прверяющим-принимающими бух.отчётность и не тратить время на перепроверку я буду вынужден удалить дебильный Excel 2010 и найти где-то старый, сделанный не ногами, Excel 2003 .
Ответ на вопрос "а зачем тогда переходили на Excel 2010 ?" - пропустил обновление, которое начало приставать с требованиями о регистрации.

0

Сообщение от OldMan1 с табличными вычислениями в Excel 2003 ничего подобного никогда не происходило

Вы ошибаетесь.

0

Включили "точность, как на экране- ткнули носом, пристыдили. Извините за волну.

0

Сообщение от OldMan1 При суммировании в Microsoft Excel 2010 функцией СУММ или методом простого суммирования чисел в столбце , полученных умножением двух констант , выскакивает невероятная для такой простой операции погрешность - 0,02 сотых на каждых 12 слагаемых! Я в шоке! Даже самый простой китайский калькулятор считает лучше. Можете проверить - посмотрите файл.

Погрешность как раз у калькулятора из-за округления слагаемых до сотых, а если это копейки, то надо округлять явно.

Добавлено через 1 минуту

Сообщение от Serge 007 Давно известно, что калькулятор считает точнее Excel, когда речь идёт о не целых числах. На то он и калькулятор, что бы считать, а Excel - это электронная таблица, которая умеет гораздо больше калькулятора, но имеет свои недостатки

Как раз давно известно, что экзел считает точнее лучших калькуляторов: 20 знаков больше 12-ти.

Добавлено через 6 минут

Сообщение от OldMan1 это бухгалтерская таблица, и все рассуждения об округлении и трёх разрядах после запятой разбиваится о простое рассуждение: проверяющий форму человек не должен ничего знать и уметь, кроме умения складывать в столбик на бумаге числа, которые он видит перед собой.

Бред. Любой бухгалтер должен уметь округлять и знать, что все денежные суммы округляются до сотых, чего в файле нет, они до сотых только отображены. Так что не умеешь ты задавать точность вычислений. Для того, чтоб её задать, надо не формат числа ставить, а набрать формулу =округл(A3*B3; 0,01), или =округл(A3*B3; 2) (в зависимости от версии экзела) вместо =A3*B3 и растянуть.

0

Сообщение от OldMan1 КЛЯНУСЬ!:с табличными вычислениями в Excel 2003 ничего подобного никогда не происходило.

У меня как раз 2003 и всё то же самое.

Миниатюры

 

0

Сообщение от taras atavin Как раз давно известно, что экзел считает точнее лучших калькуляторов: 20 знаков больше 12-ти.

Поясните. Что такое 20 знаков и что такое 12.
Excel ни в одну из этих категорий не попадает.

0

Сообщение от Serge 007 ...я готов объяснить, почему 3.1-3=0.1, а 4.1-4<0.1 Давно известно, что калькулятор считает точнее Excel, когда речь идёт о не целых числах. На то он и калькулятор, что бы считать, а Excel - это электронная таблица, которая умеет гораздо больше калькулятора, но имеет свои недостатки

Уважаемый Serge 007, а что делать-то? Пихать в абсолютно все формулы ОКРУГЛ()? Тоже ведь не вариант.

Прошу прощения за некропостинг, не посмотрел на дату предыдущего ответа.

0

Сообщение от Александр Смит Пихать в абсолютно все формулы ОКРУГЛ()? Тоже ведь не вариант.

...

Сообщение от OldMan1 Включили "точность, как на экране- ткнули носом, пристыдили. Извините за волну.

0

Ну, как вариант для некоторых ситуаций. При условии, что эта настройка сохраняется в файле. Спасибо.

0

Здравствуйте, всем.

Exel 2007 добавляет при некоторых операциях 1 в 15-ом разряде.
Это глюк моего Ехеля, или это так надо?
пример во вложении:

59,75 - 59,85 = -0,100000...001

Потом результаты используются в логических выражениях , из-за этого портит мне всю картину.

Может кто подскажет, в чем моя беда?
Заранее спасибо

Вложения

Книга1.xlsx (9.6 Кб, 33 просмотров)

Формат ячеек измените на числовой и всё будет ок. Не благодарите

0