Загрузить решатель Надстройка | сформулирует модель | пробы и ошибки | Решить модель

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

Загрузите Solver Добавить в

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

  1. На вкладке Файл выберите команду Параметры.

  2. В надстроек, выберите Solver надстройку и нажмите на кнопку Go.

Click Solver Add-in

  1. Проверьте Solver надстройку и нажмите кнопку OK.

Check Solver Add-in

  1. Вы можете найти Solver на вкладке Данные в группе Анализ.

Click Solver

Сформулируйте модель

Модель, которую мы будем решать выглядит следующим образом в Excel.

Formulate the Model

  1. Для того чтобы сформулировать эту линейную модель программирования, ответьте на следующие три вопроса.

\ А. Какие решения должны быть сделаны? Для решения этой проблемы нам нужно Excel, чтобы узнать, сколько на заказ каждого изделия (велосипеды, мопеды и детские сиденья).

\ Б. Каковы ограничения на эти решения? В сдерживает здесь, что количество капитала и хранения используются продукты, не может превышать ограниченное количество капитала и хранения (ресурсов).

Например, каждый велосипед использует 300 единиц капитала и 0,5 единицы хранения.

\ С. Какова общая мера производительности этих решений? Общая мера работы является общей прибылью из трех продуктов, так что цель состоит в том, чтобы максимально увеличить это количество.

  1. Для того, чтобы сделать модель легче понять, назвать следующие диапазоны.

Range Name

Cells

UnitProfit

C4:E4

OrderSize

C12:E12

ResourcesUsed

G7:G8

ResourcesAvailable

I7:I8

TotalProfit

I12

  1. Вставьте следующие три функции SUMPRODUCT.

Sumproduct Functions

Объяснение: Сумма капитала используется равно SUMPRODUCT диапазона C7: E7 и OrderSize. Объем памяти, используемой равно SUMPRODUCT диапазона C8: E8 и OrderSize. Общая прибыль равна SUMPRODUCT из UnitProfit и OrderSize.

Метод проб и ошибок

При такой постановке становится легко проанализировать любое пробное решение.

Например, если мы заказываем 20 велосипедов, 40 мопедов и 100 детских кресел, общий объем используемых ресурсов не превышает количество имеющихся ресурсов. Это решение имеет общую прибыль 19000.

Trial Solution

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

Решить Модель

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

  1. На вкладке Данные в группе Анализ выберите пункт Поиск решения.

Click Solver

Введите параметры решателя (читайте дальше). Результат должен соответствовать картинке ниже.

Solver Parameters

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

  1. Введите TotalProfit для этой цели.

  2. Нажмите кнопку Макс.

  3. Введите OrderSize для меняющаяся переменная клеток.

  4. Нажмите кнопку Добавить, чтобы ввести следующее ограничение.

Add Constraint

  1. Проверка «Make Неограниченные переменные неотрицательные» и выберите «Simplex LP».

  2. Наконец, нажмите кнопку Выполнить.

Результат:

Solver Results

Оптимальное решение:

Optimal Solution

Вывод: Оптимально порядка 94 велосипедов и 54 мопедов. Это решение дает максимальную прибыль 25600. Это решение использует все доступные ресурсы.