Інформаційні технології (Практичні роботи)
136

Практична робота №8. MS Excel. Інструменти Пошук розв'язку, Таблиця підстановки

Мета

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

План

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

2. Аналіз кредиту таблицями підстановки.

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

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

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

I             18      15      12              360 -п

II            6        4        8                192

III           5        3        3                180 + п Ціна      9        10      16

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

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

 Теоретичні відомості

1. Планування випуску продукції.Задача 9 є задачею ліній-

ного програмування. Вона розв'язується за допомогою інстру-

мента Пошук РОЗВ'ЯЗКУ (Solver).

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

18х + 15і/ + 12z<= 360 - п 6х + 4у + 8z <= 192 5х +3у + 3z <=180 + п х, у  z >== 0; хуу,г — цілі. Розв'язування. Потрібно виконати такий алгоритм:

1) клітинкам А1, В1, С1 присвоїти імена х, у, г командами Вставити => Ім'я => Присвоїти=> Ввести х => ОК і т.д.;

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

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

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

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

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

х <=(360 - п - 15*у - 12*z)/18

у <= (192 - 6*х - 8*2)/4

z <= (180 + п - 5*х - 3*у)/3

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

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