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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

"Розв’язувач" – це надбудова 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. Виконайте одну з таких дій:

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

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

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

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

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

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

    • Щоб зберігати значення розв’язання на аркуші, у діалоговому вікні Результати розв’язувача виберіть елемент Зберегти розв’язання розв’язувача.

    • Щоб відновити вихідні значення, натисніть кнопку Відновити первинні значення, перш ніж натиснути кнопку Розв’язати.

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

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

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

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

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

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

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

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

    • Щоб продовжити процес розв’язання та відобразити наступне пробне розв’язання, натисніть кнопку Продовжити.

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

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

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

  2. Введіть діапазон клітинок для області моделі та натисніть кнопку Зберегти або Завантажити.

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

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

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

  • Нелінійний метод узагальненого зведеного градієнта (GRG).    Використовується для гладких нелінійних задач.

  • Симплекс-метод.    Використовується для лінійних задач.

  • Розвиваний розв’язувач.    Використовується для розв’язання негладких задач.

Увага! : Слід вмикати надбудови Пошук розв'язання спочатку. Додаткові відомості про завантаження надбудови Пошук розв'язання.

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

Приклад обчислення надбудови "розв'язувач"

Виноска 1 Змінних клітинок

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

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

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

Веб-застосунок PowerPoint Web App

  1. У програмі Excel 2016 для Mac: послідовно виберіть елементи дані > Пошук розв'язання.

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

    У програмі Excel 2011 для Mac: перейдіть на вкладку дані , у розділі аналізу, натисніть кнопку розв'язувач.

    «Дані», Група «аналіз», надбудови Пошук розв'язання

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

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

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

    Дія

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

    Зробити значення клітинки цільової функції великим

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

    Зробити значення клітинки цільової функції якомога менше

    Виберіть пункт мінімум.

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

    Виберіть Значення за потім введіть значення в полі.

  4. У полі За зміненням змінних клітинок введіть імена або посилання для кожного рішення змінних клітинок діапазону. Несуміжні посилання розділити комами.

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

  5. У полі підлягає обмеженням додайте будь-які обмеження, які потрібно застосувати.

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

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

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

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

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

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

    Дія

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

    Прийняти обмеження та додати ще одне

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

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

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

  6. Натисніть кнопку розв'язатита виконайте одну з таких дій:

    Дія

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

    Зберігати значення розв'язання на аркуші

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

    Відновити вихідні дані

    Натисніть кнопку відновити первинні значення.

Примітки : 

  1. Щоб перервати процес розв'язання, натисніть клавішу ESC. Excel переобчислює аркуш з останнє значення, які знаходяться змінних клітинок.

  2. Щоб створити звіт, який базується на ваше рішення після розв'язання знаходить рішення, можна звіт тип у полі звітів і натисніть кнопку OK. Звіт буде створено на новому аркуші у книзі. Якщо надбудови "розв'язувач" не може знайти вирішення проблеми, можна створити звіт недоступна.

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

  1. У програмі Excel 2016 для Mac: послідовно виберіть елементи дані > Пошук розв'язання.

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

    У програмі Excel 2011 для Mac: перейдіть на вкладку дані , у розділі аналізу, натисніть кнопку розв'язувач.

    «Дані», Група «аналіз», надбудови Пошук розв'язання

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

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

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

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

    Дія

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

    Зупинити процес розв'язання та відобразити діалогове вікно параметри Розв'язувача

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

    Продовжити процес розв'язання та відобразити наступне пробне розв'язання

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

  1. У програмі Excel 2016 для Mac: послідовно виберіть елементи дані > Пошук розв'язання.

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

    У програмі Excel 2011 для Mac: перейдіть на вкладку дані , у розділі аналізу, натисніть кнопку розв'язувач.

    «Дані», Група «аналіз», надбудови Пошук розв'язання

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

    Дія

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

    Настроювання рішення час і кількість ітерацій

    На вкладці Усі методиРозв'язання, обмеження, у вікні Max час (у секундах) у розділі введіть кількість секунд, які потрібно дозволити розв'язання. У полі кількість ітерацій Введіть максимальну кількість ітерацій, які ви хочете дозволити.

    Примітка : Якщо процес розв'язання настала максимальний час або кількість ітерацій, перш ніж надбудови "розв'язувач" буде знайдено рішення, розв'язувач відображаються в діалоговому вікні Показати пробне розв'язання .

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

    На вкладці Усі методи у полі Обмеження точність введіть ступеня точність, які ви хочете. Що менше число, тим вища точність.

    Установлення ступінь збіжності

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

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

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

  1. У програмі Excel 2016 для Mac: послідовно виберіть елементи дані > Пошук розв'язання.

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

    У програмі Excel 2011 для Mac: перейдіть на вкладку дані , у розділі аналізу, натисніть кнопку розв'язувач.

    «Дані», Група «аналіз», надбудови Пошук розв'язання

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

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

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

  1. У програмі Excel 2016 для Mac: послідовно виберіть елементи дані > Пошук розв'язання.

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

    У програмі Excel 2011 для Mac: перейдіть на вкладку дані , у розділі аналізу, натисніть кнопку розв'язувач.

    «Дані», Група «аналіз», надбудови Пошук розв'язання

  2. Виберіть спосіб вирішення спливаючого меню виберіть один із наведених нижче дій.

Спосіб вирішення

Опис

За методом зведеного Градієнта (узагальненого зведеного градієнта) нелінійних задач

Вибір за промовчанням, для моделі за допомогою більшості функцій Excel не якщо, виберіть, ПІДСТАНОВКИ та інші функції "вийти".

СИМПЛЕКС

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

Розвиваний розв’язувач.

Цей метод, на основі генетичних алгоритмів, найкраще використовувати під час моделі використовується, якщо вибрати або ПІДСТАНОВКИ з аргументами, які залежать від клітинок змінних.

Примітка : Пошук розв'язання програмного коду для окремих частин авторського права 1990-2010 від компанії Frontline Systems, Inc. частини авторського права 1989 оптимальний методів, Inc.

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

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

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 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.

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

Див. також

Використання надбудови "Розв’язувач" для складання кошторисів капітальних внесків

Використання надбудови "Розв’язувач" для фінансового планування

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

Аналіз "what-if" за допомогою засобу "Розв’язувач"

Знайомство з аналізом "what-if"

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

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

Виявлення помилок у формулах

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

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

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

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

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

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

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

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

×