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

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

Таблица 5.9. Ошибки в формулах

Обозначение ошибки	Причина возникновения	Возможное решение
####	· Столбец недостаточно широк · Дата и время являются отрицательными числами	· Измените ширину столбца · Проверьте корректность ввода данных
#ЗНАЧ! (#VALUE!)	Используется недопустимый тип аргумента или операнда. Например, вместо числа используется текст.	Вместо арифметических операторов используйте для выполнения арифметических операций над содержимым ячеек с текстом функции
#ДЕЛ/0 (#DIV/0)	Деление числа на 0 (ноль) или на ячейку, в которой нет значения.	Например, если ошибка возникает в формуле =A1/A2, можно заменить ее формулой =ЕСЛИ(A2=0;"";A1/A2), чтобы она возвращала пустую строку, или формулой =ЕСЛИ(A2=0;0;A1/A2), чтобы она возвращала 0
#ИМЯ? (#NAME?)	MS Excel не может распознать имя, используемое в формуле	· Убедитесь в том, что имя, используемое в формуле, действительно существует. · Если формула содержит ссылки на ячейки других листов или книг, а имя другой книги или листа содержит небуквенные символы или пробел, это имя необходимо заключить в одиночные кавычки (').
#Н/Д (#N/A)	Значение недоступно функции или формуле	· Если в несколько ячеек введена формула массива, проверьте, что диапазоны, используемые в формуле, содержат то же количество строк и столбцов. · Задайте все обязательные аргументы для функции, которая возвращает ошибку. · Убедитесь в том, что аргументы функции верны и помещены в правильные позиции
#ССЫЛКА! (#REF!)	Ссылка на ячейку указана неверно	Проверьте аргументы функции и убедитесь, что они ссылаются на допустимые ячейки или диапазоны ячеек
#ЧИСЛО! (#NUM)	· В функции, требующей числовой аргумент, используется неверный тип данных; · Результат формулы дает число, слишком большое или слишком малое для представления в Excel.	· Убедитесь в том, что в функции используются только числовые аргументы · Измените формулу так, чтобы ее результат находился в диапазоне от -1*10307 до 1*10307.
#ПУСТО! (#NULL)	Задано пересечение двух областей, которые в действительности не имеют общих ячеек. Оператором пересечения областей является пробел между ссылками	Проверьте аргументы функции и убедитесь, что они ссылаются на допустимые диапазоны ячеек

Обнаружение ошибок

При возникновении ошибки MS Excel отображает в левом верхнем углу ячейки зеленый треугольник (индикатор ошибки). При выборе такой ячейки появляется — смарт-тег проверки ошибок (Рисунок 5.23).

Индикатор ошибки

Смарт-тег проверки ошибок

Рисунок 5.23. Смарт-тег проверки ошибок

 

Если на листе уже выполнялась проверка ошибок, то ошибки, которые были пропущены, не будут отображаться, пока их состояние не будет сброшено. Для сброса состояния пропущенных ошибок выполните следующие действия:

1. На вкладке Файл выберите пункт Параметры.

2. На вкладке Формулы в разделе Контроль ошибок нажмите кнопку Сброс пропущенных ошибок.

3. Нажмите кнопку ОК.

Исправление ошибок

Для исправления ошибки можно воспользоваться списком действий, предоставляемым cмарт-тегом проверки ошибок (Рисунок 5.23). В случае если будет выбран пункт Пропустить ошибку (Ignore Error), такая ошибка при последующих проверках отображаться не будет.

Для проверки ошибок необходимо выполнить следующие шаги:

1. Выберите лист, который требуется проверить на наличие ошибок.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок… (Error Checking…). Откроется окно диалога Контроль ошибок (Error Checking) (Рисунок 5.24).

Рисунок 5.24. Окно диалога Контроль ошибок

 

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов ее исправления нажмите кнопку Справка по этой ошибке.

5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы (Рисунок 5.25), где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчета формулы.

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

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

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

d. Чтобы снова увидеть вычисления, нажмите кнопку Заново.

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

Рисунок 5.25. Окно диалога вычисление формулы.

 

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

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

8. Для перехода к следующей ошибке нажмите кнопку Далее (Next). Для возврата к предыдущей – кнопку Назад (Previous).

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.