✅ Анализ данных - использование решателя в Excel

Содержание

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

Другие статьи из этой серии:

Одна таблица переменных данных в Excel.
Таблица двух переменных данных в Excel.
Менеджер сценариев в Excel.
Поиск цели в Excel.

Посмотреть видео - Использование решателя в Excel

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

Как найти надстройку Solver в Excel

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

Вот шаги, чтобы включить его:

Перейдите в Файл -> Параметры.
В диалоговом окне «Параметры Excel» выберите «Надстройка» на левой панели.
На правой панели внизу выберите Надстройки Excel в раскрывающемся списке и нажмите Перейти…
В диалоговом окне надстроек вы увидите список доступных надстроек. Выберите «Надстройка решателя» и нажмите «ОК».
Это включит надстройку Solver. Теперь он будет доступен на вкладке «Данные» в группе «Анализ».

Использование решателя в Excel - пример

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

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

В этом примере представлены производственные данные для 3 виджетов - количество, цена за виджет и общая прибыль.

Задача: Чтобы получить максимальную прибыль.

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

Ограничения:

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

Должно быть произведено не менее 100 штук виджета A.
Должно быть произведено не менее 20 штук виджета B.
Должно быть произведено не менее 50 штук виджета C.
Всего должно быть сделано 350 виджетов.

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

Шаги по использованию решателя в Excel

После активации надстройки решателя (как описано выше в этой статье) перейдите в раздел «Данные» -> «Анализ» -> «Решатель».
В диалоговом окне Solver Parameter используйте следующее:
1. Задайте цель: $ D $ 5 (это ячейка, которая имеет желаемое значение - в данном случае это общая прибыль).
2. To: Max (так как мы хотим максимальную прибыль).
3. Изменяя ячейки переменных: $ B $ 2: $ B $ 4 (переменные, которые мы хотим оптимизировать - в данном случае это количество).
4. При условии соблюдения ограничений:
  - Здесь нужно указать ограничения. Чтобы добавить ограничение, нажмите «Добавить». В диалоговом окне Добавить ограничение укажите ссылку на ячейку, условие и значение ограничения (как показано ниже):
  - Повторите этот процесс для всех ограничений.
5. Выберите метод решения: выберите Simplex LP.
6. Нажмите Решить
  - Если решатель найдет решение, откроется диалоговое окно «Результат решателя». Вы можете сохранить решение решателя (которое вы можете увидеть в своем наборе данных) или вернуться к исходным значениям.
    - Вы также можете сохранить это как один из сценариев, которые можно использовать в диспетчере сценариев.
    - Наряду с этим вы также можете создавать отчеты: «Ответ», «Чувствительность» и «Пределы». Просто выберите его и нажмите ОК. Это создаст разные вкладки с подробностями, по одной для ответа, чувствительности и пределов (если вы выберете только одну или две, тогда будет создано много вкладок).

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