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

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

1.1 Отделение корней

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

Например , для уравнения выполним преобразование и приведем его к виду f ( x )= 0 т.е. . График этой функции представлен на рисунке 1. Очевидно, что данное уравнение имеет два действительных корня – один на отрезке [-1, 0] , а второй – [1, 2].

Рисунок 1. График функции

1.2 Решение уравнений, используя инструмент “Подбор параметра”

Используя возможности Excel , можно находить корни нелинейного уравнения вида f ( x )=0 в допустимой области определения переменной. Последовательность операций нахождения корней следующая:

1. Производится вычисление значений функции в диапазоне вероятного существования корней от значений аргумента, изменяющегося с определенным шагом;

2. В таблице выделяются ближайшие приближения к значениям корней (пары соседних значений функции с разными знаками);

3. Используя средство Excel Подбор параметра, вычисляются корни уравнения.

2. Работа с матрицами в MS Excel . Решение систем уравнений.

Нахождение определителя матрицы

Перед нахождением определителя необходимо ввести матрицу в диапазон ячеек Excel в виде таблицы.

Для нахождения определителя матрицы в Excel необходимо:

· сделать активной ячейку, в которой в последующем будет записан результат;

· в меню Вставка – Функция в категории Математические выбрать функцию МОПРЕД и нажать OK ;

· на втором шаге задать диапазон ячеек, в котором содержатся элементы матрицы, и нажать OK .

Нахождение обратной матрицы

Для нахождения обратной матрицы необходимо

· выделить диапазон ячеек, в которых в последующем будут записаны элементы матрицы ( количество строк и количество столбцов должны равняться соответствующим параметрам исходной матрицы).

· в меню Вставка – Функция в категории Математические выбрать функцию МОБР и нажать OK ;

· на втором шаге задать диапазон ячеек, в котором содержатся элементы исходной матрицы, и нажать OK .

· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F 2 и комбинацию клавиш Ctrl + Shift + Enter .

Для перемножения матриц необходимо

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

· в меню Вставка – Функция в категории Математические выбрать функцию МУМНОЖ и нажать OK ;

· на втором шаге задать два диапазона ячеек с элементами перемножаемых матриц, и нажать OK .

· после появления значения в левом верхнем углу выделенного диапазона последовательно нажать клавишу F 2 и комбинацию клавиш Ctrl + Shift + Enter .

Решение системы уравнений в Excel .

Решение системы уравнений при помощи нахождения обратной матрицы.

Пусть дана линейная система уравнений.

Данную систему уравнений можно представить в матричной форме:

Матрица неизвестных вычисляется по формуле

где A -1 – обратная матрица по отношению к A .

Для вычисления уравнения в Excel необходимо:

· ввести матрицу A;

· ввести матрицу B;

· вычислить обратную матрицу по отношению к А ;

· перемножить полученную обратную матрицу с матрицей B .

Порядок выполнения работы

Задание 1

Найти все корни уравнения 2x 3 -15sin( x )+0,5x-5=0 на отрезке [-3 ; 3].

1. Построить таблицу значений функции f ( x ) для значений x от –3 до 3, шаг 0,2.

Для этого ввести первые два значения переменной x , выделить эти две ячейки, с помощью маркера автозаполнения размножить значения до 3.

Затем ввести формулу для вычисления f ( x ). Скопировать формулу с использованием маркера автозаполнения на весь столбец.

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

2. Выделить цветом пары значений x и f ( x ), где f ( x ) меняет знак (см .р исунок 2).

3. Построить график функции f ( x ).

Рисунок 2. Поиск приближенных значений корней уравнения

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

5. Выполнить команду меню Сервис/Подбор параметра. В диалоговом окне (рисунок 3) заполнить следующие поля:

þ Установить в ячейке : в поле указывается адрес ячейки, в которой записана формула правой части функции;

þ Значение : в поле указывается значение, которое должен получить полином в результате вычислений, т.е. правая часть уравнения (в нашем случае 0);

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

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

6. После щелчка на ОК должно получиться значение первого корня -1,65793685 .

7. Выполнить последовательно операции, аналогичные предыдущим, для вычисления значений остальных корней: -0,35913476 и 2,05170101 .

Задание 2

Решить систему уравнений:

1. Ввести значения элементов матриц A и B уравнения в ячейки Excel .

2. Вычислить обратную матрицу с помощью матричной функции МОБР.

3. Перемножить обратную матрицу A -1 на матрицу B с помощью матричной функции МУМНОЖ (Порядок умножения важен ­– первой должна идти матрица A -1 а второй B .)

4. Проверить правильность полученной матрицы корней X .

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

1. Порядок действий для решения нелинейного уравнения с помощью инструмента Подбор параметра MS Excel .

2. Порядок действий для решения системы уравнений матричным методом в MS Excel .

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

Разберём решение нелинейного уравнения в Excel вида:

y=4x 3 +2x–7

Ячейку A4 оставим пустой, а в ячейки B4 запишем формулу вида

Затем в Excel перейдём на вкладку Данные -> Поиск Решения

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

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

x=1,06744215530327

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

Решение нелинейных уравнений с помощью средства MS Excel Подбор параметра

Постановка задачи. Дано уравнение: x 3 –0,01x 2 –0,7044x+0,139104 = 0. Необходимо решить его с помощью средства MS Excel Подбор параметра с точностью 0,001 [6].

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

Проведем табулирование нашего полинома на интервале от -1 до 1 с шагом 0,2. Результаты вычислений приведены на рис.1, где в ячейку В2 была введена формула: = A2^3-0,01*A2^2-0,7044*A2+0,139104.

На графике видно, что функция три раза пересекает ось Оx, а так как полином третьей степени имеет не более трех вещественных корней, то графическое решение поставленной задачи найдено: была проведена локализация корней, т. е. определены интервалы, на которых находятся корни данного полинома: [-1,-0.8], [0.2,0.4] и [0.6,0.8].

Теперь можно найти корни полинома методом последовательных приближений с помощью команды: Сервис → Подбор параметра. Относительная погрешность вычислений и предельное число итераций (например, 0,00001 и 1000) задаются на вкладке Сервис → Параметры.

Рис.1. Результаты вычислений

После ввода начальных приближений и значений функции можно обратиться к пункту меню Сервис → Подбор параметра и заполнить диалоговое окно следующим образом (рис.2.).

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

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

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

Рис. 3. Диалоговое окно «Результат подбора параметра»

Рис.4. Результаты вычислений

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

Постановка задачи. Дано уравнение: e x – (2x – 1) 2 = 0.

Необходимо решить его с помощью средства MS Excel Подбор параметра – с точностью 0,001.

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

Для этого представим его в виде 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). Для этого в диапазон А3:А18 введем значения аргумента. В ячейку В3 введем формулу для вычисления значений функции:

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

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

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

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