Эксель неправильно считает сумму, не вычитает, причины
Я много пишу о работе в программе Еxcel, есть и статья о том, как производить суммирование. Но, после этого ко мне стали поступать вопросы, почему Эксель неправильно считает сумму.
Хочу срезу сказать, что программа не может считать неправильно, за каждой такой ошибкой стоит наша невнимательность.
Причин, по которым вы получаете неверный результат, или вовсе не получаете ничего, достаточно много. Сегодня мы постараемся разобраться со всеми проблемами. Начнём, как обычно, с наиболее распространённых ошибок.
Эксель неправильно считает сумму
Если вы производите вычисления, и вдруг заметили, что ответ неверный — внимательно просмотрите все числовые ячейки.
Ошибки допускаемые при подсчёте:
- В столбце используют значения нескольких видов: чистые числа и числа с рублями, долларами, евро. Например, 10, 30, 5 руб, $4 и так далее. Или где-то не целые числа, а дробные;
- В таблице присутствуют скрытые ячейки (строки), которые добавляются к общей сумме;
- Ошибочная формула. Высока вероятность того, что допущена ошибка при вводе выражения;
- Ошибка в округлении. Задайте для всех ячеек, содержащих числа, числовой формат с 3 или 4 знаками после запятой;
- В качестве разделения целого значения используют точку вместо запятой.
Как видите, все причины ошибок — это людской фактор, а программа лишь выполняет то, что заложено в её задачах.
Эксель отказывается подсчитывать сумму
Иногда можно столкнуться с тем, что программа вообще не выполняет каких либо вычислений, даже не показывает промежуточную сумму при выделении столбца.
И опять, таки, всё дело в нашей невнимательности или в неверных настройках. А для получения верных расчётов, необходима правильная настройка Excel для финансовых расчётов.
Давайте пройдём по порядку, по всем пунктам.
Текстовые и числовые значения
Часто копируя и вставляя данные из разных источников в программу Эксель, пользователь сталкивается с проблемой в расчётах.
Причина, чаще всего, лежит на поверхности — Эксель воспринимает введённые данные как текст. Переведите все ячейки с цифрами в числовой формат — всё заработает.
Посмотрите внимательно на ячейки с цифрами, если вы заметили в левом верхнем углу треугольник — то это текстовая ячейка.
Соответственно, программа видит текст и не определяет числа. Для того, чтобы это изменить, нужно выделить ячейки с числами.
Появится значок с восклицательным знаком. Клик по нему — преобразовать в число.
Обычно этого достаточно для того, чтобы программа стала работать. Но, если этого не случилось, двигаемся далее.
Автоматический расчёт формул в Excel
Если программа не хочет считать, проверьте в настройках указание на автоматические расчёты. иногда этот параметр может быть отключен самой программой, в целях экономии мощностей.
Пройдите по пути — файл — параметры — формулы — установите галочку — автоматически.
После изменения данной настройки программа подсчитает всё, что вам нужно.
Сумма не совпадает с калькулятором
Опять же, вся проблема в округлении. Например, при подсчёте на калькуляторе, как правило, считаем 2 знака после запятой.
Получается один результат, а в таблице может быть настроено знаков гораздо больше. Получается расчёт точнее, но он не совпадает с «калькуляторным».
Для того, чтобы проверить настройки, откройте формат ячеек. Нажмите на вкладку — число, выбрав числовой формат. Здесь можно указать требуемое число десятичных знаков.
В заключение хочу сказать, что программа Excel никогда не ошибается, ошибаются люди. Иногда всё дело в настройках программы.
Поэтому, если вы заметили, что Эксель неправильно считает сумму, не спеша проверьте всё то, о чём я сегодня написал для вас.
Почему эксель неправильно считает сумму столбца
Приложение Эксель используют не только для создания таблиц. Его главным предназначением является расчет чисел по формулам. Достаточно вписать в ячейки новые значения и система автоматически пересчитает их. Однако, в некоторых случаях расчет не происходит. Тогда, необходимо выяснить, почему Эксель не считает сумму.
Основные причины неисправности
Эксель может не считать сумму или формулы по многим причинам. Проблема часто заключается, как в неправильной формуле, так и в системных настройках книги. Поэтому, рекомендуется воспользоваться несколькими советами, чтобы выяснить, какой именно подходит в данной конкретной ситуации.
Изменяем формат ячеек
Программа выводит неправильные расчеты, если указанные форматы не соответствуют значению, которое находится в ячейке. Тогда вычисление или вообще не будет применяться, или выдавать совсем другое число. Например, если формат является текстовым, то расчет проводится не будет. Для программы, это только текст, а не числа. Также, может возникнуть ситуация, когда формат не соответствует действительному. В таком случае, у пользователя не получится правильно вставить вычисление, и Эксель не посчитает сумму и не рассчитает результат формулы.
Чтобы проверить, действительно ли дело в формате, следует перейти во вкладку «Главная». Предварительно, необходимо выбрать непроверенную ячейку. В этой вкладке находится информация о формате.
Если его нужно изменить, достаточно нажать на стрелочку и выбрать требуемый из списка. После этого, система произведет новый расчет.
Список форматов в данном разделе полный, но без описаний и параметров. Поэтому в некоторых случаях пользователь не может найти нужный. Тогда, лучше воспользоваться другим методом. Так же, как и в первом варианте, следует выбрать ячейку. После этого кликнуть правой клавишей мыши и открыть команду «Формат ячеек».
В открытом окне находится полный список форматов с описанием и настройками. Достаточно выбрать нужный и нажать на «ОК».
Отключаем режим «Показать формулы»
Иногда пользователь может заметить, что вместо числа отображено само вычисление и формула в ячейке не считается. Тогда, нужно отключить данный режим. После этого система будет выводить готовый результат расчета, а не выражения.
Для отключения функции «Показать формулы», следует перейти в соответствующий раздел «Формулы». Здесь находится окно «Зависимости». Именно в нем расположена требуемая команда. Чтобы отобразить список всех зависимостей, следует кликнуть на стрелочке. Из перечня необходимо выбрать «Показать» и отключить данный режим, если он активен.
Ошибки в синтаксисе
Часто, неправильное отображение результата является следствием ошибок синтаксиса. Такое случается, если пользователь вводил вычисление самостоятельно и не прибегал к помощи встроенного мастера. Тогда, все ячейки с ошибками не будут выдавать расчет.
В таком случае, следует проверить правильное написание каждой ячейки, которая выдает неверный результат. Можно переписать все значения, воспользовавшись встроенным мастером.
Включаем пересчет формулы
Все вычисления могут быть прописаны правильно, но в случае изменения значений ячеек, перерасчет не происходит. Тогда, может быть отключена функция автоматического изменения расчета. Чтобы это проверить, следует перейти в раздел «Файл», затем «Параметры».
В открытом окне необходимо перейти во вкладку «Формулы». Здесь находятся параметры вычислений. Достаточно установить флажок на пункте «Автоматически» и сохранить изменения, чтобы система начала проводить перерасчет.
Ошибка в формуле
Программа может проводить полный расчет, но вместо готового значения отображается ошибка и столбец или ячейка может не суммировать числа. В зависимости от выводимого сообщения можно судить о том, какая неисправность возникла, например, деление на ноль или неправильный формат.
Для того, чтобы перепроверить синтаксис и исправить ошибку, следует перейти в раздел «Формулы». В зависимостях находится команда, которая отвечает за вычисления.
Откроется окно, которое отображает саму формулу. Здесь, следует нажать на «Вычислить», чтобы провести проверку ошибки.
Другие ошибки
Также, пользователь может столкнуться с другими ошибками. В зависимости от причины, их можно исправить соответствующим образом.
Формула не растягивается
Растягивание необходимо в том случае, когда несколько ячеек должны проводить одинаковые вычисления с разными значениями. Но бывает, что этого не происходит автоматически. Тогда, следует проверить, что установлена функция автоматического заполнения, которая расположена в параметрах.
Кроме того, рекомендуется повторить действия для растягивания. Возможно, ошибка была в неправильной последовательности.
Неверно считается сумма ячеек
Сумма также считается неверно, если в книге находятся скрытые ячейки. Их пользователь не видит, но система проводит расчет. В итоге, программа отображает одно значение, а реальная сумма должна быть другой.
Такая же проблема возникает, если отображены значения с цифрами после запятой. В таком случае их требуется округлить, чтобы вычисление производилось правильно.
Формула не считается автоматически
Эксель не будет считать формулу автоматически, если данная функция отключена в настройках. Пользователь может устранить данную проблему, если перейдет в параметры, которые находятся в разделе «Файл».
В открытом окне следует перейти к настройке автоматического перерасчета и установить флажок на соответствующей команде. После этого требуется сохранить изменения.
Бухгалтеры (и не только) знают одну «нехорошую» особенность Excel – «неумение» правильно суммировать. 🙂 Иногда это приводит к казусам в бухгалтерских документах, сформированных в Excel (рис. 1)
Рис. 1. Фрагмент счет-фактуры с «неверным» суммированием
Скачать заметку в формате Word, примеры в формате Excel
Видно, что общий итог по налогу (значение в ячейке G7) и стоимости товаров (Н7) отличаются на копейку от суммы по строкам (G4:G6 и Н4:Н6, соответственно). Это ошибка является следствием округления. Дело в том, что значения только отображаются в формате с двумя десятичными знаками. Фактические значения в этих ячейках содержат больше десятичных знаков (рис. 2). Excel суммирует не отображаемые значения, а фактические.
Рис. 2. Тот же счет-фактура с большим числом знаков после запятой
Чтобы значение в ячейке G7 равнялось сумме отображаемых значений в ячейках G4:G6, можно применить формулу массива, проводящую округление значений до двух десятичных знаков перед суммированием: <=СУММ(ОКРУГЛ(G4:G6;2))>(рис. 3). [1]
Рис. 3. «Правильное» суммирование с использованием формулы массива
Чуть подробнее, как работает эта формула. Excel формирует виртуальный массив (в памяти компьютера), состоящий из трех элементов: ОКРУГЛ(G4;2), ОКРУГЛ(G5;2), ОКРУГЛ(G6;2), то есть значений в ячейках G4:G6, округленных до двух десятичных знаков, а затем суммирует эти три элемента. Вуаля! 🙂
Ошибки округления можно также исключить, применив функцию ОКРУГЛ в каждой из ячеек диапазона G4:G6. Этот прием не требует применения формулы массива, однако требует многократного использования функции ОКРУГЛ. Вам судить, что проще!
[1] Идея подсмотрена в книге Джона Уокенбаха «MS Excel 2007. Библия пользователя». Если вы не использовали ранее формулы массива, рекомендую начать с заметки Excel. Введение в формулы массива .
Комментарии: 42 комментария
Для полноты картины можно упомянуть еще об одном варианте, в параметрах Excel указать «точность как на экране» (Файл-Параметры-Дополнительно-При пересчете этой книги: задать точность как на экране)
Вот не рекомендуют программисты такой способ
Очень осторожно с этой приблудой. Точность_как_на_экране применяется ко всем листам книги и после сохранения вернуть прежние значения не получится.
Спасибо ! Очень помогло! сэкономило кучу времени
Спасибо за статью. «соответсвенно» лучше исправить
а как быть, если в документе производится большое количество вычислений, и числа «завязаны» друг за друга. использование формул ОКРУГЛ и т.п. немного неудобно.
как можно отключить округление отображаемого числа? как сделать, чтобы ексель показывал то что есть? пример — число 12345.6789, с точностью после запятой 2, он не округлял 12345.68, а отображал 12345.67, но значение оставалось 12345.6789?
Александр, если Вы хотите, чтобы Excel отражал с точностью до двух знаков после запятой, а хранил число с максимальной точностью, просто задайте форматирование «два знака после запятой», и никакие дополнительные формулы не потребуются. Но… именно против этого и направлена статья, так как в бухгалтерских расчетах не допускается расхождение между суммой и слагаемыми…
Как быть, если это не помогло. В формате ячейки ставлю «2 знака после запятой» затем вписываю в нее число 1950,4787, то автоматически записывается 1950,48, остальное отбрасывается вообще.
>>остальное отбрасывается вообще
Не отбрасывается, а не отображается в ячейке. Формат — лишь визуальное представление значения Выделите ячейку и посмотрите в строке формул — там то число, которое находится в ячейке
>>В формате ячейки ставлю «2 знака после запятой».
А кто Вам мешает поставить 4?
если файл загрузить сюда , а здесь выложить ссылку для скачивания, тогда:
а) мы не будем играть в угадайку
б) найдем ошибку и укажем на нее
в) предоставим правильное решение
Возможные варианты:
— в столбце используются значения 2-х видов: чистые числа и числа с рублями,
23
56руб
12
Ответ =35
— скрытые строки
— в качестве разделителя целой части используется точка (надо запятую)