2.2.7. Розв’язок рівнянь і задач оптимізації

Для рішення завдань оптимізації широке застосування знаходять різні засоби Excel:

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

 Надбудову Пошук рішення для розрахунку оптимальної величини по декількох змінним і обмеженнях;

 Диспетчер сценаріїв для створення й оцінки наборів сценаріїв «що - якщо» з декількома варіантами вихідних даних.

Підбір параметрів

Основною командою для рішення оптимизационных завдань в Excel є команда Сервіс/Підбор параметра. Ця команда визначає невідому величину, що приводить до необхідного результату. Якщо команда Підбор параметра відсутній у меню Сервіс, виконаєте команду Сервіс/Надбудова й установите прапорець Пакет аналізу у вікні діалогу Надбудова Для роботи з командою Підбор параметра необхідно підготувати аркуш, щоб в аркуші перебували:

 формула для розрахунку;

 порожній комірка для шуканого значення;

 інші величини, які використовуються у формулі. Посилання на порожній комірку повинна обов'язково бути присутнім у формулі, тому що саме вона є змінної, значення якої шукає Excel. Під час підбора параметра в змінний комірку безупинно заносяться нові значення, поки не буде знайдене рішення поставленого завдання. Такий процес називається ітерацією, і триває він доти, поки редактор не виконає 100 спроб або не знайде рішення, що лежить у межах точності 0,001 від точного значення (настроювання цих параметрів здійснюється за допомогою  команди Сервіс/Параметри, вкладка Обчислення) Оптимізація за допомогою команди Підбор параметрів виконується так:

1. Створіть аркуш, наприклад, з формулою =B1*B2 в комірці B3, порожній (змінної) осередком (B2) і інш даними (B1), які можуть знадобитися при обчисленнях. Наприклад, необхідно визначити кількість книг за ціною 23,75 грн., які необхідно продати, щоб обсяг продажів склав 10000,00 грн.

2. Виділите комірку аркуша (B3), у якій утримується формула (цей комірка з'явиться в поле "Установити в комірці" у вікні діалогу Підбор параметра). Виконаєте команду Сервіс/Підбор параметра. Відкривається вікно діалогу Підбор параметра..

3. Уведіть у текстове поле Значення число, що відповідає обсягу продажів - 10000. Перемістите курсор у текстовому полі Змінюючи значення комірці. Виділите той комірка, у якій повинен утримуватися відповідь (змінний комірка). Її вміст буде підібране й підставлене у формулу командою Підбор параметра. Виділений комірка (B2) виділяється на аркуші рамкою. Натисніть кнопку ОК, щоб знайти рішення.

Після завершення ітераційного циклу у вікні діалогу Результат підбора параметра з'являється повідомлення, а результат заноситься в комірка аркуша. Рішення показує, що для досягнення обсягу продажів 10000 грн. необхідно продати 421 книгу  за ціною 23,75 грн. Для закриття вікна діалогу Результат підбора параметра клацніть на кнопці ОК.

Команда Пошук рішення

Для рішення складних завдань, що вимагають застосування лінійного й нелінійного програмування, а також методів дослідження операцій застосовується надбудова - Пошук рішення. Щоб використовувати надбудову Пошук рішення не обов'язково знати методи програмування й дослідження операцій, але необхідно визначати, які завдання можна вирішувати цими методами. Користувач повинен уміти за допомогою діалогових вікон надбудови Пошук рішення правильно сформулювати умови завдання, і якщо рішення існує, то “Пошук рішення” відшукає його. В основі надбудови  лежать ітераційні методи. У тому випадку, коли оптимизационная завдання містить кілька змінних величин, для аналізу сценарію необхідно скористатися надбудовою Пошук рішення. “Пошук рішення” дозволяє використовувати одночасно велику кількість  змінюваних комірок (до 200) і  задавати обмеження для змінюваних комірок.

Загальні властивості, які характерні для завдань, розв'язуваних за допомогою надбудови Пошук рішення:

 Існує єдиний цільовий осередок, що містить формулу, значення якої повинне бути зроблене максимальному, мінімальним або ж рівним, якийсь конкретному значенню.

 Формула в цьому цільовому комірці містить посилання на ряд змінюваних комірок. Пошук рішення полягає в тім, щоб підібрати такі значення змінних у змінюваних комірках, які б забезпечили оптимальне значення для формули в цільовому комірці.

 Може бути задана деяка кількість обмежень - умов або співвідношень, яким повинні задовольняти деякі зі змінюваних комірок.

Постановка завдання

Першим кроком при роботі з командою Пошук рішення є створення спеціалізованого аркуша. Для цього необхідно створити цільовий осередок, у яку вводиться основна формула. Крім того, аркуш може включати інші значення й формули, що використовують значення цільового й змінного комірок. Формула в цільовому комірці повинна опиратися в обчисленнях на значення змінних комірок. Після того, як завдання оптимізації буде підготовлена на аркуші, можна приступати до роботи. 1. Виділите на аркуші цільовий осередок, у яку уведена формула. 2. Виконаєте команду Сервіс/Пошук рішення. Відкривається вікно діалогу Пошук рішення. Оскільки був виділений осередок, у текстовому полі «Установити цільовий осередок» з'явиться правильне посилання на осередок. У групі «Рівної» перемикач за замовчуванням установлюється в положення «Максимальному значенню».

3. Перейдіть до поля "Змінюючи комірці" і введіть змінні комірки аркуша 4. Додайте обмеження на змінні в змінюваних комірках. Для уведення обмежень натисніть кнопку Додати, щоб задати перше обмеження у вікні діалогу, потім можна ввести друге, третє й т.д. 5. Коли оптимизационная завдання буде готова до виконання, можна натиснути кнопку Виконати для одержання відповіді. З'явиться вікно діалогу з описом результатів процесу оптимізації. 6. Щоб відобразити знайдені рішення в комірках аркуша, установите перемикач "Зберегти знайдене рішення" і натисніть кнопку ОК. Знайдена максимальна величина міститься в цільовий осередок, а змінні комірки заповнюються оптимальними значеннями змінних, які задовольняють установленим обмеженням.