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

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

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

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

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

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

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

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

Планирование рабочей силы в организации.

Решение транспортных проблем.

Финансовое планирование и бюджетирование.

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

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

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

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

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

Откроется диалоговое окно «Надстройки». Проверьте Надстройку Solver и нажмите Ok. Теперь вы можете найти команду Solver на ленте под вкладкой DATA.

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

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

LP Simplex

Используется для линейных задач. Модель Солвера является линейной при следующих условиях:

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

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

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

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

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

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

Понимание оценки Солвера

Для Солвера требуются следующие параметры —

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

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

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

Значения в ячейках переменных решения ограничены значениями в ячейках ограничений.

Вычисление значения в целевой ячейке включает значения в ячейках переменных решения.

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

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

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

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

Вы можете приступить к определению проблемы как —

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

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

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

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

Количество единиц, доступных для продажи в квартале 1, составляет 400, а в квартале 2 — 600 (ячейки — C7 и D7).

Начальные значения для рекламного бюджета установлены как 10000 за квартал (ячейки — C8 и D8).

Количество проданных единиц зависит от стоимости рекламы на единицу и, следовательно, является бюджетом на квартал / Adv. Стоимость за единицу. Обратите внимание, что мы использовали функцию Min, чтобы убедиться, что нет. единиц, проданных в <= нет. из доступных единиц. (Клетки — C9 и D9).

Выручка рассчитывается как цена за единицу * Количество проданных единиц (ячейки — C10 и D10).

Расходы рассчитываются как стоимость единицы * Количество доступных единиц + Adv. Стоимость за этот квартал (Клетки — C11 и D12).

Прибыль — это доход — расходы (ячейки C12 и D12).

Общая прибыль — это прибыль за квартал 1 + прибыль за квартал 2 (ячейка — D3).

Далее вы можете установить параметры для Солвера, как указано ниже —

Как вы можете заметить, параметры Солвера —

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

Объективная ячейка — D3, в которой содержится общая прибыль, которую вы хотите максимизировать.

Ячейки с переменными решениями — это C8 и D8, которые содержат бюджеты на два квартала — квартал 1 и квартал 2.

Есть три ячейки ограничения — C14, C15 и C16.

Ячейка C14, которая содержит общий бюджет, должна установить ограничение 20000 (ячейка D14).

Ячейка C15, которая содержит номер единиц, проданных в первом квартале, — установить ограничение <= нет. единиц, доступных в Quarter1 (ячейка D15).

Ячейка C16, которая содержит номер единиц, проданных в Quarter2, это установить ограничение <= нет. единиц, доступных в квартале 2 (ячейка D16).

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

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

Шаг 1 — Перейдите в ДАННЫЕ> Анализ> Решатель на ленте. Откроется диалоговое окно «Параметры решателя».

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

Шаг 3 — Выберите Макс.

Шаг 4 — Выберите диапазон C8: D8 в поле « Изменение переменных ячеек» .

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

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

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

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

Появится диалоговое окно «Параметры решателя» с тремя ограничениями, добавленными в поле «Подчинить ограничениям».

Шаг 9 — В поле « Выбрать метод решения» выберите Simplex LP.

Шаг 10 — Нажмите кнопку Решить. Откроется диалоговое окно «Результаты решателя». Выберите Keep Solver Solution и нажмите ОК.

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

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

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

Пошаговое решение Solver Trial Solutions

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

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

Откроется диалоговое окно « Параметры ».

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

Шаг 3 — Откроется диалоговое окно « Параметры решателя». Нажмите Решить .

Шаг 4 — Появится диалоговое окно « Показать пробное решение », в котором будет отображено сообщение « Солвер остановлен», а текущие значения решения будут отображены на листе .

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

Шаг 5 — Нажмите Продолжить.

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

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

У вас есть следующие варианты сохранения для задач, которые вы решаете с помощью Солвера —

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

Каждый лист в книге может иметь свои собственные варианты Солвера, и все они будут сохранены при сохранении книги.

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

Нажмите кнопку Загрузить / Сохранить . Откроется диалоговое окно загрузки / сохранения.

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

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

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