Тема: Проверка и отладка рабочих листов

Исследование зависимостей

Программа Excel имеет ряд мощных и гибких средств, которые помогут вам проверить и отладить создаваемые рабочие листы.

Большинство средств проверки и отладки Excel доступны на на вкладке Формулы в группе Зависимости формул.

 

 

Рис. 7.1

Зависимые ячейки – показывает стрелками ячейки, зависящие от выделенной ячейки.

Влияющие ячейки – показывает стрелками ячейки, влияющие на выделенную ячейку.

 

Убрать стрелки – удаление стрелок влияющих и зависимых ячеек.

 

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

 

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

 

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

 

 

Трассировка зависимостей ячеек.

 

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

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

 

Влияющие и Зависимые ячейки

 

Термины «влияющие» и «зависимые» ячейки связаны с зависимостями ячеек, содержащих формулы, от других ячеек.

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

Рис. 7.2

Зависимые ячейки – это ячейки, которые используют значение выделенной ячейки. Ячейка, которая имеет зависимые ячейки, может содержать формулу или константное значение.

 

Рис. 7.3

Трассировка ошибок

 

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

 

 

 

Рис. 7.4

 

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

 

Сообщение об ошибочных данных

 

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

 

#ДЕЛ / 0! (#DIV / 0!),

#ИМЯ? (#NAME?),

#ЗНАЧ! (#VALUE!),

#ССЫЛКА! (#REF!),

#Н/Д (#N/A),

#ЧИСЛО! (#NUM!),

#ПУСТО! (#NULL!).

 

Описание ошибочных значений:

Таблица 7.1

 

Ошибочное значение	Описание
#ДЕЛ / 0!	Попытка деления на ноль. Эта ошибка обычно связана с тем, что вы создали формулу, которая ссылается на пустую ячейку.
#ИМЯ?	В формуле используется имя, отсутствующее в списке имен окна диалога присвоение имени. Возможно, вы сделали опечатку при вводе имени или указали имя, которое было удалено. Excel также выводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки.
#ЗНАЧ!	Введена математическая формула, которая ссылается на текстовое значение.
#ССЫЛКА	Отсутствует диапазон ячеек, на который ссылается формула (возможно, вы ее удалили).
#Н/Д	Нет данных для вычислений. При построении модели можно ввести #Н/Д в ячейки и тем самым показать, что они зарезервированы для ожидаемых в дальнейшем данных. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д.
#ЧИСЛО!	Задан неправильный аргумент функции. #ЧИСЛО! может также указывать на то, что значение слишком велико или слишком мало и не может быть представлено на листe. Excel может вычислять положительные значения приблизи- тельно от 1,00E – 307 до 9,99E+307. Если формула возвращает значение вне этого диапазона, Excel сохраняет число как текст и назначает ячейке с формулой ошибочное значение.
#ПУСТО!	В формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

 

Ограничение ввода

 

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

 

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

 

Рис. 7.5

 

Excel отобразит на экране диалоговое окно Проверка вводимых значений с тремя вкладками.

Рис. 7.6

 

1. Щелкните на вкладке Параметры и укажите тип данных, которые должны находиться в данной ячейке.

2. Щелкните на вкладке Сообщения для ввода и введите сообщение, которое должно появляться на экране при выделении указанной ячейки (это необязательно). Это сообщение появится в маленьком всплывающем окне рядом с ячейкой, на которую наложены ограничения.

3. Щелкните на вкладке Сообщение об ошибке и введите сообщение, которое должно появляться в случае введения недопустимых данных (это необязательно).

Практические задания:

1. Рабочему листу дайте имя «Параметры треугольника».

2. В ячейки А1, А2, А3, А5, А7, А9, А11, А13, А15 введите соответственно тексты (но не присваивая этим ячейкам имена): x, y, z, p, S, r, R, h, m.

3. Ячейкам В1, В2, В3, В5, В7 дайте соответственно имена x, y, z, p, S.

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

5. Даны 3 стороны треугольника x,y,z вычислите в ячейке В7 его площадь по формуле , где -полупериметр треугольника.

6. Вычислите для данного треугольника в ячейке В9 радиус вписанной окружности по формуле .

7. Вычислите для данного треугольника в ячейке В11 радиус описанной окружности по формуле .

8. Вычислите в ячейке В13 значение одной из высот данного треугольника по формуле , где a – длина любой стороны треугольника (x,y,z).

9. Вычислите в ячейке В15 значение одной из медиан данного треугольника по формуле

10. Установите причину ошибок, проследив, где необходимо, зависимости ячеек на листе, и устраните ошибки.

Таблица значений x,y,z

Вариант	x	y	z

11. Найдите влияющие ячейки для значения р.

12. Найдите ячейки, зависящие от значения S.

13. Составьте логическую формулу в ячейке р для проверки, являются ли значения x,y,z сторонами треугольника (x+y>z, y+z>x, z+x>y). Если заданные x,y,z не являются сторонами треугольника, то вместо значения р пусть выдаётся «Это не треугольник!»

14. Дайте второму листу имя «Квадратное уравнение».

15. В ячейки А1,А2,А3 введите тексты a,b,c соответственно (в ячейках В1,В2,В3 будут находиться соответственно значения a,b,c).

16. В ячейку В1 разрешите вводить любые числа, кроме 0.

17. В ячейки А5,А7,А9 введите тексты D,x1,x2.

18. Найдите действительные корни квадратного уравнения (значения D,x1,x2 пусть выводятся в ячейках В5,В7,В9 соответственно). Если корней нет, то в ячейке С5 пусть выводится «корней нет», а в В7 и В9 ничего не выводится. Если есть только один корень, то в ячейке В7 пусть он выводится, В9 при этом пусть остаётся пустой.

Значения для ячеек В1,В2,В3

Вариант	В1	В2	В3
	-5
			-5
			-6
	-2		-8