3.3 Решение нелинейных уравнений

Используя надстройку «Поиск решения» можно найти решение одного нелинейного уравнения или системы уравнений. Корень одного уравнения можно получить, если в окне «Поиск решения» отметить переключатель «Установить целевую ячейку: равной значению 0».

Пусть требуется найти корень уравнения

\

Поскольку формула, по которой вычисляется значение функции,сложная, следует оформить алгоритм определения значения этой функции в виде подпрограммыFunction. Так как функция может иметь несколько корней, следует построить график зависимости функции от аргументахи выбрать отрезок, на котором эта функция меняет знак. На рисунке 3.9 представлено окно «Поиск решения», с помощью которого был получен результат х=36,67. Указать в поле «Ограничения», что х – целое число здесь нельзя.

Введем в рассмотрение функцию Z(x), равную (F(x))². ФункцияF(x) принимает значение ноль в точкех, в которойZ(x) имеет минимум, и этот минимум равен нулю. МинимумZ(x) определяется в соответствии с алгоритмом, описанным в разделе 3.1. Однако, при вычислении минимумаZ(x) надо учесть тот факт, что в окрестности точки минимума значения минимизируемой функции могут оказаться меньше заданной в окне «Параметры» относительной погрешности ε, а это приводит к тому, что программа работать не будет. В этом случае для получения решения надо либо уменьшить величину ε, либо умножить функциюZ(x) на большое положительное число (1000, 10000, …).

Рис.3.9 Решение нелинейного уравнения

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

Примеры использования надстройки «Поиск решения» приведены в Excelв файле C:\Program Files\Office11\Samples\SolvSamp.xls.

4. Лабораторные работы

Лабораторная работа №1

Решение нелинейного уравнения

Задание 1

Найти корни нелинейного уравнения:

Ва-риант	Уравнение	Ответ
1	для значений =[-2;2]	0
2		-0,94644
3	для =[-3;3]	-0,32; 1,229997; 2,010001
4		4,700766
5		3,542723

Задание 2

Определить, сколько надо докупить акций второго типа, чтобы доход по всем ценным бумагам составил 12%:

Вариант	Кол-во акций 1го типа	Стоимость акций 1го типа	Доходность акций 1го типа, %	Кол-во акций 2го типа	Стоимость акций 2го типа	Доходность акций 2го типа, %
1	40	90	10	10	40	11
2	25	80	11	4	40	14
3	35	75	11	10	50	16
4	40	100	10	20	45	16
5	60	120	10	6	60	15

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

Оптимизация нелинейной функции

Задание 1

Выполните все приведенные в разделах 3.1 и 3.2 примеры. Каждый пример размещайте на отдельном рабочем листе Excel.

Задание 2

Для аргумента , изменяющегося отдос шагом0.1, вычислите функцию, в соответствии с указанным преподавателем вариантом. Постройте график зависимости функции от аргумента. Используя «Поиск решения» найдите экстремум (максимум или минимум) этой функции на заданном отрезке изменения аргумента. Вычисление функции оформите в виде подпрограммыFunction. Затем найдите целое максимальное значение функции.

№ варианта	Формула
1		0	3,25	0,2
2		-1	2,3	0,2
3		0	3,2	0,2
4		-0,2	2,5	0,1

Лабораторная работа №3

Линейные модели

Задание 1

Предприятие выпускает три вида продукции:

• телевизоры;

• стереосистемы;

• акустические системы.

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

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

Математическая модель

Введем обозначения:

х1 – количество телевизоров,

х2 – количество стереосистем,

х3 – количество акустических систем.

Прибыль по каждому из видов изделий равна норме прибыли на 1 изделие умноженной на количества изделий. Целевая функция («Прибыль всего») вычисляется по формуле

F=75*x1+50*x2+35*x3

Исходные значения х1, х2 и х3 указаны в столбце «План производства». Для максимизации прибыли, надо так выбрать эти значения, чтобы они соответствовали максимуму функции F.

В таблице 1 в столбцах с 3 по 7 приведены количества комплектующих, необходимые для производства каждого из трех видов продукции: телевизора, стереосистемы и акустические системы. Общее количество комплектующих каждого вида (шасси, кинескопы, динамики и т.д.) вычисляются в строке «Расход комплектующих» по формулам:

K_шасси=1*x1+1*x2

K_{кинескоп} =1*x1

K_{динамик}=2*x1+2*x2+1*x3

K_{блок_пит} =1*x1+1*x2

K_{электр_пл.}=2*x1+1*x2+1*x3

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

K_шасси ≤ 450,K_{кинескоп} ≤ 250,K_{динамик} ≤ 800,

K_{блок_пит} ≤ 450,K_{электр_пл.} ≤ 600.

Следует также учесть не отрицательность величин х1, х2 и х3. Итого, имеем 8 ограничений.

Варианты заданий

Таблица для решения задачи в Excel

Наименование продукции	План производства, шт.	Наименование комплектующих					Норма прибыли на 1 изделие	Прибыль по видам изделий
		Шасси	Кинескопы	Динамики	Блоки питания	Электрон. Платы
Телевизор	200	1	1	2	1	2	75	15000
Стереосистема	200	1	0	2	1	1	50	10000
Акустическая система	0	0	0	1	0	1	35	0
Запас комплектующих на складе		450	250	800	450	600
Расход комплектующих		0	0	0	0	0
Прибыль всего								25000

Задание 2.

Транспортная задача. Трем фирмам поставляют комплектующие 3 цеха. Ежедневные потребности в комплектующих для каждого из предприятий равны или больше 200, 100 и 100 единиц соответственно. Из цехов можно вывезти ежедневно: 170 единиц комплектующих изделий из первого, 120 - из второго и 150 - из третьего. Тарифы перевозок даны в таблице A.

таблице A

\ Цеха Фирмы \	Цех 1	Цех 2	Цех3
Фирма 1	6	8	9
Фирма 2	4	3	5
Фирма 3	9	5	2

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

Математическая модель

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

Здесь первый индекс означает номер фирмы, второй – номер цеха. Количество изделий (), вывезенных из каждого цеха, подсчитывается по формулам:

Обозначим ограничения по производительности цехов. В поле «Ограничения» окна «Поиск решения» следует записать ограничения:

Количество изделий (), доставленных на каждую из фирм, подсчитывается по формулам:

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

Варианты заданий

№ варианта	Ограничения по производительности цехов			Ограничения количества комплектующих по потребностям фирм
	Цех 1	Цех 2	Цех 3	Фирма 1	Фирма 2	Фирма 3
1	150	200	250	200	160	120
2	180	180	200	200	160	120
3	200	150	180	200	160	120
4	200	150	180	180	150	130
5	200	150	180	150	200	100

Таблица для решения задачи в Excel

	Затраты на перевозку одного изделия			Ограничения количчества изделий по потребностям фирм	Затраты на перевозку всех изделий
	Цех 1	Цех 2	Цех 3
Фирма 1	6	8	9	200
Фирма 2	4	3	5	100
Фирма 3	9	5	2	100
Ограничения по производительности цехов	170	120	150
	Количество перевозимых изделий
	Цех 1	Цех 2	Цех 3	Вывезено на фирмы
Фирма 1	0	120	80	200	1680
Фирма 2	30	0	70	100	470
Фирма 3	140	0	0	140	1260
Вывезено из цехов	170	120	150
			Всего		3410

Содержание

1.Решение нелинейных уравнений и оптимизация вExcel

1.1 Надстройки в Excel3

1.2 Модели и алгоритмы 4

2. Надстройка «Подбор параметра»

2.1. Введение 6

2.2. Пример использования надстройки «Подбор параметра» 6

2.3. Подбор процентной ставки 7

2.4. Поиск корней нелинейного уравнения 9

2.5. Задача о портфеле ценных бумаг 11

3. Надстройка «Поиск решения»

3.1. Оптимизация нелинейной функции 13

3.1.1. Оптимизация нелинейной функции без учета ограничений 13

3.1.2. Оптимизация нелинейной функции с учетом ограничений 15

3.2. Решение задачи линейного программирования 17

3.3. Решение нелинейных уравнений 19

4. Лабораторные работы

4.1. Лабораторная работа №1 21

Решение нелинейного уравнения

4.2. Лабораторная работа №2 22

Оптимизация нелинейной функции

4.3. Лабораторная работа №3 22

Линейные модели

Литература

1. В. Долженков, Ю. Колесников. MicrosoftExcel2002. Наиболее полное руководство,BHV- Санкт-Петербург, 2002.

2. Ф. Новиков, А. Яценко. MicrosoftOfficeXPв целом,BHV- Санкт-Петербург, 2002.

3. Разработка бизнес-приложений в экономике на базе MSEXCEL. Учебник. Под редакцией к.т.н. А.Н. Афоничкина, М:. Диалог-МИФИ, 2003.

4. Б. Курицкий. Поиск оптимальных решений средствами Excel7.0,BHV- Санкт-Петербург, 1997.

5. Документация Microsoft по Excel 2003.