Содержание:
Постановка задачи
Предположим, что компания, где вы работаете, имеет три склада, откуда товар поступает в пять ваших магазинов, разбросанных по Москве.
Каждый магазин способен продать определенное количество известного нам товара. Каждый из складов имеет ограниченную вместимость. Задача состоит в том, чтобы рационально выбирать, с какого склада в какие магазины доставить товар, чтобы минимизировать общие затраты на транспортировку.
Прежде чем приступить к оптимизации, необходимо будет составить на листе Excel простую таблицу — нашу математическую модель, описывающую ситуацию:
Понятно, что:
- Светло-желтая таблица (C4:G6) описывает стоимость доставки одного товара с каждого склада в каждый магазин.
- Фиолетовые ячейки (C15:G14) описывают количество товаров, необходимое для продажи каждому магазину.
- Красные ячейки (J10:J13) отображают вместимость каждого склада — максимальное количество товаров, которое может хранить склад.
- Желтые (C13:G13) и синие (H10:H13) ячейки представляют собой суммы строк и столбцов для зеленых ячеек соответственно.
- Общая стоимость доставки (J18) рассчитывается как сумма произведений количества товаров и соответствующих им затрат на доставку – для расчета здесь используется функция SUMPRODUCT (СУММПРОИЗВ).
Таким образом, наша задача сводится к выбору оптимальных значений зеленых ячеек. И чтобы общая сумма по линии (синие ячейки) не превышала вместимость склада (красные ячейки), и при этом каждый магазин получал то количество товара, которое ему необходимо продать (сумму для каждого магазина в желтые ячейки должны быть максимально приближены к требованиям – фиолетовые клетки).
Решения
В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно, уже давно разработаны способы их решения не тупым перебором (а это очень долго), а за очень малое количество итераций. Excel предоставляет пользователю такую функциональность с помощью надстройки. Поисковые решения (Решатель) на вкладке Данные (Дата):
Если на вкладке Данные в вашем Excel нет такой команды – ничего страшного – это значит, что надстройка просто еще не подключена. Чтобы активировать его, откройте Файл, А затем выберите параметры – Дополнения – О нас (Параметры — Надстройки — Перейти). В открывшемся окне ставим галочку возле нужной нам строки Поисковые решения (Решатель).
Запустим дополнение:
В этом окне вам необходимо установить следующие параметры:
- Оптимизировать целевую функцию (Установить tденьги клетка) – здесь необходимо указать конечную основную цель нашей оптимизации, т.е. розовое поле с общей стоимостью доставки (J18). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести ее к заданному значению (например, точно уложиться в выделенный бюджет).
- Изменение ячеек переменных (By изменения клетки) — здесь мы указываем зеленые ячейки (C10:G12), варьируя значения которых мы хотим добиться нашего результата — минимальной стоимости доставки.
- Соответствует ограничениям (Тема в домен Ограничения) — список ограничений, которые необходимо учитывать при оптимизации. Чтобы добавить ограничения в список, нажмите кнопку Добавить (Добавлять) и введите условие в появившемся окне. В нашем случае это будет ограничение спроса:
и ограничение на максимальный объём складов:
Помимо очевидных ограничений, связанных с физическими факторами (вместимость складов и транспортных средств, бюджетные и временные ограничения и т.п.), иногда необходимо добавить ограничения «специально для Excel». Так, например, Excel может легко устроить вам «оптимизацию» стоимости доставки, предложив перевезти товар из магазина обратно на склад – затраты станут отрицательными, т.е. мы получим прибыль! 🙂
Чтобы этого не произошло, лучше оставить флажок включенным. Сделать неограниченное количество переменных неотрицательными или даже иногда явно прописывать такие моменты в списке ограничений.
После установки всех необходимых параметров окно должно выглядеть так:
В раскрывающемся списке «Выберите метод решения» дополнительно необходимо выбрать соответствующий математический метод решения на выбор из трех вариантов:
- Симплексный метод — это простой и быстрый метод решения линейных задач, то есть задач, в которых выходные данные линейно зависят от входных данных.
- Общий метод пониженного градиента (OGG) – для нелинейных задач, где существуют сложные нелинейные зависимости между входными и выходными данными (например, зависимость продаж от затрат на рекламу).
- Эволюционный поиск решения — относительно новый метод оптимизации, основанный на принципах биологической эволюции (привет Дарвину). Этот метод работает во много раз дольше первых двух, но позволяет решить практически любую задачу (нелинейную, дискретную).
Наша задача четко линейна: поставили 1 штуку — потратили 40 рублей, доставили 2 штуки — потратили 80 рублей. и т. д., поэтому симплексный метод является лучшим выбором.
Теперь, когда данные для расчета введены, нажимаем кнопку Найти решение (Решать)чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяющихся ячеек и ограничений поиск решения может занять много времени (особенно при использовании эволюционного метода), но наша задача для Excel не станет проблемой — через пару мгновений мы получим следующие результаты :
Обратите внимание, как интересно распределились объемы поставок по магазинам, при этом не превышая вместимость наших складов и удовлетворяя все заявки на необходимое количество товаров для каждого магазина.
Если найденное решение нас устраивает, то мы можем его сохранить, либо откатиться к исходным значениям и повторить попытку с другими параметрами. Вы также можете сохранить выбранную комбинацию параметров как Сценарий. По желанию пользователя Excel может строить три типа. Отчеты по решаемой задаче на отдельных листах: отчет о результатах, отчет о математической устойчивости решения и отчет о пределах (ограничениях) решения, однако в большинстве случаев они интересны только специалистам. .
Однако бывают ситуации, когда Excel не может найти подходящее решение. Смоделировать такой случай можно, если указать в нашем примере потребности магазинов в сумме, превышающей общую вместимость складов. Тогда при выполнении оптимизации Excel попытается максимально приблизиться к решению, а затем выведет сообщение о том, что решение невозможно найти. Тем не менее, даже в этом случае у нас есть много полезной информации – в частности, мы можем увидеть «слабые звенья» наших бизнес-процессов и понять, где их можно улучшить.
Рассмотренный пример, конечно, относительно прост, но легко масштабируется для решения гораздо более сложных задач. Например:
- Оптимизация распределения финансовых ресурсов по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями в данном случае будут объем финансирования и сроки реализации проекта, а целью оптимизации является максимизация прибыли и минимизация затрат проекта.
- Оптимизация графика работы сотрудников с целью минимизации фонда оплаты труда предприятия. Ограничениями в данном случае будут пожелания каждого сотрудника по графику приема на работу и требованиям штатного расписания.
- Оптимизация инвестиционных вложений – необходимость правильного распределения средств между несколькими банками, ценными бумагами или акциями предприятий, чтобы опять же максимизировать прибыль или (что важнее) минимизировать риски.
В любом случае доп. Поисковые решения (Решатель) – очень мощный и красивый инструмент Excel, достойный вашего внимания, так как он может выручить во многих сложных ситуациях, с которыми вам приходится сталкиваться в современном бизнесе.