Сумма в excel, подробная инструкция. Рекомендации, позволяющие избежать появления неработающих формул Эксель не показывает сумму

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

Устраняем проблему

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

При протягивании не работают формулы

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

Нажмите кнопку F9, чтобы начался пересчет листа.
Измените формат ячейки на общий. Для этого нужно выделить ее и кликнуть правой кнопкой мыши. Откроется меню, где следует выбрать «Формат ячейки» и изменить на «Общий».
Откройте параметры вычисления формул и проверьте, чтобы отметки стояли, как на скрине.

Формула не считает

Есть несколько причин, почему формула в экселе не считает:

Не верный формат ячейки. Для его изменения, нужно выделить нужный диапазон ячеек и выбрать «Формат Ячейки».

Активирован режим «Показать формулы». Для его отключения нужно на панели сверху перейти в раздел «Формулы», там кликнуть на «Зависимости формул» и отключить «Показать формулы».

Неверно введена формула или ее синтаксис. В таком случае отображается формула вместо значения. Нужно внимательно ее проверить.

Это несколько основных причин, почему Эксель не считает формулы.

Программа не считает формулу, введённую в ячейку

Такое часто случается из-за того, что установлен текстовый формат ячейки. Как его сменить на другой уже описывалось ранее.

Не происходит подсчет суммы

Есть два варианта, почему приложение Эксель не считает сумму:

Формат ячеек является тестовым.
Деактивирован подсчет суммы. Нужно, используя правую кнопку мыши клацнуть на специальной панели и установить отметку на «Сумма».

Вот, почему не считается сумма.

Не верно суммируются данные ячеек

Некоторые пользователи столкнулись с тем, когда Еxcel неправильно считает сумму выделенных ячеек. Зачастую это происходит из-за того, что некоторые ячейки, а то и большая их часть, имеют текстовый формат. Из-за этого суммируются не все данные и результат получается некорректным. Нужно изменить формат на «Общий», чтобы устранить проблему.

Отключен перерасчет

Бывает, что пользователь подсчитал все данные, но при их корректировании в связанных с формулой ячейках итог не меняется. Это означает, что неверно выставленные параметры:

Откройте параметры программы.
Там следует перейти в раздел «Формулы». Сверху будет блок «Параметры вычислений», а там параметр «Вычисления в книге», где должна быть выбрана позиция «Автоматически». Если нет, выставляете как следует и нажимаете «ОК».

Проблемы с работой формул в Microsoft Excel

Теперь, если какое-либо значение будет изменено, произойдет пересчет результата.

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

Читатели Лайфхакера уже знакомы с Денисом Батьяновым , который делился с нами . Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

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

Вы не даёте заголовки столбцам таблиц

Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, - подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.

Пустые столбцы и строки внутри ваших таблиц

Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы , вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel - формул и сводных таблиц.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Разнесение информации по разным листам книги «для удобства»

Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.

Информация в комментариях

Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.

Бардак с форматированием

Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:

Объединение ячеек

Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

Страничный режим листа в Excel

Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.

Ещё больше полезной информации про Excel можно узнать на

Если Excel не может распознать формулу, которую вы пытаетесь создать, может появиться сообщение об ошибке такого вида:

К сожалению, это значит, что Excel не понимает, что вы пытаетесь делать, так что, возможно, вам лучше выйти и начать все заново.

Прежде всего нажмите кнопку ОК или клавишу ESC , чтобы закрыть сообщение об ошибке.

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

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

Видите ли вы ошибку решетки (#)?

В Excel выводятся различные ошибки решетки (#), такие как #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? и #NULL!, чтобы указать, что в формуле не работает правильно. Например, #VALUE! ошибка вызвана неправильным форматированием или неподдерживаемыми типами данных в аргументах. Кроме того, вы увидите #REF! ошибка, если формула ссылается на ячейки, которые были удалены или заменены другими данными. Рекомендации по устранению неполадок будут отличаться для каждой ошибки.

Примечание: # # # # не является ошибкой, связанной с формулой. Это означает, что столбец недостаточно широк для отображения содержимого ячейки. Просто перетащите столбец, чтобы расширить его, или перейдите на вкладку главная > формат > Автоподбор ширины столбца .

Ознакомьтесь со следующими разделами, в которых рассматриваются ошибки, которые вы видите:

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

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

Вы также можете отключить показ этого диалогового окна при открытии файла. Для этого перейдите в раздел Файл > Параметры > Дополнительно > Общие и снимите флажок Запрашивать об обновлении автоматических связей .

Формула возвращает синтаксическую конструкцию, а не значение

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

Формула не вычисляется

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

Дополнительные сведения о вычислениях можно найти в разделе Изменение пересчета формул, итераций или точности .

Формула содержит одну или несколько циклических ссылок

Циклическая ссылка возникает, когда формула ссылается на ячейку, в которой она находится. Исправление предназначено для того, чтобы переместить формулу в другую ячейку или изменить синтаксис формулы, чтобы избежать циклических ссылок. Однако в некоторых сценариях вам может потребоваться циклическая ссылка, так как они приводят к итерации функций, повторяются до тех пор, пока не будет выполнено определенное числовое условие. В таком случае вам потребуется включить или отключить циклическую ссылку .

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

Начинается ли функция со знака равенства (=)?

Если запись начинается не со знака равенства, она не считается формулой и не вычисляется (это распространенная ошибка).

Если ввести СУММ(A1:A10) , Excel отобразит текстовую строку СУММ(A1:A10) вместо результата формулы. Кроме того, при вводе 11/2 в Excel отображается дата, например 2-ноя или 11/02/2009, вместо деления 11 на 2.

Чтобы избежать подобных неожиданных результатов, всегда начинайте формулу со знака равенства. Например, введите = СУММ(A1:A10) и =11/2 .

Соблюдается ли соответствие открывающих и закрывающих скобок?

Если в формуле используется функция, для ее правильной работы важно, чтобы у каждой открывающей скобки была закрывающая, поэтому убедитесь, что каждой скобке соответствует парная. Например, формула =ЕСЛИ(B5<0);"Недопустимо";B5*1,05) не будет работать, так как в ней две закрывающие и только одна открывающая скобка. Правильный вариант этой формулы выглядит следующим образом: =ЕСЛИ(B5<0;"Недопустимо";B5*1,05) .

Включает ли синтаксис все обязательные аргументы?

Функции в Excel имеют аргументы - значения, которые необходимо указать, чтобы функция работала. Без аргументов работает лишь небольшое количество функций (например, ПИ или СЕГОДНЯ). Проверьте синтаксис формулы, который отображается, когда вы начинаете вводить функцию, чтобы убедиться в том, что указаны все обязательные аргументы.

Если вам нужно быстро просмотреть синтаксис определенной функции, см. список функций Excel (по категориям) .

Есть ли в формулах неформатированные числа?

Не вводите числа, отформатированные с помощью знаков доллара ($) и десятичных разделителей (,) в формулах, так как знаки доллары означают абсолютные ссылки и запятые - это разделители аргументов. Вместо $1,000 в формуле необходимо ввести 1000.

Если вы используете в аргументах форматированные числа, вы получаете непредвиденные результаты вычислений, но вы также можете увидеть ошибку #NUM! . Например, если ввести формулу = ABS (-2 134) , чтобы найти абсолютное значение-2134, в Excel появится #NUM! ошибка, так как Функция ABS принимает только один аргумент, и она видит числа-2 и 134 в качестве отдельных аргументов.

Примечание: Результат формулы можно отформатировать с использованием десятичных разделителей и обозначений денежных единиц после ввода формулы с неформатированными числами (константами). Как правило, использовать константы непосредственно в формулах не рекомендуется: их бывает сложно найти в случае, если потребуется обновить значения, а кроме того, при их вводе часто допускаются опечатки. Гораздо удобнее помещать константы в отдельные ячейки, в которых они будут доступны и на них легко ссылаться.

Формула может не возвратить ожидаемые результаты, если тип данных ячейки не подходит для вычислений. Например, если ввести простую формулу =2+3 в ячейке, которая имеет текстовый формат, Excel не сможет вычислить введенные данные. В ячейке будет отображаться строка =2+3 . Чтобы исправить эту ошибку, измените тип данных ячейки с Текстовый на Общий , как описано ниже.

Выделите ячейку.

На вкладке Главная щелкните стрелку рядом со списком Числовой формат (или нажмите CTRL+1 ) и выберите пункт Общий .

Нажмите клавишу F2 , чтобы перейти в режим правки, а затем - клавишу ВВОД , чтобы подтвердить формулу.

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

Пытаетесь ли вы выполнить умножение, не используя символ *?

В качестве оператора умножения в формуле часто используют крестик (x ), однако в этих целях в Excel необходимо использовать звездочку (*). Если в формуле использовать знак "x", появится сообщение об ошибке и будет предложено исправить формулу, заменив x на "*".

Отсутствуют ли кавычки вокруг текста в формулах?

Если в формуле содержится текст, его нужно заключить в кавычки.

Например, формула ="Сегодня " & ТЕКСТ(СЕГОДНЯ();"дддд, дд.ММ") объединяет текстовую строку "Сегодня " с результатами функций ТЕКСТ и СЕГОДНЯ и возвращает результат наподобие следующего: Сегодня понедельник, 30.05 .

В формуле в поле "сегодня" есть пробел перед закрывающей кавычкой, чтобы предоставить пустое пространство между словами "сегодня" и "Понедельник, 30 мая". При отсутствии кавычек вокруг текста в формуле может отображаться #NAME? ошибка .

Включает ли формула больше 64 функций?

Заключены ли имена листов в апострофы?

Например, чтобы возвратить значение из ячейки D3 на листе с квартальными данными в книге, введите: = "квартальные данные"! D3 . Без кавычек вокруг имени листа в формуле отображается #NAME? ошибка .

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

Если формула ссылается на внешнюю книгу, правильно ли указан путь к книге?

Например, для ссылки на ячейки a1 – A8 на листе Sales в книге операций Q2, открытой в Excel, введите: = Sales! A1: A8 . При отсутствии квадратных скобок в формуле отображается #REF! .

Если книга не открыта в Excel, введите полный путь к файлу.

Например =ЧСТРОК("C:\Мои документы\[Операции за II квартал.xlsx]Продажи"!A1:A8) .

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

Совет: Чтобы получить путь к другой книге, проще всего открыть ее, ввести в исходной книге знак равенства (=), а затем с помощью клавиш ALT+TAB перейти во вторую книгу и выделить на нужном листе любую ячейку. После этого книгу-источник можно закрыть. Формула автоматически обновится, и в ней отобразится полный путь к имени листа с правильным синтаксисом. При необходимости этот путь можно копировать и вставлять.

Пытаетесь ли вы делить числовые значения на нуль?

Деление ячейки на другую ячейку, которая имеет нулевое значение (0) или отсутствие значения, приводит к #DIVу или 0! Error .

Чтобы устранить эту ошибку, можно просто проверить, существует ли знаменатель.

ЕСЛИ(B1;A1/B1;0)

Смысл это формулы таков: ЕСЛИ B1 существует, вернуть результат деления A1 на B1, в противном случае вернуть 0.

Ссылается ли формула на удаленные данные?

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

Если вам не удается заменить формулы их результатами, прочтите сведения об ошибках и возможных решениях:

Если формула ссылается на ячейки, которые были удалены или заменены другими данными, и возвращает ошибку #REF! Выделите ячейку с #REF! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!. В строке формул нажмите кнопку #REF! и удалите его. Затем снова введите диапазон для формулы.

Если определенное имя отсутствует, а формула, которая ссылается на это имя, возвращает #NAME? ошибка Определите новое имя, которое ссылается на нужный диапазон, или измените формулу таким образом, чтобы она ссылалась непосредственно на диапазон ячеек (например, a2: D8).

Если лист отсутствует, а формула, ссылающаяся на него, возвращает #REF! Ошибка: не удается устранить эту проблему, к сожалению, лист, который был удален, невозможно восстановить.

Если отсутствует книга, это не влияет на формулу, которая ссылается на нее, пока не обновить формулу.

Например, если используется формула =[Книга1.xlsx]Лист1"!A1 , а такой книги больше нет, значения, ссылающиеся на нее, будут доступны. Но если изменить и сохранить формулу, которая ссылается на эту книгу, появится диалоговое окно Обновить значения с предложением ввести имя файла. Нажмите кнопку Отмена и обеспечьте сохранность данных, заменив формулу, которая ссылается на отсутствующую книгу, ее результатами.

Бывает, скопировали таблицу в Excel, а числа в ней не считаются, формулы не работают в Excel . Одной из причин может быть то, что значения ячеек в текстовом формате. Вспоминаем, в Excel есть два формата – формат ячеек и формат значений. Подробнее об этом читайте в статье «Преобразовать дату в текст Excel ».
Итак, рассмотрим, почему в Excel не работает формула .
Нужно сложить верхние числа в ячейках. В ячейках значения написаны в текстовом формате с установкой между строками непечатаемых знаков. Нам нужно преобразовать текстовый формат значений в числовой и выделить первое число в ячейке. У нас такая скопированная таблица из 1С.
Первый этап.
Преобразуем значение ячеек в числовой формат.
Выделяем ячейки. На закладке «Главная» в разделе «Редактирование» нажимаем на кнопку «Найти».
В появившемся диалоговом окне в строке «Найти» ставим запятую». В строке «Заменить» ничего не ставим.
Нажимаем кнопку «Заменить всё». Получится так.
Второй этап.
В этом же окне «Найти и заменить» (мы его не убирали и ячейки выделены остались), в строке «Найти» ставим точку. А в строке «Заменить» ставим запятую. Нажимаем «Выделить всё».

Получилось так.
Убираем окно «Найти и заменить».
Третий этап.
Как выделить первое число в ячейке Excel .
В ячейке В1 пишем такую формулу. =ЛЕВСИМВ(A1;НАЙТИ(СИМВОЛ(10);A1)-1)
Этой формулой мы выделили из ячейки первое число. Копируем формулу по столбцу. Получилось так.
В ячейке В4 мы написали формулу сложения. =B1+B2+B3
Автосумма не работает, но, формулы, написанные вручную работают.
В Excel можно установить в ячейку ссылку на другой лист, диапазон, ячейку, сайт, т.д. При нажатии на эту ссылку, Excel переходит по ней. Но, как выделить саму ячейку, не гиперссылку? Об этом читайте статью "

Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

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

Хочется верить, что хоть когда-нибудь описанную особенность стандарта IEEE754 Microsoft сможет победить или хотя бы сделать заплатку, которая будет производить простые вычисления не хуже 50-рублевого калькулятора:) Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}