Материал: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78

1.6. Собственные функции1.6.1. Общие сведенияВ Excel имеется много встроенных функций. Но если приходится часто вычислять однотипные сложные выражения, то их целесообразно оформить в виде отдельных собственных функций.Пусть нам необходимо часто вычислять выражение:Для его вычислении можно создать собственную функцию.Создание функции состоит из следующих этапов:1. Командами:Вид > Панели инструментов > VisualBasicвызывается редактор Visual Basic.(В Excel 2010 на ленте надо включить Разработчик (Файл/Параметры/Настройка ленты и в списке Основные вкладки поставить флажок Разработчик и нажать ОК)2. На появившейся панели выбрать редактор Visual Basic.3. В редакторе выполнить команды: Insert > Module и затемInsert > Procedure4. Появится окно параметров функции.Здесь необходимо задать имя функции (Name), например Lenaи переключатель установить в положение Functionи затем Ok5. Появится заготовка функции вида:Public Function Lena()End Function6. Здесь необходимо:а) в заголовке - указать имя и тип аргумента, передаваемого функции, а также тип результата, возвращаемый функцией. В данном случае: Public Function Lena(X As Double) As DoubleEndFunctionб) внутри функции - выражение для вычисления. В рассматриваемом примере:Public Function Lena(X As Double) As Double Lena = (X ^ 2 + 3 * X + 1) / ( (X – 1) ^ 2 + 5)End FunctionФункция создана.7. Если сейчас вернуться в Excel, то созданную функцию можно вызвать, используя кнопку «Вставка функций». Если все было сделано правильно, то в категории «Определенные пользователем» должна появиться функция Lena. Работа с ней аналогична имеющимся стандартным функциям.1.6.2. Общие сведения о VisualBasicforApplications (VBA)В основе VBA, лежит стандартный Visual Basic, который располагает собственным набором математических операций и встроенных функций. Их список приводится в табл. 1.2, 1.3. Таблица 1.2Математические операции

Операция	Название	Пример	Результат
+	Сложение	3 + 5	8
-	Вычитание	7 - 4	3
*	Умножение	3 * 6	18
/	Вещественное деление	5 / 4	1.25
^	Возведение в степень	2 ^ 3	8
\	Целочисленное деление	7 \ 4	1
Mod	Остаток от целочисленного деления	7 Mod 4	3

Таблица 1.3Математические функции

Название	Обозначение	Запись в Бейсике	Пример	Результат
Синус	Sin(x)	Sin(x)	Sin(0)	0
Косинус	Cos(x)	Cos(x)	Cos(0)	1
Тангенс	Tg(x)	Tan(x)	Tan(0.785)	1
Арктангенс	ArcTan(x)	Atn(x)	Atn(1)	0,785
Натуральный логарифм	Ln(x)	Log(x)	Log(10)	2,302585
Модуль числа	│x│	Abs(x)	Abs(-12)	12
Экспонента	ex	Exp(x)	Exp(1)	2,718282
Целая часть числа		Int(x)	Int(99,8) Int(-99,8) Int(-99,2)	99 -100 -100
Отсечение дробной части числа		Fix(x)	Fix(99,2) Fix(-99,2) Fix(-99,8)	99 -99 -99
Корень квадратный		Sqr(x)	Sqr(9)	3
Знак числа		Sgn(x)	Sgn(3) Sgn(0) Sgn(-3)	1 0 -1

Вычисление сложных выраженийВычисления в сложных выражениях производятся слева направо с учетом приоритета операций.

Название	Обозначение	Приоритет
возведение в степень	^	1
умножение, деление	*, /	2
сложение, вычитание	+, -	3

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

Выражение	Запись в Visual Basic
x+3	X+3
	(X+Y)/4
	X+Y/4
	Log(2*X^2)
	Exp(-Abs(X))
	(X+5)^(1/3)
	Log(3*X+5)/Log(10)
	Sin(X)^2+Cos(X^2)
	(Log(Abs(x^(5/3))/Log(7))^2

При вычислении сложного выражения удобнее разбить его на ряд простых, вычислить их отдельно и только затем вычислить исходное.НапримерНам необходимо создать собственную функцию, которая вычисляет выражение: .Выражение очень сложное. В частности, при его наборе очень легко ошибиться в расстановке скобок. Для снижения вероятности ошибок его можно разбить на три части и вычислить отдельно каждую из них. Тогда текст собственной функции будет иметь вид:FunctionLena(XasDouble) AsDoubleA = 1+ Exp(- X^2) + Sqr(Tan(Abs(X)) – 1 / Tan(1 / X))B = Log(1 + X^4) / Log(3) – (Sin(X) + Cos(X))^(1 / 5)C = Atn(X / 2) – 3

Lena = A / B * CEndFunction1.6.3. Варианты заданийСоздать собственные функции согласно таблице 1.5.Номер задания соответствует номеру студента по классному журналу. Проверочные значения позволяют проверить правильность составленных выражений без калькулятора. Таблица 1.5

№	F(x)	Проверочные значения
		X1	X2
1		2	4
2		1	3
3		1	3
4		2	5
5		1	4
6		1	5
7		1	4
8		1	2
9		3	5
10		4	6
11		1	3
12		1	5
13		1	5
14		2	5
15		1	5

II. Численные методы

1. 
   Решение алгебраических уравнений
   

2.1.1. Общие сведенияС помощью инструмента Подбор параметра в Excel можно решать уравнения видаf(x) = C, (2.1)где f(x) – непрерывная функция, а С ­– некоторая постоянная.Применение этого инструмента можно разделить на два шага.

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

2.1.2. ПримерЦена на товар вначале увеличилась на 25%,а затем снизилась на 15%, после чего она стала равной 163 руб. Определить исходную цену товара. Решение. 1. Подготовим в Excel таблицу для расчета итоговой цены, считая первоначальную цену известной и равной, например, 100 р.

	A	B
1	Исходная цена	100
2	Цена после повышения на 25%	=B1*1,25
3	Цена после снижения на 15%	=B2*0,85

В итоге в ячейке B3 получим значение, равное 106,25. Чтобы подобрать исходную цену, при которой итоговая цена станет равной 163 р. выполните2. Выберите в меню Сервис команду Подбор параметра…Рис.2.1. Окно Подбор параметраВ появившемся окне (рис.2.1) введите для поля Установить в ячейке