Ошибки в формулах

Ошибки в формулах


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

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


Сообщение об ошибке в формуле


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


Сообщения об ошибках


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

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

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


Логические функции в Excel


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

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

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).

В логическом выражении функции ЕСЛИ могут использоваться числа, даты, ссылки на ячейки, а также знаки > (больше), < (меньше), = (равно), >= (не меньше), <= (не больше), <> (не равно). Функция ЕСЛИ выполняется следующим образом.



  1. Вычисляется логическое выражение, которое может иметь одно из двух значений: ИСТИНА или ЛОЖЬ.

  2. В зависимости от результата вычисления логического выражения функция возвращает один из двух возможных результатов, которые записаны в аргументах значение_если_истина и значение_если_ложь. Рассмотрим пример. Пусть на предприятии выплачивается надбавка к зарплате за стаж в размере 20 % для работников, имеющих стаж 10 и более лет. Пример расчетной, где показан процесс ввода формулы в ячейку D3 с использованием строки формул. В этой формуле используется логическое выражение C3>=10, чтобы определить право работника на надбавку. Если логическое выражение будет иметь значение ИСТИНА, то сумма надбавки рассчитывается по формуле B3*20%, в противном случае результат функции будет равен нулю.




Пример ввода логической функции ЕСЛИ


Для объединения нескольких условий в одно можно использовать логическую функцию И, которая возвращает значение ИСТИНА, если все входящие условия имеют значение ИСТИНА. Например, условие «значение ячейки A1 должно быть больше 5 и меньше 10» записывается так: И(A1>5;A1<10). Логическая функция ИЛИ возвращаетзначение ИСТИНА, если хотя бы одно входящее условие имеет значение ИСТИНА.

Другой способ постройки сложных логических выражений состоит в использовании вложений функции ЕСЛИ. Этот способ будет рассмотрен в следующей практической работе.

ПРИМЕЧАНИЕ

Количество вложений функции ЕСЛИ может достигать 64, что позволяет задавать сложные условия. Читатели, имеющие понятие о программировании, должны обратить внимание на то, что функция ЕСЛИ является аналогом условного оператора (if… then… else).

Полезные статьи

Комментарии и пинги к записи запрещены.

Комментарии закрыты.

data-title= src=