Главная страница Случайная страница
Разделы сайта
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Тема: Способи розв’язування екстремальних задач в Excel, технологія «Поиск решения», аналіз та корегування отриманого рішення.
|
|
Лабораторна робота № 6
Мета: Отримати практичні навики розв’язання екстремальних задач економічного змісту за
допомогою інструменту Пошуку рішень, вивчити можливості застосовування засобу – надбудови MS Excel «Поиск решения»
Навчальні питання:
1. Надбудова MS Excel «Поиск решения».
2. Методика розв’язування задач засобами MS Excel «Поиск решения»
Обладнання та методичне забезпечення: персональні комп'ютери, програмне забезпечення, роздатковий дидактичний матеріал.
Література: 1. Берлинер Э.М., Глазырина И.Б., Глазырин Б.Э. Microsoft Office 2003. – М.: ООО «Бином-Пресс», 2004. – 576 с.
2. Блаттнер П. Использование Microsoft Office Excel 2003. - М.: Вільямс, 2004. - 864 с.
3. Глушаков С.В., Мачула О.В., Сурядный А.С. Редактор электронных таблиц Microsoft Excel XP / Худож.-оформитель А.С. Ютман. – Харьков: Фолио, 2003. – 95 с.
4. Інформатика: Комп’ютерна техніка. Комп’ютерні технології. Посіб. / За ред. О.І.Пушкаря. – К., 2005. – 696 с.
ТЕОРЕТИЧНІ ВІДОМОСТІ:
Засіб Поиск решения є частиною засобів розв’язання блоку завдань, який називають аналізом «що – якщо». Процедура пошуку рішення дає змогу знайти оптимальне значення формули, яка міститься в комірці, що визначається як цільова комірка. Ця процедура працює з групою комірок, безпосередньо або опосередковано пов’язаних із формулою в цільовій комірці. Для одержання за формулою, яка є в цільовій комірці, заданого результату, процедура змінює значення у комірках, які впливають на це значення. Щоб звузити безліч значень, що використовуються в моделі, застосовуються обмеження; вони можуть посилатися на інші комірки впливу. Процедуру пошуку рішення застосовують з метою визначення значення комірки впливу, яке відповідає екстремуму залежної комірки. Наприклад, можна змінити обсяг планованого бюджету реклами і з’ясувати, як це вплине на проектовану суму витрат. Завдань такого змісту є дуже багато у фінансово-економічній сфері, сфері маркетингу та менеджменту. Вони дуже важливі, й тому важливо вміти їх розв’язувати. Середовище MS Excel для розв’язання таких завдань надає до послуг користувачів такий засіб, як надбудова Поиск решения. Щоб з’явився цей засіб, потрібно відкрити меню Сервис і вибрати пункт Поиск решения, за умови, що під час інсталяції програмного засобу MS Excel надбудови встановлені на комп’ютері. У процесі першого його використання потрібно виконати активізацію засобу меню Сервис®Надстройки®Поиск решения.
хід виконання роботи:
Створіть документ MS Excel з ім’ям Лаборат_6 і зберігайте усі дані лабораторної роботи у своїй папці. Назвіть робочі аркуші відповідно Завдання 1, Завдання 2, і виконайте на них необхідні обчислення.
Завдання 1. Визначте оптимальний місячний план випуску продукції, за якого витрати на виготовлення будуть мінімальними. Кількість продукції кожного виду змінюється в межах від 15 до 35 одиниць, а загальна кількість одиниць становить не менше 125.
Таблиця 7.1
Алгоритм розв’язання:
1. На робочому аркуші Завдання 1 створіть дані як у табл. 7.1.
2. Виконайте обчислення у стовпці Витрати на партію продукції товару, застосовуючи формули та копіюючи їх на потрібний діапазон комірок.
3. У рядку Всього обчисліть сумарну кількість одиниць продукції та загальні витрати на партію продукції.
4. Усвідомте той факт, що цільовою коміркою в цій задачі буде комірка, в якій обчислено загальні витрати на партію продукції.
5. Виділіть комірку, де вирахувано загальні витрати на партію продукції.
6. У меню Сервис клацніть на пункті Поиск решения.
7. У діалоговому вікні Поиск решения (рис.7.2) в полі Установить целевую ячейку відображатиметься абсолютна адреса цільової комірки.
8. Встановіть альтернативний перемикач групи Равной у положення Минимальному значению.
Рис.1 Діалогове вікно «Поиск решения». Встановлення режиму мінімізації значення цільової комірки
10. З’ясуйте, в яких комірках величини впливають на значення цільової комірки. Оскільки витрати на виготовлення одиниці продукції в таких умовах змінюватися не можуть, то величинами, які впливають, будуть кількості одиниць певного різновиду продукції, отже, комірки C3: C8 є такими, що впливають.
11. У діалоговому вікні Поиск решения встановіть текстовий курсор у текстовому віконці Изменяя ячейки і виділіть діапазон комірок C3: C8 (рис.2).
Рис.2. Діалогове вікно Поиск решения. Встановлення діапазону комірок впливу
12. За умовою задачі загальна кількість одиниць продукції має бути не меншою ніж 125, а кількість одиниць продукції кожного різновиду перебувати в межах між 15 і 35 одиницями. У цих умовах і створюється система обмежень на величини комірок впливу.
13. Натисніть кнопку Добавить в діалоговому вікні Поиск решения.
В діалоговому вікні Добавление ограничения (рис.3) встановіть текстовий курсор у полі Ссылка на ячейку і клацніть на кнопку комірки, в якій обчислено загальну кількість одиниць продукції. Потім виберіть зі списку Оператор порівняння > = і встановіть текстовий курсор у текстовому полі Ограничение, введіть із клавіатури 125, після чого клацніть на кнопку Добавить цього самого діалогового вікна.
Рис.3. Діалогове вікно Встановлення обмеження на загальну кількість одиниць продукції Добавление ограничения.
15. Введіть наступне обмеження на комірки впливу C3: C8, яке полягає в тому, що кількість одиниць продукції кожного різновиду не перевищує 35. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (рис.4).
Рис.4. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції кожного різновиду не більша 35.
16. Введіть наступне обмеження на комірки впливу C3: C8, яке полягає в тому, що кількість одиниць продукції кожного різновиду не менша 15. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження (рис.5).
Рис.5. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції кожного різновиду не менша 15.
17. Введіть наступне обмеження на комірки впливу C3: C8, яке полягає в тому, що кількість одиниць продукції кожного різновиду становить ціле число (рис.6) і ОК.
Рис.6. Діалогове вікно Добавление ограничения. Встановлення обмеження: кількість одиниць продукції становить ціле число.
18. Діалогове вікно Поиск решения матиме вигляд як на рис.7.
Рис.7. Діалогове вікно Поиск решения. Вигляд після встановлення необхідних режимів та обмежень.
19. Клацніть на кнопку Параметры діалогового вікна Поиск решения і вивчіть діалогове вікно Параметры поиска решения (рис.8), а також встановлені за замовчуванням значення й положення індикаторних та альтернативних перемикачів.
Рис.8. Діалогове вікно Параметры поиска решения
20. Клацніть на кнопку Справка діалогового вікна Параметры поиска решения і вивчіть довідкову інформацію. Після цього закрийте вікно Справка, натисніть на кнопку ОК діалогового вікна Параметры поиска решения. У діалоговому вікні Поиск решения натисніть на кнопку Выполнить.
21. У діалоговому вікні Результаты поиска решения (рис.9), якщо розв’язок знайдено, клацніть на кнопці ОК при положенні альтернативного перемикача Сохранить найденное решение.
22. Перейдіть до таблиці значень й уважно вивчіть встановлені в комірках C3: C8 i G14 значення.
Рис.9. Діалогове вікно Результаты поиска решения
Значення величин у комірках C3: C8 i G14 є розв’язком задачі. Порівняйте з вихідною таблицею, зробіть висновки.
Задайте інші параметри на загальну кількість та на мінімальну і максимальну кількість продукції.
ЗАВДАННЯ 2. Знайдіть хоча б одне значення економічного показниках, яке є невід’ємним розв’язком рівняння:
Завдання 2. Знайдіть хоча б одне значення економічного показниках, яке є невід’ємним розв’язком рівняння: 
Алгоритм розв’язання:
1. На робочому аркуші Завдання 2 виділіть деяку комірку, наприклад К5, і введіть у неї довільне числове значення X.
2. В іншу комірку, наприклад М5, введіть формулу, що відповідає лівій частині рівняння.
3. З’ясуйте той факт, що цільовою коміркою в цій задачі буде комірка, в якій обчислено значення лівої частини рівняння, тобто комірка М5.
4. Виділіть цільову комірку, тобто комірку М5.
5. У меню Сервис клацніть на кнопку пункту Поиск решения.
6. У діалоговому вікні Поиск решения (рис.10) у полі Установить целевую ячейку відображатиметься абсолютна адреса цільової комірки.
Рис.10. Діалогове вікно Поиск решения
7. Встановіть альтернативний перемикач групи Равной у положення Значению і введіть у текстове поле значення правої частини рівняння. Це буде нуль.
8. У діалоговому вікні Поиск решения встановіть текстовий курсор у текстовому віконці Изменяя ячейки і виділіть комірку К5 (див. рис.10).
9. За умовою задачі значення показника X має бути невід’ємним (X < = 0). Ця умова і створює обмеження на величину комірки впливу К5.
10. Натисніть на кнопку Добавить у діалоговому вікні Поиск решения.
11. У діалоговому вікні Добавление ограничения (рис.11) встановіть текстовий курсор у полі Ссылка на ячейку і клацніть на кнопку комірки К5. Потім виберіть зі списку оператор порівняння > =, встановіть текстовий курсор у текстовому полі Ограничения і введіть значення 0.
Рис.11. Діалогове вікно Добавление ограничения. Встановлення обмеження на значення розв’язку.
12. Діалогове вікно Поиск решения матиме вигляд, як на рис.12.
У діалоговому вікні Поиск решения натисніть кнопку Выполнить.
Зауваження: подбайте, щоб точність становила 0, 000 000 01.
Рис.12. Діалогове вікно Поиск решения після встановлення обмежень на комірку впливу
13. У діалоговому вікні Результаты поиска решения клацніть на кнопку ОК за положення альтернативного перемикача Сохранить найденное решение.
14. Перейдіть до комірок значень та уважно вивчіть встановлені в комірках М5 і К5 значення. Значення у комірці К5 є розв’язком задачі.
15. У текстовому полі Ограничения введіть інше числове значення (наприклад - 5). У діалоговому вікні Результаты поиска решения клацніть на кнопку ОК. Перевірте отриманий результат.
Зауваження: За потреби можна знайти ще розв’язок, якщо він є на множині дійсних чисел, правильно задаючи обмеження.
Контрольні питання:
1. Які задачі можна розв’язувати, використовуючи надбудову Поиск решения?
2. Як у Excel задати надбудову Поиск решения?
3. Яка комірка називається цільовою?
4. Як сформувати обмеження на значення у певних комірках?
5. Як задати точність розв’язку?
6. Наведіть приклади задач, що потребують застосування засобу Поиск решения.
|
|