Поиск решения

    решение 
оптимизационных зачад

Рассмотрим все  аспекты работы надстройки «Поиск решения» 
на основе примера модели сбыта.

Откройте файл Solvsamp. XLS и 
перейдите на первый рабочий лист Краткий обзор. Для большей 
наглядности некоторые ячейки в моделях выделены цветом. Зеленым 
выделены ячейки-параметры, данные в которых могут изменяться для 
получения требуемого значения в целевой ячейке, выделенной синим 
цветом. Красным цветом помечены ячейки, на значения которых наложены 
ограничения. В данном случае рассматривается модель сбыта. «Поиск 
решения» поможет определить необходимость увеличения 
рекламного бюджета или его перераспределения с учетом сезонной 
поправки (табл. 18). Описание содержимого ячеек в данном примере 
приведено ниже модели.

Таблица18

Рабочий 
лист задачи Модель сбыта (исходные данные)

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

Чтобы найти  наилучшее решение:

1. Выделить 
   оптимизируемую ячейку. В данном примере это ячейка F15 
   (общая прибыль за год, обратите внимание, что при исходных данных 
   затрат на рекламу она равна 69662 р.)
2. Выберите 
   команду Сервис, Поиск решения. (рис.20)

 

 

Рисунок 
20. Диалоговое окно Поиск решения,

(указана 
только целевая ячейка)  

 

1. В 
   поле Установить целевую ячейку уже находится ссылка на 
   выделенную на первом шаге ячейку. При необходимости эту ссылку можно 
   изменить.
2. Установите 
   тип взаимосвязи между целевой ячейкой и решением путем выбора 
   переключателя в группе Равной.
3. В 
   поле Изменяя ячейки укажите ячейки-параметры, которые могут 
   изменятся в процессе поиска решения. В данном примере это ячейки 
   $B$11:$E$11 
   (расходы на рекламу в каждом квартале).
4. Нажмите 
   кнопку Добавить, чтобы ввести ограничения для задачи. При 
   этом откроется диалоговое окно Добавление ограничения (рис.21).

Рисунок 21. Диалоговое окно Добавление ограничения.

1. Введите 
   первое ограничение. В данном примере значение в ячейке F11 
   (общие расходы на рекламу) не должно превышать 40000.
2. В 
   поле ссылка на ячейку укажите ячейку F11, 
   а в поле ограничение введите число 40000. Знак отношения <=, 
   установленный по умолчанию, в данном случае можно не изменять.

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

1. Нажмите 
   кнопку ОК. В результате появится заполненное окно Поиск решения. 
   Нажмите кнопку Выполнить. По окончании поиска решения 
   появится диалоговое окно Результаты поиска решения (рис.22).

 

 

Рисунок 22. Окно диалога Результаты поиска решения.  

 

1. Выберите 
   переключатель Сохранить найденное значение, или 
   переключатель Восстановить исходные значения, чтобы оставить 
   значения, которые были на рабочем листе.

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

В предъявленном  диалоговом окне необходимо ввести имя  сценария и 
нажать кнопку ОК.

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

Издательский  дом "Геоцентр-Медиа" издаст два журнала: "Автомеханик" и "Инструмент", которые печатаются в трех типографиях: "Алмаз-Пресс", "Карелия-Принт" и "Hansaprint" (Финляндия), где общее количество часов, отведенное для  печати и производительность печати одной тысячи экземпляров ограничены и представлены в  следующей таблице: 
 
Спрос на журнал "Автомеханик" составляет 12 тысяч экземпляров, а на журнал "Инструмент" -не более 7,5 тысячи в месяц. 
Определите оптимальное количество издаваемых журналов, которое обеспечит максимально выручку от продажи.

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

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

По какому принципу их подбирать, что считать эффективным, что нет. Перед нами поставлена задача получить максимальную выручку. Таким  образом, цель - максимальная выручка.

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

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

Еще одно важное ограничение, о котором обычно забывают - переменные должны быть неотрицательными. 

Попытаемся представить  модель в Excel.

Переменные, то есть объем тиража, находятся в  ячейках B10:C12. Целевая функция - в  ячейке D13. Обратите внимание, целевая  функция построена формулой, ссылаясь на ячейки с переменными и исходные данные (стоимость единицы тиража).

Также формулами  подсчитывается фактическое время  печати тиража в каждой из типографий (ячейки E3:E5).

Все готово, приступаем решению задачи с помощью надстройки.

Включается она  через меню Сервис - Поиск решений. Если такого пункта меню нет, войдите  в меню Сервис - Надстройки и отметьте галочкой соответствующую надстройку. Может понадобиться установочный комплект Office.

Перед Вами появится следующий диалог:

Здесь указываем  адрес целевой ячейки, отмечаем, что ее нужно привести к максимальному  значению, изменяя ячейки $B$10:$C$12. Диапазоны  можно указывать мышью - станьте  в нужное поле диалога и выделите на листе нужные ячейки. Адрес автоматически  попадет в диалог.

Добавляем ограничения. После нажатия кнопки Добавить появляется диалог:

Вспоминаем. У  нас фактическое время печати тиража в каждой типографии не может  превышать заданного лимита.

Для Алмаз-Пресс  ограничение будет таким E3 ≤ D3. В  ячейке E3 должна быть формула суммы  продолжительности печати тиража первого  и вторго журналов в этой типографии, полученной перемножением тиража на норму времени.

Думаю, понятно, как ввести в диалог описанное  ограничение.

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

Ограничения неотрицательности  можно также задать с помощью  этого диалога - для каждой ячейки с объемом тиража установить ограничение  ≥0.

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

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

Все модель готова к расчету:

Нажимаем Выполнить.