Почему Microsoft Excel неправильно считает в формулах
С помощью Microsoft Excel можно производить очень сложные вычисления, строить различные графики, визуализировать большие объемы информации и многое другое. Большинство пользователей доверяют вычислениям сделанным в Excel на 100 %. Мало кто может допустить такую ситуацию когда сам Excel не правильно произведет вычисления. Практически все знают что ошибки могут быть только по вине самого пользователя, не правильно написанная формула или данные. Совсем не давно ко мне обратилась знакомая, которая сообщила о том что результат вычисления произведенных по формуле в Excel отличается от обычного калькулятора. На первый взгляд все было написано правильно и данные и формула, но потом выяснилось то что проблема была в округлении чисел до сотых. Если честно я впервые столкнулся с такой проблемой, поэтому решил поделиться ею и с вами. Правильней будет сказать что это не ошибка а некоторая особенность Excel которую должен знать каждый.
Ошибка в результате вычисления по формулам в Excel
Давайте обо всем по порядку, необходимо было сделать следующие небольшие вычисления, сложить первые три числа и поделить на три, в общем найти среднее значение. На первый взгляд тут все правильно.
Следующим шагом нужно было получившееся среднее значение умножить на количество. В итоге результат получился такой 37371,33.
Если тоже самое посчитать на калькуляторе то результат буде другой 37371,14.
А вот если проверить первое действие то все правильно.
В итоге получаем что Excel не правильно производить округление числе и при умножении получаем не правильный результат.
Смотрим формат ячейки где расположена первая формула, в ней указано 2 десятичных знака после запятой. А по факту умножение происходить не на 644,33 а на 644,33333 в итоге и получается не правильный результат. Excel не производить правильного округления а просто отображает указанное количество знаков после запятой, а умножение делает на изначально получившееся число т.е на 644,33333.
Исправить ситуацию можно с помощью функции «ОКРУГЛ» она округляет числа по общепринятым правилам математики т.е. если число будет 2,155 то данная функция округлить его в большую сторону 2,16, если число будет 2,154 то в меньшие 2,15.
Добавив данную функцию в первую формулу получилось добиться верного результата. Вот так казалось бы в элементарных вычислениях можно получить не правильный результат.
Эксель неправильно считает сумму, не вычитает, причины
Я много пишу о работе в программе Еxcel, есть и статья о том, как производить суммирование. Но, после этого ко мне стали поступать вопросы, почему Эксель неправильно считает сумму.
Хочу срезу сказать, что программа не может считать неправильно, за каждой такой ошибкой стоит наша невнимательность.
Причин, по которым вы получаете неверный результат, или вовсе не получаете ничего, достаточно много. Сегодня мы постараемся разобраться со всеми проблемами. Начнём, как обычно, с наиболее распространённых ошибок.
Эксель неправильно считает сумму
Если вы производите вычисления, и вдруг заметили, что ответ неверный — внимательно просмотрите все числовые ячейки.
Ошибки допускаемые при подсчёте:
- В столбце используют значения нескольких видов: чистые числа и числа с рублями, долларами, евро. Например, 10, 30, 5 руб, $4 и так далее. Или где-то не целые числа, а дробные;
- В таблице присутствуют скрытые ячейки (строки), которые добавляются к общей сумме;
- Ошибочная формула. Высока вероятность того, что допущена ошибка при вводе выражения;
- Ошибка в округлении. Задайте для всех ячеек, содержащих числа, числовой формат с 3 или 4 знаками после запятой;
- В качестве разделения целого значения используют точку вместо запятой.
Как видите, все причины ошибок — это людской фактор, а программа лишь выполняет то, что заложено в её задачах.
Эксель отказывается подсчитывать сумму
Иногда можно столкнуться с тем, что программа вообще не выполняет каких либо вычислений, даже не показывает промежуточную сумму при выделении столбца.
И опять, таки, всё дело в нашей невнимательности или в неверных настройках. А для получения верных расчётов, необходима правильная настройка Excel для финансовых расчётов.
Давайте пройдём по порядку, по всем пунктам.
Текстовые и числовые значения
Часто копируя и вставляя данные из разных источников в программу Эксель, пользователь сталкивается с проблемой в расчётах.
Причина, чаще всего, лежит на поверхности — Эксель воспринимает введённые данные как текст. Переведите все ячейки с цифрами в числовой формат — всё заработает.
Посмотрите внимательно на ячейки с цифрами, если вы заметили в левом верхнем углу треугольник — то это текстовая ячейка.
Соответственно, программа видит текст и не определяет числа. Для того, чтобы это изменить, нужно выделить ячейки с числами.
Появится значок с восклицательным знаком. Клик по нему — преобразовать в число.
Обычно этого достаточно для того, чтобы программа стала работать. Но, если этого не случилось, двигаемся далее.
Автоматический расчёт формул в Excel
Если программа не хочет считать, проверьте в настройках указание на автоматические расчёты. иногда этот параметр может быть отключен самой программой, в целях экономии мощностей.
Пройдите по пути — файл — параметры — формулы — установите галочку — автоматически.
После изменения данной настройки программа подсчитает всё, что вам нужно.
Сумма не совпадает с калькулятором
Опять же, вся проблема в округлении. Например, при подсчёте на калькуляторе, как правило, считаем 2 знака после запятой.
Получается один результат, а в таблице может быть настроено знаков гораздо больше. Получается расчёт точнее, но он не совпадает с «калькуляторным».
Для того, чтобы проверить настройки, откройте формат ячеек. Нажмите на вкладку — число, выбрав числовой формат. Здесь можно указать требуемое число десятичных знаков.
В заключение хочу сказать, что программа Excel никогда не ошибается, ошибаются люди. Иногда всё дело в настройках программы.
Поэтому, если вы заметили, что Эксель неправильно считает сумму, не спеша проверьте всё то, о чём я сегодня написал для вас.
Почему excel неправильно считает
Здравствуйте, возникла следующая проблема. Стал последнее время замечать в экселе такую проблему как некорректный подсчет формулы.
При растягивании формулы в ячейках, в некоторых ячейках он считает все правильно, а в некоторых появляются несопоставимые цифры или ЗНАЧ!
Использовал функцию Вычислить формулу, чтобы пошагово посмотреть как рассчитывается формула в той или иной ячейке. Так в одних он верно рассчитывает, а в других делает ошибки.
Никто не скажет, в чем загвоздка и как ее устранить?
Здравствуйте, возникла следующая проблема. Стал последнее время замечать в экселе такую проблему как некорректный подсчет формулы.
При растягивании формулы в ячейках, в некоторых ячейках он считает все правильно, а в некоторых появляются несопоставимые цифры или ЗНАЧ!
Использовал функцию Вычислить формулу, чтобы пошагово посмотреть как рассчитывается формула в той или иной ячейке. Так в одних он верно рассчитывает, а в других делает ошибки.
Никто не скажет, в чем загвоздка и как ее устранить? Reveal31
Сообщение Здравствуйте, возникла следующая проблема. Стал последнее время замечать в экселе такую проблему как некорректный подсчет формулы.
При растягивании формулы в ячейках, в некоторых ячейках он считает все правильно, а в некоторых появляются несопоставимые цифры или ЗНАЧ!
Использовал функцию Вычислить формулу, чтобы пошагово посмотреть как рассчитывается формула в той или иной ячейке. Так в одних он верно рассчитывает, а в других делает ошибки.
Никто не скажет, в чем загвоздка и как ее устранить? Автор — Reveal31
Дата добавления — 15.01.2017 в 01:11
Почему excel неправильно считает
Помогите пожалуйста с проблемой.
Сделал в 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
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 21:40:09
Проверьте округление
13.02.2010 22:06:23
Да, числовой, пробовал другие форматы и количество знаков, ничего не получается.
Сообщений: 60949 Регистрация: 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 КБ)
Сообщений: 60949 Регистрация: 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)) — массивом
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
13.02.2010 22:52:13
Подозрения подтвердились — попробуйте для всех ячеек, где числа, установить числовой формат с тремя знаками после запятой, и гляньте на результат. Excel, несмотря на Ваше утверждение, считает правильно 🙂
13.02.2010 23:22:46
Большое спасибо за помощь, это меня калькулятор сбил с толку со своими 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
Не те числа смотрите — они получаются от перемножения других чисел.
Посмотрите, что в ячейках столбца Е: меню Формат-Ячейки-Числовой-Число_десятичных-знаков-4.