Підбору параметрів і пошуку рішень
v Аналіз зв`язків– це графічне відображення ланцюжків посилань від формули до формули, що виникають при .розробці електронної таблиці. Завдяки такому відображенню стає легким спостереження впливу вихідних значень на результати обчислень і пошук помилок у алгоритмі.
v Підбір параметрів –.це простіша операція зворотного розрахунку, коли користувач задає результат обчислення, а система автоматично розраховує належне вихідне значення у вказаній комірці.
v Пошук рішення–більш потужна операція зворотного розрахунку, коли користувач може задавати не тільки результат обчислення, але й деяку мету, – наприклад отримання максимального або мінімального значення результату. Крім того він може вказати обмеження на варіацію вихідних даних. Система автоматично робить покроковий підбір значень вхідних даних у вказаному діапазоні доки не буде досягнута мета, або не виконане обмеження на кількість ітерацій
За допомогою табличного процесора Excel можна виконувати аналіз даних і оптимізацію знайденого рішення.
Підбір параметра забезпечує дослідження даних, зв'язаних математичним рівнянням типу F(x)=А, у якому змінна х є шуканим параметром, а значення А – необхідним результатом.
Схема використання засобу Подбор параметра:
Сервис → Подбор параметра…
В поле Установить в ячейке ввести адресу комірки з формулою обчислень
В поле Значение ввести необхідний результат
В поле Изменяя значение ячейки ввести адресу змінного параметра
Натиснути кнопку ОК
У вікні Результат подбора параметра
• натиснути кнопку OK, якщо результат відповідає необхідним вимогам;
• в протилежному випадку натиснути Отмена для повернення до початкових значень комірок.
Приклад використання засобу Подбор параметра представлені на рис. 1, 2.
Рис.1 . Фрагмент вікна Excel з діалоговим вікном Подбор параметра
Рис. 2. Результати роботи засобу Подбор параметра
В завданні для досягнення необхідних планових показників підприємства (у прикладі – значення 1300), передбачається уточнити показники філії 2 (початкове значення – 120, уточнене значення після підбору параметрів – 168).
Засіб Поиск решения.
Припустимо, що на деякий процес можна впливати, змінюючи кілька параметрів управління. Якщо деяку характеристику цього процесу можна описати як функцію від параметрів управління, то природно шукати такі значення параметрів {оптимальний план), при яких функція набуватиме оптимального в певному розумінні значення (як правило, максимального або мінімального). При цьому оптимальний план має задовольняти обмеження, що накладаються на параметри управління.
Для розв'язування таких завдань в Excel існує спеціальний засіб Поиск решения. Але перед тим як використовувати його, потрібно ввести вихідні дані.
• Розрізняють такі дані оптимізаційної задачі: параметри управління, цільова функція і обмеження.
• Для параметрів управління потрібно відвести область комірок, де вони будуть записуватись. Потім в цю область слід ввести довільні значення параметрів (наприклад, усі нулі). Під час роботи засіб Поиск решения підбиратиме значення цих параметрів доти, поки не отримає оптимальний план.
• Цільову функцію будують, використовуючи посилання на комірки з початковими значеннями параметрів управління. Комірку, де міститься формула цільової функції, називають цільовою.
• Кожне обмеження задачі в математичному записі має такий вигляд:
h(параметри управління) <= b,
де h — деяка функція; b — дійсне число. Знак відношення може приймати інший вигляд(=, > або <)
Інсталяція засобу Поиск решения
Якщо в меню Сервис немає пункту Поиск решения, цей засіб потрібно інсталювати. Для інсталяції засобу Поиск решения виконайте команду Сервис /Надстройки. Потім у групі Список надстроек виберіть пункт Поиск решения. Після цього натисніть кнопку ОК.
Засіб Поиск решения
• Для запуску засобу Поиск решения виконайте команду Сервис /Поиск решения.
• Діалогове вікно Поиск решения містить три групи полів, які потрібно заповнити. Це опції для цільової комірки, адреси комірок із змінними параметрами управління та поле обмежень.
• У групі опцій цільової комірки зазначте адресу комірки (у полі Установить целевую ячейку) і тип оптимізаційної задачі. У підгрупі равной (тип задачі) є такі пункти:
- максимальному значению (задача максимізації цільової функції);
- минимальному значению (задача мінімізації цільової функції);
- значению (задача рівності цільової функції конкретному числу).За замовчуванням цільовою вважається комірка, де розміщується курсор.
• Адреси змінних параметрів управління задачі задайте в полі Изменяя ячейки.
• Обмеження задачі задаються у групі Ограничения. їх вводять натисканням кнопки Добавить. Ця кнопка викликає діалогове вікно Добавление ограничения, де потрібно заповнити три поля. У лівому полі Ссылка на ячейку зазначають адресу лівої частини обмеження, у правому полі Ограничение — адресу правої частини обмеження або число, з яким порівнюється ліва частина, і в центральному полі вибирають тип обмеження: <=, =, >=, "цел" або "двоич". Останні два типи вказують, що ліва частина набуває лише цілих значень або відповідно значення 0 і 1. Обмеження додають до списку обмежень, використовуючи кнопку Добавить або ОК. При цьому:
- кнопка Добавить дає змогу записати наступне обмеження;
- кнопка ОК закриває вікно Добавление ограничения.
• Редагувати обмеження можна за допомогою кнопки Изменить.
• Параметри керування засобом Поиск решения задають у діалоговому вікні Параметры поиска решения, що викликається натисканням кнопки Параметры. Розглянемо параметри керування (їх значення за замовчуванням наведено в дужках).
- Максимальное время (100 с) — максимальний час, відведений на розв'язування задачі. Якщо за цей час Поискрешения не знайде оптимального розв'язку, він повідомить результати останньої ітерації.
- Предельное количество итераций (100) — обмеження на час роботи Поиск решения у термінах максимальної кількості ітерацій алгоритму.
- Относительная погрешность (0,000001) — відносна точність, з якою шукається оптимальне значення цільової комірки.
- Допустимое отклонение (5 %) —допустиме відхилення значення цільової комірки від оптимального, якщо в задачі є параметри, область зміни яких обмежена цілими числами.
- Параметр сходимость (0,0001). Якщо відносна зміна у п'яти останніх ітераціях менша від цього параметра, оптимізаційна задача вважається розв'язаною. Цей параметр можна застосувати тільки для нелінійних задач.
- Параметр линейная модель використовує методи лінійного програмування.
- Параметр значения не отрицательные означає, що всі змінні параметри невід'ємні.
- Параметр автоматическое масштабирование використовують тоді, коли значення змінних параметрів та оптимальне значення цільової комірки істотно різняться.
- Параметр показывать результаты итераций виводить проміжні результати після кожної ітерації.
- У нелінійних задачах у групі Оценки доцільніше вибрати опцію квадратичная.
- Параметри разности і Метод поиска. Доцільніше залишити значення параметрів цих груп, що є за замовчуванням.
• Для розв'язання задачі натисніть кнопку Выполнить.
Аналіз результатів
Після визначення розв'язку оптимізаційної задачі підпрограма Поиск решения відкриває діалогове вікно Результаты поиска решения, звідки вибирають бажані типи звітів про розв'язок.
• Для простого відображення розв'язку в робочому аркуші виберіть опцію Сохранить найденное решение.
• Для відмови від отриманого розв'язку виберіть опцію Восстановить исходные значения.
• Для відображення результатів на окремому аркуші виберіть тип звіту Результаты. При цьому на новому аркуші буде наведено інформацію про оптимальний план та оптимальне значення параметрів, а також про зв'язаність (рівність лівої та правої частин обмеження) чи незв'язаність обмежень.
• Для отримання звіту про стійкість розв'язку щодо малих змін у цільовій функції та обмеженнях виберіть тип звіту Устойчивость.Зауважимо, що найважливішим результатом звіту про стійкість є множники Лагранжа (тіньові ціни). Множник Лагранжа для кожного обмеження вказує на миттєве покращення значення цільової функції, якщо збільшити (за умови відношення "<") праву частинуобмеження на 1.
• Для аналізу допустимих змін кожного параметра за умови, що значення інших параметрів є фіксованими і такими, як в оптимальному плані, використовують тип звіту Пределы.
• За допомогою миші можна вибрати кілька типів звітів одночасно.