Глава 11. Виды ошибок в Excel. Проектирование расчетов на рабочем листе

Типы ошибок в Excel

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

Таблица 11.1

Типы ошибок

Код ошибки	Описание (причины и примеры)
#######	Ширина столбца недостаточна для того, чтобы вместить значение
#ЗНАЧ!	- В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст. - Для оператора или функции, требующей одного значения, указывается диапазон.
#ДЕЛ/0!	В формуле содержится явное деление на ноль (например, =А1/0) или ИСПОЛЬЗУЕТСЯ ссылка на пустую ячейку или ячейку, содержащую 0 в качестве делителя.
#ИМЯ?	- Используется имя ячейки или диапазона, которое не было определено. - Ошибка в написании имени ячейки или диапазона. - Ошибка в написании имени функции. - В ссылке на диапазон ячеек пропущено двоеточие (:) - В формулу введен текст, не заключенный в двойные кавычки.
#Н/Д	Значение недоступно функции или формуле, т.е. не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа.
#ССЫЛКА!	Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.
#ЧИСЛО!	- Функция КОРЕНЬ для отрицательного числа - Числовое значение результата вычисления формулы слишком велико или слишком мало, чтобы его можно было представить в Excel
#ПУСТО!	Задано пересечение двух областей, которые в действительности не имеют общих ячеек

Вычисления в Excel

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

Задание 1. Вычисление элементов треугольника.

Даны три стороны треугольника а, b, с. Требуется вычислить:

- площадь треугольника S:

S = Ö p (p – a) (p – b) (p – c),

где полупериметр p равен:

p =	a + b +c
	2

- радиус вписанной окружности r:

r =	S
	p

- радиус описанной окружности R:

R =	abc
	4S

Ваши действия:

1. Откройте книгу Упражнения.xlsx.

2. Добавьте в книгу новый лист, переименуйте его в Упр.11.

3. В ячейки В2: В4 введите числа и присвойте им соответствующие имена из левого столбца (рис. 11.1).

Рис. 11.1. Исходные данные

Обратите внимание, какое имя получила ячейка В4. Это связано с тем, что имена c и r в Excel зарезервированы: c (column) – столбец, r (row) – строка. Поэтому Excel ввел в имя символ подчеркивания – с_.

4. В ячейке В8 вычислите площадь по предложенной выше формуле.

5. Задайте длину стороны а, равную 10. В ячейках с результатами появится сообщение об ошибке – #ЧИСЛО! Дело в том, что стороны 10, 4, 5 не могут образовать треугольника. При вычислении площади под корнем получается отрицательное число.

6. Необходимо переделать формулы таким образом, чтобы пользователь получал сообщение, почему не могут быть вычислены значения S, R и r, а в ячейках с результатами вычислений R и r ничего не должно выводиться.

7. В ячейке В7 отдельно вычислите подкоренное выражение. Анализ подкоренного выражения можно выполнить с помощью функции ЕСЛИ(). Если подкоренное выражение получится положительным, вычислите S, R и r. Если же нет, то в ячейке В8 введите текстовую строку «Это не треугольник!», а в ячейках В10 и В12 выведите пустые строки (рис. 11.2).

Рис.11.2. Формулы с использованием функции ЕСЛИ()

Задание 2. Самостоятельно выполните следующие примеры.

1. Вычислите сторону треугольника а по заданному R = 3. Используйте команду Данные, Анализ «что-если», Подбор параметра.

2. Вычислите углы треугольника А, В, С (по теореме косинусов), используя функцию ACOS(). В результате углы будут вычислены в радианах (рис.11.3).

A =	arccos	b² + c² – a²
		2bc

Рис.11.3. Вид формул для вычисления углов треугольника

3. Переведите углы, вычисленные в радианах в градусы, используя функцию ГРАДУСЫ(). Вычислите отдельно сумму углов для углов треугольника, выраженных в радианах и в градусах соответственно (рис. 11.4).

Рис. 11.4. Пересчет радиан в градусы

4. Вычислите длину и площадь окружности по заданному радиусу R по следующим формулам: pR² и 2pR. Такие формулы с использованием функции ПИ() в Excel выглядят, как показано на рис.11.5.

Рис.11.5. Вычисление площади и длины окружности

5. Вычислите расстояние между двумя точками на плоскости, заданными своими координатами (рис. 11.6) по следующей формуле: Ö(Х2-Х1)^2+(Y2-Y2)^2. В формуле должны быть использованы имена координат.

Рис. 11.6. Координаты точек

6. Вычислите общее сопротивление трех параллельных сопротивлений по формуле

R =	1
	1	+	1	+	1
	R1		R2		R3