Тема. Решение нелинейных уравнений и систем в электронных таблиц MS Excel.

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

Задание. Найти корни полинома (табл. 9), решить уравнение (табл. 10) и систему уравнений (табл. 11).

Таблица 12. Варианты заданий

№	уравнение	№	уравнение

Таблица 13. Варианты заданий

№	уравнение	№	уравнение

Таблица 14. Варианты заданий

№	Система уравнений	№	Система уравнений
		20-

Рекомендации к выполнению лабораторной работы. Рассмотрим несколько примеров.

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

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

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

Теперь можно найти корни полинома методом последовательных приближений с помощью команды Сервис®Подбор параметра [38]. В качестве начальных значений приближений к корням можно взять любые точки из отрезков локализации корней. Пусть это будут -0.9, 0.3 и 0.7. Введем эти значения в диапазон А14:А16, и вычислим для них значения функции по формуле

В14 =A14^3-0,01*A14^2-0,7044*A14+0,139104,

которую скопируем в ячейки В15 и В16 при помощи маркера заполнения.

Рис. 18. Решение уравнения

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

В поле Установить в ячейке дается ссылка на ячейку в которую введена формула, вычисляющая значение левой части уравнения[39]. В поле Значение вводим правую часть уравнения, а в поле Изменяя значения ячейки дается ссылка на ячейку, отведенную под переменную[40]. После нажатия кнопки ОК появится диалоговое окно Результат подбора параметра с сообщением об успешном завершении поиска решения и приближенное значение корня будет помещено в ячейку А14. Два оставшихся корня находим аналогично. Результаты вычислений будут помещены в ячейки А15 и А16.

Рис. 19. Подбор параметра

Пример 2. Решить уравнение .

Проведем локализацию корней нелинейного уравнения. Для этого представим его в виде , т.е. или , и решим графически. Графическим решением уравнения будет точка пересечения линий и .

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

Рис. 20. Решение уравнения

На графике видно, что линии и пересекаются дважды, т.е. данное уравнение имеет два решения. Одно из них тривиальное и равно нулю. Для второго можно определить интервал изоляции корня: и уточнить его методом последовательных приближений. Введём начальное приближение в ячейку Н17=1.5, и зададим само уравнение:

I17 =EXP(H17)-(2*H17-1)^2.

Далее воспользуемся пунктом меню Сервис®Подбор параметра и заполним диалоговое окно Подбор параметра. В поле Установить в ячейке вводим адрес функции I17. В поле Значение вводим правую часть уравнения, т.е. ноль. Поле Изменяя значения ячейки заполняется адресом переменной Н17. Результатпоиска решения будет выведен в ячейку Н17.

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

.

В MS Excel есть очень удобная операция – Поиск решения (рис. 21). Вообще говоря, она предназначена для решения задач оптимизации. Применим ее для решения системы уравнений, сведя задачу к задаче отыскания минимума функции.

Пусть в ячейках D1 и D2 [41] хранятся начальные значения переменных x₁ и x₂. В ячейки E1 и E2 введем уравнения системы: