Excel Solver: оптимізація результатів, додавання обмежень і збереження рішень у вигляді сценаріїв

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

Оптимізація результатів Excel Solver

Коли Solver знайде рішення, ви можете вибрати «Зберегти рішення рішення» або «Відновити вихідні значення». Якщо вибрати Keep Solver Solution, Excel назавжди змінить аркуш. Ви не можете скасувати зміни.

Коли ваша модель робочого аркуша, готова до вирішення, готова до роботи, ось кроки, яких потрібно виконати, щоб знайти оптимальний результат для вашої моделі за допомогою Solver:

Виберіть Дані → Розв’язувач.
Excel відкриває діалогове вікно Параметри рішення.

У полі Set Objective введіть адресу клітинки цілі вашої моделі.
Зверніть увагу, що якщо ви клацнете клітинку, щоб ввести її, Solver автоматично введе абсолютну адресу комірки (наприклад, $B$14 замість B14). Solver працює нормально в будь-якому випадку.

У групі Кому виберіть опцію:

• Макс.: повертає максимально можливе значення.
• Min: Повертає мінімально можливе значення.
• Значення: введіть число, щоб встановити це число для цільової клітинки.

  Для прикладу моделі було вибрано значення «Значення» і введено 0 у текстове поле.

У полі «Змінюючи клітинки змінних» введіть адреси комірок, які вирішувач має змінити, поки шукає рішення.
У прикладі змінюються клітинки B4 і C4. На наступному зображенні показано завершене діалогове вікно параметрів рішення.

Завершене діалогове вікно параметрів розв’язувача.

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

У будь-якому діалоговому вікні «Показати пробне рішення», що з’явиться, натисніть «Продовжити», щоб перемістити речі.
Після завершення оптимізації Excel відображає діалогове вікно Результати розв’язування.

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

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

Натисніть OK.

Ви можете попросити Solver відобразити один або кілька звітів, які надають вам додаткову інформацію про результати. У діалоговому вікні «Результати рішення» скористайтеся списком «Звіти», щоб вибрати кожен звіт, який потрібно переглянути:

• Відповідь: Відображає інформацію про цільову клітинку моделі, змінні клітинки та обмеження. Для цільової клітинки та клітинок змінної Solver показує вихідні та кінцеві значення.
• Чутливість: спроби показати, наскільки чутливе рішення до змін у формулах моделі. Макет звіту про чутливість залежить від типу моделі, яку ви використовуєте.
• Обмеження: відображає цільову клітинку та її значення, а також клітинки змінних та їх адреси, імена та значення.

Excel Solver може використовувати один із кількох методів вирішення. У діалоговому вікні «Параметри розв’язувача» скористайтеся списком «Вибір методу розв’язування», щоб вибрати один із наступних варіантів:

• Simplex LP: використовуйте, якщо модель робочого аркуша є лінійною. Простіше кажучи, лінійна модель — це модель, в якій змінні не підводяться до будь-яких степенів і не використовується жодна з так званих трансцендентних функцій — таких як SIN і COS.
• GRG Nonlinear: використовуйте, якщо модель вашого робочого аркуша є нелінійною та гладкою. Загалом, гладка модель — це модель, в якій на графіку використаного рівняння немає гострих країв або розривів.
• Еволюційний: використовуйте, якщо модель вашого робочого аркуша є нелінійною та негладкою.

Чи варто турбуватися про щось із цього? Майже ні. За замовчуванням Excel Solver використовує GRG Nonlinear, і це має працювати майже для всього, що ви робите з Solver.

Додавання обмежень до Excel Solver

Реальний світ накладає на формули обмеження та умови. Завод може мати максимальну потужність 10 000 одиниць на день, кількість співробітників у компанії не може бути від’ємним числом, а ваші витрати на рекламу можуть бути обмежені до 10 відсотків загальних витрат.

Аналогічно, припустимо, що ви проводите аналіз беззбитковості двох продуктів. Якщо ви запустите оптимізацію без будь-яких обмежень, Solver може досягти загального прибутку 0, встановивши для одного продукту незначний збиток, а для іншого — незначний прибуток, де збиток і прибуток компенсують один одного. Насправді, якщо ви уважно подивитеся на попереднє зображення, вирішувач зробив саме це. Щоб отримати справжнє рішення беззбитковості, ви можете віддати перевагу бачити обидва значення прибутку від продукту як 0.

Такі обмеження та умови є прикладами того, що Solver називає обмеженнями. Додавання обмежень повідомляє Solver знайти рішення, щоб ці умови не порушувалися.

Ось як запустити Solver з обмеженнями, доданими до оптимізації:

Виберіть Дані → Розв’язувач.
Excel відкриває діалогове вікно Параметри рішення.

Використовуйте поле «Встановити ціль», групу «Кому» та поле «Змінюючи змінні клітинки», щоб налаштувати розв’язувач, як описано вище.

Натисніть Додати.
Excel відображає діалогове вікно Додати обмеження.

У полі Посилання на клітинку введіть адресу комірки, яку потрібно обмежити.
Ви можете ввести адресу або вибрати клітинку на аркуші.

У спадному списку виберіть оператора, який потрібно використовувати.
У більшості випадків ви використовуєте оператор порівняння, наприклад, дорівнює (=) або більше (>). Використовуйте оператор int (цілочисельний), якщо вам потрібно, щоб обмеження, наприклад загальна кількість працівників, було цілим значенням замість реального числа (тобто числом із десятковим компонентом; у вас не може бути 10,5 співробітників!). Використовуйте оператор bin (двійковий), якщо у вас є обмеження, яке має бути TRUE або FALSE (або 1 або 0).

Якщо ви вибрали оператор порівняння на кроці 5, у полі Обмеження введіть значення, за яким потрібно обмежити комірку.
На цьому зображенні показано приклад завершеного діалогового вікна Додати обмеження. У прикладі моделі це обмеження наказує Solver знайти рішення таким чином, щоб прибуток від продукту надувної дошки для дартсу (комірка B12) дорівнював 0.

Завершене діалогове вікно Додати обмеження.

Щоб вказати додаткові обмеження, клацніть «Додати» і за потреби повторіть кроки з 4 по 6.

Для прикладу ви додаєте обмеження, яке вимагає, щоб прибуток продукту Dog Polisher (комірка C12) був дорівнює 0.

Натисніть OK.

Excel повертається до діалогового вікна «Параметри вирішувача» і відображає ваші обмеження у списку «Тема обмежень».

Натисніть Вирішити.

У будь-якому діалоговому вікні «Показати пробне рішення», що з’явиться, натисніть «Продовжити», щоб перемістити речі.
На зображенні нижче показано приклад рішення беззбитковості з доданими обмеженнями. Зверніть увагу, що не тільки для клітинки Загальний прибуток (B14) встановлено значення 0, але й для двох клітинок Прибутку продукту (B12 і C12).

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

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

Натисніть OK.

Ви можете додати не більше 100 обмежень. Крім того, якщо вам потрібно внести зміни до обмеження, перш ніж почати розв’язування, виберіть обмеження у списку «Тема обмежень», натисніть «Змінити», а потім внесіть зміни в діалоговому вікні «Змінити обмеження», що з’явиться. Якщо ви хочете видалити обмеження, яке вам більше не потрібне, виберіть обмеження та натисніть Видалити.

Збережіть рішення Excel Solver як сценарій

Кожного разу, коли у вас є модель електронної таблиці, яка використовує узгоджений набір вхідних значень — відомих як змінні клітинки — у вас є те, що Excel називає сценарієм. У Solver ці клітинки, що змінюються, є його змінними комірками, тому рішення Solver є свого роду сценарієм в Excel . Однак Solver не дає вам простого способу зберегти та повторно запустити конкретне рішення. Щоб вирішити цю проблему, ви можете зберегти рішення як сценарій, який потім можна буде викликати за допомогою функції «Диспетчер сценаріїв» у Excel.

Виконайте такі дії, щоб зберегти рішення Solver як сценарій:

Виберіть Дані → Розв’язувач.
Excel відкриває діалогове вікно Параметри рішення.

Використовуйте поле Set Objective, Group To, By Changing Variable Cells і Subject to the Constraints list, щоб налаштувати Solver, як описано вище.

Натисніть Вирішити.

Щоразу, коли з’являється діалогове вікно «Показати пробне рішення», виберіть «Продовжити».
Після завершення оптимізації Excel відображає діалогове вікно Результати розв’язування.

Натисніть Зберегти сценарій.
Excel відображає діалогове вікно збереження сценарію.

У діалоговому вікні «Назва сценарію» введіть назву сценарію та натисніть «ОК».
Excel повертає вас до діалогового вікна Результати розв’язування.

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

Натисніть OK.