Методичні рекомендації

Засіб Поиск решения є частиною засобів розв'язання блоку завдань, який називають аналізом "що — якщо". Процедура пошуку рішення дає змогу знайти оптимальне значення формули, яка міститься в комірці, що визначається цільова комірка. Ця процедура працює з групою комірок, безпосередньо або опосередовано пов'язаних із формулою в цільовій комірці. Для одержання за формулою, яка є в цільовій комірці, заданого результату, процедура змінює значення у комірках, які впливають на це значення. Щоб звузити безліч значень, що використовуються в моделі, застосовуються обмеження; вони можуть посилатися на інші комірки впливу. Процедуру пошуку рішення застосовують з метою визначення значення комірки впливу, яке відповідає екстремуму залежної комірки. Наприклад, можна змінити обсяг планованого бюджету реклами і з 'ясу вати, як це вплине на проектовану суму витрат. Завдань такого змісту є дуже багато у фінансово-економічній сфері, сфері маркетингу та менеджменту. Вони дуже важливі, й тому важливо вміти їх розв'язувати. Середовище MS Excel для вирішення таких завдань надає до послуг користувачів такий засіб, як надбудова Поиск решения. Щоб з'явився цей засіб, потрібно відкрити меню Сервис і вибрати пункт Поиск решения, за умови, що під час інсталяції програмного засобу MS Excel надбудови встановлені на комп'ютері. У процесі першого його використання потрібно виконати активізацію засобу меню Сервиса Надстройки Поиск решения.

Алгоритм розв’язання

1. Створіть документ Ms Excel з ім'ям Практ 40. Назвіть робочі аркуші відповідно Завдання 1, Завдання 2, Завдання З, Завдання 4 і виконайте на них необхідні обчислення.

2. На робочому аркуші Завдання 1 створіть дані як у табл. 40.1.

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

4. У рядку Всього обчисліть сумарну кількість одиниць продукції та загальні витрати на партію продукції. Запишіть цю таблицю у звіт.

5. Усвідомте той факт, що цільовою коміркою в цій задачі буде комірка, в якій обчислено загальні витрати на партію продукції.

6. Виділіть комірку, де вирахувано загальні витрати на партію продукції.

7. У меню Сервис клацніть на пункті Поиск решения.

8. У діалоговому вікні Поиск решения (рис. 40.1) в полі Установить целевую ячейку відображатиметься абсолютна адреса цільової комірки.

9. Встановіть альтернативний перемикач групи Равной у положення Минимальному значению.

Рис. 40.1. Діалогове вікно Поиск решения. Встановлення режиму мінімізації значення цільової комірки

10. З'ясуйте, в яких комірках величини впливають на значення цільової комірки. Оскільки витрати на виготовлення одиниці продукції в таких умовах змінюватися не можуть, то величинами, які впливають, будуть кількості одиниць певного різновиду продукції, отже, комірки F8:F13 є такими, які впливають.

11. У діалоговому вікні Поиск решения встановіть текстовий курсор у текстовому віконці Изменяя ячейки і виділіть діапазон комірок F8:F13 (рис. 40.2).

12. За умовою задачі загальна кількість одиниць продукції має бути не меншою ніж 125, а кількість одиниць продукції кожного різновиду перебувати в межах між 15 і 35 одиницями. У цих умовах і створюється система обмежень на величини комірок впливу.

13. Натисніть кнопку Добавить в діалоговому вікні Поиск решения.

14. В діалоговому вікні Добавление ограничения (рис. 40.3) встановіть текстовий курсор у полі Ссылка на ячейку і клацніть на кнопку комірки, в якій обчислено загальну кількість одиниць продукції. Потім виберіть зі списку Оператор порів няння > = і встановіть текстовий курсор у текстовому полі Ограничение, введіть із клавіатури 125, після чого клацніть на кнопку Добавить цього самого діалогового вікна.

Рис. 40.2. Діалогове вікно Поиск решения. Встановлення діапазону комірок впливу

Рис. 40.3. Діалогове вікно Добавление ограничения. Встановлення обмеження на загальну кількість одиниць продукції

15. Введіть наступне обмеження на комірки впливу F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду не перевищує 35. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (рис. 40.4).

16. Введіть наступне обмеження на комірки впливу F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду не менша 15. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (рис. 40.5).

Рис. 40.4. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції кожного різновиду не більша 35

Рис. 40.5. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції кожного різновиду не менша 15

17. Введіть наступне обмеження на комірки впливу F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду становить ціле число (рис. 40.6). Натисніть на кнопку ОК.

Рис. 40.6. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції становить ціле число

17. Діалогове вікно Поиск решения матиме вигляд як на рис. 40.7

Рис. 40.7. Діалогове вікно Поиск решения. Вигляд після встановлення необхідних режимів та обмежень

19. Клацніть на кнопку Параметры діалогового вікна Поиск решения і вивчіть діалогове вікно Параметры поиска решения (рис. 40.8), а також встановлені за замовчуванням значення й положення індикаторних та альтернативних перемикачів.

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

20. Клацніть на кнопку Справка діалогового вікна Параметры поиска решения і вивчіть довідкову інформацію. Після цього закрийте вікно Справка, натисніть на кнопку ОК діалогового вікна Параметры поиска решения. У діалоговому вікні Поиск решения натисніть на кнопку Выполнить.

21. У діалоговому вікні Результаты поиска решения (рис. 40.9), якщо розв'язок знайдено, клацніть на кнопці ОК при положенні альтернативного перемикача Сохранить найденное решение.

22. Перегляньте звіти: Результаты, Устойчивость та Пределы. Проаналізуйте їх.

22. Перейдіть до таблиці значень й уважно вивчіть встановлені в комірках F8:F13 і G14 значення.

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

Значення величин у комірках F8:F13 і G14 є розв'язком задачі.

Запишіть у звіті цей розв'язок (усю таблицю). Порівняйте з вихідною таблицею, зробіть висновки.

ЗАВДАННЯ 2. Знайдіть хоча б одне значення економічного

показника х, яке є невід'ємним розв'язком рівняння:

ж⁵ - 2х⁴ +17 х³ - 37х² +11* - 57 = 0.

Алгоритм розв'язання

1. На робочому аркуші Завдання 2 виділіть деяку комірку, наприклад К5, і введіть у неї довільне числове значення X.

2. В іншу комірку, наприклад М5, введіть формулу, що відповідає лівій частині рівняння:

= К5~5 - 2 * К5~4 + 17* К5^Л3-37 * К5~2 + 11 * К5 - 57.

3. З'ясуйте той факт, що цільовою коміркою в цій задачі буде комірка, в якій обчислено значення лівої частини рівняння, тобто комірка М5.

4. Виділіть цільову комірку, тобто комірку М5.

5. У меню Сервис клацніть на кнопку пункту Поиск решения.

6. У діалоговому вікні Поиск решения (рис. 40.10) у полі Установить целевую ячейку відображатиметься абсолютна адреса цільової комірки.

Рис. 40.10. Діалогове вікно Поиск решения

7. Встановіть альтернативний перемикач групи Равной у положення Значению і введіть у текстове поле значення правої частини рівняння. Це буде нуль.

8. У діалоговому вікні Поиск решения встановіть текстовий курсор у текстовому віконці Изменяя ячейки і виділіть комірку К5 (див. рис. 40.10).

9. За умовою задачі значення показника X має бути невід'ємним (X > = 0). Ця умова і створює обмеження на величину комірки впливу К5.

10. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения.

11. У діалоговому вікні Добавление ограничения (рис. 40.11) встановіть текстовий курсор у полі Ссылка на ячейку і клацніть на кнопку комірки К5. Потім виберіть зі списку оператор порівняння > =, встановіть текстовий курсор у текстовому полі Ограничения і введіть значення 0.

12. Діалогове вікно Поиск решения матиме вигляд, як на рис. 40.12.

У діалоговому вікні Поиск решения натисніть кнопку Выполнить.

Зауваження: подбайте, щоб точність становила 0,000 000 01.

Рис. 40.11. Діалогове вікно Добавление ограничения. Встановлення обмеження на значення розв'язку

Рис. 40.12. Діалогове вікно Поиск решения після встановлення обмежень на комірку впливу

13. У діалоговому вікні Результаты поиска решения клацніть на кнопку ОК при положенні альтернативного перемикача Сохранить найденное решение.

14. Перейдіть до комірок значень та уважно вивчіть встановлені в комірках М5 і К5 значення. Значення у комірці К5 є розв'язком задачі. Запишіть у звіт рівняння і знайдений розв'язок.

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

ЗАВДАННЯ 3. Знайдіть значення економічного показника, що є невід'ємним розв'язком рівняння 5х³ - 7 sinx = 3.

Самостійно виконайте завдання, орієнтуючись на алгоритм задачі 2.

Подбайте, щоб точність розв'язку становила 0,000 000 1.

ЗАВДАННЯ 4. Деяка фірма випускає вироби двох різновидів А і В. Кожен різновид виробу потребує певного часу на виготовлення і збирання. Кожен виріб А потребує 5 год на виготовлення і 2 год — на збирання; кожен виріб В — 3 год на виготовлення і 4 год — на збирання. Протягом робочого тижня фірма має 126 год на виготовлення і 80 год — на збирання виробів. Організація може продати всі виготовлені вироби, оскільки на них є попит. Кожен виріб А дає прибуток у 140 грн, а виріб Б — у 200 грн. Вирахуйте тижневий план випуску виробів А та Б, який забезпечить фірмі максимальний прибуток.

Методичні рекомендації Математична модель задачі

Нехай х — кількість виробів зразка А, а у — кількість виробів зразка Б. Тоді прибуток дорівнюватиме 140* + 200у (грн).

Система обмежень, що випливає з умов задачі, матиме такий вигляд:

Алгоритм розв'язання

1. У дві комірки робочого аркуша Завдання 4 введіть довільні допустимі значення X та У.

2. У третю комірку введіть формулу обчислення прибутку. Ця комірка буде цільовою коміркою.

3. Ще в дві комірки введіть формули обчислення лівих частин перших нерівностей системи обмежень.

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

5. Додайте умову невід'ємності X та У.

6. Додайте умову цілих числових значень X та У.

7. Задайте точність.

8. Знайдіть розв'язок.

ЗАВДАННЯ 5. Захистіть виконану роботу.

Запитання до захисту практичної роботи:

1. Які задачі можна розв'язувати, використовуючи надбудову Поиск решения?

2. Яка комірка називається цільовою?

3. Як сформувати обмеження на значення у певних комірках?

4. Як задати точність розв'язку?

5. Наведіть приклади задач, що потребують застосування засобу Поиск решения.

Практична робота № 41

ТЕМА: Табличний процесор Microsoft Excel. Застосування фінансових функцій для розв'язання задач.

МЕТА: навчитись застосовувати фінансові функції під час розв'язання задач.

ХІД РОБОТИ

Запишіть тему і мету роботи. Складіть звіт про виконання практичної роботи.

ЗАВДАННЯ 1. Вивчіть теоретичні відомості. Занотуйте у звіт найважливіше. Розв'яжіть задачі за наведеними алгоритмами й самостійно.

Кожну задачу розв'яжіть на окремому робочому аркуші книги з ім'ям Практ_41 Назвіть робочі аркуші Задача Задача 2,... відповідно. Текст кожної задачі надрукуйте в написі та розмістіть напис на початку робочого аркуша.

Методичні рекомендації

Табличний процесор Microsoft Excel в арсеналі має категорію функцій Финансовые, що орієнтуються на розв'язання задач фінансового аналізу. Всі фінансові функції Microsoft Excel можна класифікувати на такі групи:

1. для аналізу дисконтування;

2. для аналізу інвестицій;

3. для аналізу амортизації;

4. для аналізу цінних паперів.

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

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

Приклад. Якщо здійснюються щомісячні платежі з періодом три роки за річної процентної ставки 15 %, то, задаючи аргументи фінансових функцій, слід задавати місячну відсоткову ставку і кількість періодів виплат теж задавати у

місяцях 3 • 12 = 36. Якби за даних умов було задано, що виплати здійснюються поквартально, то квартальна відсоткова ставка становила б , а кількість періодів виплат 3 • 4 = 12.

Якщо здійснюються щорічні платежі, то варто задати відсоткову ставку 15 %, а кількість періодів виплат — три.