Глава 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

- радиус вписанной окружности 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	b2 + c2 – a2
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 =
	+		+
	R1	R2	R3

Самостоятельная работа 11. Проектирование расчетов на рабочем листе.

Откройте книгу Лабораторные работы.xls и на листе Лаб.11 выполните следующие задания.

Задание 1. Дан прямоугольный параллелепипед со сторонами a, b, c.

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

1. Создайте таблицу с исходными данными (значения сторон введите любые).

2. Присвойте значениям сторон соответствующие имена.

3. Вычислите объем:

V = abc

4. Вычислите площадь поверхности:

S = 2(ab + bc + ac)

5. Вычислите длину диагонали, полученному значению присвойте имя d:

d = Ö a² + b² + c²

6. Вычислите угол между диагональю и плоскостью основания, полученному значению присвойте имя j:

j =	arctg (	c	)
Ö a2 + b2

7. Вычислите угол между диагональю и боковым ребром:

a =	p	- j

8. Вычислите объем шара:

Vш =	p d3

Задание 2. В правильной четырехугольной пирамиде заданы длина стороны основания k и высота h.

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

1. Создайте таблицу с исходными данными (значения введите любые).

2. Присвойте значениям к и h соответствующие имена.

3. Вычислите объем, полученному значению присвойте имя V:

V =	к2h

4. Вычислите угол наклона бокового ребра к плоскости основания:

a =	arctg	h Ö 2
a

5. Вычислите радиус описанного около пирамиды шара:

R =	2h2 + к2
4h

6. Вычислите угол наклона боковой грани к основанию:

b =	arctg	2h
к

7. Вычислите радиус вписанного в пирамиду шара, полученному значению присвойте имя r_:

r =	к	tg	b

8. Вычислите площадь полной поверхности пирамиды:

S =	3 V
r

Вопросы для самоконтроля

1. Почему при вычислениях в математических формулах рекомендуется использовать имена?

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

3. На какой вкладке находятся команды для вызова математических функций?

Тесты

1. Укажите неправильную формулу:

A. A2+D4

B. =А1/С4

C. =N8-К8

D. =С2*М6

2. Вычислительные формулы в ячейках электронной таблицы записываются …

A. В обычной математической записи.

B. Специальным образом с использование встроенных функций и по правилам, принятым для записи выражений в языках программирования.

C. По правилам, принятым исключительно для электронных таблиц.

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

3. Выражение 5(A2+C3):(2B2-3D3) в электронной таблице имеет вид:

A. 5(A2+C3)/(2B2-3D3)

B. 5*(A2+C3):(2*B2-3*D3)

C. 5*(A2+C3)/(2*B2-3*D3)

D. 5(A2+C3)/(2*B2-3*D3)

4. Укажите правильную формулу:

A. 10/8*2+3+4*D4

B. C3=C1+2*C2

C. A5B5+23

D. =A2*A3-A4

5. Чему будет равно значение ячейки С1, если в нее ввести формулу =А1+B1?

A. 20

B. 15

C. 10

D. 60

6. Чему будет равно значение ячейки А5, если в нее ввести формулу =СУММ(A1:A4)/2?

A. 220

B. 140

C. 55

D. 110

7. В одной из ячеек электронной таблицы записано арифметическое выражение =50+25/(4*10-2)*8. Какое математическое выражение ему соответствует?

A.

B.

C.

D.

8. Среди приведенных ниже записей формулой для электронной таблицы является только:

A. A2+D4B3

B. A2+D4*B3

C. A1=A2+D4*B3

D. =A2+D4*B3

9. Какая из формул содержит в записи ошибку?

A. =А3*SIN(B3)+5

B. G3/B3+4А5

C. =(A13+(D3-2)*5)/7)

D. =COS(D3+8)

10. Выражение 10(3B2-A3): 4(A2+B2), записанное в соответствии с правилами, принятыми в математике, в электронной таблице имеет вид:

A. =10(3*B2-A3): 4(A2+B2)

B. =10*(3*B2-A3)/4*(A2+B2)

C. =10(3B2-A3)/4(A2+B2)

D. =10(3B2-A3):(4(A2+B2))

11. Какую функцию следует использовать для преобразования угловых градусов в радианы?

A. ГРАДУСЫ()

B. ЗАМЕНИТЬ()

C. ПОДСТАВИТЬ()

D. РАДИАНЫ()

12. Какую функцию следует использовать для преобразования величины угла, указанного в радианах, в градусы?

A. ГРАДУСЫ

B. ЗАМЕНИТЬ

C. ПОДСТАВИТЬ

D. РАДИАНЫ

13. В каких единицах измерения должен быть указан аргумент функции COS()?

A. В градусах.

B. В радианах.

C. В градусах или в радианах, по усмотрению пользователя.

D. Не имеет значения.

14. В каких единицах измерения должен быть указан аргумент функции SIN()?

A. В градусах.

B. В радианах.

C. В градусах или в радианах, по усмотрению пользователя.

D. Не имеет значения.