Методы и модели оптимизации для решения экономических задач

 

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

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

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

Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует выполнить команду Поиск решения меню Данные – Анализ «что-если» (при условии, что выбрана надстройка Поиск решения).

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

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

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

Поле Изменяя ячейки нельзя оставить пустым, и указанные в нем ячейки обязательно должны влиять на значение целевой ячейки.

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

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

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

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

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

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

1. С помощью полей Максимальное время и Предельное число итераций можно указать поиску решений, сколько усилий он должен приложить для решения задачи.

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

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

2. Относительная погрешность используется поиском решения для определения точности выполнения ограничений. Чем ближе это значение к 1, тем ниже точность. Задание относительной погрешности, меньше установленной по умолчанию (0,000 001) приводит к росту времени поиска решения.

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

4. Переключатели Оценки, Разности и Метод поиска позволяют уточнить применяемые методы оптимизации, обычно используются значения, установленные по умолчанию.

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

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

Следует иметь в виду, что промежуточные результаты могут не удовлетворять всем заданным ограничениям.

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

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

Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся «статус» и «разница». Разница – это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: «Связанное» (ограничение, для которого значение разницы равно 0), «Не связанное» (ограничение, которое было выполнено с ненулевым значением разницы) и «Не выполненное».

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

Для каждой изменяемой ячейки этот отчет содержит оптимальное значение, а также наименьшее и наибольшее значения, которые ячейка может принимать без нарушения ограничений. Поиск решений может решить не каждую предложенную задачу. Если оптимальное решение не найдено, в окне диалога
Результаты поиска решений выводится сообщение о неуспешном завершении.

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

 

 

Контрольные вопросы

 

1. Дайте характеристику возможностям электронных таблиц.

2. Назовите известные вам табличные процессоры.

3. Какие инструменты MS Excel можно использовать для проведения анализа данных?

4. Для решения, каких категорий задач целесообразно использовать MS Excel?

5. Какие виды анализа можно проводить с помощью MS Excel?

6. В чем состоит назначение функций для проведения анализа с помощью MS Excel?

7. Назовите категории встроенных функций MS Excel.

8. В каких случаях используется Пакет анализа
в MS Excel?

9. Дайте характеристику инструменту Пакет анализа.

10. Дайте характеристику инструменту Поиск решения.