Файл: Е.Е. Дадонова Решение уравнений средствами Excel.pdf

12

параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сер-

вис/Поиск решения.

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

Найти:

х1 ,х2, … , хn

такие, что:

F(х1 ,х2, … , хn) → {Max; Min; = Value}

при ограничениях:

G(х1 ,х2, … , хn) → {≤ Value; ≥ Value; = Value}

Искомые переменные – ячейки рабочего листа Excel – называются регулируемыми ячейками. Целевая функция F(х1 ,х2, … , хn), называемая иногда просто целью, должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определенные пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

•найти максимум целевой функции F(х1 ,х2, … , хn);

•найти минимум целевой функции F(х1 ,х2, … , хn);

•добиться того, чтобы целевая функция F(х1 ,х2, … , хn) имела фиксированное значение: F(х1 ,х2, … , хn) = а.

Функции G(х1 ,х2, … , хn) называются ограничениями. Их можно задать

как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить дополнительные ограничения: неотрицательности и/или целочисленности, тогда искомое решение ищется в области положительных и/или целых чисел.

Под эту постановку попадает самый широкий круг задач оптимизации, в том числе решение различных уравнений и систем уравнений, задачи линейного и нелинейного программирования. Такие задачи обычно проще сформулировать, чем решать. И тогда для решения конкретной оптимизаци-

13

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

Выше для нахождения корней квадратного уравнения был применен метод Ньютона (п. 1.4) с использованием циклических ссылок (п. 2.1) и средство Подбор параметра (п. 2.2). Рассмотрим, как воспользоваться Поиском решения на примере того же квадратного уравнения.

После открытия диалога Поиск решения (рис. 12)

необходимо выполнить следующие действия:

1) в поле Уста-

новить целевую ячейку

ввести адрес ячейки, содержащей формулу для вычис-

Рис. 12. Окно диалога Поиск решения ления значений оптимизируемой функции, в нашем примере целевая ячейка – это С4, а

формула в ней имеет вид: = C3^2 - 5*C3 + 6;

2)для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение , для минимизации используется переключатель минимальному значению, в нашем случае устанавливаем переключатель в положение значению и вводим значение 0;

3)в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (С3), разделяя их знаком «;» (или щелкая мышью при нажатой клавише Сtrl на соответствующих ячейках),

14

для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;

4)в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: для нашего примера ограничений задавать не нужно;

5)для запуска процесса поиска решения нажать кнопку Выполнить. Для сохранения полученного

лист примет вид, представленный на рис. 13. Полученное решение зависит от выбора начального приближения, которое задается в ячейке С4 (аргумент функции). Если в качестве начального приближения в ячейку С4 ввести значение, равное 1,0, то с помощью Поиска решения найдем второй корень, равный 2,0.

Опции, управляющие работой Поиска решения, задаваемые в окне Параметры (окно появляется, если нажать на кнопку Параметры окна Поиск

15

сти, то время необходимо увеличить).

•Предельное число итераций – еще один способ ограничения времени поиска путем задания максимального числа итераций. По умолчанию задано 100, и, чаще всего, если решение не получено за 100 итераций, то при увеличении их количества (в поле можно ввести время, не превышающее 32767 секунд) вероятность получить результат мала. Лучше попытаться изменить начальное приближение и запустить процесс поиска заново.

•Относительная погрешность – задает точность, с которой определяется соответствие ячейки целевому значению или приближение к указанным ограничениям (десятичная дробь от 0 до 1).

•Допустимое отклонение – задается в % только для задач с целочисленными ограничениями. Поиск решения в таких задачах сначала находит оптимальное нецелочисленное решение, а потом пытается найти ближайшую целочисленную точку, решение в которой отличалось бы от оптимального не более, чем на указанное данным параметром количество процентов.

•Сходимость – когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа (дробь из интервала от 0 до 1), указанного в данном параметре, поиск прекращается.

•Линейная модель – этот флажок следует включать, когда целевая функция и ограничения – линейные функции. Это ускоряет процесс поиска решения.

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

•Автоматическое масштабирование – этот флажок следует включать, ко-

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

16

•Показывать результаты итераций – этот флажок позволяет включить пошаговый процесс поиска, показывая на экране результаты каждой итерации.

•Оценки – эта группа служит для указания метода экстраполяции – линейная или квадратичная, – используемого для получения исходных оценок значений переменных в каждом одномерном поиске. Линейная служит для использования линейной экстраполяции вдоль касательного вектора. Квадратичная служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.

•Разности (производные) – эта группа служит для указания метода численного дифференцирования, который используется для вычисления частных производных целевых и ограничивающих функций. Параметр Прямые используется в большинстве задач, где скорость изменения ограничений относительно невысока. Параметр Центральные используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдается сообщение о том, что получить более точное решение не удается.

•Метод поиска – служит для выбора алгоритма оптимизации. Метод

Ньютона был рассмотрен ранее. В Методе сопряженных градиентов за-

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

Сохранить модель поиска решения можно следующими способами:

1)при сохранении книги Excel после поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения;

2)если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например найти максимум и минимум

17

одной функции, или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Парамет-

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

ляется с помощью кнопки Параметры/Загрузить модель диалога Поиск решения;

3)еще один способ сохранения параметров поиска – сохранение их в виде именованных сценариев. Для этого необходимо нажать на кнопку Сохра-

нить сценарий диалогового окна Результаты поиска решений.

Кроме вставки оптимальных значений в изменяемые ячейки Поиск ре-

шения позволяет представлять результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения. Рассмотрим более подробно каждый из них.

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

Каждая изменяемая ячейка и ограничения приводятся в отдельной строке. Раздел для изменяемых ячеек содержит значение нормированного градиента, которое показывает, как целая ячейка реагирует на увеличение значения в соответствующей изменяемой ячейке на одну единицу. Подобным образом, множитель Лагранжа в

18

разделе для ограничений показывает, как целевая ячейка реагирует на увеличение соответствующего значения ограничения на одну единицу. При использовании целочисленных ограничений Excel выводит сообщение Отче-

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

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

ния, как статус и разница. Статус может принимать три состояния: связанное, несвязанное или невыполненное. Значение разницы – это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Связанное ограничение – это ограничение, для которого значение разницы равно нулю. Несвязанное ограничение – это ограничение, которое было выполнено с ненулевым значением разницы.

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

19

3.ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ ЛАБОРАТОРНЫХ РАБОТ

3.1.Лабораторная работа №1

Найти корень нелинейного уравнения f1(x)=f2(x) на заданном отрезке [a,b] средствами Excel тремя возможными способами:

1)по согласованию с преподавателем методом касательных (п. 1.4) либо методом простой итерации (п. 1.5) с использованием циклических ссылок (п. 2.1);

2)с помощью средства Подбор параметра (п. 2.2);

3)используя возможности Поиска решения (п. 2.3) при ограничениях

корень≥a и корень≤b.

Варианты заданий приведены в таблице (см. приложение).

3.2. Лабораторная работа №2

Разработать VBA–программу поиска корня нелинейного уравнения f1(x)=f2(x) на заданном отрезке [a,b] c заданной точностью ε по согласованию с преподавателем, реализовав в виде подпрограмм два из четырех описанных методов:

1)метод половинного деления (п. 1.2) либо метод хорд (п. 1.3);

2)метод касательных (п. 1.4) либо метод простой итерации (п. 1.5). Варианты заданий приведены в таблице (см. приложение).

Используя свои познания в математике, убедитесь в наличии или от-

сутствии корней внутри предложенного вам отрезка (п. 1.1) и в сходимости итерационного процесса, получаемого при реализации выбранного метода (в

применимости метода).

Вывод итоговой оценки для корня программа должна производить лишь с верными цифрами (число верных цифр после десятичной точки имеет порядок Lg(1/ε)).