✅ Аналіз даних - використання розв’язувача в Excel

Зміст

Це п’ята, остання стаття із п’яти частин серії “Аналіз даних у Excel”. У цьому розділі я покажу вам, як використовувати Solver в Excel.

Інші статті цієї серії:

Одна таблиця змінних даних у Excel.
Дві таблиці змінних даних у Excel.
Менеджер сценаріїв в Excel.
Пошук цілей в Excel.

Перегляд відео - Використання розв’язувача в Excel

Solver in Excel-це надбудова, яка дозволяє отримати оптимальне рішення, коли існує багато змінних та обмежень. Ви можете вважати це розширеною версією пошуку цілей.

Як знайти додаток Solver у Excel

Надбудова Solver відключена в Excel за замовчуванням. Нижче описано, як це зробити.

Нижче описано, як це зробити.

Перейдіть до Файл -> Параметри.
У діалоговому вікні "Параметри Excel" на лівій панелі виберіть "Надбудова".
На правій панелі внизу виберіть зі спадного меню надбудови Excel і натисніть кнопку Перейти…
У діалоговому вікні Надбудови ви побачите список доступних надбудов. Виберіть надбудову Solver і натисніть OK.
Це дозволить надбудову Solver. Тепер він буде доступний на вкладці Дані в групі Аналіз.

Використання Solver в Excel - Приклад

Solver дає бажаний результат, коли ви згадуєте залежні змінні та умови/обмеження.

Наприклад, припустимо, що у мене є набір даних, як показано нижче.

У цьому прикладі є дані про виробництво для 3 віджетів - кількості, ціни за віджет та загального прибутку.

Об'єктивно: Щоб отримати максимальний прибуток.

Якщо у вас є уявлення про виробництво, ви б знали, що вам потрібно оптимізувати виробництво, щоб отримати найкращий результат. Хоча теоретично ви можете виготовляти необмежену кількість віджетів із найвищим прибутком, завжди існує багато обмежень, за яких потрібно оптимізувати виробництво.

Обмеження:

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

Необхідно створити принаймні 100 Кількість віджетів А.
Необхідно створити принаймні 20 Кількість віджета В.
Необхідно створити принаймні 50 Кількість віджетів C.
Всього має бути створено 350 віджетів.

Це типова проблема оптимізації виробництва, і на неї можна легко відповісти за допомогою Solver у Excel.

Етапи використання розв’язувача в Excel

Після активації надбудови розв’язувача (як пояснювалося вище у цій статті), перейдіть до Дані -> Аналіз -> Розв’язач.
У діалоговому вікні Параметр розв’язувача використовуйте наступне:
1. Встановити ціль: $ D $ 5 (це клітина, яка має бажане значення - в даному випадку це загальний прибуток).
2. Кому: Макс (оскільки ми хочемо максимального прибутку).
3. Змінюючи змінні клітинки: $ B $ 2: $ B $ 4 (змінні, які ми хочемо оптимізувати - у цьому випадку це кількість).
4. З урахуванням обмежень:
  - Тут потрібно вказати обмеження. Щоб додати обмеження, натисніть Додати. У діалоговому вікні Додати обмеження вкажіть посилання на комірку, умову та значення обмеження (як показано нижче):
  - Повторіть цей процес для всіх обмежень.
5. Виберіть метод вирішення: Виберіть Simplex LP.
6. Натисніть Вирішити
  - Якщо вирішувач знайде рішення, це відкриє діалогове вікно Результат вирішення. Ви можете залишити рішення розв’язувача (яке ви бачите у наборі даних) або повернутися до початкових значень.
    - Ви також можете зберегти це як один із сценаріїв, які можна використовувати в Менеджері сценаріїв.
    - Поряд з цим, ви також можете створювати звіти: відповідь, чутливість та обмеження. Просто виберіть його та натисніть OK. Це створить різні вкладки з детальною інформацією по одній для відповідей, чутливості та обмежень (якщо вибрати лише одну або дві, то буде створено стільки вкладок).

У цій статті я спробував познайомити вас із Solver. Можна зробити набагато більше, і якщо ви розбираєтеся у статистиці, я б рекомендував вам зайти і почитати про це докладніше. Ось кілька хороших статей, які я міг би знайти в Інтернеті: