Решение задачи в Excel

Для решения задачи используется команда Сервис/Поиск решения.

Если такой команды в меню нет, то необходимо выполнить команду Сервис/Надстройки и установить Поиск решения.

После выполнения команды появится окно:

 

Задать ячейку с целевой функцией, изменяемые ячейки, ограничения.

Добавление ограничений:

 

 

 

Задать параметры поиска решения:

 

Параметр "Максимальное время"; служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).

Параметр "Предельное число итераций";служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.

Параметр "Относительная погрешность"; служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.

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

Параметр "Сходимость"; применяется только при решении нелинейных задач.

Установка флажка "Линейная модель"; обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода. При этом в отчете по устойчивости, который можно получить после решения задачи, будет выводиться более полная информация.

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

Параметр "Автоматическое масштабирование"; служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине — например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Параметр "Показывать результаты итераций"; служит для приостановки поиска решения для просмотра результатов отдельных итераций.

Параметр "Оценка"; служит для указания метода экстраполяции — линейная или квадратичная — используемого для получения исходных оценок значений переменных в каждом одномерном поиске.

Параметр "Производные"; служит для указания метода численного дифференцирования — прямые или центральные производные — который используется для вычисления частных производных целевых и ограничивающих функций.

Параметр "Метод"; служит для выбора алгоритма оптимизации — метод Ньютона или сопряженных градиентов — для указания направление поиска.

 

Для нахождения решения нажать кнопку «Выполнить» в окне Поиска решения.

 

В появившемся окне «Результаты поиска решения» отображается информация о том, найдено или нет решение, в этом окне можно выбрать тип отчета.

 

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

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

Описание отчетов:

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

Устойчивость. Используется для создания отчета, содержащего сведения о чувствительности решения к малым изменениям в формуле (поле Установить целевую ячейку, диалоговое окно Поиск решения) или в формулах ограничений. В случае нелинейных моделей отчет содержит данные для градиентов и множителей Лагранжа. В отчет по нелинейным моделям включаются ограниченные затраты, фиктивные цены, объективный коэффициент (с некоторым допуском), а также диапазоны ограничений справа.

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

 

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

 

Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме можно сразу нажать кнопку "OK". После этого в экранной форме появляется оптимальное решение задачи.