Численное решение уравнений средствами MS EXCEL

Цель: изучить технологию численного решения уравнений средствами MS EXCEL.

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

Пример 4.1. Нахождение корней полиномов при помощи табулирования и сервисной функции Подбор параметра.

Известно, что если функция, определенная в интервале [a,b], имеет значения F(а) и F(b) с разными знаками, то в интервале [a,b] есть, по крайней мере, один корень. Построить алгоритм нахождения корней уравнений с заданной точностью EPS следующим образом.

1. Определить начальный интервал [A, B], где находятся корни.

Для полиномов

модули всех корней x_k, k = 1…n расположены в круговом кольце

, (4.1)

где, ;

Таким образом, положительные корни лежат в интервале [A, B], а отрицательные корни - в интервале [-B, -A].

2. Табулируя полином в найденных начальных интервалах (например, с шагом (В-А)/10), составить таблицу {x, P(x)}.

3. Определить две соседние ячейки х, где функция меняет свой знак. Одно из значений (для которого значение функции ближе к нулю) принять за начальное приближение к корню полинома.

4. (меню Сервис). Относительная погрешность вычислений и предельное число итераций задаются на вкладке Вычисления диалогового окна Параметры (Сервис з Параметры з Вычисления) (рис. 4.1).

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

 

Рис. 4.1. Диалоговое окно Параметры

 

Рис. 4.2. Диалоговое окно Подбор параметра

 

Примечание. В этой ячейке должно содержаться числовое значение, а не формула, его вычисляющая. Для того, чтобы заменить в ячейке формулу на ее числовое значение, необходимо, находясь в этой ячейке, вызвать контекстно-зависимое меню и выбрать Копировать. Затем, находясь в той же ячейке, снова вызвать контекстно-зависимое меню и выбрать Специальная вставка (рис. 4.3).

В появившемся диалоговом окне отметить Значения. После этого ячейка готова к использованию в поле Изменяя значение ячейки диалогового окна Подбор параметра.

6. После подбора параметра (ОК) х получит значение корня. Процесс повторяется для всех найденных начальных приближений в диапазоне, определяемом формулой (4.1).

Рис. 4.3. Диалоговое окно Специальная вставка

Пример 4.2. Нахождение корней нелинейных уравнений с помощью метода итераций.

Пусть дано уравнение f(x)=0. Для нахождения его корней методом итераций уравнение представляют в виде x=F(x) и записывают итерационную схему

, (4.2)

с помощью которой строится итерационный процесс уточнения корней, начиная с начального значения x₀, которое выбирается самостоятельно. Достаточное условие сходимости процесса: в окрестности корня | F ^/(x)| <1.

Порядок действий в Excel может быть следующий.

1. Представить данное уравнение в виде x=F(x).

2. Создать таблицу с заголовками столбцов Номер шага, Очередное приближение к корню, Проверка на точность.

3. В первую ячейку первой строки таблицы занести значение 0, во вторую – начальное приближение.

4. В следующие строки занести, соответственно, номер очередного шага, итерационную формулу, вычисляющую правую часть итерационной схемы, и условную формулу, позволяющую помещать в ячейку текст «Стоп» или «Дальше» в зависимости от выполнения заданной точности решения (см. п. 5).

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

Если процесс расходится (получающиеся приближения удаляются друг от друга) или сходится очень медленно, то необходимо сменить вид представления x=F(x).

В этом может оказать помощь другой итерационный метод решения нелинейных уравнений – метод Ньютона. Его итерационная схема имеет вид

(4.3)

Сравнивая (4.2) и (4.3), замечаем, что в качестве функции F(x_k) можно взять правую часть из формулы (4.3). В большинстве случаев метод Ньютона сходится быстрее.

Пример 4.3. Нахождение корней нелинейных уравнений методом бисекции.

Если метод итераций сходится не всегда, то метод бисекции (или метод деления отрезка пополам, или метод дихотомии) – безусловно сходящийся метод нахождения корней нелинейного уравнения F(x)=0, лишь бы был известен отрезок, на котором расположен корень уравнения.

Пусть непрерывная функция F(x) меняет знак на концах отрезка [a,b], т.е. F(a)ЧF(b) < 0. Назовем такой отрезок отрезком локализации корня: на нем есть, по крайней мере, один корень. Найдем координату середины этого отрезка c=(a+b)/2 и рассмотрим два получившихся отрезка [a,c] и [c,b]. Если F(a)ЧF(с) < 0, то корень находится на отрезке [a,c], в противном случае – на отрезке [с,b]. Процесс деления все новых и новых отрезков локализации корня продолжаем до тех пор, пока длина отрезка не станет меньше заданной величины точности решения EPS.

Пример 4.4. Решение систем линейных алгебраических уравнений.

В Excel имеются специальные функции для работы с матрицами (Вставка функции пМатематические):

МОБР вычисление обратной матрицы А^-1;

МОПРЕД вычисление определителя матрицы D;

МУМНОЖ нахождение произведения двух матриц.

С их помощью можно решать системы линейных алгебраических уравнений вида

(4.4)

или в матричном виде

АЧХ=В,

где А = {a_ij}– матрица коэффициентов при неизвестных;

В = {b_ij} – вектор-столбец правых частей уравнений;

Х = {x_ij} – вектор-столбец неизвестных.

Способ 1 (метод обратной матрицы).

Решение имеет вид Х = А^–1ЧВ, где А^–1 – матрица, обратная по отношению к матрице А.

С помощью функции МОБР находится обратная матрица, а затем с помощью функции МУМНОЖ она перемножается с вектором-столбцом правых частей уравнений.

Примечание. При работе с матрицами перед вводом формулы необходимо выделить область на рабочем листе, куда будет выведен результат вычисления, а после задания исходных данных в поле функции выйти не как обычно, нажатием клавиши Enter или кнопки ОК, а нажатием клавиш Ctrl + Shift + Enter.

Способ 2 (правило Крамера). Если определитель D, составленный из коэффициентов при неизвестных, отличен от нуля, то решение имеет вид

x _j = D_j / D, j=1...n. (4.5)

Здесь D_j – дополнительный определитель, полученный из главного определителя системы D путем замены его j-го столбца вектором-столбцом В.

С помощью функции МОПРЕД находятся главный и дополнительные определители, и по формулам (4.5) вычисляются корни СЛАУ.

Способ 3 (метод исключений Гаусса). Этот метод основан на приведении матрицы системы к треугольному виду, что достигается последовательным исключением неизвестных из уравнений системы.

Предположим, что в (4.4) a₁₁ ¹ 0. Разделим первое уравнение системы на a₁₁ (этот коэффициент называется ведущим или главным элементом), получим:

.

Затем из каждого из остальных уравнений вычитается первое уравнение, умноженное на соответствующий коэффициент a_i1 (i=2,3,¼, n).

Эти n–1 уравнений принимают вид:

где

Далее аналогичную процедуру выполняют с этой системой, оставляя в покое первое уравнение. Только теперь делят на другой ведущий элемент a₂₂⁽¹⁾ ¹0.

В результате исключения неизвестных приходим к СЛАУ с верхней треугольной матрицей с единицами на главной диагонали:

(4.6)

Индексы над коэффициентами означают, сколько раз данное уравнение преобразовывалось.

Прямой ход метода Гаусса завершен.

Обратный ход метода Гаусса заключается в нахождении неизвестных x_n, x_n-1,..., x₁, причем в указанном порядке.

В этом списке x_n уже определено из последнего уравнения системы (4.6), а общая формула обратного хода имеет вид:

Проиллюстрируем этот алгоритм на примере решения системы из трех уравнений.

1.Располагаем на листе Excel матрицу коэффициентов и столбец правых частей (т.н. расширенная матрица 3´4), например, в ячейках А4:D6 (рис. 4.4).

2.Выделяем диапазон ячеек А8:D8 и вводим формулу:

{=A4:D4/A4}.

Фигурные скобки появляются автоматически при вводе формулы комбинацией клавиш Shift+Ctrl+Enter, как признак того, что идет работа не с отдельными ячейками, а с массивами.

3.Выделяем диапазон ячеек А9:D9, вводим формулу:

{=A5:D5-$A$8:$D$8*B5}

и копируем эту формулу в диапазон ячеек А10:D10. В ячейках А9 и А10 появились нули.

4.В ячейки А12:D12 копируем значения первой строки расширенной матрицы А8:D8, в ячейки А13:D13 – формулу {=A9:D9/B9}.

При этом второй элемент главной диагонали матрицы коэффициентов становится равным единице.

Рис.4.4 – Результаты решения СЛАУ

В ячейки А14:D14 вводим формулу: {=A10:D10–$A$13:$D$13*B10}.

5)В ячейки А16:D17 копируем значения первых двух строк расширенной матрицы (А12:D13), а в ячейки А18:D18 – формулу: {=A14:D14/C14}.

Прямой ход метода Гаусса завершен: получилась верхняя треугольная матрица с диагональными элементами, равными 1.

 

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

С помощью сервисной программы Поиск решения (Сервис | Поиск решения) в Excel можно решать системы нелинейных уравнений.

В общем случае система нелинейных уравнений имеет вид:

(4.7)

Составим новую функцию F(x₁, х₂,..., х_n), представляющую собой сумму квадратов правых частей уравнений:

(4.8)

Очевидно, переменные x₁, х₂,..., х_n, являющиеся решением системы (4.7), с необходимостью и достаточностью являются также решением уравнения:

(4.9)

Технология решения.

На листе Excel отводим ячейки для неизвестных данной системы уравнений, например с А1 по А5 (если пять переменных), и вводим туда начальные приближения. В ячейку В2 вводим формулу, вычисляющую функцию (4.8).

Открываем диалоговое окно Поиск решения (рис. 4.5). В поле Установить целевую ячейку вводим В2, в группе Равной устанавливаем переключатель в положение Значению и в поле ввода задаем 0. В поле Изменяя ячейки вводим диапазон ячеек А1:А5.

Рис.4.5. Диалоговое окно Поиск решения при решении нелинейного уравнения

 

После нажатия на кнопку Выполнить будет найдено решение, которое поместится в ячейки А1:А5. В ячейке В2 будет вычислено значение левой части уравнения (4.9) с относительной погрешностью, задаваемой в диалоговом окне Параметры поиска решения.

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

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

 

4.2. задания для практической работы

Задание 4.1. Найти корни полиномов при помощи табулирования и сервисной функции Подбор параметра.

Данные для решения взять из таблицы 4.1. Корни найти с точностью EPS=0,00001.

Таблица 4.1 Полиномы

Вариант	Уравнение	Вариант	Уравнение
	x^4+6x^3+11x^2–2x–28=0		x^4+3x^3+8x^2–5=0
	x^4+5x^3+9x^2+5x–1=0		x^4–6x^3+11x^2+2x–28=0
	x^4+3x^3+3x^2–2=0		x^4–5x^3+9x^2–5x–1=0
	x^4+x^3–7x^2+8x–6=0		x^4–3x^3+3x^2–2=0
	x^4–10x^3+16x+5=0		x^4–x^3–7x^2–8x–6=0
	x^4–3x^3–4x^2–x–3=0		x^4–10x^2–16x+5=0
	x^4+4x^3+4x^2+4x–1=0		x^4+3x^3+4x^2+x–3=0
	x^4+6x^3+13x^2+10x+1=0		x^4–4x^3–4x^2–4x–1=0
	x^4+x^3–4x^2+16x–8=0		x^4+2x^3+3x^2+2x–2=0
	x^4–x^3–4x^2–11x–3=0		x^4–6x^3+13x^2–10x+1=0
	x^4–6x^3–12x–8=0		x^4–3x^2+4x–3=0
	x^4+4x^3+4x^2–4=0		x^4–6x^2+12x–8=0
	x^4+x^3+2x+1=0		x^4–4x^3+4x^2–4=0
	x^4+2x^3+x^2+2x+1=0		x^4–x^3–2x+1=0
	x^4+3x^2–4x–1=0		x^4–2x^3+x^2–2x+1=0

 

Задание 4.2. Найти корни нелинейных уравнений с помощью метода итераций.

Данные для решения взять из таблицы 4.2. Точность решения EPS=0,0001.

После получения решения построить график, иллюстрирующий процесс сходимости: по оси абсцисс отложить номер шага, по оси ординат – очередное приближение к корню.

Задание 4.3. Найти корни нелинейных уравнений методом бисекции.

Таблица 4.2 Нелинейные уравнения

Вариант	Уравнение	Вариант	Уравнение
	ln(x)+(x+1)^3=0		x–sin(x)=0,25
	x2^x=1		tg(0,58x+0,1)=x^2
	x–cos(x)=0		3x–cos(x)–1=0
	3x+cos(x)+1=0
	x+ln(x)=0,5		x+lg(x)=0,5
	2–x=ln(x)		x^2+4sin(x)=0
			ctg(1,05x)–x^2=0
	(2–x)exp(x)=0,5		xlg(x)–1,2=0
	2,2x–2^x=0
	x^2+4sin(x)=0		2x–lg(x)–7=0
	2x–lg(x)=7
	5x–8ln(x)=8
	sin(x-0,5)–x+0,8=0		cos(x+0,3)=x^2
			x^2cos(2x)=–1

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

- вычисление значений левой и правой границы отрезков локализации;

- нахождение середины отрезка;

- вычисление произведения значений функций в левой и правой границах отрезка (для контроля правильности алгоритма);

- проверку на точность решения (аналогично предыдущему заданию).

Затем формулы копируются вниз по столбцам до тех пор, пока не будет найден корень с заданной степенью точности EPS=0,0001.

Данные для решения взять из таблицы 4.2, то есть решить одно и то же уравнение двумя способами.

Задание 4.4. Решить тремя способами систему линейных алгебраических уравнений, взяв данные для решения из таблицы 4.3.

Проверить найденное решение умножением матрицы коэффициентов на вектор-столбец решения.

 

Таблица 4.3 Системы линейных алгебраических уравнений

Вариант	СЛАУ	Вариант	СЛАУ
	2,7x1+3,3x2+1,3x3=2,1; 3,5x1–1,7x2+2,8x3=1,7; 4,1x1+5,8x2–1,7x3=0,8		0,34x1+0,71x2+0,63x3=2,08; 0,71x1–0,65x2–0,18x3=0,17; 1,17x1–2,35x2+0,75x3=1,28
	1,7x1+2,8x2+1,9x3=0,7; 2,1x1+3,4x2+1,8x3=1,1; 4,2x1–3,3x2+1,3x3=2,1		3,75x1–0,28x2+0,17x3=0,75; 2,11x1–0,11x2–0,12x3=1,11; 0,22x1–3,17x2+1,81x3=0,05
	3,1x1+2,8x2+1,9x3=0,2; 1,9x1+3,1x2+2,1x3=2,1; 7,5x1+3,8x2+4,8x3=5,6		0,21x1–0,18x2+0,75x3=0,11; 0,13x1+0,75x2–0,11x3=2,00; 3,01x1–0,33x2+0,11x3=0,13
	9,1x1+5,6x2+7,8x3=9,8; 3,8x1+5,1x2+2,8x3=6,7; 4,1x1+5,7x2+1,2x3=5,8		0,13x1–0,14x2–2,00x3=0,15; 0,75x1+0,18x2–0,77x3=0,11; 0,28x1–0,17x2+0,39x3=0,12
	3,3x1+2,1x2+2,8x3=0,8; 4,1x1+3,7x2+4,8x3=5,7; 2,7x1+1,8x2+1,1x3=3,3		3,01x1–0,14x2–0,15x3=1,00; 1,11x1+0,13x2–0,75x3=0,13; 0,17x1–2,11x2+0,71x3=0,17
	7,6x1+5,8x2+4,7x3=10,1; 3,8x1+4,1x2+2,7x3=9,7; 2,9x1+2,1x2+3,8x3=7,8		0,92x1–0,83x2+0,62x3=2,15; 0,24x1–0,54x2+0,43x3=0,62; 0,73x1–0,81x2–0,67x3=0,88
	3,2x1–2,5x2+3,7x3=6,5; 0,5x1+0,34x2+1,7x3=-0,2 1,6x1+2,3x2–1,5x3=4,3		1,24x1–0,87x2–3,17x3=0,46; 2,11x1–0,45x2+1,44x3=1,50; 0,48x1+1,25x2–0,63x3=0,35
	5,4x1–2,3x2+3,4x3=-3; 4,2x1+1,7x2–2,3x3=2,7; 3,4x1+2,4x2+7,4x3=1,9		0,64x1–0,83x2+4,2x3=2,23; 0,58x1–0,83x2+1,43x3=1,71; 0,86x1+0,77x2+0,88x3=–0,54
	3,6x1+1,8x2–4,7x3=3,8; 2,7x1–3,6x2+1,9x3=0,4; 1,5x1+4,5x2+3,3x3=-1,6		0,32x1–0,42x2+0,85x3=1,32; 0,63x1–1,43x2–0,58x3=-0,44; 0,84x1–2,23x2–0,52x3=0,64
	5,6x1+2,7x2–1,7x3=1,9; 3,4x1–3,6x2–6,7x3=-2,4; 0,8x1+1,3x2+3,7x3=1,2		0,73x1+1,24x2–0,38x3=0,58; 1,25x1+0,66x2–0,78x3=0,66; 0,75x1+1,22x2–0,83x3=0,92
	2,7x1+0,9x2–1,5x3=3,5; 4,5x1–2,8x2+6,7x3=2,6; 5,1x1+3,7x2–1,4x3=-0,14		0,62x1–0,44x2–0,86x3=0,68; 0,83x1+0,42x2–0,56x3=1,24; 0,58x1–0,37x2–0,62x3=0,87
	4,5x1–3,5x2+7,4x3=2,5; 3,1x1–0,6x2–2,3x3=-1,5; 0,8x1+7,4x2–0,5x3=6,4		1,26x1–2,34x2+1,17x3=3,14; 0,75x1+1,24x2–0,48x3=–1,17; 3,44x1–1,85x2+1,16x3=1,83
	3,8x1+6,7x2–1,2х3=5,2; 6,4x1+1,3x2–2,7x3=3,8; 2,4x1–4,5x2+3,5x3=–0,6		0,46x1+1,72x2+2,53x3=2,44; 1,53x1–2,32x2–1,83x3=2,83; 0,75x1+0,86x2+3,72x3=1,06
	5,4x1–6,2x2–0,5x3=0,52; 3,4x1+2,3x2+0,8x3=-0,8; 2,4x1–1,1x2+3,8x3=1,8		2,47x1+0,65x2–1,88x3=1,24; 1,34x1+1,17x2+2,54x3=2,35; 0,86x1–1,73x2–1,08x3=3,15
	7,8x1+5,3x2+4,8x3=1,8; 3,3x1+1,1x2+1,8x3=2,3; 4,5x1+3,3x2+2,8x3=3,4		4,24x1+2,73x2–1,55x3=1,87; 2,34x1+1,27x2+3,15x3=2,16; 3,05x1–1,05x2–0,63x3=–1,25
	3,8x1+4,1x2–2,3x3=4,8; 2,1x1+3,9x2–5,8x3 = 3,3; 1,8x1+1,1x2–2,1x3=5,8		0,43x1+1,24x2–0,58x3=2,71; 0,74x1+0,83x2+1,17x3=1,26; 1,43x1 - 1,58x2+0,83x3=1,03
	1,7x1–2,2x2+3,0x3=1,8; 2,1x1+1,9x2–2,3x3=2,8; 4,2x1+3,9x2–3,1x3=5,1		0,43x1+0,63x2+1,44x3=2,18; 1,64x1–0,83x2–2,45x3=1,84; 0,58x1+1,55x2+3,18x3=0,74
	2,8x1+3,8x2–3,2x3=4,5; 2,5x1–2,8x2+3,3x3=7,1; 6,5x1–7,1x2+4,8x3=6,3		1,24x1+0,62x2–0,95x3=1,43; 2,15x1–1,18x2+0,57x3=2,43; 1,72x1–0,83x2+1,57x3=3,88
	3,3x1+3,7x2+4,2x3=5,8; 2,7x1+2,3x2–2,9x3=6,1; 4,1x1+4,8x2–5,0x3=7,0		0,62x1+0,56х2–0,43x3=1,16; 1,32x1–0,88x2+1,76x3=2,07; 0,73x1+1,42x2–0,34x3=2,18
	7,1x1+6,8x2+6,1x3=7,0; 5,0x1+4,8x2+5,3x3=6,1; 8,2x1+7,8x2+7,1x3=5,8		1,06x1+0,34x2+1,26x3=1,17; 2,54x1–1,16x2+0,55x3=2,23; 1,34x1–0,47x2–0,83x3=3,26

Задание 5. Решить систему нелинейных уравнений, взяв данные из таблицы 1.4. Проверить найденное решение.

Таблица 1.4. Системы нелинейных уравнений

Вариант	Система нелинейных уравнений	Вариант	Система нелинейных уравнений

4.3.Контрольные вопросы

1. Опишите технологию нахождения корней полиномов при помощи табулирования и сервисной функции Подбор параметра.

2. С помощью какой сервисной команды осуществляется уточнение корня при использовании метода последовательных приближений?

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

4. Укажите итерационную схему метода Ньютона.

5. Укажите специальные функции Excel для работы с матрицами.

6. Опишите технологию решения систем линейных алгебраических уравнений.

7. С помощью, какой сервисной программы Excel осуществляется решение систем нелинейных уравнений?

8. В чем заключается прямой и обратный ход методом Гаусса?

 

Практическая работа №5.