Полное руководство по использованию Solver в Excel для решения проблем

Microsoft Excel оснащен множеством функций, которые упрощают процесс выполнения вычислений и решения уравнений, тем самым повышая производительность. Одной из таких функций является инструмент Solver, который имеет сходство с функцией Goal Seek.

Solver, обычно используемый для анализа «Что если», позволяет пользователям определять значение конкретной ячейки при множественных ограничениях. Его можно использовать для определения конкретного значения, минимального порога или максимального предела для числа. Хотя он может не решать все проблемы, Solver является бесценным ресурсом для сценариев оптимизации, где поиск наилучшего возможного решения имеет решающее значение.

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

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

Активация Solver в Excel

Чтобы начать использовать Solver, вам сначала нужно включить эту надстройку, так как она не активирована по умолчанию, как функция Goal Seek. К счастью, процесс довольно прост.

Начните с выбора меню «Файл» в верхней части экрана, а затем нажмите «Параметры».

Затем нажмите «Надстройки», расположенные в левой части окна «Параметры».

Теперь выберите «Надстройки Excel» в раскрывающемся меню «Управление» внизу и нажмите «Перейти».

В открывшемся диалоговом окне установите флажок рядом с опцией «Надстройка решателя», чтобы включить ее, затем нажмите «ОК».

Теперь при нажатии на вкладку «Данные» в Excel вы должны увидеть доступный «Поиск решения».

Ключевые компоненты решателя

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

Ячейка цели: эта ячейка содержит формулу, которая представляет цель или задачу задачи, будь то минимизация, максимизация или достижение определенного значения.
Ячейки переменных: Эти ячейки содержат переменные, которые Solver будет корректировать для достижения цели. В Solver можно назначить максимум 200 ячеек переменных.
Ограничения: Ограничения — это параметры, в рамках которых Solver должен работать для достижения желаемого результата. Они определяют условия, которые должны быть выполнены при определении требуемых значений.

Применение решателя

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

В ячейках B3–E3 перечислены различные типы поддонов, которые должна производить компания. Строка непосредственно ниже представляет количество поддонов, которые необходимо изготовить для каждого типа, инициализированное с нуля. Последующая строка детализирует прибыль, связанную с каждым типом поддона. Наша цель — определить, сколько поддонов необходимо изготовить для каждого типа, а общая прибыль отображается в ячейке F5. Ограничениями здесь являются доступные ресурсы, определяющие, сколько поддонов компания может реально произвести.

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

В поле «Изменение ячеек переменной» выберите диапазон B4:E4, перетаскивая мышь или вводя имена ячеек напрямую. Эти ячейки представляют количество поддонов каждого типа и в настоящее время установлены на ноль. Solver скорректирует эти значения во время выполнения.

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

Введите F8:F11 для «Ссылки на ячейку», что соответствует столбцу «Используется», и G8:G11 для столбца «Доступно» в поле Ограничение. Убедитесь, что отношение установлено <=по умолчанию, указывая, что значения в столбце «Используется» должны быть меньше или равны значениям в столбце «Доступно».

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

После заполнения полей в диалоговом окне «Параметры решателя» нажмите кнопку «Решить» и дождитесь, пока Excel выдаст результаты.

После генерации результатов появится диалоговое окно Solver Results, в котором будут показаны новые значения в ячейках B4–E4. Помните, что Solver изменяет ваши данные; если вы предпочитаете вернуться к исходным значениям, вы можете выбрать опцию «Восстановить исходные значения». После принятия решения о сохранении решения или возврате к исходным данным убедитесь, что справа отмечено «Ответ», затем нажмите «ОК», чтобы выйти из диалогового окна.

Если вы решите сохранить новое решение, оно будет отражено в вашей таблице после закрытия диалогового окна Solver. Продукция компании будет включать 23 поддона Tahoe, 15 поддонов Pacific, 39 поддонов Savannah и ни одного поддона Aspen, что будет указано в строке «Поддоны» от B4 до D4. Кроме того, ячейка общей прибыли обновится с нуля до 58 800 долларов США.

Важные соображения

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

Источник