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

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

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

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

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

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

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

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

1. На вкладці Дані в групі Аналіз натисніть кнопку Пошук розв'язання.
   

   Примітка.

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

   

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

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

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

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

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

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

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

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

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

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

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

        Примітка.

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

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

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

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

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

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

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

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

3. У діалоговому вікні Solver Parameters (Параметри розв'язувача) натисніть кнопку Solve (Розв'язати).

4. У діалоговому вікні Показати ознайомлювальне рішення виконайте одну з наведених нижче дій.

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

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

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

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

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

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

   Порада.

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

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

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

• Нелінійний узагальнений зменшений градієнт (GRG): Використовується для гладких нелінійних проблем.
• Симплекс LP: Використовується для лінійних проблем.
• Еволюційної: Використовується для негладжних проблем.

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

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

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

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

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

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

1. Виберіть Пошук даних>.

   

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

   Примітка.

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

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

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

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

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

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

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

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

      Примітка.

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

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

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

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

   Дія	Зробіть це
   Збереження значень розв’язання на аркуші	У діалоговому вікні Результати розв'язувача натисніть кнопку Зберегти розв'язання розв'язувача.
   Відновлення первинних даних	Виберіть відновити вихідні значення.

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

1. Виберіть Пошук даних>.

   

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

3. Установіть прапорець Відображати результати ітерації , щоб переглянути значення кожного пробного рішення, а потім натисніть кнопку OK.

4. У діалоговому вікні Solver Parameters (Параметри розв'язувача) натисніть кнопку Solve (Розв'язати).

5. У діалоговому вікні Показати ознайомлювальне рішення виконайте одну з наведених нижче дій.

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

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

1. Виберіть Пошук даних>.

   

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

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

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

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

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

1. Виберіть Пошук даних>.

   

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

   Порада.

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

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

1. Виберіть Пошук даних>.

   

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

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

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