Почему в экселе считает неправильно копейки
Перейти к содержимому

Почему в экселе считает неправильно копейки

  • автор:

Задание точности округления

Часто можно предотвратить влияние ошибок округления с плавающей запятой на работу, установив параметр Точность как отображаемое перед применением числового формата к данным. Этот параметр заставляет значение каждого числа на листе быть с точностью, отображаемой на листе.

Примечание: Использование параметра «Точность как отображаемое » может привести к совокупному эффекту вычислений, что может сделать данные все более неточными с течением времени. Используйте этот параметр, только если вы уверены, что отображаемая точность будет поддерживать точность данных.

  1. Выберите Файл >Параметры. В Excel 2007 нажмите кнопку Microsoft Office

рядом с полем Число.

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

Почему в экселе считает неправильно копейки

Помогите пожалуйста с проблемой.
Сделал в Excel накладную, все считает правильно, а вот там где итого считает неправильно 467 055,68
93 411,14
560 466,81

13.02.2010 21:39:59

Помогите пожалуйста с проблемой.
Сделал в Excel накладную, все считает правильно, а вот там где итого считает неправильно
467 055,68+
93 411,14=
560 466,81

Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 21:40:09
Проверьте округление
13.02.2010 22:06:23
Да, числовой, пробовал другие форматы и количество знаков, ничего не получается.
Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 22:12:42
Покажите нам этот столбик цифр. В файле.
Пользователь
Сообщений: 732 Регистрация: 01.01.1970
13.02.2010 22:18:27

Запишите формулу так: =ОКРУГЛ(467 055,68;2)+ОКРУГЛ(93 411,14;2), или округляйте до двух знаков получаемые по формулам числа.
С уважением, Александр.

13.02.2010 22:29:52
Покажите нам этот столбик цифр. В файле.
Прикрепленные файлы

  • post_99607.png (18.04 КБ)

Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 22:45:44

Юрий, мы ведь разговор ведём про Excel? Нет ли у Вас возможности приложить именно xls? Зачем нам «фотография»?

13.02.2010 22:45:48
Прикрепленные файлы

  • post_99612.xls (20.5 КБ)

13.02.2010 22:48:36
=СУММ(ОКРУГЛ(D2:D69;2)) — массивом
Сообщений: 60996 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 22:52:13

Подозрения подтвердились — попробуйте для всех ячеек, где числа, установить числовой формат с тремя знаками после запятой, и гляньте на результат. Excel, несмотря на Ваше утверждение, считает правильно 🙂

13.02.2010 23:22:46

Подозрения подтвердились — попробуйте для всех ячеек, где числа, установить числовой формат с тремя знаками после запятой, и гляньте на результат. Excel, несмотря на Ваше утверждение, считает правильно 🙂

Большое спасибо за помощь, это меня калькулятор сбил с толку со своими 82коп.

14.02.2010 09:47:04
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
15.02.2010 03:07:18

То неправильно считает, то не помогает 🙂
Формула массива, заканчиваете ввод формулы одновременным нажатием Ctrl-Shift-Enter, по краям формулы должны появиться фигурные скобки.
Не хотите так, применяйте
=СУММПРОИЗВ(ОКРУГЛ(D2:D69;2)*1)

15.02.2010 20:12:20

То неправильно считает, то не помогает 🙂
Формула массива, заканчиваете ввод формулы одновременным нажатием Ctrl-Shift-Enter, по краям формулы должны появиться фигурные скобки.
Не хотите так, применяйте
=СУММПРОИЗВ(ОКРУГЛ(D2:D69;2)*1)

Спасибо, функцию вставил, но это тоже самое, что в параметрах отметить «точность как на экране»

Пользователь
Сообщений: 7 Регистрация: 01.01.1970
14.11.2011 10:16:59

Добрый день. Сижу и не могу сделать так, чтобы формула считала правильно. Видимо все происходит из за округления. Пытаюсь сам сделать не получается. Подскажите как правильно ввести формулу. В результате сложения сумма получилась 858,85, но из чисел видно что сумма на конце должна быть 858,86. файл прикрепляю

Прикрепленные файлы

  • post_277684.xls (48 КБ)

Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
14.11.2011 10:26:30

. но из чисел видно что сумма на конце должна быть 858,86
Не те числа смотрите — они получаются от перемножения других чисел.
Посмотрите, что в ячейках столбца Е: меню Формат-Ячейки-Числовой-Число_десятичных-знаков-4.

Excel неправильно считает. Почему?

Часто при вычислении разницы двух ячеек в Excel можно видеть, что она не равна нулю, хотя числа одинаковые. Например, в ячейках A1 и B1 записано одно и тоже число 10,7 , а в C1 мы вычитаем из одного другое:
Разность чисел
И самое странное то, что в итоге мы не получаем 0! Почему?
И еще одна ситуация — я её называю «Куда пропала копейка?!». На скрине ниже изображена достаточно частая ситуация в Excel:
Откуда лишняя копейка?
Слева числа получены формулами и сложены в единую сумму при помощи функции СУММ (SUM) , а правее они же посчитаны на калькуляторе. Как видно — суммы различаются на копейку. На самом деле различаться суммы в итоге могут и более чем на копейку.

Разберем причины подобного поведения и главное — как эту проблему можно решить.

Формат ячеек

Причина очевидная — формат ячеек
Сначала самый очевидный ответ: если идет сравнение значений двух ячеек, то необходимо убедиться, что числа там действительно равны и не округлены форматом ячеек. Например, если взять те же числа из примера выше, то если выделить их -правая кнопка мыши —Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 7:

Теперь все становится очевидным — числа отличаются и были просто округлены форматом ячеек. И естественно не могут быть равны. В данном случае оптимальным будет понять почему числа именно такие, а уже потом принимать решение. И если уверены, что числа надо реально округлять до десятых долей — то можно применить в формуле функцию ОКРУГЛ:
=ОКРУГЛ( B1 ;1)-ОКРУГЛ( A1 ;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0

Но как быть, если таких формул десять, двадцать и более? Не прописывать же вручную для каждой формулу ОКРУГЛ. Можно использовать одну из функций моей надстройки MulTEx, которая двумя кликами мыши все сделает за вас. Достаточно будет выделить все ячейки с «неверными» числами, которые надо округлить, перейти на вкладку MulTEx -группа Ячейки/ДиапазоныЯчейкиФункции и выбрать Округлить (см. подробное описание функции). Указать количество знаков 2 и все. Там, где были формулы, будет добавлена функция ОКРУГЛ, а где реальные числа — будет произведено округление.

Но есть и более кардинальный метод , если сторонние утилиты и надстройки использовать нельзя или нет желания:

  • Excel 2007:Кнопка офисПараметры Excel (Excel options)Дополнительно (Advanced)Задать точность как на экране (Set precision as displayed)
  • Excel 2010:Файл (File)Параметры (Options)Дополнительно (Advanced)Задать точность как на экране (Set precision as displayed)
  • Excel 2013 и выше:Файл (File)Параметры (Options)Дополнительно (Advanced)Задать указанную точность (Set precision as displayed)

Это запишет все числа на всех листах книги ровно так, как они отображены форматом ячеек. Данное действие лучше выполнять на копии книги, т.к. оно приводит все числовые данные во всех листах книги к тому виду, как они отображены на экране. Т.е. если само число содержит 5 десятичных разрядов, а форматом ячеек задан только 1 — то после применения данной опции число будет округлено до 1 знака после запятой. При этом отменить данную операцию нельзя, если только не закрыть книгу без сохранения.

Причина программная
Но нередко в Excel можно наблюдать более интересный «феномен»: разница двух дробных чисел, полученная формулой не равна точно такому же числу, записанному напрямую в ячейку. Для примера, запишите в ячейку такую формулу:
=10,8-10,7=0,1
по виду результатом должен быть ответ ИСТИНА (TRUE) . Но по факту будет ЛОЖЬ (FALSE) . И этот пример не единственный — такое поведение Excel далеко не редкость при вычислениях. Его можно встретить и в менее явной форме — когда вычисления основаны на значении других ячеек, которые тоже в свою очередь вычисляются формулами и т.д. Но причина во всех случаях одна.

15 значащий разряд

Почему с виду одинаковые числа не равны?
Сначала разберемся почему Excel считает приведенное выше выражение ложным. Ведь если вычесть из 10,8 число 10,7 — в любом случае получится 0,1 . Значит где-то по пути что-то пошло не так. Запишем в отдельную ячейку левую часть выражения: =10,8-10,7 . В ячейке появится 0,1 . А теперь выделяем эту ячейку -правая кнопка мыши —Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 15:

и теперь видно, что на самом деле в ячейке не ровно 0,1 , а 0,100000000000001 . Т.е. в 15 значащем разряде у нас появился «хвостик» в виде лишней единицы.
А теперь будем разбираться откуда этот «хвостик» появился, ведь и логически и математически его там быть не должно. Рассказать я постараюсь очень кратко и без лишних заумностей — их на эту тему при желании можно найти в интернете немало.
Все дело в том, что в те далекие времена(это примерно 1970-е годы), когда ПК был еще чем-то вроде экзотики, не было единого стандарта работы с числами с плавающей запятой(дробных, если по простому). Зачем вообще этот стандарт? Затем, что компьютерные программы видят числа по своему, а дробные так вообще со статусом «все сложно». И при этом одно и то же дробное число можно представить по-разному и обрабатывать операции с ним тоже. Поэтому в те времена одна и та же программа, при работе с числами, могла выдать различный результат на разных ПК. Учесть все возможные подводные камни каждого ПК задача не из простых, поэтому в один прекрасный момент началась разработка единого стандарта для работы с числами с плавающей запятой. Опуская различные подробности, нюансы и интересности самой истории скажу лишь, что в итоге все это вылилось в стандарт IEEE754. А в соответствии с его спецификацией в десятичном представлении любого числа допускаются ошибки в 15-м значащем разряде. Что и приводит к неизбежным ошибкам в вычислениях. Чаще всего это можно наблюдать именно в операциях вычитания, т.к. именно вычитание близких между собой чисел ведет к потере значимых разрядов.
Подробнее про саму спецификацию так же можно узнать в статье Microsoft: Результаты арифметических операций с плавающей точкой в Excel могут быть неточными
Вот это как раз и является виной подобного поведения Excel. Хотя справедливости ради надо отметить, что не только Excel, а всех программ, основанных на данном стандарте. Конечно, напрашивается логичный вопрос: а зачем же приняли такой глючный стандарт? Я бы сказал, что был выбран компромисс между производительностью и функциональностью. Хотя возможно, были и другие причины.

Куда важнее другое: как с этим бороться?
По сути никак, т.к. это программная «ошибка». И в данном случае нет иного выхода, как использовать всякие заплатки вроде ОКРУГЛ и ей подобных функций. При этом ОКРУГЛ здесь надо применять не как в было продемонстрировано в самом начале, а чуть иначе:
=ОКРУГЛ( 10,8 — 10,7 ;1)=0,1
=ROUND(10.8-10.7,1)=0,1
т.е. в ОКРУГЛ мы должны поместить само «глючное» выражение, а не каждый его аргумент отдельно. Если поместить каждый аргумент — то эффекта это не даст, ведь проблема не в самом числе, а в том, что с ним происходит дальше(в примере — вычитание). И в данном случае 10,8 и 10,7 уже округлены до одного разряда и понятно, что округление отдельно каждого числа ничего не изменит. Здесь и еще один нюанс — вполне достаточно, зная эту особенность, округлить до 14 знаков и проблема тоже исчезнет. В чем здесь плюс — как правило очень мало задач для решения требуют 15 знаков после запятой и этот 15-ый можно просто «игнорировать», но при этом не убирать более значимые разряды(ведь не всегда известно до какого разряда можно округлять без потерь):
=ОКРУГЛ( 10,8 — 10,7 ;14)=0,1
=ROUND(10.8-10.7,14)=0,1

Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
=((10,8*1000)-(10,7*1000))/1000=0,1

Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора 🙂

P.S. В последних версиях Excel(на моем 365, например) подобный «глюк» может проявляется уже даже не в 15-м значащем разряде, а куда раньше — даже на 11-м. Это следует учитывать при применении округлений выражения.

Почему в эксель сумму считает неправильно?

очевидно формула с ошибкой, проверь.
пара советов:
* чтобы подсчитать сумму в столбце не нужно ничего выделять сначала — ставишь курсор в пустую ячейку внизу столбца и жмешь «автосумма»
* если числа в столбце разделены пустыми ячейками, тогда придется ввести диапазон вручную
* для проверки и быстрого нахождения суммы используй строку состояния. если выделить диапазон, содержащий более 1 числа, внизу в правой половине строки можно увидеть: в екселе 1997-2003 на выбор сумму/среднее/макс/мин/?? (изменить правой кнопкой) ; в екселе 2007 сразу несколько этих функций

Отличия функций суммирования: сумм () и промежуточные итоги () и чем лучше пользоваться.
Ссылка на это видео https://youtu.be/JCZC2OIYw7o

Abram Pupkin, благодарен. Впервые с этим столкнулся. Думал у меня office заражён, проверил нормально. Собрался уже переустанавливать, типа глюки, но вот всё получилось.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *