Диагностика ошибок в формулах Excel

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

Сообщения об ошибках в Excel могут принадлежать к одному из восьми типов: ######; #ЗНАЧ!; #ДЕЛ/0!; #ИМЯ?; #Н/Д; #ССЫЛКА!; #ЧИСЛО!; #ПУСТО!.

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

Ошибка #ЗНАЧ! возникает, когда используется недопустимый тип аргумента, например пользователь пытается сложить текстовое и числовое значения.

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. В частности, такая ситуация возникнет, если:

• при наборе имени произошла опечатка;

• текст ошибочно не был заключен в двойные кавычки;

• в ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибка #Н/Д является сокращением термина "неопределенные данные".

Ошибка #ССЫЛКА! появляется, когда при ссылке на ячейку указывается недопустимый адрес.

Сообщение об ошибке вида #ЧИСЛО! возникает в том случае, когда в формуле задан неприемлемый аргумент для функции.

Сообщение об ошибке тина #ПУСТО! выводится, когда используется ошибочная ссылка на ячейку или диапазон; скажем, задано пересечение двух областей, которые не имеют общих ячеек.

10.1..6. Контроль достоверности ввода данных

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

1. Контроль формата вводимых данных – указывается допустимый тип данных: целое, действительное число, дата, время, текст определенной длины.

2. Контроль диапазона значений вводимых данных – значение между заданными границами, вне заданных границ; а также значения операндов в операциях отношения (сравнения): равно, неравно, больше, меньше, больше или равно, меньше или равно.

3. Контроль принадлежности вводимого значения фиксированному списку – перечисление через точку с запятой значений либо ссылка на именованный блок или диапазон ячеек. Для ячейки создается управляющий элемент – поле со списком для выбора. При логическом контроле вводи -

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

Для выбора метода контроля вводимых данных в ячейки служит команда Работа с данными на панели Данные:

• на вкладке Параметры следует указать метод контроля и его параметры;

• вкладке Сообщение для ввода можно подготовить вид окна ввода данных в ячейку;

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

Условия проверки ввода данных можно распространять и на другие ячейки таблицы. Для этого требуется:

1. Выделить ячейку, содержащую условия проверки.

2. Выполнить команды меню Правка/Коиировать.

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

4. Выполнить команды меню Правка/Специальная вставка/Вставить условия на значение.

Для удаления условий проверки следует повторно выделить блок ячеек, выполнить команду Работа с данными на панели Данные, указать тип данных – "любое значение". Если требуется, чтобы ячейка, на которую накладываются ограничения, оставалась пустой, а также если формула включает в себя изначально пустые ячейки, устанавливается флажок Игнорировать пустые ячейки.

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

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

2. Выполнить команду Условное форматирование на панели Главная.

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