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

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

Решение проблемы с подсчетом суммы выделенных ячеек в Microsoft Excel

Эксель не считает сумму выделенных ячеек

Выделение ячеек в Excel в текстовом формате для проверки результата суммы

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

Как только вы удалите этот вручную введенный текст, произойдет автоматическое форматирование формата ячейки в числовую. и при выделении пункт «Сумма» отобразится внизу таблицы. Однако это правило не сработает, если из-за этих самых надписей формат ячейки остался текстовым. В таких ситуациях обратитесь к следующим способам.

Выделение ячеек в Excel в формате числа для просмотра результата суммы

Способ 2: Изменение формата ячеек

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

Выделение ячеек в Excel для перехода к изменению их формата при решении проблем с отображением их суммы

    Зажмите левую кнопку мыши и выделите все значения, при подсчете которых возникают проблемы.

Способ 3: Переход в режим просмотра «Обычный»

Иногда проблемы с подсчетом суммы чисел в ячейках связаны с отображением самой таблицы в режиме «Разметка страницы» или «Страничный». Почти всегда это не мешает нормальной демонстрации результата при выделении ячеек, но при возникновении неполадки рекомендуется переключиться в режим просмотра «Обычный», нажав по кнопке перехода на нижней панели.

Переход в обычный режим просмотра таблицы в Excel для решения проблемы с подсчетом суммы выделенных ячеек

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

Результат перехода в обычный режим просмотра таблицы в Excel при решении проблемы с подсчетом суммы выделенных ячеек

Способ 4: Проверка знака разделения дробной части

Практически в любой программе на компьютере запись дробей происходит в десятичном формате, соответственно, для отделения целой части от дробной нужно написать специальный знак «,». Многие пользователи думают, что при ручном вводе дробей разницы между точкой и запятой нет, но это не так. По умолчанию в настройках языкового формата операционной системы в качестве разделительного знака всегда используется запятая. Если же вы поставите точку, формат ячейки сразу станет текстовым, что видно на скриншоте ниже.

Lumpics.ru

Изменение разделителя целой и дробной части при решении проблемы с подсчетом суммы ячеек в Excel

Проверьте все значения, входящие в диапазон при подсчете суммы, и поменяйте знак отделения дробной части от целой, чтобы получить корректный подсчет суммы. Если невозможно исправить все точки сразу, есть вариант изменения настроек операционной системы, о чем читайте в следующем способе.

Результат изменения разделителя целой и дробной части при решении проблемы с подсчетом целой и дробной части выделенных ячеек в Excel

Способ 5: Изменение разделителя целой и дробной части в Windows

Решить необходимость изменения разделителя целой и дробной части в Excel можно, если настроить этот знак в самой Windows. За это отвечает особый параметр, для редактирования которого нужно вписать новый разделитель.

  1. Откройте «Пуск» и в поиске найдите приложение «Панель управления». Переход в панель управления Windows для изменения разделителя целой и дробной части
  2. Переключите тип просмотра на «Категория» и через раздел «Часы и регион» вызовите окно настроек «Изменение форматов даты, времени и чисел». Переход в раздел Изменение форматов даты, времени и чисел для изменения разделителя целой и дробной части в Windows
  3. В открывшемся окне нажмите по кнопке «Дополнительные параметры». Переход в дополнительные параметры форматов даты, времени и чисел для изменения разделителя целой и дробной части в Windows
  4. Оказавшись на первой же вкладке «Числа», измените значение «Разделителя целой и дробной части» на оптимальное, а затем примените новые настройки. Изменение типа разделителя целой и дробной части через Панель управления в Windows

Как только вы вернетесь в Excel и выделите все ячейки с указанным разделителем, никаких проблем с подсчетом суммы возникнуть не должно.

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

Часто при вычислении разницы двух ячеек в 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 знака после запятой. При этом отменить данную операцию нельзя, если только не закрыть книгу без сохранения.

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

Почему с виду одинаковые числа не равны?
Сначала разберемся почему 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-рублевого калькулятора 🙂

Почему Эксель не считает сумму выделенных ячеек? Что делать?

Вместо суммы Эксель считает КОЛИЧЕСТВО выделенных ячеек.

Эксель считает количество выделенных ячеек потому, что воспринимает их содержание, как текстовое и не понимает, как с ними производить какие-либо математические операции. Поэтому для начала надо преобразовать содержимое нужных ячеек в числовой формат.

Вот один из способов преобразования группы ячеек, находящихся в одном столбце. Здесь используется встроенный инструмент Экселя "Текст по столбцам" — его кнопка находится на ленте во вкладке "Данные":

После нажатия на кнопку открывается мастер выполнения и первые два шага пропускаем без изменений, нажимая кнопку "Далее", а на третьем шаге нажимаем кнопку "Подробнее. ":

В открывшемся окне выбираем присутствующие в преобразуемой ячейке разделители (у вас это точка между целой и дробной частью и пробел между разрядами — в случае чего выбираем нужные разделители):

Жмем "ОК" и потом "Готово" — преобразование свершилось и Эксель воспринимает ячейки, как числа и находит и сумму, и количество, и среднее значение.

Если эксель не распознает цифры, то он не сможет считать суммы в ячейках. Существует несколько способов исправить ситуацию. Выберите тот, который применим в вашей ситуации.

1) Удалите пробелы между цифрами.

2) При наличии апострофов перед числами — удалите их тоже.

3) Проверьте числовой формат в ячейках: измените "текст" на "общий".

4) Исправьте числа, которые отображаются в виде дробей (например, 1/4, 1/2).

5) В экселе 2002+ уже встроены параметры проверки ошибок, которые самостоятельно обнаруживают числа, выведенные в виде текста. Обычно они выровнены по левому, а не по правому краю в ячейке, и часто помечены индикатором ошибки — маленький зеленый треугольник в верхнем левом углу.

Нужно выбрать такую ячейку и затем нажать кнопку ошибки рядом с ней. Затем следует выбрать «Преобразовать в число» во всплывающем меню.

Ссылка на основную публикацию