Решение задач оптимизации средствами Microsoft Excel
Для решения задач оптимизации в Microsoft Excel предназначена надстройка Поиск решения.
Постановка задачи
В общем виде задача оптимизации ставится следующим образом: найти оптимальное (максимальное или минимальное) значение функции
при ограничениях
.
Если функции 
и 
являются линейными, т. е.
,
то такая задача называется задачей линейного программирования.
Подготовка блока данных
1. На рабочем листе отвести блок данных под изменяемые ячейки, т. е. ячейки для хранения переменных 
. В результате решения задачи в этих ячейках появятся искомые значения 
.
2. В отдельную ячейку ввести формулу для целевой функции . Эта ячейка называется целевой.
3. отдельные ячейки ввести формулы для левой части ограничений .
Запуск программы «Поиск решения»
1. На вкладке Данные в группе Анализ выбрать команду Поиск решения.
2. В поле Установить целевую ячейку ввести ссылку на целевую ячейку.
3. В поле Изменяя ячейки ввести ссылку на диапазон изменяемых ячеек.
4. Для задания ограничений в группе Ограничениящелкнуть по кнопке Добавить.
В открывшемся диалоговом окне выполнить следующие действия:
- в поле Ссылка на ячейку ввести ссылку на ячейку с формулой, определяющей первое ограничение ( 
);
- во втором поле выбрать оператор ограничения (>, <, = и т.д);
- в поле Ограничение ввести значение ограничения 
5. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 4.
6. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.
7. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.
8. При необходимости можно задать максимальное время решения, предельное число итераций, относительную погрешность, допустимое отклонение, сходимость, метод поиска. Для этого с помощью кнопки Параметры открыть диалоговое окно Параметры поиска решения.
Если известно, что решаемая задача линейная, то следует включить режим Линейная модель: процесс решения значительно ускорится.
Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК.
9. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить.
Полученные результаты будут выведены на рабочий лист.
После завершения процедуры решения в диалоговом окне Результаты поиска решения можно выполнить один из следующих вариантов:
- сохранить найденное решение или восстановить исходные значения на рабочем листе;
- сохранить параметры поиска решения в виде модели;
- сохранить решение в виде сценария;
- просмотреть любой из встроенных отчетов.
Сохранение модели
Текущие установочные параметры для поиска решения можно сохранить в виде модели.
Для этого надо в диалоговом окне Параметры поиска решения щелкнуть по кнопке Сохранить модель и указать на рабочем листе область для сохранения модели (можно указать только верхнюю ячейку области).
При сохранении модели запоминаются целевая ячейка, изменяемые ячейки, ограничения и параметры поиска решения.
Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения.
Сохранение сценария
Найденные решения (значения изменяемых ячеек) можно сохранить в качестве сценария. Для этого нужно:
1. В диалоговом окне Результаты поиска решения выбрать Сохранить сценарий.
2. В поле Название сценария ввести имя.
Просмотреть сценарии можно с помощью команды Диспетчера сценариев (на вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев).
Создания отчета по результатам поиска решения
С помощью программы Поиск решения можно создать три типа отчетов по результатам, полученным при успешном завершении процедуры решения. Каждый отчет создается на отдельном листе текущей рабочей книги.
Для создания отчета надо в диалоговом окне Результаты поиска решения выбрать нужный тип в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша Ctrl).
Типы отчетов:
- результаты – отчет содержит целевую ячейку, список изменяемых ячеек, их исходные и конечные значения, ограничения и сведения о них;
- устойчивость – отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений);
- пределы – выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.
Практическая работа 8
Решение задач оптимизации средствами Microsoft Excel
1. Решить задачи линейного программирования:
1) найти максимальное значение функции
при следующих ограничениях: 
Ответ: 
при 
2) найти минимальное значение функции
при следующих ограничениях: 
Ответ: 
при 
2. Решить задачи нелинейного программирования:
1) найти максимальное значение функции 
при условии 
Ответ: 
при 
;
2) найти максимальное и минимальное значение функции 
при условии 
.
Ответ: 
при 
; 
при 
;
3) найти максимальное и минимальное значение функции 
при условии 
.
и 
.
Ответ: 
при 
.
3. Найти оптимальное решение следующих экономических задач:
1) Задача планирования производства
Фирма реализует товары двух видов: Товар 1 и Товар 2, используя при этом четыре типа ресурсов – технические, трудовые, финансовые и транспортные. Норма затрат ресурсов на единицу товара, общий объем каждого ресурса и прибыль от реализации каждого товара заданы в таблице 1.
Таблица 1
| Ресурсы | Норма затрат ресурсов на товары | Общее количество ресурсов | |
| Товар 1 | Товар 2 | ||
| Технические | |||
| Трудовые | |||
| Финансовые | |||
| Транспортные | |||
| Прибыль на единицу продукции, руб. | 2,00 | 3,00 |
Требуется определить количество товаров каждого вида, обеспечивающее фирме максимальную прибыль.
В этой задаче переменные 
и 
– количество товаров каждого вида; целевая функция
;
ограничения на объем каждого вида ресурсов: 
Ответ: 
2) Транспортная задача
Предприятия, выпускающие одинаковую продукцию, могут реализовать ее в трех различных магазинах. Стоимость транспортировки единицы объема продукции от каждого предприятия до каждого магазина, а также объем готовой продукции на каждом предприятии и пропускная способность каждого магазина приведены в таблице 2.
Таблица 2
| Магазин 1 | Магазин 2 | Магазин 3 | Магазин 4 | Объем готовой продукции | |
| Предприятие 1 | 1 000 | ||||
| Предприятие 2 | 1 100 | ||||
| Предприятие 3 | |||||
| Пропускная возможность магазинов | 1 100 |
Рассчитать объем продукции, который следует поставлять с каждого предприятия в каждый магазин, чтобы затраты по транспортировке были наименьшими.
Здесь переменные 
− объем продукции, поставляемый из i-го предприятия в j-й магазин; целевая функция
ограничения на объем готовой продукции: 
ограничения, накладываемые пропускной возможностью магазинов: 
кроме того 
‑ ограничения, определяемые физическим смыслом задачи.
Ответ: 