Розв’язувач Excel: що це таке і коли його потрібно використовувати

Інструменти для електронних таблиць Excel, такі як Goal Seek, які змінюють одну змінну, корисні, але, на жаль, більшість проблем у бізнесі не так просто. Зазвичай ви стикаєтеся з формулами, що містять принаймні дві, а іноді і десятки змінних. Часто проблема матиме більше одного рішення, і вашою задачею буде використання Excel для пошуку оптимального рішення (тобто такого, яке максимізує прибуток, мінімізує витрати чи відповідає іншим критеріям). Для цих більших завдань вам потрібен більш м’язовий інструмент. У Excel є тільки відповідь: Solver. Excel Solver — це складна програма оптимізації, яка дає змогу знаходити рішення складних проблем, які в іншому випадку вимагали б високорівневого математичного аналізу.

Розуміння Excel Solver

Solver, як і Goal Seek , використовує ітераційний метод для виконання обчислень. Використання ітерації означає, що Excel Solver пробує рішення, аналізує результати, пробує інше рішення тощо. Однак ця циклічна ітерація — це не просто здогадки Solver. Це було б нерозумно. Ні, Excel Solver вивчає, як результати змінюються з кожною новою ітерацією, і за допомогою деяких складних математичних процесів (які, на щастя, відбуваються у фоновому режимі і їх можна ігнорувати), зазвичай може визначити, в якому напрямку слід рухатися до рішення.

Переваги вирішувача Excel

Так, Goal Seek і Solver обидва ітераційні, але це не робить їх рівними. Насправді, Excel Solver має ряд переваг:

• Excel Solver дозволяє вказати кілька регульованих клітинок. Всього можна використовувати до 200 регульованих осередків.
• Excel Solver дає змогу встановлювати обмеження для регульованих осередків. Наприклад, ви можете наказати Solver знайти рішення, яке не тільки максимізує прибуток, але й задовольняє певним умовам, таким як досягнення валової маржі від 20 до 30 відсотків або збереження витрат менше 100 000 доларів США. Ці умови називаються обмеженнями на рішення.
• Excel Solver шукає не тільки бажаний результат («ціль» у Goal Seek), але й оптимальний. Наприклад, пошук оптимального результату може означати, що ви можете знайти максимально або мінімально можливе рішення.
• Для складних проблем Solver може генерувати кілька рішень . Потім ви можете зберегти ці різні рішення в різних сценаріях.

Коли слід використовувати Excel Solver?

Гаразд, давайте почнемо прямо тут: Excel Solver — це потужний інструмент, який не потрібен більшості користувачів Excel. Наприклад, було б надмірно використовувати Solver для обчислення чистого прибутку з урахуванням фіксованих показників доходу та витрат. Однак багато проблем вимагають не менше, ніж підходу Solver. Ці проблеми охоплюють багато різних областей і ситуацій, але всі вони мають такі спільні характеристики:

• Вони мають одну цільову клітинку (також звану цільовою коміркою ), яка містить формулу, яку потрібно максимізувати, мінімізувати або встановити на певне значення. Ця формула може бути таким розрахунком, як загальні транспортні витрати або чистий прибуток.
• Формула цільової клітинки містить посилання на одну або кілька змінних клітинок (також називаються невідомими або змінними клітинками ). Розв’язувач коригує ці клітинки, щоб знайти оптимальне рішення для формули цільової клітинки. Ці змінні клітинки можуть включати такі елементи, як продані одиниці, витрати на доставку або витрати на рекламу.
• За бажанням, існує одна або кілька обмежувальних осередків, які повинні задовольняти певним критеріям. Наприклад, ви можете вимагати, щоб реклама становила менше 10 відсотків загальних витрат або щоб знижка для клієнтів становила від 40 до 60 відсотків.

Наприклад, на зображенні нижче показано модель даних робочого аркуша, яка повністю налаштована для Excel Solver. Модель показує дохід (ціна помножена на продані одиниці) і витрати на два продукти, прибуток, отриманий кожним продуктом, і загальний прибуток. Тут потрібно відповісти на таке питання: скільки одиниць кожного продукту потрібно продати, щоб отримати загальний прибуток 0 доларів США? Це відомо в бізнесі як аналіз беззбитковості.

Мета цієї моделі даних — знайти точку беззбитковості (де загальний прибуток дорівнює 0 дол. США).

Це звучить як просте завдання пошуку цілі, але ця модель має складний аспект: змінні витрати. Як правило, змінні витрати на продукт – це його вартість одиниці, помножена на кількість проданих одиниць. Якщо виробництво продукту А коштує 10 доларів, а ви продаєте 10 000 одиниць, то змінні витрати на цей продукт становлять 100 000 доларів. Однак у реальному світі такі витрати часто змішуються між кількома продуктами. Наприклад, якщо ви проводите спільну рекламну кампанію для двох продуктів, ці витрати покривають обидва продукти. Тому ця модель припускає, що витрати на один продукт пов’язані з одиницями, проданими іншого. Ось, наприклад, формула, яка використовується для розрахунку вартості надувної дошки для дартсу (комірка B8):

=B7 * B4 – C4

Іншими словами, змінні витрати на надувну дошку для дартсу зменшуються на один долар за кожну продану одиницю Dog Polisher. Змінні витрати останнього використовують подібну формулу (у клітинці C8):

=C7 * C4 – B4

Наявність змінних витрат, пов’язаних із кількома продуктами, виводить цю модель даних за межі того, що може зробити Goal Seek, але Solver вирішує цю проблему. Ось спеціальні клітинки в моделі, які буде використовувати Solver:

• Об’єктивна клітина – С14; загальний прибуток і цільове рішення для цієї формули дорівнює 0 (тобто точка беззбитковості).
• Змінними клітинками є B4 і C4, які містять кількість одиниць, проданих для кожного продукту.
• Для обмежень ви можете додати, що обидві клітинки прибутку продукту (B12 і C12) також мають бути 0.

Завантаження надбудови Excel Solver

Надбудова є програмним забезпеченням , яке додає одну або кілька функцій в Excel. Встановлення надбудов надає додаткові функції Excel, які за замовчуванням недоступні на стрічці. Додаткове програмне забезпечення в комплекті входить до складу Excel, але не встановлюється автоматично під час інсталяції Excel . Кілька надбудов поставляються в стандартній комплектації з Excel, включаючи Solver, який дозволяє вирішувати проблеми оптимізації.

Ви встановлюєте комплектні надбудови за допомогою діалогового вікна Параметри Excel; ви можете знайти їх у розділі Надбудови. Після їх встановлення надбудови стають доступними одразу. Зазвичай вони відображаються на вкладці, пов’язаній з їхньою функцією. Наприклад, на вкладці «Дані» з’являється «Розв’язувач».

Щоб завантажити надбудову Excel Solver, потрібно виконати наведені нижче дії.

Виберіть Файл→ Параметри.
З’явиться діалогове вікно Параметри Excel.

Виберіть Надбудови.

У списку Керування виберіть Надбудови Excel, а потім виберіть Перейти.
Excel відображає діалогове вікно Надбудови.

Установіть прапорець Надбудова Solver

Натисніть OK.
Excel додає кнопку «Розв’язувач» до групи «Аналіз» на вкладці «Дані».

Подивіться, щоб дізнатися, як використовувати Excel Solver .