Как использовать Solver в Excel для оптимизации решения?

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

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

Что такое Solver в Excel?

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

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Как включить Solver в Excel?

Solver — это надстройка, доступная в Excel. Теперь давайте рассмотрим, как добавить этот инструмент.

• Если у вас Excel версий: 2010, 2013, 2016 или 2019, вам сначала необходимо перейти на вкладку Файл → ПАРАМЕТРЫ, как показано на рисунке.

Примечание: Если у вас Excel 2007, вам нужно нажать кнопку Microsoft Office → Параметры.

• Это откроет окно параметров Excel. Нажмите на опцию Надстройки, а затем нажмите Перейти.

• Откроется окно надстройки с различными инструментами, которые вы можете выбрать. Отметьте опцию Solver Add-in в списке. Нажмите OK.

В версии Excel 2003 года мы можем добавить Solver из меню Tools, чтобы найти Add-ins. Щелкнув по нему, вы откроете список надстроек, из которого нужно выбрать Solver и нажать OK.

• Теперь вы найдете Решатель в разделе Анализ на вкладке Данные.

Недавно добавленный «Поиск решения» можно найти в меню «Инструменты» версии Excel 2003 года.

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

Как использовать Solver в Excel?

Давайте узнаем, как использовать решатель на примере. Три основных шага, которые необходимо выполнить:

• Сформулируйте модель
• Методом проб и ошибок
• Решить модель

Мы подробно обсудим это вместе с пошаговым руководством.

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

Для демонстрации работы Solver в Excel мы будем использовать следующий набор данных хранилища.

Этот набор данных состоит из столбцов с названиями продуктов, общим количеством единиц, ценой продажи, себестоимостью каждой единицы, прибылью и общим доходом. Ячейка B5 содержит общее количество доступных единиц, а ячейка F5 содержит общий полученный доход.

Теперь давайте рассмотрим постановку задачи.

Постановка задачи

Максимальное количество единиц, которые можно заказать, составляет 150. Прибыль, полученная от продажи каждой единицы, указана в таблице. Нам необходимо максимизировать общую прибыль, полученную от продажи товаров: TV Set, Stereo и Speakers.

Цель

Сколько единиц каждого продукта необходимо, чтобы прибыль была максимальной?

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

Используя эту концепцию, пользователь может легко проанализировать любое пробное решение.

Предположим, мы заказываем 50 единиц телевизоров, 50 единиц стереосистем и 50 единиц колонок, при этом максимальное количество единиц, которые можно заказать, составляет 150, мы получим общий доход в размере 125 000 рупий. Однако, чтобы максимизировать прибыль, давайте продвинемся вперед и введем необходимые данные в решатель.

3. Решить модель

Для решения этой задачи мы используем Solver в Excel. Теперь мы выполним следующие шаги для поиска оптимального решения.

• Нажмите на инструмент Solver, который теперь добавлен в раздел Analyze вкладки Data. Это откроет вкладку Solver Parameters.
• Далее нам необходимо сформулировать модель. Для этого необходимо указать три основных параметра, т.е.

1. 1. Объективная ячейка
   2. Переменные ячейки
   3. Ограничивает

Excel находит оптимальное значение (Минимум, Максимум, Указанное) для формулы, представленной в ячейке Цель. Это делается путем изменения значений в ячейках Переменная в зависимости от ограничений, указанных в ячейках Ограничение.

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

1. Объективные ячейки

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

Здесь цель состоит в том, чтобы найти максимальное значение, поскольку мы максимизируем прибыль. Поэтому мы устанавливаем значение целевой ячейки как ячейку F5 и выбираем опцию MAX.

2. Переменные ячейки

Переменные ячейки состоят из переменных данных, которые изменяются Решателем для достижения цели.

Это ячейки, содержащие переменные данные. Значение этих ячеек необходимо изменить для оптимизации прибыли. В этом примере мы указываем ячейки с числом единиц/продукта как переменные ячейки, которые находятся в диапазоне B2: B4.

3. Ограничения

Ограничения — это ограничения, которые задаются Solver. Эти ограничения аналогичны ограничениям реального мира на общую производительность продукта, рыночные требования и т. д. Решение, предоставляемое Solver, должно удовлетворять этим ограничениям. Чтобы прийти к оптимальному решению, эти условия должны быть выполнены.

В этом примере мы можем указать следующие ограничения:

• Количество единиц для каждого продукта должно быть больше или равно нулю. Следовательно, мы можем установить ограничение как $B$2 >= 0, $B$3 >= 0 и $B$4 >= 0.
• Количество единиц, которые будут проданы, не может превышать общее количество единиц. Таким образом, $B$5 <=150.

Чтобы добавить эти ограничения, нажмите кнопку Добавить.

Далее откроется диалоговое окно «Добавить ограничение». Теперь введите ссылку на ячейку. Укажите требуемое значение ограничения и нажмите «Добавить».

После добавления всех ограничений закройте диалоговое окно «Добавить ограничение».

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Параметры решателя

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

1. В диалоговом окне «Параметры решателя» нажмите кнопку «Параметры».
2. Появится диалоговое окно «Параметры решателя».
3. На вкладке «Все методы» выберите один или несколько вариантов:

1. Точность ограничений

Введите необходимую степень точности в поле «Точность ограничений». Это определяет точность ограничений. Вы можете указать меньшее значение (от 0 до 1), чтобы сократить время, необходимое Solver для возврата решения.

2. Используйте автоматическое масштабирование

Установка этого флажка автоматически масштабирует результаты при решении задачи.

3. Показать результаты итерации

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

4. Игнорировать целочисленные ограничения

Установка этого флажка приведет к игнорированию всех ограничений, указанных для целых чисел.

5. Целочисленная оптимальность (%)

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

6. Максимальное время (секунды)

Это определяет максимальное количество секунд, которое потребуется Решателю для поиска решения.

7. Итерации

Это определяет количество раз, которое Решатель будет пересчитывать задачу при поиске решения.

8. Максимальное количество подзадач

Это определяет максимальное количество подзадач, разрешенных для Решателя.

9. Максимально допустимые решения

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

После изменения необходимых параметров нажмите кнопку «ОК».

Наконец, нажмите кнопку «Решить» в диалоговом окне «Параметры решателя», чтобы найти оптимальное решение.

Excel найдет решение, которое максимизирует прибыль. Откроется диалоговое окно под названием Solver Results, в котором будет написано: «Solver found a solution». Вы можете выбрать любые отчеты, необходимые для создания отчета Solver. Затем выберите опцию «Keep Solver Results» и нажмите «OK».

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

Оптимальным решением будет заказать 100 единиц телевизоров и 50 единиц стереосистем. Это решение дает максимальную прибыль в размере 150 000 рупий.

Будет сгенерирован следующий отчет. В этом отчете указаны исходные и конечные значения ячеек Objective Cell и Variable, а также статус каждого ограничения в оптимальном решении.

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

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

Заключение

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

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

Вы можете еще больше улучшить свои навыки работы с Excel, записавшись на курс бизнес-аналитика, предлагаемый Simplilearn.