Оптимизация с помощью Excel Solver

Solver это программа-надстройка Microsoft Excel, которую можно использовать для оптимизации анализа «что, если».

По словам О'Брайена и Маракаса, optimization analysisпредставляет собой более сложное расширение целенаправленного анализа. Вместо того, чтобы устанавливать конкретное целевое значение для переменной, цель состоит в том, чтобы найти оптимальное значение для одной или нескольких целевых переменных при определенных ограничениях. Затем одна или несколько других переменных изменяются многократно с учетом указанных ограничений, пока вы не обнаружите наилучшие значения для целевых переменных.

В Excel вы можете использовать Solver найти optimal value (максимальное или минимальное, или определенное значение) для формулы в одной ячейке, называемой целевой ячейкой, с учетом определенных ограничений или ограничений для значений других ячеек формулы на листе.

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

Вы можете использовать Solver, чтобы найти оптимальные решения для различных проблем, таких как -

Определение ежемесячного ассортимента продукции для подразделения по производству лекарств, обеспечивающего максимальную прибыльность.
Планирование рабочей силы в организации.
Решение транспортных проблем.
Финансовое планирование и бюджетирование.

Активация надстройки Solver

Прежде чем приступить к поиску решения проблемы с Solver, убедитесь, что Solver Add-in активируется в Excel следующим образом -

Щелкните вкладку ДАННЫЕ на ленте. ВSolver команда должна появиться в группе анализа, как показано ниже.

Если вы не нашли команду Solver, активируйте ее следующим образом:

Щелкните вкладку ФАЙЛ.
Щелкните Параметры на левой панели. Откроется диалоговое окно "Параметры Excel".
Щелкните Надстройки на левой панели.
Выберите надстройки Excel в поле «Управление» и нажмите «Перейти».

Откроется диалоговое окно "Надстройки". ПроверьтеSolver Add-inи нажмите ОК. Теперь вы сможете найти команду Solver на ленте на вкладке DATA.

Методы решения, используемые Solver

Вы можете выбрать один из следующих трех методов решения, которые поддерживает Excel Solver, в зависимости от типа проблемы:

LP Simplex

Используется для линейных задач. АSolver модель является линейной при следующих условиях -

Целевая ячейка вычисляется путем сложения членов формы (изменяющаяся ячейка) * (константа).
Каждое ограничение удовлетворяет требованиям линейной модели. Это означает, что каждое ограничение оценивается путем сложения членов формы (изменяющаяся ячейка) * (константа) и сравнения сумм с константой.

Обобщенный приведенный градиент (GRG), нелинейный

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

Эволюционный

Понимание оценки решателя

Решателю требуются следующие параметры -

Ячейки переменной решения
Ячейки ограничения
Объективные ячейки
Метод решения

Оценка решателя основана на следующем -

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

Определение проблемы

Предположим, вы анализируете прибыль, получаемую компанией, производящей и продающей определенный продукт. Вас просят найти сумму, которую можно потратить на рекламу в следующие два квартала, но не более 20 000. Уровень рекламы в каждом квартале влияет на следующее:

Количество проданных единиц, косвенно определяющее сумму выручки от продаж.
Сопутствующие расходы и
Прибыль.

Вы можете перейти к определению проблемы как -

Найдите стоимость единицы.
Найдите стоимость рекламы за единицу.
Найдите цену за единицу.

Затем установите ячейки для необходимых вычислений, как указано ниже.

Как вы можете заметить, расчеты выполняются для квартала 1 и квартала 2, которые рассматриваются:

Количество единиц, доступных для продажи в 1 квартале - 400, а во 2 квартале - 600 (ячейки - C7 и D7).
Начальные значения рекламного бюджета установлены как 10000 на квартал (ячейки - C8 и D8).
Количество проданных единиц зависит от стоимости рекламы на единицу и, следовательно, составляет бюджет на квартал / рекламу. Стоимость за единицу. Обратите внимание, что мы использовали функцию Min, чтобы убедиться, что нет. единиц, проданных в <= no. единиц в наличии. (Ячейки - C9 и D9).
Выручка рассчитывается как цена за единицу * количество проданных единиц (ячейки - C10 и D10).
Расходы рассчитываются как Стоимость единицы * Количество доступных единиц + Adv. Стоимость за квартал (ячейки - C11 и D12).
Прибыль - это доход - расходы (ячейки C12 и D12).
Общая прибыль - это прибыль в 1-м квартале + прибыль во 2-м квартале (ячейка - D3).

Затем вы можете установить параметры для Solver, как указано ниже -

Как вы можете заметить, параметры Solver следующие:

Ячейка цели - это D3, которая содержит общую прибыль, которую вы хотите максимизировать.
Ячейки переменной решения - это C8 и D8, которые содержат бюджеты для двух кварталов - Quarter1 и Quarter2.
Есть три ячейки ограничений - C14, C15 и C16.
- Ячейка C14, которая содержит общий бюджет, должна установить ограничение 20000 (ячейка D14).
- Ячейка C15, содержащая номер единиц, проданных в квартале 1, - установить ограничение <= no. единиц, доступных в квартале 1 (ячейка D15).
- Ячейка C16, содержащая номер. единиц, проданных во втором квартале, устанавливается ограничение <= no. единиц, доступных в квартале 2 (ячейка D16).

Решение проблемы

Следующим шагом является использование Solver для поиска решения следующим образом:

Step 1- Перейдите в ДАННЫЕ> Анализ> Решатель на ленте. Появится диалоговое окно Solver Parameters.

Step 2 - В поле «Установить цель» выберите ячейку D3.

Step 3 - Выберите Макс.

Step 4 - Выберите диапазон C8: D8 в By Changing Variable Cells коробка.

Step 5 - Затем нажмите кнопку «Добавить», чтобы добавить три ограничения, которые вы определили.

Step 6- Откроется диалоговое окно «Добавить ограничение». Установите ограничение для общего бюджета, как указано ниже, и нажмите «Добавить».

Step 7- Установите ограничение для общего количества. единиц, проданных в квартале 1, как указано ниже, и нажмите «Добавить».

Step 8- Установите ограничение для общего количества. единиц, проданных во втором квартале, как указано ниже, и нажмите ОК.

Появится диалоговое окно Solver Parameters с тремя ограничениями, добавленными в поле –Subject to the Constraints.

Step 9 - В Select a Solving Method в поле выберите Simplex LP.

Step 10- Щелкните кнопку Решить. Появится диалоговое окно Solver Results. ВыбратьKeep Solver Solution и нажмите ОК.

Результаты появятся на вашем листе.

Как вы можете заметить, оптимальным решением, обеспечивающим максимальную общую прибыль с учетом данных ограничений, является следующее:

Общая прибыль - 30000.
Adv. Бюджет на 1 квартал - 8000.
Adv. Бюджет на 2 квартал - 12000.

Пошаговое ознакомление с пробными решениями Solver

Вы можете просмотреть пробные решения Solver, глядя на результаты итераций.

Step 1 - Нажмите кнопку «Параметры» в диалоговом окне «Параметры решателя».

В Options появится диалоговое окно.

Step 2 - Установите флажок «Показать результаты итерации» и нажмите «ОК».

Step 3 - The Solver Parametersпоявится диалоговое окно. НажмитеSolve.

Step 4 - The Show Trial Solution появляется диалоговое окно, отображающее сообщение - Solver paused, current solution values displayed on worksheet.

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

Step 5 - Щелкните Продолжить.

В Show Trial Solutionдиалоговое окно появляется на каждом шаге, и, наконец, после того, как оптимальное решение найдено, появляется диалоговое окно результатов поиска. Ваш рабочий лист обновляется на каждом этапе, наконец, показывая значения результатов.

Сохранение выбора решателя

У вас есть следующие варианты сохранения для проблем, которые вы решаете с помощью Solver:

Вы можете сохранить последние выборы в диалоговом окне «Параметры решателя» на листе, сохранив книгу.
Каждый рабочий лист в книге может иметь свой собственный выбор Решателя, и все они будут сохранены при сохранении книги.
Вы также можете определить более одной проблемы на листе, для каждой из которых будет свой выбор Решателя. В таком случае вы можете загружать и сохранять проблемы индивидуально с помощью команды «Загрузить / сохранить» в диалоговом окне «Параметры решателя».
- Щелкните значок Load/Saveкнопка. Откроется диалоговое окно "Загрузить / сохранить".
- Чтобы сохранить модель проблемы, введите ссылку для первой ячейки вертикального диапазона пустых ячеек, в который вы хотите поместить модель проблемы. Щелкните Сохранить.

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

Чтобы загрузить модель проблемы, введите ссылку для всего диапазона ячеек, содержащих модель проблемы. Затем нажмите кнопку «Загрузить».