Надстройка Поиск решения

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

Вы научитесь:

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

Мини-кейс: Проблемы компании "Фасад"

Небольшая компания "Фасад" производит 3 типа дверей: стандартные, полированные и резные. Компания работает "под заказ", поэтому продает всю производимую продукцию.
На производстве работают 10 рабочих в одну смену (8 рабочих часов) 5 дней в неделю, что дает 400 рабочих часов в неделю. Рабочее время поделено между двумя существенно различными технологическими процессами: собственно производством и конечной обработкой дверей. Из 400 рабочих часов в неделю 250 отведены под собственно производство и 150 под конечную обработку. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.

Продукция	Время на производство, мин.	Время на отделку, мин.	Прибыль, у.е.
Стандартные	30	15	45
Полированные	30	30	90
Резные	60	30	120

Сколько дверей различных типов нужно производить, чтобы максимизировать прибыль?

Решение задачи на листе MS Excel.

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

Создадим таблицу и перенесем туда данные задачи.
Добавим столбец "Количество дверей", в ячейках которого будем искать, сколько дверей каждого типа нужно произвести. Выделим ячейки этого столбца желтым цветом. Это переменные нашей задачи.
Добавим строку "Ограничения". В нашей задаче ограничивающим фактором является рабочее время, необходимое для производства и отделки дверей: 250 и 150 рабочих часов соответственно. Обратите внимание, в таблице время дано в минутах, поэтому для корректного расчета необходимо перевести рабочий час в минуту, т.е. значение в рабочих часах умножить на 60 минут
Также нам необходимо вычислить, сколько времени потребуется для производства и отделки искомого количества дверей всех трех типов. Затем мы сравним эти вычисленные значения с заданными ограничениями. Для этого:
- создадим строку "Количество времени".
- вычислим общее время, требующееся на изготовление искомого количества дверей каждого типа. Для этого количество изготовленных дверей каждого типа умножим на время, затраченное на изготовление 1 двери этого типа и полученные результаты сложим.
- В Excel для этих целей используют формулу СУММПРОИЗВ(). В нашем случае эта формула будет выглядеть так:
  =СУММПРОИЗВ(диапазон ячеек столбца "Количество дверей"; диапазон ячеек столбца "Время производства")
- Аналогично найдем, сколько времени потребуется на отделку искомого количества дверей.
Создадим ячейку, в которой будем вычислять прибыль. Выделим ее зеленым цветом и назовем "Полная прибыль". Чтобы вычислить полную прибыль, нужно прибыль от двери каждого типа умножить на количество дверей каждого типа и полученные результаты сложить. Вычислять полную прибыль будем также с помощью формулы СУММПРОИЗВ()

Все перечисленные выше действия проиллюстрированы в анимации ниже:

Мы подробно разобрали этап построения математической модели задачи, так как без работающей модели применить надстройку "Поиск решения" невозможно.

переменные - это количество дверей каждого типа, которое необходимо произвести, чтобы достичь цель;.
цель - получить максимальную прибыль при существующих ограничениях
ограничения - это ресурсы, которые мы можем использовать при производстве дверей. В этой задаче - это рабочее время.

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

Для запуска надстройки "Поиск решения" необходимо щелкнуть по команде Поиск решения на панели "Данные" главного меню MS Excel.
Если вы не видите этой команды на панели, то надстройка не подключена. Подключите её.

здесь.

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

В поле "Оптимизировать целевую функцию" сразу появится адрес ячейки, выделенной в данный момент. Поэтому целесообразно, перед тем как запустить надстройку, выделить целевую ячейку (окрашена зеленым цветом в нашей задаче).
В поле "До" необходимо отметить, какое значение целевой функции вы будете искать:
- максимальное - тогда отметьте "Максимум" - эта опция выбирается изначально по умолчанию,
- минимальное - тогда отметьте "Минимум"
- или равное определенному значению - тогда отметьте "Значение", а в поле рядом укажите нужное значение.
В поле "Изменяя ячейки переменных" необходимо ввести адреса ячеек, содержащих переменные. Эти ячейки мы выделили желтым цветом.
В большое поле "В соответствии с ограничениями" нужно ввести все ограничения, которые есть в задачи. Для ввода ограничений удобно воспользоваться командой "Добавить". В панели "Добавление ограничения" введите ограничения задачи.
После ввода ограничений нажмите "Найти решение", и решение будет найдено или не найдено. Об этом вам сообщит панель "Результаты поиска решения".

Все эти действия я показываю в анимации ниже:

Анимация использования поиска решения для нахождения плана производства.

Итак, мы использовали надстройку "Поиск решения" для нахождения оптимального плана производства и выяснили, какую максимальную прибыль сможем получить в случае реализации этого плана.
Теперь рассмотрим панель надстройки подробнее и обсудим некоторые особенности, которые необходимо учитывать при работе с надстройкой.
При работе с надстройкой необходимо соблюдать ряд правил:

Все поля панели "Параметры поиска решения" должны быть заполнены.
Поле "Оптимизировать целевую функцию" должно содержать формулу.
Число переменных не должно превышать 200.
Число ограничений не может быть больше 100.

Ограничения задачи:

На панели "Параметры поиска решения" вы можете добавлять, изменять и удалять ограничения задачи.
Для изменения введенного ранее ограничения в поле "В соответствии с ограничениями" выделите ограничение, которое вы хотите изменить, и нажмите кнопку "Изменить". Появится панель "Изменение ограничений" с помощью которой внесите нужные изменения. Нажмите клавишу "ОК", и в поле "В соответствии с ограничениями" вы увидите исправленное ограничение.
Для удаления введенного ранее ограничения выделите его и нажмите команду "Удалить". Выделенное ограничение будет удалено.
Для того чтобы очистить все поля панели "Параметры поиска решения" нажмите кнопку "Сбросить".
Также вы можете загрузить уже созданную модель из другой задачи для решения текущей задачи, для этого используйте кнопку "Загрузить/Сохранить". Эта же кнопка используется для сохранения модели текущей задачи, если вы планируете использовать эту модель в дальнейшем.

Остановимся коротко на панели "Параметры", которая вызывается с помощью клавиши "Параметры".

Здесь вы можете уменьшить или увеличить точность оптимизации. Для уменьшения точности оптимизации в ведите в поле "Целочисленная оптимальность (%)" значение больше 1. Единица в этом поле стоит по умолчанию. Для увеличения точности поставьте значение меньше 1, например, 0.1. Но помните, что при увеличении точности может сильно увеличиться и время расчета. Поэтому необходимо соблюдать разумный баланс.
На панели "Параметры" вы можете задать границу решения задачи по времени или по числу итерация. Но я рекомендую вам использовать эти возможности только после обретения некоторого опыта в работе с надстройкой.
Иногда надстройка будет находить нецелочисленные решения даже в том случае, когда вы явно потребовали в ограничениях, чтобы переменные были целыми числами. Если это произошло, то посмотрите, не отмечен ли галочкой пункт "Игнорировать целочисленные решения" на панели "Параметры". Для гарантии нахождения целочисленных решений эту галочку нужно снять.

Большинство описанных выше действий проиллюстрированы на анимации ниже.

Анимация, поясняющая заполнение панели Параметры поиска решений.

Теперь рассмотрим внимательно панель "Результаты поиска решения", которую вы увидите после нажатия клавиши "Найти решение". Вы можете увидеть два варианта этой панели.

Решение найдено.

В этом случае вы получите не только решение, но и отчеты "Результаты", "Устойчивость", "Пределы".
Вы можете выделить любой из этих отчетов или все три, нажать "ОК", и MS Excel добавит выделенные отчеты на новые листы вашу рабочей книги.
Отчет результаты не очень интересен, так как повторяет решение, которое вы и так увидите на вашем рабочем листе с моделью, а вот "Отчет об устойчивости" и "Отчет о пределах" могут быть очень полезными. О том, какую информацию вы можете почерпнуть в этих отчетах, я рассказываю в кейсах в разделе "Лекции" этого сайта.

Решение не найдено.

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

Анимация, выводящая панели с возможным результатом поиска.

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