Значения ошибки при вычислениях по формулам

Лабораторная работа № 2 по Excel

«Расчеты с использованием функций Excel . Типы ссылок»

Теоретические сведения:

Описание основных функций.

Категория Дата и время.

Сегодня() - возвращает текущую дату

Год(дата), Месяц(дата), День(дата), ДеньНед (дата;2) - соответственно, год, месяц, день, день недели. Аргумент 2 у функции ДеньНед нужен для отсчета дней с понедельника. Пример: =ДеньНед(Сегодня();2) - вывести текущий день недели в ячейке

Категория Математические.

ABS (число) – модуль числа.

ACOS (число) – арккосинус числа. угол определяется в радианах в интервале от 0 до p.

ASIN (число) – арксинус числа. Угол определяется в интервале от – p/2 до p/2.

ATAN (число) – арктангенс числа. Угол определяется в радианах в диапазоне от -p/2 до p/2.

COS(число) – косинус заданного числа.

EXP(число) – возвращает число е, возведенное в указанную степень.

LN(число) – возвращает натуральный логарифм числа.

LOG(число; основание) – возвращает логарифм числа по заданному основанию.

LOG10 (число) – возвращает десятичный логарифм числа

SIN (число) – возвращает синус заданного числа.

TAN (число) – возвращает тангенс заданного числа.

ГРАДУСЫ (угол) – преобразует радианы в градусы.

ЗНАК (число) – определяет знак числа. 1, если число положительное, 0, если число равно 0, и -1, если число отрицательное.

КОРЕНЬ (число) – возвращает положительное значение квадратного корня.

МОБР (массив) – возвращает обратную матрицу для квадратной матрицы, заданной в массиве. Массив может быть задан как интервал ячеек, например А1:С3, или как массив констант {1;2;3: 4;5;6: 7;8;9} (здесь значения в пределах столбца должны быть разделены двоеточием, в пределах строки – точкой с запятой) или как имя массива или интервала. Ввод матричных формул следует завершать нажатием клавиши CTRL + SHIFT +ENTER.

МОПРЕД (массив) – возвращает определитель квадратной матрицы, заданной в массиве.

МУМНОЖ (массив1; массив2) – возвращает произведение матриц, которые задаются массивами. Результатом является массив с таким же числом строк как массив1, и таким же числом столбцов, как массив2.

ОКРУГЛ (число; число_разрядов) – округляет до указанного числа десятичных разрядов.

ОСТАТ (число; делитель) – возвращает остаток от деления аргумента число на делитель.

ПИ () – возвращает число p с точностью до 15 цифр.

ПРОИЗВЕД(число1; число2; ...) – перемножает числа, заданные в аргументах и возвращает их произведение.

РАДИАНЫ (угол) – преобразует градусы в радианы.

СТЕПЕНЬ (число; степень) – возвращает результат возведения в степень;

СУММ (число1; число2; ...) – возвращает сумму всех чисел, входящих в список аргументов;

СУММЕСЛИ (диапазон_просмотра;условие_суммирования;диапазон_суммирования) - просматривает диапазон просмотра, выбирает ячейки, отвечающие условию суммирования, и суммирует значения из диапазона суммирования. Размеры диапазона просмотра и диапазона суммирования должны совпадать.

СУММКВ (число1; число2; ...) – возвращает сумму квадратов аргументов;

СУММКВРАЗН (массв1; массив2) – возвращает сумму квадратов разностей соответствующих значений в двух массивах;

СУММПРОИЗВ (массив1; массив2; ...) – перемножает соответствующие элементы заданных массивов и возвращает сумму произведений;

ФАКТР (число) – возвращает факториал числа;

ЦЕЛОЕ (число) – округляет число до ближайшего целого;

ЧАСТНОЕ (числитель; знаменатель) – Возвращает частное от деления нацело.

Статистические функции.

МАКС(аргумент1; аргумент2;…) - ищет максимальный из аргументов;

МИН(аргумент1; аргумент2;…) - ищет минимальный из аргументов;

СРЗНАЧ(аргумент1; аргумент2;…) - вычисляет среднее своих аргументов;

СЧЕТЕСЛИ(диапазон; условие) - подсчитывает число аргументов в диапазоне, отвечающих условию

Логические функции служат для выполнения вычислений в зависимости от выполнения некоторого условия. В условиях могут использоваться операции сравнения =, >, <, <>(не равно), >= (больше или равно), <= (меньше или равно).

ЕCЛИ (логическое_выражение; значение1; значение2) - результатом является значение1, если логическое_выражение истинно и значение2 в противном случае. Пример: в ячейке A1 набрано число 30000, а в ячейке B1 формула =ЕСЛИ(A1<20000; 12; 15). Результатом будет число 15, т.к. условие на выполняется.

Функции И, ИЛИ служат для создания сложных условий:

И (логическое_выражение1; логическое_выражение2;....) - возвращает значение “ИСТИНА”, если все аргументы имеют значение “ИСТИНА”, а в противном случае -“ЛОЖЬ”.

ИЛИ (логическое_выражение1; логическое_выражение2;...) - возвращает значение “ИСТИНА”, если хотя бы один из аргументов имеет значение “ИСТИНА”, а противном случае - “ЛОЖЬ”.

Примеры: =ЕСЛИ (И(A1>=20000;A1<40000);15;18) - вычисленное значение равно 15 при величине A1 от 20000 до 40000 и равно 18 в противном случае.

Можно создавать сложные условия и вложением функций ЕСЛИ. Пример:

=ЕСЛИ (A1<20000; 12; ЕСЛИ (A1<40000; 15; 18)) - если величина A1 меньше 20000, вычисленное значение равно 12, иначе если она меньше 40000, то результат равен 15, а в противном случае (то есть, А1 больше 40000) ,формула вернет значение 18.

Значения ошибки при вычислениях по формулам

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

Значение ошибки	Причина
# Дел/0!	Задано деление на 0
# Знач!	Указан неправильный аргумент или неправильный оператор
# Имя?	Указано недопустимое имя
# Н/Д	Значение не указано
# Пусто!	Задана область пересечения двух диапазонов, которые не пересекаются
#Ссылка!	Указана некорректная ссылка
# Число!	Ошибка при использовании или получении числа

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

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

Абсолютная ссылка задает абсолютные координаты ячейки. При копировании формулы абсолютная ссылка на ячейку не будет изменяться. Абсолютная ссылка задается путем указания символа доллара перед номером строки и столбца, например $A$2.

Смешанная ссылка представляет собой комбинацию абсолютной и относительной ссылок, когда для строки и столбца используются разные способы адресации, например, $A1, B$2. При копировании формулы абсолютная часть ссылки не изменяется.

При выделении ссылки на ячейку в режиме редактирования путем нажатия клавиши F4 можно изменять тип ссылки.

Задание 1 Создать прайс-лист по образцу:

Поля "Цена, у.е." и "Сумма" - вычисляемые. В поле "Сегодня" автоматически подставляется текущая дата. При расчете цены в у.е. используется абсолютная ссылка. Заполнить прайс на несколько товаров.

Задание 2Используя смешанные ссылки, построить на новом листе рабочей книги таблицу значений функции 2 переменных f(x,y) в виде

	x1	…	xm
y1	f(x1, y1)	…	f(x1, ym)
…	…	…	…
yn	f(xn, y1)	…	f(xn, ym)

Функцию, границы и шаг изменения аргументов взять из таблицы вариантов. Границы изменения по X и по Y совпадают.

 

№	Функция f(x,y)	Границы по x,y	Шаг по x,y
1	2x-y+x2	[0,2]	0,2
2	y/x-4/x2	[1,3]	0,2
3	sin 2x-y2-cos x	[0,π/2]	π/20
4	0.5(cos 2x-y2)-sin x	[0,π/2]	π/20
5	x(2+x2sin x2-y sin y)	[-2,0]	0,25
6	(x+1)(cos x-y)-sin x	[π/2, 3π/2]	π/12
7	(x+1)sin x –y –x cos x	[0, π]	π/10
8	2x-xy+x3	[1,2]	0,1
9		[-1,1]	0,1
10		[0,4]	0,25
11		[0, π]	π/12
12	x2+y2-max(x,y)	[0, π]	π/10

На этом же листе рабочей книги поместить результаты расчетов по формулам, а именно: с помощью функций Excel определить максимальное, среднее и минимальное значения функции в диапазоне построения, найти сумму ее положительных значений, произведение всех значений, подсчитать количество значений функции, больших значения 1. Оформить рабочий лист, выведенные результаты расчетов снабдить комментариями. Построить график поверхности f(x,y) в заданных границах.

Задание 3 С помощью условной функции «ЕСЛИ» выполнить вычисления по формуле Вашего варианта.

№ варианта	функция	значения аргументов и величин
1.		a=0, b=1 x=-1,0.5,…,2
2.		, шаг по x и по y = 0,5
3.		x=-1,0,1, y=-2,-1,…,2
4.	z=max2(x,y)+min(|x|,|y|,0), если , иначе 1	x,y меняются от -3 до 3 с шагом 0,5
5.	z=x, если x и y - четные, z=y, если x и y - нечетные, z=0, в остальных случаях	x,y меняются от 1 до 6 с шагом 1
6.		a=-1, b=1 x=-3,-2,…,3
7.	, если точка (x,y) принадлежит кругу радиуса 3 с центром в н начале координат; z=0, в противном случае	x,y меняются от -4 до 4 с шагом 1
8.		x,y меняются от -5 до 5 с шагом 1
9.	z=x-y, если x и y - четные, z=x, если x и y - нечетные, z=0, в остальных случаях	x,y меняются от 1 до 6 с шагом 1
10.	z=max2(x,y)+min(|x|,|y|,0), если , иначе 1	x,y меняются от -4 до 3 с шагом 0,2
11.		, шаг по x и по y = 1
12.		, шаг по x и по y = 0,5

 

Задание 4 Работа с базой данных