Практична робота №2 на тему Розробка табличних моделей економічних детермінованих задач у Microsoft Excel із застосуванням засобу-надбудови Поиск решения
« НазадПРАКТИЧНА РОБОТА №2
|
Таблиця 2.1 |
|||||||
|
D |
E |
F |
G |
|||
6 |
ВІДОМОСТІ ПРО ВИПУСК ПРОДУКЦІЇ ЗА МІСЯЦЬ |
||||||
7 |
Назва продукції |
Витрати на одиницю продукції, грн. |
Кількість одиниць |
Витрати на партію продукції, грн. |
|||
8 |
Продукція 1 |
100 |
25 |
|
|||
9 |
Продукція 2 |
80 |
35 |
|
|||
10 |
Продукція 3 |
150 |
15 |
|
|||
11 |
Продукція 4 |
230 |
15 |
|
|||
12 |
Продукція 5 |
96 |
32 |
|
|||
13 |
Продукція 6 |
130 |
15 |
|
|||
14 |
Всього |
|
|
|
|||
Алгоритм роз’язання
1. Створіть документ Ms Excel з ім’ям Практ_2.xls. Дайте імена робочим аркушам відповідно Завдання 1, Завдання 2, Завдання 3, Завдання 4 для побудови на них відповідних табличних моделей.
2. На робочому аркуші Завдання 1 створіть дані як у табл.31.1.
3.Виконайте необхідні обчислення у стовпці Витрати на партію товару, застосовуючи формули і копіюючи їх на потрібний діапазон комірок.
4. У рядку Всього обчисліть сумарну кількість одиниць продукції та сумарні витрати на партію продукції.
5. Усвідомте той факт, що цільовою коміркою в даній задачі буде комірка, в якій обчислено сумарні витрати на партію продукції.
6. Виділіть комірку, в якій обчислені сумарні витрати на партію продукції.
7. В меню Сервис клацніть на пункті Поиск решения.
8. В діалоговому вікні Поиск решения (Рис. 2.1) в полі Установить целевую комірку буде відображатись абсолютна адреса цільової комірки.
9. Встановіть альтернативний перемикач групи Равной в положення минимальному значению.
10. Усвідомте, величини в яких комірках впливають на значення цільової комірки.
Оскільки витрати на виготовлення одиниці продукції в даних умовах змінюватись не можуть, то впливаючими величинами будуть кількість одиниць даного різновиду продукції, отже комірки F8:F13 – впливаючі.
9. У діалоговому вікні Поиск решения встановіть текстовий курсор в текстовому віконці Изменяя ячейки і виділіть діапазон комірок F8:F13 (Рис. 2.2).
10.За умовою задачі сумарна кількість одиниць продукції повинна бути не меншою за 125, а кількість одиниць продукції кожного різновиду повинна знаходитись в межах між 15 і 35 одиницями. Ці умову і створюють систему обмежень на величини впливаючих комірок.
11. Натисніть кнопку Добавить в діалоговому вікні Поиск решения.
12. В діалоговому вікні Добавление ограничения (Рис. 2.3) становіть текстовий курсор в полі Ссылка на ячейку і клацніть на комірці, в якій обчислено сумарну кількість одиниць продукції. Потім виберіть із списку оператор порвняння >= , а далі встановіть текстовий курсор в текстовому полі Ограничение і введіть з клавіатури 125, після чого клацніть кнопку Добавить цього ж діалогового вікна.
13. Далі введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду не перевищує 35 одиниць. Натисніть кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (Рис. 2.4).
Встановлення обмеження: кількість одиниць продукції кожного різновиду не більша 35
14. Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду не менше 15 одиниць. Натисніть кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (Рис. 2.5).
Встановлення обмеження: кількість одиниць продукції кожного різновиду не менша 15
15.Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що кількість одиниць продукції кожного різновиду - ціле число (Рис. 2.6). Натисніть кнопку ОК.
16. Діалогове вікно Поиск решения, матиме виглядяк на Рис. 2.7.
17.Натисніть кнопку Параметры діалогового вікна Поиск решения і вивчіть діалогове вікно Параметры поиска решения (Рис. 2.8), а також встановлені за замовчуванням значення та положення індикаторних і альтернативних перемикачів.
18.Натисніть кнопку Справка діалогового вікна Параметры поиска решения і вивчіть довідкову інформацію. Після вивчення закрийте вікно Справка і натисніть кнопку ОК діалогового вікна Параметры поиска решения. В діалоговому вікні Поиск решения натисніть кнопку Выполнить.
19.В діалоговому вікні Результаты поиска решения (Рис. 2.9), якщо розв’язок знайдено, клацніть на кнопці ОК при положенні альтернативного перемикача Сохранить найденное решение.
20.Перегляньте звіти: Результаты, Устойчивость та Пределы. Проаналізуйте їх.
21. Перейдіть до таблиці значень і уважно вивчіть встановлені у комірках F8:F13 та G14 значення.
Значення величин у комірках F8:F13 та G14 і є розв’язком задачі.
Завдання 2. Знайти хоча б одне значення економічного показника х, яке є невід’ємним розв’язком рівняння:
Алгоритм розв’язання
1. На робочому аркуші Завдання 2 виділіть деяку комірку, наприклад К5, і введіть в неї довільне числове значення величини х.
2. У іншу комірку, наприклад М5, введіть формулу, яка відповідає лівій частині рівняння:
=K5^5-2*K5^4+17*K5^3-37*K5^2+11*K5-57
Усвідомте той факт, що цільовою коміркою в даній задачі буде комірка, в якій обчислено значення лівої частини рівняння, тобто комірка М5.
3. Виділіть цільову комірку, тобто комірку М5.
4. В меню Сервис клацніть на пункті Поиск решения.
5. В діалоговому вікні Поиск решения (Рис. 2.10) в полі Установить целевую комірку буде відображатись абсолютна адреса цільової комірки.
6. Встановіть альтернативний перемикач групи Равной в положення значению і введіть в текстове поле значення правої частини рівняння. У даному випадку це буде нуль.
7. У діалоговому вікні Поиск решения встановіть текстовий курсор в текстовому віконці Изменяя ячейки і виділіть комірку К5 (Рис. 2.10).
8. За умовою задачі значення показника Х має бути невід’ємним (X>=0). Ця умова і створює обмеження на величину впливаючої комірки К5.
9. Натисніть кнопку Добавить в діалоговому вікні Поиск решения.
10. В діалоговому вікні Добавление ограничения (Рис. 2.11) встановіть текстовий курсор в полі Ссылка на ячейку і клацніть на комірці К5. Потім виберіть із списку оператор порівняння >= , а далі встановіть текстовий курсор в текстовому полі Ограничения і введіть значення 0.
11. Діалогове вікно Поиск решения матиме вигляд як на Рис. 2.12.
12. В діалоговому вікні Поиск решения натисніть кнопку Выполнить.
Зауваження. подбайте, щоб точність становила 0,000 000 01.
13. В діалоговому вікні Результаты поиска решения клацніть на кнопці ОК при положенні альтернативного перемикача Сохранитьнайденное решение.
14. Перейдіть до комірок значень і уважно вивчіть встановлені у комірках М5 та К5 значення. Значення у комірці К5 і є розв’язком задачі.
Зауваження. При необхідності можна знайти ще розв’язок, якщо він існує на множині дійсних чисел, правильно задаючи обмеження.
Завдання 3. Знайти значення економічного показника, яке є невід’ємним розв’язком рівняння .
Самостійно виконайте завдання, послуговуючись алгоритмом задачі 2.
Подбайте, щоб точність розв’язку становила 0,000 000 1.
Завдання 4. Деяка фірма випускає вироби двох різновидів А та В. Кожний різновид виробу вимагає певного часу на виготовлення і збірку. Кожний виріб А потребує 5 годин на виготовлення і 2 годин на збірку. Кожний виріб В потребує 3 годин на виготовлення і 4 годин на збірку. Протягом робочого тижня фірма має 126 годин на виготовлення і 80 годин на збірку виробів. Фірма може продати всі виготовлені вироби, тому що вони користуються попитом. Кожний виріб А дає прибуток у 140 грн., а кожний виріб Б дає прибуток у 200 грн. Знайдіть тижневий план випуску виробів типу А та типу Б, який забезпечить максимальний прибуток фірмі.
Математична модель задачі
Нехай x - кількість виробів типу А, а y - кількість виробів типу Б.
Тоді прибуток = 140х+200у, грн.
Система обмежень, що випливає з умов задачі:
Алгоритм розв’язання
-
В деякі дві комірки робочого аркуша Завдання 4 введіть довільні допустимі значення Х та У.
-
Далі в деяку третю комірку введіть формулу обчислення прибутку.
-
Ця комірка буде цільовою коміркою.
-
Ще в дві комірки введіть формули обчислення лівих частин перших нерівностей системи обмежень.
-
При створенні обмежень у діалоговому вікні Поиск решения посилайтесь на ці останні дві комірки, при створенні перших двох обмежень системи обмежень.
-
Додайте умову невід’ємності Х та У.
-
Додайте умову цілочислових значень Х та У.
-
Задайте точність.
-
Знайдіть розв’язок.
Завдання 2. Захистіть виконану роботу.
Питання до захисту практичної роботи
-
Які задачі можна розв’язувати з використанням надбудови Поиск решения?
-
Яку комірку називають цільовою?
-
Як сформувати обмеження на значення певних комірках?
-
Як задати точність розвязку?
-
Наведіть приклади задач, які потребують використання засобу Поиск решения.
-
Що відображається у звіті типу „Результаты”?
-
Що відображається у звіті типу „Устойчивость”?
- Що відображається у звіті типу „Пределы”?
З повагою ІЦ “KURSOVIKS”!