7

Тема 8: Моделювання і аналіз

1. Аналіз даних за допомогою засобу Підбір параметрів.

2. Застосування засобу Пошук рішення для оптимізаційного моделювання.

1. Аналіз даних за допомогою засобу Підбір параметра

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

Наприклад, відомий розмір внеску S_O, який буде вкладений i% на заданий період n років. Визначити коефіцієнт приросту і суму внеску на кінець періоду.

Коефіцієнт приросту і сума внеску визначатимуться по формулах:

K=(1+i)ⁿ S=S_O*k=S_O*(1+i)ⁿ

Оскільки n=10, S_O=150 уд.ед i=3%,

матимемо

K=1,34 S=201,59 ум.од.

Аналогічно можна сформувати і зворотні завдання.

1. Відомі: початкова сума внеску, термін внеску і Визначити під які відсотки потрібно вносити внесок?

2. Відомі: термін внеску, відсоток приросту і сума внеску на кінець періоду. Визначити початкову суму внеску.

Для вирішення таких завдань в Excel використовують команду Сервіс>Підбір параметру.

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

В результаті набудемо числового значення коефіцієнта приросту і значення суми внеску на кінець терміну внеску (див. мал. 47).

Мал. 47. Початкові дані для інструменту «Підбір параметра»

Для вирішення зворотних завдань скористаємося командою Підбір параметра. Так для вирішення першого завдання потрібне:

• Вибрати команду Сервіс> Підбір параметра;

• У діалоговому вікні, що з'явилося, в полі Встановити в комірці ввести абсолютну адресу комірки В22, в полі Значення ввести, наприклад, значення 300, а в полі Змінюючи значення комірки – абсолютна адреса комірки В19;

• Н ажать кнопку ОК (рис. 48).

Мал. 48. Застосування інструменту «Підбір параметра»

В результаті набудемо в комірці В4 значення 7%. У діалоговому вікні Підбір параметра потрібно вказувати:

• абсолютна адреса комірки з формулою і значення цього комірки – бажаний результат, якого потрібно досягти;

• абсолютна адреса комірки , значення якої потрібно визначити.

Аналогічно розв'язуються інші сформульовані завдання.

2. Застосування| засобу Пошук рішення для оптимізаційного моделювання

Оптимізаційне моделювання - достатньо складний інструмент. Його призначення полягає в пошуку оптимального рішення деякої функції за певних заданих умов.

Метод оптимізаційного моделювання вимагає дуже великого об'єму обчислень і застосування його при виконанні розрахунків вручну важко. Але саме тут на допомогу приходить Excel, дозволяючи легко і швидко вирішувати різноманітні задачі оптимізаційного моделювання. Інструмент Пошук рішення дозволяє знаходити оптимальне рішення сформульованої задачі. Розглянемо його використання на окремому прикладі.

Завдання. Для виготовлення двох видів продукції Р1 і Р2

використовують три види сировини SI S2, S3.

Запаси сировини SI, S2, S3 відповідно прирівнюють 30, 24, 20 одиниць. На виготовлення одиниці продукції Р1 необхідно витратити 5 одиниць сировини S1, 2 одиниці сировини S2, 4 одиниці сировини S3. На виготовлення одиниці продукції Р2 необхідно витратити 6 одиниць сировини S1, 6 одиниць сировини S2, 2 одиниці сировини S3.

Дохід від реалізації одиниці продукції Р1 і Р2 відповідно представляє 40, 30 умовних грошових одиниць.

Скласти такий план випуску продукції, щоб від її реалізації одержати максимальний дохід.

	вид сировини:			Дохід від реалізації одиниці продукції (у.о.)	кількість планових одиниць продукції	позначення
види продукції	S1	S2	S3
Р1	5	2	4	40		X1
Р2	6	6	2	30		Х2
Запаси сировини	30	24	20

Позначимо X1 - кількість планових одиниць продукції Р1;

Х2 - кількість планових одиниць продукції Р2.

Функція мети|цілі| (цільова функція) F=40X₁ + 30X2.---- об’єктна формула (сумарний дохід)

Умова:

Тоді (1)

Математичне завдання полягає в тому, щоб серед рішення системи (1) виявити таке рішення, щоб функція мети мала найбільше значення.

Для пошуку оптимального рішення за допомогою інструментів Пошук рішення необхідно ввести вхідні дані і здійснити запуск програми Пошук рішення за допомогою команди Сервіс > Пошук рішення. (Сервіс > Настройки, включити параметр Пошук рішення).

Спроектуємо розрахункову таблицю на робочому листі.

У відповідних комірках запишемо формули:

=ВЗ*х1+СЗ*х2

=В4*х1+С4*х2

=В5*х1+С5*х2

=40*х1+30*х2, де Х1 і Х2 – адреси комірок.

У відповідних комірках будуть розміщені знайдені оптимальні значення.

Спроектувавши таблицю, виконаємо команду Сервіс>Пошук рішення. В результаті з'явиться вікно діалогу Пошук рішення (див. мал. 57).

Рис.57. Діалогове вікно інструменту Пошук рішення

У полі Встановити цільову комірку вказуємо адресукомірки, в яку записана формула цільової функції.

У полі Змінюючи комірки указуємо адреси комірок, де знаходяться значення X1 і Х2.

У полі Обмеження задати обмеження. Їх вводити за допомогою кнопки Додати. Після її натиснення з'являється вікно діалогу Додавання обмеження (мал. 58). У цьому вікні в рядок введення Посилання на осередок вводимо адресу комірки, значення якої повинно задовольняти заданому обмеженню. Потім відкрити список обмежень і вибрати потрібний знак обмеження, а в рядок Обмеження ввести значення обмеження.

Рис.58. Діалогове вікно Додавання обмеження

Якщо в процесі введення даних виникає необхідність в зміні або вилученні певних обмежень, то слід скористатися кнопками Змінити або Видалити.

Після того, як всі обмеження будуть задані, натиснути кнопку Виконати. Після закінчення виконання цієї команди на екрані з'явиться вікно Результат пошуку рішення, в якому повідомляється, що рішення знайдене і всі обмеження і умови оптимальності виконані (мал. 59).

Рис.59. Діалогове вікно Результати пошуку рішення

Якщо рішення не існує (система обмежень не сумісна), тоді видається повідомлення Пошук не може знайти відповідного рішення.

Значення цільового комірки не сходиться (Якщо функція мети не обмежена).

Результат:

• Зберегти знайдене рішення;

• Відновити початкові дані.

Крім того пропонується вибрати тип звіту. Після вибору типу звіту:

Результати, в комірках В2 і С2 відобразяться шукані значення змінних Х1 і Х2;

у комірці В6 – значення функції мети, яке вона прийме при знайдених значеннях;

а в діапазоні комірок Е3:Е5 відобразяться значення, які прикмет ліва частина кожного з обмежень системи (1), якщо замість Х1 і Х2 підставити знайдені значення (мал. 60).

Рис.60. Результати рішення системи (1)

У діалоговому вікні Пошук рішення|розв'язання,вирішення,розв'язування| кнопка Відновити використовується для видалення|віддалення| всіх введених|запроваджених| у вікні посилань|заслань| і описів, що дозволяє почати|розпочати,зачати| опис завдання|задачі| із самого початку|з самого початку|.

Може трапитися, що як одне з обмежень, що накладаються на цільову функцію, буде задана вимога, по якій ця функція повинна повертати в змінні комірки тільки|лише| цілочисельні значення. При заданні|задаванні| такого обмеження слід використовувати як оператор значення – ЦІЛЕ.

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

Щоб поліпшити ці відомості, необхідно зажадати від засобу Пошук рішення підготувати спеціальні звіти, які і міститимуть всю вищезазначену інформацію. Для цього в діалоговому вікні Результати пошуку рішень слід вибрати із списку Типи звіту будь-які необхідні варіанти звітів. Тепер, після клацання на кнопці ОК, Excel не тільки збереже знайдені оптимальні значення в зміннихк омірках, але і помістить в робочу книгу нові робочі листи з необхідними звітами.

Рис.61. Звіт за наслідками інструменту Пошук рішення

У верхній частині звіту по результатах (рис.61) знаходяться відомості про значення цільового комірки, повернені об'єктною функцією до і після оптимізації. Якщо початкове значення в змінних комірках реально відображають наші суб'єктивні припущення про оптимальні значення, то ця інформація дозволить оцінити ступінь поліпшення (якщо вони є), досягнутих за допомогою засобу Пошук рішення. Додатково в звіті вказується, як змінилися початкові значення в змінних комірках в результаті застосування функції оптимізації.

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

Рис.62. Звіт по стійкості інструменту Пошук рішення

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

З віт по межах - останній тип звіту (мал. 63). У ньому указується, наскільки можуть мінятися значення в змінних комірках при збереженні результатів обчислення цільової функції в межах заданих обмежень.