В настоящее время имеется большое количество компьютерных программ для использования различных итерационных методов. Наиболее простые и удобные программы оформлены в виде функций MS Excel Подбор параметра и Поиск решения.
Перед тем как рассмотреть основные средства MS Excel полезно изучить возможности программы для решения менее сложных задач, например, задачи исследования функций.
Многие инженерные задачи сводятся к исследованию функций одной или нескольких переменных вида Y = f(X) или Y = f(x1,x2,…xN).
Исследовать функцию, значит установить область ее существования (значения Х при которых возможно вычислить Y), определить области значений Х, при которых Y принимает положительные, отрицательные и аномально большие значения ("уходит в бесконечность"), найти максимумы, минимумы, иногда точки перегиба графика функции, а также корни уравнения Y = f(x) – значения х, при которых Y обращается в 0 (график функции пересекает ось абсцисс).
Наиболее простые методы исследования функциональных зависимостей с помощью компьютера - итерационные, основанные на многократном выполнении сравнительно простых операций.
Один из итерационных методов - табулированиефункции, включающий следующие этапы:
- расчет значений Y при заданных X в большом диапазоне значений Х с большим шагом;
- табулирование с небольшим шагом в наиболее близких диапазонах – вблизи корней, максимумов и минимумов;
- сужение диапазонов Х и уменьшение шага для получения все более точных значений экстремумов и корней. Получаемые решения зависят от того, в каких диапазонах Х и Y ведется их поиск, т.е. от их начальных значений.
Пример 1. Решение уравнений
Пример показывает использование функции Подбор параметра для решения уравнений.
Требуется: решить уравнение Y = 0,1∙х2 - х -11.
Решение.
1. С помощью график функции определить количество действительных корней уравнения. Для этого следует:
1.1. Задать область определения (х) от –20 до +20: занести в соседние ячейки (например, А5 и А6) –20 и –19, выделить обе ячейки, поставить курсор на черный квадратик в правом нижнем углу, нажать левую клавишу мыши и потащить вниз до появления числа 20.
1.2. Создать область значений: в ячейку рядом с –20 вставить формулу =0,1*А5^2-А5-11, скопировать ее вниз.
1.3. Выделить область значений и вызвать в меню Вставка команду Диаграмма… (Excel 2003) или на вкладке Вставка в группе Диаграммы выбрать кнопку График(Excel 2007). Обратите внимание, что на оси Х указываются не значения аргумента, а порядковые номера.
2. Найти первый корень уравнения:
2.1. Сделать активной ячейку в диапазоне Y вблизи одного из корней (первое пересечение графика функции с осью абсцисс).
2.2. Вызвать Подбор параметра:
2.2.1. Excel 2003: в менюСервис, выбрать команду Подбор параметра;
2.2.2. Excel 2007: на вкладке Данные, в группе Работа с данными выбрать кнопку Анализ «что-если» и указать команду Подбор параметра.
2.3. В окне Значение установить 0, в нижнем окне Изменяя значение ячейки указать адрес ячейки Х, соответствующей активной ячейки Y, после чего кликнуть по кнопке ОК.
3. Найти второй корень, выбрав значения Y и X вблизи него.
Пример 2. Решение системы уравнений
Расчет установившихся значений вероятностей состояний системы (финальных вероятностей), описанной марковским процессом, сводится к решению системы алгебраических уравнений. Для изучения принципа решения подобных систем рассмотрим решение системы из трех уравнений с тремя неизвестными вида aiX + biY + ciZ = di (i = 1,2,3) с помощью команды Поиск решения.
Поиск решения – это надстройка, входящая в поставку Excel и предназначенная для решения задач линейной и нелинейной оптимизации. Для этого в ней используются методы и алгоритмы математического программирования, которые позволяют находить оптимальные решения задач оптимизации, представленные в виде табличных моделей. Пример подобной задачи рассматривается в следующей работе. В данном примере с помощью этой надстройки решается система уравнений.
Для решения системы уравнений следует:
1. Выполнить подготовительные действия (см. табл. 2.1):
1.1 Записать строкой начальные значения неизвестных X, Y, Z, например, нулевые значения в ячейки A10, B10, C10 соответственно.
1.2 Составить таблицу, содержащую значения коэффициентов при этих неизвестных ai, bi, ci (i = 1,2,3), и значения соответствующих свободных членов di, например, в ячейках A12 : D14.
1.3 Перемножить начальные значения X, Y, Z на соответствующие коэффициенты (ячейки A16:C18) и просуммировать произведения по строкам (ячейки D16:D18).
2. Запустить Поиск решения:
2.1 Excel 2003: в менюСервис, выбрать команду Поиск решения;
2.2 Excel 2007: на вкладке Данные, в группе Анализ выбрать команду Поиск решения.
3. Заполнить окно диалога Поиск решения данными решаемой задачи (см. рис. 2.1):
3.1 В качестве целевой ячейки установить первую сумму: задать “Установить целевую ячейку (в примере это ячейка D16) равной значению” первого свободного члена d1.
3.2 На две другие суммы наложить ограничения: равенство двум другим свободным членам d2 и d3 (используя кнопку “Добавить”).
3.3 В окне “Изменяя ячейки” указать ячейки с начальными значениями неизвестных X, Y, Z (в этих ячейках окажется результат решения системы).
3.4 Нажать кнопку “Параметры” и ознакомиться с параметрами и методами, используемыми при оптимизационных расчетах (при необходимости можно параметры изменить), чтобы закрыть окно “Параметры”, следует нажать кнопку ОК.
4. Запустить выполнение программы нажатием кнопки “Выполнить”. В результате появится сообщение о нахождении или не нахождении решения.
Таблица 2.1
A
B
C
D
Комментарии
X
Y
Z
Начальные значения (числа), после решения задачи на их месте появятся результирующие значения
a1
b1
c1
d1
Коэффициенты (числа) в уравнениях и значения свободных членов
