Інформатика

Прочитайте!

Комп’ютерне моделювання ефективно використовується для розв’язування задач будь-якої галузі. Математичні моделі процесів, явищ реалізують за допомогою рівнянь або систем рівнянь, і в MS Excel є засоби їх розв’язування.

Приклад 1. Клієнт банку планує вносити гроші на депозитний рахунок щороку й рівномірними частинами. Яким має бути розмір щорічного внеску, щоб за 10 років зібрати 20000 грн при 5% річних?

Розв’язання

Складаємо таблицю за зразком. У клітинку В4 вводимо формулу: =FV(B1;B2;B3).

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

FV(ставка;кпер;спл;[зв];[тип])

Докладніший опис аргументів функції FV й додаткові відомості про функції для обчислення фінансової ренти див. у статті про функцію PV.

Синтаксис функції FV має такі аргументи:

• ставка    – обов’язковий аргумент. Відсоткова ставка за період.
• Кпер    – обов’язковий аргумент. Загальна кількість періодів сплати фінансової ренти.
• спл    – обов’язковий аргумент. Виплата, яка здійснюється в кожний період; вона залишається незмінна протягом строку фінансової ренти. Зазвичай значення аргументу «спл» містить основну частину боргу й відсоток і не містить жодні додаткові внески або податки. Якщо аргумент «спл» не вказано, необхідно включити аргумент «зв».
• зв    – необов’язковий аргумент. Зведена вартість або загальна сума, яка на цей час дорівнює сукупності майбутніх виплат. Якщо аргумент «зв» не вказано, припускається, що його значення дорівнює 0 (нулю), і необхідно включити аргумент «спл».
• Тип    – необов’язковий аргумент. Число 0 або 1, яке вказує на час виплати. Якщо аргумент «тип» не вказано, припускається, що його значення дорівнює 0.

Вибираємо клітинку В4 і виконуємо команду Дані-Аналіз “якщо”-Підбір параметра задавши значення 20000 та ввівши у поле змінююче заначення клітинки посилання на клітинку В3.

Приклад 2.

Кредит в 1000 у. о. виданий на 5 років під 20 % річних за таких умов повернення: наприкінці 2 року повернути 200 у. о., наприкінці кожного наступного — по 400 у. о. Визначити, яку суму необхідно внести наприкінці першого року, щоб кредит був погашений вчасно (до кінця 5 року). Щороку борг із кредитування обчислюється за формулою:

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

Позначимо борг наприкінці кожного року як борг1, борг2 тощо. Отже, якщо x — повернення грошей у перший рік, то:

Оскільки кредит необхідно сплатити за 5 років, то борг5 = 0 (наприкінці п’ятого року необхідно з боргом5 вийти в 0), тобто рівняння матиме такий вигляд:

борг4 – 400 + 0,2 (борг4 – 400) = 0.

Указане рівняння є математичною моделлю, адже борг4 залежить від значення борг3 і, розмірковуючи так, дійдемо до 1 року, борг якого залежить від x.

У таблиці, побудованій в MS Excel, x у моделі — значення клітинки В4. На рис. 3 подано таблицю зі значеннями після виклику методу Підбір параметра. У цю клітинку введемо 1 — сплачено в 1 рік, у наступні клітинки стовпця В занесемо 200, 400 і 400, як зазначено в умові. Для зручності залишок внеску як різницю бор- гу та сплаченого обчислимо в стовпці С і остаточний борг на кінець року — у стовпці D.

Вікно Підбір параметра зі введеними посиланнями та значеннями подано на рис. 4.

Розв’язування оптимізаційних задач методом Пошук розв’язування

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

У математичній моделі розв’язування оптимізаційних задач необхідно знайти значення параметрів, від яких залежить результат обчислення певної математичної функції (у MS Excel ця функція подається формулою). Шуканий результат може бути максимальним (наприклад, прибуток організації), мінімальним (витрати на сировину) або дорівнювати певному значенню (потужність електричної мережі).

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

Щоб розв’язок і значення параметрів були реальними, модель обов’язково містить обмеження або на значення пара- метрів, або на допоміжні величини, які залежать від оптимізаційних параметрів. Обмеження на дані завжди задаються в умові задачі (приклад 2-3).

Приклад 3 (стародавня задача)

Купець придбав 138 аршин чорного та червоного сукна за 540 карбованців (крб). Скільки аршин сукна кожного кольору придбав купець, якщо червоне коштувало 5 крб за аршин, а чорне — 3 крб?

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

Нехай кількість аршин чорного сукна x, тоді кількість червоного сукна — 138 – х. Складемо рівняння: 3х + 5(138 – х) = 540.

Звичайно, таке рівняння можна розв’язати й усно, але  на  цьому  прикладі  розгляньмо, як працює метод MS Excel Підбір параметра.

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

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

Складемо таблицю за умовою задачі. У клітинках наведено формули та результати їх обчислення.

Таблиця  має  містити  початкові   значення  з умови  задачі  та  клітинку  з   формулою, яка відповідає рівнянню математичної моделі.

Клітинка С2 призначена для невідомого x: на початку в клітинці С2 кількість чорного сукна дорівнює 0. Оскільки MS Excel не здійснює арифметичні операції з нечисловими даними, то на початку замість x вставляють будь-яке значення. Із таким довільним значенням, звичайно, не отримаємо правильну вартість усього сукна з умови задачі (540). Але головним є те, що в клітинці D4 вставлено формулу, результатом обчислення якої має бути значення з умови задачі.

Приклад 4

Підприємство виготовляє вироби трьох видів: А, B і С. Денний дохід від кожного виду одиниць виробу становить 13, 18 і 22 у. о. Необхідно визначити, за якої кількості виробів підприємство отримає максимальний дохід, з урахуванням того, що:

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

• підприємству потрібно виготовити 50 оди- ниць виробу А для виконання наявного замовлення; 40 одиниць виробу В — для виконання планового замовлення; а оскільки збут виробів С відносно невеликий, то їх необхідно виготовляти не більше ніж 40 одиниць.

Складемо математичну модель. Позначимо кількість виробів кожного виду а, b, с.

Складемо рівняння для цільової функції: дохід = 13 а + 18 b + 22 с.

Цільова функція — дохід — залежить від значень параметрів а, b, с і має бути спрямована на отримання максимального значення. Оптимізаційними параметрами є а, b, с — їх значення впливають на значення цільової функції. Під час пошуку максимального значення до- ходу необхідно врахувати обмеження на значення параметрів а, b, с:

а + b + с = 300;

a ≥ 50;

b ≥ 40;

с ≤ 40;

а, b, с — цілі додатні числа.

Щоб реалізувати модель у MS Excel із використанням надбудови Розв’язувач, необхідно дотримуватися певних правил:

• у MS Excel обов’язково має бути заданий діапазон клітинок для значень оптимізаційних параметрів;
• у таблиці має бути клітинка з формулою обчислення цільової функції;

• таблиця обов’язково містить клітинки зі значеннями параметрів, щоб мати можливість посилатися на них.

Розв’яжемо задачу за допомогою надбудови   Розв’язувач.   У   стандартному   наборі   команд MS Excel команда запуску надбудови Розв’язувач відсутня, її необхідно встановити на стрічку ДАНІ.  Для  цього  виконаємо  такі дії.

1. Перейдемо в меню ФАЙЛ  Параметри. Виберемо Надбудови.

2. У вікні, що відкрилося, виберемо Пакет аналізу й активуємо кнопку Перейти — відкриється вікно Надбудови.
3. Поставимо галочку  біля  Розв’язувач.  Підтвердимо дії кнопкою ОК. На стрічці ДАНІ з’явилася   команда   Розв’язувач.   Скористаємося розробленою математичною моделлю для створення таблиці в середовищі MS Excel (рис. 5).
4. У клітинки С2:С4 введемо значення для виробів а, b, с.
5. Після запуску команди Розв’язувач відкриється вікно Параметри розв’язувача (рис. 6, с. 169). У цьому вікні виконаємо дії:
   1. у поле Оптимізувати цільову клітинку введемо клітинку D5 з підрахунком загального доходу та вкажемо, що функція прямує до максимуму;
   2. у поле Змінюючи значення клітинки введемо клітинки С2:С4 (клітинки містять невідомі).
6. Для введення в поле Полягає обмеженням скористаємося кнопкою Додати, щоб увести обмеження на значення невідомих — обмеження вводять у вікні Додати обмеження (рис. 7, с. 159).
7. Підтвердимо дії  —  кнопкою  Розв’язати залишаємо активною команду Зберегти знайде­ний розв’язок; клацнемо кнопку ОК.Дані таблиці будуть змінені (рис. 8):