Процедура пошуку розв’язку

На відміну від процедури підбору параметрапроцедура пошуку розв’язку дозволяє:

· вказати кілька змінюваних комірок;

· вказати обмеження на значення в змінюваних комірках;

· знайти розв’язок, при якому значення в певній комірці робочого листка досягає максимума, або мінімуму, або певного значення;

· отримати кілька варіантів розв’язку задачі.

Задачі, які розв’язуються з використанням процедури пошуку розв’язку, мають такі особливості:

· Значення в цільовій комірці (target cell) визначається за формулою, що вміщує посилання на вихідні комірки. Треба визначити всі вихідні параметри, при яких значення в цільовій комірці буде максимальним, мінімальним або заздалегідь визначеним.

· Значення в цільовій комірці залежить від значень в групі комірок, які називаються змінюваними комірками (changing cells). Їх значення впливають на результат в цільовій комірці.

· Розв’язок повинен знаходитись в певних межах або задовольняти певним обмеженням (constraints).

Приклад:

Необхідно визначити обсяги виробництва трьох видів продукції А, В, С при яких прибуток від збуту був би максимальним. Обмеження, які необхідно витримувати:

® Загальний обсяг виробництва — всього 300 виробів за день.

® Фірмі потрібно виробити не менше 50 виробів А для виконання існуючого замовлення.

® Фірмі потрібно виробити не менше 40 виробів В для виконання існуючого замовлення.

® Оскільки збут виробів С відносно невеликий, то можна виготовити не більше 40 одиниць цього виробу.

Вихідні дані на робочому листку розміщені так. В стовпчику B відображено кількість товару по кожному виду, в стовпчику С — прибуток від продажу одного виробу. В стовпчику D містяться формули, за якими вираховується прибуток від продажу кожного виду продукції через множення їх кількості на прибуток від продажу одного виробу.

 

 

Рис. 8. Фрагмент робочої книги з вихідними даними прикладу.

 

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

Етапи процедура пошуку розв’язку:

1. Ввести в робочий листок вихідні дані та формули.

2. Активізувати діалогове вікно “Пошук розв’язку” (Solver Parameters) (рис. 8).

3. Вказати цільову комірку.

4. Вказати змінювані комірки.

5. Задати обмеження.

6. При необхідності змінити опції процедури пошуку розв’язку.

7. Дозволити процедурі пошуку розв’язку виконати поставлену задачу.

Щоб активізувати діалогове вікно “Пошук розв’язку ”, треба залучити команду “Сервіс ð Пошук розв’язку ” (Tools ð Solver).

 

 

 

Рис. 9. Діалогове вікно “Пошук розв’язку”.

Якщо ця команда відсутня, необхідно завантажити відповідну надбудову. Для цього треба залучити команду “Сервіс ð Надбудови” (Tools ð Add-Ins). В результаті з’явиться діалогове вікно “Надбудови” (Add-Ins). Прокручуючи перелік надбудов, треба знайти надбудову “Пошук розв’язку ” (Solver Add-Ins) і встановити біля неї прапорець. Якщо клацнути на кнопці “ОК”, то надбудова буде завантажена і потрібна команда з’явиться. Якщо в переліку надбудов потрібної надбудови не виявиться, необхідно запустити програму встановлення Excel або Microsoft Office і, використовуючи вибіркове встановлення, вказати, що надбудова “Пошук розв’язку” повинна бути встановленою.

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

В полі “Встановити цільову комірку” (Set Target Cell) вказується адреса або ім’я цільової комірки. Для наведеного прикладу це к омірка D6, яка містить загальний прибуток по трьом видам виробів. Встановлюється потрібний, відповідно меті, перемикач. Для наведеного прикладу це перемикач “Що дорівнює максимальному значенню” (Max).

Вказуються змінювані комірки. Для наведеного прикладу це діапазон B3:B5.

Далі накладаються обмеження. Обмеження додаються по одному і відображаються у вікні “Обмеження” (Subject ti the Constraints). Для додавання обмежень треба клацнути на кнопці “Додати” (Add), щоб з’явилось діалогове вікно “Додати обмеження” (Add Constraint).

В цьому вікні треба ввести посилання на комірку, оператор обмеження та певне значення. Для наведеного прикладу перше обмеження — загальний обсяг продукції повинен дорівнювати 300 одиниць виробів. Так, треба ввести адресу комірки В6 або клацнути по ній, вибрати оператор дорівнює (equal) (=) в переліку, що розкривається, ввести значення — число 300 і клацнути на кнопці “ОК”, якщо це останнє в задачі обмеження, в іншому разі — на кнопці “Додати”. В даному прикладі так треба ввести чотири обмеження:

Зміст обмеження	Скорочений запис	Зміст обмеження	Скорочений запис
Обсяг випуску 300 одиниць	В6=300	Не менше 40 виробів В	В4>=40
Не менше 50 виробів А	В3>=50	Не більше 40 виробів С	В5<=40

 

Після введення останнього обмеження слід клацнути на кнопці “ОК” і повернутися до діалогового вікна “Пошук розв’язку”, яке для наведеного прикладу тепер повинно містити чотири обмеження.

Щоб почати процес розв’язання задачі, треба клацнути на кнопці “Виконати” (Solve). В рядку стану буде відображуватись хід розв’язання задачі і через деякий час з’явиться вікно “Результати пошуку розв’язку” (Solver Results) з повідомленням про знайдений розв’язок і пропозиціями що-до подальших дій:

· Замінити вихідні значення в змінюваних комірках на ті, які були знайдені в результаті розв’язання задачі.

· Відновити вихідні значення в змінюваних комірках.

· Створити кілька звітів про процедуру пошуку розв’язку. Для цього необхідно в переліку типів звіту, натискаючи клавішу < Shift >, клацнути на потрібних.

· Клацнути на кнопці “Зберегти сценарій” (Save Scenario) для збереження розв’язку у вигляді сценарію, який може бути використаний в засобі “Диспетчер сценаріїв”.

Якщо було вказано про створення звіту, він буде розміщений на новому робочому листку, якому автоматично буде привласнене відповідне ім’я. В розділі звіту “Обмеження” (Constraints) буде вказаний стан всіх обмежень. Тип пов’язаний (binding) означає, що це обмеження задоволено, але при цьому відповідний параметр має своє граничне значення.

В процедурі пошуку розв’язку дуже важливими для досягнення результату є режими пошуку, які встановлюються в діалоговому вікні “Параметри пошуку розв’язку” (Solver Options). Щоб з’явилось це вікно, треба клацнути в діалоговому вікні “Пошук розв’язку” на кнопці “Параметри” (Options).

Можна встановлювати такі параметри процедури пошуку розв’язку:

· Максимальний час (Max Time) — надає можливість обмежити максимальний час (в секундах) розв’язування задачі. Якщо з’являється повідомлення, що час вийшов, можна додати час для продовження пошуку розв’язку.

· Граничне число ітерацій (Iteration) — дозволяє задати максимальне число проміжних розв’язків — ітерацій — при пошуку розв’язку.

· Відносна похибка (Precision) — параметр, призначений для задання точності, з якою визначаються значення у вихідних комірках відповідно до цілі задачі. Задача буде розв’язана швидше, якщо встановити меншу точність.

· Допустиме відхилення (Tolerance) — це максимальне відхилення в процентах для цілочисельних розв’язків (--якщо задане цілочисельне обмеження).

· Лінійна модель (Assume Liner Model) — дозволяє прискорити пошук розв’язку, але при умові, що всі залежності в моделі лінійні, тобто не можна використовувати цю опцію, якщо значення в змінюваних комірках множаться або діляться, або в задачі використовується піднесення до степеню.

· Показувати результати ітерацій (Show Iteration Results). Якщо опція активізована, то після кожної ітерації процес пошуку розв'язку призупиняється і на екрані будуть відображені знайдені на цій ітерації результати.

· Автоматичне масштабування (Use Automatic Scaling) — параметр призначений для включення автоматичної нормалізації вихідних та одержаних значень, що якісно різняться по величині, наприклад, максимальний прибуток в процентах по відношенню до вкладів, які вираховуються в мільйонах умовних одиниць.

· Розділи Оцінка, Різниці та Методи пошуку (Estimates, Derivatives, Search). Ці опції дозволяють контролювати деякі технічні аспекти розв’язання задачі. В більшості випадків нема необхідності змінювати установки цих опцій.

· Зберегти модель (Save model) — відображує діалогове вікно “Зберегти модель” (Save Model), в якому треба визначити посилання на діапазон комірок робочого листка, де будуть збережені параметри моделі.

· Завантажити модель (Load Model) — відображує діалогове вікно “Завантажити модель” (Load Model), в якому треба визначити посилання на діапазон комірок робочого листка, що містять параметри моделі, яку необхідно завантажити.

Звичайно модель зберігають тільки при використанні кількох різних наборів параметрів пошуку розв’язку для робочого листка. Параметри для першої моделі пошуку розв’язку запам’ятовуються автоматично в робочому листку (при цьому використовуються сховані імена). Якщо запам’ятовуються додаткові моделі, інформація записується у вигляді формул, які відповідають певним установкам — установкам користувача (остання комірка в діапазоні, що зберігається, уявляє собою формулу масиву, яка містить установки опцій). Для завантаження цих установок використовується діалогове вікно, яке викликається кнопкою “Завантажити модель” (Load Model).

 

Статистичний аналіз даних

 

До складу Microsoft Excel входить набір засобів аналізу даних (так званий пакет аналізу), призначений для розв’язання складних статистичних і інженерних задач. Для проведення аналізу даних за допомогою цих інструментів потрібно вказати вихідні дані і вибрати відповідні параметри. Аналіз буде проведений за допомогою відповідної статистичної або інженерної макрофункції, а результат буде вміщений у відповідний діапазон. Інші інструменти Excel дозволяють представити одержані результати аналізу в графічному вигляді.

Щоб вивести список доступних інструментів аналізу, потрібно в меню вибрати команду “Сервіс Þ Аналіз даних” (рис. 10).

 

 

Рис. 10. Вікно вибору інструментів аналізу даних

 

Якщо команда Аналіз даних відсутня в меню Сервіс, то необхідно запустити програму установки Microsoft Excel. Після установки пакету аналізу його необхідно вибрати і активізувати за допомогою команди Настройки.

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

 

Примітка. Додатково в Microsoft Excel міститься велике число статистичних, фінансових і інженерних функцій. Щоб вивести список доступних функцій листа, натисніть кнопку Змінити формулу в рядку формул, а потім натисніть стрілку в Вставка функції.