Визначення та вирішення задач за допомогою надбудови «Пошук розв’язання»

Примітка.: Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою.

"Розв’язувач" – це надбудова Microsoft Excel, яка використовується для аналіз "what-if". За її допомогою можна знайти оптимальне (максимальне або мінімальне) значення для формула в одній клітинці (так званій клітинці цільової функції), що обмежується значеннями формул в інших клітинках аркуша. Надбудова "Розв’язувач" працює із групою клітинок (які називаються клітинками змінних рішення або просто клітинками змінних), що використовуються для обчислення формул у цільових функціях і клітинках обмежень. Надбудова регулює значення у клітинках змінних відповідно до меж у клітинках обмежень і виводить потрібний результат у клітинці цільової функції.

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

Примітка.: У версіях надбудови "Пошук розв’язання", старіших за Excel 2007, клітинки цільових функцій називають цільовими клітинками, а клітинки змінних рішень – змінюваними клітинками або клітинками змінних. Надбудова "Пошук розв’язання" для Excel 2010 значно вдосконалена, тому в Excel 2007 вона дещо відрізнятиметься.

Примітка.: 

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

Перед застосуванням надбудови "Пошук розв’язання"

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

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

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

Так виглядатимуть нові значення після запуску надбудови "Розв’язувач".

Після застосування надбудови "Пошук розв’язання"

  1. На вкладці Дані в групі Аналіз виберіть Розв’язувач.
    Зображення стрічки Excel

    Примітка.: Якщо команда Розв’язувач або група Аналіз недоступні, необхідно активувати Розв’язувач надбудова. Див. статтю Активування надбудови "Розв’язувач".

    Діалогове вікно "Параметри розв’язувача" в Excel 2010 і пізніших версіях програми
  2. У полі Оптимізувати цільову функцію введіть посилання на клітинку або назва клітинки цільової функції. Клітинка цільової функції має містити формулу.

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

    • Якщо потрібно встановити найбільше значення клітинки цільової функції, виберіть пункт Максимум.

    • Якщо потрібно встановити найменше значення клітинки цільової функції, виберіть пункт Мінімум.

    • Якщо у клітинці цільової функції потрібно встановити певне значення, виберіть пункт Значення та введіть потрібне значення в полі.

    • У полі Змінюючи клітинки змінних введіть ім’я або посилання для кожного діапазону клітинок змінних. Несуміжні посилання слід розділяти комами. Клітинки змінних мають бути безпосередньо або опосередковано пов’язані з клітинками цільової функції. Можна вказати не більше 200 клітинок змінних.

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

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

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

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

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

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

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

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

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

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

    7. Натисніть кнопку Змінити та внесіть зміни або натисніть кнопку Видалити.

  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 Змінних клітинок

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

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

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

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

  1. В Excel 2016 для Mac: на вкладці Data (Дані) натисніть кнопку Solver (Розв’язувач).

    Пошук розв’язання

    В Excel 2011 для Mac: на вкладці Data (Дані) у групі Analysis (Аналіз) натисніть кнопку Solver (Розв’язувач).

    Вкладка Data (Дані), група Analysis (Аналіз), надбудова Solver (Пошук розв’язання)

  2. У поле Set Objective (Оптимізувати цільову функцію) введіть посилання на клітинку або ім’я клітинки цільової функції.

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

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

    Дія

    Виконайте такі дії

    Установлення найбільшого значення в клітинці цільової функції

    Виберіть параметр Max (Максимум).

    Установлення найменшого значення в клітинці цільової функції

    Виберіть параметр Min (Мінімум).

    Установлення певного значення в клітинці цільової функції

    Виберіть параметр Value Of (Значення), а потім введіть у поле потрібне значення.

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

    Клітинки змінних мають бути безпосередньо або опосередковано пов’язані з клітинкою цільової функції. Ви можете вказати до 200 клітинок змінних.

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

    Щоб додати обмеження, виконайте такі дії:

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

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

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

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

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

    Дія

    Виконайте такі дії

    Прийняття обмеження та додавання іншого

    Натисніть кнопку Add (Додати).

    Прийняття обмеження та повернення до діалогового вікна Solver Parameters (Параметри розв’язувача)

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

  6. Натисніть кнопку Solve (Розв’язати) і виконайте одну з таких дій:

    Дія

    Виконайте такі дії

    Збереження значень розв’язання на аркуші

    У діалоговому вікні Solver Results (Результати розв’язувача) натисніть кнопку Keep Solver Solution (Зберегти розв’язання розв’язувача).

    Відновлення первинних даних

    Натисніть кнопку Restore Original Values (Відновити первинні значення).

Примітки.: 

  1. Пошук розв’язання можна перервати, натиснувши клавішу Esc. Excel заново обчислить аркуш з останніми значеннями в клітинках змінних.

  2. Щоб створити звіт на основі власного розв’язання після того, як надбудова знайде своє, виберіть тип звіту в полі Reports (Звіти) і натисніть кнопку OK. Звіт створюється на новому аркуші книги. Якщо розв’язання не знайдено, створити звіт не можна.

  3. Щоб зберегти значення клітинок змінних як сценарій, до якого можна повернутися пізніше, у діалоговому вікні Solver Results (Результати розв’язувача) натисніть кнопку Save Scenario (Зберегти сценарій) і введіть ім’я сценарію в поле Scenario Name (Назва сценарію).

  1. В Excel 2016 для Mac: на вкладці Data (Дані) натисніть кнопку Solver (Розв’язувач).

    Пошук розв’язання

    В Excel 2011 для Mac: на вкладці Data (Дані) у групі Analysis (Аналіз) натисніть кнопку Solver (Розв’язувач).

    Вкладка Data (Дані), група Analysis (Аналіз), надбудова Solver (Пошук розв’язання)

  2. Визначивши задачу, у діалоговому вікні Solver Parameters (Параметри розв’язувача) натисніть кнопку Options (Параметри).

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

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

  5. У діалоговому вікні Show Trial Solution (Показати пробне розв’язання) виконайте одну з таких дій:

    Дія

    Виконайте такі дії

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

    Натисніть кнопку Stop (Зупинити).

    Продовження розв’язання й відображення наступного пробного розв’язання

    Натисніть кнопку Continue (Продовжити).

  1. В Excel 2016 для Mac: на вкладці Data (Дані) натисніть кнопку Solver (Розв’язувач).

    Пошук розв’язання

    В Excel 2011 для Mac: на вкладці Data (Дані) у групі Analysis (Аналіз) натисніть кнопку Solver (Розв’язувач).

    Вкладка Data (Дані), група Analysis (Аналіз), надбудова Solver (Пошук розв’язання)

  2. Натисніть кнопку Options (Параметри), а потім у діалоговому вікні Options (Параметри) або Solver Options (Параметри розв’язувача) виконайте одну або кілька таких дій:

    Дія

    Виконайте такі дії

    Установлення тривалості розв’язання та кількості ітерацій

    На вкладці All Methods (Усі методи) у розділі Solving Limits (Ліміти розв’язання в поле Max Time (Seconds) (Максимальний час [Секунди]) введіть потрібну кількість секунд для пошуку розв’язання. Потім у поле Iterations (Ітерації) введіть максимальну дозволену кількість ітерацій.

    Примітка.: Якщо тривалість пошуку розв’язання або кількість ітерацій досягне максимально припустимого значення, відобразиться діалогове вікно Show Trial Solution (Показати пробне розв’язання).

    Установлення ступеня точності

    На вкладці All Methods (Усі методи) у поле Constraint Precision (Точність обмеження) введіть потрібний ступінь точності. Що менше число, то вища точність.

    Установлення ступеня конвергенції

    На вкладці GRG Nonlinear (За методом зведеного градієнта) або Evolutionary (Розвиваний розв’язувач) у поле Convergence (Конвергенція) введіть значення відносної зміни, яку потрібно дозволити в останніх п’яти ітераціях, перш ніж надбудова "Пошук розв’язання" припинить роботу й знайде розв’язання. Що менше число, то менша відносна зміна дозволена.

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

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

  1. В Excel 2016 для Mac: на вкладці Data (Дані) натисніть кнопку Solver (Розв’язувач).

    Пошук розв’язання

    В Excel 2011 для Mac: на вкладці Data (Дані) у групі Analysis (Аналіз) натисніть кнопку Solver (Розв’язувач).

    Вкладка Data (Дані), група Analysis (Аналіз), надбудова Solver (Пошук розв’язання)

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

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

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

  1. В Excel 2016 для Mac: на вкладці Data (Дані) натисніть кнопку Solver (Розв’язувач).

    Пошук розв’язання

    В Excel 2011 для Mac: на вкладці Data (Дані) у групі Analysis (Аналіз) натисніть кнопку Solver (Розв’язувач).

    Вкладка Data (Дані), група Analysis (Аналіз), надбудова Solver (Пошук розв’язання)

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

Метод розв’язання

Опис

GRG Nonlinear (За методом зведеного градієнта)

Стандартний параметр для моделей, у яких застосовується більшість функцій Excel, окрім IF, CHOOSE, LOOKUP та інших покрокових функцій.

Simplex LP (За симплекс-методом)

Використовуйте цей метод для завдань лінійного програмування. У моделі мають використовуватися функції SUM і SUMPRODUCT, а також оператори +, - і * у формулах, що залежать від клітинок змінних.

Evolutionary (Розвиваний розв’язувач)

Цей метод, заснований на генетичних алгоритмах, найкраще підходить, коли в моделі застосовуються функції IF, CHOOSE або LOOKUP з аргументами, що залежать від клітинок змінних.

Примітка.: Авторські права на частини програмного коду надбудови "Пошук розв’язання" версій 1990–2010 років належать корпорації Frontline Systems, Inc. Авторські права на версію 1989 року належать корпорації Optimal Methods, Inc.

Додаткова довідка з використання надбудови "Розв’язувач"

Контактні дані для отримання поглибленої довідки стосовно надбудови "Розв’язувач":

Передньому краї Systems, Inc
Поле ЗЗ 4288
Нахил с, н 89450-4288
(775) 831-0300
Веб-сайт: http://www.solver.com
Адреса електронної пошти: info@solver.com
Довідка з пошуку розв'язання на www.solver.com.

Авторські права на частини програмного коду надбудови "Розв’язувач" версій 1990–2009 рр. належать корпорації Frontline Systems, Inc. Авторські права на версію 1989 року належать корпорації Optimal Methods, Inc.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Додаткові відомості

За допомогою надбудови "розв'язувач" для бюджетування капіталу

За допомогою надбудови "розв'язувач" для визначення поєднання оптимальні продукту

Загальні відомості про аналізу what-if

Огляд формул в Excel

Способи уникнення недійсних формул

Виправлення поширених помилок у формулах за допомогою функції перевірки помилок

Сполучення клавіш і функціональні клавіші в Excel для Windows

Сполучення клавіш в Excel 2016 для Mac

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×