Постановка задачи
На этом этапе необходимо сформулировать математическую задачу. Для этого нужно определить, какую задачу необходимо решить (решение уравнения, системы уравнения, поиска максимального или минимального значения функции), какие переменные используются, какая зависимость существует для уравнения и критериальной функции от выявленных переменных, какие есть ограничения (граничные условия) и как они зависят от входящих переменных. Результатом этого этапа является формализованная запись задачи на бумаге.
При решении систем уравнений правая часть первого уравнения должна быть числом. Поэтому при необходимости необходимо осуществить предварительные преобразования задачи. Другие уравнения и ограничения в правой части могут содержать выражения.
Этот этап не зависит от табличного процессора, в котором осуществляется решение задачи (Excel или Calc)
Создание модели вычислений
Этот этап заключается в правильной формализованной записи содержания задачи на листе табличного процессора.
1. Выбрать ячейки листа, где будут находиться найденные программой значения переменных (неизвестных). Эти ячейки называются изменяемыми, так как программа последовательно изменяет их значения в итерационном процессе до получения допустимого решения.
2. Записать формулу левой части одного из уравнений системы или целевой функции в отдельной ячейке. Эта ячейка называется целевой. При записи формулы использовать адреса выбранных ячеек с переменными.
3. Выражение в левой и правой частях для других уравнений в системе или каждого ограничения записать в виде формулы в отдельной ячейке листа. При записи формул использовать адреса выбранных ячеек с переменными.
|
|
Каждой ячейке, используемой в модели вычислений, желательно присвоить имя, или дать пояснения в соседних ячейках.
Перед обращением к программе Поиск решения желательно выделить целевую ячейку (тогда не нужно будет заполнять окно со ссылкой на нее, она появится автоматически).
Поиск решения
Обратиться к программе Поиск решения… в меню Сервис. В открывшемся окне Поиск решения произвести основные установки.
1. В окне Установить целевую ячейку указать адрес ячейки с введенной формулой целевой функции или левой частью уравнения. Он появится автоматически, если перед вызовом программы нужная ячейка будет активизирована.
2. В меню Равно указать вариант оптимизации (максимизация, минимизация). Если ставится задача поиска решения, при котором целевая функция должна быть равна некоторому числу, то необходимо ввести это число в поле. Требование равенства целевой функции определенному числу означает возможность решения уравнений и систем уравнений.
3. В окне Изменяя ячейки указать адреса выбранных ранее ячеек (или выделить соответствующие ячейки) для переменных (неизвестных) в целевой функции или в уравнении. При нажатой клавише Ctrl можно выделить несмежные ячейки. В этих ячейка будут изменяться значения переменных в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации. Можно решать задачи, включающие до 200 переменных.
Для каждой изменяемой ячейки можно указать два ограничения.
Адреса в поле Изменяя ячейки могут появиться автоматически, если установить метку Предположить, и предварительно указать начальные значения переменных. Тогда программа самостоятельно находит все ячейки, влияющие на формулу, ссылка на которую дана в поле Установить целевую ячейку.
|
|
4. В поле Ограничения отобразить все граничные условий поставленной задачи. Для каждой переменной или для каждого выражения с этими переменными можно задать диапазон (верхнюю и/или нижнюю границы) допустимых значений.
Указать адреса ячеек с записанными в них формулами для ограничений. Для этого использовать кнопки Добавить, Изменить, Удалить для выбранных ограничений.
Для целевой ячейки также можно установить ограничения.
Для удобства можно выбрать ячейки по их именам из предлагаемого списка после нажатия клавиши F3. Предварительно нужно задать ячейкам имена.
Для присвоения имени ячейке или диапазону ячеек нужно их выделить, поместить курсор в поле имени и заменить представленный адрес нужным именем. Затем нажать Enter. Присвоенное имя будет отображено в поле имени.
5. Решить уравнение, систему уравнений или задачу оптимизацию, нажав кнопку Выполнить в окне поиска решения. Результаты появятся на листе с вычислительной моделью и в окне диалога Результаты поиска решения.
Дополнительная настройка программы
При необходимости можно указать особые, то есть отличные от установленных по умолчанию, условия и варианты поиска решения. Для этого использовать диалоговое окно Параметры поиска решения, в которое можно зайти из окна Поиск решения, нажав кнопку Параметры.
|
|
В свою очередь это окно включает следующие диалоговые окна и переключатели.
Окно Максимальное время служит для ограничения времени в секундах на поиск решения. Наибольшее допустимое время равно 32767 секунд (более 9 часов). По умолчанию устанавливается 100 секунд, что обычно бывает достаточно.
Окно Число итераций также ограничивает время вычисления за счет ограничения числа промежуточных вычислений (итераций).
Окно Относительная погрешность служит для указания того, насколько найденное решение может отклоняться от установленного значения в целевой ячейке. Она задается в виде десятичной дроби между нулем и единицей.
Окно Допустимое отклонение служит для задания максимального допуска на отклонение для значения изменяемой ячейки, если множество значений ограничено целыми числами. Чем больше допуск, тем быстрее будет найдено решение.
Окно Сходимость также определяет условие прекращения поиска. Когда относительное изменение значения целевой функции за последние пять итераций становится меньше порога (числа, указанного в поле Сходимость), то поиск прекращается. Сходимость применяется только к нелинейным задачам, условием служит десятичная дробь от 0 до 1. Лучшая сходимость (с меньшим дробным числом) требует больше времени на поиск оптимального решения.
Метка Линейная модель предназначена для ускорения поиска, если решается задача линейной оптимизации.
Опция Показывать результаты итераций используется для приостановки поиска и просмотра результатов после каждой итерации.
Опция Автоматическое масштабирование необходима, если значения в изменяемых ячейках по величине сильно отличаются от выходных значений в целевой ячейке и в ограничениях.
Значения не отрицательны. Используется, когда для влияющих ячеек не была установлена нулевая нижняя граница в поле Ограничения диалогового окна Добавить ограничение.
Переключатель Оценка служит для указания метода получения оценок значений переменных в каждом одномерном поиске (Линейная или Квадратичная). Квадратичная экстраполяция, используемая для получения оценок, более точна и обычно применяется в нелинейных задачах.
Переключатель Производные служит для указания метода численного дифференцирования — Прямые (используются в большинстве задач) или Центральные (используются, когда выдается сообщение о том, что получить более точное решение не удается).
Переключатель Метод служит для выбора алгоритма оптимизации — метод Ньютона (для задач небольшой размерности и для хорошо сходящихся задач) или сопряженных градиентов (для задач большой размерности и когда итерации дают слишком малое отличие в последовательных приближениях).
Опция Загрузить модель позволяет задать ссылку на область ячеек, содержащих загружаемую модель.
Сохранить модель. С его помощью этой опции можно задать ссылку на область ячеек, предназначенную для хранения модели оптимизации, если необходимо хранить на листе более одной модели оптимизации. Первая модель сохраняется автоматически.
Анализ результатов
Программа после отдельного запуска может найти только одно из возможных решений. Это являться существенным ограничением программы, требующим дополнительного анализа задачи и результатов, а в некоторых случаях и уточнения условий задачи.
В результате поиска в ячейках листа появятся значения, определяемые найденными переменными. После каждого запуска программы необходимо проверить для найденного программой решения удовлетворение исходных требований, выражений и функции. Для проверки используются занесенные программой в ячейки соответствующие значения.
Программа может найти такое решение, которое противоречит физическому смыслу задачи и потому не удовлетворяет специалиста. Она может вообще не найти никакого допустимого решения, или найти решение с недостаточной точность. По завершении поиска программа выдает соответствующие комментарии о результатах. Каждый случай требует дополнительного анализа.
Если решение находится в неприемлемой области, противоречит физическому смыслу задачи, то следует провести дополнительное исследование задачи и ввести дополнительные граничные условия. После этого нужно повторить расчет. С этой проблемой иногда можно справиться, сразу задавая начальные значения переменных из ожидаемой области решений.
Таким образом, при необходимости поиска других допустимых решений необходимо изменить начальные значения переменных и/или ограничить область поиска, задав дополнительные ограничения.
Если одно из требований в результате поиска не удовлетворяется, то программа не смогла найти допустимое решение. В этом случае необходимо выбрать другие исходные данные для переменных, или увеличить число итераций, или изменить метод поиска.
Недостатком программы поиска является высокая чувствительность к начальным значениям неизвестных. Если начальные значения неизвестных выбраны неудачно, то решение может не быть найдено, хотя оно заведомо существует.
Например, программа не может найти ни одного решения уравнения x*sinx = -1, если установить начальные значения x = 0, или х = 1, или x = 2, или x = -1, или x = -2 и т.д. Вместе с тем существует бесконечное множество решений этого уравнения, из них наименьшими по модулю являются x = 3,4368 и x = -3,4368.
Если не устанавливать начальные значения переменных, то программа пробует начать поиск с различными исходными данными. Поэтому при отсутствии сведений об области, где находится решение, рекомендуется вначале не устанавливать никаких исходных значений переменных.
Таким образом, если допустимое решение не достигнуто, то причиной может служить неверное задание исходных значения переменных. Нужно дать возможность программе самой выбрать исходные данные (то есть следует стереть значения переменных на листе перед запуском программы), или установить исходные данные в области предполагаемого решения. Если эти меры не помогли, то следует проанализировать инженерную задачу и по возможности ослабить ограничения.
Если решение найдено, но его точность не удовлетворяет специалиста, то нужно повторно запустить программу с уже найденным решением в качестве новых исходных данных, или изменить настройки программы в окне Параметры поиска решения (увеличить число итераций или применить другой метод).
При выполнении программы Поиск решения она может выдавать следующие сообщения:
- Оптимальное решение не найдено.
- Значения влияющих ячеек и целевой ячейки или ячейки, на которую наложены ограничения, сильно различаются.
- Ожидаемое решение не получено.
- Найденное решение отличается от предыдущего результата.
- Поиск не может найти оптимальное решение.
- Поиск не может улучшить текущее решение. Все ограничения выполнены.
- Поиск остановлен (истекло заданное на поиск время).
- Поиск остановлен (достигнуто максимальное число итераций).
- Значения целевой ячейки не сходятся.
- Поиск не может найти подходящего решения.
- Поиск остановлен по требованию пользователя.
- Условия для линейной модели не выполняются.
- При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.
- Мало памяти для решения задачи.
- Другой экземпляр Microsoft Excel использует SOLVER.DLL
Для разрешение вопросов, возникающих при поиске решения, необходимо обращаться к справочной информации в составе табличного процессора.