Файл: Занятие 1 Тема Моделирование оптимизационных задач в Excel.pdf

Практическое занятие № 1
Тема 3. Моделирование оптимизационных задач в Excel
Достаточно часто в практике финансово-экономической деятельности приходится сталкиваться с оптимизационными задачами при ограничениях на какие-либо ресурсы. Примерами служат задачи оптимизации ассортимента продукции, задачи составления графиков занятости сотрудников организации, транспортные задачи, задачи о назначениях
(например, выбора продавцов продукции по сегментам рынка).
Для решения данной группы задач может быть использована надстройка
«
Поиск решения
», которая доступна из меню
Сервис
. Если эта надстройка не была инсталлирована, то ее установка происходит после выполнения последовательности команд
Сервис
+
Надстройки
+ «
Поиск решения
»+
ОК
Задачи, которые рационально решать с помощью надстройки «
Поиск решения
», имеют следующие свойства:
 имеется единственная максимизируемая или минимизируемая цель
(минимизация затрат на доставку, максимизация доходов за счет оптимизации средств на разные инвестиционные цели и т.п.);
 имеются ограничения, выражающиеся, как правило, в виде неравенств
(например, объем используемого сырья не должен превышать объем имеющегося сырья на складе);
 имеется набор входных значений-переменных, прямо или косвенно влияющих на ограничения и на оптимизируемые величины.
Рассмотрим применение надстройки «
Поиск решения
» на примере 1.
Пример 1. Оптимальные смеси компонентов.
Некоторый технологический процесс требует использования сырья с определенным содержанием примесей: примеси Ф не более 0.03%, примеси
П не более 3.25%. Состав сырья нормируется сортами А, В, С, однако ни один из сортов не соответствует требованиям по содержанию обеих примесей одновременно. Следовательно, нужно закупать все 3 сорта в определенной пропорции и смешивать их для обеспечения допустимых

долей примесей. Требуется найти эти пропорции, в которых следует закупать сырье разных сортов. Каждый сорт характеризуется не только долями примесей, но и стоимостью, поэтому требуется минимизировать общую стоимость приобретаемого сырья (целевая функция).
Исходные данные, подготовленные на листе рабочей книги, показаны на рис.
1.
Рис. 1. Исходные данные по примеру 1
Следует обратить внимание, что исходные данные обнаруживают достаточно запутанную ситуацию с выбором оптимального решения. В частности, сорт А удовлетворяет требованиям по содержанию П, но не соответствует норме по примеси Ф. Встречается ситуация, когда сырье (сорт
С) укладывается в норму по обеим примесям, но при этом отличается высокой ценой. Для сорта B ситуация обратная — пороги по примесям превышены, но цена относительно низка.
Ограничения по содержанию примесей Ф и П в финальной смеси являются неравенствами («не более порога»), поэтому не обязательно стремиться к тому, чтобы в финальной смеси проценты содержания примесей равнялись заданным значениям — они могут быть и меньше.
Не выдвигается требований по обязательности закупки какого-либо количества сырья каждого сорта. Таким образом оптимальный выбор может заключаться в закупке двух сортов из трех представленных (если это позволит сделать комбинация примесей в финальной смеси).
Кроме этого, не следует предполагать, что приобретение сырья единственного сорта будет целесообразным в том случае, если только он соответствует требованиям по примесям. Если имеется такая ситуация, что:
 определенный сорт подходит по обеим примесям, причем их содержание меньше предельно допустимого с заметным запасом на ухудшение качества;
 цена сорта заметно больше, чем цена другого сорта, качество которого

хуже, то для минимизации общей стоимости сырья может оказаться полезным частичное приобретение худшего дешевого сырья для смешивания с лучшим дорогим.
Вручную найти даже неоптимальное решение, отвечающее всем ограничениям, достаточно сложно. Рассмотрение задачи начнем с заполнения ячеек
С11:Е13
расчетными формулами (
=B11*C5
). В
С17
введем проверочную формулу
=ЕСЛИ(C14>E1*1,001;"Ошибка";" ")
(и аналогично для
D17
), что позволит визуально контролировать нарушение нормативов по содержанию примесей. Поправочный сомножитель позволяет избежать ложных срабатываний при сравнении вещественных чисел.
Рис. 2. Ограничения и целевая функция при поиске оптимальной закупки в Excel
Рис. 3. Параметры поиска решения в
Excel
В надстройке «
Поиск решения
» (см. рис. 2) зададим целевую функцию, изменяемые ячейки, 3 ограничения (сумма долей разных сортов должна быть равна 1 и суммарные доли по обеим примесям не должны быть больше предельно допустимых) и найдем оптимальное решение (см. рис. 3).
Можно проанализировать, как влияют изменения исходных данных на результат. Например, улучшение качества сорта B за счет снижения доли примеси П до 3.5% приводит к отказу от закупки сорта А.
Пример 2. Требуется определить оптимальные смеси материалов с ограничениями на доступные ресурсы компонентов: нормируемый показатель качества материала был не ниже 76, а содержание примеси S — не более 0.3%. Для смешивания используются 4 компонента, имеющие

разные значения контрольного показателя, содержание примеси и стоимость.
Фактическое наличие каждого компонента ограничено. Требуется получить определенное количество материала при минимально достижимой стоимости.
Формулировка примера напоминает предыдущий, но имеются и существенные отличия. В ситуации со смесью сырья разных сортов не было ограничений на доступность произвольного количества любого сорта, речь шла только о поиске приемлемой по содержанию примесей и оптимальной по стоимости смеси. Также не формулировалось требований по объему смеси, которую нужно получить.
Как и в предыдущих задачах, начнем с разработки структуры шаблона на рабочем листе. Шаблон будет состоять из 3 таблиц (см. рис.
4): требуемых характеристик, доступных компонентов и вывода решения.
Рис. 4. Лист Excel для примера 2
Таблица решения — изменяемые данные о количестве каждого компонента, и расчетные доли вклада каждого компонента в суммарный показатель качества, суммарную долю примеси, общий вес и общую стоимость. Суммарные показатели рассчитываются в столбце
H
. Формула в
D15
имеет вид
=D$14/$D$4*D8
, аналогично определяются результаты в остальных ячейках из блока
D15:G16
. Стоимость компонента получается перемножением его веса на себестоимость.
Суммарный вес (
H14
) должен быть не больше требуемого количества смеси (
D4
), т.е. предполагается, что при смешивании компоненты не теряются. Суммарный показатель качества (
H15
) должен быть не ниже допустимого (
D2
), а доля примеси (
H16
), наоборот, не выше нормированной
(
D3
). Целевой ячейкой выберем
H17
, т.е. будем минимизировать суммарную

стоимость (
H17
). Варьироваться будут ячейки с весом компонентов
(
D14:G14
).
Суммируем накладываемые на задачу ограничения:
 количество каждого из компонентов не должно превышать имеющийся запас этих компонентов (
$D$14:$G$14 <= $D$10:$G$10
);
 используемое количество каждого из компонентов должно быть целым числом (необязательное ограничение
$D$14:$G$14 = целое
);
 суммарный вес смеси (сумма по всем использованным компонентам) должен быть равен требуемому количеству смеси (
$H$14 = $D$4
);
 показатель качества смеси должно быть не ниже заданного (но может быть выше
$H$15 >= $D$2
);
 доля примеси S в смеси не должна быть превышать заданного порога
(может быть меньше
$H$16 <= $D$3
).
Результат поиска показан на рис. 5. Видно, что компонент №2 не требуется (что ожидаемо из-за его достаточно высокой стоимости при очень низком качестве).
Рис. 5. Оптимальное решение для примера 2
Пересчитаем оптимум при увеличении требуемого тоннажа смеси до
1200 (при этом имеющееся количество каждого из компонентов, как и прочие исходные данные, останется неизменным). Обратим внимание, что компонент
№ 2 по-прежнему не используется из-за своих неудовлетворительных характеристик. Компонент № 3 используется практически полностью (439 тонн из 440 имеющихся).
Оптимизация очень часто востребована в логистических задачах, таких как перевозка грузов.
Пример 3. Необходимо определить оптимальный объем перевозок товаров с 3 производственных площадок на 5 региональных складов с учетом

ряда ограничений. Товары могут доставляться с любого производства на любой склад, однако, очевидно, что стоимость доставки на большее расстояние будет большей (хотя и необязательно пропорционально расстоянию).
Цель решения задачи — уменьшение совокупных транспортных расходов.
Исходные данные приведены на рис. 6 и в табл.
1.
Целевой функцией является минимизация затрат на перевозку (
В15
).
Ограничения перечислены в табл. 2 (см. также рис. 7).
Рис. 6. Оформление шаблона для примера 3
Таблица 1
Структура рабочего листа для примера 3
Ячейки
Показатель
Формула и комментарий
В15
Общая стоимость перевозок
=СУММ(C15:G15)
C3:G5
Объемы перевозок от каждого из производств к каждому складу изменяемые ячейки
C7:G7
Суммарный объем перевозок на каждый из складов
С7 =СУММ(С3:С5) и далее аналогично
C9:G9
Потребности складов исходные данные
В11:В13
Объемы производства каждым из заводов исходные данные
C11:G13 Стоимости перевозок
Стоимость перевозки товарной единицы в пределах логистического звена
Продолжение табл. 1
C15:G15 Суммарные стоимости перевозок по каждому складу
С15 =СУММПРОИЗВ(C3:C5;
C11:C13) и далее аналогично
B15
Суммарные логистические издержки
=СУММ(C15:G15)

Таблица 2
Ограничения на перемещения товаров в примере 3
Ограничение
Примечания
В3:В5<=В11:В13
Количества перевезенных грузов не могут превышать производственных возможностей
C7:G7>=C9:G9
Количество доставляемых грузов не должно быть меньше потребностей складов, т.е. общее производство должно быть не меньше интегральной потребности
C3:G5>=0 n
Объем перевозок не может быть отрицательным
Результат поиска оптимального решения приведен на рис. 8.
Рис. 7. Ограничения для примера 3
Рис. 8. Решение для примера 3
Корректировка исходных данных дает получить иные предложения по организации логистических цепочек. Такой подход с известными ограничениями, может быть использован в различных задачах оптимизации перевозок. Существенным недостатком этого примера является отсутствие ограничений на «кратность» перевозимых партий товаров, например, в соответствии с вместимостью транспортных средств или товарными упаковками. Допустим, перевозки должны осуществляться партиями по 10 единиц. В этом случае объемы перевозок по некоторым из направлений
«производство → региональный склад» окажутся неоптимальными, так как несколько перевозок окажутся «частично холостыми» (избыточный расход топлива, неоправданный износ транспортных средств, нерациональная оплата работы водителей и т.п.).
Пример 4. Построение графика занятости.

Предприятие торговли обслуживается семью группами сотрудников
(группы обозначены А, Б...Ж). Признак разделения на группы — разные выходные дни. Выходных дней для каждой группы — не менее двух, выходные дни должны следовать подряд. Каждый сотрудник входит только в одну группу. На основе статистических данных о среднем количестве посетителей в зависимости от дня недели известна потребность в сотрудниках в каждый из дней. Существенно, что эта потребность различна.
Все сотрудники имеют одинаковый размер понедельной оплаты, не зависящий от графика работы.
Необходимо подобрать такую численность сотрудников в каждой группе, чтобы добиться минимизации суммарных затрат на оплату труда при выполнении требований по числу сотрудников на каждый день.
Описание шаблона для решения задачи (см. рис. 9) приведено в табл. 3.
Рис. 9. Оформление шаблона для примера 4
Таблица 3
Описание шаблона для примере 4
Ячейки
Показатель
Формула и примечание
C15
Общая недельная зарплата
Минимизируемая величина
(=C14*СУММ(D11:J11))
C14
Дневная зарплата 1 работника
Параметр
B4:B10
Выходные дни
Перечисление аббревиатур выходных дней
C4:C10 Число сотрудников в каждой из групп
Изменяемые данные
D4:J10
График работы групп
Кодирование выходных дней (1 — группа

работает, 0 — день отдыха). Используются формулы вида =ЕСЛИ( ЕОШИБКА(
НАЙТИ(D$3; $B4)); 1;0).
D11:J11 Количество сотрудников, работающих в каждый из дней
=СУММПРОИЗВ($C$4:$C$10;D4:D10)
D12:J12 Требуемое количество сотрудников в каждый из дней недели
Исходные данные — значения на базе накопленного опыта работы
С12
Общее число сотрудников
=СУММ(С4:С10)
Целевой функцией является общая недельная зарплата (
С15
).
Ограничения, действующие в задаче оптимизации графика занятости, суммированы в табл. 4 (см. также рис. 10).
Таблица 4
Ограничения на перемещения товаров в примере 4
Ограничение
Примечания
C4:C10>=0
Число сотрудников в группе неотрицательно, однако может быть равным нулю — это означает, что можно организовать меньшее количество групп
C4:C10=Целое
Число сотрудников должно быть целым
D11:J11>=D12:J12 Число занятых сотрудников не должно быть меньше ежедневной потребности
Результат задачи приведен на рис. 11. В данном случае оказывается, что группа Ж вовсе не требуется для выполнения сформулированных условий.
Рис. 10. Ограничения для примера 4
Рис. 11. Решение для примера 4
Необходимо отметить, что оптимальное распределение по группам не обязано может быть единственным. Варьируя начальные значения в массиве
C4:C10
и повторно вызывая окно «
Поиск решения
», можно получить другие

оптимальные решения с иными числами сотрудников в группах и иными числами сотрудников в каждый из дней недели. Таким методом последовательного расчета можно выбрать наиболее подходящее решение с точки зрения дополнительных критериев (которые не заданы в явном виде или не могут быть формализованы в качестве ограничений). Вполне целесообразным окажется подбор варианта, обеспечивающего большее, чем требуется, число сотрудников в напряженные дни работы парка (создавая тем самым резерв сотрудников, работающих в эти дни). В реальных задачах подобного класса требуется учитывать график отпусков сотрудников, необходимый резерв на случай пребывания сотрудников на больничном и т.п.
Пример 5. Необходимо найти оптимальное соотношение числа студентов, обучающихся в ВУЗе на бюджетных местах и по платной схеме.
Предположим (весьма условно), что перед ВУЗом не стоит никакой иной задачи, кроме получения максимальной прибыли. Допустим, что определены:
 максимальное число студентов, которые могут одновременно учиться в
ВУЗе (не более, может быть и меньше — обеспечивать полную загрузку аудиторного фонда и профессорско-преподавательского коллектива не требуется);
 максимальное число бюджетных мест для студентов (не более, может быть и меньше);
 нет ограничений на число платных студентов (однако ограничено общее число студентов);
 общее число преподавателей (не более, может потребоваться и меньше);
 максимальное число студентов, обучающихся за счет бюджетных средств, которое приходится на одного преподавателя;
 максимальное число студентов, оплачивающих свое образование и приходящихся на одного преподавателя;
 максимальное число мест в общежитии;
 доля от общего числа студентов-бюджетников, проживающих в