Виявлення й вирішення проблем за допомогою надбудови "Пошук розв’язання"

Приклад аналізу за допомогою надбудови "Пошук розв’язання"

У цьому прикладі рівень реклами в кожному кварталі впливає на кількість проданих одиниць товару, опосередковано визначаючи обсяг збуту, пов’язані з цим витрати та прибуток. За допомогою надбудови "Пошук розв'язання" можна змінити квартальні бюджети на рекламу (клітинки змінних рішень B5:C5), аж до загального обмеження бюджету в розмірі 20 000 грн (клітинка F5), доки загальний прибуток (клітинка цільової функції F7) не досягне максимально можливого рівня. Значення в клітинках змінних використовуються для обчислення прибутку в кожному кварталі, тому вони пов'язані з формулою =SUM (Прибуток_кв.1:Прибуток_кв.2) у клітинці цільової функції F7.

1. Клітинки змінних

2. Клітинка з обмеженням

3. Клітинка цільової функції

Такий вигляд матимуть нові значення після запуску надбудови "Пошук розв’язання":

Визначення й вирішення задач

1. На вкладці "Дані" в групі "Аналіз" виберіть "Розв'язувач".
   

   Примітка.

   Якщо команда «Пошук розв'язання » або група «Аналіз » недоступні, потрібно активувати надбудову «Пошук розв'язання». Докладні відомості див. в статті " Активування надбудови "Пошук розв'язання".

   

2. У полі Установлення цільової функції введіть посилання на клітинку або ім'я для неї. Клітинка цільової функції має містити формулу.

3. Виконайте одну з наведених нижче дій.

   • Якщо потрібно встановити найбільше значення клітинки цільової функції, виберіть "Макс".
   • Якщо потрібно встановити найменше значення клітинки цільової функції, виберіть " Мінімум".
   • Якщо у клітинці цільової функції потрібно встановити певне значення, виберіть " Value of" (Значення), а потім введіть значення в поле.
   • У поле By Changing Variable Cells (Змінюючи клітинки змінних) введіть ім’я або посилання для кожного діапазону клітинок змінних рішень. Несуміжні посилання слід розділяти крапкою з комою. Клітинки змінних мають бути безпосередньо або опосередковано пов’язані з клітинкою цільової функції. Ви можете вказати до 200 клітинок змінних.

4. У полі "Subject to the Constraints" (Підлягає обмеженням) введіть потрібні обмеження, виконавши наведені нижче дії.

   1. У діалоговому вікні "Параметри розв'язувача " натисніть кнопку "Додати".

   2. У полі Посилання на клітинку введіть посилання на клітинку або ім’я діапазону клітинок, для якого потрібно обмежити значення.

   3. Виберіть зв'язок (<=,=, >=, int, bin, dif ) між указаною клітинкою та обмеженням. Якщо вибрано ціле число, у полі "Обмеження" відобразиться ціле число. Якщо вибрати bin, у полі Constraint (Обмеження) відобразиться двійковий код. Якщо вибрати значення dif, у полі Constraint з'явиться alldifferent.

   4. Якщо ви вибрали <для зв'язку в полі "Обмеження" значення =, = або >=, введіть число, посилання на клітинку, ім'я клітинки або формулу.

   5. Виконайте одну з наведених нижче дій.

      • Щоб прийняти обмеження та додати ще одне, натисніть кнопку "Додати".

      • Щоб прийняти обмеження та повернутися до діалогового вікна "Параметри розв'язувача ", натисніть кнопку OK.

        Примітка.

        Зв'язки int (ціл),bin (двійк) і dif (різ) можна застосовувати лише в обмеженнях у клітинках змінних рішень.

   6. Щоб змінити або видалити наявне обмеження, виконайте наведені нижче дії.

      • У діалоговому вікні " Параметри розв'язувача " виберіть обмеження, яке потрібно змінити або видалити.
      • Натисніть кнопку "Змінити", а потім внесіть зміни або виберіть "Видалити".

5. Натисніть кнопку "Розв'язати" та виконайте одну з наведених нижче дій.

   • Щоб зберігати значення розв'язання на аркуші, у діалоговому вікні " Результати розв'язувача " виберіть " Зберегти розв'язання розв'язувача".
   • Щоб відновити вихідні значення, перш ніж натиснути кнопку "Розв'язання", виберіть " Відновити первинні значення".
   • Процес пошуку розв'язання можна перервати, натиснувши клавішу Esc. Програма Excel заново обчислить аркуш з останніми значеннями, знайденими у клітинках змінних рішення.
   • Щоб створити звіт на основі власного розв'язання після того, як надбудова знайде своє, виберіть тип звіту в полі "Звіти" та натисніть кнопку "OK". Звіт буде створено на новому аркуші книги. Якщо розв’язання не знайдено, не буде доступних звітів або будуть доступні лише деякі з них.
   • Щоб зберегти значення клітинок змінних рішень як сценарій, до якого можна повернутися пізніше, натисніть кнопку "Зберегти сценарій " у діалоговому вікні " Результати розв'язувача", а потім введіть ім'я сценарію в полі " Ім'я сценарію ".

Робота з пробними розв’язаннями, отриманими за допомогою надбудови "Пошук розв’язання"

1. Визначивши задачу, натисніть кнопку "Параметри " в діалоговому вікні " Параметри розв'язувача ".

2. У діалоговому вікні "Параметри " встановіть прапорець "Відображати результати ітерацій", щоб переглядати значення для кожного пробного розв'язання, а потім натисніть кнопку OK.

3. У діалоговому вікні « Параметри розв'язувача » натисніть кнопку «Розв'язати».

4. У діалоговому вікні « Демонстрація пробного розв'язання » виконайте одну з наведених нижче дій.

   • Щоб зупинити процес розв'язання та відобразити діалогове вікно "Результати розв'язувача ", натисніть кнопку "Зупинити".
   • Щоб продовжити процес розв'язання та відобразити наступне пробне розв'язання, натисніть кнопку "Продовжити".

Змінення способу пошуку розв’язань надбудови "Пошук розв’язання"

1. У діалоговому вікні « Параметри розв'язувача » натисніть кнопку «Параметри».
2. У діалоговому вікні «Параметри» виберіть або введіть значення для параметрів на вкладках Усі методи, За методом зведеного градієнта та Розвиваний розв’язувач.

Збереження та завантаження моделі задачі

1. У діалоговому вікні « Параметри розв'язувача » натисніть кнопку «Завантажити/зберегти».

2. Введіть діапазон клітинок для області моделі та виберіть " Зберегти" або "Завантажити".
   Під час збереження моделі введіть посилання на першу клітинку вертикального діапазону пустих клітинок, у яких потрібно розташувати модель задачі. Під час завантаження моделі введіть посилання для всього діапазону клітинок, який містить модель задачі.

   Порада.

   Останні вибрані параметри в діалоговому вікні Параметри розв’язувача можна зберегти разом з аркушем, зберігши книгу. Кожен аркуш у книзі може містити власні параметри надбудови "Пошук розв'язання", і всі вони зберігаються. Крім того, ви можете визначити кілька задач для аркуша, натиснувши кнопку "Завантажити/зберегти ", щоб зберегти кожну задачу окремо.

Методи розв’язання, які використовуються в надбудові "Пошук розв’язання"

У діалоговому вікні " Параметри розв'язувача " можна вибрати будь-який із трьох алгоритмів або методів розв'язання.

• Узагальнений зведений градієнт (GRG) нелінійний: Використовується для гладких нелінійних задач.
• Симплекс-метод: Використовується для лінійних задач.
• Еволюційні: Використовується для розв'язання негладких задач.

Приклад аналізу за допомогою надбудови "Пошук розв’язання"

У цьому прикладі рівень реклами в кожному кварталі впливає на кількість проданих одиниць товару, опосередковано визначаючи обсяг збуту, пов’язані з цим витрати та прибуток. За допомогою надбудови "Пошук розв’язання" можна змінити квартальні бюджети на рекламу (клітинки змінних рішень B5:C5), аж до загального обмеження бюджету в розмірі 20 000 грн (клітинка D5), доки загальний прибуток (клітинка цільової функції D7) не досягне максимально можливого рівня. Значення в клітинках змінних використовуються для обчислення прибутку в кожному кварталі, тому вони пов'язані з формулою =SUM (Прибуток_кв.1:Прибуток_кв.2) у клітинці цільової функції D7.

Клітинки змінних

Клітинка з обмеженням

Клітинка цільової функції

Такий вигляд матимуть нові значення після запуску надбудови "Пошук розв’язання":

Визначення й вирішення задач

1. Виберіть>Data Solver.

   

2. У полі "Установити цільову функцію" введіть посилання на клітинку або ім'я для неї.

   Примітка.

   Клітинка цільової функції має містити формулу.

3. Виконайте одну з наведених нижче дій.

   Дія	Зробіть це
   Установлення найбільшого значення в клітинці цільової функції	Виберіть "Макс.
   Установлення найменшого значення в клітинці цільової функції	Виберіть мін.
   Установлення певного значення в клітинці цільової функції	Виберіть поле "Value Of" (Значення ), а потім введіть його в поле.

4. У поле By Changing Variable Cells (Змінюючи клітинки змінних) введіть ім’я або посилання для кожного діапазону клітинок змінних рішень. Несуміжні посилання слід розділяти крапкою з комою.
   Клітинки змінних мають бути безпосередньо або опосередковано пов’язані з клітинкою цільової функції. Ви можете вказати до 200 клітинок змінних.

5. У поле Subject to the Constraints (Підлягає обмеженням) введіть потрібні обмеження.
   Щоб додати обмеження, виконайте такі дії:

   1. У діалоговому вікні "Параметри розв'язувача " натисніть кнопку "Додати".

   2. У полі Посилання на клітинку введіть посилання на клітинку або ім’я діапазону клітинок, для якого потрібно обмежити значення.

   3. У спливаючому меню "= зв'язок" виберіть потрібний зв'язок між указаною клітинкою та обмеженням.< Якщо ви вибрали <=, = або >=, у полі " Обмеження " введіть число, посилання на клітинку, ім'я клітинки або формулу.

      Примітка.

      Зв'язки int ( ціл),bin (двійк) і dif (різ) можна застосовувати лише в обмеженнях у клітинках змінних рішень.

   4. Виконайте одну з наведених нижче дій.

   Дія	Зробіть це
   Прийняття обмеження та додавання іншого	Натисніть Додати.
   Прийняття обмеження та повернення до діалогового вікна Solver Parameters (Параметри розв’язувача)	Натисніть кнопку OK.

6. Натисніть кнопку "Розв'язати" та виконайте одну з наведених нижче дій.

   Дія	Зробіть це
   Збереження значень розв’язання на аркуші	У діалоговому вікні «Solver Results» (Результати розв'язувача) виберіть Keep Solver Solution (Зберегти розв'язання розв'язувача).
   Відновлення первинних даних	Виберіть "Відновити первинні значення".

Робота з пробними розв’язаннями, отриманими за допомогою надбудови "Пошук розв’язання"

1. Виберіть>Data Solver.

   

2. Визначивши задачу, у діалоговому вікні " Параметри розв'язувача " виберіть " Параметри".

3. Установіть прапорець Show Iteration Results (Відображати результати ітерацій), щоб переглядати значення для кожного пробного розв'язання, а потім натисніть кнопку OK.

4. У діалоговому вікні « Параметри розв'язувача » натисніть кнопку «Розв'язати».

5. У діалоговому вікні « Демонстрація пробного розв'язання » виконайте одну з наведених нижче дій.

   Дія	Зробіть це
   Припинення розв’язання й відображення діалогового вікна Solver Results (Результати розв’язувача)	Натисніть кнопку "Зупинити".
   Продовження розв’язання й відображення наступного пробного розв’язання	Натисніть кнопку "Продовжити".

Змінення способу пошуку розв’язань надбудови "Пошук розв’язання"

1. Виберіть>Data Solver.

   

2. Виберіть елемент "Параметри", а потім у діалоговому вікні " Параметри" або " Параметри розв'язувача " виберіть один або кілька з таких параметрів:

   Дія	Зробіть це
   Установлення тривалості розв’язання та кількості ітерацій	На вкладці All Methods (Усі методи) у розділі Solving Limits (Ліміти розв’язання в поле Max Time (Seconds) (Максимальний час [Секунди]) введіть потрібну кількість секунд для пошуку розв’язання. Потім у поле Iterations (Ітерації) введіть максимальну дозволену кількість ітерацій. Примітка. Якщо тривалість пошуку розв'язання або кількість ітерацій досягне максимально припустимого значення, відобразиться діалогове вікно « Показати пробне розв'язання ».
   Установлення ступеня точності	На вкладці All Methods (Усі методи) у поле Constraint Precision (Точність обмеження) введіть потрібний ступінь точності. Що менше число, то вища точність.
   Установлення ступеня конвергенції	На вкладці GRG Nonlinear (За методом зведеного градієнта) або Evolutionary (Розвиваний розв’язувач) у поле Convergence (Конвергенція) введіть значення відносної зміни, яку потрібно дозволити в останніх п’яти ітераціях, перш ніж надбудова "Пошук розв’язання" припинить роботу й знайде розв’язання. Що менше число, то менша відносна зміна дозволена.

3. Натисніть кнопку OK.

4. У діалоговому вікні " Параметри розв'язувача " натисніть кнопку "Розв'язати " або "Закрити".

Збереження та завантаження моделі задачі

1. Виберіть>Data Solver.

   

2. Виберіть Load/Save, введіть діапазон клітинок для області моделі, а потім виберіть Save (Зберегти ) або Load.
   Під час збереження моделі введіть посилання на першу клітинку вертикального діапазону пустих клітинок, у яких потрібно розташувати модель задачі. Під час завантаження моделі введіть посилання для всього діапазону клітинок, який містить модель задачі.

   Порада.

   Останні вибрані параметри в діалоговому вікні Solver Parameters (Параметри розв’язувача) можна зберегти разом з аркушем, зберігши книгу. Кожен аркуш у книзі може містити власні параметри надбудови "Пошук розв’язання", і всі вони зберігаються. Крім того, ви можете визначити кілька задач для аркуша, натиснувши кнопку "Завантажити/зберегти ", щоб зберегти кожну задачу окремо.

Методи розв’язання, які використовуються в надбудові "Пошук розв’язання"

1. Виберіть>Data Solver.

   

2. У спливаючому меню Select a Solving Method (Виберіть метод розв’язання) виберіть один із таких параметрів:

Оскільки надбудови не підтримуються в вебпрограма Excel, за допомогою надбудови "Пошук розв'язання" виконати аналіз "what-if" для даних і знайти оптимальні розв'язання, не можна.

Маючи програму Excel для настільних комп'ютерів, можна скористатися кнопкою " Відкрити у програмі Excel ", щоб відкрити книгу та скористатися надбудовою "Пошук розв'язання".