Загрузите надстройку Solver | Сформулируйте модель | Метод проб и ошибок | Решите модель
Excel включает инструмент под названием решатель который использует методы исследования операций, чтобы найти оптимальные решения для всех видов проблем, связанных с принятием решений.
Загрузите надстройку Solver
Чтобы загрузить надстройку решателя, выполните следующие действия.
1. На вкладке Файл щелкните Параметры.
2. В разделе «Надстройки» выберите «Надстройка решателя» и нажмите кнопку «Перейти».
3. Установите флажок «Надстройка решателя» и нажмите «ОК».
4. Вы можете найти Решатель на вкладке «Данные» в группе «Анализ».
Сформулируйте модель
В модель мы собираемся решить в Excel выглядит следующим образом.
1. Чтобы сформулировать эту модель линейного программирования, ответьте на следующие три вопроса.
а. Какие решения нужно принимать? Для этой задачи нам понадобится Excel, чтобы узнать, сколько нужно заказать каждого продукта (велосипеды, мопеды и детские сиденья).
б. Что сдерживает эти решения? Ограничения здесь заключаются в том, что объем капитала и хранилища, используемых продуктами, не может превышать ограниченный объем доступного капитала и хранилища (ресурсов). Например, на каждый велосипед используется 300 единиц капитала и 0,5 единицы хранения.
c. Каков общий показатель эффективности этих решений? Общий показатель эффективности - это общая прибыль от трех продуктов, поэтому цель состоит в том, чтобы максимизировать это количество.
2. Чтобы облегчить понимание модели, создайте следующие именованные диапазоны.
| Название диапазона | Клетки |
|---|---|
| UnitProfit | C4: E4 |
| Размер заказа | C12: E12 |
| Ресурсы | G7: G8 |
| РесурсыДоступны | I7: I8 |
| Общая прибыль | I12 |
3. Вставьте следующие три функции СУММПРОИЗВ.
Объяснение: Сумма используемого капитала равна сумме из диапазона C7: E7 и OrderSize. Объем используемой памяти равен сумме из диапазона C8: E8 и OrderSize. Общая прибыль равна произведению UnitProfit и OrderSize.
Методом проб и ошибок
С такой формулировкой становится легко анализировать любое пробное решение.
Например, если мы заказываем 20 велосипедов, 40 мопедов и 100 детских сидений, общий объем используемых ресурсов не превышает объем доступных ресурсов. Это решение имеет общую прибыль 19000.
Необязательно использовать метод проб и ошибок. Далее мы опишем, как можно использовать Excel Solver для быстрого поиска оптимального решения.
Решите модель
Чтобы найти Оптимальным решениемвыполните следующие шаги.
1. На вкладке «Данные» в группе «Анализировать» щелкните Решатель.
Введите параметры решателя (читайте дальше). Результат должен соответствовать изображенному ниже.
У вас есть выбор: ввести имена диапазонов или щелкнуть ячейки в электронной таблице.
2. Введите TotalProfit для цели.
3. Щелкните Макс.
4. Введите OrderSize для изменяющихся ячеек переменных.
5. Щелкните Добавить, чтобы ввести следующее ограничение.
6. Отметьте «Сделать неограниченные переменные неотрицательными» и выберите «Simplex LP».
7. Наконец, нажмите «Решить».
Результат:
Оптимальное решение:
Вывод: оптимально заказать 94 велосипеда и 54 мопеда. Это решение дает максимальную прибыль 25600. Это решение использует все доступные ресурсы.
