Задания 2.1 / Поиск и исправление ошибок в вычислениях

Поиск и исправление ошибок в вычислениях

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

MS Excel 2007 облегчает поиск источника ошибки, указывая ячейки, которые используются в вычислениях, и предоставляя описание ошибки.

При обнаружении ошибки рядом с активной ячейкой появляется кнопка. Щелчок на этой кнопке открывает меню параметров,

предоставляющих сведения об ошибке и предлагающих помощь по ее исправлению (см. Рис.1).

Рисунок 1 – Меню параметров ошибки В таблице 1 представлены наиболее распространенные коды ошибок и

дается их описание.

						Таблица 1
Код ошибки	Описание
#####	Не хватает ширины ячейки для
	отображения числового значения
#ЗНАЧ!	Неправильный			тип		аргумента.
	Введена	математическая				формула,
	которая	ссылается			на	текстовое
	значение
#ИМЯ?	Формула содержит текст, который
	MS Excel		не	может		распознать
	(например,		имя		неизвестного
	диапазона). Например, в формуле
	используется имя, отсутствующее в
	списке		имен		окна	диалога
	Присвоение имени. MS Excel также

	выводит эту ошибку в том случае,
	когда строка символов не заключена
	в двойные кавычки
#ССЫЛКА!	Отсутствует диапазон ячеек, на
	который ссылается формула
#ДЕЛ/0!	Попытка деления на ноль. Ошибка
	также связана со ссылкой делителя на
	пустую ячейку
#Н/Д	Нет данных для вычислений
#ЧИСЛО!	Задан	неправильный	аргумент
	функции
#ПУСТО!	В формуле указано		пересечение
	диапазонов, но эти диапазоны не
	имеют общих ячеек

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

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

2007 идентифицирует влияющие ячейки с помощью синей стрелки,

направленной от влияющей ячейки к активной, содержащей формулу.

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

Ячейки, значения которых вычисляются на основе значений других ячеек,

называются зависимыми. Аналогично тому, как это делается для выявления влияющих ячеек, можно выполнить следующую команду. На ленте вкладка

Формулы группа Зависимости формул кнопка Зависимые ячейки.

Для того чтобы убрать стрелки, отражающие влияющие и (или)

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

Для содержательного поиска ошибок можно воспользоваться диалоговым окном Контроль ошибок, которое открывается, если активизировать команду Проверка наличия ошибок (см. Рис. 2).

Рисунок 2 – Вызов диалогового окна Проверка наличия ошибок Это диалоговое окно позволяет перемещаться по формуле по шагам,

пропускать ошибки, а также переходить к предыдущей или следующей ошибке. Активизировав команду Параметры, можно отобразить диалоговое окно Параметры Excel и настроить поиск ошибок (см. Рис. 3).

Рисунок 3 – Окно настройки вычислений в MS Excel 2007

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

которое открывается, если активизировать команду Вычислить формулу в

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

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

расположенное в других рабочих листах и даже рабочих книгах. Чтобы задать контрольное значение, щелкните в ячейке с формулой, а затем щелкните на кнопке Окно контрольного значения в разделе Зависимости формул вкладки Формула. Щелкните на кнопке Добавить контрольное значение и укажите ячейки, которые нужно контролировать.

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

можно удалить контрольное значение, выделив его и щелкнув по кнопке

Удалить контрольное значение.

Пример использования средств проверки формул при выявлении и исправлении ошибок.

Рассмотрим таблицу MS Excel (см. Рис. 4)

Рисунок 4

Щелкните в ячейке D20

На вкладке Формулы в разделе Зависимости формул щелкните на кнопке Окно контрольного значения. Появится окно контрольного значения (см. Рис. 4)

Щелкните на кнопке Добавить контрольное значение, а затем в диалоговом окне, которое появится, щелкните по кнопке Добавить. В окне контрольного значения появится D20

Щелкните в ячейке D8. В строке формул появится =СУММ(С3:С7)

На вкладке Формулы в разделе Зависимости формул щелкните на кнопке Влияющие ячейки.

Между ячейкой D8 и диапазоном ячеек С3:С7 появится стрелка,

указывающая, что значения в ячейках С3:С7 влияют на значение в ячейке D8

(см. Рис 5).

Рисунок 5

На вкладке Формулы в разделе Зависимости формул щелкните на кнопке Убрать стрелки. Стрелка исчезнет.

Щелкните в ячейке А1. На вкладке Формулы в разделе

Зависимости формул щелкните на кнопке Проверка наличия ошибок.

Появится диалоговое окно Контроль ошибок (см. Рис. 6).

Рисунок 6 – Диалоговое окно Контроль ошибок

Щелкните на кнопки Продолжить, а затем Далее. Появится сообщение, указывающее, что на рабочем листе больше ошибок нет.

Щелкните на кнопке ОК.

На вкладке Формулы в разделе Зависимости формул щелкните на стрелке кнопки Проверка наличия ошибок, а затем щелкните на команде

Источник ошибок.

Появятся синие стрелки, направленные от ячеек C12 и D19 к ячейке

D21 (см. Рис. 7), которые указывают, что значения в этих ячейках (или отсутствие значений, как в данном случае) приводит к ошибке в ячейке D21.

Рисунок 7

 В строке формул переопределите ссылку (введите С12/D20). В

ячейке D21 появится значение 14,34%.