Як користуватися вирішувачем Excel 2019

Хоча команди Excel «Таблиця даних» і « Пошук цілі» чудово працюють для простих задач, які вимагають визначення прямого зв’язку між вхідними параметрами та результатами у формулі, вам потрібно використовувати надбудову «Розв’язувач», якщо ви маєте справу з більш складними проблемами. Наприклад, скористайтеся Solver, щоб знайти найкраще рішення, коли вам потрібно змінити кілька вхідних значень у вашій моделі Excel і вам потрібно накласти обмеження на ці значення та/або вихідне значення.

Надбудова Solver працює шляхом застосування ітераційних методів для пошуку «найкращого» рішення з урахуванням вхідних даних, бажаного рішення та обмежень, які ви накладете. З кожною ітерацією програма застосовує метод проб і помилок (на основі використання лінійних або нелінійних рівнянь і нерівностей), який намагається наблизитися до оптимального рішення.

Використовуючи надбудову Solver, пам’ятайте, що багато проблем, особливо складніші, мають багато рішень. Хоча Solver повертає оптимальне рішення, враховуючи початкові значення, змінні, які можуть змінюватися, і обмеження, які ви визначаєте, це рішення часто не є єдиним можливим і, насправді, може бути не найкращим рішенням для вас. Щоб переконатися, що ви знаходите найкраще рішення, ви можете запустити Solver кілька разів, змінюючи початкові значення кожного разу, коли ви вирішуєте проблему.

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

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

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

Ви можете використовувати надбудову Solver разом із диспетчером сценаріїв, щоб допомогти налаштувати проблему для вирішення або зберегти рішення, щоб ви могли переглянути його пізніше. Змінні клітинки, які ви визначаєте для Менеджера сценаріїв, автоматично підбираються та використовуються Розв’язувачем, коли ви вибираєте цю команду, і навпаки. Крім того, ви можете зберегти рішення задачі розв’язувачем як сценарій (натиснувши кнопку «Зберегти сценарій» у діалоговому вікні «Розв’язувач»), який потім можна буде переглянути за допомогою диспетчера сценаріїв.

Налаштування та визначення проблеми в Excel 2019

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

Майте на увазі, що Solver — це додаткова утиліта. Це означає, що перш ніж ви зможете використовувати його, вам потрібно переконатися, що програма надбудови Solver все ще завантажена, про що свідчить поява кнопки «Розв’язувач» у групі «Аналіз» у кінці вкладки «Дані» на стрічці. Якщо ця кнопка відсутня, ви можете завантажити Solver, відкривши вкладку «Надбудови» діалогового вікна «Параметри Excel» (Alt+FTAA), а потім натиснувши кнопку «Перейти», переконавшись, що надбудови Excel відображаються у спадному меню «Керування». поле зі списком ліворуч від нього. Потім установіть прапорець Надбудова Solver у діалоговому вікні Надбудови, щоб поставити галочку перед тим, як натиснути кнопку OK, щоб закрити діалогове вікно та перезавантажити надбудову.

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

Натисніть командну кнопку «Розв’язувач» у групі «Аналіз» у кінці вкладки «Дані» на стрічці.

Excel відкриває діалогове вікно Параметри рішення.

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

Клацніть цільову клітинку на аркуші або введіть посилання на клітинку чи назву діапазону в текстовому полі «Встановити ціль».

Далі потрібно вибрати параметр Кому. Натисніть кнопку параметра Макс, якщо потрібно, щоб значення цільової клітинки було якомога більшим. Натисніть кнопку параметра Мін, якщо потрібно, щоб значення цільової клітинки було якомога меншим. Натисніть кнопку параметра «Значення», а потім введіть значення у пов’язане текстове поле, якщо ви хочете, щоб значення цільової клітинки досягало певного значення.

Натисніть відповідний параметр кнопки в розділі Кому діалогового вікна. Якщо ви виберете кнопку «Значення», введіть відповідне значення у відповідне текстове поле.

Далі позначте змінні клітинки — тобто ті, які Solver може змінити, щоб досягти вашої мети Equal To.

Клацніть текстове поле Шляхом зміни клітинок змінних, а потім виберіть клітинки, які потрібно змінити, на аркуші або введіть посилання на клітинки чи назву діапазону в текстове поле.

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

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

(Необов’язково) Натисніть кнопку «Додати» праворуч від списку «Тема обмежень» у діалоговому вікні «Параметри рішення».

Ця дія відкриває діалогове вікно Додати обмеження. Визначаючи обмеження, виберіть комірку, значення якої потрібно обмежити, або виберіть комірку на аркуші або введіть посилання на неї в текстовому полі Посилання на клітинку. Потім виберіть співвідношення (=, <=,>= або int для цілого чи bin для двійкового) зі спадного списку праворуч і (якщо ви не вибрали int або bin ) введіть відповідне значення або посилання на клітинку в Текстове поле обмеження.

Щоб продовжити додавати обмеження для інших комірок, які використовуються розв’язувачем, натисніть кнопку «Додати», щоб додати обмеження, і очистіть текстові поля в діалоговому вікні «Додати обмеження». Потім повторіть крок 5, щоб додати нове обмеження. Після того, як ви закінчите визначати обмеження для цільової комірки та змінювати значення в моделі, натисніть кнопку OK, щоб закрити діалогове вікно Додати обмеження та повернутися до діалогового вікна Параметри розв’язувача (у якому тепер перераховані ваші обмеження у списку Тема до обмежень).

(Необов’язково) Зніміть прапорець Зробити змінні без обмежень невід’ємними, якщо ви хочете дозволити від’ємні значення, коли клітинки змінних не підпадають під обмеження.
За замовчуванням надбудова Solver використовує нелінійний метод GRG (Generalized Reduced Gradient) для вирішення моделі, параметри якої ви встановлюєте, відомі як дуже ефективний спосіб вирішення плавних нелінійних задач. Щоб використовувати метод LP Simplex (для лінійного програмування за алгоритмом Simplex) або Evolutionary engine для вирішення негладких задач, вам потрібно виконати крок 7.

(Необов’язково) Виберіть LP Simplex або Evolutionary зі спадного списку Select a Solving Method, щоб використовувати один із цих методів для вирішення негладких задач.

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

Вирішення проблеми за допомогою вирішувача Excel

Коли ви натискаєте кнопку «Вирішити», діалогове вікно «Параметри розв’язувача» зникає, а рядок стану вказує, що розв’язувач встановлює проблему, а потім інформує вас про прогрес у розв’язанні проблеми, показуючи номер проміжного (або пробного) рішення, як вони випробовуються. Щоб перервати процес розв’язання в будь-який момент до того, як Excel обчислить останню ітерацію, натисніть клавішу Esc. Потім Excel відобразить діалогове вікно Показати пробне рішення, інформуючи вас про те, що процес рішення призупинено. Щоб продовжити процес вирішення, натисніть кнопку Продовжити. Щоб припинити процес вирішення, натисніть кнопку Зупинити.

Коли Excel завершить процес розв’язання, з’явиться діалогове вікно Результати розв’язування. Це діалогове вікно інформує вас, чи зміг розв’язувач знайти рішення, враховуючи цільову комірку, зміну клітинок та обмеження, визначені для проблеми. Щоб зберегти зміни, які розв’язувач вносить у вашу модель робочого аркуша, залиште опцію «Зберегти рішення рішення» вибраною та натисніть «ОК», щоб закрити діалогове вікно «Результати рішення». Щоб повернути вихідні значення на робочий аркуш, натисніть кнопку параметра Відновити вихідні значення. Щоб зберегти зміни як сценарій перед відновленням вихідних значень, натисніть кнопку «Зберегти сценарій» та призначте назву поточному сценарію, перш ніж натиснути параметр «Відновити вихідні значення» та кнопку «ОК».

Діалогове вікно Результати розв’язування, яке показує, що Solver знайшов рішення проблеми.

На відміну від команди «Пошук цілі», після натискання кнопки «Зберегти рішення рішення» у діалоговому вікні «Результати розв’язування» ви не можете використовувати командну кнопку «Скасувати» на панелі інструментів швидкого доступу, щоб відновити вихідні значення на аркуші. Якщо ви хочете мати можливість перемикатися між виглядом «до» і «після» вашого робочого аркуша, ви повинні зберегти зміни за допомогою кнопки «Зберегти сценарій», а потім вибрати кнопку параметра «Відновити вихідні значення». Таким чином, ви можете зберегти подання «до» в оригінальному робочому аркуші та використовувати Менеджер сценаріїв для відображення подання «після», створеного Solver.

Зміна параметрів вирішувача Excel

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

Змінення параметрів рішення у діалоговому вікні «Параметри».

Налаштування параметрів розв’язувача Excel 2019

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

Якщо ви використовуєте нелінійний або еволюційний метод GRG (Generalized Reduced Gradient) за замовчуванням, ви можете встановити додаткові параметри Solver за допомогою параметрів на вкладках GRG Nonlinear та Evolutionary діалогового вікна Options. Ці параметри включають зміну параметрів «Зближення», «Розмір популяції» та «Випадкове заповнення» для будь-якого з цих методів.

Збереження та завантаження проблеми моделі в Excel 2019

Цільова комірка, змінні клітинки, клітинки обмежень і параметри розв’язувача, які ви використовували останнім часом, зберігаються як частина робочого аркуша Excel, коли ви натискаєте кнопку «Зберегти» на панелі інструментів швидкого доступу (Ctrl+S). Коли ви визначаєте інші проблеми для того самого робочого аркуша, який потрібно зберегти, ви повинні натиснути кнопку «Зберегти модель» у діалоговому вікні «Параметри розв’язування» та вказати посилання на клітинку або ім’я діапазону на активному аркуші, де ви хочете, щоб параметри проблеми були. вставлено.

Коли ви натискаєте кнопку Завантажити/Зберегти, Excel відкриває діалогове вікно Завантажити/Зберегти модель, що містить текстове поле Вибрати область моделі. Це текстове поле містить посилання на клітинки для діапазону, достатньо великого, щоб вмістити всі параметри проблеми, починаючи з активної клітинки. Щоб зберегти параметри проблеми в цьому діапазоні, натисніть OK. Якщо цей діапазон містить клітинки з наявними даними, вам потрібно змінити посилання на клітинку в цьому текстовому полі, перш ніж натиснути кнопку OK, щоб Excel не міг замінити наявні дані.

Після натискання кнопки ОК Excel копіює параметри проблеми у вказаному діапазоні. Ці значення потім зберігаються як частина робочого аркуша наступного разу, коли ви зберігаєте книгу. Щоб повторно використовувати ці параметри проблеми під час вирішення проблеми, вам просто потрібно відкрити діалогове вікно Параметри розв’язування, натиснути кнопку Завантажити/Зберегти, щоб відкрити діалогове вікно Завантажити/Зберегти модель, натисніть кнопку Завантажити, а потім виберіть діапазон, що містить збережені параметри проблеми. Коли ви натискаєте кнопку «ОК» у діалоговому вікні «Завантажити модель», Excel завантажує параметри з цього діапазону комірок у відповідні текстові поля в діалоговому вікні «Параметри розв’язувача». Потім можна закрити діалогове вікно Параметри розв’язування, натиснувши кнопку OK, і вирішити проблему, використовуючи ці параметри, натиснувши командну кнопку Розв’язати.

Пам’ятайте, що ви можете використовувати кнопку «Скинути все», коли захочете очистити всі параметри, визначені для попередньої проблеми, і повернути параметри «Розв’язувач» до значень за замовчуванням.

Створення звітів Solver в Excel 2019

За допомогою Solver можна створити три різні типи звітів:

• Звіт про відповіді: перелічує цільову клітинку та клітинки, що змінюються, з їх початковими та кінцевими значеннями, а також обмеженнями, які використовуються під час вирішення проблеми.
• Звіт про чутливість : вказує, наскільки чутливим є оптимальне рішення до змін у формулах, які обчислюють цільову клітинку та обмеження. Звіт показує зміни комірки з їх кінцевими значеннями та зменшеним градієнтом для кожної клітинки. (Зменшений градієнт вимірює ціль на одиницю збільшення в комірці, що змінюється.) Якщо ви визначили обмеження, у звіті про чутливість перераховано їх кінцеві значення та множник Лагранжа для кожного обмеження. (Множник Лагранжа вимірює ціль на одиницю збільшення, яка відображається в правій частині рівняння обмеження.)
• Звіт про обмеження: показує цільову клітинку та клітинки, що змінюються, з їх значеннями, нижньою та верхньою межами та цільовими результатами. Нижня межа являє собою найнижче значення, яке може мати комірка, що змінюється, при фіксації значень усіх інших комірок і при дотриманні обмежень. Верхня межа являє собою найвище значення, яке буде робити це.

Excel поміщає кожен звіт, який ви створюєте для задачі Solver, на окремому аркуші в книзі. Щоб створити один (або всі) з цих звітів, виберіть тип звіту (Відповідь, Чутливість або Обмеження) зі списку Звіти діалогового вікна Результати розв’язування. Щоб вибрати більше одного звіту, просто клацніть назву звіту.

Коли ви натискаєте кнопку «ОК», щоб закрити діалогове вікно «Результати рішення» (після вибору між параметрами «Зберегти рішення рішення» та «Відновити вихідні значення»), Excel створює звіт (або звіти), який ви вибрали на новому аркуші, який він додає до початку книги. . (Вкладки аркуша звіту називаються за типом звіту, як у Звіті про відповіді 1, Звіті про чутливість 1 і Звіті про обмеження 1. )