Зміст звіту з лабораторної роботи

Лабораторна робота № 5

з предмету "Програмне забезпечення".

Тема

Excel. Інструменти Пошук розв'язку.

Мета

Вміти користуватися інструментами Пошук розв'язку і Таб­лиця підстановки для аналізу підприємницької діяльності і прийняття рішень.

Обладнання: IBM - сумісні ПК.

"Програма виконання роботи".

1. Планування випуску продукції.

2. Аналіз кредиту.

Задача. Планування випуску продукції

Для виготовлення виробів х, у, z використовують три види сировини: І, II, III. У таблиці задано норми витрат сировини на один виріб кожного виду, ціна одного виробу, а також кількості сировини кожного виду, які можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний (п — номер варіанта)?

х у z Загальна к-сть сировини

І 18 15 12 360 - п

II 6 4 8 192

III 5 3 3 180 + п

Ціна 9 10 16

Задача. Аналіз кредиту

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

1. Задача 8 є задачею лінійного програмування. Вона розв'я­зується за допомогою Інструмента Пошук розв'язку (Solver).

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити цілі значення х, у, z, для яких досягається максимум функції прибутку f = 9 х + 10 y + 16 z за таких обмежень:

18 x +15 y + 12 z <= 360 - n

6 x + 4 y + 8 z <= 192

5 x +3 y + 3 z <= 180 - n

x, y, z >= 0; x, y, z — цілі.

Розв'язування. Для розв'язування потрібно виконати такий алгоритм:

1) клітинкам А1, В1, С1 присвоїти імена х, у, z;

2) у клітинку D1 ввести формулу =9* х +10* y +16* z;

3) запустити програму Пошук розв'язку з меню Сервіс;

4) задати адресу цільової клітинки D1 і зазначити дію до­сягнення максимуму функції (рис. 1);

рис. 1

5) задати клітинки, де має міститися розв'язок: х; у; z;

6) за допомогою кнопки Додати обмеження (рис. 2) у вигляді дев’яти умов (значення п потрібно підставити конкретне):

х <= (360 - n - 15* y - 12* z)/18

у <= (192 - 6* x - 8* z)/4

z <= (180 + n - 5* x - 3* у)/3

х >= 0; у >= 0; z >= 0

х — ціле; у — ціле; z — ціле;

7) натиснути на кнопку Параметри і зазначити, що модель лінійна;

Рис. 2. Вікно для введення обмежень.

8) отримати розв'язок, натиснувши на кнопку Виконати. Для п = 0 відповідь (у клітинках А1, В1, С1, D1) така: х = 0, у = 8, z = 20, f = 400. ВІДПОВІДІ ЗАПИСАТИ ДО ЗВІТУ.

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

На прикладі розв'язування задачі 9 розглянемо вплив зміни одного параметра (кількості періодів позики) на дві величини: щомісячну і сумарну виплати за кредит.

Основною формулою розв'язування задачі є =ПЛТ(ПС; КП; сума кредиту), яка розглядалася в попередній роботі. Виконайте наступний алгоритм (рис. 45).

1. У діапазон А1:АЗ введіть вхідні дані: ставку (6%), кіль­кість періодів (4) і суму кредиту, нехай, 3000.

2. У діапазон А5:А8 введіть можливі терміни позики: 4, 5, 6, 7.

3. У клітинку В5 введіть формулу =ПЛТ(А1; А2; A3). У клітинку С5 введіть формулу =В5*А2. Ці формули мають бути першими у своїх стовпцях.

4. Виокремте діапазон А5:С8 і застосуйте команду Дані => Таблиця підстановки (Table...). Параметром у даній задачі є кіль­кість періодів з клітинки А2. Тому в отриманому діалоговому вікні у друге поле Підставляти значення по рядках введіть А2. Натис­ніть на кнопку ОК. Отримаєте таблицю, придатну для прийняття рішень. Який термін позики вам найбільше підходить?

Для аналізу щомісячних виплат, залежних від двох пара­метрів (можливих сум і термінів позики) таблицю будують так: у клітинку D1 вводять формулу =ПЛТ(А1; А2; A3). Клітинки справа Е1:Н1 заповнюють деякими можливими сумами позики: 2000, 2500, 3000, 3500, а клітинки знизу (D2:D5) - можливими термінами 4, 5, 6, 7 місяців. Вибирають прямокутний діапазон D1:H5 і виконують команду Дані => Таблиця підстановки. В отриманому діалоговому вікні у перше поле вводять A3, а в друге — А2. Отримаємо таблицю, аналіз якої дає змогу вибрати суму і термін позики, враховуючи щомісячну платоспроможність підприємця.

Рис. 3. Зразок розв'язування задачі 9.

Цю задачу можна розв'язати іншим способом — методом табулювання функції двох змінних ПЛТ($А$1; термін; сума позики). Для цього очистіть діапазон Е2:Н5, у клітинку Е2 введіть формулу =ПЛТ($А$1; $D2; Е$1) і скопіюйте її у діапазон Е2:Н5.

Хід роботи

1. Розв'яжіть задачі.

Виконайте вказівки, описані в теоретичних відомостях.

Зміст звіту з лабораторної роботи.

1. № роботи.

2. Тема.

3. Мета.

4. Обладнання.

5. Програма виконання роботи.

6. Обробка результатів - покроковий опис дій, перелічених у розділі "Програма виконання".

7. Висновки.