Роздрукувати сторінку
Главная \ Методичні вказівки \ Методичні вказівки \ 929 Практична робота №2 на тему Розробка табличних моделей економічних детермінованих задач у Microsoft Excel із застосуванням засобу-надбудови Поиск решения

Практична робота №2 на тему Розробка табличних моделей економічних детермінованих задач у Microsoft Excel із застосуванням засобу-надбудови Поиск решения

« Назад

ПРАКТИЧНА РОБОТА №2
Тема: "Розробка табличних моделей економічних детермінованих задач у Microsoft Excel із застосуванням засобу-надбудови Поиск решения"

Мета: навчитись застосовувати засіб – надбудову MS Excel Поиск решения для побудови моделей економічних детермінованих задач.

Час виконання роботи – 2 години

ХІД РОБОТИ

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

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

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

Таблиця 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у, грн.

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

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

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

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

  3. Ця комірка буде цільовою коміркою.

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

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

  6. Додайте умову невід’ємності Х та У.

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

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

  9. Знайдіть розв’язок.

Завдання 2. Захистіть виконану роботу.

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

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

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

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

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

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

  6. Що відображається у звіті типу „Результаты”?

  7. Що відображається у звіті типу „Устойчивость”?

  8. Що відображається у звіті типу „Пределы”?

З повагою ІЦ “KURSOVIKS”!