Как решить нелинейное уравнение в excel

решение уравнений в excel

Цель работы: Изучение возможностей пакета Ms Excel 2007 при решении нелинейных уравнений и систем. Приобретение навыков решения нелинейных уравнений и систем средствами пакета.

Задание1. Найти корни полинома x 3 — 0,01x 2 — 0,7044x + 0,139104 = 0.

Для начала решим уравнение графически. Известно, что графическим решением уравнения f(x)=0 является точка пересечения графика функции f(x) с осью абсцисс, т.е. такое значение x, при котором функция обращается в ноль.

Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на ри., где в ячейку В2 была введена формула: = A2^3 — 0,01*A2^2 — 0,7044*A2 + 0,139104. На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеется не более трех вещественных корней, то графическое решение поставленной задачи найдено. Иначе говоря, была проведена локализация корней, т.е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], [0.2,0.4] и [0.6,0.8].

Теперь можно найти корни полинома методом последовательных приближений с помощью команды Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра.

После ввода начальных приближений и значений функции можно обратиться к команде Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра и заполнить диалоговое окно следующим образом.

В поле Установить в ячейке дается ссылка на ячейку, в которую введена формула, вычисляющая значение левой части уравнения (уравнение должно быть записано так, чтобы его правая часть не содержала переменную). В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную. Заметим, что вводить ссылки на ячейки в поля диалогового окна Подбор параметров удобнее не с клавиатуры, а щелчком на соответствующей ячейке.

После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения, приближенное значение корня будет помещено в ячейку А14.

Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.

Задание 2. Решить уравнение e x — (2x — 1) 2 = 0.

Проведем локализацию корней нелинейного уравнения.

Для этого представим его в виде f(x) = g(x) , т.е. e x = (2x — 1) 2 или f(x) = e x , g(x) = (2x — 1) 2 , и решим графически.

Графическим решением уравнения f(x) = g(x) будет точка пересечения линий f(x) и g(x).

Построим графики f(x) и g(x). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции f(x): = EXP(A3), а в С3 для вычисления g(x): = (2*A3-1)^2.

Результаты вычислений и построение графиков f(x) и g(x):

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

Для второго можно определить интервал изоляции корня: 1,5 < x < 2.

Теперь можно найти корень уравнения на отрезке [1.5,2] методом последовательных приближений.

Введём начальное приближение в ячейку Н17 = 1,5, и само уравнение, со ссылкой на начальное приближение, в ячейку I17 = EXP(H17) — (2*H17-1)^2.

Далее воспользуемся командой Данные→Работа с данными→Анализ «Что-Если» →Подбор параметра.

и заполним диалоговое окно Подбор параметра.

Результат поиска решения будет выведен в ячейку Н17.

Задание 3. Решить систему уравнений:

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

Для первого уравнения системы имеем:

Выясним ОДЗ полученной функции:

Второе уравнение данной системы описывает окружность.

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

Не трудно заметить, что заданная система имеет два решения. Поэтому процедуру поиска решений системы необходимо выполнить дважды, предварительно определив интервал изоляции корней по осям Оx и Oy . В нашем случае первый корень лежит в интервалах (-0.5;0)_x и (0.5;1)_y, а второй — (0;0.5)_x и (-0.5;-1)_y. Далее поступим следующим образом. Введем начальные значения переменных x и y, формулы отображающие уравнения системы и функцию цели.

Теперь дважды воспользуемся командой Данные→Анализ→Поиск решений, заполняя появляющиеся диалоговые окна.

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

Задания для самостоятельного решения

Задание 1. Найти корни полинома

1. 
2. 
3. 
4. 
5. 
6. 
7. 
8. 

Задание 2. Найдите решение нелинейного уравнения.

1. 
2. 
3. 
4. 
5. 
6. 
7. 
8. 

Задание 3. Найдите решение системы нелинейных уравнений.

1. 
2. 
3. 
4. 

7.4. Решение нелинейных уравнений в Excel

Нелинейные уравнения – это уравнения вида f(x)=0, где f(x) – нелинейная функция. Решение уравнения f(x)=0 сводится к поиску таких значений х * (корней уравнения), которые превращают уравнение в тождество. Различают нелинейные алгебраические уравнения и трансцендентные.

Например, нелинейное алгебраическое уравнение ax 2 + вx +с =0 имеет два корня, которые могут быть действительными или мнимыми. Например, уравнение х 2 + 2=0 имеет два мнимых корня х₁= -2 и х₂= --2 .

В дальнейшем будет идти речь о вычислении только действительных корней.

Трансцендентным называется уравнение, если в f(x) входит хотя бы одна трансцендентная функция. Например, sin(x) –1=0;

Решение нелинейных уравнений выполняют в два этапа:

1. Этап отделения корней.
2. Этап уточнения корней , т.е. поиск коней с заданной точностью.

Этап отделения корней Для этого построим график заданной функции f(x)=0. В столбце А располагаем изменение аргумента, а в столбце В табулируемую функцию. Строим график. На графике выделяем границы корня и в этих границах берем начальное приближение корня (нарисовать график, выделить корни и взять начальное приближение). Этап уточнение корня Команда Подбор параметров Порядок уточнения: 1. В ячейку A1 вводим начальное приближение корня Х₁. 2. В ячейку В1 вводим формулу с заданной функцией. 3. Выполняем команды Сервис,Подбор параметра. Появляется окно Подбор параметра (рис. 7.7). 4. В поле «Установить в ячейке» записать адрес первой формулы (можно снять окно и щелкнуть ячейку В1, затем восстановить окно). 5. В поле «Значение» установить 0. 6. В поле «Изменяя значениеячейки« установить адрес А1 (снять окно и щелкнуть А1). 7. Щелкнуть ОК. Появляется окно Результат подбора параметра (рис. 7.8), а в ячейке А1 будет уточненное значение корня. Рис. 7.8 Рис. 7.7

7.5. Вычисления по итерационным формулам

Итерационной называется формула типа y_i+1 = f (y_i) . Пример1. Вычисление задано итерационной формулой y_i+1=(x/y_i 2 +2y_i)/3 Начальное приближении у₀=1 и значение х= 27. Составим ЭТ для вычисления: 1. В ячейку a2 запишем значение х равное 27 (рис. 7.9). 2. В ячейку b2 запишем значение у₀ равное 1. 3 Рис. 7.9 . В ячейку b3 запишем формулу = ($A$1/B1^2+2*B1)/3, которую копируем вниз Пример 2. Заданы итерационные формулы x _i =2x_i-1 и y_i= x_i-1 + 3y_i-1 при изменении i=2,3,4,5. При i=2 х₂ = 2х₁ и y₂= x₁ + 3y₁ Начальные значения x₁=1 ; y₁=1 (рис. 7.10) запишем в В2 и С2 соответственно. В ячейки В3 и С3 запишем формулы для х₂ и у₂ . Выделяем В3:С3 и копируем вниз до С6. Результат вычисления на рис. 7.11. Рис. 7.10 Рис. 7.11 Пример 3. Решение задач следующего типа: Даны действительные числа у1, у2,…у5, которые записаны в В2:В6 Составить ЭТ для вычисления и определения min(z1 2 , z2 2 , …,z5 2 ) при i=1,2,…,5 Рис. 7.12

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

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

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

\

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

Введем в рассмотрение функцию Z(x), равную (F(x)) 2 . Функция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

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

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

для значений =[-2;2]

для =[-3;3]

-0,32; 1,229997; 2,010001

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

Кол-во акций 1го типа

Стоимость акций 1го типа

Доходность акций 1го типа, %

Кол-во акций 2го типа

Стоимость акций 2го типа

Доходность акций 2го типа, %

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

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

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

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

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

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

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

• телевизоры;
• стереосистемы;
• акустические системы.

Каждому виду изделий соответствует своя норма прибыли. На складе запас комплектующих, необходимых для производства продукции, ограничен. Определить какое количество каждого вида изделий надо произвести для того, чтобы прибыль была максимальна. В таблице на стр.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. В. Долженков, Ю. Колесников. MicrosoftExcel2002. Наиболее полное руководство,BHV- Санкт-Петербург, 2002.

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

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

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

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

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

Открывается окно Параметры поиска решения. В поле оптимизировать целевую функцию выбираем ячейку B4, ставим Значения 0, ячейку переменной указываем A4, ставим галочку сделать переменные без ограничений неотрицательными, выбираем метод решения — поиск решения нелинейных задач методом ОПГ (обобщенного приведенного градиента) и жмем Найти решение

Получаем решение искомой задачи

x=1,06744215530327

Отчет результатов вычисления в Excel

7654