Практична робота 15 на тему Табличний процесор Microsoft Excel та Функція Поиск решения

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

Тема: "Табличний процесор Microsoft Excel. Функція Поиск решения"

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

Хід роботи

Завдання 1. Уважно вивчіть теоретичні відомості. Занотуйте у звіт найважливіше.

Функція Поиск решения є частиною блоку задач, який називають аналізом "що-якщо".    Процедура пошуку рішення дозволяє знайти оптимальне значення формули, яка  міститься в комірці, яка називається цільовою коміркою.Ця процедура працює з групою комірок, прямо або опосередовано пов’язаних з формулою в цільовій комірці.Щоб одержати за формулою, що міститься в цільовій комірці,заданий результат, процедура змінює значення у впливаючих  на це значення комірках. Щоб звузити безліч значень, що використовуються в моделі, застосовуються обмеження.Ці обмеження можуть посилатися на інші впливаючі комірки.Процедуру пошуку рішення можна використовувати для визначення значення впливаючої комірки,  яке відповідає екстремуму залежної комірки — наприклад, можна змінити об’єм планованого бюджету реклами і побачити, як це вплине на проектовану суму витрат. Задач такого змісту існує дуже багато у фінансово-економічній сфері, сфері маркетингу та менеджменту.Ці задачі дуже важливі і тому важливо вміти їх розв’язувати.Середовище MS Excel  для розв’язання таких задач надає до послуг користувачів функцію- надбудову Поиск решения.Щоб викликати цю функцію- надбудову, потрібно відкрити меню Сервис і вибрати пункт Поиск решения,за умови, що при інсталяції програмного засобу MS Excel надбудови встановлені на вашому комп’ютері.Засіб пошуку рішення Microsoft Excel використовує алгоритм нелінійної оптимізації Generalized Reduced Gradient (GRG2), розроблений Леоном Ласдоном (Leon Lasdon, University Texas at Austin)і Аланом Уореном (Allan Waren, Cleveland State University).Алгоритми сімплексного методу і методу «branch-and-bound» для вирішення лінійних і цілочисельних задач  з обмеженнями розроблені Джоном Уотсоном (John Watson) і Деном Філстра (Dan Fylstra) з Frontline Systems, Inc.

Задача 1

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

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

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

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

Запишіть у звіт цю таблицю.

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

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

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

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

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

8. Усвідомте, величини в яких комірках впливають на значення цільової комірки.

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

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

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

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

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

13. Далі введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду не перевищує  35 одиниць.

Натисніть кнопку Добавить.

14. Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду не менше 15 одиниць.

15. Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду - ціле число.

Натисніть кнопку ОК.

16. Відкриється діалогове вікно Поиск решения, яке матиме вигляд:

17.Натисніть кнопку Параметры діалогового вікна Поиск решения і вивчіть діалогове вікно Параметры поиска решения, а також встановлені за замовчуванням значення та положення індикаторних і альтернативних перемикачів.

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

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

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

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

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

Задача 2

Знайти хоча б одне  значення економічного показника х, яке є невід’ємним розв’язок рівняння

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

1. Виділіть деяку комірку, наприклад К5, і введіть в неї довільне числове значення Х.

2. У іншу комірку, наприклад М5, введіть  формулу, яка відповідає лівій частині рівняння: =K5^5-2*K5^4+17*K5^3-37*K5^2+11*K5-57

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

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

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

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

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

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

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

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

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

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 грн. Знайдіть тижневий план випуску виробів типу А та типу Б, який забезпечить максимальний прибуток фірмі.

Математична модель задачі

Нехай Х - кількість виробів типу А, а У - кількість виробів типу Б.

Тоді прибуток = 140х+200у, грн.

Система обмежень:

Розв’яжіть задачу самостійно, використовуючи функцію Поиск решения.

Алгоритм

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

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

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

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

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

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

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

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

9. Знайти розв’язок.

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

Перелік питань до захисту практичної роботи

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

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

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

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

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

З повагою ІЦ "KURSOVIKS"!