Почему эксель считает неправильно
Часто при вычислении разницы двух ячеек в Excel можно видеть, что она не равна нулю, хотя числа одинаковые. Например, в ячейках A1 и B1 записано одно и тоже число 10,7 , а в C1 мы вычитаем из одного другое:
И самое странное то, что в итоге мы не получаем 0! Почему?
Причина очевидная - формат ячеек
Сначала самый очевидный ответ: если идет сравнение значений двух ячеек, то необходимо убедиться, что числа там действительно равны и не округлены форматом ячеек. Например, если взять те же числа из примера выше, то если выделить их -правая кнопка мыши -Формат ячеек (Format cells) -вкладка Число (Number) -выбираем формат Числовой и выставляем число десятичных разрядов равным 7:
Теперь все становится очевидным - числа отличаются и были просто округлены форматом ячеек. И естественно не могут быть равны. В данном случае оптимальным будет понять почему числа именно такие, а уже потом принимать решение. И если уверены, что числа надо реально округлять до десятых долей - то можно применить в формуле функцию ОКРУГЛ:
=ОКРУГЛ( B1 ;1)-ОКРУГЛ( A1 ;1)=0
=ROUND(B1,1)-ROUND(A1,1)=0
Так же есть более кардинальный метод:
- 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 знака после запятой. При этом отменить данную операцию нельзя, если только не закрыть книгу без сохранения.
Можно, правда, выкрутиться и иначе. Умножить каждое число на некую величину(скажем на 1000, чтобы 100% убрать знаки после запятой) и после этого производить вычитание и сравнение:
=((10,8*1000)-(10,7*1000))/1000=0,1
Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора :)
Одной из наиболее востребованных возможностей Excel является работа с формулами. Благодаря данной функции программа самостоятельно производит различного рода расчеты в таблицах. Но иногда случается так, что пользователь вписывает формулу в ячейку, но она не выполняет своего прямого назначения – вычисления результата. Давайте разберемся, с чем это может быть связано, и как решить данную проблему.
Устранение проблем с вычислением
Причины проблем с вычислением формул в Экселе могут быть совершенно разными. Они могут быть обусловлены, как настройками конкретной книги или даже отдельного диапазона ячеек, так и различными ошибками в синтаксисе.
Способ 1: изменение формата ячеек
Одной из самых распространенных причин, по которой Эксель вообще не считает или не правильно считает формулы, является неверно выставленный формат ячеек. Если диапазон имеет текстовый формат, то расчет выражений в нем вообще не производится, то есть, они отображаются как обычный текст. В других случаях, если формат не соответствует сути рассчитываемых данных, результат, выводимый в ячейку, может отображаться некорректно. Давайте выясним, как решить эту проблему.
-
Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.
Теперь будет производиться расчет формулы в стандартном порядке с выводом результата в указанную ячейку.
Способ 2: отключение режима «Показать формулы»
Но возможно причина того, что вместо результатов расчета у вас отображаются выражения, состоит в том, что в программе включен режим «Показать формулы».
-
Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.
Способ 3: исправление ошибки в синтаксисе
Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».
В таких случаях нужно внимательно пересмотреть синтаксис тех формул, которые неправильно отображаются, и внести в них соответствующие коррективы.
Способ 4: включение пересчета формулы
Бывает и такая ситуация, что формула вроде и отображает значение, но при изменении связанных с ней ячеек сама не меняется, то есть, результат не пересчитывается. Это означает, что у вас неправильно настроены параметры вычислений в данной книге.
-
Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».
Теперь все выражения в данной книге будут автоматически пересчитываться при изменении любого связанного значения.
Способ 5: ошибка в формуле
Если же программа все-таки производит расчет, но в результате показывает ошибку, то тут вероятна ситуация, что пользователь просто ошибся при вводе выражения. Ошибочными формулами считаются те, при расчете которых в ячейке появляются следующие значения:
В этом случае нужно проверить, правильно ли записаны данные в ячейках, на которые ссылается выражение, нет ли в них ошибок в синтаксисе или не заложено ли в самой формуле какое-либо некорректное действие (например, деление на 0).
Если функция сложная, с большим количеством связанных ячеек, то легче проследить вычисления с помощью специального инструмента.
-
Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».
Как видим, причины того, что Эксель не считает или не правильно считает формулы, могут быть совершенно различными. Если вместо расчета у пользователя отображается сама функция, тот в этом случае, скорее всего, либо ячейка отформатирована под текст, либо включен режим просмотра выражений. Также, возможна ошибка в синтаксисе (например, наличие пробела перед знаком «=»). В случае если после изменения данных в связанных ячейках результат не обновляется, то тут нужно посмотреть, как настроено автообновление в параметрах книги. Также, нередко вместо корректного результата в ячейке отображается ошибка. Тут нужно просмотреть все значения, на которые ссылается функция. В случае обнаружения ошибки следует устранить её.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Чаще всего проблемы с подсчетом суммы выделенных ячеек появляются у пользователей, которые не совсем понимают, как работает эта функция в Excel. Подсчет не производится, если в ячейках присутствует вручную добавленный текст, пусть даже и обозначающий валюту или выполняющий другую задачу. При выделении таких ячеек программа показывает только их количество.
Как только вы удалите этот вручную введенный текст, произойдет автоматическое форматирование формата ячейки в числовую. и при выделении пункт «Сумма» отобразится внизу таблицы. Однако это правило не сработает, если из-за этих самых надписей формат ячейки остался текстовым. В таких ситуациях обратитесь к следующим способам.
Способ 2: Изменение формата ячеек
Если ячейка находится в текстовом формате, ее значение не входит в сумму при подсчете. Тогда единственным правильным решением станет форматирование формата в числовой, за что отвечает отдельное меню программы управления электронными таблицами.
-
Зажмите левую кнопку мыши и выделите все значения, при подсчете которых возникают проблемы.
Способ 3: Переход в режим просмотра «Обычный»
Иногда проблемы с подсчетом суммы чисел в ячейках связаны с отображением самой таблицы в режиме «Разметка страницы» или «Страничный». Почти всегда это не мешает нормальной демонстрации результата при выделении ячеек, но при возникновении неполадки рекомендуется переключиться в режим просмотра «Обычный», нажав по кнопке перехода на нижней панели.
На следующем скриншоте видно то, как выглядит этот режим, а если у вас в программе таблица не такая, используйте описанную выше кнопку, чтобы настроить нормальное отображение.
Способ 4: Проверка знака разделения дробной части
Практически в любой программе на компьютере запись дробей происходит в десятичном формате, соответственно, для отделения целой части от дробной нужно написать специальный знак «,». Многие пользователи думают, что при ручном вводе дробей разницы между точкой и запятой нет, но это не так. По умолчанию в настройках языкового формата операционной системы в качестве разделительного знака всегда используется запятая. Если же вы поставите точку, формат ячейки сразу станет текстовым, что видно на скриншоте ниже.
Способ 5: Изменение разделителя целой и дробной части в Windows
Решить необходимость изменения разделителя целой и дробной части в Excel можно, если настроить этот знак в самой Windows. За это отвечает особый параметр, для редактирования которого нужно вписать новый разделитель.
-
Откройте «Пуск» и в поиске найдите приложение «Панель управления».
Как только вы вернетесь в Excel и выделите все ячейки с указанным разделителем, никаких проблем с подсчетом суммы возникнуть не должно.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Я много пишу о работе в программе Еxcel, есть и статья о том, как производить суммирование. Но, после этого ко мне стали поступать вопросы, почему Эксель неправильно считает сумму.
Хочу срезу сказать, что программа не может считать неправильно, за каждой такой ошибкой стоит наша невнимательность.
Причин, по которым вы получаете неверный результат, или вовсе не получаете ничего, достаточно много. Сегодня мы постараемся разобраться со всеми проблемами. Начнём, как обычно, с наиболее распространённых ошибок.
Эксель неправильно считает сумму
Если вы производите вычисления, и вдруг заметили, что ответ неверный — внимательно просмотрите все числовые ячейки.
Ошибки допускаемые при подсчёте:
- В столбце используют значения нескольких видов: чистые числа и числа с рублями, долларами, евро. Например, 10, 30, 5 руб, $4 и так далее. Или где-то не целые числа, а дробные;
- В таблице присутствуют скрытые ячейки (строки), которые добавляются к общей сумме;
- Ошибочная формула. Высока вероятность того, что допущена ошибка при вводе выражения;
- Ошибка в округлении. Задайте для всех ячеек, содержащих числа, числовой формат с 3 или 4 знаками после запятой;
- В качестве разделения целого значения используют точку вместо запятой.
Как видите, все причины ошибок — это людской фактор, а программа лишь выполняет то, что заложено в её задачах.
Эксель отказывается подсчитывать сумму
Иногда можно столкнуться с тем, что программа вообще не выполняет каких либо вычислений, даже не показывает промежуточную сумму при выделении столбца.
И опять, таки, всё дело в нашей невнимательности или в неверных настройках. А для получения верных расчётов, необходима правильная настройка Excel для финансовых расчётов.
Давайте пройдём по порядку, по всем пунктам.
Текстовые и числовые значения
Часто копируя и вставляя данные из разных источников в программу Эксель, пользователь сталкивается с проблемой в расчётах.
Причина, чаще всего, лежит на поверхности — Эксель воспринимает введённые данные как текст. Переведите все ячейки с цифрами в числовой формат — всё заработает.
Посмотрите внимательно на ячейки с цифрами, если вы заметили в левом верхнем углу треугольник — то это текстовая ячейка.
Соответственно, программа видит текст и не определяет числа. Для того, чтобы это изменить, нужно выделить ячейки с числами.
Появится значок с восклицательным знаком. Клик по нему — преобразовать в число.
Обычно этого достаточно для того, чтобы программа стала работать. Но, если этого не случилось, двигаемся далее.
Автоматический расчёт формул в Excel
Если программа не хочет считать, проверьте в настройках указание на автоматические расчёты. иногда этот параметр может быть отключен самой программой, в целях экономии мощностей.
Пройдите по пути — файл — параметры — формулы — установите галочку — автоматически.
После изменения данной настройки программа подсчитает всё, что вам нужно.
Сумма не совпадает с калькулятором
Опять же, вся проблема в округлении. Например, при подсчёте на калькуляторе, как правило, считаем 2 знака после запятой.
Получается один результат, а в таблице может быть настроено знаков гораздо больше. Получается расчёт точнее, но он не совпадает с «калькуляторным».
Для того, чтобы проверить настройки, откройте формат ячеек. Нажмите на вкладку — число, выбрав числовой формат. Здесь можно указать требуемое число десятичных знаков.
В заключение хочу сказать, что программа Excel никогда не ошибается, ошибаются люди. Иногда всё дело в настройках программы.
Поэтому, если вы заметили, что Эксель неправильно считает сумму, не спеша проверьте всё то, о чём я сегодня написал для вас.
Возможности Эксель позволяют выполнять вычисления практически любой сложности благодаря формулам и функциям. Однако иногда пользователи могут столкнуться с тем, что формула отказывается работать или вместо желаемого результата выдает ошибку. В данной статье мы рассмотрим, почему так получается, и какие действия предпринять для решения возникшей проблемы.
Решение 1: меняем формат ячеек
Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.
Например, если задан текстовый формат, то вместо результата мы будем видеть просто саму формулу в виде обычного текста.
В некоторых ситуациях, когда выбран не тот формат, результат может быть посчитан, но отображаться он будет совсем не так, как мы хотели бы.
Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:
Формат ячеек можно сменить с помощью другого инструмента, который позволяет задать более расширенные настройки.
- Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.
- В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.
- Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.
Примечание: Если данных слишком много, на ручное выполнение последнего шага потребуется немало времени. В данном случае можно поступить иначе – воспользуемся Маркером заполнения. Но этот работает только в том случае, когда во всех ячейках используются одинаковая формула.
Решение 2: отключаем режим “Показать формулы”
Когда мы вместо результатов видим сами формулы, это может быть связано с тем, что активирован режим показа формул, и его нужно отключить.
Решение 3: активируем автоматический пересчет формул
Иногда может возникать ситуация, когда формула посчитала какой-то результат, однако, если мы решим изменить значение в одной из ячеек, на которую формула ссылается, пересчет выполнен не будет. Это исправляется в параметрах программы.
Решение 4: исправляем ошибки в формуле
Если в формуле допустить ошибки, программа может воспринимать ее как простое текстовое значение, следовательно, расчеты по ней выполнятся не будут. Например, одной из самых популярных ошибок является пробел, установленный перед знаком “равно”. При этом помним, что знак “=” обязательно должен стоять перед любой формулой.
Также, довольно часто ошибки допускаются в синтаксисах функций, так как заполнить их не всегда просто, особенно, когда используется несколько аргументов. Поэтому, рекомендуем использовать Мастер функций для вставки функции в ячейку.
Иногда проще удалить формулу и написать ее заново, чем пытаться искать ошибку в уже написанной. То же самое касается функций и их аргументов.
Распространенные ошибки
В некоторых случаях, когда пользователь допустил ошибку при вводе формулы, в ячейке могут отображаться такие значения:
В случаях, когда приходится иметь со сложными функциями, которые ссылаются на много ячеек, можно воспользоваться инструментов проверки.
Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.
Откроется окно, в котором будет описана причина ошибки, а также предложен ряд действий касательно нее, в т.ч. исправление в строке формул.
Заключение
Читайте также: