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

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

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

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

Задача оптимизации в общем виде формулируется следующим образом (табл. 1.1).

Таблица 1.1 Постановка задачи оптимизации в общем случае

Название	Математическая запись	Описание
Целевая функция (критерий оптимизации)		Показывает, в каком смысле решение должно быть оптимальным, т. е. наилучшим. Возможны три вида целевой функции: максимизация, минимизация, назначение заданного значения
Ограничения	–целые (для задач целочисленного программирования); – для задач с булевыми переменными	Устанавливают зависимости между переменными. Могут быть односторонними и двусторонними. При решении задач двустороннее ограничение записывается в виде двух односторонних
Граничные условия		Показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении

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

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

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

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

Для решения задачи следует установить флажок Линейная модель в окне Параметры поиска решения. Это обеспечит применение симплекс-метода. В противном случае даже для решения линейной задачи будут использоваться более общие (т. е. более медленные) методы.

Поиск решения может работать также и с нелинейными зависимостями и ограничениями. Это, как правило, задачи нелинейного программирования или, например, решение системы нелинейных уравнений. Для успешной работы средства Поиск решения следует стремиться к тому, чтобы зависимости были гладкими или, по крайней мере, непрерывными. Наиболее часто разрывные зависимости возникают при использовании функции если то, среди аргументов которой имеются переменные величины модели. Проблемы могут возникнуть также и при использовании в модели функций типа ABS(), ОКРУГЛ() и т. д. Решая задачи с нелинейными зависимостями, следует: ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении); в окне Параметры поиска решения снять (если установлен) флажок.

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

Использование надстройки «Поиск решения»

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

Надстройка Поиск решения запускается командой Сервис | Поиск решения. Если в меню Сервис отсутствует команда Поиск решения, следует воспользоваться командой Сервис | Надстройки и установить флажок Поиск решения. Вид диалогового окна Поиск решения приведен на рис. 1.1, а опции его настройки – в табл. 1.2.

Таблица 1.2 Опции окна «Поиск решения»

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

Рис. 1.1. Окно Поиск решения

При нажатии кнопки Параметры в окне Поиск решения открывается окно Параметры поиска решения (рис. 1.2), описание опций которого приведено в табл. 1.3.

Рис. 1.2. Окно Параметры поиска решения

Таблица 1.3 Опции окна «Параметры поиска решения»

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

Сохранение (загрузка) различных данных для поиска решения осуществляется соответственно с помощью кнопок Сохранить модель и Загрузить модель окна Параметры поиска решения.

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