2015-07-04
238
Цель: изучить основные возможности приложения Microsoft Excel для решения систем нелинейных уравнений.
Пример
Найти решение системы нелинейных уравнений:
(1)
с относительной погрешностью 
.
Методические рекомендации
1. Запустите приложение Microsoft Excel 2010: Пуск → Все программы → Microsoft Office → Microsoft Excel 2010. В окне Excel откроется новая рабочая книга с тремя листами.
2. Сохраните рабочую книгу в своей рабочей папке на диске или на личном внешнем носителе: вкладка Файл – команда Сохранить как. Дайте имя файлу Л.р.№7-пример.
3. Замените имя текущего рабочего листа. Для этого дважды щелкните левой кнопкой мыши по ярлычку рабочего листа с надписью Лист1 и наберите имя листа Графическое решение.
4. Преобразуйте систему уравнений (1) к виду
(2)
5. Постройте графики функций 
и 
, как показано на рисунке 7.1. На этом рисунке видно, что графики функций пересекаются в двух точках, следовательно, система уравнений (1) имеет два решения.
Рис.7.1. Результаты табулирования и построения графиков функций 
и
6. Пользуясь графиком, определите приблизительные значения координат xi, для точек в которых функции пересекаются: 
– это и есть начальное приближение корней xi системы уравнений.
7. Уточните значение корней с заданной относительной погрешностью . Для этого откройте новый лист и назовите его Корни системы уравнений. Для уточнения корней используются итерации и надстройка Поиск решения.
8. На вкладке Файл выберите команду Параметры, а затем категорию Формулы.
9. Установите флажок Включить итеративные вычисления в положение Включено. Значение параметра Предельное число итераций:100 оставьте без изменений, а в поле Относительная погрешность занесите значение 1e-3.
10. Подключите надстройку Поиск решения. Для этого на вкладке Файл в команде Параметры перейдите в категорию Надстройки, в поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти.
11. В окне Надстройки найдите поле Доступные надстройки, установите флажок рядом с пунктом Поиск решения в положение Включено и нажмите кнопку ОК.
12. Как показано на рисунке 7.2, занесите в ячейку A2 приближенное значение первого корня: -2. В ячейку B2 занесите формулу =A2^2-1, а в ячейку B3 – формулу =2-A2. Для организации процесса вычислений в ячейку C2 введите формулу целевой функции =СРОТКЛ(B2:B3), которая вычисляет среднее отклонение значений функций друг от друга. Очевидно, если эти функции пересекаются (т.е. имеются решения), значение в ячейке C2 должно быть равно нулю.
Рис.7.2. Вид листа Корни системы уравнений
13. Перейдите на вкладку Данные и в группе Анализ укажите Поиск решения.
14. В панели Параметры поиска решения установите значения параметров, как показано на рисунке 7.3.
Для этого:
1. В поле Оптимизировать целевую функцию: укажите адрес ячейки, в которую занесена целевая функция C2.
2. Установите переключатель До: в положение З начения:, а в поле ввода занесите значение целевой функции равное 0. 
Рис. 7.3. Панель установки необходимых параметров поиска решения
3. В поле Изменяя ячейки переменных: укажите ячейку, в которой будет находится искомое решение A2.
4. Флажок Сделать переменные без ограничений неотрицательными установите в положение Выключено.
5. В раскрывающемся списке Выберите метод решения: укажите Поиск решения нелинейных задач методом ОПГ.
19. После установки всех параметров, необходимых для решения уравнения, нажмите кнопку Найти решение.
20. В панели Результаты поиска решения изучить полученные результаты и нажать кнопку OK, чтобы сохранить найденное решение, которое должно выглядеть так, как показано на рисунке 7.4.
Рис. 7.4. Конечный вид таблицы вычисления корней x1, y1 системы (1)
21. Получите второе решение системы(1), для чего повторите расчет, задавая начальное значение корня x2= 1,5. Вид панели интерфейса Microsoft Excel для этого варианта показан на рисунке 7.5.
Рис. 7.5. Конечный вид таблицы вычисления корней x2, y2 системы (1)
Необходимо иметь в виду, что результат вычислений существенно зависит от заданного начального приближения.
Таким образом, найдены два решения системы (1) с относительной погрешностью :
и
Вопросы для самоконтроля
1. Как определить приближенные значения корней системы нелинейных уравнений?
2. Почему при решении системы нелинейных уравнений для различных значений начальных приближений могут получаться разные результаты?
3. Сколько ячеек используется при уточнении корня системы нелинейных уравнений, и какую информацию необходимо в них задавать?
4. Какие значения необходимо устанавливать в диалоговом окне Параметры поиска решения?
