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

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

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

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

 

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

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

 

 

 

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

 

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

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

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

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

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

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

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

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

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

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

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

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

 

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

 

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

 

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

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

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

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

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

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

 

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

 

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