Проблемы с формулами в электронной таблице Excel

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

Решение 1. Измените формат ячейки

Очень часто Excel отказывается выполнять расчеты из-за того, что выбран неправильный формат ячеек.

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

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

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

1. Чтобы определить текущий формат ячейки (диапазон ячеек), выберите его и, находясь на вкладке "Главная", обратите внимание на группу инструментов "Число". Здесь есть специальное поле, в котором отображается используемый в данный момент формат.
2. Вы можете выбрать другой формат из списка, который откроется после того, как мы нажмем на стрелку вниз рядом с текущим значением.

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

1. Выделив ячейку (или выделив диапазон ячеек), щелкните по ней правой кнопкой мыши и в открывшемся списке нажмите на команду «Формат ячейки». Или вместо этого после выбора нажмите комбинацию Ctrl + 1.
2. В открывшемся окне мы окажемся во вкладке "Число". Здесь, в списке слева, представлены все доступные форматы, из которых мы можем выбирать. С левой стороны отображаются настройки выбранной опции, которые мы можем изменить по своему усмотрению. Нажмите, когда будете готовы OK.
3. Чтобы изменения отразились в таблице, активируем режим редактирования поочередно для всех ячеек, в которых формула не сработала. Выбрав нужный элемент, можно перейти к редактированию, нажав клавишу F2, дважды щелкнув по нему или щелкнув внутри строки формул. После этого, ничего не меняя, нажмите кнопку Enter.

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

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

Решение 2: отключить режим «Показать формулы»

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

1. Перейти на вкладку «Формулы». В группе инструментов «Формульная зависимость» нажмите на кнопку «Покажи формулы»если он активен.
2. В результате в ячейках с формулами теперь будут отображаться результаты вычислений. Правда из-за этого могут измениться границы столбцов, но это поправимо.

Решение 3. Активируйте автоматический пересчет формул.

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

1. Перейти в меню "файл".
2. Выберите раздел из списка слева «Параметры».
3. В появившемся окне перейдите в подраздел «Формулы». В правой части окна в группе «Варианты вычислений» установите флажок рядом с опцией "автоматически"если выбран другой вариант. Нажмите, когда будете готовы OK.
4. Все готово, теперь все результаты формул будут пересчитываться автоматически.

Решение 4: исправление ошибок в формуле

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

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

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

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

Распространенные ошибки

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

• #ДЕЛ/0! – результат деления на ноль;
• #Н/Д – ввод недопустимых значений;
• #ЧИСЛО! – неправильное числовое значение;
• #ЦЕНИТЬ! – в функции используется неправильный тип аргумента;
• #ПУСТОЙ! – неправильный адрес диапазона;
• #СВЯЗЬ! – ячейка, на которую ссылается формула, удалена;
• #ИМЯ? – недопустимое имя в формуле.

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, правильно ли заполнены все данные в ячейках, участвующих в формуле. Затем проверяем саму формулу и наличие в ней ошибок, в том числе противоречащих законам математики. Например, деление на ноль не допускается (ошибка #ДЕЛ/0!).

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

1. Отмечаем ячейку, содержащую ошибку. Во вкладке «Формулы» в группе инструментов «Формульные зависимости» нажми на кнопку «Рассчитать формулу».
2. В открывшемся окне будет отображена пошаговая информация по расчету. Для этого нажмите кнопку «Вычислить» (каждое нажатие переходит к следующему шагу).
3. Таким образом, вы сможете отслеживать каждый шаг, найти ошибку и исправить ее.

Вы также можете воспользоваться полезным инструмент «Проверка ошибок», который находится в том же блоке.

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

Заключение

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