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

Лабораторная работа № 8. Поиск оптимальных решений

Введение

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

Таким образом, при постановке задачи должны быть определены:

-  целевая функция (с оговоркой характеристики оптимизации — максимум, минимум, конкретное значение);

-  переменные и зависимости между ними;

-  ограничения или дополнения.

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

Общая схема решения такова:

Составляется таблица, в которой в виде формул определяются зависимости между переменными; здесь же задается и формула, определяющая целевую функцию.

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

Затем Поиск решения запускается на выполнение.

Через некоторое время появляется окно Результаты поиска решения. Если решение найдено, то можно его сохранить. Но можно и скорректировать какие-то ограничения и еще  раз запустить команду Поиск решения.

Постановка задачи

В качестве примера решим такую задачу:

Предприятие, изготавливающее некоторое изделие, находится в городе Ангорск. Это предприятие должно по договору за свой счет доставлять изготовленные изделия в  торговые точки, находящиеся в городах Берск, Вирск, Гурск, Дорск, Егорск, Жарск (в каждом городе по одной точке). Для каждой торговой точки известны размер контейнера, в котором туда доставляются изделия (количество изделий, доставляемых за один раз), и стоимость доставки одного контейнера.

Определить количество контейнеров, доставляемых в каждую конкретную торговую точку, при следующих условиях:

всего должно быть доставлено не менее, чем 12000  изделий;

общая стоимость доставки должна быть минимальной;

в каждую точку должно быть доставлено не менее, чем 6 контейнеров;

для точек в городах Берск, Вирск, Гурск каждый десятый контейнер доставляется бесплатно (за счет торговой точки);

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

Выполнение

1.  Составим Таблицу 1 (для определенности разместим эту таблицу  начиная с ячейки А1):

Таблица 1.

Начиная со столбца Оплачено доставоктаблицапока не заполнена.

2.  Чтобы определить количество оплачиваемых доставок с учетом бесплатности каждой десятой доставки (в города Берск, Вирск, Гурск), поместите в ячейки столбца Оплачено доставок: для этих городов формулы типа  =D2-ЦЕЛОЕ(D2/10),  для остальных — формулы типа = D5.

3.  В колонке Кол-во изделий поместите формулы произведения размера контейнера на Кол-во доставок, а в колонке Стоимость доставки —  формулы произведения цены одной доставки на Оплачено доставок.

4.  В этих же колонках посчитайте суммы.

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

В результате  заполнения таблицы формулами получится Таблица 2.

Таблица 2