Апарат економіко-математичної обробки та аналізу даних в середовищі MS Excel: математичні, статистичні, фінансові функції

В Excel существует специальная программа – мастер, упрощаю-щая процесс создания формул. [¦_X] – кнопка “Ввод формул”. Нажа-тием этой кнопки вызываем мастера функций. Слева выбираем ка-тегорию “Функции”. Справа – саму функцию. Арефметические и тригонометрические функции позволяют производить простые и сложные математические вычисления, например вычисление сум-мы ячеек диапозона, удовлетворяющих указанному условию, округ-ление чисел и прочее: АBS, ACOS, ACOSH, ASIN, ASINH,ATAH, COS, COSH и другие. Логические функции предназначены для про-верки нескольких условий. Так функция “ЕСЛИ” позволяет опре-делить, выполняются ли указанное условие, и возвращает одно зна-чение если условие истинно, и другое – если оно можно. “ЕСЛИ” (логическое условие, код верно, кода неверно).

23. Поиск решений". Обмін інформацією між додатками Microsoft Office.

Процедура підбору параметрів є простим допоміжним інструментом, що дає змогу визначити, якими повинні бути значення вихідних даних, щоб одержати заданий резуль­тат. Сутність даної процедури ілюструє класична задача — розрахунок суми виплат за вкладами:

де S — сума виплат;

V — розмір внеску, грн.;

К — коефіцієнт нарощування;

Р — відсоткова ставка, %;

Т — термін вкладу, років.

Коефіцієнт нарощування розраховується як частка від ділення суми виплат на розмір вкладу. Для обчисленні суми виплат занесемо в комірку В6 таку формулу:

=В2*(1+В4/100)^ВЗ.

Результат розрахунку S при заданих значеннях пара­метрів V, Т і Р наведено на рис. 2.122.

Рис. 2.122. Розрахунок виплат за вкладами

-32-

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

Викликає зацікавленість і розв'язання зворотної зада­чі — визначення значень кожного з вихідних параметрів V, Т чи Р на основі суми виплати S. Для цього до Excel і введено процедуру підбору параметра.

Нехай, наприклад, передбачається довести суму випла­ти S до 8000 грн., тобто збільшити розмір початкового вкладу V у два рази при незмінній відсотковій ставці Р = 10%. Очевидний шлях розв'язання цієї задачі — збіль­шення терміну вкладу Т.

Командою Сервис—Подбор параметра... відкривають однойменне вікно діалогу (рис. 2.123), де встановлюють абсолютну адресу суми виплат — $В$6 і її цільове зна­чення — 8000 грн., а також абсолютну адресу комірки зі змінюваним параметром Т — $В$3.

Пошук потрібного значення параметра Т починають через команду ОК. Далі на екран викликається вікно діа­логу «Результат подбора параметра» (рис. 2.124), де відобра­жаються поточне і підібране значення параметра.

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

Організація пошуку розв'язків починається з вихід­ної таблиці, зображеної на рис. 2.135. У ній містяться Дані про витрати на випуск окремих видів продукції й підсумкові витрати на всі види.

Пошук розв'язку починається через команду меню Сервис—Поиск решения, що відкриває однойменне вікно.

У вікні «Поиск решения» установлюють:

• абсолютну адресу $D$9 цільової комірки, тобто ко­мірки, вміст якої використовується як критерій оп-тимізації;

• абсолютну адресу діапазону комірок $С$3:$С$8, де розміщені значення змінюваного параметра «Кіль­кість»;

• чотири обмеження: $С$9>=100 і $С$3:$С$8=целое, $С$3:$С$8>=10 і $С$3:$С$8<=40

Обмеження послідовно вводять до вікна діалогу «До­бавление ограничения» (рис. 2.137), що відкривається при активізації кнопки «Добавить» (див. рис. 2.136).

-33-

Далі вони передаються в поле «Ограничение» (рис. 2.137) клацанням мишею на кнопці «Добавить». Користуючись відповідними кнопками, обмеження можна змінювати, вида­ляти і відновлювати.

Потім за допомогою опції «Параметри» (рис. 2.136) на екран викликають вікно діалогу (рис. 2.138), де вста­новлюють значення основних параметрів пошуку розв'яз­ку: максимальний час пошуку, кількість ітерацій, модель — «Линейная», метод пошуку — «Ньютона» тощо.

Власне пошук розв'язку починається через команду Выполнить і має ітераційний характер. По його закінчен­ні отримані значення параметрів автоматично вставляють­ся в таблицю, і на екрані з'являється діалогове вікно з відповідним повідомленням (рис. 2.139).

Поиск по сайту: